CREATE TABLE exchange_rates (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    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',
    valid_from timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (base_currency <> quote_currency)
);

CREATE INDEX exchange_rates_pair_valid_from_idx
    ON exchange_rates (base_currency, quote_currency, valid_from DESC);

CREATE INDEX exchange_rates_created_at_idx ON exchange_rates (created_at DESC);

CREATE TABLE fx_quotes (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    from_account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    to_account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    from_currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    to_currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    from_amount_cents bigint NOT NULL CHECK (from_amount_cents > 0),
    to_amount_cents bigint NOT NULL CHECK (to_amount_cents > 0),
    rate_micros bigint NOT NULL CHECK (rate_micros > 0),
    spread_bps integer NOT NULL DEFAULT 0 CHECK (spread_bps BETWEEN 0 AND 2000),
    fee_cents bigint NOT NULL DEFAULT 0 CHECK (fee_cents >= 0),
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'expired', 'canceled')),
    idempotency_key text,
    expires_at timestamptz NOT NULL,
    accepted_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (from_account_id <> to_account_id),
    CHECK (from_currency <> to_currency)
);

CREATE UNIQUE INDEX fx_quotes_user_idempotency_key_uniq
    ON fx_quotes (user_id, idempotency_key)
    WHERE idempotency_key IS NOT NULL;

CREATE INDEX fx_quotes_user_created_at_idx ON fx_quotes (user_id, created_at DESC);
CREATE INDEX fx_quotes_status_expires_at_idx ON fx_quotes (status, expires_at);

CREATE TABLE fx_conversions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    quote_id uuid NOT NULL UNIQUE REFERENCES fx_quotes(id) ON DELETE RESTRICT,
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    from_account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    to_account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    from_wallet_id uuid REFERENCES wallets(id) ON DELETE RESTRICT,
    to_wallet_id uuid REFERENCES wallets(id) ON DELETE RESTRICT,
    from_currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    to_currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    from_amount_cents bigint NOT NULL CHECK (from_amount_cents > 0),
    to_amount_cents bigint NOT NULL CHECK (to_amount_cents > 0),
    rate_micros bigint NOT NULL CHECK (rate_micros > 0),
    spread_bps integer NOT NULL DEFAULT 0 CHECK (spread_bps BETWEEN 0 AND 2000),
    fee_cents bigint NOT NULL DEFAULT 0 CHECK (fee_cents >= 0),
    status text NOT NULL DEFAULT 'completed' CHECK (status IN ('completed', 'reversed')),
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (from_account_id <> to_account_id),
    CHECK (from_currency <> to_currency)
);

CREATE INDEX fx_conversions_user_created_at_idx ON fx_conversions (user_id, created_at DESC);
CREATE INDEX fx_conversions_accounts_idx ON fx_conversions (from_account_id, to_account_id);

INSERT INTO exchange_rates (base_currency, quote_currency, rate_micros, spread_bps, source, valid_from) VALUES
    ('EUR', 'USD', 1080000, 25, 'seed', now()),
    ('USD', 'EUR', 925900, 25, 'seed', now()),
    ('EUR', 'GBP', 850000, 25, 'seed', now()),
    ('GBP', 'EUR', 1176500, 25, 'seed', now()),
    ('EUR', 'CHF', 950000, 25, 'seed', now()),
    ('CHF', 'EUR', 1052600, 25, 'seed', now());