CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS schema_migrations (
    version BIGINT PRIMARY KEY,
    name TEXT NOT NULL,
    checksum TEXT NOT NULL,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS control_state (
    singleton BOOLEAN PRIMARY KEY DEFAULT TRUE CHECK (singleton),
    generation BIGINT NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO control_state (singleton) VALUES (TRUE) ON CONFLICT DO NOTHING;

CREATE TABLE IF NOT EXISTS tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    slug TEXT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, slug),
    UNIQUE (id, tenant_id)
);

CREATE TABLE IF NOT EXISTS api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    project_id UUID NOT NULL,
    name TEXT NOT NULL,
    key_prefix TEXT NOT NULL,
    key_suffix TEXT NOT NULL DEFAULT '',
    key_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(key_hash) = 32),
    scopes JSONB NOT NULL DEFAULT '["inference"]'::jsonb,
    status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled', 'revoked')),
    last_used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    revoked_at TIMESTAMPTZ,
    FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE CASCADE
);
ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS monthly_spend_micros BIGINT NOT NULL DEFAULT 0 CHECK (monthly_spend_micros >= 0);
ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS daily_spend_micros BIGINT NOT NULL DEFAULT 0 CHECK (daily_spend_micros >= 0);
ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS requests_per_minute BIGINT NOT NULL DEFAULT 0 CHECK (requests_per_minute >= 0);
ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS tokens_per_minute BIGINT NOT NULL DEFAULT 0 CHECK (tokens_per_minute >= 0);
ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS expires_at TIMESTAMPTZ;
ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS tags JSONB NOT NULL DEFAULT '[]'::jsonb;
ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS disabled_at TIMESTAMPTZ;
ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS rotated_from_id UUID REFERENCES api_keys(id) ON DELETE SET NULL;
ALTER TABLE api_keys ADD COLUMN IF NOT EXISTS key_suffix TEXT NOT NULL DEFAULT '';
ALTER TABLE api_keys DROP CONSTRAINT IF EXISTS api_keys_key_suffix_check;
ALTER TABLE api_keys ADD CONSTRAINT api_keys_key_suffix_check CHECK (key_suffix = '' OR key_suffix ~ '^[A-Za-z0-9_-]{6}$');
ALTER TABLE api_keys DROP CONSTRAINT IF EXISTS api_keys_status_check;
ALTER TABLE api_keys ADD CONSTRAINT api_keys_status_check CHECK (status IN ('active', 'disabled', 'revoked'));

CREATE TABLE IF NOT EXISTS providers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug TEXT NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9][a-z0-9-]{1,62}[a-z0-9]$'),
    name TEXT NOT NULL UNIQUE,
    protocol TEXT NOT NULL CHECK (protocol IN ('openai', 'anthropic')),
    wire_api TEXT NOT NULL DEFAULT 'chat_completions' CHECK (wire_api IN ('chat_completions', 'responses', 'embeddings', 'messages')),
    base_url TEXT NOT NULL,
    api_key_ciphertext BYTEA NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE providers ADD COLUMN IF NOT EXISTS slug TEXT;
WITH normalized AS (
    SELECT id,
           left(trim(both '-' from regexp_replace(lower(name), '[^a-z0-9]+', '-', 'g')), 64) AS base
    FROM providers
), ranked AS (
    SELECT id, base, count(*) OVER (PARTITION BY base) AS base_count
    FROM normalized
)
UPDATE providers p
SET slug = CASE
    WHEN length(r.base) BETWEEN 3 AND 64
         AND r.base ~ '^[a-z0-9][a-z0-9-]{1,62}[a-z0-9]$'
         AND r.base_count = 1 THEN r.base
    ELSE 'provider-' || left(replace(p.id::text, '-', ''), 12)
END
FROM ranked r
WHERE p.id = r.id AND (p.slug IS NULL OR p.slug = '');
ALTER TABLE providers ALTER COLUMN slug SET NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS providers_slug_unique_idx ON providers (slug);
ALTER TABLE providers DROP CONSTRAINT IF EXISTS providers_slug_check;
ALTER TABLE providers ADD CONSTRAINT providers_slug_check CHECK (slug ~ '^[a-z0-9][a-z0-9-]{1,62}[a-z0-9]$');
ALTER TABLE providers ADD COLUMN IF NOT EXISTS wire_api TEXT NOT NULL DEFAULT 'chat_completions';
UPDATE providers SET wire_api = 'messages' WHERE protocol = 'anthropic' AND wire_api = 'chat_completions';
ALTER TABLE providers DROP CONSTRAINT IF EXISTS providers_wire_api_check;
ALTER TABLE providers ADD CONSTRAINT providers_wire_api_check CHECK (wire_api IN ('chat_completions', 'responses', 'embeddings', 'messages'));
ALTER TABLE providers DROP CONSTRAINT IF EXISTS providers_protocol_wire_api_check;
ALTER TABLE providers ADD CONSTRAINT providers_protocol_wire_api_check CHECK (
    (protocol = 'openai' AND wire_api IN ('chat_completions', 'responses', 'embeddings')) OR
    (protocol = 'anthropic' AND wire_api = 'messages')
);

