SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

DROP TABLE IF EXISTS reports;
DROP TABLE IF EXISTS competitors;
DROP TABLE IF EXISTS pricing_rules;
DROP TABLE IF EXISTS forecasts;
DROP TABLE IF EXISTS reservations;
DROP TABLE IF EXISTS room_types;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS password_reset_tokens;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS cache;
DROP TABLE IF EXISTS cache_locks;
DROP TABLE IF EXISTS jobs;
DROP TABLE IF EXISTS job_batches;
DROP TABLE IF EXISTS failed_jobs;
DROP TABLE IF EXISTS hotels;

CREATE TABLE hotels (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(255) NOT NULL,
  city VARCHAR(255) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  total_rooms INT NOT NULL DEFAULT 0,
  gross_profit_margin DECIMAL(5,2) NOT NULL DEFAULT 35.00,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY hotels_code_unique (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  hotel_id BIGINT UNSIGNED NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  role VARCHAR(255) NOT NULL DEFAULT 'reservation_staff',
  email_verified_at TIMESTAMP NULL,
  password VARCHAR(255) NOT NULL,
  remember_token VARCHAR(100) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY users_email_unique (email),
  KEY users_hotel_id_index (hotel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE password_reset_tokens (
  email VARCHAR(255) NOT NULL,
  token VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NULL,
  PRIMARY KEY (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE 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;

CREATE TABLE cache (
  `key` VARCHAR(255) NOT NULL,
  value MEDIUMTEXT NOT NULL,
  expiration INT NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cache_locks (
  `key` VARCHAR(255) NOT NULL,
  owner VARCHAR(255) NOT NULL,
  expiration INT NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  queue VARCHAR(255) NOT NULL,
  payload LONGTEXT NOT NULL,
  attempts TINYINT UNSIGNED NOT NULL,
  reserved_at INT UNSIGNED NULL,
  available_at INT UNSIGNED NOT NULL,
  created_at INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY jobs_queue_index (queue)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE job_batches (
  id VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  total_jobs INT NOT NULL,
  pending_jobs INT NOT NULL,
  failed_jobs INT NOT NULL,
  failed_job_ids LONGTEXT NOT NULL,
  options MEDIUMTEXT NULL,
  cancelled_at INT NULL,
  created_at INT NOT NULL,
  finished_at INT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE failed_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid VARCHAR(255) NOT NULL,
  connection TEXT NOT NULL,
  queue TEXT NOT NULL,
  payload LONGTEXT NOT NULL,
  exception LONGTEXT NOT NULL,
  failed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY failed_jobs_uuid_unique (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE room_types (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  hotel_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(255) NOT NULL,
  room_count INT NOT NULL,
  base_rate DECIMAL(12,2) NOT NULL,
  min_rate DECIMAL(12,2) NOT NULL,
  max_rate DECIMAL(12,2) NOT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY room_types_hotel_code_unique (hotel_id, code),
  CONSTRAINT room_types_hotel_id_foreign FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reservations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  hotel_id BIGINT UNSIGNED NOT NULL,
  room_type_id BIGINT UNSIGNED NOT NULL,
  stay_date DATE NOT NULL,
  rooms_sold INT NOT NULL,
  room_revenue DECIMAL(12,2) NOT NULL,
  total_revenue DECIMAL(12,2) NOT NULL,
  market_segment VARCHAR(255) NOT NULL DEFAULT 'Transient',
  booked_at DATE NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  CONSTRAINT reservations_hotel_id_foreign FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE,
  CONSTRAINT reservations_room_type_id_foreign FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE forecasts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  hotel_id BIGINT UNSIGNED NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  granularity VARCHAR(255) NOT NULL DEFAULT 'daily',
  occupancy_forecast DECIMAL(6,2) NOT NULL,
  adr_forecast DECIMAL(12,2) NOT NULL,
  revenue_forecast DECIMAL(12,2) NOT NULL,
  accuracy DECIMAL(6,2) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  CONSTRAINT forecasts_hotel_id_foreign FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pricing_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  hotel_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  occupancy_min DECIMAL(6,2) NULL,
  occupancy_max DECIMAL(6,2) NULL,
  pickup_min INT NULL,
  booking_window_min INT NULL,
  booking_window_max INT NULL,
  day_of_week VARCHAR(255) NULL,
  season VARCHAR(255) NULL,
  adjustment_percent DECIMAL(6,2) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  CONSTRAINT pricing_rules_hotel_id_foreign FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE competitors (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  hotel_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  rate_date DATE NOT NULL,
  competitor_rate DECIMAL(12,2) NOT NULL,
  room_type VARCHAR(255) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  CONSTRAINT competitors_hotel_id_foreign FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  hotel_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(255) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  payload JSON NOT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  PRIMARY KEY (id),
  CONSTRAINT reports_hotel_id_foreign FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO hotels (id, name, code, city, currency, total_rooms, gross_profit_margin, created_at, updated_at) VALUES
(1, 'Saigon Harbor Hotel', 'SAIGON-DEMO', 'Ho Chi Minh City', 'USD', 120, 38.00, NOW(), NOW());

INSERT INTO users (id, hotel_id, name, email, role, password, created_at, updated_at) VALUES
(1, 1, 'Admin', '[email protected]', 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi.', NOW(), NOW()),
(2, 1, 'Revenue Manager', '[email protected]', 'revenue_manager', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi.', NOW(), NOW()),
(3, 1, 'Reservation Staff', '[email protected]', 'reservation_staff', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi.', NOW(), NOW());

INSERT INTO room_types (id, hotel_id, name, code, room_count, base_rate, min_rate, max_rate, created_at, updated_at) VALUES
(1, 1, 'Superior', 'SUP', 55, 85.00, 62.00, 140.00, NOW(), NOW()),
(2, 1, 'Deluxe', 'DLX', 35, 115.00, 82.00, 190.00, NOW(), NOW()),
(3, 1, 'Suite', 'STE', 20, 185.00, 135.00, 320.00, NOW(), NOW()),
(4, 1, 'Family', 'FAM', 10, 155.00, 110.00, 260.00, NOW(), NOW());

INSERT INTO pricing_rules (hotel_id, name, occupancy_min, occupancy_max, pickup_min, booking_window_min, booking_window_max, day_of_week, season, adjustment_percent, active, created_at, updated_at) VALUES
(1, 'High Occupancy Yield', 80.00, NULL, NULL, NULL, NULL, NULL, NULL, 15.00, 1, NOW(), NOW()),
(1, 'Low Occupancy Stimulus', NULL, 40.00, NULL, NULL, NULL, NULL, NULL, -10.00, 1, NOW(), NOW()),
(1, 'Weekend Pickup Premium', NULL, NULL, 18, NULL, NULL, 'Saturday', NULL, 8.00, 1, NOW(), NOW());

INSERT INTO reservations (hotel_id, room_type_id, stay_date, rooms_sold, room_revenue, total_revenue, market_segment, booked_at, created_at, updated_at) VALUES
(1, 1, CURDATE(), 44, 3960.00, 4610.00, 'Transient', DATE_SUB(CURDATE(), INTERVAL 12 DAY), NOW(), NOW()),
(1, 2, CURDATE(), 27, 3260.00, 3890.00, 'Corporate', DATE_SUB(CURDATE(), INTERVAL 18 DAY), NOW(), NOW()),
(1, 3, CURDATE(), 15, 2925.00, 3480.00, 'OTA', DATE_SUB(CURDATE(), INTERVAL 7 DAY), NOW(), NOW()),
(1, 4, CURDATE(), 8, 1320.00, 1580.00, 'Group', DATE_SUB(CURDATE(), INTERVAL 20 DAY), NOW(), NOW()),
(1, 1, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 39, 3510.00, 4070.00, 'Transient', DATE_SUB(CURDATE(), INTERVAL 13 DAY), NOW(), NOW()),
(1, 2, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 24, 2760.00, 3310.00, 'Corporate', DATE_SUB(CURDATE(), INTERVAL 16 DAY), NOW(), NOW()),
(1, 3, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 13, 2535.00, 2990.00, 'OTA', DATE_SUB(CURDATE(), INTERVAL 8 DAY), NOW(), NOW()),
(1, 4, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 7, 1120.00, 1370.00, 'Group', DATE_SUB(CURDATE(), INTERVAL 22 DAY), NOW(), NOW()),
(1, 1, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 36, 3060.00, 3560.00, 'Transient', DATE_SUB(CURDATE(), INTERVAL 15 DAY), NOW(), NOW()),
(1, 2, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 22, 2530.00, 3040.00, 'OTA', DATE_SUB(CURDATE(), INTERVAL 10 DAY), NOW(), NOW());

INSERT INTO competitors (hotel_id, name, rate_date, competitor_rate, room_type, created_at, updated_at) VALUES
(1, 'Hotel Alpha', CURDATE(), 102.00, 'Deluxe', NOW(), NOW()),
(1, 'Bayfront Suites', CURDATE(), 126.00, 'Deluxe', NOW(), NOW()),
(1, 'Central Palace', CURDATE(), 111.00, 'Deluxe', NOW(), NOW()),
(1, 'Urban Nest', CURDATE(), 95.00, 'Deluxe', NOW(), NOW());

INSERT INTO forecasts (hotel_id, period_start, period_end, granularity, occupancy_forecast, adr_forecast, revenue_forecast, accuracy, created_at, updated_at) VALUES
(1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 1 DAY), 'daily', 74.50, 112.80, 10090.00, 87.20, NOW(), NOW());