-- 1. Corrections de la table cles_repartition
ALTER TABLE `cles_repartition` 
MODIFY COLUMN `type_systeme` ENUM('tantieme','superficie','lots','fixe','custom') NOT NULL DEFAULT 'tantieme';

-- 2. Ajout des colonnes manquantes dans cles_repartition
ALTER TABLE `cles_repartition` 
ADD COLUMN `description` TEXT DEFAULT NULL AFTER `unite_base`,
ADD COLUMN `active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `description`,
ADD COLUMN `auto_calculate` TINYINT(1) NOT NULL DEFAULT 0 AFTER `active`,
ADD COLUMN `valeur_defaut` DECIMAL(10,2) DEFAULT NULL AFTER `auto_calculate`,
ADD COLUMN `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- 3. Correction de la table cles_repartition_valeurs (structure incorrecte)
DROP TABLE IF EXISTS `cles_repartition_valeurs`;
CREATE TABLE `cles_repartition_valeurs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_cle` INT NOT NULL,
  `id_lot` INT NOT NULL,
  `valeur` DECIMAL(11,4) NOT NULL DEFAULT 0.0000,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_cle_lot` (`id_cle`, `id_lot`),
  KEY `idx_val_cle` (`id_cle`),
  KEY `idx_val_lot` (`id_lot`),
  CONSTRAINT `fk_val_cle` FOREIGN KEY (`id_cle`) REFERENCES `cles_repartition` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_val_lot` FOREIGN KEY (`id_lot`) REFERENCES `lots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 4. Ajout d'index pour optimiser les performances
ALTER TABLE `cles_repartition` 
ADD INDEX `idx_cles_actives` (`id_copro`, `active`),
ADD INDEX `idx_cles_batiment` (`id_batiment`, `active`);

-- 5. Correction des colonnes existantes qui avaient des types incorrects
ALTER TABLE `cles_repartition` 
MODIFY COLUMN `description` TEXT DEFAULT NULL,
MODIFY COLUMN `active` TINYINT(1) NOT NULL DEFAULT 1,
MODIFY COLUMN `auto_calculate` TINYINT(1) NOT NULL DEFAULT 0,
MODIFY COLUMN `valeur_defaut` DECIMAL(10,2) DEFAULT NULL;
