ALTER TABLE reconciliation_runs
    DROP CONSTRAINT IF EXISTS reconciliation_runs_run_type_check;

ALTER TABLE reconciliation_runs
    ADD CONSTRAINT reconciliation_runs_run_type_check
    CHECK (run_type IN ('manual', 'scheduled', 'provider_snapshot', 'daily'));

ALTER TABLE reconciliation_breaks
    DROP CONSTRAINT IF EXISTS reconciliation_breaks_break_type_check;

ALTER TABLE reconciliation_breaks
    ADD CONSTRAINT reconciliation_breaks_break_type_check
    CHECK (break_type IN (
        'journal_entry_unbalanced',
        'account_ledger_mismatch',
        'wallet_available_mismatch',
        'wallet_reserved_mismatch',
        'provider_account_mismatch',
        'provider_ledger_mismatch',
        'provider_snapshot_orphan'
    ));

CREATE TABLE currency_rounding_policies (
    currency char(3) PRIMARY KEY REFERENCES currencies(code) ON DELETE RESTRICT,
    minor_unit smallint NOT NULL CHECK (minor_unit BETWEEN 0 AND 4),
    fx_rounding_mode text NOT NULL DEFAULT 'half_up' CHECK (fx_rounding_mode IN ('floor', 'half_up', 'half_even')),
    cash_rounding_increment_minor_units integer NOT NULL DEFAULT 1 CHECK (cash_rounding_increment_minor_units > 0),
    policy_source text NOT NULL DEFAULT 'system',
    effective_from timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO currency_rounding_policies (
    currency, minor_unit, fx_rounding_mode, cash_rounding_increment_minor_units, policy_source
)
SELECT code, minor_unit, 'half_up', 1, 'seeded_from_currencies'
FROM currencies
ON CONFLICT (currency) DO NOTHING;

CREATE TRIGGER currency_rounding_policies_set_updated_at
BEFORE UPDATE ON currency_rounding_policies
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE ledger_reversal_policies (
    source_type text PRIMARY KEY,
    movement_type text NOT NULL,
    product_state_required boolean NOT NULL DEFAULT true,
    allowed_reversal_types text[] NOT NULL DEFAULT ARRAY['correction', 'provider_error', 'customer_refund', 'fraud', 'operational'],
    notes text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO ledger_reversal_policies (
    source_type, movement_type, product_state_required, notes
) VALUES
    ('transfer', 'internal_or_outbound_transfer', true, 'Reverse through transfer/payment state workflow first, then post the ledger reversal.'),
    ('sepa_settlement', 'outbound_sepa_settlement', true, 'Use provider failure/refund workflow where possible.'),
    ('sepa_settlement_refund', 'outbound_sepa_refund', true, 'Reverse only for erroneous refunds with product balance correction.'),
    ('payment_review_reject_refund', 'manual_review_refund', true, 'Reverse only when a rejected review refund was erroneous.'),
    ('inbound_payment', 'inbound_sepa_payment', true, 'Reverse with provider return/recall workflow before ledger reversal.'),
    ('wallet_balance_adjustment', 'admin_wallet_adjustment', true, 'Create an opposite approved wallet adjustment before ledger reversal.'),
    ('savings_goal_transaction', 'savings_goal_movement', true, 'Use savings goal withdrawal/close-return workflow before ledger reversal.'),
    ('fx_conversion', 'fx_conversion', true, 'Create compensating FX conversion or treasury correction before ledger reversal.')
ON CONFLICT (source_type) DO UPDATE
SET movement_type = EXCLUDED.movement_type,
    product_state_required = EXCLUDED.product_state_required,
    allowed_reversal_types = EXCLUDED.allowed_reversal_types,
    notes = EXCLUDED.notes,
    updated_at = now();

CREATE TRIGGER ledger_reversal_policies_set_updated_at
BEFORE UPDATE ON ledger_reversal_policies
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TABLE ledger_journal_reversals (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    original_journal_entry_id uuid NOT NULL UNIQUE REFERENCES ledger_journal_entries(id) ON DELETE RESTRICT,
    reversal_journal_entry_id uuid UNIQUE REFERENCES ledger_journal_entries(id) ON DELETE RESTRICT,
    requested_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    source_type text NOT NULL,
    movement_type text NOT NULL,
    reversal_type text NOT NULL CHECK (reversal_type IN ('correction', 'provider_error', 'customer_refund', 'fraud', 'operational')),
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'failed')),
    reason text NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz
);

CREATE INDEX ledger_journal_reversals_created_at_idx
    ON ledger_journal_reversals (created_at DESC);

CREATE INDEX ledger_journal_reversals_source_type_idx
    ON ledger_journal_reversals (source_type, created_at DESC);

CREATE TABLE end_of_day_balance_snapshots (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    snapshot_date date NOT NULL,
    subject_type text NOT NULL CHECK (subject_type IN ('account', 'wallet', 'provider_account', 'ledger_account')),
    subject_id text NOT NULL,
    owner_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    provider text,
    external_account_id text,
    ledger_account_id uuid REFERENCES ledger_accounts(id) ON DELETE SET NULL,
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    balance_cents bigint NOT NULL,
    available_balance_cents bigint,
    reserved_balance_cents bigint,
    debit_balance_cents bigint NOT NULL DEFAULT 0 CHECK (debit_balance_cents >= 0),
    credit_balance_cents bigint NOT NULL DEFAULT 0 CHECK (credit_balance_cents >= 0),
    source text NOT NULL DEFAULT 'daily_reconciliation',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (snapshot_date, subject_type, subject_id, currency)
);

CREATE INDEX end_of_day_balance_snapshots_date_type_idx
    ON end_of_day_balance_snapshots (snapshot_date DESC, subject_type);

CREATE INDEX end_of_day_balance_snapshots_owner_idx
    ON end_of_day_balance_snapshots (owner_user_id, snapshot_date DESC);

CREATE INDEX end_of_day_balance_snapshots_ledger_account_idx
    ON end_of_day_balance_snapshots (ledger_account_id, snapshot_date DESC);

CREATE INDEX ledger_journal_lines_currency_created_at_idx
    ON ledger_journal_lines (currency, created_at DESC);

CREATE INDEX ledger_journal_entries_source_created_at_idx
    ON ledger_journal_entries (source_type, created_at DESC);