-- Deploy Live 2026-05-25 — Hotels extra fields + hotel_locations
-- Rulează în phpMyAdmin pe baza de date live

CREATE TABLE IF NOT EXISTS `hotel_locations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `slug` varchar(150) NOT NULL,
  `type` enum('tara','regiune','oras') NOT NULL DEFAULT 'oras',
  `parent_id` bigint unsigned DEFAULT NULL,
  `sort_order` smallint unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `hotel_locations_slug_index` (`slug`),
  KEY `hotel_locations_parent_id_index` (`parent_id`),
  CONSTRAINT `hotel_locations_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `hotel_locations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE `hotels`
    ADD COLUMN IF NOT EXISTS `seo_title`             VARCHAR(255)      NULL AFTER `is_featured`,
    ADD COLUMN IF NOT EXISTS `seo_description`       TEXT              NULL AFTER `seo_title`,
    ADD COLUMN IF NOT EXISTS `seo_image`             VARCHAR(255)      NULL AFTER `seo_description`,
    ADD COLUMN IF NOT EXISTS `min_nights`            TINYINT UNSIGNED  NOT NULL DEFAULT 1 AFTER `check_out_time`,
    ADD COLUMN IF NOT EXISTS `advance_booking_days`  SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER `min_nights`,
    ADD COLUMN IF NOT EXISTS `vacation_cards`        JSON              NULL AFTER `advance_booking_days`,
    ADD COLUMN IF NOT EXISTS `cancellation_policy`   TEXT              NULL AFTER `rules`,
    ADD COLUMN IF NOT EXISTS `rules`                 TEXT              NULL AFTER `website`,
    ADD COLUMN IF NOT EXISTS `hotel_location_id`     BIGINT UNSIGNED   NULL AFTER `destination_id`,
    ADD COLUMN IF NOT EXISTS `video_url`             VARCHAR(500)      NULL AFTER `gallery`,
    ADD COLUMN IF NOT EXISTS `main_video_url`        VARCHAR(500)      NULL AFTER `video_url`,
    ADD COLUMN IF NOT EXISTS `video_as_main`         TINYINT(1)        NOT NULL DEFAULT 0 AFTER `main_video_url`;

CREATE TABLE IF NOT EXISTS `hotel_attribute_categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `icon` varchar(80) DEFAULT NULL,
  `sort_order` smallint NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hotel_attribute_categories_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hotel_attributes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `category_id` bigint unsigned DEFAULT NULL,
  `icon` varchar(80) DEFAULT NULL,
  `sort_order` smallint NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `hotel_attributes_category_id_foreign` (`category_id`),
  CONSTRAINT `hotel_attributes_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `hotel_attribute_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hotel_attribute_hotel` (
  `hotel_id` bigint unsigned NOT NULL,
  `hotel_attribute_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`hotel_id`,`hotel_attribute_id`),
  KEY `hotel_attribute_hotel_hotel_attribute_id_foreign` (`hotel_attribute_id`),
  CONSTRAINT `hotel_attribute_hotel_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `hotel_attribute_hotel_hotel_attribute_id_foreign` FOREIGN KEY (`hotel_attribute_id`) REFERENCES `hotel_attributes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hotel_room_availability` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `room_id` bigint unsigned NOT NULL,
  `date` date NOT NULL,
  `available_count` smallint unsigned DEFAULT NULL,
  `price_override` decimal(10,2) DEFAULT NULL,
  `status` enum('available','blocked') NOT NULL DEFAULT 'available',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hotel_room_availability_room_id_date_unique` (`room_id`,`date`),
  CONSTRAINT `hotel_room_availability_room_id_foreign` FOREIGN KEY (`room_id`) REFERENCES `hotel_rooms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hotel_room_ical_feeds` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `room_id` bigint unsigned NOT NULL,
  `hotel_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `platform` varchar(30) NOT NULL,
  `label` varchar(100) DEFAULT NULL,
  `ical_url` varchar(1000) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `auto_block` tinyint(1) NOT NULL DEFAULT '1',
  `sync_count` int unsigned NOT NULL DEFAULT '0',
  `events_imported` int unsigned NOT NULL DEFAULT '0',
  `last_sync_at` timestamp NULL DEFAULT NULL,
  `last_sync_status` varchar(20) DEFAULT NULL,
  `last_sync_error` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `hotel_room_ical_feeds_room_id_foreign` (`room_id`),
  KEY `hotel_room_ical_feeds_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `hotel_room_ical_feeds_room_id_foreign` FOREIGN KEY (`room_id`) REFERENCES `hotel_rooms` (`id`) ON DELETE CASCADE,
  CONSTRAINT `hotel_room_ical_feeds_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `hotel_rooms`
    ADD COLUMN IF NOT EXISTS `ical_export_token` VARCHAR(64) NULL DEFAULT NULL AFTER `sort_order`;

ALTER TABLE `hotels` DROP COLUMN IF EXISTS `website`;

INSERT IGNORE INTO `migrations` (`migration`, `batch`)
VALUES
    ('2026_05_25_120000_add_extra_fields_to_hotels_table', 99),
    ('2026_05_25_085211_add_vacation_cards_and_advance_booking_to_hotels_table', 99),
    ('2026_05_25_091402_create_hotel_locations_table', 99),
    ('2026_05_25_091402_add_hotel_location_id_to_hotels_table', 99),
    ('2026_05_25_200000_add_video_fields_to_hotels_table', 99),
    ('2026_05_25_210000_create_hotel_attribute_categories_table', 99),
    ('2026_05_25_210001_create_hotel_attributes_table', 99),
    ('2026_05_25_220000_create_hotel_room_availability_table', 99),
    ('2026_05_25_230000_create_hotel_room_ical_feeds_table', 99),
    ('2026_05_25_240000_drop_website_from_hotels_table', 99);