-- Instant AI Virtuals database foundation
-- Domain: https://instantaivirtuals.com
-- Target: MySQL 8.x or MariaDB 10.4+ on cPanel
-- Import this file after selecting the database in phpMyAdmin.
-- This file intentionally does not CREATE or USE a database because cPanel
-- database names normally include the account prefix.

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `audit_logs`;
DROP TABLE IF EXISTS `mail_logs`;
DROP TABLE IF EXISTS `mail_queue`;
DROP TABLE IF EXISTS `login_attempts`;
DROP TABLE IF EXISTS `auth_tokens`;
DROP TABLE IF EXISTS `user_consents`;
DROP TABLE IF EXISTS `user_sessions`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `site_settings`;
DROP TABLE IF EXISTS `schema_meta`;

CREATE TABLE `schema_meta` (
  `meta_key` varchar(64) NOT NULL,
  `meta_value` varchar(255) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`meta_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL COMMENT 'ULID generated by the application',
  `username` varchar(32) NOT NULL,
  `email` varchar(191) DEFAULT NULL,
  `phone_e164` varchar(20) DEFAULT NULL COMMENT 'Normalized international number, for example +233241234567',
  `password_hash` varchar(255) NOT NULL,
  `display_name` varchar(100) DEFAULT NULL,
  `country_code` char(2) NOT NULL DEFAULT 'GH',
  `timezone` varchar(64) NOT NULL DEFAULT 'Africa/Accra',
  `locale` varchar(16) NOT NULL DEFAULT 'en_GH',
  `preferred_theme` enum('system','dark','light') NOT NULL DEFAULT 'system',
  `status` enum('pending','active','suspended','disabled','deleted') NOT NULL DEFAULT 'pending',
  `email_verified_at` datetime DEFAULT NULL,
  `phone_verified_at` datetime DEFAULT NULL,
  `password_changed_at` datetime DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `last_login_ip` varbinary(16) DEFAULT NULL,
  `failed_login_count` smallint unsigned NOT NULL DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_public_id` (`public_id`),
  UNIQUE KEY `uq_users_username` (`username`),
  UNIQUE KEY `uq_users_email` (`email`),
  UNIQUE KEY `uq_users_phone` (`phone_e164`),
  KEY `idx_users_status` (`status`),
  KEY `idx_users_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_sessions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `token_hash` char(64) NOT NULL COMMENT 'SHA-256 hash; never store the raw session token',
  `ip_address` varbinary(16) DEFAULT NULL,
  `user_agent` varchar(512) DEFAULT NULL,
  `device_label` varchar(120) DEFAULT NULL,
  `last_activity_at` datetime NOT NULL,
  `expires_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_sessions_token_hash` (`token_hash`),
  KEY `idx_user_sessions_user_active` (`user_id`,`revoked_at`,`expires_at`),
  KEY `idx_user_sessions_expires_at` (`expires_at`),
  CONSTRAINT `fk_user_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_consents` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `consent_type` enum('terms','privacy','age_18','responsible_use','email_marketing','sms_marketing') NOT NULL,
  `consent_version` varchar(32) NOT NULL,
  `consent_text` text NOT NULL,
  `accepted` tinyint(1) NOT NULL DEFAULT 1,
  `ip_address` varbinary(16) DEFAULT NULL,
  `user_agent` varchar(512) DEFAULT NULL,
  `accepted_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_consents_user_type` (`user_id`,`consent_type`,`accepted_at`),
  CONSTRAINT `fk_user_consents_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `auth_tokens` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `purpose` enum('email_verification','phone_verification','password_reset','phone_login') NOT NULL,
  `token_hash` char(64) NOT NULL COMMENT 'SHA-256 hash of the one-time token or code',
  `destination_hash` char(64) DEFAULT NULL COMMENT 'Optional hash of destination email or phone',
  `attempts` smallint unsigned NOT NULL DEFAULT 0,
  `max_attempts` smallint unsigned NOT NULL DEFAULT 5,
  `expires_at` datetime NOT NULL,
  `consumed_at` datetime DEFAULT NULL,
  `created_ip` varbinary(16) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_auth_tokens_hash` (`token_hash`),
  KEY `idx_auth_tokens_user_purpose` (`user_id`,`purpose`,`consumed_at`,`expires_at`),
  KEY `idx_auth_tokens_expiry` (`expires_at`),
  CONSTRAINT `fk_auth_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `login_attempts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned DEFAULT NULL,
  `identifier_type` enum('username','email','phone','unknown') NOT NULL DEFAULT 'unknown',
  `identifier_hash` char(64) NOT NULL COMMENT 'Hash the normalized identifier; do not store the raw value',
  `ip_address` varbinary(16) DEFAULT NULL,
  `user_agent` varchar(512) DEFAULT NULL,
  `was_successful` tinyint(1) NOT NULL DEFAULT 0,
  `failure_reason` varchar(64) DEFAULT NULL,
  `attempted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_login_attempts_identifier_time` (`identifier_hash`,`attempted_at`),
  KEY `idx_login_attempts_user_time` (`user_id`,`attempted_at`),
  KEY `idx_login_attempts_time` (`attempted_at`),
  CONSTRAINT `fk_login_attempts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mail_queue` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned DEFAULT NULL,
  `recipient_email` varchar(191) NOT NULL,
  `template_key` varchar(64) NOT NULL,
  `subject` varchar(191) NOT NULL,
  `payload_json` longtext DEFAULT NULL,
  `status` enum('pending','processing','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
  `priority` tinyint unsigned NOT NULL DEFAULT 5,
  `attempts` smallint unsigned NOT NULL DEFAULT 0,
  `max_attempts` smallint unsigned NOT NULL DEFAULT 3,
  `next_attempt_at` datetime DEFAULT NULL,
  `locked_at` datetime DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `failed_at` datetime DEFAULT NULL,
  `last_error` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_mail_queue_worker` (`status`,`priority`,`next_attempt_at`,`created_at`),
  KEY `idx_mail_queue_user` (`user_id`,`created_at`),
  CONSTRAINT `fk_mail_queue_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mail_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue_id` bigint unsigned DEFAULT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `recipient_hash` char(64) NOT NULL,
  `template_key` varchar(64) NOT NULL,
  `provider_message_id` varchar(191) DEFAULT NULL,
  `status` enum('sent','failed','bounced','complained') NOT NULL,
  `error_message` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_mail_logs_queue` (`queue_id`),
  KEY `idx_mail_logs_user_time` (`user_id`,`created_at`),
  KEY `idx_mail_logs_status_time` (`status`,`created_at`),
  CONSTRAINT `fk_mail_logs_queue` FOREIGN KEY (`queue_id`) REFERENCES `mail_queue` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_mail_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `site_settings` (
  `setting_key` varchar(100) NOT NULL,
  `setting_value` longtext DEFAULT NULL,
  `value_type` enum('string','integer','boolean','json') NOT NULL DEFAULT 'string',
  `is_public` tinyint(1) NOT NULL DEFAULT 0,
  `autoload` tinyint(1) NOT NULL DEFAULT 1,
  `updated_by` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`setting_key`),
  KEY `idx_site_settings_autoload` (`autoload`),
  CONSTRAINT `fk_site_settings_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audit_logs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `actor_user_id` bigint unsigned DEFAULT NULL,
  `event_type` varchar(100) NOT NULL,
  `entity_type` varchar(100) DEFAULT NULL,
  `entity_id` varchar(100) DEFAULT NULL,
  `ip_address` varbinary(16) DEFAULT NULL,
  `user_agent` varchar(512) DEFAULT NULL,
  `metadata_json` longtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_audit_logs_actor_time` (`actor_user_id`,`created_at`),
  KEY `idx_audit_logs_event_time` (`event_type`,`created_at`),
  KEY `idx_audit_logs_entity` (`entity_type`,`entity_id`),
  CONSTRAINT `fk_audit_logs_actor` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `schema_meta` (`meta_key`, `meta_value`) VALUES
  ('schema_name', 'Instant AI Virtuals Foundation'),
  ('schema_version', '1.0.0'),
  ('canonical_domain', 'instantaivirtuals.com'),
  ('created_for', 'cPanel MySQL/MariaDB');

INSERT INTO `site_settings` (`setting_key`, `setting_value`, `value_type`, `is_public`, `autoload`) VALUES
  ('site.name', 'Instant AI Virtuals', 'string', 1, 1),
  ('site.url', 'https://instantaivirtuals.com', 'string', 1, 1),
  ('site.domain', 'instantaivirtuals.com', 'string', 1, 1),
  ('site.timezone', 'Africa/Accra', 'string', 1, 1),
  ('site.locale', 'en_GH', 'string', 1, 1),
  ('site.currency', 'GHS', 'string', 1, 1),
  ('theme.default', 'system', 'string', 1, 1),
  ('theme.choices', '["system","dark","light"]', 'json', 1, 1),
  ('auth.login_identifiers', '["email","phone","username"]', 'json', 0, 1),
  ('auth.default_country', 'GH', 'string', 1, 1),
  ('auth.minimum_age', '18', 'integer', 1, 1),
  ('mail.enabled', 'false', 'boolean', 0, 1),
  ('responsible_use.predictions_guaranteed', 'false', 'boolean', 1, 1);

SET FOREIGN_KEY_CHECKS = 1;

-- Login implementation note:
-- 1. Normalize one submitted identifier.
-- 2. If it is a valid email, query LOWER(email) = LOWER(:login).
-- 3. If it matches E.164, query phone_e164 = :login.
-- 4. Otherwise query LOWER(username) = LOWER(:login).
-- 5. Always verify password_hash with password_verify(); never compare hashes in SQL.
