-- ============================================================
-- Migration: create_demand_tables
-- Description: Espace Demandes module – requests, attachments,
--              status history and comments.
-- ============================================================

CREATE TABLE IF NOT EXISTS `demand` (
    `id`                     INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `ref`                    VARCHAR(20)      NOT NULL UNIQUE COMMENT 'Human-readable ref, e.g. RS-0001 / DEM-0001',
    `type`                   ENUM('RS_POST','NEW_PRODUCT','SAAS_DEV') NOT NULL,
    `status`                 ENUM('NOUVELLE','EN_COURS','EN_REVIEW','APPROUVEE','PUBLIEE','REFUSEE')
                             NOT NULL DEFAULT 'NOUVELLE',
    -- common fields
    `demandeur`              VARCHAR(191)     NOT NULL,
    `email`                  VARCHAR(191)     NOT NULL,
    `pdv_name`               VARCHAR(191)     NOT NULL COMMENT 'TODO: replace with FK to shop when shop table is mapped',
    -- RS_POST specific
    `subject`                VARCHAR(255)     NULL,
    `type_contenu`           VARCHAR(100)     NULL,
    `platforms`              VARCHAR(500)     NULL COMMENT 'JSON-encoded array of platform names',
    `description`            TEXT             NULL,
    `visual_notes`           TEXT             NULL,
    `deadline_date`          DATE             NULL,
    -- NEW_PRODUCT specific
    `product_name`           VARCHAR(255)     NULL,
    `category`               VARCHAR(100)     NULL,
    `product_description`    TEXT             NULL,
    `expected_volume`        VARCHAR(100)     NULL,
    `estimated_price_gross`  DECIMAL(10,2)    NULL,
    `context_motivation`     TEXT             NULL,
    -- SAAS_DEV specific
    `role`                   VARCHAR(100)     NULL,
    `request_types`          VARCHAR(500)     NULL COMMENT 'JSON-encoded array of request type tags',
    `title`                  VARCHAR(255)     NULL,
    `expected_benefit`       TEXT             NULL,
    `priority`               ENUM('LOW','MEDIUM','HIGH','CRITICAL') NULL DEFAULT 'MEDIUM',
    `notes`                  TEXT             NULL,
    -- audit
    `created_by_user_id`     INT UNSIGNED     NULL COMMENT 'TODO: FK to users table',
    `created_at`             DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`             DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_demand_status`     (`status`),
    INDEX `idx_demand_type`       (`type`),
    INDEX `idx_demand_pdv`        (`pdv_name`),
    INDEX `idx_demand_created_at` (`created_at`),
    INDEX `idx_demand_created_by` (`created_by_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Auto-generate human-readable ref via trigger
DELIMITER //
CREATE TRIGGER IF NOT EXISTS `before_insert_demand`
BEFORE INSERT ON `demand`
FOR EACH ROW
BEGIN
    DECLARE next_id INT;
    SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES
                   WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'demand');
    IF NEW.type = 'RS_POST' THEN
        SET NEW.ref = CONCAT('RS-', LPAD(next_id, 4, '0'));
    ELSE
        SET NEW.ref = CONCAT('DEM-', LPAD(next_id, 4, '0'));
    END IF;
END//
DELIMITER ;

CREATE TABLE IF NOT EXISTS `demand_attachment` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `demand_id`    INT UNSIGNED NOT NULL,
    `filename`     VARCHAR(255) NOT NULL COMMENT 'Original filename',
    `stored_name`  VARCHAR(255) NOT NULL COMMENT 'Stored filename on disk (UUID-based)',
    `mime_type`    VARCHAR(100) NOT NULL,
    `size_bytes`   INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_da_demand_id` (`demand_id`),
    CONSTRAINT `fk_da_demand` FOREIGN KEY (`demand_id`) REFERENCES `demand`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `demand_status_history` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `demand_id`    INT UNSIGNED NOT NULL,
    `old_status`   VARCHAR(50)  NULL,
    `new_status`   VARCHAR(50)  NOT NULL,
    `changed_by`   INT UNSIGNED NULL COMMENT 'TODO: FK to users table',
    `comment`      TEXT         NULL,
    `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_dsh_demand_id` (`demand_id`),
    CONSTRAINT `fk_dsh_demand` FOREIGN KEY (`demand_id`) REFERENCES `demand`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `demand_comment` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `demand_id`   INT UNSIGNED NOT NULL,
    `author_id`   INT UNSIGNED NULL COMMENT 'TODO: FK to users table',
    `author_name` VARCHAR(191) NOT NULL,
    `body`        TEXT         NOT NULL,
    `is_internal` TINYINT(1)   NOT NULL DEFAULT 1 COMMENT '1 = admin-only, 0 = visible to requester',
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_dc_demand_id` (`demand_id`),
    CONSTRAINT `fk_dc_demand` FOREIGN KEY (`demand_id`) REFERENCES `demand`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
