-- Referral codes and survey responses for post-completion flow

-- Referral codes (one per customer)
CREATE TABLE IF NOT EXISTS referral_codes (
    id SERIAL PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,            -- e.g. REF-MARKADAMS
    customer_email TEXT NOT NULL,
    customer_name TEXT,
    credit_cents INTEGER DEFAULT 2500,     -- $25 credit
    times_used INTEGER DEFAULT 0,
    total_earned_cents INTEGER DEFAULT 0,
    balance_cents INTEGER DEFAULT 0,       -- unused credit
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_referral_codes_email ON referral_codes(customer_email);
CREATE INDEX IF NOT EXISTS idx_referral_codes_code ON referral_codes(code);

-- Referral usage tracking
CREATE TABLE IF NOT EXISTS referral_uses (
    id SERIAL PRIMARY KEY,
    referral_code TEXT NOT NULL REFERENCES referral_codes(code),
    referred_order TEXT NOT NULL,          -- order_number of the new customer's order
    referred_email TEXT NOT NULL,
    credit_cents INTEGER DEFAULT 2500,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Exit survey responses
CREATE TABLE IF NOT EXISTS exit_surveys (
    id SERIAL PRIMARY KEY,
    order_number TEXT NOT NULL,
    customer_email TEXT NOT NULL,
    rating INTEGER CHECK (rating BETWEEN 1 AND 5),
    feedback TEXT,
    would_recommend BOOLEAN,
    review_link_clicked BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_exit_surveys_order ON exit_surveys(order_number);

-- Track which completion emails have been sent (prevent duplicates)
ALTER TABLE compliance_orders
    ADD COLUMN IF NOT EXISTS completion_email_sent_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS followup_email_sent_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS referral_code TEXT;