-- =============================================================================
-- Dragonpay Test Suite - Supabase Database Schema
-- Run this script in the Supabase SQL Editor (Dashboard > SQL Editor > New Query)
-- =============================================================================

-- 1. Table for Incoming Dragonpay Postbacks / Webhooks
CREATE TABLE IF NOT EXISTS public.dragonpay_postbacks (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    environment VARCHAR(20) DEFAULT 'UAT',
    result VARCHAR(100),
    method VARCHAR(50),
    failure_reason TEXT,
    txnid VARCHAR(100),
    refno VARCHAR(100),
    procid VARCHAR(50),
    status VARCHAR(10),
    message TEXT,
    amount NUMERIC(15, 2) DEFAULT 0.00,
    param1 TEXT,
    param2 TEXT,
    settledate VARCHAR(100),
    digest TEXT,
    signature TEXT,
    signatures TEXT,
    canonical TEXT,
    raw_payload JSONB
);

-- Indexes for fast searching and filtering
CREATE INDEX IF NOT EXISTS idx_dp_postbacks_txnid ON public.dragonpay_postbacks(txnid);
CREATE INDEX IF NOT EXISTS idx_dp_postbacks_refno ON public.dragonpay_postbacks(refno);
CREATE INDEX IF NOT EXISTS idx_dp_postbacks_status ON public.dragonpay_postbacks(status);
CREATE INDEX IF NOT EXISTS idx_dp_postbacks_created_at ON public.dragonpay_postbacks(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_dp_postbacks_env ON public.dragonpay_postbacks(environment);

-- 2. Table for Dragonpay Customer Returns (Redirects)
CREATE TABLE IF NOT EXISTS public.dragonpay_returns (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    environment TEXT DEFAULT 'UAT',
    txnid TEXT,
    refno TEXT,
    status TEXT,
    status_label TEXT,
    verification_result TEXT,
    verification_method TEXT,
    message TEXT,
    amount NUMERIC(15, 2) DEFAULT 0.00,
    param1 TEXT,
    param2 TEXT,
    raw_payload JSONB
);

-- Indexes for returns
CREATE INDEX IF NOT EXISTS idx_dp_returns_txnid ON public.dragonpay_returns(txnid);
CREATE INDEX IF NOT EXISTS idx_dp_returns_refno ON public.dragonpay_returns(refno);
CREATE INDEX IF NOT EXISTS idx_dp_returns_status ON public.dragonpay_returns(status);
CREATE INDEX IF NOT EXISTS idx_dp_returns_created_at ON public.dragonpay_returns(created_at DESC);

-- =============================================================================
-- Row Level Security (RLS) Configuration
-- =============================================================================
ALTER TABLE public.dragonpay_postbacks ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.dragonpay_returns ENABLE ROW LEVEL SECURITY;

-- Allow anonymous / authenticated read, insert, update, and delete access
-- (Appropriate for developer testing toolkits and webhook endpoints)
DROP POLICY IF EXISTS "Allow all access to dragonpay_postbacks" ON public.dragonpay_postbacks;
CREATE POLICY "Allow all access to dragonpay_postbacks" 
ON public.dragonpay_postbacks 
FOR ALL 
USING (true) 
WITH CHECK (true);

DROP POLICY IF EXISTS "Allow all access to dragonpay_returns" ON public.dragonpay_returns;
CREATE POLICY "Allow all access to dragonpay_returns" 
ON public.dragonpay_returns 
FOR ALL 
USING (true) 
WITH CHECK (true);