USE les_kozettes;
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS 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')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);