CREATE TABLE IF NOT EXISTS models (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    public_id TEXT NOT NULL UNIQUE,
    owned_by TEXT NOT NULL DEFAULT '',
    input_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (input_price_micros_per_million >= 0),
    output_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (output_price_micros_per_million >= 0),
    cache_read_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_read_price_micros_per_million >= 0),
    cache_write_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_write_price_micros_per_million >= 0),
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE models ADD COLUMN IF NOT EXISTS input_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (input_price_micros_per_million >= 0);
ALTER TABLE models ADD COLUMN IF NOT EXISTS output_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (output_price_micros_per_million >= 0);
ALTER TABLE models ADD COLUMN IF NOT EXISTS cache_read_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_read_price_micros_per_million >= 0);
ALTER TABLE models ADD COLUMN IF NOT EXISTS cache_write_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_write_price_micros_per_million >= 0);
ALTER TABLE models ADD COLUMN IF NOT EXISTS display_name TEXT NOT NULL DEFAULT '';
ALTER TABLE models ADD COLUMN IF NOT EXISTS description TEXT NOT NULL DEFAULT '';
ALTER TABLE models ADD COLUMN IF NOT EXISTS input_modalities JSONB NOT NULL DEFAULT '["text"]'::jsonb;
ALTER TABLE models ADD COLUMN IF NOT EXISTS output_modalities JSONB NOT NULL DEFAULT '["text"]'::jsonb;
ALTER TABLE models ADD COLUMN IF NOT EXISTS context_window BIGINT NOT NULL DEFAULT 0 CHECK (context_window >= 0);
ALTER TABLE models ADD COLUMN IF NOT EXISTS max_output_tokens BIGINT NOT NULL DEFAULT 0 CHECK (max_output_tokens >= 0);
ALTER TABLE models ADD COLUMN IF NOT EXISTS capabilities JSONB NOT NULL DEFAULT '["chat","streaming"]'::jsonb;
ALTER TABLE models ADD COLUMN IF NOT EXISTS regions JSONB NOT NULL DEFAULT '[]'::jsonb;
ALTER TABLE models ADD COLUMN IF NOT EXISTS lifecycle TEXT NOT NULL DEFAULT 'active';
ALTER TABLE models ADD COLUMN IF NOT EXISTS released_at TIMESTAMPTZ;
ALTER TABLE models ADD COLUMN IF NOT EXISTS deprecated_at TIMESTAMPTZ;
ALTER TABLE models ADD COLUMN IF NOT EXISTS retired_at TIMESTAMPTZ;
ALTER TABLE models ADD COLUMN IF NOT EXISTS replacement_model TEXT;
ALTER TABLE models DROP CONSTRAINT IF EXISTS models_lifecycle_check;
ALTER TABLE models ADD CONSTRAINT models_lifecycle_check CHECK (lifecycle IN ('preview','active','deprecated','retired'));

CREATE TABLE IF NOT EXISTS model_price_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
    version INTEGER NOT NULL CHECK (version > 0),
    currency TEXT NOT NULL CHECK (currency = lower(currency) AND length(currency) = 3),
    input_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (input_price_micros_per_million >= 0),
    output_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (output_price_micros_per_million >= 0),
    cache_read_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_read_price_micros_per_million >= 0),
    cache_write_price_micros_per_million BIGINT NOT NULL DEFAULT 0 CHECK (cache_write_price_micros_per_million >= 0),
    effective_from TIMESTAMPTZ NOT NULL DEFAULT now(),
    effective_to TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (model_id, version),
    CHECK (effective_to IS NULL OR effective_to > effective_from)
);
CREATE UNIQUE INDEX IF NOT EXISTS model_price_versions_one_open_idx
    ON model_price_versions (model_id) WHERE effective_to IS NULL;
CREATE INDEX IF NOT EXISTS model_price_versions_effective_idx
    ON model_price_versions (model_id, effective_from DESC);

INSERT INTO model_price_versions (
    model_id, version, currency, input_price_micros_per_million,
    output_price_micros_per_million, cache_read_price_micros_per_million,
    cache_write_price_micros_per_million, effective_from)
SELECT id, 1, 'usd', input_price_micros_per_million, output_price_micros_per_million,
       cache_read_price_micros_per_million, cache_write_price_micros_per_million, created_at
FROM models
ON CONFLICT (model_id, version) DO NOTHING;

CREATE TABLE IF NOT EXISTS model_aliases (
    alias TEXT PRIMARY KEY,
    model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
    deprecated BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS model_aliases_model_idx ON model_aliases (model_id);

CREATE TABLE IF NOT EXISTS model_tenant_allowlist (
    model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (model_id, tenant_id)
);

CREATE TABLE IF NOT EXISTS api_key_model_allowlist (
    api_key_id UUID NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
    model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (api_key_id, model_id)
);

-- Per-key restrictions are separate from the platform model allowlist above:
-- an empty set means that the key may use every model visible to its tenant.
CREATE TABLE IF NOT EXISTS api_key_model_restrictions (
	api_key_id UUID NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
	model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
	created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
	PRIMARY KEY (api_key_id, model_id)
);

CREATE TABLE IF NOT EXISTS model_routes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
    provider_id UUID NOT NULL REFERENCES providers(id) ON DELETE RESTRICT,
    upstream_model TEXT NOT NULL,
    priority INTEGER NOT NULL DEFAULT 0 CHECK (priority >= 0),
    weight INTEGER NOT NULL DEFAULT 1 CHECK (weight BETWEEN 1 AND 100),
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (model_id, provider_id, upstream_model)
);

