-- ============================================
-- سیستم رزرو خودکار هوشمند بلیط هواپیما
-- فایل نصب پایگاه داده
-- ============================================


-- جدول ایرلاین‌ها
CREATE TABLE IF NOT EXISTS `airlines` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(3) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `name_en` VARCHAR(255) NULL,
    `logo` VARCHAR(255) NULL,
    `country` VARCHAR(2) DEFAULT 'IR',
    `quality_score` DECIMAL(3, 2) DEFAULT 3.00,
    `avg_delay_minutes` DECIMAL(6, 2) DEFAULT 0,
    `on_time_percentage` DECIMAL(5, 2) DEFAULT 80.00,
    `cancellation_rate` DECIMAL(5, 2) DEFAULT 2.00,
    `website` VARCHAR(255) NULL,
    `phone` VARCHAR(50) NULL,
    `email` VARCHAR(255) NULL,
    `description` TEXT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `is_domestic` TINYINT(1) DEFAULT 1,
    `is_international` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `airlines_code_unique` (`code`),
    KEY `airlines_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول پروازها
CREATE TABLE IF NOT EXISTS `flights` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `airline_id` BIGINT UNSIGNED NOT NULL,
    `flight_number` VARCHAR(10) NOT NULL,
    `origin` VARCHAR(3) NOT NULL,
    `origin_city` VARCHAR(100) NOT NULL,
    `destination` VARCHAR(3) NOT NULL,
    `destination_city` VARCHAR(100) NOT NULL,
    `departure_time` DATETIME NOT NULL,
    `arrival_time` DATETIME NOT NULL,
    `duration_minutes` INT NOT NULL,
    `base_price` DECIMAL(12, 2) NOT NULL,
    `current_price` DECIMAL(12, 2) NOT NULL,
    `min_price` DECIMAL(12, 2) NULL,
    `max_price` DECIMAL(12, 2) NULL,
    `total_seats` INT DEFAULT 180,
    `available_seats` INT DEFAULT 180,
    `cabin_class` ENUM('economy', 'premium_economy', 'business', 'first') DEFAULT 'economy',
    `stops` INT DEFAULT 0,
    `stop_airports` JSON NULL,
    `delay_history` JSON NULL,
    `avg_delay_minutes` DECIMAL(6, 2) DEFAULT 0,
    `delay_probability` DECIMAL(5, 4) DEFAULT 0.0000,
    `aircraft_type` VARCHAR(50) NULL,
    `amenities` JSON NULL,
    `is_refundable` TINYINT(1) DEFAULT 0,
    `is_changeable` TINYINT(1) DEFAULT 1,
    `change_fee` DECIMAL(10, 2) DEFAULT 0,
    `status` ENUM('scheduled', 'delayed', 'cancelled', 'completed') DEFAULT 'scheduled',
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `flights_origin_destination_index` (`origin`, `destination`, `departure_time`),
    KEY `flights_airline_id_index` (`airline_id`),
    UNIQUE KEY `flights_number_time_unique` (`flight_number`, `departure_time`),
    CONSTRAINT `flights_airline_id_foreign` FOREIGN KEY (`airline_id`) REFERENCES `airlines` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول رزروها
CREATE TABLE IF NOT EXISTS `bookings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `booking_code` VARCHAR(10) NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `booking_type` ENUM('manual', 'auto_basic', 'auto_supervised') DEFAULT 'manual',
    `ab_group` ENUM('control', 'auto_basic', 'auto_supervised') NOT NULL,
    `flights` JSON NOT NULL,
    `subtotal` DECIMAL(12, 2) NOT NULL,
    `tax` DECIMAL(12, 2) DEFAULT 0,
    `discount` DECIMAL(12, 2) DEFAULT 0,
    `total_price` DECIMAL(12, 2) NOT NULL,
    `adult_count` INT DEFAULT 1,
    `child_count` INT DEFAULT 0,
    `infant_count` INT DEFAULT 0,
    `passengers` JSON NULL,
    `supervisor_score` DECIMAL(5, 4) NULL,
    `supervisor_details` JSON NULL,
    `personalization_score` DECIMAL(5, 4) NULL,
    `status` ENUM('pending', 'confirmed', 'cancelled', 'completed', 'needs_review') DEFAULT 'pending',
    `search_started_at` TIMESTAMP NULL,
    `booking_started_at` TIMESTAMP NULL,
    `confirmed_at` TIMESTAMP NULL,
    `completed_at` TIMESTAMP NULL,
    `cancelled_at` TIMESTAMP NULL,
    `cancellation_reason` VARCHAR(255) NULL,
    `notes` TEXT NULL,
    `payment_method` VARCHAR(50) NULL,
    `payment_reference` VARCHAR(100) NULL,
    `payment_status` ENUM('pending', 'paid', 'refunded', 'failed') DEFAULT 'pending',
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    `deleted_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `bookings_code_unique` (`booking_code`),
    KEY `bookings_user_id_index` (`user_id`),
    KEY `bookings_ab_group_index` (`ab_group`),
    CONSTRAINT `bookings_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول واسط رزرو-پرواز
