-- =====================================================================
-- Break the Locks — SaaS schema
--   Tier, affiliates, purchases, coupons, password resets, questions.
--   Run this once after deploying the new code.
-- =====================================================================

-- ---- users table additions ----
ALTER TABLE users
    ADD COLUMN tier VARCHAR(24) NOT NULL DEFAULT 'free' AFTER progress,
    ADD COLUMN tier_upgraded_at TIMESTAMP NULL DEFAULT NULL,
    ADD COLUMN free_parts_unlocked TINYINT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN affiliate_code VARCHAR(24) DEFAULT NULL,
    ADD COLUMN referred_by_user_id INT UNSIGNED DEFAULT NULL,
    ADD COLUMN referred_by_code VARCHAR(24) DEFAULT NULL,
    ADD COLUMN email_verified TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN reset_token VARCHAR(80) DEFAULT NULL,
    ADD COLUMN reset_expires DATETIME DEFAULT NULL,
    ADD UNIQUE KEY uq_users_affiliate (affiliate_code),
    ADD KEY idx_users_tier (tier),
    ADD KEY idx_users_referred_by (referred_by_user_id);

-- ---- purchases ledger ----
CREATE TABLE IF NOT EXISTS purchases (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    product_key VARCHAR(32) NOT NULL,         -- 'core' | 'plus' | 'upgrade' | 'inner'
    amount_cents INT UNSIGNED NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    whop_payment_id VARCHAR(120) DEFAULT NULL,
    whop_product_id VARCHAR(120) DEFAULT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'paid',   -- 'paid' | 'refunded' | 'chargeback'
    affiliate_user_id INT UNSIGNED DEFAULT NULL,
    affiliate_code VARCHAR(24) DEFAULT NULL,
    affiliate_commission_cents INT UNSIGNED NOT NULL DEFAULT 0,
    coupon_code VARCHAR(40) DEFAULT NULL,
    coupon_discount_cents INT UNSIGNED NOT NULL DEFAULT 0,
    raw_webhook JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_purchases_whop (whop_payment_id),
    KEY idx_purchases_user (user_id),
    KEY idx_purchases_affiliate (affiliate_user_id),
    KEY idx_purchases_created (created_at)
);

-- ---- coupons (admin-managed) ----
CREATE TABLE IF NOT EXISTS coupons (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(40) NOT NULL,
    label VARCHAR(180) DEFAULT NULL,           -- internal description
    percent_off TINYINT UNSIGNED DEFAULT NULL, -- 0-100
    fixed_off_cents INT UNSIGNED DEFAULT NULL,
    grants_free_preview TINYINT(1) NOT NULL DEFAULT 1, -- unlock first N parts free
    valid_until DATE DEFAULT NULL,
    max_uses INT UNSIGNED DEFAULT NULL,        -- NULL = unlimited
    uses INT UNSIGNED NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_by_user_id INT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_coupons_code (code)
);

CREATE TABLE IF NOT EXISTS coupon_redemptions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    coupon_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    purchase_id INT UNSIGNED DEFAULT NULL,
    redeemed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_coupon_user (coupon_id, user_id),
    KEY idx_redempt_user (user_id)
);

-- ---- affiliate payouts (admin marks "paid out") ----
CREATE TABLE IF NOT EXISTS affiliate_payouts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    amount_cents INT UNSIGNED NOT NULL,
    method VARCHAR(40) DEFAULT NULL,           -- 'paypal' | 'venmo' | 'wire' | 'manual'
    note TEXT,
    paid_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_payouts_user (user_id)
);

-- ---- "Ask a Question" + admin replies (in-app messaging) ----
CREATE TABLE IF NOT EXISTS questions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    subject VARCHAR(180) NOT NULL,
    body TEXT NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'open', -- 'open' | 'answered' | 'closed'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_questions_user (user_id),
    KEY idx_questions_status (status)
);

CREATE TABLE IF NOT EXISTS question_messages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    question_id INT UNSIGNED NOT NULL,
    sender VARCHAR(16) NOT NULL,                -- 'user' | 'admin'
    sender_user_id INT UNSIGNED DEFAULT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_qm_question (question_id),
    KEY idx_qm_created (created_at)
);