-- Migration: Create recommendations table for storing ordered temperament recommendations
-- Usage: mysql -u <user> -p<pass> <database> < migrations/20240206_add_recommendations_table.sql
-- The script is idempotent and will not overwrite the table if it already exists.

START TRANSACTION;

CREATE TABLE IF NOT EXISTS `recommendations` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `temperaments_orders` JSON NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;
