-- ============================================================
-- AroGlow Core Schema — MySQL 8.0
-- Charset: utf8mb4 / Collation: utf8mb4_unicode_ci throughout
-- NOTE: Database creation and USE are handled by install.php
-- ============================================================

-- ------------------------------------------------------------
-- 1. admin_users
-- ------------------------------------------------------------
CREATE TABLE `admin_users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  `email` VARCHAR(190) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL COMMENT 'Argon2id hash',
  `role` ENUM('super_admin','support_admin','ai_engineer') NOT NULL DEFAULT 'support_admin',
  `status` ENUM('active','disabled') NOT NULL DEFAULT 'active',
  `must_reset_password` TINYINT(1) NOT NULL DEFAULT 0,
  `last_login_at` DATETIME NULL,
  `failed_login_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `locked_until` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_admin_email` (`email`),
  KEY `idx_admin_role_status` (`role`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. users (mobile app end-users, synced via Google Sign-In)
-- ------------------------------------------------------------
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `google_uid` VARCHAR(64) NOT NULL COMMENT 'Google Sign-In sub claim',
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(190) NOT NULL,
  `avatar_url` VARCHAR(500) NULL,
  `date_of_birth` DATE NULL,
  `gender` ENUM('male','female','other','undisclosed') NOT NULL DEFAULT 'undisclosed',
  `skin_type` ENUM('oily','dry','combination','normal','sensitive','unknown') NOT NULL DEFAULT 'unknown',
  `status` ENUM('active','blocked','deleted') NOT NULL DEFAULT 'active',
  `blocked_at` DATETIME NULL,
  `blocked_reason` VARCHAR(255) NULL,
  `daily_ai_quota` SMALLINT UNSIGNED NOT NULL DEFAULT 20 COMMENT 'Monetization: max AI calls/day',
  `last_login_at` DATETIME NULL,
  `last_active_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_google_uid` (`google_uid`),
  UNIQUE KEY `uq_users_email` (`email`),
  KEY `idx_users_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. ai_models (dynamic Gemini model registry)
-- ------------------------------------------------------------
CREATE TABLE `ai_models` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `model_identifier` VARCHAR(80) NOT NULL COMMENT 'e.g. gemini-2.0-flash',
  `display_label` VARCHAR(120) NOT NULL,
  `max_output_tokens` INT UNSIGNED NOT NULL DEFAULT 2048,
  `default_temperature` DECIMAL(4,2) NOT NULL DEFAULT 0.40 COMMENT '0.00 - 2.00',
  `cost_tier` ENUM('free','standard','premium') NOT NULL DEFAULT 'standard',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `supports_vision` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_model_identifier` (`model_identifier`),
  KEY `idx_model_active_default` (`is_active`, `is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. gemini_api_keys (multi-key pool with priority + status)
-- ------------------------------------------------------------
CREATE TABLE `gemini_api_keys` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(100) NOT NULL,
  `api_key_encrypted` VARBINARY(1024) NOT NULL COMMENT 'AES-256-GCM encrypted at rest',
  `key_last4` CHAR(4) NOT NULL COMMENT 'For masked display: sk-...xxxx',
  `model_id` INT UNSIGNED NULL COMMENT 'NULL = usable across all models',
  `priority` SMALLINT UNSIGNED NOT NULL DEFAULT 100 COMMENT 'Lower = tried first',
  `status` ENUM('active','exhausted','dead','disabled') NOT NULL DEFAULT 'active',
  `requests_today` INT UNSIGNED NOT NULL DEFAULT 0,
  `requests_total` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `last_used_at` DATETIME NULL,
  `last_error_code` VARCHAR(10) NULL,
  `last_error_at` DATETIME NULL,
  `quota_reset_at` DATETIME NULL COMMENT 'When provider-side quota is expected to reset',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_keys_priority_status` (`status`, `priority`),
  CONSTRAINT `fk_keys_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. gemini_system_prompts (versioned prompt history)