CREATE INDEX IF NOT EXISTS api_keys_active_hash_idx ON api_keys (key_hash) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS api_keys_rotated_from_idx ON api_keys (rotated_from_id) WHERE rotated_from_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS projects_tenant_idx ON projects (tenant_id);
CREATE INDEX IF NOT EXISTS model_routes_model_idx ON model_routes (model_id) WHERE enabled;
CREATE INDEX IF NOT EXISTS model_routes_provider_idx ON model_routes (provider_id) WHERE enabled;

CREATE TABLE IF NOT EXISTS tenant_wallets (
    tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
    currency TEXT NOT NULL CHECK (currency = lower(currency) AND length(currency) = 3),
    balance_micros BIGINT NOT NULL DEFAULT 0 CHECK (balance_micros >= 0),
    reserved_micros BIGINT NOT NULL DEFAULT 0 CHECK (reserved_micros >= 0 AND reserved_micros <= balance_micros),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Tenant-scoped developer and billing preferences. These values are control
-- plane data, but are intentionally not loaded into the inference snapshot.
CREATE TABLE IF NOT EXISTS tenant_preferences (
    tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
    default_model TEXT REFERENCES models(public_id) ON DELETE SET NULL,
    fallback_model TEXT REFERENCES models(public_id) ON DELETE SET NULL,
    low_balance_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    low_balance_threshold_micros BIGINT NOT NULL DEFAULT 5000000 CHECK (low_balance_threshold_micros >= 0),
	spend_anomaly_enabled BOOLEAN NOT NULL DEFAULT TRUE,
	spend_anomaly_multiplier BIGINT,
	spend_anomaly_min_micros BIGINT,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CHECK (default_model IS NULL OR fallback_model IS NULL OR default_model <> fallback_model)
);
ALTER TABLE tenant_preferences ADD COLUMN IF NOT EXISTS spend_anomaly_enabled BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE tenant_preferences ADD COLUMN IF NOT EXISTS spend_anomaly_multiplier BIGINT;
ALTER TABLE tenant_preferences ADD COLUMN IF NOT EXISTS spend_anomaly_min_micros BIGINT;
ALTER TABLE tenant_preferences DROP CONSTRAINT IF EXISTS tenant_preferences_spend_anomaly_multiplier_check;
ALTER TABLE tenant_preferences ADD CONSTRAINT tenant_preferences_spend_anomaly_multiplier_check
	CHECK (spend_anomaly_multiplier IS NULL OR spend_anomaly_multiplier BETWEEN 2 AND 1000);
ALTER TABLE tenant_preferences DROP CONSTRAINT IF EXISTS tenant_preferences_spend_anomaly_min_check;
ALTER TABLE tenant_preferences ADD CONSTRAINT tenant_preferences_spend_anomaly_min_check
	CHECK (spend_anomaly_min_micros IS NULL OR spend_anomaly_min_micros BETWEEN 0 AND 1000000000000000);

CREATE TABLE IF NOT EXISTS billing_reservations (
    request_id TEXT PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    project_id UUID NOT NULL,
    key_id UUID NOT NULL,
    public_model TEXT NOT NULL,
    currency TEXT NOT NULL,
    reserved_micros BIGINT NOT NULL CHECK (reserved_micros >= 0),
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'settled', 'released', 'metering_failed')),
    input_price_micros_per_million BIGINT NOT NULL,
    output_price_micros_per_million BIGINT NOT NULL,
    cache_read_price_micros_per_million BIGINT NOT NULL,
    cache_write_price_micros_per_million BIGINT NOT NULL,
    actual_cost_micros BIGINT NOT NULL DEFAULT 0 CHECK (actual_cost_micros >= 0),
    charged_micros BIGINT NOT NULL DEFAULT 0 CHECK (charged_micros >= 0),
    uncollected_micros BIGINT NOT NULL DEFAULT 0 CHECK (uncollected_micros >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    settled_at TIMESTAMPTZ,
    FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE CASCADE
);
ALTER TABLE billing_reservations ADD COLUMN IF NOT EXISTS price_version_id UUID REFERENCES model_price_versions(id) ON DELETE SET NULL;
ALTER TABLE billing_reservations DROP CONSTRAINT IF EXISTS billing_reservations_status_check;
ALTER TABLE billing_reservations ADD CONSTRAINT billing_reservations_status_check
    CHECK (status IN ('pending','settled','released','metering_failed'));

