<?php
declare(strict_types=1);

return [
    'up' => "
CREATE TABLE `sa_academy_pricing` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `academy_code` VARCHAR(50) NOT NULL,
    `academy_name_ar` VARCHAR(300) NOT NULL,
    `discipline_code` VARCHAR(30) NOT NULL,
    `category` VARCHAR(50) NOT NULL COMMENT 'subscription, team, session_card, private_training, lane_rental',
    `level_name_ar` VARCHAR(200) NULL COMMENT 'Group name/level/age group',
    `level_name_en` VARCHAR(200) NULL,
    `age_group` VARCHAR(50) NULL COMMENT 'under_13, adult, under_6, under_7, etc.',
    `birth_year` INT NULL COMMENT 'Specific birth year for age-based pricing',
    `age_from` INT UNSIGNED NULL,
    `age_to` INT UNSIGNED NULL,
    `gender` VARCHAR(10) NULL COMMENT 'male, female, NULL=mixed',
    `group_size_max` INT UNSIGNED NULL COMMENT 'Max swimmers in group (for swimming)',
    `sessions_per_week` INT UNSIGNED NULL,
    `sessions_per_month` INT UNSIGNED NULL,
    `session_duration_minutes` INT UNSIGNED NULL,
    `price_member` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `price_nonmember` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `price_subscription` DECIMAL(10,2) NULL COMMENT 'Base subscription price before member/nonmember',
    `billing_period` VARCHAR(20) NOT NULL DEFAULT 'monthly' COMMENT 'monthly, quarterly, per_session, per_card, yearly',
    `includes_fitness` TINYINT(1) NOT NULL DEFAULT 0,
    `num_players_max` INT UNSIGNED NULL COMMENT 'For private training - max players per session',
    `coach_level` VARCHAR(50) NULL COMMENT 'For private training - director/coach/fitness',
    `lane_type` VARCHAR(30) NULL COMMENT '50m, 25m, mix',
    `total_sessions` INT UNSIGNED NULL COMMENT 'For session cards - total sessions in card',
    `season` VARCHAR(20) NOT NULL DEFAULT '2025_2026',
    `notes_ar` TEXT NULL,
    `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `effective_from` DATE NOT NULL DEFAULT '2025-09-01',
    `effective_to` DATE NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_sap_academy` (`academy_code`),
    INDEX `idx_sap_discipline` (`discipline_code`),
    INDEX `idx_sap_category` (`category`),
    INDEX `idx_sap_age` (`age_from`, `age_to`),
    INDEX `idx_sap_active` (`is_active`, `effective_from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sa_rental_entity_pricing` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `facility_type` VARCHAR(50) NOT NULL COMMENT 'football_full, football_five, tennis, padel, squash, multi_court, bowling, table_tennis, playstation, billiards, track, combat_hall',
    `facility_name_ar` VARCHAR(200) NOT NULL,
    `entity_type` VARCHAR(50) NOT NULL COMMENT 'member, non_member, non_member_private_coach, schools_gov, clubs_federations, other_entities, foreign_entities',
    `usage_type` VARCHAR(50) NOT NULL DEFAULT 'practice' COMMENT 'practice, match, activity',
    `time_period` VARCHAR(20) NOT NULL COMMENT 'morning, evening',
    `duration_minutes` INT UNSIGNED NOT NULL DEFAULT 60,
    `price_amount` DECIMAL(10,2) NOT NULL,
    `max_players` INT UNSIGNED NULL,
    `notes_ar` VARCHAR(500) NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `effective_from` DATE NOT NULL DEFAULT '2025-09-01',
    `effective_to` DATE NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_srep_facility` (`facility_type`),
    INDEX `idx_srep_entity` (`entity_type`),
    INDEX `idx_srep_usage` (`usage_type`),
    INDEX `idx_srep_active` (`is_active`, `effective_from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sa_discount_rules` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `rule_code` VARCHAR(50) NOT NULL UNIQUE,
    `name_ar` VARCHAR(200) NOT NULL,
    `discount_type` VARCHAR(20) NOT NULL COMMENT 'percentage, fixed',
    `discount_value` DECIMAL(10,2) NOT NULL,
    `applies_to` VARCHAR(50) NOT NULL COMMENT 'academy, facility, all',
    `academy_code` VARCHAR(50) NULL COMMENT 'NULL = all academies',
    `condition_type` VARCHAR(50) NOT NULL COMMENT 'advance_payment, sibling, nonmember_surcharge, free_period',
    `condition_value` TEXT NULL COMMENT 'JSON or simple value for condition params',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_sdr_applies` (`applies_to`, `academy_code`),
    INDEX `idx_sdr_condition` (`condition_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
",
    'down' => "
DROP TABLE IF EXISTS `sa_discount_rules`;
DROP TABLE IF EXISTS `sa_rental_entity_pricing`;
DROP TABLE IF EXISTS `sa_academy_pricing`;
"
];
