-- Active: 1772601349048@@metro.proxy.rlwy.net@13239@railway
-- API key IP controls: per-key whitelists, blacklists, seen-IP aggregation, virgin mode,
-- plus global whitelist/blacklist overrides that take precedence.
--
-- Idempotent; safe to run repeatedly.

ALTER TABLE api_keys
    ADD COLUMN IF NOT EXISTS virgin_mode boolean NOT NULL DEFAULT false;

ALTER TABLE api_keys
    ADD COLUMN IF NOT EXISTS virgin_resolved boolean NOT NULL DEFAULT false;

ALTER TABLE api_keys
    ADD COLUMN IF NOT EXISTS virgin_resolved_at timestamptz;

ALTER TABLE api_keys
    ADD COLUMN IF NOT EXISTS virgin_until_n_requests integer NOT NULL DEFAULT 0;

ALTER TABLE api_keys
    ADD COLUMN IF NOT EXISTS virgin_request_count integer NOT NULL DEFAULT 0;

ALTER TABLE api_keys
    ADD COLUMN IF NOT EXISTS max_whitelist_ips integer NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS api_key_ip_whitelist (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_ip_whitelist_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    api_key_id bigint NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    addr cidr NOT NULL,
    label text,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (api_key_id, addr)
);
CREATE INDEX IF NOT EXISTS idx_api_key_ip_whitelist_key ON api_key_ip_whitelist (api_key_id);

CREATE TABLE IF NOT EXISTS api_key_ip_blacklist (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_ip_blacklist_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    api_key_id bigint NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    addr cidr NOT NULL,
    label text,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (api_key_id, addr)
);
CREATE INDEX IF NOT EXISTS idx_api_key_ip_blacklist_key ON api_key_ip_blacklist (api_key_id);

CREATE TABLE IF NOT EXISTS api_key_ip_seen (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_ip_seen_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    api_key_id bigint NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    addr inet NOT NULL,
    first_seen_at timestamptz NOT NULL DEFAULT now(),
    last_seen_at timestamptz NOT NULL DEFAULT now(),
    hit_count bigint NOT NULL DEFAULT 1,
    locked_in boolean NOT NULL DEFAULT false,
    UNIQUE (api_key_id, addr)
);
CREATE INDEX IF NOT EXISTS idx_api_key_ip_seen_key ON api_key_ip_seen (api_key_id);
CREATE INDEX IF NOT EXISTS idx_api_key_ip_seen_last_seen ON api_key_ip_seen (api_key_id, last_seen_at DESC);

CREATE TABLE IF NOT EXISTS api_key_ip_global_whitelist (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_ip_global_whitelist_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    addr cidr NOT NULL,
    client_name text,
    label text,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (addr, client_name)
);
CREATE INDEX IF NOT EXISTS idx_api_key_ip_global_whitelist_client ON api_key_ip_global_whitelist (client_name);

CREATE TABLE IF NOT EXISTS api_key_ip_global_blacklist (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    api_key_ip_global_blacklist_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    addr cidr NOT NULL,
    client_name text,
    label text,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (addr, client_name)
);
CREATE INDEX IF NOT EXISTS idx_api_key_ip_global_blacklist_client ON api_key_ip_global_blacklist (client_name);
