CREATE DATABASE IF NOT EXISTS les_kozettes CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE les_kozettes;

CREATE TABLE users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 first_name VARCHAR(100) NOT NULL,
 last_name VARCHAR(100) NOT NULL,
 role ENUM('admin','user','adherent') NOT NULL DEFAULT 'user',
 active TINYINT(1) NOT NULL DEFAULT 1,
 reset_token VARCHAR(64) NULL,
 reset_expires DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(role,active)
);

CREATE TABLE members (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id INT UNSIGNED NULL UNIQUE,
 first_name VARCHAR(100) NOT NULL,
 last_name VARCHAR(100) NOT NULL,
 email VARCHAR(190) NOT NULL,
 phone VARCHAR(60),
 website VARCHAR(255),
 trade_name VARCHAR(190) NOT NULL,
 description TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE memberships (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id INT UNSIGNED NOT NULL,
 start_date DATE NOT NULL,
 end_date DATE NOT NULL,
 amount DECIMAL(10,2) NOT NULL,
 notes TEXT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE,
 INDEX(member_id,end_date)
);

CREATE TABLE contents (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 type ENUM('post','story','gazette') NOT NULL,
 channel ENUM('instagram','facebook','both','gazette') NOT NULL,
 title VARCHAR(255) NULL,
 body LONGTEXT NULL,
 link VARCHAR(500) NULL,
 planned_date DATETIME NULL,
 month CHAR(7) NULL,
 gazette_number INT NULL,
 gazette_editorial LONGTEXT NULL,
 pdf_url VARCHAR(500) NULL,
 status ENUM('draft','submitted','validated','published','rejected') NOT NULL DEFAULT 'draft',
 rejection_reason TEXT NULL,
 created_by INT UNSIGNED NOT NULL,
 validated_by INT UNSIGNED NULL,
 validated_at DATETIME NULL,
 published_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(created_by) REFERENCES users(id),
 FOREIGN KEY(validated_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(status,planned_date),
 INDEX(type,month)
);

CREATE TABLE content_media (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 content_id INT UNSIGNED NOT NULL,
 file_path VARCHAR(500) NOT NULL,
 original_name VARCHAR(255),
 sort_order INT NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(content_id) REFERENCES contents(id) ON DELETE CASCADE
);

CREATE TABLE gazette_sections (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 content_id INT UNSIGNED NOT NULL,
 section_type ENUM('dossier','portrait','focus','bon_plan') NOT NULL,
 title VARCHAR(255) NOT NULL,
 body LONGTEXT,
 link VARCHAR(500),
 sort_order INT NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(content_id) REFERENCES contents(id) ON DELETE CASCADE,
 INDEX(content_id,section_type,sort_order)
);

CREATE TABLE section_media (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 section_id INT UNSIGNED NOT NULL,
 file_path VARCHAR(500) NOT NULL,
 original_name VARCHAR(255),
 sort_order INT NOT NULL DEFAULT 0,
 FOREIGN KEY(section_id) REFERENCES gazette_sections(id) ON DELETE CASCADE
);

CREATE TABLE submissions (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id INT UNSIGNED NOT NULL,
 type ENUM('focus','bon_plan') NOT NULL,
 title VARCHAR(255) NOT NULL,
 body TEXT NOT NULL,
 image_url VARCHAR(500),
 link VARCHAR(500),
 target_month CHAR(7) NOT NULL,
 status ENUM('submitted','accepted','rejected','published') NOT NULL DEFAULT 'submitted',
 reviewer_note TEXT,
 reviewed_by INT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE,
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(member_id,target_month,type),
 INDEX(status,target_month)
);

CREATE TABLE notifications (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id INT UNSIGNED NOT NULL,
 title VARCHAR(255) NOT NULL,
 message TEXT NOT NULL,
 url VARCHAR(500),
 is_read TINYINT(1) NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX(user_id,is_read,created_at)
);

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id INT UNSIGNED NULL,
 action VARCHAR(190) NOT NULL,
 entity_type VARCHAR(80),
 entity_id INT UNSIGNED NULL,
 details TEXT,
 ip_address VARCHAR(45),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(created_at),
 INDEX(entity_type,entity_id)
);

CREATE TABLE content_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, content_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NULL,
 from_status VARCHAR(30) NOT NULL, to_status VARCHAR(30) NOT NULL, note TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(content_id) REFERENCES contents(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(content_id,created_at)
);
CREATE TABLE email_queue (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, recipient VARCHAR(190) NOT NULL, subject VARCHAR(255) NOT NULL, body LONGTEXT NOT NULL,
 status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending', attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
 last_error TEXT NULL, sent_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(status,created_at)
);
CREATE TABLE settings (
 setting_key VARCHAR(100) PRIMARY KEY, setting_value TEXT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO settings(setting_key,setting_value) VALUES ('annual_plan_limit','4'),('submission_deadline_day','15'),('renewal_warning_days','45');
