-- =====================================================================
-- Recueil ERBC – Schéma MySQL 8 (compatible MariaDB 10.5+)
-- Encodage : utf8mb4 obligatoire
-- 
-- Pour créer la base puis l'utilisateur :
-- 
--   CREATE DATABASE recueil_erbc 
--     DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
--   CREATE USER 'recueil_user'@'localhost' IDENTIFIED BY 'mot_de_passe';
--   GRANT ALL PRIVILEGES ON recueil_erbc.* TO 'recueil_user'@'localhost';
--   FLUSH PRIVILEGES;
-- 
-- Ensuite : USE recueil_erbc; SOURCE schema.sql;
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- users : admins ET membres
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name                  VARCHAR(120) NOT NULL,
    email                 VARCHAR(180) NOT NULL,
    password              VARCHAR(255) NULL,
    role                  ENUM('admin','member') NOT NULL DEFAULT 'member',
    is_active             TINYINT(1) NOT NULL DEFAULT 1,
    newsletter_opt_in     TINYINT(1) NOT NULL DEFAULT 1,
    email_verified_at     TIMESTAMP NULL DEFAULT NULL,
    invitation_token      VARCHAR(64) NULL DEFAULT NULL,
    invitation_sent_at    TIMESTAMP NULL DEFAULT NULL,
    invitation_expires_at TIMESTAMP NULL DEFAULT NULL,
    remember_token        VARCHAR(100) NULL DEFAULT NULL,
    last_login_at         TIMESTAMP NULL DEFAULT NULL,
    created_at            TIMESTAMP NULL DEFAULT NULL,
    updated_at            TIMESTAMP NULL DEFAULT NULL,
    deleted_at            TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_users_email (email),
    KEY idx_users_role_active (role, is_active),
    KEY idx_users_invitation_token (invitation_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- hymns : chants ET psaumes
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS hymns;
CREATE TABLE hymns (
    id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    number            INT UNSIGNED NOT NULL,
    type              ENUM('chant','psaume') NOT NULL DEFAULT 'chant',
    title             VARCHAR(255) NOT NULL,
    content_json      JSON NOT NULL,
    content_plain     MEDIUMTEXT NOT NULL,
    author_lyrics     VARCHAR(255) NULL DEFAULT NULL,
    author_music      VARCHAR(255) NULL DEFAULT NULL,
    copyright_notice  VARCHAR(500) NULL DEFAULT NULL,
    is_active         TINYINT(1) NOT NULL DEFAULT 1,
    created_at        TIMESTAMP NULL DEFAULT NULL,
    updated_at        TIMESTAMP NULL DEFAULT NULL,
    deleted_at        TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_hymns_number_type (number, type),
    KEY idx_hymns_type_active (type, is_active),
    FULLTEXT KEY ft_hymns_search (title, content_plain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- celebrations
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS celebrations;
CREATE TABLE celebrations (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title           VARCHAR(255) NOT NULL,
    celebrated_at   DATE NOT NULL,
    is_current      TINYINT(1) NOT NULL DEFAULT 0,
    notes           TEXT NULL DEFAULT NULL,
    created_by      BIGINT UNSIGNED NULL DEFAULT NULL,
    created_at      TIMESTAMP NULL DEFAULT NULL,
    updated_at      TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_celebrations_current (is_current),
    KEY idx_celebrations_date (celebrated_at),
    CONSTRAINT fk_celebrations_created_by FOREIGN KEY (created_by)
        REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- celebration_hymn (pivot)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS celebration_hymn;
CREATE TABLE celebration_hymn (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    celebration_id  BIGINT UNSIGNED NOT NULL,
    hymn_id         BIGINT UNSIGNED NOT NULL,
    position        INT UNSIGNED NOT NULL DEFAULT 1,
    note            VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_celeb_hymn (celebration_id, hymn_id),
    KEY idx_celeb_hymn_position (celebration_id, position),
    CONSTRAINT fk_ch_celebration FOREIGN KEY (celebration_id)
        REFERENCES celebrations(id) ON DELETE CASCADE,
    CONSTRAINT fk_ch_hymn FOREIGN KEY (hymn_id)
        REFERENCES hymns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- announcements (newsletters)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS announcements;
CREATE TABLE announcements (
    id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    subject           VARCHAR(255) NOT NULL,
    body_html         LONGTEXT NOT NULL,
    body_text         LONGTEXT NULL DEFAULT NULL,
    sent_by           BIGINT UNSIGNED NOT NULL,
    status            ENUM('draft','queued','sending','sent','failed')
                      NOT NULL DEFAULT 'draft',
    sent_at           TIMESTAMP NULL DEFAULT NULL,
    recipients_count  INT UNSIGNED NOT NULL DEFAULT 0,
    created_at        TIMESTAMP NULL DEFAULT NULL,
    updated_at        TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_announcements_status (status),
    CONSTRAINT fk_announcements_sender FOREIGN KEY (sent_by)
        REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- announcement_recipients
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS announcement_recipients;
CREATE TABLE announcement_recipients (
    id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    announcement_id   BIGINT UNSIGNED NOT NULL,
    user_id           BIGINT UNSIGNED NOT NULL,
    sent_at           TIMESTAMP NULL DEFAULT NULL,
    delivered_at      TIMESTAMP NULL DEFAULT NULL,
    opened_at         TIMESTAMP NULL DEFAULT NULL,
    bounced_at        TIMESTAMP NULL DEFAULT NULL,
    bounce_reason     VARCHAR(255) NULL DEFAULT NULL,
    unsubscribe_token VARCHAR(64) NOT NULL,
    unsubscribed_at   TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_ar_unsub_token (unsubscribe_token),
    UNIQUE KEY uk_ar_announcement_user (announcement_id, user_id),
    KEY idx_ar_user (user_id),
    CONSTRAINT fk_ar_announcement FOREIGN KEY (announcement_id)
        REFERENCES announcements(id) ON DELETE CASCADE,
    CONSTRAINT fk_ar_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- audit_logs
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS audit_logs;
CREATE TABLE audit_logs (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     BIGINT UNSIGNED NULL DEFAULT NULL,
    action      VARCHAR(80) NOT NULL,
    entity_type VARCHAR(80) NULL DEFAULT NULL,
    entity_id   BIGINT UNSIGNED NULL DEFAULT NULL,
    payload     JSON NULL DEFAULT NULL,
    ip_address  VARCHAR(45) NULL DEFAULT NULL,
    user_agent  VARCHAR(255) NULL DEFAULT NULL,
    created_at  TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_audit_user (user_id),
    KEY idx_audit_entity (entity_type, entity_id),
    KEY idx_audit_action (action),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- sessions (auth)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions (
    id            VARCHAR(255) NOT NULL,
    user_id       BIGINT UNSIGNED NULL DEFAULT NULL,
    ip_address    VARCHAR(45) NULL DEFAULT NULL,
    user_agent    TEXT NULL DEFAULT NULL,
    payload       LONGTEXT NOT NULL,
    last_activity INT NOT NULL,
    PRIMARY KEY (id),
    KEY idx_sessions_user (user_id),
    KEY idx_sessions_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- Compte admin initial
-- ⚠ MODIFIE L'EMAIL avant exécution !
-- Le mot de passe sera défini lors de la première connexion (lien magique)
-- ---------------------------------------------------------------------
INSERT INTO users (name, email, role, is_active, created_at, updated_at)
VALUES ('Administrateur', 'admin@erbcapitale.org', 'admin', 1, NOW(), NOW());