-- Grange Station booking database
-- MySQL 8.0+ / MariaDB 10.5+

CREATE DATABASE IF NOT EXISTS gstation
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE gstation;

CREATE TABLE admin_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  role ENUM('owner', 'manager') NOT NULL DEFAULT 'owner',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  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_admin_users_email (email)
) ENGINE=InnoDB;

CREATE TABLE booking_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(36) NOT NULL,
  status ENUM('new', 'contacted', 'tentative', 'declined', 'confirmed', 'archived') NOT NULL DEFAULT 'new',
  request_type ENUM('Event Reservation', 'Private Viewing') NOT NULL DEFAULT 'Event Reservation',
  contact_name VARCHAR(160) NOT NULL,
  contact_email VARCHAR(190) NOT NULL,
  contact_phone VARCHAR(40) NOT NULL,
  requested_date DATE NOT NULL,
  requested_start TIME NOT NULL,
  requested_end TIME NULL,
  guest_count SMALLINT UNSIGNED NULL,
  event_type VARCHAR(80) NULL,
  space_preference VARCHAR(120) NULL,
  vendor_help VARCHAR(160) NULL,
  extra_hours DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  price_adjustment DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  amount_owed DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  message TEXT NULL,
  source_ip VARBINARY(16) NULL,
  user_agent VARCHAR(255) NULL,
  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_requests_public_id (public_id),
  KEY idx_booking_requests_date_status (requested_date, status),
  KEY idx_booking_requests_email (contact_email)
) ENGINE=InnoDB;

CREATE TABLE booking_request_addons (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  request_id BIGINT UNSIGNED NOT NULL,
  addon_name VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_request_addons_request_id (request_id),
  CONSTRAINT fk_request_addons_request
    FOREIGN KEY (request_id) REFERENCES booking_requests(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE confirmed_bookings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  request_id BIGINT UNSIGNED NULL,
  title VARCHAR(180) NOT NULL,
  event_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  guest_count SMALLINT UNSIGNED NULL,
  extra_hours DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  price_adjustment DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  amount_owed DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status ENUM('confirmed', 'cancelled') NOT NULL DEFAULT 'confirmed',
  private_notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_confirmed_bookings_date_status (event_date, status),
  KEY idx_confirmed_bookings_request_id (request_id),
  CONSTRAINT fk_confirmed_bookings_request
    FOREIGN KEY (request_id) REFERENCES booking_requests(id)
    ON DELETE SET NULL,
  CONSTRAINT fk_confirmed_bookings_admin
    FOREIGN KEY (created_by) REFERENCES admin_users(id)
    ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE availability_blocks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(160) NOT NULL,
  block_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  reason ENUM('maintenance', 'owner_hold', 'private_hold', 'other') NOT NULL DEFAULT 'owner_hold',
  is_public TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_availability_blocks_date (block_date)
) ENGINE=InnoDB;

CREATE TABLE rental_addons (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  description TEXT NULL,
  starting_price DECIMAL(10,2) NULL,
  pricing_unit VARCHAR(60) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT 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_rental_addons_name (name)
) ENGINE=InnoDB;

CREATE TABLE vendor_categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_vendor_categories_name (name)
) ENGINE=InnoDB;

CREATE TABLE preferred_vendors (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id BIGINT UNSIGNED NOT NULL,
  company_name VARCHAR(160) NOT NULL,
  contact_name VARCHAR(160) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  website VARCHAR(255) NULL,
  notes TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT 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),
  KEY idx_preferred_vendors_category (category_id, is_active),
  CONSTRAINT fk_preferred_vendors_category
    FOREIGN KEY (category_id) REFERENCES vendor_categories(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB;

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

CREATE TABLE audit_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  admin_user_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(80) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id BIGINT UNSIGNED NULL,
  details JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_events_entity (entity_type, entity_id),
  KEY idx_audit_events_admin (admin_user_id),
  CONSTRAINT fk_audit_events_admin
    FOREIGN KEY (admin_user_id) REFERENCES admin_users(id)
    ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO rental_addons (name, description, starting_price, pricing_unit, sort_order) VALUES
  ('Setup Time & Extra Hours', 'Additional setup access or extra event hours.', 100.00, 'per hour', 10),
  ('Photo Session Access', 'Additional venue/photo-area access for portraits.', 100.00, 'per hour', 20),
  ('Vendor Walkthrough', 'Walkthrough time for vendors before the event.', 30.00, 'flat', 30),
  ('Table linens', 'Optional table linens priced per table.', 10.00, 'per table', 40),
  ('Catering introduction', 'Introduction to catering options.', 0.00, 'free', 50),
  ('DJ introduction', 'Introduction to DJ or music options.', 0.00, 'free', 60);

INSERT INTO vendor_categories (name, sort_order) VALUES
  ('Catering', 10),
  ('Rental Company', 20),
  ('Music and DJ', 30),
  ('Bar Service', 40),
  ('Desserts', 50);

INSERT INTO confirmed_bookings (title, event_date, start_time, end_time, status, private_notes) VALUES
  ('Confirmed private event', '2026-06-20', '14:00:00', '22:00:00', 'confirmed', 'Seed example; replace with real booking.'),
  ('Confirmed holiday booking', '2026-07-04', '10:00:00', '18:00:00', 'confirmed', 'Seed example; replace with real booking.'),
  ('Confirmed wedding', '2026-08-15', '15:00:00', '23:00:00', 'confirmed', 'Seed example; replace with real booking.');