CREATE TABLE wallet_balance_adjustment_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,
    target_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    wallet_id uuid NOT NULL REFERENCES wallets(id) ON DELETE RESTRICT,
    account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    direction text NOT NULL CHECK (direction IN ('add', 'subtract')),
    amount_cents bigint NOT NULL CHECK (amount_cents > 0),
    currency char(3) NOT NULL REFERENCES currencies(code) ON DELETE RESTRICT,
    reason text NOT NULL,
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'canceled')),
    decision_note text,
    adjustment_id uuid UNIQUE REFERENCES wallet_balance_adjustments(id) ON DELETE RESTRICT,
    idempotency_key text NOT NULL,
    decided_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (requester_admin_user_id, idempotency_key),
    CHECK (reviewer_admin_user_id IS NULL OR reviewer_admin_user_id <> requester_admin_user_id),
    CHECK (
        (status = 'pending' AND reviewer_admin_user_id IS NULL AND adjustment_id IS NULL AND decided_at IS NULL)
        OR (status = 'approved' AND reviewer_admin_user_id IS NOT NULL AND adjustment_id IS NOT NULL AND decided_at IS NOT NULL)
        OR (status = 'rejected' AND reviewer_admin_user_id IS NOT NULL AND adjustment_id IS NULL AND decided_at IS NOT NULL)
        OR (status = 'canceled' AND reviewer_admin_user_id IS NULL AND adjustment_id IS NULL AND decided_at IS NOT NULL)
    )
);

CREATE INDEX wallet_balance_adjustment_requests_status_created_at_idx
    ON wallet_balance_adjustment_requests (status, created_at DESC);

CREATE INDEX wallet_balance_adjustment_requests_target_user_created_at_idx
    ON wallet_balance_adjustment_requests (target_user_id, created_at DESC);

CREATE INDEX wallet_balance_adjustment_requests_wallet_created_at_idx
    ON wallet_balance_adjustment_requests (wallet_id, created_at DESC);

CREATE TRIGGER wallet_balance_adjustment_requests_set_updated_at
BEFORE UPDATE ON wallet_balance_adjustment_requests
FOR EACH ROW EXECUTE FUNCTION set_updated_at();