-- ============================================
-- Migration 0052: Creation des tables pour le calcul de l'IS
-- Date: 2026-03-20
-- ============================================
-- Description:
-- 1. Table Bareme_IS : bareme IS Maroc par annee et type (droit commun, industriel, preferentiel)
-- 2. Table Acomptes_IS : suivi des acomptes IS verses par trimestre
-- 3. Donnees pre-remplies du bareme IS 2023-2026 + cotisation minimale + taux RAS dividendes
-- ============================================

-- =============================================
-- TABLE 1 : Bareme_IS (taux IS par tranche et annee)
-- =============================================
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Bareme_IS')
BEGIN
    EXEC('
        CREATE TABLE [Bareme_IS] (
            [Id] INT IDENTITY(1,1) NOT NULL,
            [Annee] INT NOT NULL,
            [Type] INT NOT NULL DEFAULT 1,
            [TrancheMin] NUMERIC(18,2) NOT NULL DEFAULT 0,
            [TrancheMax] NUMERIC(18,2) NOT NULL DEFAULT 0,
            [Taux] NUMERIC(8,4) NOT NULL DEFAULT 0,
            CONSTRAINT [PK_Bareme_IS] PRIMARY KEY ([Id])
        )
    ');
    PRINT 'Table Bareme_IS creee';

    -- Type 1 = Droit commun
    -- Type 2 = Entreprises industrielles
    -- Type 3 = Secteurs preferentiels

    -- ========== BAREME 2023 ==========
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2023, 1, 0, 300000, 0.1250)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2023, 1, 300001, 1000000, 0.2000)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2023, 1, 1000001, 100000000, 0.2825)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2023, 1, 100000001, 999999999999, 0.3200)');

    -- ========== BAREME 2024 ==========
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2024, 1, 0, 300000, 0.1500)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2024, 1, 300001, 1000000, 0.2000)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2024, 1, 1000001, 100000000, 0.2550)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2024, 1, 100000001, 999999999999, 0.3300)');

    -- ========== BAREME 2025 ==========
    -- Droit commun
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 1, 0, 300000, 0.1750)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 1, 300001, 1000000, 0.2000)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 1, 1000001, 100000000, 0.2275)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 1, 100000001, 999999999999, 0.3400)');
    -- Entreprises industrielles
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 2, 0, 300000, 0.1750)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 2, 300001, 1000000, 0.2000)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 2, 1000001, 100000000, 0.2150)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 2, 100000001, 999999999999, 0.3400)');
    -- Secteurs preferentiels
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 3, 0, 300000, 0.1750)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 3, 300001, 100000000, 0.2000)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2025, 3, 100000001, 999999999999, 0.3400)');

    -- ========== BAREME 2026 ==========
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2026, 1, 0, 300000, 0.2000)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2026, 1, 300001, 1000000, 0.2000)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2026, 1, 1000001, 100000000, 0.2000)');
    EXEC('INSERT INTO Bareme_IS (Annee, Type, TrancheMin, TrancheMax, Taux) VALUES (2026, 1, 100000001, 999999999999, 0.3500)');

    PRINT 'Bareme IS pre-rempli (2023-2026)';
END
ELSE
BEGIN
    PRINT 'Table Bareme_IS deja existante - migration ignoree';
END

-- =============================================
-- TABLE 2 : Params_IS (cotisation minimale + taux RAS dividendes)
-- =============================================
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Params_IS')
BEGIN
    EXEC('
        CREATE TABLE [Params_IS] (
            [Id] INT IDENTITY(1,1) NOT NULL,
            [Annee] INT NOT NULL,
            [CM_TauxNormal] NUMERIC(8,4) NOT NULL DEFAULT 0.0025,
            [CM_TauxReduit] NUMERIC(8,4) NOT NULL DEFAULT 0.0015,
            [CM_Minimum] NUMERIC(12,2) NOT NULL DEFAULT 3000,
            [CM_ExonerationMois] INT NOT NULL DEFAULT 36,
            [RAS_Dividendes] NUMERIC(8,4) NOT NULL DEFAULT 0,
            CONSTRAINT [PK_Params_IS] PRIMARY KEY ([Id])
        )
    ');
    PRINT 'Table Params_IS creee';

    EXEC('INSERT INTO Params_IS (Annee, CM_TauxNormal, CM_TauxReduit, CM_Minimum, CM_ExonerationMois, RAS_Dividendes) VALUES (2023, 0.0025, 0.0015, 3000, 36, 0.1375)');
    EXEC('INSERT INTO Params_IS (Annee, CM_TauxNormal, CM_TauxReduit, CM_Minimum, CM_ExonerationMois, RAS_Dividendes) VALUES (2024, 0.0025, 0.0015, 3000, 36, 0.1375)');
    EXEC('INSERT INTO Params_IS (Annee, CM_TauxNormal, CM_TauxReduit, CM_Minimum, CM_ExonerationMois, RAS_Dividendes) VALUES (2025, 0.0025, 0.0015, 3000, 36, 0.1250)');
    EXEC('INSERT INTO Params_IS (Annee, CM_TauxNormal, CM_TauxReduit, CM_Minimum, CM_ExonerationMois, RAS_Dividendes) VALUES (2026, 0.0025, 0.0015, 3000, 36, 0.1125)');
    EXEC('INSERT INTO Params_IS (Annee, CM_TauxNormal, CM_TauxReduit, CM_Minimum, CM_ExonerationMois, RAS_Dividendes) VALUES (2027, 0.0025, 0.0015, 3000, 36, 0.1000)');

    PRINT 'Params IS pre-remplis (2023-2027)';
END
ELSE
BEGIN
    PRINT 'Table Params_IS deja existante - migration ignoree';
END

-- =============================================
-- TABLE 3 : Acomptes_IS (suivi des acomptes verses)
-- =============================================
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Acomptes_IS')
BEGIN
    EXEC('
        CREATE TABLE [Acomptes_IS] (
            [Id] INT IDENTITY(1,1) NOT NULL,
            [Annee] INT NOT NULL,
            [Trimestre] INT NOT NULL,
            [Echeance] DATE NOT NULL,
            [MontantDu] NUMERIC(12,2) NOT NULL DEFAULT 0,
            [ExcedentImpute] NUMERIC(12,2) NOT NULL DEFAULT 0,
            [CashPaye] NUMERIC(12,2) NOT NULL DEFAULT 0,
            [DatePaiement] DATE NULL,
            [Observations] VARCHAR(255) NOT NULL DEFAULT (''''),
            CONSTRAINT [PK_Acomptes_IS] PRIMARY KEY ([Id])
        )
    ');
    PRINT 'Table Acomptes_IS creee';
END
ELSE
BEGIN
    PRINT 'Table Acomptes_IS deja existante - migration ignoree';
END
