CREATE TABLE provider_balance_snapshots (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL,
    external_account_id text NOT NULL,
    reference_type text NOT NULL DEFAULT 'account' CHECK (reference_type IN ('account', 'wallet', 'provider_account')),
    reference_id text,
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    balance_cents bigint NOT NULL CHECK (balance_cents >= 0),
    as_of timestamptz NOT NULL DEFAULT now(),
    source text NOT NULL DEFAULT 'manual',
    raw_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX provider_balance_snapshots_provider_account_idx
    ON provider_balance_snapshots (provider, external_account_id, currency, as_of DESC);

CREATE INDEX provider_balance_snapshots_created_at_idx
    ON provider_balance_snapshots (created_at DESC);

CREATE TABLE reconciliation_runs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    run_type text NOT NULL DEFAULT 'manual' CHECK (run_type IN ('manual', 'scheduled', 'provider_snapshot')),
    status text NOT NULL DEFAULT 'running' CHECK (status IN ('running', 'completed', 'failed')),
    started_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    total_checks integer NOT NULL DEFAULT 0 CHECK (total_checks >= 0),
    matched_count integer NOT NULL DEFAULT 0 CHECK (matched_count >= 0),
    break_count integer NOT NULL DEFAULT 0 CHECK (break_count >= 0),
    error_message text,
    started_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz
);

CREATE INDEX reconciliation_runs_started_at_idx ON reconciliation_runs (started_at DESC);
CREATE INDEX reconciliation_runs_status_idx ON reconciliation_runs (status);

CREATE TABLE reconciliation_breaks (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    run_id uuid NOT NULL REFERENCES reconciliation_runs(id) ON DELETE RESTRICT,
    break_type text NOT NULL CHECK (break_type IN (
        'journal_entry_unbalanced',
        'account_ledger_mismatch',
        'wallet_available_mismatch',
        'wallet_reserved_mismatch',
        'provider_account_mismatch',
        'provider_snapshot_orphan'
    )),
    severity text NOT NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
    status text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'investigating', 'resolved', 'ignored')),
    reference_type text NOT NULL,
    reference_id text NOT NULL,
    owner_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    expected_amount_cents bigint NOT NULL,
    actual_amount_cents bigint NOT NULL,
    difference_cents bigint NOT NULL,
    description text NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    resolved_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    resolution_note text,
    resolved_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX reconciliation_breaks_run_id_idx ON reconciliation_breaks (run_id);
CREATE INDEX reconciliation_breaks_status_severity_idx ON reconciliation_breaks (status, severity);
CREATE INDEX reconciliation_breaks_reference_idx ON reconciliation_breaks (reference_type, reference_id);
CREATE INDEX reconciliation_breaks_created_at_idx ON reconciliation_breaks (created_at DESC);

CREATE TRIGGER reconciliation_breaks_set_updated_at
BEFORE UPDATE ON reconciliation_breaks
FOR EACH ROW EXECUTE FUNCTION set_updated_at();