explorer-monorepo/backend/database/migrations/0012_admin_config_schema.up.sql

-- Migration: Admin Configuration Schema
-- Description: Creates tables for managing API keys, endpoints, and service configuration
-- For Token Aggregation Service Control Panel

-- API Keys Management
CREATE TABLE IF NOT EXISTS api_keys (
    id BIGSERIAL PRIMARY KEY,
    provider VARCHAR(50) NOT NULL CHECK (provider IN ('coingecko', 'coinmarketcap', 'dexscreener', 'custom')),
    key_name VARCHAR(255) NOT NULL,
    api_key_encrypted TEXT NOT NULL,
    is_active BOOLEAN DEFAULT true,
    rate_limit_per_minute INTEGER,
    rate_limit_per_day INTEGER,
    last_used_at TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE,
    created_by VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(provider, key_name)
);

CREATE INDEX idx_api_keys_provider ON api_keys(provider);
CREATE INDEX idx_api_keys_active ON api_keys(is_active) WHERE is_active = true;

-- API Endpoints Configuration
CREATE TABLE IF NOT EXISTS api_endpoints (
    id BIGSERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    endpoint_type VARCHAR(50) NOT NULL CHECK (endpoint_type IN ('rpc', 'explorer', 'indexer', 'custom')),
    endpoint_name VARCHAR(255) NOT NULL,
    endpoint_url TEXT NOT NULL,
    is_primary BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    requires_auth BOOLEAN DEFAULT false,
    auth_type VARCHAR(50),
    auth_config JSONB,
    rate_limit_per_minute INTEGER,
    timeout_ms INTEGER DEFAULT 10000,
    health_check_enabled BOOLEAN DEFAULT true,
    last_health_check TIMESTAMP WITH TIME ZONE,
    health_check_status VARCHAR(20),
    created_by VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(chain_id, endpoint_type, endpoint_name)
);

CREATE INDEX idx_api_endpoints_chain ON api_endpoints(chain_id);
CREATE INDEX idx_api_endpoints_type ON api_endpoints(endpoint_type);
CREATE INDEX idx_api_endpoints_active ON api_endpoints(is_active) WHERE is_active = true;

-- DEX Factory Configuration
CREATE TABLE IF NOT EXISTS dex_factory_config (
    id BIGSERIAL PRIMARY KEY,
    chain_id INTEGER NOT NULL,
    dex_type VARCHAR(20) NOT NULL CHECK (dex_type IN ('uniswap_v2', 'uniswap_v3', 'dodo', 'custom')),
    factory_address VARCHAR(42) NOT NULL,
    router_address VARCHAR(42),
    pool_manager_address VARCHAR(42),
    start_block BIGINT DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    description TEXT,
    created_by VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(chain_id, dex_type, factory_address)
);

CREATE INDEX idx_dex_factory_chain ON dex_factory_config(chain_id);
CREATE INDEX idx_dex_factory_type ON dex_factory_config(dex_type);

-- Admin Users
CREATE TABLE IF NOT EXISTS admin_users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255),
    password_hash TEXT NOT NULL,
    role VARCHAR(50) DEFAULT 'admin' CHECK (role IN ('super_admin', 'admin', 'operator', 'viewer')),
    is_active BOOLEAN DEFAULT true,
    last_login TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_admin_users_username ON admin_users(username);
CREATE INDEX idx_admin_users_active ON admin_users(is_active) WHERE is_active = true;

-- Admin Sessions
CREATE TABLE IF NOT EXISTS admin_sessions (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
    session_token VARCHAR(255) NOT NULL UNIQUE,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_admin_sessions_token ON admin_sessions(session_token);
CREATE INDEX idx_admin_sessions_user ON admin_sessions(user_id);

-- Audit Log
CREATE TABLE IF NOT EXISTS admin_audit_log (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES admin_users(id),
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50) NOT NULL,
    resource_id BIGINT,
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_audit_log_user ON admin_audit_log(user_id);
CREATE INDEX idx_audit_log_resource ON admin_audit_log(resource_type, resource_id);
CREATE INDEX idx_audit_log_created ON admin_audit_log(created_at DESC);

-- Update triggers (if update_updated_at_column function exists)
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'update_updated_at_column') THEN
        CREATE TRIGGER update_api_keys_updated_at BEFORE UPDATE ON api_keys
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
        CREATE TRIGGER update_api_endpoints_updated_at BEFORE UPDATE ON api_endpoints
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
        CREATE TRIGGER update_dex_factory_config_updated_at BEFORE UPDATE ON dex_factory_config
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
        CREATE TRIGGER update_admin_users_updated_at BEFORE UPDATE ON admin_users
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
    END IF;
END $$;