-- ============================================
-- Migration 0002: Supprimer colonnes inutilisees
-- Date: 2026-01-06
-- ============================================
-- Suppression de Version_Analyse, date_maj (omag)
-- Suppression des colonnes varbinary inutilisees
-- ============================================

DECLARE @constraint NVARCHAR(255)

IF OBJECT_ID('omag', 'U') IS NOT NULL
BEGIN

    -- Supprimer Version_Analyse
    IF COL_LENGTH('omag', 'Version_Analyse') IS NOT NULL
    BEGIN
        SELECT @constraint = dc.name
        FROM sys.default_constraints dc
            JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
        WHERE dc.parent_object_id = OBJECT_ID('omag') AND c.name = 'Version_Analyse'

        IF @constraint IS NOT NULL
            EXEC('ALTER TABLE omag DROP CONSTRAINT ' + @constraint)

        ALTER TABLE omag DROP COLUMN Version_Analyse
    END

    -- Supprimer date_maj
    SET @constraint = NULL
    IF COL_LENGTH('omag', 'date_maj') IS NOT NULL
    BEGIN
        SELECT @constraint = dc.name
        FROM sys.default_constraints dc
            JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
        WHERE dc.parent_object_id = OBJECT_ID('omag') AND c.name = 'date_maj'

        IF @constraint IS NOT NULL
            EXEC('ALTER TABLE omag DROP CONSTRAINT ' + @constraint)

        ALTER TABLE omag DROP COLUMN date_maj
    END

END

-- ============================================
-- Suppression des colonnes varbinary inutilisees
-- ============================================

-- Agence.logo
IF OBJECT_ID('Agence', 'U') IS NOT NULL AND COL_LENGTH('Agence', 'logo') IS NOT NULL
BEGIN
    ALTER TABLE Agence DROP COLUMN logo
END

-- Conducteur.Photo
IF OBJECT_ID('Conducteur', 'U') IS NOT NULL AND COL_LENGTH('Conducteur', 'Photo') IS NOT NULL
BEGIN
    ALTER TABLE Conducteur DROP COLUMN Photo
END

-- SalleRestau.photo
IF OBJECT_ID('SalleRestau', 'U') IS NOT NULL AND COL_LENGTH('SalleRestau', 'photo') IS NOT NULL
BEGIN
    ALTER TABLE SalleRestau DROP COLUMN photo
END

-- TableRestau.photo
IF OBJECT_ID('TableRestau', 'U') IS NOT NULL AND COL_LENGTH('TableRestau', 'photo') IS NOT NULL
BEGIN
    ALTER TABLE TableRestau DROP COLUMN photo
END

-- Vehicule.photo
IF OBJECT_ID('Vehicule', 'U') IS NOT NULL AND COL_LENGTH('Vehicule', 'photo') IS NOT NULL
BEGIN
    ALTER TABLE Vehicule DROP COLUMN photo
END

-- ============================================
-- Index sur colonne 'origine' pour optimiser SP_DiversMaintenanceCommercial
-- ============================================

IF OBJECT_ID('lblivr', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_lblivr_origine' AND object_id = OBJECT_ID('lblivr'))
    CREATE NONCLUSTERED INDEX [nix_lblivr_origine] ON [lblivr] ([origine]);

IF OBJECT_ID('lfacturec', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_lfacturec_origine' AND object_id = OBJECT_ID('lfacturec'))
    CREATE NONCLUSTERED INDEX [nix_lfacturec_origine] ON [lfacturec] ([origine]);

IF OBJECT_ID('lbcomc', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_lbcomc_origine' AND object_id = OBJECT_ID('lbcomc'))
    CREATE NONCLUSTERED INDEX [nix_lbcomc_origine] ON [lbcomc] ([origine]);

IF OBJECT_ID('lbcomf', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_lbcomf_origine' AND object_id = OBJECT_ID('lbcomf'))
    CREATE NONCLUSTERED INDEX [nix_lbcomf_origine] ON [lbcomf] ([origine]);

IF OBJECT_ID('lbonrecep', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_lbonrecep_origine' AND object_id = OBJECT_ID('lbonrecep'))
    CREATE NONCLUSTERED INDEX [nix_lbonrecep_origine] ON [lbonrecep] ([origine]);

IF OBJECT_ID('ldevis', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_ldevis_origine' AND object_id = OBJECT_ID('ldevis'))
    CREATE NONCLUSTERED INDEX [nix_ldevis_origine] ON [ldevis] ([origine]);

IF OBJECT_ID('lfacturef', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_lfacturef_origine' AND object_id = OBJECT_ID('lfacturef'))
    CREATE NONCLUSTERED INDEX [nix_lfacturef_origine] ON [lfacturef] ([origine]);

-- ============================================
-- Index pour optimiser SP_DiversMaintenanceCompta
-- ============================================

-- Ecriture.origin (UPDATE WHERE origin='n_1')
IF OBJECT_ID('Ecriture', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_ecriture_origin' AND object_id = OBJECT_ID('Ecriture'))
    CREATE NONCLUSTERED INDEX [nix_ecriture_origin] ON [Ecriture] ([origin]);

-- Ecriture (CodeJournal, Annee, nligne) pour les mises à jour AN
IF OBJECT_ID('Ecriture', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_ecriture_journal_annee_nligne' AND object_id = OBJECT_ID('Ecriture'))
    CREATE NONCLUSTERED INDEX [nix_ecriture_journal_annee_nligne] ON [Ecriture] ([CodeJournal], [Annee], [nligne]);

-- ltva_reglements pour jointures TVA achats
IF OBJECT_ID('ltva_reglements', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_ltva_reglements_ntva_achats' AND object_id = OBJECT_ID('ltva_reglements'))
    CREATE NONCLUSTERED INDEX [nix_ltva_reglements_ntva_achats] ON [ltva_reglements] ([ntva_achats], [nligne_tva_achats]);

IF OBJECT_ID('ltva_reglements', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_ltva_reglements_ntva_reglements' AND object_id = OBJECT_ID('ltva_reglements'))
    CREATE NONCLUSTERED INDEX [nix_ltva_reglements_ntva_reglements] ON [ltva_reglements] ([ntva_reglements]);

-- ltva_encaissements pour jointures TVA ventes
IF OBJECT_ID('ltva_encaissements', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_ltva_encaissements_ntva_ventes' AND object_id = OBJECT_ID('ltva_encaissements'))
    CREATE NONCLUSTERED INDEX [nix_ltva_encaissements_ntva_ventes] ON [ltva_encaissements] ([ntva_ventes], [nligne_tva_ventes]);

IF OBJECT_ID('ltva_encaissements', 'U') IS NOT NULL AND NOT EXISTS (SELECT 1
    FROM sys.indexes
    WHERE name = 'nix_ltva_encaissements_ntva_encaissements' AND object_id = OBJECT_ID('ltva_encaissements'))
    CREATE NONCLUSTERED INDEX [nix_ltva_encaissements_ntva_encaissements] ON [ltva_encaissements] ([ntva_encaissements]);
