-- Migration: Nettoyage donnees paie (corrections ponctuelles)
-- Date: 2026-02-11
-- Note: Utilisation de SQL dynamique (EXEC) pour eviter les erreurs de
--       metadonnees en cache quand les colonnes temporelles (SysStartTime/SysEndTime)
--       ou d'autres colonnes sont supprimees dans le meme batch de maintenance

-- Supprimer le trigger qui reference des colonnes obsoletes (tolerance, Repos, etc.)
-- Necessaire si la migration 0029 a ete appliquee sans cette correction
IF EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trigger_Option_paie_Journal')
BEGIN
    DROP TRIGGER trigger_Option_paie_Journal
    PRINT 'Trigger trigger_Option_paie_Journal supprime'
END

-- Ignorer sur commun6 (base de parametrage)
IF DB_NAME() LIKE '%commun6'
BEGIN
    PRINT 'Base commun6 detectee - migration ignoree';
    RETURN;
END

-- Calculer le nom de la base commun6
DECLARE @dbName NVARCHAR(100) = DB_NAME()
DECLARE @prefixe NVARCHAR(100) = ''
DECLARE @dbCommun6 NVARCHAR(100)
DECLARE @dbCurrent NVARCHAR(100) = DB_NAME()
DECLARE @sql NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)

IF @dbName LIKE 'omag_[0-9]%'
BEGIN
    DECLARE @secondUnderscore INT = CHARINDEX('_', @dbName, 6)
    IF @secondUnderscore > 0
        SET @prefixe = LEFT(@dbName, @secondUnderscore)
END

SET @dbCommun6 = @prefixe + 'commun6'

-- ============================================================================
-- Nettoyage contrat: Correction date manquante
-- ============================================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'contrat')
BEGIN
    EXEC('UPDATE contrat SET date = DateDu WHERE date IS NULL');
    IF @@ROWCOUNT > 0 PRINT CONCAT('contrat.date corrigees: ', @@ROWCOUNT);
END

