<?php
declare(strict_types=1);

return [
    'up' => "
CREATE TABLE `sa_pool_reservations` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `reservation_number` VARCHAR(50) NOT NULL,
    `group_id` BIGINT UNSIGNED NOT NULL,
    `booker_name` VARCHAR(300) NOT NULL,
    `booker_phone` VARCHAR(30) NULL,
    `booker_national_id` VARCHAR(14) NULL,
    `booker_type` VARCHAR(30) NOT NULL DEFAULT 'freelance_coach' COMMENT 'freelance_coach, entity, individual',
    `participant_count` INT UNSIGNED NOT NULL DEFAULT 1,

    `reservation_type` VARCHAR(30) NOT NULL COMMENT 'lane_50m, lane_25m, lane_mix, cards',
    `pricing_id` BIGINT UNSIGNED NULL,
    `sessions_total` INT UNSIGNED NOT NULL,
    `sessions_used` INT UNSIGNED NOT NULL DEFAULT 0,
    `sessions_remaining` INT UNSIGNED GENERATED ALWAYS AS (`sessions_total` - `sessions_used`) STORED,

    `unit_price` DECIMAL(10,2) NOT NULL,
    `total_amount` DECIMAL(10,2) NOT NULL,
    `payment_status` VARCHAR(20) NOT NULL DEFAULT 'unpaid' COMMENT 'unpaid, pending, paid, partial',
    `payment_request_id` BIGINT UNSIGNED NULL,

    `status` VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT 'active, completed, cancelled, expired',
    `start_date` DATE NOT NULL,
    `expiry_date` DATE NULL,
    `cancelled_at` TIMESTAMP NULL,
    `cancellation_reason` TEXT NULL,

    `has_overflow` TINYINT(1) NOT NULL DEFAULT 0,
    `overflow_type` VARCHAR(30) NULL COMMENT 'additional_lane, overflow_cards',
    `overflow_cards_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `overflow_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,

    `notes` TEXT NULL,
    `branch_id` BIGINT UNSIGNED NULL,
    `created_by` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY `uq_spr_number` (`reservation_number`),
    INDEX `idx_spr_group` (`group_id`),
    INDEX `idx_spr_booker` (`booker_name`(100)),
    INDEX `idx_spr_type` (`reservation_type`),
    INDEX `idx_spr_status` (`status`),
    INDEX `idx_spr_dates` (`start_date`, `expiry_date`),
    CONSTRAINT `fk_spr_group` FOREIGN KEY (`group_id`) REFERENCES `sa_groups`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
",
    'down' => "DROP TABLE IF EXISTS `sa_pool_reservations`",
];
