-- Authentication and API management tables

-- API Keys table for storing API credentials
CREATE TABLE IF NOT EXISTS api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_key VARCHAR(64) UNIQUE NOT NULL,
    secret_key VARCHAR(128) NOT NULL,
    name VARCHAR(255) NOT NULL,
    permissions JSON DEFAULT '[]',
    rate_limit_per_minute INT DEFAULT 60,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used TIMESTAMP NULL,
    INDEX idx_api_key (api_key),
    INDEX idx_active (is_active)
);

-- Rate limiting table
CREATE TABLE IF NOT EXISTS rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_key VARCHAR(64) NOT NULL,
    minute_window VARCHAR(16) NOT NULL,
    request_count INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_api_minute (api_key, minute_window),
    INDEX idx_api_key_minute (api_key, minute_window),
    FOREIGN KEY (api_key) REFERENCES api_keys(api_key) ON DELETE CASCADE
);

-- API request logging table
CREATE TABLE IF NOT EXISTS api_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_key VARCHAR(64) NOT NULL,
    method VARCHAR(10) NOT NULL,
    uri VARCHAR(500) NOT NULL,
    response_code INT NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_api_key (api_key),
    INDEX idx_timestamp (timestamp),
    FOREIGN KEY (api_key) REFERENCES api_keys(api_key) ON DELETE CASCADE
);

-- Insert a default API key for testing
INSERT INTO api_keys (api_key, secret_key, name, permissions, rate_limit_per_minute) 
VALUES (
    'test_api_key_12345',
    'test_secret_key_67890_very_secure',
    'Test API Key',
    '["read", "write"]',
    100
) ON DUPLICATE KEY UPDATE name = VALUES(name);
