SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('admin','editor') NOT NULL DEFAULT 'editor',status ENUM('active','disabled') NOT NULL DEFAULT 'active',created_at DATETIME NOT NULL,updated_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS categories (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,slug VARCHAR(160) NOT NULL UNIQUE,sort_order INT NOT NULL DEFAULT 0,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS category_translations (category_id INT UNSIGNED NOT NULL,locale CHAR(2) NOT NULL,name VARCHAR(160) NOT NULL,PRIMARY KEY(category_id,locale),CONSTRAINT fk_ct_cat FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS posts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,slug VARCHAR(190) NOT NULL UNIQUE,category_id INT UNSIGNED NULL,cover_image VARCHAR(255) NOT NULL,status ENUM('draft','published') NOT NULL DEFAULT 'draft',featured TINYINT(1) NOT NULL DEFAULT 0,reading_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 5,published_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX idx_posts_status_date(status,published_at),CONSTRAINT fk_posts_cat FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS post_translations (post_id BIGINT UNSIGNED NOT NULL,locale CHAR(2) NOT NULL,title VARCHAR(220) NOT NULL,excerpt TEXT NOT NULL,content MEDIUMTEXT NOT NULL,meta_title VARCHAR(220) NULL,meta_description VARCHAR(320) NULL,PRIMARY KEY(post_id,locale),FULLTEXT KEY ft_post_text(title,excerpt,content),CONSTRAINT fk_pt_post FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS tags (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,slug VARCHAR(140) NOT NULL UNIQUE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS tag_translations (tag_id INT UNSIGNED NOT NULL,locale CHAR(2) NOT NULL,name VARCHAR(140) NOT NULL,PRIMARY KEY(tag_id,locale),CONSTRAINT fk_tt_tag FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS post_tags (post_id BIGINT UNSIGNED NOT NULL,tag_id INT UNSIGNED NOT NULL,PRIMARY KEY(post_id,tag_id),CONSTRAINT fk_pot_post FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE,CONSTRAINT fk_pot_tag FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS projects (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,slug VARCHAR(190) NOT NULL UNIQUE,cover_image VARCHAR(255) NOT NULL,tech_stack VARCHAR(600) NOT NULL,status ENUM('draft','published','archived') NOT NULL DEFAULT 'published',featured TINYINT(1) NOT NULL DEFAULT 0,started_at DATE NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS project_translations (project_id BIGINT UNSIGNED NOT NULL,locale CHAR(2) NOT NULL,name VARCHAR(220) NOT NULL,tagline VARCHAR(320) NOT NULL,description MEDIUMTEXT NOT NULL,PRIMARY KEY(project_id,locale),CONSTRAINT fk_prt_project FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS project_versions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,project_id BIGINT UNSIGNED NOT NULL,version VARCHAR(50) NOT NULL,title VARCHAR(220) NOT NULL,summary TEXT NOT NULL,changelog MEDIUMTEXT NULL,released_at DATE NOT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_project_version(project_id,version),CONSTRAINT fk_pv_project FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS project_version_translations (version_id BIGINT UNSIGNED NOT NULL,locale CHAR(2) NOT NULL,summary TEXT NOT NULL,PRIMARY KEY(version_id,locale),CONSTRAINT fk_pvt_version FOREIGN KEY(version_id) REFERENCES project_versions(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sources (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(190) NOT NULL,type ENUM('rss','json') NOT NULL DEFAULT 'rss',feed_url VARCHAR(700) NOT NULL,default_locale CHAR(2) NOT NULL DEFAULT 'fr',enabled TINYINT(1) NOT NULL DEFAULT 1,last_import_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS imported_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,source_id BIGINT UNSIGNED NOT NULL,external_hash CHAR(64) NOT NULL,external_url VARCHAR(1000) NOT NULL,title VARCHAR(500) NOT NULL,excerpt TEXT NULL,image_url VARCHAR(1000) NULL,published_at DATETIME NULL,status ENUM('draft','ignored','converted') NOT NULL DEFAULT 'draft',created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_import_hash(source_id,external_hash),CONSTRAINT fk_imp_source FOREIGN KEY(source_id) REFERENCES sources(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS analytics_daily (day DATE NOT NULL,path VARCHAR(500) NOT NULL,post_id BIGINT UNSIGNED NULL,views INT UNSIGNED NOT NULL DEFAULT 0,PRIMARY KEY(day,path),INDEX idx_analytics_post(post_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS settings (`key` VARCHAR(120) PRIMARY KEY,`value` TEXT NULL,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;