-- ============================================================
--  NexSMS v2 — Additive PostgreSQL Migration (consolidated)
--  Includes all v2 + v2.1 changes in a single file.
--  Safe to run multiple times (all guards use IF NOT EXISTS
--  or DO-block checks).
--  Run after schema.sql / initial setup.
-- ============================================================

-- ── Extend existing ENUM types ──────────────────────────────

-- sms_log_status: add scheduled, cancelled
DO $$ BEGIN
    ALTER TYPE sms_log_status ADD VALUE IF NOT EXISTS 'scheduled';
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
DO $$ BEGIN
    ALTER TYPE sms_log_status ADD VALUE IF NOT EXISTS 'cancelled';
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- transaction_type: add topup, refund
DO $$ BEGIN
    ALTER TYPE transaction_type ADD VALUE IF NOT EXISTS 'topup';
EXCEPTION WHEN duplicate_object THEN NULL; END $$;
DO $$ BEGIN
    ALTER TYPE transaction_type ADD VALUE IF NOT EXISTS 'refund';
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- user_status: add pending (existing: active, suspended, inactive)
DO $$ BEGIN
    ALTER TYPE user_status ADD VALUE IF NOT EXISTS 'pending';
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ── Create new ENUM types ────────────────────────────────────

DO $$ BEGIN
    CREATE TYPE user_theme AS ENUM ('light','dark');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE send_method AS ENUM ('system','api');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE api_key_status AS ENUM ('active','revoked');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE order_status AS ENUM ('pending','paid','cancelled');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE sender_id_status AS ENUM ('active','inactive');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE sender_id_req_status AS ENUM ('pending','approved','rejected');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ── 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
);

-- api_keys
CREATE TABLE IF NOT EXISTS api_keys (
    id          TEXT            NOT NULL,
    user_id     TEXT            NOT NULL,
    name        VARCHAR(80)     NOT NULL,
    key_hash    VARCHAR(64)     NOT NULL,
    key_prefix  VARCHAR(8)      NOT NULL,
    status      api_key_status  NOT NULL DEFAULT 'active',
    last_used   TIMESTAMP       DEFAULT NULL,
    created_at  TIMESTAMP       NOT NULL DEFAULT NOW(),
    expires_at  TIMESTAMP       DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE (key_hash),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_api_keys_user   ON api_keys (user_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_status ON api_keys (status);

-- ── 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);

-- ── Default settings rows ────────────────────────────────────

INSERT INTO settings (key, value) VALUES
    ('system_name',                     'NexSMS'),
    ('system_logo',                     ''),
    ('account_creation_fee_reseller',   '0'),
    ('account_creation_fee_customer',   '0'),
    ('gateway_username',                ''),
    ('gateway_password',                ''),
    ('referral_bonus_sms',              '10'),
    ('referee_bonus_sms',               '5'),
    ('referral_enabled',                'false')
ON CONFLICT (key) DO NOTHING;