CREATE TABLE IF NOT EXISTS billing_settlement_jobs (
    request_id TEXT PRIMARY KEY REFERENCES billing_reservations(request_id) ON DELETE CASCADE,
    event JSONB,
    status TEXT NOT NULL DEFAULT 'awaiting_event'
        CHECK (status IN ('awaiting_event', 'pending', 'processing', 'retry', 'done')),
    attempts INTEGER NOT NULL DEFAULT 0 CHECK (attempts >= 0),
    available_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    locked_at TIMESTAMPTZ,
    last_error TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS billing_settlement_jobs_ready_idx
    ON billing_settlement_jobs (available_at, created_at)
    WHERE status IN ('pending', 'retry');
CREATE INDEX IF NOT EXISTS billing_settlement_jobs_stale_idx
    ON billing_settlement_jobs (created_at)
    WHERE status IN ('awaiting_event', 'processing', 'retry');

CREATE TABLE IF NOT EXISTS usage_events (
    request_id TEXT PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
    project_id UUID NOT NULL,
    key_id UUID NOT NULL,
    public_model TEXT NOT NULL,
    provider_id TEXT,
    upstream_model TEXT,
    protocol TEXT NOT NULL,
    stream BOOLEAN NOT NULL DEFAULT FALSE,
    status_code INTEGER NOT NULL,
    success BOOLEAN NOT NULL,
    error_type TEXT NOT NULL DEFAULT '',
    attempts INTEGER NOT NULL DEFAULT 0,
    started_at TIMESTAMPTZ NOT NULL,
    duration_ms BIGINT NOT NULL DEFAULT 0,
    ttft_ms BIGINT NOT NULL DEFAULT 0 CHECK (ttft_ms >= 0),
    input_tokens BIGINT NOT NULL DEFAULT 0,
    output_tokens BIGINT NOT NULL DEFAULT 0,
    total_tokens BIGINT NOT NULL DEFAULT 0,
    cache_creation_input_tokens BIGINT NOT NULL DEFAULT 0,
    cache_read_input_tokens BIGINT NOT NULL DEFAULT 0,
    cost_micros BIGINT NOT NULL DEFAULT 0,
    charged_micros BIGINT NOT NULL DEFAULT 0,
    uncollected_micros BIGINT NOT NULL DEFAULT 0,
	usage_reported BOOLEAN NOT NULL DEFAULT FALSE,
	metering_status TEXT NOT NULL DEFAULT 'not_billable'
		CHECK (metering_status IN ('not_billable','reported','missing','released_unmetered','upstream_failed')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE RESTRICT
);
ALTER TABLE usage_events ADD COLUMN IF NOT EXISTS usage_reported BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE usage_events ADD COLUMN IF NOT EXISTS metering_status TEXT NOT NULL DEFAULT 'not_billable';
ALTER TABLE usage_events ADD COLUMN IF NOT EXISTS ttft_ms BIGINT NOT NULL DEFAULT 0;
ALTER TABLE usage_events DROP CONSTRAINT IF EXISTS usage_events_ttft_ms_check;
ALTER TABLE usage_events ADD CONSTRAINT usage_events_ttft_ms_check CHECK (ttft_ms >= 0);
ALTER TABLE usage_events DROP CONSTRAINT IF EXISTS usage_events_metering_status_check;
ALTER TABLE usage_events ADD CONSTRAINT usage_events_metering_status_check
    CHECK (metering_status IN ('not_billable','reported','missing','released_unmetered','upstream_failed'));

-- Usage persistence is independent from billing. Older installations created this
-- foreign key, which prevented recording requests when prepaid billing was disabled.
ALTER TABLE usage_events DROP CONSTRAINT IF EXISTS usage_events_request_id_fkey;

CREATE TABLE IF NOT EXISTS billing_ledger (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
    project_id UUID,
    currency TEXT NOT NULL,
    amount_micros BIGINT NOT NULL,
    balance_after_micros BIGINT NOT NULL CHECK (balance_after_micros >= 0),
    kind TEXT NOT NULL CHECK (kind IN ('topup', 'usage', 'adjustment', 'refund', 'release')),
    source_type TEXT NOT NULL,
    source_id TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (source_type, source_id)
);
ALTER TABLE billing_ledger DROP CONSTRAINT IF EXISTS billing_ledger_kind_check;
ALTER TABLE billing_ledger ADD CONSTRAINT billing_ledger_kind_check
    CHECK (kind IN ('topup','usage','adjustment','refund','release','dispute','dispute_reversal'));

CREATE TABLE IF NOT EXISTS topup_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
    amount_minor BIGINT NOT NULL CHECK (amount_minor > 0),
    amount_micros BIGINT NOT NULL CHECK (amount_micros > 0),
    currency TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'failed', 'expired')),
    stripe_session_id TEXT UNIQUE,
    checkout_url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    paid_at TIMESTAMPTZ
);
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS trigger_type TEXT NOT NULL DEFAULT 'manual';
ALTER TABLE topup_orders DROP CONSTRAINT IF EXISTS topup_orders_trigger_type_check;
ALTER TABLE topup_orders ADD CONSTRAINT topup_orders_trigger_type_check
	CHECK (trigger_type IN ('manual','auto'));
