-- Migration 022 : colonnes pour la gestion des factures PDF
--
-- Ajoute 4 colonnes sur la table orders pour stocker les métadonnées
-- des factures PDF générées par le plugin "PDF Invoices & Packing Slips
-- for WooCommerce" (WP Overnight) sur les sites marchands.
--
-- Architecture :
--   1. Le plugin Limbus Subscriptions Sync (côté site marchand) remonte
--      à chaque sync : invoice_number, invoice_date.
--   2. Un cron côté plateforme (cron/invoices.php) télécharge le PDF binaire
--      via l'endpoint REST custom du plugin, le stocke dans
--      /storage/invoices/AAAA/MM/ et inscrit le chemin dans invoice_pdf_path.
--   3. Le téléchargement utilisateur sert ce fichier local (rapide).
--
-- Migration idempotente : utilise information_schema pour vérifier
-- l'existence des colonnes avant l'ALTER (re-exécution safe).

-- 1. invoice_number : le numéro de facture côté plugin WPO (ex: "LWP-2026-0042").
--    Différent de o.order_number qui est le n° de commande WC.
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'invoice_number'
);
SET @sql = IF(
    @col_exists = 0,
    'ALTER TABLE orders ADD COLUMN invoice_number VARCHAR(64) NULL DEFAULT NULL COMMENT "N° de facture (plugin WPO PDF Invoices)" AFTER product_name',
    'SELECT "invoice_number déjà présent" AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. invoice_date : date d'émission de la facture (peut différer de order_date
--    si le plugin génère la facture en différé).
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'invoice_date'
);
SET @sql = IF(
    @col_exists = 0,
    'ALTER TABLE orders ADD COLUMN invoice_date DATETIME NULL DEFAULT NULL COMMENT "Date émission facture" AFTER invoice_number',
    'SELECT "invoice_date déjà présent" AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. invoice_pdf_path : chemin RELATIF du PDF stocké localement.
--    Ex: "invoices/2026/05/limbuswp-2026-0042.pdf"
--    NULL si pas encore téléchargé.
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'invoice_pdf_path'
);
SET @sql = IF(
    @col_exists = 0,
    'ALTER TABLE orders ADD COLUMN invoice_pdf_path VARCHAR(255) NULL DEFAULT NULL COMMENT "Chemin relatif du PDF dans storage/" AFTER invoice_date',
    'SELECT "invoice_pdf_path déjà présent" AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. invoice_synced_at : timestamp de la dernière récup réussie du PDF.
--    NULL = jamais tenté ou échec persistant.
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'invoice_synced_at'
);
SET @sql = IF(
    @col_exists = 0,
    'ALTER TABLE orders ADD COLUMN invoice_synced_at DATETIME NULL DEFAULT NULL COMMENT "Dernière récup PDF réussie" AFTER invoice_pdf_path',
    'SELECT "invoice_synced_at déjà présent" AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index sur invoice_date pour grouper par mois rapidement dans /invoices
SET @idx_exists = (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND INDEX_NAME = 'idx_orders_invoice_date'
);
SET @sql = IF(
    @idx_exists = 0,
    'CREATE INDEX idx_orders_invoice_date ON orders (invoice_date)',
    'SELECT "idx_orders_invoice_date déjà présent" AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