CREATE TABLE IF NOT EXISTS `booking_flights` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `booking_id` BIGINT UNSIGNED NOT NULL,
    `flight_id` BIGINT UNSIGNED NOT NULL,
    `sequence` INT DEFAULT 1,
    `price` DECIMAL(12, 2) NOT NULL,
    `passenger_count` INT NOT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `booking_flights_unique` (`booking_id`, `flight_id`, `sequence`),
    CONSTRAINT `booking_flights_booking_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE,
    CONSTRAINT `booking_flights_flight_foreign` FOREIGN KEY (`flight_id`) REFERENCES `flights` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول ترجیحات کاربران
CREATE TABLE IF NOT EXISTS `user_preferences` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `min_price` DECIMAL(12, 2) NULL,
    `max_price` DECIMAL(12, 2) NULL,
    `avg_price` DECIMAL(12, 2) NULL,
    `price_sensitivity` DECIMAL(3, 2) DEFAULT 0.50,
    `preferred_departure_start` TIME NULL,
    `preferred_departure_end` TIME NULL,
    `preferred_arrival_start` TIME NULL,
    `preferred_arrival_end` TIME NULL,
    `preferred_days` JSON NULL,
    `preferred_airlines` JSON NULL,
    `avoided_airlines` JSON NULL,
    `preferred_cabin` ENUM('economy', 'premium_economy', 'business', 'first') DEFAULT 'economy',
    `max_stops` INT DEFAULT 1,
    `min_connection_time` INT DEFAULT 60,
    `max_connection_time` INT DEFAULT 240,
    `frequent_routes` JSON NULL,
    `frequent_destinations` JSON NULL,
    `requires_wifi` TINYINT(1) DEFAULT 0,
    `requires_meal` TINYINT(1) DEFAULT 0,
    `requires_entertainment` TINYINT(1) DEFAULT 0,
    `avg_days_before_travel` INT NULL,
    `trip_type_preference` ENUM('business', 'leisure', 'both') DEFAULT 'both',
    `system_trust_score` DECIMAL(3, 2) DEFAULT 0.50,
    `auto_updated_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_preferences_user_unique` (`user_id`),
    CONSTRAINT `user_preferences_user_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول متریک‌های A/B
CREATE TABLE IF NOT EXISTS `ab_metrics` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `booking_id` BIGINT UNSIGNED NULL,
    `ab_group` ENUM('control', 'auto_basic', 'auto_supervised') NOT NULL,
    `event_type` ENUM('search', 'view_result', 'select_flight', 'start_booking', 'complete_booking', 'abandon_booking', 'cancel_booking', 'survey_response') NOT NULL,
    `event_at` TIMESTAMP NOT NULL,
    `time_spent_seconds` INT NULL,
    `conversion_value` DECIMAL(12, 2) NULL,
    `event_data` JSON NULL,
    `device_type` VARCHAR(20) NULL,
    `browser` VARCHAR(50) NULL,
    `ip_address` VARCHAR(45) NULL,
    `regret_score` DECIMAL(3, 2) NULL,
    `satisfaction_score` DECIMAL(3, 2) NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `ab_metrics_group_type_index` (`ab_group`, `event_type`),
    KEY `ab_metrics_user_index` (`user_id`),
    KEY `ab_metrics_booking_index` (`booking_id`),
    CONSTRAINT `ab_metrics_user_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `ab_metrics_booking_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول نظرسنجی پشیمانی
CREATE TABLE IF NOT EXISTS `regret_surveys` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `booking_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `survey_type` ENUM('initial', 'followup') NOT NULL,
    `q_missed_better_option` TINYINT NULL,
    `q_would_not_repeat` TINYINT NULL,
    `q_system_wrong_decision` TINYINT NULL,
    `q_lack_of_control` TINYINT NULL,
    `q_overall_satisfaction` TINYINT NULL,
    `q_trust_in_system` TINYINT NULL,
    `q_would_recommend` TINYINT NULL,
    `regret_score` DECIMAL(3, 2) NULL,
    `regret_level` ENUM('none', 'low', 'moderate', 'high', 'severe') NULL,
    `feedback` TEXT NULL,
    `improvement_suggestions` TEXT NULL,
    `sentiment_analysis` JSON NULL,
    `status` ENUM('pending', 'completed', 'expired', 'skipped') DEFAULT 'pending',
    `sent_at` TIMESTAMP NULL,
    `opened_at` TIMESTAMP NULL,
    `completed_at` TIMESTAMP NULL,
    `expires_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `regret_surveys_booking_index` (`booking_id`),
    KEY `regret_surveys_user_index` (`user_id`),
    CONSTRAINT `regret_surveys_booking_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE,
    CONSTRAINT `regret_surveys_user_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول مکالمات AI
