-- ============================================================
--  migration_0_1.0.sql
--  Création complète de la base SaaS (variante B — org_id)
--  UNE SEULE BASE pour tout : tables SaaS + tables métier
--  À exécuter dans phpMyAdmin sur votre base OVH
-- ============================================================

-- ============================================================
--  PARTIE 1 : TABLES SAAS (gestion des organisations)
-- ============================================================

-- Configuration globale du SaaS (1 seule ligne)
CREATE TABLE IF NOT EXISTS saas_config (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mode                ENUM('production','recette') NOT NULL DEFAULT 'recette',
    email_support       VARCHAR(200) NOT NULL DEFAULT '',
    version_app         VARCHAR(10)  NOT NULL DEFAULT '1.0',
    tarif_mensuel       DECIMAL(8,2) NOT NULL DEFAULT 29.90,
    duree_essai_jours   INT          NOT NULL DEFAULT 90,
    maintenance_active  TINYINT(1)   NOT NULL DEFAULT 0,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                        ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO saas_config (mode, email_support, version_app, tarif_mensuel, duree_essai_jours)
VALUES ('recette', 'VOTRE_EMAIL_SUPPORT', '1.0', 29.90, 90);

-- Maintenances planifiées
CREATE TABLE IF NOT EXISTS saas_maintenances (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    debut       DATETIME     NOT NULL,
    fin         DATETIME     NOT NULL,
    message     TEXT         NOT NULL,
    actif       TINYINT(1)   NOT NULL DEFAULT 1,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_debut_fin (debut, fin),
    INDEX idx_actif (actif)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Paiements
CREATE TABLE IF NOT EXISTS paiements (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id              INT UNSIGNED    NOT NULL,
    stripe_invoice_id   VARCHAR(100)    NOT NULL UNIQUE,
    montant             DECIMAL(8,2)    NOT NULL,
    statut              ENUM('paye','echoue','rembourse') NOT NULL DEFAULT 'paye',
    periode_debut       DATE            NOT NULL,
    periode_fin         DATE            NOT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE,
    INDEX idx_org_id (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Réponses questionnaire profil
CREATE TABLE IF NOT EXISTS org_profil_questions (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id          INT UNSIGNED    NOT NULL,
    question_key    VARCHAR(100)    NOT NULL,
    question_label  VARCHAR(500)    NOT NULL,
    reponse         TEXT            DEFAULT NULL,
    updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                    ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_org_question (org_id, question_key),
    FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mots sensibles
CREATE TABLE IF NOT EXISTS org_mots_sensibles (
    id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id  INT UNSIGNED    NOT NULL,
    mot     VARCHAR(200)    NOT NULL,
    action  ENUM('alerter','supprimer') NOT NULL DEFAULT 'alerter',
    actif   TINYINT(1)      NOT NULL DEFAULT 1,
    FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE,
    INDEX idx_org_actif (org_id, actif)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Thèmes
CREATE TABLE IF NOT EXISTS fb_themes (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id          INT UNSIGNED    NOT NULL,
    libelle         VARCHAR(100)    NOT NULL,
    couleur_fond    CHAR(7)         NOT NULL DEFAULT '#4A90D9',
    couleur_texte   CHAR(7)         NOT NULL DEFAULT '#FFFFFF',
    gras            TINYINT(1)      NOT NULL DEFAULT 0,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_org_libelle (org_id, libelle),
    FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE,
    INDEX idx_org_id (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sujets de publication
CREATE TABLE IF NOT EXISTS fb_sujets (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id              INT UNSIGNED    NOT NULL,
    id_theme            INT UNSIGNED    NOT NULL,
    libelle             VARCHAR(255)    NOT NULL,
    objectif            TEXT            DEFAULT NULL,
    conseils            TEXT            DEFAULT NULL,
    texte_post          TEXT            DEFAULT NULL,
    proposition_a       TEXT            DEFAULT NULL,
    proposition_b       TEXT            DEFAULT NULL,
    proposition_c       TEXT            DEFAULT NULL,
    proposition_choisie TINYINT UNSIGNED DEFAULT NULL,
    photo_video         VARCHAR(512)    DEFAULT NULL,
    hashtags            TEXT            DEFAULT NULL,
    memo_actions        TEXT            DEFAULT NULL,
    brouillon           TINYINT(1)      NOT NULL DEFAULT 1,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                        ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id)     REFERENCES organisations(id) ON DELETE CASCADE,
    FOREIGN KEY (id_theme)   REFERENCES fb_themes(id),
    INDEX idx_org_id (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Publications prévisionnelles
CREATE TABLE IF NOT EXISTS fb_publications_prev (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id      INT UNSIGNED    NOT NULL,
    id_sujet    INT UNSIGNED    NOT NULL,
    date_prevue DATE            NOT NULL,
    statut      ENUM('planifiee','en_retard','publiee') NOT NULL DEFAULT 'planifiee',
    note        TEXT            DEFAULT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id)   REFERENCES organisations(id) ON DELETE CASCADE,
    FOREIGN KEY (id_sujet) REFERENCES fb_sujets(id),
    INDEX idx_org_date (org_id, date_prevue),
    INDEX idx_statut (statut)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Publications réelles
CREATE TABLE IF NOT EXISTS fb_posts (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id          INT UNSIGNED    NOT NULL,
    post_id         VARCHAR(64)     NOT NULL,
    id_sujet        INT UNSIGNED    DEFAULT NULL,
    message         TEXT,
    created_time    DATETIME        NOT NULL,
    permalink       VARCHAR(512)    DEFAULT NULL,
    collected_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_org_post (org_id, post_id),
    FOREIGN KEY (org_id)   REFERENCES organisations(id) ON DELETE CASCADE,
    FOREIGN KEY (id_sujet) REFERENCES fb_sujets(id) ON DELETE SET NULL,
    INDEX idx_org_id (org_id),
    INDEX idx_created (created_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Commentaires
CREATE TABLE IF NOT EXISTS fb_comments (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id              INT UNSIGNED    NOT NULL,
    comment_id          VARCHAR(64)     NOT NULL,
    post_id             VARCHAR(64)     NOT NULL,
    parent_comment_id   VARCHAR(64)     DEFAULT NULL,
    from_name           VARCHAR(255),
    from_id             VARCHAR(64),
    message             TEXT            NOT NULL,
    created_time        DATETIME        NOT NULL,
    statut              ENUM('nouveau','reponse_generee','valide','publie','ignore')
                        NOT NULL DEFAULT 'nouveau',
    collected_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_org_comment (org_id, comment_id),
    FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE,
    INDEX idx_org_statut (org_id, statut),
    INDEX idx_org_post (org_id, post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Réponses aux commentaires
CREATE TABLE IF NOT EXISTS fb_reponses (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id                  INT UNSIGNED    NOT NULL,
    comment_id              VARCHAR(64)     NOT NULL,
    reponse_auto            TEXT,
    reponse_finale          TEXT,
    genere_at               DATETIME        DEFAULT NULL,
    valide_at               DATETIME        DEFAULT NULL,
    valide_par              VARCHAR(100)    DEFAULT NULL,
    publie_at               DATETIME        DEFAULT NULL,
    fb_comment_id_reponse   VARCHAR(64)     DEFAULT NULL,
    UNIQUE KEY uk_org_comment (org_id, comment_id),
    FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE,
    INDEX idx_org_id (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Journal des collectes
CREATE TABLE IF NOT EXISTS fb_collectes (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id                  INT UNSIGNED    NOT NULL,
    collecte_at             DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    posts_trouves           INT             DEFAULT 0,
    commentaires_trouves    INT             DEFAULT 0,
    nouveaux_commentaires   INT             DEFAULT 0,
    statut                  ENUM('ok','erreur') NOT NULL DEFAULT 'ok',
    message_erreur          TEXT            DEFAULT NULL,
    FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE,
    INDEX idx_org_id (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bibliothèque photos
CREATE TABLE IF NOT EXISTS fb_photos (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id                  INT UNSIGNED    NOT NULL,
    nom                     VARCHAR(255)    NOT NULL,
    chemin                  VARCHAR(512)    NOT NULL,
    contenu                 TEXT            DEFAULT NULL,
    contextes_utilisation   TEXT            DEFAULT NULL,
    tags                    VARCHAR(500)    DEFAULT NULL,
    actif                   TINYINT(1)      NOT NULL DEFAULT 1,
    created_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE,
    INDEX idx_org_actif (org_id, actif)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bibliothèque vidéos
CREATE TABLE IF NOT EXISTS fb_videos (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id                  INT UNSIGNED    NOT NULL,
    nom                     VARCHAR(255)    NOT NULL,
    type                    ENUM('upload','youtube','vimeo') NOT NULL DEFAULT 'youtube',
    chemin_ou_url           VARCHAR(512)    NOT NULL,
    duree_secondes          INT             DEFAULT NULL,
    contenu                 TEXT            DEFAULT NULL,
    contextes_utilisation   TEXT            DEFAULT NULL,
    tags                    VARCHAR(500)    DEFAULT NULL,
    actif                   TINYINT(1)      NOT NULL DEFAULT 1,
    created_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id) REFERENCES organisations(id) ON DELETE CASCADE,
    INDEX idx_org_actif (org_id, actif)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Vérification finale
-- ============================================================
SELECT table_name, TABLE_ROWS
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY table_name;
