-- ============================================================
--  NexSMS v2.1 — PostgreSQL Patch Migration
--  Fixes: users.theme default ('dark' → 'light')
--  Adds:  referral_codes table
--  Safe to run multiple times (guarded).
-- ============================================================

-- Fix users.theme default to 'light'
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'users' AND column_name = 'theme'
    ) THEN
        ALTER TABLE users ALTER COLUMN theme SET DEFAULT 'light';
        -- Also update existing rows that were set to 'dark' by default migration
        UPDATE users SET theme = 'light' WHERE theme = 'dark';
    END IF;
END $$;

-- referral_codes: track per-user referral codes and bonus grants
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_code   ON referral_codes (code);
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 referral settings
INSERT INTO settings (key, value) VALUES
    ('referral_bonus_sms',   '10'),
    ('referee_bonus_sms',    '5'),
    ('referral_enabled',     'false')
ON CONFLICT (key) DO NOTHING;