-- ---------------------------------------------------------------------------
-- Factory-New-Prod — Schéma initial (Chapitre 0.2.0)
-- MariaDB 10.11 | utf8mb4_unicode_ci
-- Généré depuis database_schema.json — ENUM, FK, Index stricts
-- ---------------------------------------------------------------------------

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------------
-- BLOC: INTERNATIONALIZATION (i18n_dictionary)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `i18n_dictionary` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `key_code` VARCHAR(255) NOT NULL COMMENT 'Format: FR_1_XXXX',
    `lang_code` VARCHAR(5) NOT NULL COMMENT 'FR, US, ES',
    `content_text` TEXT NULL,
    `server_region_id` VARCHAR(50) NULL COMMENT 'Sharding/Cache local',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_i18n_key_lang` (`key_code`, `lang_code`),
    KEY `idx_i18n_key_code` (`key_code`),
    KEY `idx_i18n_lang_code` (`lang_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- BLOC: USERS_AND_BILLING
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `default_lang_code` VARCHAR(5) NOT NULL DEFAULT 'FR',
    `default_currency` VARCHAR(3) NOT NULL DEFAULT 'EUR',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_users_email` (`email`),
    KEY `idx_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `subscriptions` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name_i18n_key` VARCHAR(255) NOT NULL COMMENT 'FK logique i18n_dictionary',
    `max_bots` INT NOT NULL,
    `api_credit_limit_cents` INT NOT NULL DEFAULT 50 COMMENT 'Seuil de sécurité',
    `price_monthly` DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_subscriptions_name_i18n_key` (`name_i18n_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_subscriptions` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `subscription_id` INT NOT NULL,
    `status` VARCHAR(50) NOT NULL COMMENT 'active, expired, canceled',
    `expires_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `idx_user_subscriptions_user_id` (`user_id`),
    KEY `idx_user_subscriptions_subscription_id` (`subscription_id`),
    CONSTRAINT `fk_user_subscriptions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_user_subscriptions_subscription` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- BLOC: ARTIFICIAL_INTELLIGENCE
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_bots` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `ai_model` ENUM('gpt-4', 'gpt-3.5', 'gemini-pro') NOT NULL,
    `system_prompt_key` VARCHAR(255) NOT NULL COMMENT 'FK logique i18n_dictionary',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    KEY `idx_ai_bots_user_id` (`user_id`),
    KEY `idx_ai_bots_system_prompt_key` (`system_prompt_key`),
    CONSTRAINT `fk_ai_bots_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ai_rules` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `bot_id` INT NOT NULL,
    `priority` INT NOT NULL COMMENT 'Ordre d''exécution',
    `trigger_type` VARCHAR(100) NOT NULL COMMENT 'Keyword, Sentiment, DTMF',
    `instruction_i18n_key` VARCHAR(255) NOT NULL COMMENT 'FK logique i18n_dictionary',
    `external_api_id` INT NULL COMMENT 'Liaison outils tiers',
    PRIMARY KEY (`id`),
    KEY `idx_ai_rules_bot_id` (`bot_id`),
    KEY `idx_ai_rules_instruction_i18n_key` (`instruction_i18n_key`),
    CONSTRAINT `fk_ai_rules_bot` FOREIGN KEY (`bot_id`) REFERENCES `ai_bots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `api_credentials` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `ai_rules` INT NOT NULL,
    `service_name` VARCHAR(100) NOT NULL COMMENT 'OpenAI, Google, etc.',
    `encrypted_key` TEXT NOT NULL COMMENT 'AES-256 encrypted',
    `is_admin_managed` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_api_credentials_ai_rules` (`ai_rules`),
    CONSTRAINT `fk_api_credentials_ai_rules` FOREIGN KEY (`ai_rules`) REFERENCES `ai_rules` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- BLOC: TELEPHONY_REGISTRY
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `phone_registry` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `full_number` VARCHAR(20) NOT NULL COMMENT 'E.164',
    `country_code` VARCHAR(5) NOT NULL,
    `local_number` VARCHAR(15) NOT NULL,
    `detected_country_iso` CHAR(2) NULL,
    `edited_country_iso` CHAR(2) NULL,
    `line_status` ENUM('AVAILABLE', 'ASSIGNED', 'MAINTENANCE') NOT NULL,
    `server_region_id` VARCHAR(50) NULL COMMENT 'Ping Optimization',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_phone_registry_full_number` (`full_number`),
    KEY `idx_phone_registry_full_number` (`full_number`),
    KEY `idx_phone_registry_line_status` (`line_status`),
    KEY `idx_phone_registry_country` (`country_code`, `edited_country_iso`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bot_line_assignments` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `bot_id` INT NOT NULL,
    `line_id` INT NOT NULL,
    `webhook_url` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_bot_line_assignments_line_id` (`line_id`),
    KEY `idx_bot_line_assignments_bot_id` (`bot_id`),
    KEY `idx_bot_line_assignments_line_id` (`line_id`),
    CONSTRAINT `fk_bot_line_assignments_bot` FOREIGN KEY (`bot_id`) REFERENCES `ai_bots` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_bot_line_assignments_line` FOREIGN KEY (`line_id`) REFERENCES `phone_registry` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `call_history` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `bot_id` INT NOT NULL,
    `line_id` INT NOT NULL,
    `caller_number` VARCHAR(20) NOT NULL,
    `transcript_summary` TEXT NULL,
    `duration_seconds` INT NULL,
    `cost_api_cents` INT NULL,
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_call_history_bot_id` (`bot_id`),
    KEY `idx_call_history_line_id` (`line_id`),
    KEY `idx_call_history_caller_number` (`caller_number`),
    KEY `idx_call_history_created_at` (`created_at`),
    CONSTRAINT `fk_call_history_bot` FOREIGN KEY (`bot_id`) REFERENCES `ai_bots` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_call_history_line` FOREIGN KEY (`line_id`) REFERENCES `phone_registry` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- BLOC: VOICE_AND_LATENCY
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `voice_profiles` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `bot_id` INT NOT NULL,
    `engine` ENUM('piper', 'kokoro', 'fish-speech') NOT NULL DEFAULT 'kokoro',
    `voice_model_id` VARCHAR(100) NULL COMMENT 'ID local Xeon',
    `speed_rate` FLOAT NOT NULL DEFAULT 1.0,
    `pitch` FLOAT NOT NULL DEFAULT 1.0,
    PRIMARY KEY (`id`),
    KEY `idx_voice_profiles_bot_id` (`bot_id`),
    CONSTRAINT `fk_voice_profiles_bot` FOREIGN KEY (`bot_id`) REFERENCES `ai_bots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `voice_fillers` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `bot_id` INT NOT NULL,
    `category` ENUM('ATTENTE', 'VERIFICATION', 'ACCUEIL') NOT NULL,
    `audio_file_path` VARCHAR(255) NOT NULL COMMENT 'Local .wav on Xeon',
    `text_content_key` VARCHAR(255) NOT NULL COMMENT 'FK logique i18n_dictionary',
    PRIMARY KEY (`id`),
    KEY `idx_voice_fillers_bot_id` (`bot_id`),
    KEY `idx_voice_fillers_text_content_key` (`text_content_key`),
    CONSTRAINT `fk_voice_fillers_bot` FOREIGN KEY (`bot_id`) REFERENCES `ai_bots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `redirection_settings` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `bot_id` INT NOT NULL,
    `target_number` VARCHAR(20) NOT NULL COMMENT 'Back-up P1',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    KEY `idx_redirection_settings_bot_id` (`bot_id`),
    CONSTRAINT `fk_redirection_settings_bot` FOREIGN KEY (`bot_id`) REFERENCES `ai_bots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
