-- Migration 019 — Colonnes orders pour les commandes manuelles directes
--
-- Une commande manuelle one-shot (sans abo parent) a besoin de connaître :
--   - son client (FK clients.id)
--   - son produit (libellé libre, peut déjà exister via une migration appliquée à la main)
--   - l'utilisateur de la plateforme qui l'a créée
--   - éventuellement des notes
--
-- Migration idempotente : check de l'existence de chaque colonne avant ADD.

-- Helper inline : ajoute une colonne si elle n'existe pas

-- 1. client_id
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'client_id'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE orders ADD COLUMN client_id INT UNSIGNED NULL DEFAULT NULL COMMENT "Client rattaché (one-shot sans abo)" AFTER source_site_id',
    'SELECT "client_id déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2. customer_name
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'customer_name'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE orders ADD COLUMN customer_name VARCHAR(255) NULL DEFAULT NULL COMMENT "Nom client (one-shot)"',
    'SELECT "customer_name déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3. customer_email
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'customer_email'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE orders ADD COLUMN customer_email VARCHAR(255) NULL DEFAULT NULL COMMENT "Email client (one-shot)"',
    'SELECT "customer_email déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4. product_name (peut exister déjà ; cf code ApiController)
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'product_name'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE orders ADD COLUMN product_name VARCHAR(255) NULL DEFAULT NULL COMMENT "Libellé produit (one-shot)"',
    'SELECT "product_name déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5. wc_product_id (idem, peut déjà exister)
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'wc_product_id'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE orders ADD COLUMN wc_product_id INT UNSIGNED NULL DEFAULT NULL COMMENT "ID produit WC (sync only)"',
    'SELECT "wc_product_id déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 6. created_by_user_id
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'created_by_user_id'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE orders ADD COLUMN created_by_user_id INT UNSIGNED NULL DEFAULT NULL COMMENT "User créateur (commande manuelle)"',
    'SELECT "created_by_user_id déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7. notes
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'notes'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE orders ADD COLUMN notes TEXT NULL DEFAULT NULL COMMENT "Notes libres (commandes manuelles)"',
    'SELECT "notes déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 8. FK orders.client_id → clients.id (idempotent)
SET @fk_exists := (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'orders'
      AND CONSTRAINT_NAME = 'fk_orders_client'
      AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
SET @sql := IF(@fk_exists = 0,
    'ALTER TABLE orders ADD CONSTRAINT fk_orders_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL',
    'SELECT "FK fk_orders_client déjà présente" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
