-- #migration 2026_10_13_001
-- Messagerie / fil de publications, lot 1 : parametres modifiables depuis /control (CDC § 9.2 et § 13).
--  - messaging_settings : valeurs enregistrees (cle, valeur JSON, qui, quand). Les VALEURS PAR DEFAUT vivent dans le code
--    (api/_messaging_settings.php) : sans ligne en base, la valeur par defaut s'applique.
--  - messaging_settings_log : historique de chaque modification (ancienne et nouvelle valeur, qui, quand), y compris celles des themes.
--  - post_themes : themes de publication, geres depuis /control (creer, renommer, ordonner, desactiver ; jamais supprimes :
--    des publications y feront reference). Amorce : les 6 domaines Uluana.

CREATE TABLE IF NOT EXISTS messaging_settings (
  setting_key   VARCHAR(64)  NOT NULL,
  setting_value TEXT         NOT NULL,
  updated_by    VARCHAR(64)  NULL,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messaging_settings_log (
  id          INT          NOT NULL AUTO_INCREMENT,
  setting_key VARCHAR(64)  NOT NULL,
  action      VARCHAR(20)  NOT NULL,
  old_value   TEXT         NULL,
  new_value   TEXT         NULL,
  changed_by  VARCHAR(64)  NULL,
  changed_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_key_date (setting_key, changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS post_themes (
  id         INT          NOT NULL AUTO_INCREMENT,
  label_fr   VARCHAR(80)  NOT NULL,
  label_en   VARCHAR(80)  NOT NULL,
  sort_order INT          NOT NULL DEFAULT 0,
  active     TINYINT(1)   NOT NULL DEFAULT 1,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO post_themes (label_fr, label_en, sort_order)
SELECT t.fr, t.en, t.n FROM (
  SELECT 'Leads' AS fr, 'Leads' AS en, 1 AS n UNION ALL
  SELECT 'Conversion', 'Conversion', 2 UNION ALL
  SELECT 'Offre', 'Offer', 3 UNION ALL
  SELECT 'Modèles commerciaux', 'Business models', 4 UNION ALL
  SELECT 'Image de marque', 'Brand image', 5 UNION ALL
  SELECT 'Quotidien', 'Daily routine', 6
) t
WHERE NOT EXISTS (SELECT 1 FROM post_themes);