-- Lea Lodge booking platform: settings, lifecycle, raffle, reconciliation,
-- documents, communications, seating, check-in, retention and diagnostics.
-- Run once after upgrade-003-dietary-menu-alternatives.sql.

ALTER TABLE meetings
  ADD COLUMN IF NOT EXISTS lifecycle_status ENUM('draft','open','closed','completed','archived','cancelled') NOT NULL DEFAULT 'open' AFTER bookings_open,
  ADD COLUMN IF NOT EXISTS test_mode TINYINT(1) NOT NULL DEFAULT 0 AFTER lifecycle_status,
  ADD COLUMN IF NOT EXISTS special_event_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER visitor_price,
  ADD COLUMN IF NOT EXISTS special_event_type ENUM('none','ladies_evening','white_table','blue_table','other') NOT NULL DEFAULT 'none' AFTER special_event_enabled,
  ADD COLUMN IF NOT EXISTS special_event_label VARCHAR(120) NULL AFTER special_event_type,
  ADD COLUMN IF NOT EXISTS special_event_description TEXT NULL AFTER special_event_label,
  ADD COLUMN IF NOT EXISTS raffle_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER special_event_description,
  ADD COLUMN IF NOT EXISTS raffle_ticket_price DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER raffle_enabled,
  ADD COLUMN IF NOT EXISTS raffle_max_per_booking INT UNSIGNED NULL AFTER raffle_ticket_price,
  ADD COLUMN IF NOT EXISTS raffle_description VARCHAR(500) NULL AFTER raffle_max_per_booking,
  ADD COLUMN IF NOT EXISTS template_source_id BIGINT UNSIGNED NULL AFTER raffle_description;
ALTER TABLE meetings MODIFY COLUMN special_event_type ENUM('none','ladies_evening','white_table','blue_table','other') NOT NULL DEFAULT 'none';

-- Older stable installations do not yet have attendee_type. Create it before
-- widening the permitted values so this upgrade works with or without the
-- superseded special-events draft upgrade.
ALTER TABLE bookings
  ADD COLUMN IF NOT EXISTS attendee_type ENUM('lodge_member','visiting_member') NOT NULL DEFAULT 'visiting_member' AFTER is_lodge_member;

ALTER TABLE bookings
  ADD COLUMN IF NOT EXISTS phone VARCHAR(30) NULL AFTER email;

ALTER TABLE bookings
  MODIFY attendee_type ENUM('lodge_member','visiting_member','special_event_guest','ladies_evening_guest','white_table_guest','blue_table_guest') NOT NULL DEFAULT 'visiting_member',
  ADD COLUMN IF NOT EXISTS attendee_type_label VARCHAR(120) NULL AFTER attendee_type,
  ADD COLUMN IF NOT EXISTS dining_subtotal DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER payment_selection,
  ADD COLUMN IF NOT EXISTS raffle_ticket_quantity INT UNSIGNED NOT NULL DEFAULT 0 AFTER dining_subtotal,
  ADD COLUMN IF NOT EXISTS raffle_ticket_unit_price DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER raffle_ticket_quantity,
  ADD COLUMN IF NOT EXISTS raffle_subtotal DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER raffle_ticket_unit_price,
  ADD COLUMN IF NOT EXISTS amount_received DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER total_charge,
  ADD COLUMN IF NOT EXISTS refund_amount DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER amount_received,
  ADD COLUMN IF NOT EXISTS refunded_at DATETIME NULL AFTER paid_at,
  ADD COLUMN IF NOT EXISTS treasurer_notes TEXT NULL AFTER refunded_at,
  ADD COLUMN IF NOT EXISTS checked_in_by BIGINT UNSIGNED NULL AFTER arrived_at,
  ADD COLUMN IF NOT EXISTS test_booking TINYINT(1) NOT NULL DEFAULT 0 AFTER calendar_sent_at,
  ADD CONSTRAINT fk_booking_checked_in_by FOREIGN KEY (checked_in_by) REFERENCES users(id) ON DELETE SET NULL;

ALTER TABLE users MODIFY role ENUM('admin','secretary','dining','treasurer','worshipful_master','readonly','member') NOT NULL DEFAULT 'member';