-- ============================================================================
-- Nettoyage lcalcule: TypePaie invalide + matricule vide
-- ============================================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'lcalcule')
BEGIN
    EXEC('UPDATE lcalcule SET TypePaie = 1 WHERE TypePaie NOT IN (1, 2, 3) OR TypePaie = 0');
    IF @@ROWCOUNT > 0 PRINT CONCAT('lcalcule.TypePaie corriges: ', @@ROWCOUNT);

    EXEC('DELETE FROM lcalcule WHERE Matricule = ''''');
    IF @@ROWCOUNT > 0 PRINT CONCAT('lcalcule sans matricule supprimes: ', @@ROWCOUNT);
END

-- ============================================================================
-- Nettoyage Employe: Corrections multiples
-- ============================================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Employe')
BEGIN
    -- Corrections consolidees
    EXEC('UPDATE Employe SET
        Codebare = CASE WHEN Codebare = '''' THEN Matricule ELSE Codebare END,
        date_construction = CASE WHEN date_construction = ''1900-01-01'' THEN NULL ELSE date_construction END,
        date_permise_hab = CASE WHEN date_permise_hab = ''1900-01-01'' THEN NULL ELSE date_permise_hab END,
        TypePaie = CASE WHEN TypePaie NOT IN (1, 2, 3) THEN 1 ELSE TypePaie END,
        SituationFamiliale = CASE WHEN SituationFamiliale IN ('''', ''0'', ''-1'') THEN ''C'' ELSE SituationFamiliale END,
        Nom = LTRIM(RTRIM(Nom)),
        Prenom = LTRIM(RTRIM(Prenom)),
        Adresse = LTRIM(RTRIM(Adresse)),
        Note = CASE WHEN Note LIKE ''%{\%'' THEN '''' ELSE Note END,
        titre = CASE
            WHEN titre = ''1'' THEN ''Monsieur''
            WHEN titre = ''2'' THEN ''Madame''
            WHEN titre = ''3'' THEN ''Mademoiselle''
            ELSE titre
        END
    WHERE Codebare = ''''
        OR date_construction = ''1900-01-01''
        OR date_permise_hab = ''1900-01-01''
        OR TypePaie NOT IN (1, 2, 3)
        OR SituationFamiliale IN ('''', ''0'', ''-1'')
        OR Nom <> LTRIM(RTRIM(Nom))
        OR Prenom <> LTRIM(RTRIM(Prenom))
        OR Adresse <> LTRIM(RTRIM(Adresse))
        OR Note LIKE ''%{\%''
        OR titre IN (''1'', ''2'', ''3'')');
    IF @@ROWCOUNT > 0 PRINT CONCAT('Employe corriges: ', @@ROWCOUNT);

    -- Correction references invalides (departement/site inexistants)
    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'affaire')
    BEGIN
        EXEC('UPDATE Employe SET Id_departemet = 0
        WHERE Id_departemet <> 0
            AND NOT EXISTS (SELECT 1 FROM affaire WHERE affaire.affaire_id = Employe.Id_departemet)');
        IF @@ROWCOUNT > 0 PRINT CONCAT('Employe.Id_departemet corriges: ', @@ROWCOUNT);
    END

    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'site')
    BEGIN
        EXEC('UPDATE Employe SET Id_site = 0
        WHERE Id_site <> 0
            AND NOT EXISTS (SELECT 1 FROM site WHERE site.Id_site = Employe.Id_site)');
        IF @@ROWCOUNT > 0 PRINT CONCAT('Employe.Id_site corriges: ', @@ROWCOUNT);
    END

    -- Suppression employes sans matricule
    EXEC('DELETE FROM Employe WHERE Matricule = ''''');
    IF @@ROWCOUNT > 0 PRINT CONCAT('Employe sans matricule supprimes: ', @@ROWCOUNT);

    -- Insertion employes manquants (presents dans lcalcule mais pas dans Employe)
    EXEC('INSERT INTO Employe (Matricule)
    SELECT DISTINCT L.Matricule
    FROM lcalcule L
    WHERE NOT EXISTS (SELECT 1 FROM Employe E WHERE E.Matricule = L.Matricule)');
    IF @@ROWCOUNT > 0 PRINT CONCAT('Employe manquants inseres: ', @@ROWCOUNT);
END

-- ============================================================================
-- Options paie: Activation cumul anciennete
-- ============================================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Option_paie')
BEGIN
    EXEC('UPDATE Option_paie SET cumul_anc = 1 WHERE calcule_anciennete = 2');
    IF @@ROWCOUNT > 0 PRINT CONCAT('Option_paie.cumul_anc actives: ', @@ROWCOUNT);
END

-- ============================================================================
-- Correction Calcul: Annee depuis DateAu
-- ============================================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Calcul')
BEGIN
    EXEC('UPDATE Calcul SET Annee = ISNULL(YEAR(DateAu), 0)
    WHERE Annee IS NULL OR Annee <> ISNULL(YEAR(DateAu), 0)');
    IF @@ROWCOUNT > 0 PRINT CONCAT('Calcul.Annee corriges: ', @@ROWCOUNT);
END

-- ============================================================================
-- Statuts contrat: Initialisation donnees de reference
-- ============================================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Statut_contrat')
BEGIN
    EXEC('MERGE INTO Statut_contrat AS target
    USING (VALUES (1, ''Ouvert''), (2, ''STC'')) AS source (statut_id, Libelle)
    ON target.statut_id = source.statut_id
    WHEN NOT MATCHED THEN
        INSERT (statut_id, Libelle) VALUES (source.statut_id, source.Libelle);');
    IF @@ROWCOUNT > 0 PRINT CONCAT('Statut_contrat inseres: ', @@ROWCOUNT);
END

-- ============================================================================
-- Nettoyage RsCalcul: Suppression lignes vides
-- ============================================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'RsCalcul')
BEGIN
    EXEC('DELETE FROM RsCalcul
    WHERE Gain = 0 AND Retenu = 0 AND MontantPatronal = 0 AND base = 0 AND taux = 0');
    IF @@ROWCOUNT > 0 PRINT CONCAT('RsCalcul vides supprimes: ', @@ROWCOUNT);
END

-- ============================================================================
-- Harmonisation codes CNSS (damancom)
-- ============================================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Employe')
BEGIN
    EXEC('UPDATE Employe SET situation_cnss =
        CASE situation_cnss
            WHEN ''MT'' THEN ''IT''
            WHEN ''ML'' THEN ''IL''
            WHEN ''CO'' THEN ''CS''
        END
    WHERE situation_cnss IN (''MT'', ''ML'', ''CO'')');
    IF @@ROWCOUNT > 0 PRINT CONCAT('Employe.situation_cnss convertis: ', @@ROWCOUNT);
END

-- ============================================================================
-- Nettoyage Interventions (Atelier)
-- ============================================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Interventions')
   AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Interventions' AND COLUMN_NAME = 'id_equipement')
BEGIN
    EXEC('DELETE FROM Interventions
    WHERE (id_equipement IS NULL OR id_equipement = '''') AND id_technicien = 0');
    IF @@ROWCOUNT > 0 PRINT CONCAT('Interventions vides supprimees: ', @@ROWCOUNT);
END

