-- ============================================================
-- Open Parliament API - Central Database Schema
-- hnsrd.ai central key management
-- ============================================================

-- ------------------------------------------------------------
-- Country Registry
-- Registered parliament deployments in the network
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `country_registry` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `country_code` VARCHAR(20) NOT NULL COMMENT 'Unique code: anguilla, barbados, dominica',
  `country_name` VARCHAR(255) NOT NULL COMMENT 'Full name: Anguilla House of Assembly',
  `domain` VARCHAR(255) NOT NULL COMMENT 'Deployment domain: hansard.houseofassembly.ai',
  `api_endpoint` VARCHAR(500) DEFAULT NULL COMMENT 'Internal API endpoint for routing',
  `license_key` VARCHAR(64) DEFAULT NULL COMMENT 'FK reference to deployment license',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `timezone` VARCHAR(50) DEFAULT 'UTC',
  `joined_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `country_code` (`country_code`),
  UNIQUE KEY `domain` (`domain`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Central API Keys
-- Single source of truth for all API keys across deployments
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `central_api_keys` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `api_key` VARCHAR(64) NOT NULL COMMENT 'The actual key: hnsrd_xxxx',
  `email` VARCHAR(255) NOT NULL COMMENT 'Unique per developer',
  `name` VARCHAR(255) NOT NULL COMMENT 'Developer/org name',
  `organization` VARCHAR(255) DEFAULT NULL,
  `purpose` TEXT DEFAULT NULL COMMENT 'Stated use case',

  -- Tier and limits
  `tier` ENUM('free','standard','premium','unlimited') NOT NULL DEFAULT 'free',
  `rate_limit_per_day` INT(11) NOT NULL DEFAULT 100,
  `rate_limit_per_minute` INT(11) NOT NULL DEFAULT 10,

  -- Status
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `is_revoked` TINYINT(1) NOT NULL DEFAULT 0,
  `revoked_at` TIMESTAMP NULL DEFAULT NULL,
  `revoked_by` VARCHAR(100) DEFAULT NULL COMMENT 'Who revoked: country_code or superadmin',
  `revoke_reason` TEXT DEFAULT NULL,

  -- Origin tracking
  `created_from_country` VARCHAR(20) NOT NULL COMMENT 'Which deployment issued this key',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `last_used_at` TIMESTAMP NULL DEFAULT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `api_key` (`api_key`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_active` (`is_active`),
  KEY `idx_country` (`created_from_country`),
  KEY `idx_tier` (`tier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- API Key Usage (per country)
-- Track which countries a key has accessed
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `api_key_usage` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `api_key_id` INT(11) NOT NULL,
  `country_code` VARCHAR(20) NOT NULL,
  `endpoint` VARCHAR(255) NOT NULL COMMENT 'e.g., /sittings, /speeches',
  `requests_count` INT(11) NOT NULL DEFAULT 0,
  `date` DATE NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_usage` (`api_key_id`, `country_code`, `endpoint`, `date`),
  KEY `idx_date` (`date`),
  KEY `idx_country` (`country_code`),
  CONSTRAINT `fk_usage_key` FOREIGN KEY (`api_key_id`) REFERENCES `central_api_keys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Daily Key Usage Summary
-- Aggregated daily usage per key (for rate limiting)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `api_key_daily_usage` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `api_key_id` INT(11) NOT NULL,
  `date` DATE NOT NULL,
  `requests_total` INT(11) NOT NULL DEFAULT 0,
  `last_request_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_daily` (`api_key_id`, `date`),
  CONSTRAINT `fk_daily_key` FOREIGN KEY (`api_key_id`) REFERENCES `central_api_keys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- API Audit Log
-- Track all key operations: create, revoke, update
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `api_audit_log` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `action` VARCHAR(50) NOT NULL COMMENT 'key_created, key_revoked, key_updated, key_accessed',
  `api_key_id` INT(11) DEFAULT NULL,
  `api_key` VARCHAR(64) DEFAULT NULL COMMENT 'Store key even if record deleted',
  `email` VARCHAR(255) DEFAULT NULL,
  `country_code` VARCHAR(20) DEFAULT NULL COMMENT 'Which country performed/triggered action',
  `performed_by` VARCHAR(255) DEFAULT NULL COMMENT 'User/system that performed action',
  `details` TEXT DEFAULT NULL COMMENT 'JSON with additional details',
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_action` (`action`),
  KEY `idx_key_id` (`api_key_id`),
  KEY `idx_email` (`email`),
  KEY `idx_country` (`country_code`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- Country Access Permissions (optional)
-- If a country wants to block specific keys from their data
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `country_key_permissions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `country_code` VARCHAR(20) NOT NULL,
  `api_key_id` INT(11) NOT NULL,
  `is_allowed` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '0 = blocked, 1 = allowed',
  `blocked_reason` TEXT DEFAULT NULL,
  `blocked_at` TIMESTAMP NULL DEFAULT NULL,
  `blocked_by` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_permission` (`country_code`, `api_key_id`),
  CONSTRAINT `fk_perm_key` FOREIGN KEY (`api_key_id`) REFERENCES `central_api_keys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Sample Data
-- ============================================================

-- Register Anguilla as first country
INSERT INTO `country_registry` (`country_code`, `country_name`, `domain`, `api_endpoint`, `is_active`, `timezone`)
VALUES ('anguilla', 'Anguilla House of Assembly', 'hansard.houseofassembly.ai', 'https://hansard.houseofassembly.ai/api', 1, 'America/Anguilla')
ON DUPLICATE KEY UPDATE `updated_at` = CURRENT_TIMESTAMP;
