-- Unified sanctioned addresses table (replaces per-currency tables from the Go version).
CREATE TABLE IF NOT EXISTS sanctioned_addresses (
    currency    TEXT        NOT NULL,
    address     TEXT        NOT NULL,
    source_type TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (currency, address)
);

CREATE INDEX IF NOT EXISTS idx_sanctioned_addresses_currency
    ON sanctioned_addresses (currency);

CREATE INDEX IF NOT EXISTS idx_sanctioned_addresses_address
    ON sanctioned_addresses (address);

-- API keys (matches existing Go schema).
CREATE TABLE IF NOT EXISTS api_keys (
    api_key_address       TEXT    PRIMARY KEY,
    api_key_active_status BOOLEAN NOT NULL DEFAULT TRUE,
    user_id               TEXT    NOT NULL
);

-- Sanctioned countries/jurisdictions derived from OFAC program codes.
CREATE TABLE IF NOT EXISTS sanctioned_countries (
    code        TEXT        NOT NULL,
    name        TEXT        NOT NULL,
    program     TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (code, program)
);

-- Request audit log (matches existing Go schema).
CREATE TABLE IF NOT EXISTS user_logs (
    id                   BIGSERIAL   PRIMARY KEY,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    query_user_id        TEXT        NOT NULL DEFAULT '',
    query_api_key        TEXT        NOT NULL DEFAULT '',
    query_request        TEXT        NOT NULL DEFAULT '',
    query_response       TEXT        NOT NULL DEFAULT '',
    query_error_count    INT         NOT NULL DEFAULT 0,
    query_success_status BOOLEAN     NOT NULL DEFAULT FALSE
);
