-- Table de référence des voix TTS (Piper / Kokoro) pour choix prédéfini
-- voice_id = nom du fichier .onnx sans extension (répertoire PIPER_MODELS_DIR)
CREATE TABLE IF NOT EXISTS `tts_voices` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `engine` ENUM('piper', 'kokoro') NOT NULL DEFAULT 'piper',
    `voice_id` VARCHAR(100) NOT NULL COMMENT 'ID modèle (ex. fr_FR-upmc-medium), nom fichier .onnx sans extension',
    `label` VARCHAR(120) NOT NULL COMMENT 'Libellé affiché (ex. UPMC medium)',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_tts_voices_engine_voice_id` (`engine`, `voice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Voix françaises Piper (rhasspy/piper-voices, locale fr/fr_FR)
INSERT INTO `tts_voices` (`engine`, `voice_id`, `label`) VALUES
('piper', 'fr_FR-upmc-jessica', 'UPMC (Jessica)'),
('piper', 'fr_FR-upmc-medium', 'UPMC (medium)'),
('piper', 'fr_FR-siwis-low', 'Siwis (low)'),
('piper', 'fr_FR-siwis-medium', 'Siwis (medium)'),
('piper', 'fr_FR-tom-medium', 'Tom (medium)'),
('piper', 'fr_FR-gilles-low', 'Gilles (low)'),
('piper', 'fr_FR-gilles-medium', 'Gilles (medium)'),
('piper', 'fr_FR-mls-low', 'MLS (low)'),
('piper', 'fr_FR-mls-medium', 'MLS (medium)'),
('piper', 'fr_FR-mls_1840-low', 'MLS 1840 (low)');

-- Ajout de la clé étrangère vers tts_voices sur voice_profiles
ALTER TABLE `voice_profiles`
    ADD COLUMN `tts_voice_id` INT NULL COMMENT 'Référence tts_voices (remplace voice_model_id)' AFTER `voice_model_id`,
    ADD CONSTRAINT `fk_voice_profiles_tts_voice` FOREIGN KEY (`tts_voice_id`) REFERENCES `tts_voices` (`id`) ON DELETE SET NULL;

-- Migration des données existantes : associer les profils à la voix correspondante dans tts_voices
UPDATE `voice_profiles` vp
SET vp.`tts_voice_id` = (
    SELECT tv.`id` FROM `tts_voices` tv
    WHERE tv.`voice_id` = vp.`voice_model_id` AND tv.`engine` = vp.`engine`
    LIMIT 1
)
WHERE vp.`voice_model_id` IS NOT NULL AND vp.`voice_model_id` != '';

-- Profils Piper sans correspondance → voix par défaut (UPMC Jessica)
UPDATE `voice_profiles` vp
SET vp.`tts_voice_id` = (SELECT `id` FROM `tts_voices` WHERE `voice_id` = 'fr_FR-upmc-jessica' AND `engine` = 'piper' LIMIT 1)
WHERE vp.`engine` = 'piper' AND vp.`tts_voice_id` IS NULL;

-- Kokoro / fish-speech : laisser tts_voice_id NULL (pas de référence dans tts_voices pour l’instant)
-- Suppression de l’ancienne colonne voice_model_id (optionnel : on peut la garder en dépréciée pour rétrocompat)
-- ALTER TABLE `voice_profiles` DROP COLUMN `voice_model_id`;
