USE gstation;

CREATE TABLE IF NOT EXISTS site_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  setting_key VARCHAR(120) NOT NULL,
  setting_value JSON NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_site_settings_key (setting_key)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS contact_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  contact_name VARCHAR(160) NOT NULL,
  contact_email VARCHAR(190) NOT NULL,
  contact_phone VARCHAR(40) NULL,
  reason VARCHAR(120) NULL,
  message TEXT NOT NULL,
  source_ip VARBINARY(16) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_contact_messages_created_at (created_at),
  KEY idx_contact_messages_email (contact_email)
) ENGINE=InnoDB;

ALTER TABLE booking_requests
  ADD COLUMN IF NOT EXISTS extra_hours DECIMAL(5,2) NOT NULL DEFAULT 0.00 AFTER vendor_help,
  ADD COLUMN IF NOT EXISTS price_adjustment DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER extra_hours,
  ADD COLUMN IF NOT EXISTS amount_owed DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER price_adjustment;

ALTER TABLE confirmed_bookings
  ADD COLUMN IF NOT EXISTS extra_hours DECIMAL(5,2) NOT NULL DEFAULT 0.00 AFTER guest_count,
  ADD COLUMN IF NOT EXISTS price_adjustment DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER extra_hours,
  ADD COLUMN IF NOT EXISTS amount_owed DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER price_adjustment;