-- =====================================================================
-- Fichier : database/migration_phase8_surveys.sql
--
-- Phase 8 — Système de sondages
-- 
-- 3 tables :
--   surveys           : le sondage (titre, type, dates, status)
--   survey_options    : les choix possibles (pour single_choice)
--   survey_responses  : les votes des membres
--
-- IMPORTANT : tenant_id est NULL pour maintenant, préparation multi-tenant.
-- =====================================================================

CREATE TABLE IF NOT EXISTS `surveys` (
    `id`             BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `tenant_id`      BIGINT UNSIGNED NULL,
    `title`          VARCHAR(255) NOT NULL,
    `description`    TEXT NULL,
    `type`           ENUM('single_choice', 'rating') NOT NULL DEFAULT 'single_choice',
    `visibility`     ENUM('hidden', 'after_vote', 'after_close', 'realtime') NOT NULL DEFAULT 'after_close',
    `opens_at`       TIMESTAMP NULL,
    `closes_at`      TIMESTAMP NULL,
    `status`         ENUM('draft', 'open', 'closed') NOT NULL DEFAULT 'draft',
    `created_by`     BIGINT UNSIGNED NULL,
    `created_at`     TIMESTAMP NULL,
    `updated_at`     TIMESTAMP NULL,
    `deleted_at`     TIMESTAMP NULL,
    KEY `idx_surveys_tenant`  (`tenant_id`),
    KEY `idx_surveys_status`  (`status`),
    KEY `idx_surveys_deleted` (`deleted_at`),
    CONSTRAINT `fk_surveys_creator` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `survey_options` (
    `id`         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `survey_id`  BIGINT UNSIGNED NOT NULL,
    `label`      VARCHAR(255) NOT NULL,
    `position`   INT UNSIGNED NOT NULL DEFAULT 1,
    KEY `idx_survey_options_survey` (`survey_id`, `position`),
    CONSTRAINT `fk_survey_options_survey` FOREIGN KEY (`survey_id`) REFERENCES `surveys`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `survey_responses` (
    `id`          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `survey_id`   BIGINT UNSIGNED NOT NULL,
    `user_id`     BIGINT UNSIGNED NOT NULL,
    `option_id`   BIGINT UNSIGNED NULL,
    `rating`      TINYINT UNSIGNED NULL,
    `created_at`  TIMESTAMP NULL,
    `updated_at`  TIMESTAMP NULL,
    UNIQUE KEY `uniq_response_user_survey` (`survey_id`, `user_id`),
    KEY `idx_responses_survey` (`survey_id`),
    KEY `idx_responses_option` (`option_id`),
    CONSTRAINT `fk_responses_survey` FOREIGN KEY (`survey_id`) REFERENCES `surveys`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_responses_user`   FOREIGN KEY (`user_id`)   REFERENCES `users`(`id`)   ON DELETE CASCADE,
    CONSTRAINT `fk_responses_option` FOREIGN KEY (`option_id`) REFERENCES `survey_options`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vérification
SELECT 'Tables créées' AS info;
SHOW TABLES LIKE 'survey%';