-- Central registry for the manual multi-tenant MVP.
-- Run this migration on a dedicated platform database, never on a tenant database.

CREATE TABLE IF NOT EXISTS `platform_tenant_tb` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` CHAR(36) NOT NULL,
  `request_code` VARCHAR(32) NOT NULL,
  `name` VARCHAR(190) NOT NULL,
  `slug` VARCHAR(40) NOT NULL,
  `owner_name` VARCHAR(190) DEFAULT NULL,
  `owner_email` VARCHAR(190) DEFAULT NULL,
  `owner_phone` VARCHAR(30) DEFAULT NULL,
  `estimated_students` INT UNSIGNED DEFAULT NULL,
  `database_host` VARCHAR(190) DEFAULT NULL,
  `database_port` SMALLINT UNSIGNED DEFAULT NULL,
  `database_name` VARCHAR(64) DEFAULT NULL,
  `database_username` VARCHAR(128) DEFAULT NULL,
  `database_password_encrypted` TEXT NULL,
  `schema_version` VARCHAR(40) DEFAULT NULL,
  `status` ENUM(
    'pending_review',
    'approved',
    'provisioning',
    'ready',
    'active',
    'failed',
    'suspended',
    'rejected'
  ) NOT NULL DEFAULT 'pending_review',
  `review_note` TEXT NULL,
  `provision_error` TEXT NULL,
  `approved_by` BIGINT UNSIGNED DEFAULT NULL,
  `approved_at` DATETIME DEFAULT NULL,
  `activated_by` BIGINT UNSIGNED DEFAULT NULL,
  `activated_at` DATETIME DEFAULT NULL,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_platform_tenant_uuid` (`uuid`),
  UNIQUE KEY `uq_platform_tenant_request_code` (`request_code`),
  UNIQUE KEY `uq_platform_tenant_slug` (`slug`),
  KEY `idx_platform_tenant_status` (`status`),
  KEY `idx_platform_tenant_owner_email` (`owner_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `platform_domain_tb` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `hostname` VARCHAR(253) NOT NULL,
  `domain_type` ENUM('subdomain', 'custom') NOT NULL DEFAULT 'subdomain',
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
  `dns_status` ENUM('pending', 'verified', 'failed') NOT NULL DEFAULT 'pending',
  `ssl_status` ENUM('pending', 'active', 'failed') NOT NULL DEFAULT 'pending',
  `status` ENUM('pending', 'active', 'disabled') NOT NULL DEFAULT 'pending',
  `verified_at` DATETIME DEFAULT NULL,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_platform_domain_hostname` (`hostname`),
  KEY `idx_platform_domain_tenant` (`tenant_id`),
  KEY `idx_platform_domain_status` (`status`),
  CONSTRAINT `fk_platform_domain_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `platform_tenant_tb` (`id`)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `platform_tenant_log_tb` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `action` VARCHAR(80) NOT NULL,
  `actor_id` BIGINT UNSIGNED DEFAULT NULL,
  `actor_name` VARCHAR(190) DEFAULT NULL,
  `metadata_json` LONGTEXT NULL,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_platform_tenant_log_tenant_date` (`tenant_id`, `date_created`),
  KEY `idx_platform_tenant_log_action` (`action`),
  CONSTRAINT `fk_platform_tenant_log_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `platform_tenant_tb` (`id`)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `platform_provision_job_tb` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `job_type` VARCHAR(50) NOT NULL,
  `status` ENUM('pending', 'running', 'completed', 'failed') NOT NULL DEFAULT 'pending',
  `attempt_count` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `locked_at` DATETIME DEFAULT NULL,
  `started_at` DATETIME DEFAULT NULL,
  `finished_at` DATETIME DEFAULT NULL,
  `error_message` TEXT NULL,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_platform_job_queue` (`status`, `date_created`),
  KEY `idx_platform_job_tenant` (`tenant_id`),
  CONSTRAINT `fk_platform_job_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `platform_tenant_tb` (`id`)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;