CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS accounts (
    account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owner_name TEXT NOT NULL,
    jurisdiction TEXT NOT NULL,
    account_type TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    is_beneficial_owner_verified BOOLEAN NOT NULL DEFAULT FALSE,
    opening_balance_usd NUMERIC(18,2) NOT NULL DEFAULT 0,
    current_balance_usd NUMERIC(18,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS transactions (
    tx_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    from_account UUID NOT NULL REFERENCES accounts(account_id),
    to_account UUID NOT NULL REFERENCES accounts(account_id),
    amount_usd NUMERIC(18,2) NOT NULL,
    fx_rate NUMERIC(10,6) NOT NULL DEFAULT 1,
    tx_timestamp TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    memo TEXT,
    is_fraud BOOLEAN NOT NULL DEFAULT FALSE,
    fraud_type TEXT,
    episode_id UUID NOT NULL
);

CREATE OR REPLACE VIEW transactions_visible AS
SELECT
    tx_id,
    from_account,
    to_account,
    amount_usd,
    fx_rate,
    tx_timestamp,
    memo,
    episode_id
FROM transactions;

CREATE TABLE IF NOT EXISTS audit_log (
    log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    episode_id UUID NOT NULL,
    agent_type TEXT NOT NULL,
    action TEXT NOT NULL,
    payload JSONB NOT NULL,
    logged_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS aml_violations (
    violation_code TEXT PRIMARY KEY,
    description TEXT NOT NULL,
    threshold JSONB NOT NULL,
    severity INTEGER NOT NULL CHECK (severity BETWEEN 1 AND 5)
);

CREATE TABLE IF NOT EXISTS injected_observations (
    obs_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    episode_id UUID NOT NULL,
    content TEXT NOT NULL,
    target_account UUID REFERENCES accounts(account_id),
    injected_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS episode_ground_truth (
    episode_id UUID PRIMARY KEY,
    root_fraud_tx_id UUID NOT NULL REFERENCES transactions(tx_id),
    fraud_type TEXT NOT NULL,
    fraud_archetype TEXT NOT NULL,
    misleading_injection_active BOOLEAN NOT NULL DEFAULT FALSE,
    misleading_target_account UUID REFERENCES accounts(account_id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS episode_results (
    episode_id UUID PRIMARY KEY,
    fraud_archetype TEXT NOT NULL,
    fraud_discovered BOOLEAN NOT NULL,
    reward NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS baseline_accounts (
    account_id UUID PRIMARY KEY,
    owner_name TEXT NOT NULL,
    jurisdiction TEXT NOT NULL,
    account_type TEXT NOT NULL,
    is_beneficial_owner_verified BOOLEAN NOT NULL,
    opening_balance_usd NUMERIC(18,2) NOT NULL
);

CREATE OR REPLACE FUNCTION restore_episode_snapshot() RETURNS void AS $$
BEGIN
    -- Acquire a transaction-scoped advisory lock so concurrent /reset calls
    -- queue up and execute serially instead of racing on DELETE + INSERT.
    -- The lock is automatically released when this transaction commits/rolls back.
    PERFORM pg_advisory_xact_lock(8675309);

    -- Delete in proper order: child tables first, then parent tables
    -- transactions has FK to accounts, so delete transactions first
    DELETE FROM injected_observations;
    DELETE FROM audit_log;
    DELETE FROM episode_ground_truth;
    DELETE FROM episode_results;
    DELETE FROM transactions;
    DELETE FROM accounts;

    -- ON CONFLICT DO NOTHING as a defense-in-depth safety net
    INSERT INTO accounts (
        account_id,
        owner_name,
        jurisdiction,
        account_type,
        is_beneficial_owner_verified,
        opening_balance_usd,
        current_balance_usd
    )
    SELECT
        b.account_id,
        b.owner_name,
        b.jurisdiction,
        b.account_type,
        b.is_beneficial_owner_verified,
        b.opening_balance_usd,
        b.opening_balance_usd
    FROM baseline_accounts b
    ON CONFLICT (account_id) DO NOTHING;
END;
$$ LANGUAGE plpgsql;