-- MySQL schema for B&B Council Booking Portal
CREATE TABLE organisations (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  quickbooks_customer_id VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE services (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  organisation_id BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (organisation_id) REFERENCES organisations(id)
);

CREATE TABLE users (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  work_email VARCHAR(255) NOT NULL UNIQUE,
  role ENUM('INTERNAL_ADMIN','ACCOUNTS','ORG_ADMIN','ORG_BOOKING_ADMIN','ORG_USER') NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  organisation_id BIGINT UNSIGNED NULL,
  service_id BIGINT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organisation_id) REFERENCES organisations(id),
  FOREIGN KEY (service_id) REFERENCES services(id)
);

CREATE TABLE tenants (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  dob_enc VARCHAR(255) NOT NULL,
  ni_enc VARCHAR(255) NULL,
  ni_hash VARCHAR(255) NULL UNIQUE,
  is_flagged TINYINT(1) NOT NULL DEFAULT 0,
  flag_reason VARCHAR(255) NULL,
  flagged_at DATETIME NULL,
  flag_evidence_doc_id VARCHAR(255) NULL,
  identifiers_json JSON NOT NULL DEFAULT (JSON_OBJECT()),
  created_by_user_id BIGINT UNSIGNED NOT NULL
);

CREATE TABLE bookings (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  organisation_id BIGINT UNSIGNED NOT NULL,
  service_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NULL,
  room_type VARCHAR(50) NOT NULL,
  tenant_id BIGINT UNSIGNED NOT NULL,
  state ENUM('DRAFT','PENDING_APPROVAL','APPROVED_WINDOW_REQUIRED','WINDOW_ACCEPTED','PENDING_CHECKIN','ACTIVE','END_SCHEDULED','ENDED','CANCELLED','NO_SHOW') NOT NULL DEFAULT 'DRAFT',
  requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  approved_at DATETIME NULL,
  billing_schedule VARCHAR(50) NULL,
  weekly_rate DECIMAL(10,2) NULL,
  rate_currency VARCHAR(10) NULL DEFAULT 'GBP',
  rate_confirmed_at DATETIME NULL,
  window_start DATETIME NULL,
  window_end DATETIME NULL,
  window_ack_at DATETIME NULL,
  secured_at DATETIME NULL,
  secure_charge_invoice_id VARCHAR(255) NULL,
  checkin_at DATETIME NULL,
  checkout_at DATETIME NULL,
  end_reason VARCHAR(255) NULL,
  breach_reason_id VARCHAR(255) NULL,
  notes TEXT NULL,
  rules_version VARCHAR(50) NULL,
  cancellation_same_day TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (organisation_id) REFERENCES organisations(id),
  FOREIGN KEY (service_id) REFERENCES services(id),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

CREATE TABLE booking_events (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  booking_id BIGINT UNSIGNED NOT NULL,
  actor_user_id BIGINT UNSIGNED NOT NULL,
  from_state VARCHAR(50) NULL,
  to_state VARCHAR(50) NULL,
  reason VARCHAR(255) NULL,
  notes TEXT NULL,
  ip VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (booking_id) REFERENCES bookings(id)
);

CREATE TABLE invoices (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  organisation_id BIGINT UNSIGNED NOT NULL,
  quickbooks_invoice_id VARCHAR(255) NULL,
  week_ending_date DATE NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'draft',
  subtotal DECIMAL(10,2) NULL,
  tax DECIMAL(10,2) NULL,
  total DECIMAL(10,2) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (organisation_id) REFERENCES organisations(id)
);

CREATE TABLE invoice_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  invoice_id BIGINT UNSIGNED NOT NULL,
  booking_id BIGINT UNSIGNED NOT NULL,
  description VARCHAR(255) NOT NULL,
  qty_nights INT NOT NULL,
  unit_rate DECIMAL(10,2) NOT NULL,
  line_total DECIMAL(10,2) NOT NULL,
  tags_json JSON NOT NULL DEFAULT (JSON_OBJECT()),
  FOREIGN KEY (invoice_id) REFERENCES invoices(id),
  FOREIGN KEY (booking_id) REFERENCES bookings(id)
);

CREATE TABLE otp_codes (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  code_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  consumed_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);
