ALTER TABLE exchange_rates
    ADD COLUMN provider text NOT NULL DEFAULT 'manual',
    ADD COLUMN source_reference text NOT NULL DEFAULT '',
    ADD COLUMN source_timestamp timestamptz,
    ADD COLUMN received_at timestamptz NOT NULL DEFAULT now(),
    ADD COLUMN stale_after timestamptz,
    ADD COLUMN is_fallback boolean NOT NULL DEFAULT false;

CREATE INDEX exchange_rates_stale_after_idx ON exchange_rates (stale_after)
    WHERE stale_after IS NOT NULL;

ALTER TABLE fx_quotes
    ADD COLUMN exchange_rate_id uuid REFERENCES exchange_rates(id) ON DELETE RESTRICT,
    ADD COLUMN market_rate_micros bigint CHECK (market_rate_micros IS NULL OR market_rate_micros > 0),
    ADD COLUMN rate_source text NOT NULL DEFAULT '',
    ADD COLUMN rate_provider text NOT NULL DEFAULT '',
    ADD COLUMN rate_source_timestamp timestamptz,
    ADD COLUMN rate_stale_after timestamptz;

ALTER TABLE fx_conversions
    ADD COLUMN exchange_rate_id uuid REFERENCES exchange_rates(id) ON DELETE RESTRICT,
    ADD COLUMN market_rate_micros bigint CHECK (market_rate_micros IS NULL OR market_rate_micros > 0);

CREATE TABLE fx_rate_change_requests (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    requester_admin_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    reviewer_admin_user_id uuid REFERENCES users(id) ON DELETE RESTRICT,
    base_currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    quote_currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    rate_micros bigint NOT NULL CHECK (rate_micros > 0 AND rate_micros <= 1000000000000),
    spread_bps integer NOT NULL DEFAULT 0 CHECK (spread_bps BETWEEN 0 AND 2000),
    source text NOT NULL DEFAULT 'manual',
    provider text NOT NULL DEFAULT 'manual',
    source_reference text NOT NULL DEFAULT '',
    source_timestamp timestamptz,
    valid_from timestamptz NOT NULL DEFAULT now(),
    stale_after timestamptz,
    is_fallback boolean NOT NULL DEFAULT false,
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'canceled')),
    decision_note text NOT NULL DEFAULT '',
    exchange_rate_id uuid REFERENCES exchange_rates(id) ON DELETE RESTRICT,
    idempotency_key text,
    decided_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (base_currency <> quote_currency),
    CHECK (stale_after IS NULL OR stale_after > valid_from)
);

CREATE UNIQUE INDEX fx_rate_change_requests_requester_idempotency_uniq
    ON fx_rate_change_requests (requester_admin_user_id, idempotency_key)
    WHERE idempotency_key IS NOT NULL;

CREATE INDEX fx_rate_change_requests_status_created_at_idx
    ON fx_rate_change_requests (status, created_at DESC);

CREATE INDEX fx_rate_change_requests_pair_idx
    ON fx_rate_change_requests (base_currency, quote_currency, created_at DESC);