-- ---------------------------------------------------------------------------
-- Migration 011: Lignes telephoniques Twilio
-- ---------------------------------------------------------------------------

-- Table des lignes telephoniques
CREATE TABLE IF NOT EXISTS `phone_lines` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NULL COMMENT 'NULL = ligne non assignee, disponible pour admin',
    `phone_number` VARCHAR(20) NOT NULL COMMENT 'Format E.164 ex: +33159168714',
    `friendly_name` VARCHAR(100) NULL COMMENT 'Nom lisible ex: Ligne Paris 1',
    `twilio_sid` VARCHAR(50) NULL COMMENT 'SID Twilio de la ligne',
    `country_code` VARCHAR(2) NOT NULL DEFAULT 'FR',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `monthly_cost_cents` INT NOT NULL DEFAULT 0 COMMENT 'Cout mensuel en centimes',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_phone_number` (`phone_number`),
    KEY `idx_phone_lines_user` (`user_id`),
    CONSTRAINT `fk_phone_lines_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table d'assignation bot <-> ligne
CREATE TABLE IF NOT EXISTS `bot_line_assignments` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `bot_id` INT NOT NULL,
    `line_id` INT NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_bot_line` (`bot_id`, `line_id`),
    KEY `idx_assignments_bot` (`bot_id`),
    KEY `idx_assignments_line` (`line_id`),
    CONSTRAINT `fk_assignments_bot` FOREIGN KEY (`bot_id`) REFERENCES `ai_bots` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_assignments_line` FOREIGN KEY (`line_id`) REFERENCES `phone_lines` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
