CREATE TABLE IF NOT EXISTS t_projects (
    project_id          INT AUTO_INCREMENT PRIMARY KEY,
    project_slug        VARCHAR(100) NOT NULL UNIQUE,
    project_name        VARCHAR(255) NOT NULL,
    project_tagline     VARCHAR(500) NULL COMMENT 'Phrase courte de description',
    project_description TEXT NULL,
    project_stack       JSON NULL COMMENT 'Liste des technos : ["PHP", "MySQL", "JS"]',
    project_url_live    VARCHAR(500) NULL,
    project_url_repo    VARCHAR(500) NULL,
    project_image_path  VARCHAR(500) NULL,
    project_status      VARCHAR(30) NOT NULL DEFAULT 'in_progress' COMMENT 'in_progress, completed, archived',
    project_featured    TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Mis en avant sur la home',
    project_order       INT NOT NULL DEFAULT 0,
    project_created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    project_updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx01_projects (project_featured),
    INDEX idx02_projects (project_order),
    INDEX idx03_projects (project_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DOWN

DROP TABLE IF EXISTS t_projects;
