CREATE TABLE IF NOT EXISTS t_experiences (
    experience_id          INT AUTO_INCREMENT PRIMARY KEY,
    experience_role        VARCHAR(255) NOT NULL,
    experience_company     VARCHAR(255) NOT NULL,
    experience_location    VARCHAR(255) NULL,
    experience_start_date  DATE NOT NULL,
    experience_end_date    DATE NULL COMMENT 'NULL si poste en cours',
    experience_description TEXT NULL,
    experience_highlights  JSON NULL COMMENT 'Liste de bullet points',
    experience_order       INT NOT NULL DEFAULT 0 COMMENT 'Ordre d''affichage manuel',
    experience_created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    experience_updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx01_experiences (experience_start_date),
    INDEX idx02_experiences (experience_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DOWN

DROP TABLE IF EXISTS t_experiences;
