-- ============================================
-- Migration 0048: Module Appel d'Offre Complet
-- Date: 2026-03-08
-- ============================================
-- Ajoute les fonctionnalites completes de gestion des appels d'offres:
-- - Gestion multi-lots
-- - Cycle de vie des cautions (provisoire, definitive, retenue garantie)
-- - Suivi resultat et marche attribue
-- - Suivi execution et reglement
-- - Alertes et delais calcules
-- ============================================

-- ============================================
-- PARTIE 1: Tables de reference
-- ============================================

-- Table des statuts d'appel d'offre
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffreStatut')
BEGIN
    CREATE TABLE AppelOffreStatut (
        IDStatut INT NOT NULL,
        Libelle VARCHAR(50) NOT NULL DEFAULT '',
        Couleur VARCHAR(7) NOT NULL DEFAULT '#000000',
        Ordre INT NOT NULL DEFAULT 0,
        Actif BIT NOT NULL DEFAULT 1,
        CONSTRAINT PK_AppelOffreStatut PRIMARY KEY (IDStatut)
    );

    INSERT INTO AppelOffreStatut (IDStatut, Libelle, Couleur, Ordre) VALUES
    (1, 'Brouillon', '#9E9E9E', 1),
    (2, 'En preparation', '#2196F3', 2),
    (3, 'Soumis', '#FF9800', 3),
    (4, 'En evaluation', '#9C27B0', 4),
    (5, 'Gagne', '#4CAF50', 5),
    (6, 'Perdu', '#F44336', 6),
    (7, 'Sans suite', '#795548', 7),
    (8, 'Marche en cours', '#00BCD4', 8),
    (9, 'Livre', '#8BC34A', 9),
    (10, 'Receptionne', '#CDDC39', 10),
    (11, 'Solde', '#607D8B', 11);

    PRINT 'Table AppelOffreStatut creee avec donnees';
END
ELSE
BEGIN
    PRINT 'Table AppelOffreStatut existe deja';
END

-- Table des types de caution
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffreTypeCaution')
BEGIN
    CREATE TABLE AppelOffreTypeCaution (
        IDType INT NOT NULL,
        Libelle VARCHAR(50) NOT NULL DEFAULT '',
        TauxDefaut NUMERIC(5,2) NOT NULL DEFAULT 0,
        DelaiRestitutionJours INT NOT NULL DEFAULT 0,
        CONSTRAINT PK_AppelOffreTypeCaution PRIMARY KEY (IDType)
    );

    -- Valeurs par defaut pour le Maroc
    INSERT INTO AppelOffreTypeCaution (IDType, Libelle, TauxDefaut, DelaiRestitutionJours) VALUES
    (1, 'Caution Provisoire', 1.5, 90),
    (2, 'Caution Definitive', 3.0, 365),
    (3, 'Retenue de Garantie', 10.0, 365);

    PRINT 'Table AppelOffreTypeCaution creee avec donnees';
END
ELSE
BEGIN
    PRINT 'Table AppelOffreTypeCaution existe deja';
END

