-- API Key Management Migration
-- Version: 001
-- Date: 2026-03-21
-- Description: Add API key management tables for secure API access

-- API Keys Table
CREATE TABLE IF NOT EXISTS api_keys (
    id SERIAL PRIMARY KEY,
    key_id VARCHAR(48) UNIQUE NOT NULL,
    key_hash VARCHAR(64) NOT NULL,
    key_prefix VARCHAR(8) NOT NULL,
    name VARCHAR(128) NOT NULL,
    key_type VARCHAR(20) NOT NULL DEFAULT 'user',
    user_id BIGINT,
    service_name VARCHAR(64),
    permissions JSONB DEFAULT '["read", "write"]',
    expires_at TIMESTAMP,
    last_used_at TIMESTAMP,
    last_used_ip VARCHAR(45),
    usage_count BIGINT DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    rotation_required BOOLEAN DEFAULT FALSE,
    rotation_reason TEXT,
    grace_period_end TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for api_keys
CREATE INDEX IF NOT EXISTS idx_api_keys_key_id ON api_keys(key_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_hash ON api_keys(key_hash);
CREATE INDEX IF NOT EXISTS idx_api_keys_type ON api_keys(key_type);
CREATE INDEX IF NOT EXISTS idx_api_keys_status ON api_keys(status);
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);

-- API Key Audit Log Table
CREATE TABLE IF NOT EXISTS api_key_audit_log (
    id SERIAL PRIMARY KEY,
    key_id VARCHAR(32) NOT NULL,
    action VARCHAR(50) NOT NULL,
    actor VARCHAR(128),
    ip_address VARCHAR(45),
    user_agent TEXT,
    request_path TEXT,
    response_code INT,
    anomaly_type VARCHAR(30),
    details JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for audit log
CREATE INDEX IF NOT EXISTS idx_audit_key_id ON api_key_audit_log(key_id);
CREATE INDEX IF NOT EXISTS idx_audit_action ON api_key_audit_log(action);
CREATE INDEX IF NOT EXISTS idx_audit_created_at ON api_key_audit_log(created_at);
CREATE INDEX IF NOT EXISTS idx_audit_ip ON api_key_audit_log(ip_address);

-- API Key Anomalies Table
CREATE TABLE IF NOT EXISTS api_key_anomalies (
    id SERIAL PRIMARY KEY,
    key_id VARCHAR(32) NOT NULL,
    anomaly_type VARCHAR(30) NOT NULL,
    severity VARCHAR(10) NOT NULL,
    ip_address VARCHAR(45),
    request_count INT,
    error_count INT,
    error_rate DOUBLE PRECISION,
    unique_ips INT,
    details JSONB,
    resolved BOOLEAN DEFAULT FALSE,
    resolved_at TIMESTAMP,
    resolved_by VARCHAR(128),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for anomalies
CREATE INDEX IF NOT EXISTS idx_anomalies_key_id ON api_key_anomalies(key_id);
CREATE INDEX IF NOT EXISTS idx_anomalies_resolved ON api_key_anomalies(resolved);
CREATE INDEX IF NOT EXISTS idx_anomalies_severity ON api_key_anomalies(severity);
CREATE INDEX IF NOT EXISTS idx_anomalies_created_at ON api_key_anomalies(created_at);

-- API Key Types Reference
-- System (msys_): Internal system use, 365 days TTL, 72h grace
-- User (muser_): User authentication, 90 days TTL, 24h grace
-- Service (msvc_): Service-to-service, 180 days TTL, 48h grace
-- Integration (mint_): Third-party integrations, 30 days TTL, 24h grace
-- Emergency (memg_): Emergency access, 1 day TTL, 0h grace (immediate)

-- Anomaly Types Reference
-- high_request_rate: Requests exceed threshold per minute
-- high_error_rate: Error rate exceeds threshold
-- multiple_ips: Unusual number of unique IPs
-- unusual_time: Activity at unusual hours
-- brute_force: Potential brute force attack
-- data_exfiltration: Unusual data access patterns