CREATE TABLE IF NOT EXISTS `ai_conversations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `conversation_id` CHAR(36) NOT NULL,
    `type` ENUM('chat', 'recommendation', 'profile_analysis', 'booking_assistant') DEFAULT 'chat',
    `role` ENUM('user', 'assistant', 'system') NOT NULL,
    `content` TEXT NOT NULL,
    `metadata` JSON NULL,
    `model_used` VARCHAR(100) NULL,
    `tokens_used` INT NULL,
    `response_time_ms` DECIMAL(8, 2) NULL,
    `user_rating` TINYINT NULL,
    `was_helpful` TINYINT(1) NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `ai_conversations_conversation_index` (`conversation_id`),
    KEY `ai_conversations_user_index` (`user_id`),
    CONSTRAINT `ai_conversations_user_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول قالب‌های پرامپت
CREATE TABLE IF NOT EXISTS `ai_prompt_templates` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `slug` VARCHAR(100) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `category` ENUM('chat', 'recommendation', 'analysis', 'booking', 'notification') NOT NULL,
    `system_prompt` TEXT NULL,
    `user_prompt_template` TEXT NOT NULL,
    `variables` JSON NULL,
    `settings` JSON NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `is_default` TINYINT(1) DEFAULT 0,
    `usage_count` INT DEFAULT 0,
    `avg_rating` DECIMAL(3, 2) NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ai_prompt_templates_slug_unique` (`slug`),
    KEY `ai_prompt_templates_category_index` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول لاگ فعالیت‌ها
CREATE TABLE IF NOT EXISTS `activity_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NULL,
    `type` VARCHAR(50) NOT NULL,
    `subject_type` VARCHAR(255) NULL,
    `subject_id` BIGINT UNSIGNED NULL,
    `description` TEXT NOT NULL,
    `old_values` JSON NULL,
    `new_values` JSON NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `activity_logs_subject_index` (`subject_type`, `subject_id`),
    KEY `activity_logs_user_index` (`user_id`),
    CONSTRAINT `activity_logs_user_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول سشن‌ها
CREATE TABLE IF NOT EXISTS `sessions` (
    `id` VARCHAR(255) NOT NULL,
    `user_id` BIGINT UNSIGNED NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `payload` LONGTEXT NOT NULL,
    `last_activity` INT NOT NULL,
    PRIMARY KEY (`id`),
    KEY `sessions_user_id_index` (`user_id`),
    KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================
-- داده‌های اولیه
-- ============================================

-- ایرلاین‌ها
INSERT INTO `airlines` (`code`, `name`, `name_en`, `quality_score`, `avg_delay_minutes`, `on_time_percentage`, `is_active`, `is_domestic`, `is_international`, `created_at`, `updated_at`) VALUES
('W5', 'ماهان', 'Mahan Air', 4.20, 25.00, 78.00, 1, 1, 1, NOW(), NOW()),
('IR', 'ایران ایر', 'Iran Air', 4.00, 30.00, 75.00, 1, 1, 1, NOW(), NOW()),
('EP', 'آسمان', 'Aseman Airlines', 3.50, 35.00, 70.00, 1, 1, 0, NOW(), NOW()),
('QB', 'قشم ایر', 'Qeshm Air', 3.80, 28.00, 72.00, 1, 1, 1, NOW(), NOW()),
('B9', 'کیش ایر', 'Kish Air', 3.70, 30.00, 71.00, 1, 1, 1, NOW(), NOW()),
('ZV', 'زاگرس', 'Zagros Airlines', 3.40, 38.00, 66.00, 1, 1, 0, NOW(), NOW()),
('I3', 'آتا', 'ATA Airlines', 3.60, 32.00, 68.00, 1, 1, 0, NOW(), NOW()),
('JI', 'معراج', 'Meraj Airlines', 3.90, 26.00, 74.00, 1, 1, 0, NOW(), NOW());

-- قالب‌های پرامپت
INSERT INTO `ai_prompt_templates` (`slug`, `name`, `category`, `system_prompt`, `user_prompt_template`, `is_active`, `is_default`, `created_at`, `updated_at`) VALUES
('travel_assistant', 'دستیار سفر', 'chat', 'شما یک دستیار هوشمند سفر به نام سفیر هستید. به کاربران در انتخاب بهترین پرواز کمک کنید.', 'کاربر می‌پرسد: {message}', 1, 1, NOW(), NOW()),
('flight_recommendation', 'پیشنهاد پرواز', 'recommendation', 'با توجه به ترجیحات کاربر، بهترین پروازها را پیشنهاد دهید.', 'ترجیحات: {preferences}\nپروازهای موجود: {flights}', 1, 1, NOW(), NOW());