ALTER TABLE topup_orders DROP CONSTRAINT IF EXISTS topup_orders_status_check;
ALTER TABLE topup_orders ADD CONSTRAINT topup_orders_status_check
	CHECK (status IN ('pending','paid','failed','expired','partially_refunded','refunded','disputed','reversed'));
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS stripe_customer_id TEXT;
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS stripe_payment_intent_id TEXT;
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS stripe_charge_id TEXT;
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS stripe_invoice_id TEXT;
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS invoice_url TEXT;
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS invoice_pdf_url TEXT;
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS receipt_url TEXT;
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS refunded_micros BIGINT NOT NULL DEFAULT 0 CHECK (refunded_micros >= 0);
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS disputed_micros BIGINT NOT NULL DEFAULT 0 CHECK (disputed_micros >= 0);
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS reconciliation_status TEXT NOT NULL DEFAULT 'unknown';
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS reconciled_at TIMESTAMPTZ;
ALTER TABLE topup_orders ADD COLUMN IF NOT EXISTS reconciliation_error TEXT NOT NULL DEFAULT '';
ALTER TABLE topup_orders DROP CONSTRAINT IF EXISTS topup_orders_reconciliation_status_check;
ALTER TABLE topup_orders ADD CONSTRAINT topup_orders_reconciliation_status_check
	CHECK (reconciliation_status IN ('unknown','ok','repaired','missing','mismatch','resolved'));
CREATE INDEX IF NOT EXISTS topup_orders_payment_intent_idx ON topup_orders (stripe_payment_intent_id) WHERE stripe_payment_intent_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS topup_orders_customer_idx ON topup_orders (stripe_customer_id) WHERE stripe_customer_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS topup_orders_auto_pending_idx ON topup_orders (tenant_id)
	WHERE trigger_type='auto' AND status='pending';

