

-- ── Extend existing tables ────────────────────────────────────

-- users: theme (default 'light'), referral_code, referred_by
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS theme         user_theme NOT NULL DEFAULT 'light',
    ADD COLUMN IF NOT EXISTS referral_code VARCHAR(16) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS referred_by   CHAR(36)   DEFAULT NULL;

-- Ensure theme default is 'light' even if column already existed with 'dark'
ALTER TABLE users ALTER COLUMN theme SET DEFAULT 'light';

-- sms_logs: send_method
ALTER TABLE sms_logs
    ADD COLUMN IF NOT EXISTS send_method send_method NOT NULL DEFAULT 'system';

-- ── Missing tables from base schema ─────────────────────────

-- contact_group_members (many-to-many)
CREATE TABLE IF NOT EXISTS contact_group_members (
    id          TEXT        NOT NULL,
    group_id    TEXT        NOT NULL,
    contact_id  TEXT        NOT NULL,
    created_at  TIMESTAMP   NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (group_id, contact_id),
    FOREIGN KEY (group_id)   REFERENCES contact_groups (id) ON DELETE CASCADE,
    FOREIGN KEY (contact_id) REFERENCES contacts        (id) ON DELETE CASCADE
);

-- sms_details (per-recipient delivery record)
CREATE TABLE IF NOT EXISTS sms_details (
    id          TEXT        NOT NULL,
    log_id      TEXT        NOT NULL,
    user_id     TEXT        NOT NULL,
    phone       TEXT        NOT NULL,
    status      TEXT        NOT NULL DEFAULT 'pending',
    gateway_ref TEXT        DEFAULT NULL,
    error_msg   TEXT        DEFAULT NULL,
    sent_at     TIMESTAMP   DEFAULT NULL,
    created_at  TIMESTAMP   NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id),
    FOREIGN KEY (log_id)  REFERENCES sms_logs (id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users    (id) ON DELETE CASCADE
);

-- ── New Feature Tables ────────────────────────────────────────

-- pricing_tiers: tiered quantity-based pricing
--   scope: 'global' (admin default), 'user' (per-user override by admin),
--          'reseller' (reseller sets for their customers)
CREATE TABLE IF NOT EXISTS pricing_tiers (
    id          TEXT            NOT NULL,
    scope       VARCHAR(10)     NOT NULL DEFAULT 'global',
    user_id     TEXT            DEFAULT NULL,
    min_qty     INTEGER         NOT NULL DEFAULT 1,
    max_qty     INTEGER         DEFAULT NULL,
    price_per_sms NUMERIC(10,6) NOT NULL,
    created_at  TIMESTAMP       NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMP       NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_pricing_tiers_scope   ON pricing_tiers (scope);
CREATE INDEX IF NOT EXISTS idx_pricing_tiers_user_id ON pricing_tiers (user_id);

-- orders: SMS purchase orders
CREATE TABLE IF NOT EXISTS orders (
    id          TEXT         NOT NULL,
    user_id     TEXT         NOT NULL,
    sms_units   INTEGER      NOT NULL DEFAULT 0,
    amount      NUMERIC(18,6) NOT NULL DEFAULT 0,
    price_per_sms NUMERIC(10,6) NOT NULL DEFAULT 0,
    status      order_status NOT NULL DEFAULT 'pending',
    note        TEXT         DEFAULT NULL,
    paid_at     TIMESTAMP    DEFAULT NULL,
    paid_by     TEXT         DEFAULT NULL,
    created_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY (paid_by) REFERENCES users (id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_orders_user   ON orders (user_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders (status);

-- sender_ids: Admin-managed Sender IDs
CREATE TABLE IF NOT EXISTS sender_ids (
    id          TEXT              NOT NULL,
    name        VARCHAR(20)       NOT NULL,
    description TEXT              DEFAULT NULL,
    status      sender_id_status  NOT NULL DEFAULT 'active',
    created_by  TEXT              DEFAULT NULL,
    created_at  TIMESTAMP         NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMP         NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (name),
    FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_sender_ids_status ON sender_ids (status);

-- sender_id_assignments: which Sender IDs are assigned to which users
CREATE TABLE IF NOT EXISTS sender_id_assignments (
    id          TEXT      NOT NULL,
    sender_id_id TEXT     NOT NULL,
    user_id     TEXT      NOT NULL,
    assigned_by TEXT      DEFAULT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (sender_id_id, user_id),
    FOREIGN KEY (sender_id_id) REFERENCES sender_ids (id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)      REFERENCES users       (id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by)  REFERENCES users       (id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_sida_user      ON sender_id_assignments (user_id);
CREATE INDEX IF NOT EXISTS idx_sida_sender_id ON sender_id_assignments (sender_id_id);

-- sender_id_requests: Customer uploads request letter
CREATE TABLE IF NOT EXISTS sender_id_requests (
    id           TEXT                  NOT NULL,
    user_id      TEXT                  NOT NULL,
    sender_name  VARCHAR(20)           NOT NULL,
    reason       TEXT                  DEFAULT NULL,
    document_path TEXT                 DEFAULT NULL,
    status       sender_id_req_status  NOT NULL DEFAULT 'pending',
    reviewed_by  TEXT                  DEFAULT NULL,
    review_note  TEXT                  DEFAULT NULL,
    reviewed_at  TIMESTAMP             DEFAULT NULL,
    created_at   TIMESTAMP             NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMP             NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id)     REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users (id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_sidr_user   ON sender_id_requests (user_id);
CREATE INDEX IF NOT EXISTS idx_sidr_status ON sender_id_requests (status);

-- referral_codes: per-user referral codes and bonus configuration
CREATE TABLE IF NOT EXISTS referral_codes (
    id            TEXT        NOT NULL,
    user_id       TEXT        NOT NULL,
    code          VARCHAR(16) NOT NULL,
    bonus_sms     INTEGER     NOT NULL DEFAULT 0,
    referee_bonus INTEGER     NOT NULL DEFAULT 0,
    used_count    INTEGER     NOT NULL DEFAULT 0,
    is_active     BOOLEAN     NOT NULL DEFAULT TRUE,
    expires_at    TIMESTAMP   DEFAULT NULL,
    created_at    TIMESTAMP   NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMP   NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (code),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_referral_codes_user   ON referral_codes (user_id);
CREATE INDEX IF NOT EXISTS idx_referral_codes_active ON referral_codes (is_active);

-- referral_uses: record each time a referral code was used
CREATE TABLE IF NOT EXISTS referral_uses (
    id              TEXT      NOT NULL,
    code_id         TEXT      NOT NULL,
    referee_user_id TEXT      NOT NULL,
    bonus_granted   INTEGER   NOT NULL DEFAULT 0,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id),
    UNIQUE (code_id, referee_user_id),
    FOREIGN KEY (code_id)         REFERENCES referral_codes (id) ON DELETE CASCADE,
    FOREIGN KEY (referee_user_id) REFERENCES users          (id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_referral_uses_code ON referral_uses (code_id);
