-- =============================================================
-- Migration SQL — Organisateur Symfony (avec sécurité User)
-- Compatible MySQL / MariaDB (o2switch)
-- =============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `user` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `email`         VARCHAR(180) NOT NULL,
    `display_name`  VARCHAR(100) NOT NULL DEFAULT '',
    `roles`         JSON         NOT NULL,
    `password`      VARCHAR(255) NOT NULL,
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at`    DATETIME     NOT NULL,
    `last_login_at` DATETIME     NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_user_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL,
    `type` VARCHAR(10) NOT NULL DEFAULT 'pro', `name` VARCHAR(255) NOT NULL,
    `priority` VARCHAR(20) NOT NULL DEFAULT 'normal', `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`), INDEX `idx_project_user` (`user_id`),
    CONSTRAINT `fk_project_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `project_step` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `project_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL, `position` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`), INDEX `idx_project_step_project` (`project_id`),
    CONSTRAINT `fk_project_step_project` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `micro_action` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `step_id` INT UNSIGNED NOT NULL,
    `text` VARCHAR(255) NOT NULL, `duration` INT NOT NULL DEFAULT 0, `done` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`), INDEX `idx_micro_action_step` (`step_id`),
    CONSTRAINT `fk_micro_action_step` FOREIGN KEY (`step_id`) REFERENCES `project_step` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `task` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL,
    `project_id` INT UNSIGNED NULL DEFAULT NULL,
    `type` VARCHAR(10) NOT NULL DEFAULT 'pro', `text` TEXT NOT NULL,
    `priority` VARCHAR(20) NOT NULL DEFAULT 'normal', `category` VARCHAR(30) NULL DEFAULT NULL,
    `completed` TINYINT(1) NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`), INDEX `idx_task_user` (`user_id`), INDEX `idx_task_project` (`project_id`),
    CONSTRAINT `fk_task_user`    FOREIGN KEY (`user_id`)    REFERENCES `user`    (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_task_project` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `planning_event` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL,
    `week_start_date` DATE NOT NULL, `day_of_week` TINYINT NOT NULL,
    `start_time` VARCHAR(5) NOT NULL, `end_time` VARCHAR(5) NOT NULL,
    `text` TEXT NOT NULL, `type` VARCHAR(20) NULL DEFAULT NULL,
    `cat` VARCHAR(10) NULL DEFAULT NULL, `alert` VARCHAR(5) NULL DEFAULT NULL,
    PRIMARY KEY (`id`), INDEX `idx_planning_user_week` (`user_id`, `week_start_date`),
    CONSTRAINT `fk_planning_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `formation` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL, `type` VARCHAR(10) NOT NULL DEFAULT 'pro',
    `day_of_week` TINYINT NOT NULL DEFAULT 0, `duration` INT NOT NULL DEFAULT 0,
    `done` TINYINT(1) NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`), INDEX `idx_formation_user` (`user_id`),
    CONSTRAINT `fk_formation_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `routine` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL,
    `slot` VARCHAR(10) NOT NULL DEFAULT 'matin', `label` VARCHAR(255) NOT NULL,
    `checked` TINYINT(1) NOT NULL DEFAULT 0, `checked_date` DATE NULL DEFAULT NULL,
    `position` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`), INDEX `idx_routine_user` (`user_id`),
    CONSTRAINT `fk_routine_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `memo` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL,
    `text` TEXT NOT NULL, `priority` VARCHAR(20) NOT NULL DEFAULT 'normal',
    `category` VARCHAR(50) NULL DEFAULT NULL, `cat` VARCHAR(10) NULL DEFAULT NULL,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`), INDEX `idx_memo_user` (`user_id`),
    CONSTRAINT `fk_memo_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `objectif` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL,
    `text` TEXT NOT NULL, `priority` VARCHAR(20) NOT NULL DEFAULT 'normal',
    `completed` TINYINT(1) NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`), INDEX `idx_objectif_user` (`user_id`),
    CONSTRAINT `fk_objectif_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rappel` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL,
    `text` TEXT NOT NULL, `priority` VARCHAR(20) NOT NULL DEFAULT 'normal',
    `cat` VARCHAR(10) NULL DEFAULT NULL, `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`), INDEX `idx_rappel_user` (`user_id`),
    CONSTRAINT `fk_rappel_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contact` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL, `company` VARCHAR(255) NULL DEFAULT NULL,
    `cat` VARCHAR(10) NULL DEFAULT NULL, `phone` VARCHAR(30) NULL DEFAULT NULL,
    `email` VARCHAR(180) NULL DEFAULT NULL, `status` VARCHAR(20) NOT NULL DEFAULT 'prospect',
    `next_action` TEXT NULL DEFAULT NULL, `last_contact` DATE NULL DEFAULT NULL,
    `note` TEXT NULL DEFAULT NULL, `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`), INDEX `idx_contact_user` (`user_id`),
    CONSTRAINT `fk_contact_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contact_history` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `contact_id` INT UNSIGNED NOT NULL,
    `note` TEXT NOT NULL, `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`), INDEX `idx_contact_history_contact` (`contact_id`),
    CONSTRAINT `fk_contact_history_contact` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `revue_semaine` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL,
    `week_start_date` DATE NOT NULL,
    `victoires` TEXT NULL, `non_faites` TEXT NULL, `bien` TEXT NULL, `mal` TEXT NULL,
    `apprentissage` TEXT NULL, `priorites` TEXT NULL,
    `objectif_formation` TEXT NULL, `engagement_kaizen` TEXT NULL,
    `score_productivite` TINYINT NOT NULL DEFAULT 0,
    `score_equilibre` TINYINT NOT NULL DEFAULT 0,
    `score_bien_etre` TINYINT NOT NULL DEFAULT 0,
    `bilan` TEXT NULL, `notes_pro` TEXT NULL, `notes_perso` TEXT NULL,
    `updated_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_user_week` (`user_id`, `week_start_date`),
    INDEX `idx_revue_user` (`user_id`),
    CONSTRAINT `fk_revue_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =============================================================
-- CRÉER LE PREMIER COMPTE ADMIN
-- Génère le hash avec : php bin/console security:hash-password
-- Puis décommente et adapte l'INSERT ci-dessous :
-- =============================================================
-- INSERT INTO `user` (`email`, `display_name`, `roles`, `password`, `is_active`, `created_at`)
-- VALUES ('admin@tondomaine.fr', 'Patrice', '["ROLE_ADMIN"]', '$2y$13$TON_HASH_ICI', 1, NOW());