-- ============================================================
-- Migration 017: Reconciliation EXIT Backfill Audit
-- Date: 2026-02-21
-- Purpose:
--   1) Persist non-destructive audit trail for reconciliation EXIT backfill.
--   2) Support batch-level rollback workflows without deleting rows.
-- ============================================================

CREATE TABLE IF NOT EXISTS reconciliation_backfill_audit (
    id bigserial PRIMARY KEY,
    batch_id text NOT NULL,
    profile_id uuid NOT NULL REFERENCES trade_profiles(id) ON DELETE CASCADE,
    symbol text NOT NULL,
    trade_id text NOT NULL,
    exchange_order_id text,
    exchange_client_order_id text,
    backfill_order_id text,
    filled_qty numeric,
    filled_price numeric,
    filled_at timestamptz,
    dry_run boolean NOT NULL DEFAULT true,
    decision text NOT NULL,
    reason text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    applied_at timestamptz,
    reverted_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_recon_backfill_audit_batch
    ON reconciliation_backfill_audit (batch_id);

CREATE INDEX IF NOT EXISTS idx_recon_backfill_audit_profile_symbol_trade
    ON reconciliation_backfill_audit (profile_id, symbol, trade_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_recon_backfill_audit_decision
    ON reconciliation_backfill_audit (decision, created_at DESC);

ALTER TABLE reconciliation_backfill_audit ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Allow service role reconciliation backfill audit access"
    ON reconciliation_backfill_audit;
CREATE POLICY "Allow service role reconciliation backfill audit access"
    ON reconciliation_backfill_audit
    FOR ALL
    USING (auth.role() = 'service_role')
    WITH CHECK (auth.role() = 'service_role');
