-- Veritabanını oluştur
CREATE DATABASE IF NOT EXISTS `sabeeapp_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `sabeeapp_db`;

-- Hotels tablosu
CREATE TABLE IF NOT EXISTS `hotels` (
  `id` int NOT NULL AUTO_INCREMENT,
  `hotel_name` varchar(255) NOT NULL,
  `sabeeapp_id` varchar(50) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_sabeeapp_id` (`sabeeapp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Hotel OTA eşleştirme tablosu
CREATE TABLE IF NOT EXISTS `hotel_ota` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int NOT NULL,
  `hotel_id` int NOT NULL,
  `client_hotel_id` varchar(50) NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_mapping` (`client_id`, `hotel_id`),
  FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Room Types tablosu
CREATE TABLE IF NOT EXISTS `room_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `hotel_id` int NOT NULL,
  `room_type_id` varchar(50) NOT NULL,
  `sabeeapp_room_type_id` varchar(50) NOT NULL,
  `room_type_name` varchar(255) NOT NULL,
  `max_occupancy` int NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_room_type` (`hotel_id`, `room_type_id`),
  FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Rooms tablosu
CREATE TABLE IF NOT EXISTS `rooms` (
  `id` int NOT NULL AUTO_INCREMENT,
  `hotel_id` int NOT NULL,
  `room_type_id` int NOT NULL,
  `room_id` varchar(50) NOT NULL,
  `sabeeapp_room_id` varchar(50) NOT NULL,
  `room_name` varchar(255) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_room` (`hotel_id`, `room_id`),
  FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Bookings tablosu
CREATE TABLE IF NOT EXISTS `bookings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `booking_id` varchar(50) NOT NULL,
  `sabeeapp_reference_id` varchar(50) NOT NULL,
  `sabeeapp_reservation_code` varchar(50) NOT NULL,
  `booking_type` varchar(20) NOT NULL,
  `client_id` int NOT NULL,
  `hotel_id` int NOT NULL,
  `room_type_id` int NOT NULL,
  `room_id` int DEFAULT NULL,
  `checkin_date` date NOT NULL,
  `checkout_date` date NOT NULL,
  `adult_count` int NOT NULL,
  `child_count` int NOT NULL,
  `child_ages` varchar(100) DEFAULT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `status` varchar(20) NOT NULL,
  `guest_first_name` varchar(100) NOT NULL,
  `guest_last_name` varchar(100) NOT NULL,
  `guest_email` varchar(255) NOT NULL,
  `guest_phone` varchar(50) NOT NULL,
  `notes` text,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_booking` (`booking_id`),
  UNIQUE KEY `unique_sabeeapp_booking` (`sabeeapp_reference_id`),
  FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ID Eşleştirme Tabloları
CREATE TABLE IF NOT EXISTS hotel_mappings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_hotel_id VARCHAR(50) NOT NULL,
    sabeeapp_hotel_id VARCHAR(50) NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_client_hotel (client_hotel_id),
    UNIQUE KEY unique_sabeeapp_hotel (sabeeapp_hotel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS room_type_mappings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_hotel_id VARCHAR(50) NOT NULL,
    client_room_type_id VARCHAR(50) NOT NULL,
    sabeeapp_room_type_id VARCHAR(50) NOT NULL,
    room_name VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_client_room_type (client_hotel_id, client_room_type_id),
    UNIQUE KEY unique_sabeeapp_room_type (client_hotel_id, sabeeapp_room_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rate_plan_mappings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_hotel_id VARCHAR(50) NOT NULL,
    client_rate_plan_id VARCHAR(50) NOT NULL,
    sabeeapp_rate_plan_id VARCHAR(50) NOT NULL,
    rate_plan_name VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_client_rate_plan (client_hotel_id, client_rate_plan_id),
    UNIQUE KEY unique_sabeeapp_rate_plan (client_hotel_id, sabeeapp_rate_plan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `hotels` (`hotel_name`, `sabeeapp_id`, `is_active`) VALUES
('Test Hotel', '320', 1);

INSERT INTO `hotel_ota` (`client_id`, `hotel_id`, `client_hotel_id`) VALUES
(1, 1, '9608'); 

INSERT INTO hotel_mappings (client_hotel_id, sabeeapp_hotel_id) VALUES 
('HTL789', '5267')
ON DUPLICATE KEY UPDATE is_active = 1;

INSERT INTO room_type_mappings (client_hotel_id, client_room_type_id, sabeeapp_room_type_id, room_name) VALUES 
('HTL789', 'RT001', '19988_1628', 'Aile Odası Deluxe Havuz Manzaralı')
ON DUPLICATE KEY UPDATE is_active = 1;

INSERT INTO rate_plan_mappings (client_hotel_id, client_rate_plan_id, sabeeapp_rate_plan_id, rate_plan_name) VALUES 
('HTL789', 'RP001', '1024_0', 'Alacarte Her Şey Dahil - İptal Edilemez')
ON DUPLICATE KEY UPDATE is_active = 1; 