CREATE TABLE IF NOT EXISTS booking_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_token CHAR(48) NOT NULL,
  invoice_number VARCHAR(64) NULL,
  payment_provider VARCHAR(30) NULL,
  payment_reference VARCHAR(128) NULL,
  payment_status VARCHAR(24) NOT NULL DEFAULT 'PAID',
  status VARCHAR(32) NOT NULL DEFAULT 'PAID',
  customer_first_name VARCHAR(100) NOT NULL,
  customer_last_name VARCHAR(100) NOT NULL,
  customer_phone VARCHAR(40) NOT NULL,
  customer_email VARCHAR(190) NOT NULL,
  address VARCHAR(255) NOT NULL,
  city VARCHAR(120) NOT NULL,
  state VARCHAR(40) NOT NULL,
  zip VARCHAR(20) NOT NULL,
  service_type VARCHAR(150) NOT NULL,
  frequency VARCHAR(80) NULL,
  scheduled_at DATETIME NOT NULL,
  duration_hours DECIMAL(5,2) NULL,
  amount DECIMAL(10,2) NOT NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  job_id BIGINT UNSIGNED NULL,
  cleaner_id BIGINT UNSIGNED NULL,
  cleaner_name VARCHAR(200) NULL,
  cleaner_phone VARCHAR(40) NULL,
  sms_consent TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_booking_token (public_token),
  UNIQUE KEY uq_booking_invoice (invoice_number),
  KEY idx_booking_payment_ref (payment_provider,payment_reference),
  KEY idx_booking_status (status),
  KEY idx_booking_job (job_id),
  KEY idx_booking_cleaner (cleaner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS booking_order_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(32) NOT NULL,
  actor_type VARCHAR(30) NOT NULL DEFAULT 'system',
  actor_id BIGINT UNSIGNED NULL,
  note VARCHAR(500) NULL,
  sms_sid VARCHAR(128) NULL,
  sms_sent TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_event_booking (booking_id),
  CONSTRAINT fk_event_booking FOREIGN KEY (booking_id) REFERENCES booking_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS booking_cleaner_offers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id BIGINT UNSIGNED NOT NULL,
  cleaner_id BIGINT UNSIGNED NOT NULL,
  distance_miles DECIMAL(8,2) NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'OFFERED',
  sms_sid VARCHAR(128) NULL,
  offered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  accepted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_booking_cleaner (booking_id, cleaner_id),
  KEY idx_offer_cleaner (cleaner_id),
  KEY idx_offer_status (status),
  CONSTRAINT fk_offer_booking FOREIGN KEY (booking_id) REFERENCES booking_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;