-- Spread application schema. Applied idempotently on startup.

CREATE TABLE IF NOT EXISTS subscribers (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    is_internal BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS users (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    subscriber_id BIGINT NOT NULL REFERENCES subscribers(id),
    email         TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL DEFAULT '',
    name          TEXT NOT NULL DEFAULT '',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS sessions (
    token_hash TEXT PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at TIMESTAMPTZ NOT NULL
);

-- one-time tokens for invites and password resets
CREATE TABLE IF NOT EXISTS user_tokens (
    token_hash TEXT PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    purpose    TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at TIMESTAMPTZ NOT NULL,
    used_at    TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS catalog (
    part_number  TEXT PRIMARY KEY,
    description  TEXT NOT NULL DEFAULT '',
    spread_price NUMERIC(12,4) NOT NULL,
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS boms (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    subscriber_id BIGINT NOT NULL REFERENCES subscribers(id),
    uploaded_by   BIGINT REFERENCES users(id),
    ref           TEXT NOT NULL,
    filename      TEXT NOT NULL DEFAULT '',
    total_savings NUMERIC(14,2) NOT NULL DEFAULT 0,
    our_cut       NUMERIC(14,2) NOT NULL DEFAULT 0,
    net_savings   NUMERIC(14,2) NOT NULL DEFAULT 0,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS bom_lines (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    bom_id       BIGINT NOT NULL REFERENCES boms(id) ON DELETE CASCADE,
    line         INT NOT NULL DEFAULT 0,
    part_number  TEXT NOT NULL DEFAULT '',
    description  TEXT NOT NULL DEFAULT '',
    quantity     NUMERIC NOT NULL DEFAULT 0,
    unit_price   NUMERIC(12,4) NOT NULL DEFAULT 0,
    spread_price NUMERIC(12,4) NOT NULL DEFAULT 0,
    line_saving  NUMERIC(14,2) NOT NULL DEFAULT 0,
    matched      BOOLEAN NOT NULL DEFAULT FALSE
);