-- ============================================
-- PARTIE 2: Nouvelles colonnes sur AppelOffre
-- ============================================
-- IMPORTANT: Utilisation de SQL dynamique car la table AppelOffre
-- n'existe pas dans commun6. SQL Server compile tout avant execution.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffre')
BEGIN
    -- ----------------------------------------
    -- Identification
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'Objet')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD Objet VARCHAR(500) NOT NULL DEFAULT ''''');
        PRINT 'Colonne Objet ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'NumeroAO')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD NumeroAO VARCHAR(50) NOT NULL DEFAULT ''''');
        PRINT 'Colonne NumeroAO ajoutee';
    END

    -- ----------------------------------------
    -- Resultat
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'Resultat')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD Resultat INT NOT NULL DEFAULT 0');
        PRINT 'Colonne Resultat ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'Classement')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD Classement INT NOT NULL DEFAULT 0');
        PRINT 'Colonne Classement ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'MotifRejet')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD MotifRejet VARCHAR(500) NOT NULL DEFAULT ''''');
        PRINT 'Colonne MotifRejet ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateResultat')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateResultat DATE NULL');
        PRINT 'Colonne DateResultat ajoutee';
    END

    -- ----------------------------------------
    -- Prix et Marge
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'PrixReference')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD PrixReference NUMERIC(14,2) NOT NULL DEFAULT 0');
        PRINT 'Colonne PrixReference ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'PrixAchat')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD PrixAchat NUMERIC(14,2) NOT NULL DEFAULT 0');
        PRINT 'Colonne PrixAchat ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'OffreFinanciere')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD OffreFinanciere NUMERIC(14,2) NOT NULL DEFAULT 0');
        PRINT 'Colonne OffreFinanciere ajoutee';
    END

    -- ----------------------------------------
    -- Marche attribue
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'NumeroMarche')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD NumeroMarche VARCHAR(50) NOT NULL DEFAULT ''''');
        PRINT 'Colonne NumeroMarche ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateOrdreService')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateOrdreService DATE NULL');
        PRINT 'Colonne DateOrdreService ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateLimiteExecution')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateLimiteExecution DATE NULL');
        PRINT 'Colonne DateLimiteExecution ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DelaiExecutionJours')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DelaiExecutionJours INT NOT NULL DEFAULT 0');
        PRINT 'Colonne DelaiExecutionJours ajoutee';
    END

    -- ----------------------------------------
    -- Livraison
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'EtatLivraison')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD EtatLivraison INT NOT NULL DEFAULT 0');
        PRINT 'Colonne EtatLivraison ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'TauxLivraison')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD TauxLivraison NUMERIC(5,2) NOT NULL DEFAULT 0');
        PRINT 'Colonne TauxLivraison ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateLivraison')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateLivraison DATE NULL');
        PRINT 'Colonne DateLivraison ajoutee';
    END

    -- ----------------------------------------
    -- Reception
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateReceptionProvisoire')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateReceptionProvisoire DATE NULL');
        PRINT 'Colonne DateReceptionProvisoire ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateReceptionDefinitive')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateReceptionDefinitive DATE NULL');
        PRINT 'Colonne DateReceptionDefinitive ajoutee';
    END

    -- ----------------------------------------
    -- Reglement
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'SituationReglement')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD SituationReglement INT NOT NULL DEFAULT 0');
        PRINT 'Colonne SituationReglement ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'MontantFacture')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD MontantFacture NUMERIC(14,2) NOT NULL DEFAULT 0');
        PRINT 'Colonne MontantFacture ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'MontantRegle')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD MontantRegle NUMERIC(14,2) NOT NULL DEFAULT 0');
        PRINT 'Colonne MontantRegle ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateDernierReglement')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateDernierReglement DATE NULL');
        PRINT 'Colonne DateDernierReglement ajoutee';
    END

    -- ----------------------------------------
    -- Garantie
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DelaiGarantieJours')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DelaiGarantieJours INT NOT NULL DEFAULT 365');
        PRINT 'Colonne DelaiGarantieJours ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateFinGarantie')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateFinGarantie DATE NULL');
        PRINT 'Colonne DateFinGarantie ajoutee';
    END

    -- ----------------------------------------
    -- Workflow et audit
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateCreation')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateCreation DATETIME NOT NULL DEFAULT GETDATE()');
        PRINT 'Colonne DateCreation ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateModification')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateModification DATETIME NULL');
        PRINT 'Colonne DateModification ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'CreePar')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD CreePar INT NOT NULL DEFAULT 0');
        PRINT 'Colonne CreePar ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'ModifiePar')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD ModifiePar INT NOT NULL DEFAULT 0');
        PRINT 'Colonne ModifiePar ajoutee';
    END

    -- ----------------------------------------
    -- Observations
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'Observation')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD Observation VARCHAR(MAX) NULL');
        PRINT 'Colonne Observation ajoutee';
    END

    -- ----------------------------------------
    -- Complement / Echantillon
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateDemandeComplement')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DateDemandeComplement DATE NULL');
        PRINT 'Colonne DateDemandeComplement ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DelaiComplementJours')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD DelaiComplementJours INT NOT NULL DEFAULT 0');
        PRINT 'Colonne DelaiComplementJours ajoutee';
    END

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'ComplementFourni')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD ComplementFourni BIT NOT NULL DEFAULT 0');
        PRINT 'Colonne ComplementFourni ajoutee';
    END

    -- ----------------------------------------
    -- Reference caution provisoire (migration depuis ancien champ)
    -- ----------------------------------------
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'RefCautionProvisoire')
    BEGIN
        EXEC('ALTER TABLE AppelOffre ADD RefCautionProvisoire VARCHAR(50) NOT NULL DEFAULT ''''');
        PRINT 'Colonne RefCautionProvisoire ajoutee';
    END

    PRINT 'Colonnes AppelOffre mises a jour';
END
ELSE
BEGIN
    PRINT 'Table AppelOffre non presente - colonnes ignorees (base commun6)';
END

-- ============================================
-- PARTIE 3: Table AppelOffreLot (Multi-lots)
-- ============================================

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffreLot')
BEGIN
    CREATE TABLE AppelOffreLot (
        IDLot INT IDENTITY(1,1) NOT NULL,
        nAppelOffre NUMERIC(12,0) NOT NULL DEFAULT 0,

        -- Identification lot
        NumeroLot VARCHAR(20) NOT NULL DEFAULT '',
        DesignationLot VARCHAR(500) NOT NULL DEFAULT '',

        -- Montants
        MontantEstimatif NUMERIC(14,2) NOT NULL DEFAULT 0,
        PrixAchat NUMERIC(14,2) NOT NULL DEFAULT 0,
        OffreFinanciere NUMERIC(14,2) NOT NULL DEFAULT 0,

        -- Caution specifique au lot
        MontantCautionProvisoire NUMERIC(14,2) NOT NULL DEFAULT 0,

        -- Resultat par lot
        Resultat INT NOT NULL DEFAULT 0,
        Classement INT NOT NULL DEFAULT 0,

        -- Suivi
        EtatLivraison INT NOT NULL DEFAULT 0,
        TauxLivraison NUMERIC(5,2) NOT NULL DEFAULT 0,

        -- Observation
        Observation VARCHAR(500) NOT NULL DEFAULT '',

        CONSTRAINT PK_AppelOffreLot PRIMARY KEY (IDLot)
    );

    CREATE NONCLUSTERED INDEX nix_AppelOffreLot_nAppelOffre ON AppelOffreLot (nAppelOffre);

    PRINT 'Table AppelOffreLot creee';
END
ELSE
BEGIN
    PRINT 'Table AppelOffreLot existe deja';
END

-- ============================================
-- PARTIE 4: Table AppelOffreCaution
-- ============================================

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffreCaution')
BEGIN
    CREATE TABLE AppelOffreCaution (
        IDCaution INT IDENTITY(1,1) NOT NULL,
        nAppelOffre NUMERIC(12,0) NOT NULL DEFAULT 0,
        IDLot INT NULL,

        -- Type de caution: 1=Provisoire, 2=Definitive, 3=Retenue Garantie
        TypeCaution INT NOT NULL DEFAULT 1,

        -- Informations caution
        Montant NUMERIC(14,2) NOT NULL DEFAULT 0,
        ReferenceBancaire VARCHAR(50) NOT NULL DEFAULT '',
        Banque VARCHAR(100) NOT NULL DEFAULT '',
        DateEmission DATE NULL,
        DateExpiration DATE NULL,

        -- Cycle de vie: 1=Active, 2=Demande restitution, 3=Restituee
        Statut INT NOT NULL DEFAULT 1,
        DateDemandeRestitution DATE NULL,
        DateRestitution DATE NULL,
        MontantRestitue NUMERIC(14,2) NOT NULL DEFAULT 0,

        -- Observations
        Observation VARCHAR(500) NOT NULL DEFAULT '',

        -- Audit
        DateCreation DATETIME NOT NULL DEFAULT GETDATE(),
        CreePar INT NOT NULL DEFAULT 0,

        CONSTRAINT PK_AppelOffreCaution PRIMARY KEY (IDCaution)
    );

    CREATE NONCLUSTERED INDEX nix_AppelOffreCaution_nAppelOffre ON AppelOffreCaution (nAppelOffre);
    CREATE NONCLUSTERED INDEX nix_AppelOffreCaution_TypeCaution ON AppelOffreCaution (TypeCaution);
    CREATE NONCLUSTERED INDEX nix_AppelOffreCaution_Statut ON AppelOffreCaution (Statut);

    PRINT 'Table AppelOffreCaution creee';
END
ELSE
BEGIN
    PRINT 'Table AppelOffreCaution existe deja';
END

-- ============================================
-- PARTIE 5: Table AppelOffreAvancement
-- ============================================

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffreAvancement')
BEGIN
    CREATE TABLE AppelOffreAvancement (
        IDAvancement INT IDENTITY(1,1) NOT NULL,
        nAppelOffre NUMERIC(12,0) NOT NULL DEFAULT 0,
        IDLot INT NULL,

        -- Avancement
        DateAvancement DATE NOT NULL,
        TauxAvancement NUMERIC(5,2) NOT NULL DEFAULT 0,
        MontantAvancement NUMERIC(14,2) NOT NULL DEFAULT 0,

        -- Situation de travaux
        NumeroSituation VARCHAR(20) NOT NULL DEFAULT '',
        MontantSituation NUMERIC(14,2) NOT NULL DEFAULT 0,

        -- Observations
        Observation VARCHAR(500) NOT NULL DEFAULT '',

        -- Audit
        DateCreation DATETIME NOT NULL DEFAULT GETDATE(),
        CreePar INT NOT NULL DEFAULT 0,

        CONSTRAINT PK_AppelOffreAvancement PRIMARY KEY (IDAvancement)
    );

    CREATE NONCLUSTERED INDEX nix_AppelOffreAvancement_nAppelOffre ON AppelOffreAvancement (nAppelOffre);

    PRINT 'Table AppelOffreAvancement creee';
END
ELSE
BEGIN
    PRINT 'Table AppelOffreAvancement existe deja';
END

-- ============================================
-- PARTIE 6: Table AppelOffreEtatLivraison (Reference)
-- ============================================

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffreEtatLivraison')
BEGIN
    CREATE TABLE AppelOffreEtatLivraison (
        IDEtat INT NOT NULL,
        Libelle VARCHAR(50) NOT NULL DEFAULT '',
        Couleur VARCHAR(7) NOT NULL DEFAULT '#000000',
        CONSTRAINT PK_AppelOffreEtatLivraison PRIMARY KEY (IDEtat)
    );

    INSERT INTO AppelOffreEtatLivraison (IDEtat, Libelle, Couleur) VALUES
    (0, 'Non commence', '#9E9E9E'),
    (1, 'En cours', '#2196F3'),
    (2, 'Partiel', '#FF9800'),
    (3, 'Livre', '#4CAF50');

    PRINT 'Table AppelOffreEtatLivraison creee avec donnees';
END
ELSE
BEGIN
    PRINT 'Table AppelOffreEtatLivraison existe deja';
END

-- ============================================
-- PARTIE 7: Table AppelOffreResultat (Reference)
-- ============================================

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffreResultat')
BEGIN
    CREATE TABLE AppelOffreResultat (
        IDResultat INT NOT NULL,
        Libelle VARCHAR(50) NOT NULL DEFAULT '',
        Couleur VARCHAR(7) NOT NULL DEFAULT '#000000',
        CONSTRAINT PK_AppelOffreResultat PRIMARY KEY (IDResultat)
    );

    INSERT INTO AppelOffreResultat (IDResultat, Libelle, Couleur) VALUES
    (0, 'En cours', '#9E9E9E'),
    (1, 'Gagne', '#4CAF50'),
    (2, 'Perdu', '#F44336'),
    (3, 'Sans suite', '#795548');

    PRINT 'Table AppelOffreResultat creee avec donnees';
END
ELSE
BEGIN
    PRINT 'Table AppelOffreResultat existe deja';
END

-- ============================================
-- PARTIE 8: Table AppelOffreSituationReglement (Reference)
-- ============================================

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffreSituationReglement')
BEGIN
    CREATE TABLE AppelOffreSituationReglement (
        IDSituation INT NOT NULL,
        Libelle VARCHAR(50) NOT NULL DEFAULT '',
        Couleur VARCHAR(7) NOT NULL DEFAULT '#000000',
        CONSTRAINT PK_AppelOffreSituationReglement PRIMARY KEY (IDSituation)
    );

    INSERT INTO AppelOffreSituationReglement (IDSituation, Libelle, Couleur) VALUES
    (0, 'Non facture', '#9E9E9E'),
    (1, 'Facture', '#2196F3'),
    (2, 'Partiellement regle', '#FF9800'),
    (3, 'Solde', '#4CAF50');

    PRINT 'Table AppelOffreSituationReglement creee avec donnees';
END
ELSE
BEGIN
    PRINT 'Table AppelOffreSituationReglement existe deja';
END

-- ============================================
-- PARTIE 9: Migration donnees existantes
-- ============================================

-- Migrer la valeur de MontantCaution vers AppelOffreCaution si des donnees existent
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'MontantCaution')
   AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffreCaution')
BEGIN
    -- Inserer les cautions provisoires existantes (si montant > 0 et pas deja migre)
    -- SQL dynamique car AppelOffre peut ne pas exister dans commun6
    EXEC('
        INSERT INTO AppelOffreCaution (nAppelOffre, TypeCaution, Montant, Statut, DateCreation)
        SELECT
            ao.nAppelOffre,
            1, -- Caution Provisoire
            ao.MontantCaution,
            1, -- Active
            GETDATE()
        FROM AppelOffre ao
        WHERE ao.MontantCaution > 0
          AND NOT EXISTS (
              SELECT 1 FROM AppelOffreCaution c
              WHERE c.nAppelOffre = ao.nAppelOffre AND c.TypeCaution = 1
          )
    ');

    IF @@ROWCOUNT > 0
        PRINT 'Cautions provisoires existantes migrees vers AppelOffreCaution';
    ELSE
        PRINT 'Aucune caution provisoire a migrer';
END

-- Migrer Description vers Objet si Objet est vide
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'Description')
   AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'Objet')
BEGIN
    EXEC('
        UPDATE AppelOffre
        SET Objet = Description
        WHERE (Objet IS NULL OR Objet = '''')
          AND Description IS NOT NULL
          AND Description <> ''''
    ');

    IF @@ROWCOUNT > 0
        PRINT 'Descriptions migrees vers Objet';
END

-- Initialiser le statut a 1 (Brouillon) pour les AO sans statut valide
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'Statut')
BEGIN
    EXEC('
        UPDATE AppelOffre
        SET Statut = 1
        WHERE Statut = 0 OR Statut IS NULL
    ');

    PRINT 'Statuts initialises';
END

-- ============================================
-- PARTIE 10: Index supplementaires pour performances
-- ============================================

-- Verifier que la table AppelOffre existe avant de creer les index
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppelOffre')
BEGIN
    -- Index sur Statut pour filtrage rapide
    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'nix_AppelOffre_Statut' AND object_id = OBJECT_ID('AppelOffre'))
    BEGIN
        EXEC('CREATE NONCLUSTERED INDEX nix_AppelOffre_Statut ON AppelOffre (Statut)');
        PRINT 'Index nix_AppelOffre_Statut cree';
    END

    -- Index sur Resultat pour filtrage rapide
    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'nix_AppelOffre_Resultat' AND object_id = OBJECT_ID('AppelOffre'))
    BEGIN
        EXEC('CREATE NONCLUSTERED INDEX nix_AppelOffre_Resultat ON AppelOffre (Resultat)');
        PRINT 'Index nix_AppelOffre_Resultat cree';
    END

    -- Index sur DateDepotAvant pour les alertes
    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'nix_AppelOffre_DateDepotAvant' AND object_id = OBJECT_ID('AppelOffre'))
    BEGIN
        IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateDepotAvant')
        BEGIN
            EXEC('CREATE NONCLUSTERED INDEX nix_AppelOffre_DateDepotAvant ON AppelOffre (DateDepotAvant)');
            PRINT 'Index nix_AppelOffre_DateDepotAvant cree';
        END
    END

    -- Index sur DateLimiteExecution pour les alertes
    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'nix_AppelOffre_DateLimiteExecution' AND object_id = OBJECT_ID('AppelOffre'))
    BEGIN
        IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AppelOffre' AND COLUMN_NAME = 'DateLimiteExecution')
        BEGIN
            EXEC('CREATE NONCLUSTERED INDEX nix_AppelOffre_DateLimiteExecution ON AppelOffre (DateLimiteExecution)');
            PRINT 'Index nix_AppelOffre_DateLimiteExecution cree';
        END
    END
END
ELSE
BEGIN
    PRINT 'Table AppelOffre non presente - index ignores (base commun6)';
END

-- ============================================
-- FIN DE LA MIGRATION
-- ============================================

PRINT '========================================';
PRINT 'Migration 0048 terminee avec succes';
PRINT '========================================';
PRINT 'Tables creees:';
PRINT '  - AppelOffreStatut (reference)';
PRINT '  - AppelOffreTypeCaution (reference)';
PRINT '  - AppelOffreLot (multi-lots)';
PRINT '  - AppelOffreCaution (cycle vie cautions)';
PRINT '  - AppelOffreAvancement (suivi execution)';
PRINT '  - AppelOffreEtatLivraison (reference)';
PRINT '  - AppelOffreResultat (reference)';
PRINT '  - AppelOffreSituationReglement (reference)';
PRINT '';
PRINT 'Colonnes ajoutees a AppelOffre:';
PRINT '  - Identification: Objet, NumeroAO';
PRINT '  - Resultat: Resultat, Classement, MotifRejet, DateResultat';
PRINT '  - Prix/Marge: PrixReference, PrixAchat, OffreFinanciere';
PRINT '  - Marche: NumeroMarche, DateOrdreService, DateLimiteExecution, DelaiExecutionJours';
PRINT '  - Livraison: EtatLivraison, TauxLivraison, DateLivraison';
PRINT '  - Reception: DateReceptionProvisoire, DateReceptionDefinitive';
PRINT '  - Reglement: SituationReglement, MontantFacture, MontantRegle, DateDernierReglement';
PRINT '  - Garantie: DelaiGarantieJours, DateFinGarantie';
PRINT '  - Complement: DateDemandeComplement, DelaiComplementJours, ComplementFourni';
PRINT '  - Audit: DateCreation, DateModification, CreePar, ModifiePar, Observation';
PRINT '========================================';
