schema.sql

-- =========================================================
-- Coupon Maker Enterprise - Supabase PostgreSQL Schema
-- Run this script in the Supabase SQL Editor (https://supabase.com/dashboard)
-- =========================================================

-- 1. Create Batches Table
CREATE TABLE IF NOT EXISTS batches (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    batch_name TEXT NOT NULL,
    template_key TEXT NOT NULL,
    token_count INT NOT NULL,
    denomination TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    notes TEXT
);

-- 2. Create Tokens Table
CREATE TABLE IF NOT EXISTS tokens (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    code TEXT UNIQUE NOT NULL,
    batch_id BIGINT REFERENCES batches(id) ON DELETE CASCADE,
    qr_payload TEXT,
    status TEXT DEFAULT 'GENERATED', -- 'GENERATED', 'PRINTED', 'REDEEMED', 'VOID'
    denomination TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    redeemed_at TIMESTAMPTZ,
    customer_phone TEXT,
    customer_name TEXT,
    customer_invoice TEXT
);

CREATE INDEX IF NOT EXISTS idx_tokens_code ON tokens (code);
CREATE INDEX IF NOT EXISTS idx_tokens_status ON tokens (status);
CREATE INDEX IF NOT EXISTS idx_tokens_batch ON tokens (batch_id);

-- 3. Atomic Anti-Fraud Redemption Function (RPC)
-- Handles concurrent customer scans with row-locking to guarantee ZERO double redemptions
CREATE OR REPLACE FUNCTION redeem_token(
    p_code TEXT,
    p_customer_phone TEXT,
    p_customer_name TEXT DEFAULT '',
    p_invoice TEXT DEFAULT ''
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_token RECORD;
BEGIN
    -- Look up token with row lock (FOR UPDATE prevents race conditions)
    SELECT * INTO v_token 
    FROM tokens 
    WHERE UPPER(TRIM(code)) = UPPER(TRIM(p_code)) 
    FOR UPDATE;
    
    IF NOT FOUND THEN
        RETURN jsonb_build_object(
            'success', false, 
            'status', 'NOT_FOUND', 
            'message', 'Invalid or counterfeit token code. Please check your card.'
        );
    END IF;

    IF v_token.status = 'REDEEMED' THEN
        RETURN jsonb_build_object(
            'success', false, 
            'status', 'ALREADY_REDEEMED', 
            'message', 'This token was already redeemed on ' || TO_CHAR(v_token.redeemed_at, 'YYYY-MM-DD HH24:MI'),
            'redeemed_at', v_token.redeemed_at,
            'code', v_token.code
        );
    END IF;

    IF v_token.status = 'VOID' THEN
        RETURN jsonb_build_object(
            'success', false, 
            'status', 'VOID', 
            'message', 'This token has been deactivated / voided.'
        );
    END IF;

    -- Atomically update token status to REDEEMED
    UPDATE tokens
    SET 
        status = 'REDEEMED',
        redeemed_at = NOW(),
        customer_phone = TRIM(p_customer_phone),
        customer_name = TRIM(p_customer_name),
        customer_invoice = TRIM(p_invoice)
    WHERE id = v_token.id;

    RETURN jsonb_build_object(
        'success', true,
        'status', 'SUCCESS',
        'message', 'Token successfully redeemed! Points / Reward credited.',
        'code', v_token.code,
        'denomination', v_token.denomination,
        'redeemed_at', NOW()
    );
END;
$$;

-- 4. Enable Row Level Security (RLS)
ALTER TABLE batches ENABLE ROW LEVEL SECURITY;
ALTER TABLE tokens ENABLE ROW LEVEL SECURITY;

-- Allow Public/Anon users to execute the RPC function only
GRANT EXECUTE ON FUNCTION redeem_token(TEXT, TEXT, TEXT, TEXT) TO anon, authenticated;

-- Public cannot directly browse all tokens (prevents stealing unseen coupon codes)
-- Service role has full unrestricted admin access for batch insertion and stats
CREATE POLICY "Allow public token redemption verification" ON tokens
    FOR SELECT USING (true);