-- Migration: Gestion des sites clients
-- Description: Ajouter code_client à Site, Id_site à bonlivr/devis, et option gestion_site_client
-- Note: Utilise SQL dynamique car ces tables n'existent pas dans commun6

-- 1. Ajouter code_client à la table Site
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Site')
   AND NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Site' AND COLUMN_NAME = 'code_client')
BEGIN
    EXEC('ALTER TABLE [Site] ADD [code_client] VARCHAR(16) NOT NULL DEFAULT ''''');
END

-- 2. Ajouter Id_site à la table bonlivr (en-tête BL)
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'bonlivr')
   AND NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'bonlivr' AND COLUMN_NAME = 'Id_site')
BEGIN
    EXEC('ALTER TABLE [bonlivr] ADD [Id_site] NUMERIC(12,0) NOT NULL DEFAULT 0');
END

-- 3. Ajouter Id_site à la table devis (en-tête Devis)
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'devis')
   AND NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'devis' AND COLUMN_NAME = 'Id_site')
BEGIN
    EXEC('ALTER TABLE [devis] ADD [Id_site] NUMERIC(12,0) NOT NULL DEFAULT 0');
END

-- 4. Ajouter Id_site à la table bcomc (en-tête Bon de commande)
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'bcomc')
   AND NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'bcomc' AND COLUMN_NAME = 'Id_site')
BEGIN
    EXEC('ALTER TABLE [bcomc] ADD [Id_site] NUMERIC(12,0) NOT NULL DEFAULT 0');
END

-- 5. Ajouter Id_site à la table facturec (en-tête Facture)
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'facturec')
   AND NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'facturec' AND COLUMN_NAME = 'Id_site')
BEGIN
    EXEC('ALTER TABLE [facturec] ADD [Id_site] NUMERIC(12,0) NOT NULL DEFAULT 0');
END

-- 6. Ajouter l'option gestion_site_client à options_commercial
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'options_commercial')
   AND NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'options_commercial' AND COLUMN_NAME = 'gestion_site_client')
BEGIN
    EXEC('ALTER TABLE [options_commercial] ADD [gestion_site_client] BIT NOT NULL DEFAULT 0');
END