-- ------------------------------------------------------------
CREATE TABLE `gemini_system_prompts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `model_id` INT UNSIGNED NOT NULL,
  `version` INT UNSIGNED NOT NULL,
  `prompt_text` MEDIUMTEXT NOT NULL,
  `temperature_override` DECIMAL(4,2) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 0,
  `created_by_admin_id` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_model_version` (`model_id`, `version`),
  KEY `idx_prompt_active` (`model_id`, `is_active`),
  CONSTRAINT `fk_prompt_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_prompt_admin` FOREIGN KEY (`created_by_admin_id`) REFERENCES `admin_users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 6. gemini_failover_logs (dedicated failover audit trail)
-- ------------------------------------------------------------
CREATE TABLE `gemini_failover_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` CHAR(36) NOT NULL COMMENT 'UUID correlating to the originating API request',
  `model_id` INT UNSIGNED NOT NULL,
  `failed_key_id` INT UNSIGNED NULL,
  `fallback_key_id` INT UNSIGNED NULL,
  `error_code` VARCHAR(10) NOT NULL COMMENT 'e.g. 429, 401, 403, 500',
  `error_message` TEXT NULL,
  `resolved` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 if a fallback key succeeded',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_failover_model_created` (`model_id`, `created_at`),
  CONSTRAINT `fk_failover_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_failover_failed_key` FOREIGN KEY (`failed_key_id`) REFERENCES `gemini_api_keys`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_failover_fallback_key` FOREIGN KEY (`fallback_key_id`) REFERENCES `gemini_api_keys`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. chat_sessions
-- ------------------------------------------------------------
CREATE TABLE `chat_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_uuid` CHAR(36) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(160) NULL COMMENT 'Auto-generated from first message',
  `model_id` INT UNSIGNED NULL COMMENT 'Model used for this session',
  `status` ENUM('active','archived') NOT NULL DEFAULT 'active',
  `last_message_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_session_uuid` (`session_uuid`),
  KEY `idx_sessions_user_updated` (`user_id`, `updated_at`),
  CONSTRAINT `fk_session_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_session_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. chat_messages
-- ------------------------------------------------------------
CREATE TABLE `chat_messages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_id` BIGINT UNSIGNED NOT NULL,
  `sender` ENUM('user','ai','system') NOT NULL,
  `message_text` MEDIUMTEXT NULL,
  `image_path` VARCHAR(500) NULL COMMENT 'Relative path under non-webroot storage',
  `image_thumb_path` VARCHAR(500) NULL,
  `structured_metadata` JSON NULL COMMENT 'AI response metadata: condition, confidence, severity, tokens_used',
  `gemini_key_id` INT UNSIGNED NULL COMMENT 'Which key served this AI response',
  `model_id` INT UNSIGNED NULL,
  `latency_ms` INT UNSIGNED NULL,
  `tokens_used` INT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_messages_session_created` (`session_id`, `created_at`),
  CONSTRAINT `fk_message_session` FOREIGN KEY (`session_id`) REFERENCES `chat_sessions`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_message_key` FOREIGN KEY (`gemini_key_id`) REFERENCES `gemini_api_keys`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_message_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. skin_scans
-- ------------------------------------------------------------
CREATE TABLE `skin_scans` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `source_message_id` BIGINT UNSIGNED NULL COMMENT 'Originating chat_messages row, if scan came from chat',
  `image_path` VARCHAR(500) NOT NULL,
  `image_thumb_path` VARCHAR(500) NULL,
  `condition_name` VARCHAR(150) NOT NULL,
  `condition_category` ENUM('acne','pigmentation','eczema','psoriasis','rosacea','aging','allergy','infection','other') NOT NULL DEFAULT 'other',
  `confidence_score` DECIMAL(5,2) NOT NULL COMMENT '0.00 - 100.00',
  `severity_score` TINYINT UNSIGNED NOT NULL COMMENT '1 (mild) - 10 (severe)',
  `dos` JSON NULL COMMENT 'Array of recommended actions',
  `donts` JSON NULL COMMENT 'Array of actions to avoid',
  `safe_ingredients` JSON NULL COMMENT 'Array of ingredient names safe for this condition',
  `raw_ai_response` MEDIUMTEXT NULL COMMENT 'Full raw Gemini response for audit/debug',
  `model_id` INT UNSIGNED NULL,
  `gemini_key_id` INT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_scans_user_created` (`user_id`, `created_at`),
  KEY `idx_scans_category_severity` (`condition_category`, `severity_score`),
  CONSTRAINT `fk_scan_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_scan_message` FOREIGN KEY (`source_message_id`) REFERENCES `chat_messages`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_scan_model` FOREIGN KEY (`model_id`) REFERENCES `ai_models`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_scan_key` FOREIGN KEY (`gemini_key_id`) REFERENCES `gemini_api_keys`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 10. system_logs
-- ------------------------------------------------------------
CREATE TABLE `system_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `log_type` ENUM('api_error','failover','admin_action','auth_event','ai_call','rate_limit') NOT NULL,
  `severity` ENUM('info','warning','error','critical') NOT NULL DEFAULT 'info',
  `message` VARCHAR(500) NOT NULL,
  `context` JSON NULL COMMENT 'Structured payload: request_id, stack_trace, IP, admin_id, etc.',
  `user_id` BIGINT UNSIGNED NULL,
  `admin_id` BIGINT UNSIGNED NULL,
  `ip_address` VARCHAR(45) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_logs_type_severity_created` (`log_type`, `severity`, `created_at`),
  CONSTRAINT `fk_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_logs_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 11. admin_actions (immutable audit trail)