CREATE TABLE IF NOT EXISTS billing_reconciliation_resolutions (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	topup_order_id UUID NOT NULL UNIQUE REFERENCES topup_orders(id) ON DELETE RESTRICT,
	tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
	actor_id TEXT NOT NULL DEFAULT '',
	actor_type TEXT NOT NULL CHECK (actor_type IN ('console_user','bootstrap','maintenance')),
	reason TEXT NOT NULL,
	created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS stripe_customers (
    tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
    stripe_customer_id TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS tenant_billing_profiles (
    tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
    legal_name TEXT NOT NULL,
    billing_email TEXT NOT NULL,
    address_line1 TEXT NOT NULL,
    address_line2 TEXT NOT NULL DEFAULT '',
    city TEXT NOT NULL,
    region TEXT NOT NULL DEFAULT '',
    postal_code TEXT NOT NULL,
    country TEXT NOT NULL CHECK (country ~ '^[A-Z]{2}$'),
    stripe_sync_status TEXT NOT NULL DEFAULT 'pending'
        CHECK (stripe_sync_status IN ('pending','synced','failed','disabled')),
    stripe_synced_at TIMESTAMPTZ,
    stripe_sync_error TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS tenant_auto_topup_settings (
	tenant_id UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
	enabled BOOLEAN NOT NULL DEFAULT FALSE,
	threshold_micros BIGINT NOT NULL CHECK (threshold_micros >= 0),
	topup_amount_minor BIGINT NOT NULL CHECK (topup_amount_minor > 0),
	stripe_payment_method_id TEXT,
	payment_method_type TEXT NOT NULL DEFAULT '',
	payment_method_brand TEXT NOT NULL DEFAULT '',
	payment_method_last4 TEXT NOT NULL DEFAULT '',
	payment_method_exp_month INTEGER NOT NULL DEFAULT 0 CHECK (payment_method_exp_month BETWEEN 0 AND 12),
	payment_method_exp_year INTEGER NOT NULL DEFAULT 0 CHECK (payment_method_exp_year >= 0),
	stripe_setup_session_id TEXT UNIQUE,
	status TEXT NOT NULL DEFAULT 'not_configured'
		CHECK (status IN ('not_configured','ready','charging','action_required','failed')),
	last_error TEXT NOT NULL DEFAULT '',
	failure_count INTEGER NOT NULL DEFAULT 0 CHECK (failure_count >= 0),
	last_attempt_at TIMESTAMPTZ,
	last_succeeded_at TIMESTAMPTZ,
	next_attempt_at TIMESTAMPTZ,
	created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
	CHECK (stripe_payment_method_id IS NOT NULL OR enabled = FALSE)
);
CREATE INDEX IF NOT EXISTS tenant_auto_topup_ready_idx
	ON tenant_auto_topup_settings (next_attempt_at, tenant_id)
	WHERE enabled AND stripe_payment_method_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS stripe_refunds (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
    topup_order_id UUID NOT NULL REFERENCES topup_orders(id) ON DELETE RESTRICT,
    stripe_refund_id TEXT UNIQUE,
    amount_minor BIGINT NOT NULL CHECK (amount_minor > 0),
    amount_micros BIGINT NOT NULL CHECK (amount_micros > 0),
    held_micros BIGINT NOT NULL DEFAULT 0 CHECK (held_micros >= 0),
    uncollected_micros BIGINT NOT NULL DEFAULT 0 CHECK (uncollected_micros >= 0),
    currency TEXT NOT NULL,
    reason TEXT NOT NULL DEFAULT 'requested_by_customer',
    status TEXT NOT NULL DEFAULT 'queued'
        CHECK (status IN ('queued','submitting','pending','requires_action','succeeded','failed','canceled')),
    attempts INTEGER NOT NULL DEFAULT 0,
    available_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_error TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS stripe_refunds_ready_idx ON stripe_refunds (available_at, created_at)
    WHERE status IN ('queued','submitting');

CREATE TABLE IF NOT EXISTS stripe_disputes (
    stripe_dispute_id TEXT PRIMARY KEY,
    tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
    topup_order_id UUID REFERENCES topup_orders(id) ON DELETE SET NULL,
    stripe_payment_intent_id TEXT,
    amount_minor BIGINT NOT NULL CHECK (amount_minor >= 0),
    amount_micros BIGINT NOT NULL CHECK (amount_micros >= 0),
    currency TEXT NOT NULL,
    status TEXT NOT NULL,
    reason TEXT NOT NULL DEFAULT '',
    debited_micros BIGINT NOT NULL DEFAULT 0 CHECK (debited_micros >= 0),
    uncollected_micros BIGINT NOT NULL DEFAULT 0 CHECK (uncollected_micros >= 0),
    due_by TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    closed_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS stripe_invoices (
    stripe_invoice_id TEXT PRIMARY KEY,
    tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
    topup_order_id UUID REFERENCES topup_orders(id) ON DELETE SET NULL,
    stripe_customer_id TEXT,
    status TEXT NOT NULL DEFAULT '',
    currency TEXT NOT NULL DEFAULT '',
    amount_due_minor BIGINT NOT NULL DEFAULT 0,
    amount_paid_minor BIGINT NOT NULL DEFAULT 0,
    attempt_count INTEGER NOT NULL DEFAULT 0,
    next_payment_attempt TIMESTAMPTZ,
    hosted_invoice_url TEXT NOT NULL DEFAULT '',
    invoice_pdf_url TEXT NOT NULL DEFAULT '',
    last_failure TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS billing_reconciliation_runs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    status TEXT NOT NULL CHECK (status IN ('running','clean','mismatch','failed')),
    checked_orders BIGINT NOT NULL DEFAULT 0,
    mismatch_count BIGINT NOT NULL DEFAULT 0,
    report JSONB NOT NULL DEFAULT '[]'::jsonb,
    error TEXT NOT NULL DEFAULT '',
    started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS stripe_webhook_events (
    event_id TEXT PRIMARY KEY,
    event_type TEXT NOT NULL,
    processed_at TIMESTAMPTZ,
	attempts INTEGER NOT NULL DEFAULT 0,
	last_attempt_at TIMESTAMPTZ,
	processing_error TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE stripe_webhook_events ADD COLUMN IF NOT EXISTS attempts INTEGER NOT NULL DEFAULT 0;
ALTER TABLE stripe_webhook_events ADD COLUMN IF NOT EXISTS last_attempt_at TIMESTAMPTZ;
ALTER TABLE stripe_webhook_events ADD COLUMN IF NOT EXISTS processing_error TEXT NOT NULL DEFAULT '';

CREATE TABLE IF NOT EXISTS console_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
    email TEXT NOT NULL,
    display_name TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN (
        'platform_admin', 'platform_viewer', 'tenant_admin',
        'tenant_billing', 'tenant_developer', 'tenant_viewer'
    )),
    token_prefix TEXT NOT NULL DEFAULT '',
    token_hash BYTEA UNIQUE CHECK (token_hash IS NULL OR octet_length(token_hash) = 32),
    password_hash BYTEA CHECK (password_hash IS NULL OR octet_length(password_hash) = 32),
    password_salt BYTEA CHECK (password_salt IS NULL OR octet_length(password_salt) = 16),
    password_iterations INTEGER CHECK (password_iterations IS NULL OR password_iterations >= 100000),
    password_changed_at TIMESTAMPTZ,
    status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'revoked')),
    last_used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    revoked_at TIMESTAMPTZ,
    CHECK ((role LIKE 'platform_%' AND tenant_id IS NULL) OR (role LIKE 'tenant_%' AND tenant_id IS NOT NULL))
);
ALTER TABLE console_users ALTER COLUMN token_prefix SET DEFAULT '';
ALTER TABLE console_users ALTER COLUMN token_prefix DROP NOT NULL;
ALTER TABLE console_users ALTER COLUMN token_hash DROP NOT NULL;
ALTER TABLE console_users ADD COLUMN IF NOT EXISTS password_hash BYTEA;
ALTER TABLE console_users ADD COLUMN IF NOT EXISTS password_salt BYTEA;
ALTER TABLE console_users ADD COLUMN IF NOT EXISTS password_iterations INTEGER;
ALTER TABLE console_users ADD COLUMN IF NOT EXISTS password_changed_at TIMESTAMPTZ;
ALTER TABLE console_users ADD COLUMN IF NOT EXISTS email_verified_at TIMESTAMPTZ;
ALTER TABLE console_users ADD COLUMN IF NOT EXISTS invited_at TIMESTAMPTZ;
ALTER TABLE console_users ADD COLUMN IF NOT EXISTS accepted_at TIMESTAMPTZ;
ALTER TABLE console_users DROP CONSTRAINT IF EXISTS console_users_status_check;
ALTER TABLE console_users ADD CONSTRAINT console_users_status_check
    CHECK (status IN ('pending_verification', 'invited', 'active', 'revoked'));
UPDATE console_users SET email_verified_at = COALESCE(email_verified_at, created_at)
    WHERE status = 'active' AND password_hash IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS console_users_email_tenant_idx
    ON console_users (lower(email), COALESCE(tenant_id, '00000000-0000-0000-0000-000000000000'::uuid));
CREATE UNIQUE INDEX IF NOT EXISTS console_users_login_email_idx
    ON console_users (lower(email)) WHERE password_hash IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS console_users_global_email_idx
    ON console_users (lower(email));

CREATE TABLE IF NOT EXISTS console_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE,
    token_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(token_hash) = 32),
    csrf_hash BYTEA NOT NULL CHECK (octet_length(csrf_hash) = 32),
    expires_at TIMESTAMPTZ NOT NULL,
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    revoked_at TIMESTAMPTZ,
    remote_ip INET,
    user_agent TEXT NOT NULL DEFAULT '',
    auth_method TEXT NOT NULL DEFAULT 'password',
    mfa_verified_at TIMESTAMPTZ
);
ALTER TABLE console_sessions ADD COLUMN IF NOT EXISTS auth_method TEXT NOT NULL DEFAULT 'password';
ALTER TABLE console_sessions ADD COLUMN IF NOT EXISTS mfa_verified_at TIMESTAMPTZ;
CREATE INDEX IF NOT EXISTS console_sessions_user_active_idx
    ON console_sessions (user_id, created_at DESC) WHERE revoked_at IS NULL;

CREATE TABLE IF NOT EXISTS console_login_throttles (
    identity_hash BYTEA PRIMARY KEY CHECK (octet_length(identity_hash) = 32),
    failures INTEGER NOT NULL DEFAULT 0,
    window_started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    locked_until TIMESTAMPTZ,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS console_rate_limits (
    bucket_hash BYTEA PRIMARY KEY CHECK (octet_length(bucket_hash) = 32),
    hits INTEGER NOT NULL DEFAULT 0,
    window_started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS console_action_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE,
    purpose TEXT NOT NULL CHECK (purpose IN ('verify_email', 'password_reset', 'invite')),
    token_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(token_hash) = 32),
    expires_at TIMESTAMPTZ NOT NULL,
    consumed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    requested_ip INET
);
CREATE INDEX IF NOT EXISTS console_action_tokens_active_idx
    ON console_action_tokens (user_id, purpose, expires_at DESC) WHERE consumed_at IS NULL;

CREATE TABLE IF NOT EXISTS console_mail_outbox (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    recipient TEXT NOT NULL,
	template TEXT NOT NULL CHECK (template IN ('verify_email', 'password_reset', 'invite', 'low_balance', 'spend_anomaly')),
    subject TEXT NOT NULL,
    body_ciphertext BYTEA NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sending', 'sent', 'failed')),
    attempts INTEGER NOT NULL DEFAULT 0,
    available_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    claimed_at TIMESTAMPTZ,
    sent_at TIMESTAMPTZ,
    last_error TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE console_mail_outbox DROP CONSTRAINT IF EXISTS console_mail_outbox_template_check;
ALTER TABLE console_mail_outbox ADD CONSTRAINT console_mail_outbox_template_check
	CHECK (template IN ('verify_email','password_reset','invite','low_balance','spend_anomaly'));
ALTER TABLE console_mail_outbox DROP CONSTRAINT IF EXISTS console_mail_outbox_status_check;
UPDATE console_mail_outbox SET status='retry' WHERE status='failed';
ALTER TABLE console_mail_outbox ADD CONSTRAINT console_mail_outbox_status_check
	CHECK (status IN ('pending','sending','sent','retry','dead','suppressed'));
DROP INDEX IF EXISTS console_mail_outbox_pending_idx;
CREATE INDEX console_mail_outbox_pending_idx
	ON console_mail_outbox (available_at, created_at) WHERE status IN ('pending', 'retry');

CREATE TABLE IF NOT EXISTS mail_suppressions (
	recipient TEXT PRIMARY KEY,
	reason TEXT NOT NULL CHECK (reason IN ('bounce','complaint','manual')),
	provider TEXT NOT NULL DEFAULT '',
	provider_event_id TEXT NOT NULL DEFAULT '',
	detail TEXT NOT NULL DEFAULT '',
	created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS mail_feedback_events (
	event_id TEXT PRIMARY KEY,
	event_type TEXT NOT NULL CHECK (event_type IN ('delivered','bounce','complaint')),
	recipient TEXT NOT NULL,
	provider TEXT NOT NULL DEFAULT '',
	created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS mail_notification_events (
	tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
	recipient TEXT NOT NULL,
	notification_type TEXT NOT NULL CHECK (notification_type IN ('low_balance','spend_anomaly')),
	dedupe_key TEXT NOT NULL,
	created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
	PRIMARY KEY (tenant_id,recipient,notification_type,dedupe_key)
);

CREATE TABLE IF NOT EXISTS console_auth_challenges (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE,
    token_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(token_hash) = 32),
    purpose TEXT NOT NULL CHECK (purpose IN ('mfa_login')),
    allowed_methods JSONB NOT NULL DEFAULT '[]'::jsonb,
    expires_at TIMESTAMPTZ NOT NULL,
    consumed_at TIMESTAMPTZ,
    attempts INTEGER NOT NULL DEFAULT 0,
    remote_ip INET,
    user_agent TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS console_auth_challenges_active_idx
    ON console_auth_challenges (user_id, expires_at DESC) WHERE consumed_at IS NULL;

CREATE TABLE IF NOT EXISTS console_totp_credentials (
    user_id UUID PRIMARY KEY REFERENCES console_users(id) ON DELETE CASCADE,
    secret_ciphertext BYTEA NOT NULL,
    confirmed_at TIMESTAMPTZ,
    last_used_step BIGINT NOT NULL DEFAULT -1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS console_recovery_codes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE,
    code_hash BYTEA NOT NULL CHECK (octet_length(code_hash) = 32),
    used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (user_id, code_hash)
);

CREATE TABLE IF NOT EXISTS console_passkeys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE,
    credential_id BYTEA NOT NULL UNIQUE,
    credential_ciphertext BYTEA NOT NULL,
    name TEXT NOT NULL DEFAULT 'Passkey',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_used_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS console_passkeys_user_idx ON console_passkeys (user_id, created_at);

CREATE TABLE IF NOT EXISTS console_webauthn_challenges (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES console_users(id) ON DELETE CASCADE,
    token_hash BYTEA NOT NULL UNIQUE CHECK (octet_length(token_hash) = 32),
    purpose TEXT NOT NULL CHECK (purpose IN ('register', 'login', 'mfa_login')),
    session_ciphertext BYTEA NOT NULL,
    auth_challenge_id UUID REFERENCES console_auth_challenges(id) ON DELETE CASCADE,
    expires_at TIMESTAMPTZ NOT NULL,
    consumed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS console_webauthn_challenges_active_idx
    ON console_webauthn_challenges (user_id, expires_at DESC) WHERE consumed_at IS NULL;

CREATE TABLE IF NOT EXISTS project_limits (
    project_id UUID PRIMARY KEY,
    tenant_id UUID NOT NULL,
    requests_per_minute BIGINT NOT NULL DEFAULT 0 CHECK (requests_per_minute >= 0),
    tokens_per_minute BIGINT NOT NULL DEFAULT 0 CHECK (tokens_per_minute >= 0),
    concurrent_requests BIGINT NOT NULL DEFAULT 0 CHECK (concurrent_requests >= 0),
    monthly_spend_micros BIGINT NOT NULL DEFAULT 0 CHECK (monthly_spend_micros >= 0),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS usage_monthly_rollups (
    period_start DATE NOT NULL,
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
    project_id UUID NOT NULL,
    request_count BIGINT NOT NULL DEFAULT 0,
    successful_requests BIGINT NOT NULL DEFAULT 0,
    input_tokens BIGINT NOT NULL DEFAULT 0,
    output_tokens BIGINT NOT NULL DEFAULT 0,
    total_tokens BIGINT NOT NULL DEFAULT 0,
    cost_micros BIGINT NOT NULL DEFAULT 0,
    charged_micros BIGINT NOT NULL DEFAULT 0,
    uncollected_micros BIGINT NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (project_id, period_start),
    FOREIGN KEY (project_id, tenant_id) REFERENCES projects(id, tenant_id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGSERIAL PRIMARY KEY,
    actor_id UUID REFERENCES console_users(id) ON DELETE SET NULL,
    actor_type TEXT NOT NULL CHECK (actor_type IN ('bootstrap', 'console_user', 'anonymous')),
    actor_role TEXT NOT NULL,
    tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
    request_id TEXT NOT NULL,
    method TEXT NOT NULL,
    path TEXT NOT NULL,
    action TEXT NOT NULL,
    status_code INTEGER NOT NULL,
    remote_ip INET,
    user_agent TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE audit_logs DROP CONSTRAINT IF EXISTS audit_logs_actor_type_check;
ALTER TABLE audit_logs ADD CONSTRAINT audit_logs_actor_type_check CHECK (actor_type IN ('bootstrap', 'console_user', 'anonymous'));

CREATE INDEX IF NOT EXISTS billing_ledger_tenant_idx ON billing_ledger (tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS usage_events_tenant_idx ON usage_events (tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS usage_events_project_idx ON usage_events (project_id, created_at DESC);
CREATE INDEX IF NOT EXISTS usage_events_model_idx ON usage_events (public_model, created_at DESC);
CREATE INDEX IF NOT EXISTS usage_events_key_idx ON usage_events (key_id, started_at DESC);
CREATE INDEX IF NOT EXISTS billing_reservations_pending_idx ON billing_reservations (status, created_at) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS billing_reservations_project_pending_idx ON billing_reservations (project_id, created_at) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS billing_reservations_key_period_idx ON billing_reservations (key_id, created_at DESC) WHERE status IN ('pending', 'metering_failed');
CREATE INDEX IF NOT EXISTS console_users_tenant_idx ON console_users (tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS console_sessions_user_idx ON console_sessions (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS console_sessions_expiry_idx ON console_sessions (expires_at) WHERE revoked_at IS NULL;
CREATE INDEX IF NOT EXISTS audit_logs_created_idx ON audit_logs (created_at DESC);
CREATE INDEX IF NOT EXISTS audit_logs_tenant_idx ON audit_logs (tenant_id, created_at DESC);