-- Correction des ecritures non equilibrees suite aux problemes d'arrondi
-- Les differences vont jusqu'a 0.12, on prend une marge de 0.20

-- La table Ecriture n'existe que dans les bases de production
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Ecriture')
BEGIN
    PRINT 'Table Ecriture non trouvee - migration ignoree (base commun6)'
    RETURN
END

PRINT 'Correction des ecritures non equilibrees...'

-- Afficher les ecritures concernees avant correction
SELECT
    id_ecriture,
    SUM(Debit) AS TotalDebit,
    SUM(Credit) AS TotalCredit,
    SUM(Debit) - SUM(Credit) AS Difference
FROM Ecriture
GROUP BY id_ecriture
HAVING ABS(SUM(Debit) - SUM(Credit)) > 0
AND ABS(SUM(Debit) - SUM(Credit)) <= 0.20

DECLARE @nb_ecritures INT

-- Correction : ajuster la derniere ligne de chaque ecriture desequilibree
;WITH Ecritures AS (
    SELECT
        id_ecriture,
        SUM(Debit) AS TotalDebit,
        SUM(Credit) AS TotalCredit,
        SUM(Debit) - SUM(Credit) AS Difference
    FROM Ecriture
    GROUP BY id_ecriture
    HAVING ABS(SUM(Debit) - SUM(Credit)) > 0
    AND ABS(SUM(Debit) - SUM(Credit)) <= 0.20
),
DerniereLigne AS (
    SELECT e.id_ecriture, e.CodeCompte, e.Debit, e.Credit, e.nligne,
           ROW_NUMBER() OVER (PARTITION BY e.id_ecriture ORDER BY e.nligne DESC) AS rn,
           ec.Difference
    FROM Ecriture e
    JOIN Ecritures ec ON e.id_ecriture = ec.id_ecriture
)
UPDATE DerniereLigne
SET Debit = CASE
                WHEN Debit > 0 THEN Debit - Difference
                ELSE Debit
           END,
    Credit = CASE
                WHEN Credit > 0 THEN Credit + Difference
                ELSE Credit
             END
WHERE rn = 1

SET @nb_ecritures = @@ROWCOUNT
PRINT CONCAT('Nombre d''ecritures corrigees : ', @nb_ecritures)

-- Verification : afficher les ecritures encore desequilibrees (ne devrait rien retourner)
IF EXISTS (
    SELECT 1 FROM Ecriture
    GROUP BY id_ecriture
    HAVING ABS(SUM(Debit) - SUM(Credit)) > 0
    AND ABS(SUM(Debit) - SUM(Credit)) <= 0.20
)
BEGIN
    PRINT 'ATTENTION : Il reste des ecritures non equilibrees !'
    SELECT
        id_ecriture,
        SUM(Debit) AS TotalDebit,
        SUM(Credit) AS TotalCredit,
        SUM(Debit) - SUM(Credit) AS Difference
    FROM Ecriture
    GROUP BY id_ecriture
    HAVING ABS(SUM(Debit) - SUM(Credit)) > 0
    AND ABS(SUM(Debit) - SUM(Credit)) <= 0.20
END
ELSE
BEGIN
    PRINT 'Toutes les ecritures sont maintenant equilibrees.'
END
