ALTER TABLE properties
  ADD COLUMN weekday_price DECIMAL(12,2) NULL AFTER base_price,
  ADD COLUMN thursday_price DECIMAL(12,2) NULL AFTER weekday_price,
  ADD COLUMN friday_price DECIMAL(12,2) NULL AFTER thursday_price,
  ADD COLUMN max_adults SMALLINT UNSIGNED NULL AFTER max_guests,
  ADD COLUMN max_children SMALLINT UNSIGNED NULL AFTER max_adults;

UPDATE properties
SET weekday_price = COALESCE(weekday_price, base_price),
    thursday_price = COALESCE(thursday_price, base_price),
    friday_price = COALESCE(friday_price, base_price),
    max_adults = COALESCE(max_adults, max_guests),
    max_children = COALESCE(max_children, 0);

ALTER TABLE room_types
  ADD COLUMN weekday_price DECIMAL(12,2) NULL AFTER base_price,
  ADD COLUMN thursday_price DECIMAL(12,2) NULL AFTER weekday_price,
  ADD COLUMN friday_price DECIMAL(12,2) NULL AFTER thursday_price;

UPDATE room_types
SET weekday_price = COALESCE(weekday_price, base_price),
    thursday_price = COALESCE(thursday_price, weekend_price, base_price),
    friday_price = COALESCE(friday_price, weekend_price, base_price);

ALTER TABLE rates
  ADD COLUMN weekday_price DECIMAL(12,2) NULL AFTER nightly_price,
  ADD COLUMN thursday_price DECIMAL(12,2) NULL AFTER weekday_price,
  ADD COLUMN friday_price DECIMAL(12,2) NULL AFTER thursday_price,
  ADD COLUMN adjustment_type ENUM('exact','discount_percent','increase_percent') NOT NULL DEFAULT 'exact' AFTER friday_price,
  ADD COLUMN adjustment_value DECIMAL(8,2) NULL AFTER adjustment_type;

UPDATE rates
SET weekday_price = COALESCE(weekday_price, nightly_price),
    thursday_price = COALESCE(thursday_price, weekend_price, nightly_price),
    friday_price = COALESCE(friday_price, weekend_price, nightly_price)
WHERE adjustment_type='exact';

CREATE TABLE IF NOT EXISTS bookings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  property_id BIGINT UNSIGNED NOT NULL,
  room_type_id BIGINT UNSIGNED NULL,
  check_in DATE NOT NULL,
  check_out DATE NOT NULL,
  adults SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  children SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  rooms_requested SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  guest_name VARCHAR(160) NOT NULL,
  guest_email VARCHAR(190) NULL,
  guest_phone VARCHAR(80) NULL,
  total_price DECIMAL(12,2) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'ILS',
  status ENUM('pending','confirmed','cancelled','completed','archived') NOT NULL DEFAULT 'pending',
  source_site VARCHAR(190) NULL,
  internal_notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_booking_property_dates (property_id,check_in,check_out,status),
  INDEX idx_booking_room_dates (room_type_id,check_in,check_out,status),
  CONSTRAINT fk_booking_property FOREIGN KEY (property_id) REFERENCES properties(id),
  CONSTRAINT fk_booking_room FOREIGN KEY (room_type_id) REFERENCES room_types(id)
) ENGINE=InnoDB;