-- ============================================================
-- Money+Xfer — Migration v2.0 — Correctifs portail partenaire
-- Exécuter sur la base de données existante
-- Compatible MySQL 5.7+ / MariaDB 10.2+
-- ============================================================

USE akuxfer;

SET @db = DATABASE();

-- ── Ajouter colonne destination dans partner_transactions ──
SET @q = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='partner_transactions' AND COLUMN_NAME='destination')=0,
  'ALTER TABLE partner_transactions ADD COLUMN destination VARCHAR(120) DEFAULT NULL AFTER region',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Ajouter colonne invoice_file si elle n'existe pas ──
SET @q2 = IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA=@db AND TABLE_NAME='partner_transactions' AND COLUMN_NAME='invoice_file')=0,
  'ALTER TABLE partner_transactions ADD COLUMN invoice_file VARCHAR(255) DEFAULT NULL',
  'SELECT 1'
);
PREPARE s FROM @q2; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Uniformiser la collation de la colonne reference dans les deux tables ──
-- (corrige l'erreur : Illegal mix of collations utf8mb4_unicode_ci / utf8mb4_0900_ai_ci)
ALTER TABLE partner_transactions
  MODIFY COLUMN reference VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE transactions
  MODIFY COLUMN reference VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- ── Synchroniser destination pour les transactions déjà assignées ──
UPDATE partner_transactions pt
JOIN transactions t ON t.reference = pt.reference
SET pt.destination = t.destination
WHERE pt.destination IS NULL OR pt.destination = '';