CREATE TABLE lodge_settings (
  id TINYINT UNSIGNED NOT NULL PRIMARY KEY DEFAULT 1,
  lodge_name VARCHAR(150) NOT NULL,
  lodge_number VARCHAR(30) NOT NULL,
  lodge_short_name VARCHAR(100) NULL,
  order_name VARCHAR(150) NULL,
  province_name VARCHAR(200) NULL,
  lodge_logo_url TEXT NULL,
  lodge_honours_logo_url TEXT NULL,
  province_logo_url TEXT NULL,
  order_logo_url TEXT NULL,
  companion_order_logo_url TEXT NULL,
  charity_logo_url TEXT NULL,
  primary_colour VARCHAR(7) NOT NULL DEFAULT '#990033',
  secondary_colour VARCHAR(7) NOT NULL DEFAULT '#122849',
  accent_colour VARCHAR(7) NOT NULL DEFAULT '#c7a646',
  background_colour VARCHAR(7) NOT NULL DEFAULT '#f7f1e3',
  panel_colour VARCHAR(7) NOT NULL DEFAULT '#fffdfa',
  text_colour VARCHAR(7) NOT NULL DEFAULT '#27231e',
  heading_font VARCHAR(120) NOT NULL DEFAULT 'Georgia, serif',
  body_font VARCHAR(120) NOT NULL DEFAULT 'system-ui, sans-serif',
  browser_title VARCHAR(200) NULL,
  favicon_url TEXT NULL,
  secretary_email VARCHAR(254) NULL,
  treasurer_email VARCHAR(254) NULL,
  dining_steward_email VARCHAR(254) NULL,
  director_of_ceremonies_email VARCHAR(254) NULL,
  almoner_email VARCHAR(254) NULL,
  general_email VARCHAR(254) NULL,
  provincial_website_url TEXT NULL,
  provincial_website_label VARCHAR(150) NULL,
  order_website_url TEXT NULL,
  order_website_label VARCHAR(150) NULL,
  book_of_constitutions_url TEXT NULL,
  bylaws_url TEXT NULL,
  timezone VARCHAR(80) NOT NULL DEFAULT 'Europe/London',
  currency_code CHAR(3) NOT NULL DEFAULT 'GBP',
  retention_meetings INT UNSIGNED NOT NULL DEFAULT 4,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_lodge_settings_user FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO lodge_settings (id,lodge_name,lodge_number,lodge_short_name,order_name,province_name)
SELECT 1,lodge_name,lodge_number,CONCAT(lodge_name,' ',lodge_number),'Mark Master Masons','' FROM meetings ORDER BY id LIMIT 1;

CREATE TABLE documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  description VARCHAR(500) NULL,
  document_url TEXT NOT NULL,
  visibility ENUM('public','members','officers','administrators') NOT NULL DEFAULT 'members',
  active TINYINT(1) NOT NULL DEFAULT 1,
  display_order INT UNSIGNED NOT NULL DEFAULT 0,
  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,
  CONSTRAINT fk_document_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_templates (
  template_key VARCHAR(80) PRIMARY KEY,
  display_name VARCHAR(150) NOT NULL,
  subject_template VARCHAR(250) NOT NULL,
  body_template MEDIUMTEXT NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_email_template_user FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO email_templates(template_key,display_name,subject_template,body_template) VALUES
('booking_confirmation','Booking confirmation','{{lodge_name}} booking – {{meeting_date}}','<h2>Booking confirmed</h2><p>Dear {{name}},</p><p>Your reference is <strong>{{booking_reference}}</strong>.</p><p>Total due: <strong>{{total_charge}}</strong></p>'),
('payment_reminder','Payment reminder','Payment reminder – {{booking_reference}}','<p>Dear {{name}},</p><p>Our records show a balance of <strong>{{balance}}</strong> for {{meeting_date}}.</p>{{payment_details}}'),
('booking_cancelled','Booking cancelled','Booking cancelled – {{booking_reference}}','<p>Your booking for {{meeting_date}} has been cancelled.</p>'),
('waiting_list','Waiting list confirmation','Waiting list – {{meeting_date}}','<p>You have been added to the waiting list. We will contact you if a place becomes available.</p>'),
('place_offered','Waiting-list place offered','A dining place is available','<p>A place is available until {{offer_expiry}}. Please use your booking code to accept it.</p>'),
('meeting_changed','Meeting details changed','Important update – {{meeting_date}}','<p>The meeting details have changed. Please review the updated information.</p>');

CREATE TABLE communication_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  meeting_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  template_key VARCHAR(80) NULL,
  recipient VARCHAR(254) NOT NULL,
  subject VARCHAR(250) NOT NULL,
  status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  error_message TEXT NULL,
  sent_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  INDEX idx_communication_meeting(meeting_id,created_at),
  CONSTRAINT fk_communication_meeting FOREIGN KEY(meeting_id) REFERENCES meetings(id) ON DELETE SET NULL,
  CONSTRAINT fk_communication_booking FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE SET NULL,
  CONSTRAINT fk_communication_user FOREIGN KEY(sent_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE booking_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  booking_id BIGINT UNSIGNED NOT NULL,
  changed_by_user_id BIGINT UNSIGNED NULL,
  changed_by_type ENUM('attendee','officer','system') NOT NULL DEFAULT 'system',
  field_name VARCHAR(100) NOT NULL,
  old_value TEXT NULL,
  new_value TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_history_booking(booking_id,created_at),
  CONSTRAINT fk_history_booking FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
  CONSTRAINT fk_history_user FOREIGN KEY(changed_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dining_tables (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  meeting_id BIGINT UNSIGNED NOT NULL,
  table_name VARCHAR(100) NOT NULL,
  capacity INT UNSIGNED NOT NULL DEFAULT 8,
  display_order INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uq_dining_table(meeting_id,table_name),
  CONSTRAINT fk_table_meeting FOREIGN KEY(meeting_id) REFERENCES meetings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE seat_assignments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  dining_table_id BIGINT UNSIGNED NOT NULL,
  booking_id BIGINT UNSIGNED NOT NULL,
  guest_id BIGINT UNSIGNED NULL,
  seat_number INT UNSIGNED NULL,
  UNIQUE KEY uq_booking_guest_seat(booking_id,guest_id),
  CONSTRAINT fk_seat_table FOREIGN KEY(dining_table_id) REFERENCES dining_tables(id) ON DELETE CASCADE,
  CONSTRAINT fk_seat_booking FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
  CONSTRAINT fk_seat_guest FOREIGN KEY(guest_id) REFERENCES guests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE system_jobs (
  job_key VARCHAR(80) PRIMARY KEY,
  last_started_at DATETIME NULL,
  last_completed_at DATETIME NULL,
  last_status ENUM('never','running','success','failed') NOT NULL DEFAULT 'never',
  last_message TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO system_jobs(job_key) VALUES ('reminders'),('retention'),('backup');

CREATE TABLE backup_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('success','failed') NOT NULL,
  message TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_backup_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE meetings SET special_event_enabled=(special_event_type<>'none');
UPDATE bookings SET dining_subtotal=total_charge, amount_received=IF(payment_status='paid',total_charge,0);