-- ------------------------------------------------------------
CREATE TABLE `admin_actions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` BIGINT UNSIGNED NOT NULL,
  `action` VARCHAR(100) NOT NULL COMMENT 'e.g. user.block, key.add, model.set_default',
  `target_type` VARCHAR(50) NULL COMMENT 'e.g. users, gemini_api_keys',
  `target_id` BIGINT UNSIGNED NULL,
  `before_state` JSON NULL,
  `after_state` JSON NULL,
  `ip_address` VARCHAR(45) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_actions_admin_created` (`admin_id`, `created_at`),
  KEY `idx_actions_target` (`target_type`, `target_id`),
  CONSTRAINT `fk_actions_admin` FOREIGN KEY (`admin_id`) REFERENCES `admin_users`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 12. jwt_blacklist (logout / forced-invalidation support)
-- ------------------------------------------------------------
CREATE TABLE `jwt_blacklist` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `jti` CHAR(36) NOT NULL COMMENT 'JWT ID claim',
  `expires_at` DATETIME NOT NULL COMMENT 'Mirrors token exp for cleanup jobs',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_jti` (`jti`),
  KEY `idx_blacklist_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Default AI model
INSERT INTO `ai_models` (`model_identifier`, `display_label`, `max_output_tokens`, `default_temperature`, `cost_tier`, `is_active`, `is_default`, `supports_vision`)
VALUES ('gemini-2.0-flash', 'Gemini 2.0 Flash', 8192, 0.40, 'standard', 1, 1, 1);

INSERT INTO `ai_models` (`model_identifier`, `display_label`, `max_output_tokens`, `default_temperature`, `cost_tier`, `is_active`, `is_default`, `supports_vision`)
VALUES ('gemini-2.5-flash', 'Gemini 2.5 Flash', 8192, 0.35, 'standard', 1, 0, 1);

-- Default system prompt for the default model
INSERT INTO `gemini_system_prompts` (`model_id`, `version`, `prompt_text`, `is_active`, `created_by_admin_id`)
VALUES (1, 1, 'You are AroGlow AI, a professional dermatology assistant. Analyze skin concerns and images with clinical accuracy.

When analyzing a skin condition or image, always include a structured section in your response using the following format:

<<<STRUCTURED>>>
{
  "condition_name": "Name of the condition",
  "category": "One of: acne, pigmentation, eczema, psoriasis, rosacea, aging, allergy, infection, other",
  "confidence_score": 0.00,
  "severity_score": 0,
  "dos": ["Recommended action 1", "Recommended action 2"],
  "donts": ["Avoid action 1", "Avoid action 2"],
  "safe_ingredients": ["Ingredient 1", "Ingredient 2"]
}
<<<END>>>

Always be empathetic, professional, and clear. Recommend seeing a dermatologist for serious concerns. Never diagnose with absolute certainty — always frame responses as AI-assisted analysis.', 1, NULL);

-- Default super admin (password: AroGlow@Admin2026 — must be changed on first login)
INSERT INTO `admin_users` (`name`, `email`, `password_hash`, `role`, `status`, `must_reset_password`)
VALUES ('Super Admin', 'admin@aroglow.app', '$argon2id$v=19$m=65536,t=4,p=1$YWRtaW5zYWx0MTIzNDU2Nzg5MA$K9L4T0xKhFnHJm3zLwXjHGv1E3YrEu3FxLf01T0ppFE', 'super_admin', 'active', 1);