CREATE DATABASE IF NOT EXISTS property_platform CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE property_platform;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','editor','viewer') NOT NULL DEFAULT 'editor',
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE property_types (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  slug VARCHAR(80) NOT NULL UNIQUE,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active'
) ENGINE=InnoDB;

CREATE TABLE properties (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  property_type_id SMALLINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  slug VARCHAR(190) NOT NULL UNIQUE,
  short_description VARCHAR(500) NULL,
  description MEDIUMTEXT NULL,
  internal_notes MEDIUMTEXT NULL,
  address VARCHAR(255) NULL,
  city VARCHAR(120) NULL,
  region VARCHAR(120) NULL,
  country VARCHAR(120) NOT NULL DEFAULT 'Israel',
  postal_code VARCHAR(40) NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  owner_name VARCHAR(160) NULL,
  owner_email VARCHAR(190) NULL,
  owner_phone VARCHAR(80) NULL,
  website_url VARCHAR(500) NULL,
  booking_url VARCHAR(500) NULL,
  check_in_time TIME NULL,
  check_out_time TIME NULL,
  currency CHAR(3) NOT NULL DEFAULT 'ILS',
  base_price DECIMAL(12,2) NULL,
  max_guests SMALLINT UNSIGNED NULL,
  bedrooms DECIMAL(5,1) NULL,
  beds SMALLINT UNSIGNED NULL,
  bathrooms DECIMAL(5,1) NULL,
  kosher_level VARCHAR(120) NULL,
  kosher_certification VARCHAR(190) NULL,
  kosher_authority VARCHAR(190) NULL,
  private_sukkah TINYINT(1) NOT NULL DEFAULT 0,
  shared_sukkah TINYINT(1) NOT NULL DEFAULT 0,
  sukkah_capacity SMALLINT UNSIGNED NULL,
  shabbat_elevator TINYINT(1) NOT NULL DEFAULT 0,
  shabbat_keys TINYINT(1) NOT NULL DEFAULT 0,
  synagogue_on_site TINYINT(1) NOT NULL DEFAULT 0,
  synagogue_distance_meters INT UNSIGNED NULL,
  shabbat_meals TINYINT(1) NOT NULL DEFAULT 0,
  holiday_meals TINYINT(1) NOT NULL DEFAULT 0,
  pesach_kosher TINYINT(1) NOT NULL DEFAULT 0,
  seo_title VARCHAR(190) NULL,
  meta_description VARCHAR(320) NULL,
  canonical_url VARCHAR(500) NULL,
  featured TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('draft','active','inactive','archived') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_properties_status (status),
  INDEX idx_properties_city (city),
  INDEX idx_properties_type (property_type_id),
  INDEX idx_properties_geo (latitude, longitude),
  CONSTRAINT fk_property_type FOREIGN KEY (property_type_id) REFERENCES property_types(id),
  CONSTRAINT fk_property_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_property_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE room_types (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  property_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  slug VARCHAR(190) NOT NULL,
  description TEXT NULL,
  room_code VARCHAR(80) NULL,
  max_adults SMALLINT UNSIGNED NOT NULL DEFAULT 2,
  max_children SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  max_guests SMALLINT UNSIGNED NOT NULL DEFAULT 2,
  number_of_rooms SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  base_price DECIMAL(12,2) NULL,
  weekend_price DECIMAL(12,2) NULL,
  bed_type VARCHAR(120) NULL,
  bedrooms DECIMAL(5,1) NULL,
  bathrooms DECIMAL(5,1) NULL,
  size_sqm DECIMAL(8,2) NULL,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_room_slug (property_id, slug),
  CONSTRAINT fk_room_property FOREIGN KEY (property_id) REFERENCES properties(id)
) ENGINE=InnoDB;

CREATE TABLE amenity_categories (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  sort_order INT NOT NULL DEFAULT 0,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active'
) ENGINE=InnoDB;

CREATE TABLE amenities (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id SMALLINT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  icon VARCHAR(120) NULL,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active',
  CONSTRAINT fk_amenity_category FOREIGN KEY (category_id) REFERENCES amenity_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE property_amenities (
  property_id BIGINT UNSIGNED NOT NULL,
  amenity_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (property_id, amenity_id),
  CONSTRAINT fk_pa_property FOREIGN KEY (property_id) REFERENCES properties(id),
  CONSTRAINT fk_pa_amenity FOREIGN KEY (amenity_id) REFERENCES amenities(id)
) ENGINE=InnoDB;

CREATE TABLE room_amenities (
  room_type_id BIGINT UNSIGNED NOT NULL,
  amenity_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (room_type_id, amenity_id),
  CONSTRAINT fk_ra_room FOREIGN KEY (room_type_id) REFERENCES room_types(id),
  CONSTRAINT fk_ra_amenity FOREIGN KEY (amenity_id) REFERENCES amenities(id)
) ENGINE=InnoDB;

CREATE TABLE media (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type ENUM('property','room') NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  media_type ENUM('image','youtube') NOT NULL,
  file_path VARCHAR(500) NULL,
  youtube_url VARCHAR(500) NULL,
  title VARCHAR(190) NULL,
  caption TEXT NULL,
  alt_text VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_media_entity (entity_type, entity_id, status)
) ENGINE=InnoDB;

CREATE TABLE rates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type ENUM('property','room') NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  nightly_price DECIMAL(12,2) NULL,
  weekend_price DECIMAL(12,2) NULL,
  weekly_price DECIMAL(12,2) NULL,
  minimum_stay SMALLINT UNSIGNED NULL,
  maximum_stay SMALLINT UNSIGNED NULL,
  extra_guest_price DECIMAL(12,2) NULL,
  priority SMALLINT NOT NULL DEFAULT 0,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_rates_entity_dates (entity_type, entity_id, start_date, end_date)
) ENGINE=InnoDB;

CREATE TABLE room_inventory (
  room_type_id BIGINT UNSIGNED NOT NULL,
  inventory_date DATE NOT NULL,
  rooms_available SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  price_override DECIMAL(12,2) NULL,
  minimum_stay SMALLINT UNSIGNED NULL,
  closed TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (room_type_id, inventory_date),
  CONSTRAINT fk_inventory_room FOREIGN KEY (room_type_id) REFERENCES room_types(id)
) ENGINE=InnoDB;

CREATE TABLE property_calendar (
  property_id BIGINT UNSIGNED NOT NULL,
  calendar_date DATE NOT NULL,
  available TINYINT(1) NOT NULL DEFAULT 1,
  price_override DECIMAL(12,2) NULL,
  minimum_stay SMALLINT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (property_id, calendar_date),
  CONSTRAINT fk_calendar_property FOREIGN KEY (property_id) REFERENCES properties(id)
) ENGINE=InnoDB;

CREATE TABLE sites (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  domain VARCHAR(190) NOT NULL UNIQUE,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active'
) ENGINE=InnoDB;

CREATE TABLE property_sites (
  property_id BIGINT UNSIGNED NOT NULL,
  site_id INT UNSIGNED NOT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  featured TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (property_id, site_id),
  CONSTRAINT fk_ps_property FOREIGN KEY (property_id) REFERENCES properties(id),
  CONSTRAINT fk_ps_site FOREIGN KEY (site_id) REFERENCES sites(id)
) ENGINE=InnoDB;

CREATE TABLE audit_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  old_value JSON NULL,
  new_value JSON NULL,
  ip_address VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_entity (entity_type, entity_id),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO users (name,email,password_hash,role) VALUES ('Administrator','admin@example.com','$2y$12$HYqlBwO2Ui6PSqwF4Il5bOXnI0vRKOkm46mtwIcBt3KAX/1EGqJoG','admin');
INSERT INTO property_types (name,slug) VALUES
('Hotel','hotel'),('Vacation Apartment','vacation-apartment'),('Villa','villa'),('Zimmer','zimmer'),('Guest House','guest-house'),('Hostel','hostel'),('Resort','resort');
INSERT INTO amenity_categories (name,sort_order) VALUES
('General',10),('Room',20),('Kitchen',30),('Bathroom',40),('Family',50),('Accessibility',60),('Parking',70),('Pool & Wellness',80),('Jewish',90),('Business',100);
INSERT INTO amenities (category_id,name,slug) VALUES
(1,'Wi-Fi','wifi'),(1,'Air conditioning','air-conditioning'),(1,'Heating','heating'),(2,'Balcony','balcony'),(2,'Mini-bar','mini-bar'),(2,'Safe','safe'),(3,'Kitchen','kitchen'),(3,'Refrigerator','refrigerator'),(3,'Microwave','microwave'),(4,'Bathtub','bathtub'),(4,'Walk-in shower','walk-in-shower'),(5,'Crib','crib'),(5,'High chair','high-chair'),(6,'Wheelchair accessible','wheelchair-accessible'),(7,'Free parking','free-parking'),(8,'Swimming pool','swimming-pool'),(8,'Gym','gym'),(9,'Synagogue','synagogue'),(9,'Private sukkah','private-sukkah'),(9,'Shabbat elevator','shabbat-elevator'),(10,'Workspace','workspace');
INSERT INTO sites (name,domain) VALUES
('Main Booking Site','example.com'),('Sukkot','sukkot.org.il'),('InnBnB Israel','innbnb.co.il'),('InnBnB','innbnb.com'),('Kosher Inns','kosherinns.com'),('Zimmers','zimmers.org.il');