-- Migration 003 : tables activités
-- Exécuter : mysql -u <user> -p <database> < migrations/003_create_activities_tables.sql

CREATE TABLE IF NOT EXISTS `activities` (
    `id`         INT UNSIGNED        NOT NULL AUTO_INCREMENT,
    `slug`       VARCHAR(100)        NOT NULL,
    `is_active`  TINYINT(1)          NOT NULL DEFAULT 1,
    `sort_order` INT                 NOT NULL DEFAULT 0,
    `price`      DECIMAL(8,2)        NULL,
    `created_at` TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_activities_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `activity_translations` (
    `id`                    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `activity_id`           INT UNSIGNED  NOT NULL,
    `locale`                VARCHAR(5)    NOT NULL,
    `label`                 VARCHAR(255)  NOT NULL,
    `content`               TEXT          NOT NULL,
    `price_label`           VARCHAR(255)  NULL,
    `how_to_book`           TEXT          NULL,
    `payable_on_site`       VARCHAR(255)  NULL,
    `reserve_by_mail`       TEXT          NULL,
    `reserved_to_customers` TEXT          NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_activity_locale` (`activity_id`, `locale`),
    FOREIGN KEY (`activity_id`) REFERENCES `activities`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `activity_images` (
    `id`          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `activity_id` INT UNSIGNED  NOT NULL,
    `filename`    VARCHAR(255)  NOT NULL,
    `credits`     VARCHAR(255)  NULL,
    `sort_order`  INT           NOT NULL DEFAULT 0,
    `is_primary`  TINYINT(1)   NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`activity_id`) REFERENCES `activities`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `activity_image_translations` (
    `image_id` INT UNSIGNED  NOT NULL,
    `locale`   VARCHAR(5)    NOT NULL,
    `alt`      VARCHAR(255)  NULL,
    `caption`  TEXT          NULL,
    PRIMARY KEY (`image_id`, `locale`),
    FOREIGN KEY (`image_id`) REFERENCES `activity_images`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
