-- Baltique OS Core 0.4 — TheatreFlow Scheduler
-- Operational surgery scheduling layer. Does not replace OpenCare/EDM/P1/KSeF.
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS operating_rooms (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(120) NOT NULL,
  name VARCHAR(190) NOT NULL,
  location_label VARCHAR(190) NOT NULL,
  location_kind ENUM('external_hospital','baltique_sopot','baltique_gdansk','other') NOT NULL DEFAULT 'other',
  room_type ENUM('or_general','procedure_local') NOT NULL DEFAULT 'or_general',
  anesthesia_scope ENUM('general','local','both') NOT NULL DEFAULT 'general',
  status ENUM('active','future','inactive') NOT NULL DEFAULT 'active',
  default_starts_at TIME NOT NULL DEFAULT '07:00:00',
  default_ends_at TIME NOT NULL DEFAULT '19:00:00',
  sort_order INT UNSIGNED NOT NULL DEFAULT 100,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_operating_rooms_slug (slug),
  KEY idx_operating_rooms_status_type (status, room_type, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operating_room_weekday_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  room_id BIGINT UNSIGNED NOT NULL,
  weekday TINYINT UNSIGNED NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  starts_at TIME NOT NULL DEFAULT '07:00:00',
  ends_at TIME NOT NULL DEFAULT '19:00:00',
  allows_emergency TINYINT(1) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_room_weekday (room_id, weekday),
  KEY idx_weekday_rules_weekday_enabled (weekday, enabled),
  CONSTRAINT fk_or_weekday_room FOREIGN KEY (room_id) REFERENCES operating_rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS operating_room_blackouts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  room_id BIGINT UNSIGNED NULL,
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NOT NULL,
  reason VARCHAR(255) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_blackout_room_time (room_id, starts_at, ends_at),
  KEY idx_blackout_user (created_by_user_id),
  CONSTRAINT fk_or_blackout_room FOREIGN KEY (room_id) REFERENCES operating_rooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_or_blackout_user FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS surgery_profiles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(140) NOT NULL,
  name VARCHAR(190) NOT NULL,
  category VARCHAR(120) NOT NULL DEFAULT 'chirurgia_plastyczna',
  default_room_type ENUM('or_general','procedure_local') NOT NULL DEFAULT 'or_general',
  anesthesia_type ENUM('general','local','either') NOT NULL DEFAULT 'general',
  clean_class ENUM('clean','clean_contaminated','dirty') NOT NULL DEFAULT 'clean',
  duration_minutes INT UNSIGNED NOT NULL DEFAULT 60,
  prep_minutes INT UNSIGNED NOT NULL DEFAULT 15,
  anesthesia_minutes INT UNSIGNED NOT NULL DEFAULT 15,
  pacu_minutes INT UNSIGNED NOT NULL DEFAULT 60,
  buffer_minutes INT UNSIGNED NOT NULL DEFAULT 30,
  stay_nights INT UNSIGNED NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT UNSIGNED NOT NULL DEFAULT 100,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_surgery_profiles_slug (slug),
  KEY idx_surgery_profiles_active (active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS surgery_cases (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  surgery_profile_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NOT NULL,
  surgery_date DATE NOT NULL,
  planned_start TIME NOT NULL,
  planned_end TIME NOT NULL,
  admission_at DATETIME NULL,
  status ENUM('proposed','reserved','awaiting_deposit','deposit_overdue','confirmed','needs_reschedule','postponed','cancelled','completed') NOT NULL DEFAULT 'awaiting_deposit',
  readiness_color ENUM('green','yellow','red') NOT NULL DEFAULT 'yellow',
  deposit_percent TINYINT UNSIGNED NOT NULL DEFAULT 10,
  deposit_due_at DATETIME NULL,
  deposit_status ENUM('pending','paid','waived','overdue') NOT NULL DEFAULT 'pending',
  balance_due_at DATETIME NULL,
  balance_status ENUM('pending','paid','waived','overdue') NOT NULL DEFAULT 'pending',
  patient_visible_note TEXT NULL,
  internal_note TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_surgery_cases_patient (patient_id),
  KEY idx_surgery_cases_room_date (room_id, surgery_date, planned_start, planned_end),
  KEY idx_surgery_cases_status (status, surgery_date),
  KEY idx_surgery_cases_profile (surgery_profile_id),
  KEY idx_surgery_cases_user (created_by_user_id),
  CONSTRAINT fk_surgery_cases_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_surgery_cases_profile FOREIGN KEY (surgery_profile_id) REFERENCES surgery_profiles(id),
  CONSTRAINT fk_surgery_cases_room FOREIGN KEY (room_id) REFERENCES operating_rooms(id),
  CONSTRAINT fk_surgery_cases_user FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS surgery_case_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  surgery_case_id BIGINT UNSIGNED NOT NULL,
  changed_by_user_id BIGINT UNSIGNED NULL,
  change_type ENUM('created','status','reschedule','payment','note','system') NOT NULL DEFAULT 'system',
  old_value TEXT NULL,
  new_value TEXT NULL,
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_surgery_history_case (surgery_case_id, created_at),
  KEY idx_surgery_history_user (changed_by_user_id),
  CONSTRAINT fk_surgery_history_case FOREIGN KEY (surgery_case_id) REFERENCES surgery_cases(id) ON DELETE CASCADE,
  CONSTRAINT fk_surgery_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 IF NOT EXISTS surgery_waitlist_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  surgery_profile_id BIGINT UNSIGNED NOT NULL,
  preferred_week_start DATE NOT NULL,
  preferred_week_end DATE NOT NULL,
  room_type ENUM('or_general','procedure_local','any') NOT NULL DEFAULT 'any',
  priority ENUM('normal','high','last_minute','oncology_urgent') NOT NULL DEFAULT 'normal',
  guarantee_week TINYINT(1) NOT NULL DEFAULT 1,
  available_notice_hours INT UNSIGNED NOT NULL DEFAULT 72,
  readiness_color ENUM('green','yellow','red') NOT NULL DEFAULT 'yellow',
  status ENUM('active','contacted','accepted','booked','paused','closed') NOT NULL DEFAULT 'active',
  notes TEXT NULL,
  last_contact_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_surgery_waitlist_patient (patient_id),
  KEY idx_surgery_waitlist_profile (surgery_profile_id),
  KEY idx_surgery_waitlist_week (preferred_week_start, preferred_week_end, status),
  KEY idx_surgery_waitlist_priority (status, priority, readiness_color),
  KEY idx_surgery_waitlist_user (created_by_user_id),
  CONSTRAINT fk_surgery_waitlist_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
  CONSTRAINT fk_surgery_waitlist_profile FOREIGN KEY (surgery_profile_id) REFERENCES surgery_profiles(id),
  CONSTRAINT fk_surgery_waitlist_user FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO operating_rooms (slug, name, location_label, location_kind, room_type, anesthesia_scope, status, default_starts_at, default_ends_at, sort_order, notes) VALUES
('luxmed-gdansk-or-1','Luxmed Gdańsk — sala operacyjna 1','Szpital Luxmed Gdańsk','external_hospital','or_general','general','active','07:00:00','19:00:00',10,'Start: 1 sala operacyjna do znieczuleń ogólnych.'),
('luxmed-gdansk-local-1','Luxmed Gdańsk — sala zabiegowa 1','Szpital Luxmed Gdańsk','external_hospital','procedure_local','local','active','07:00:00','19:00:00',20,'Start: 1 sala zabiegowa do znieczuleń miejscowych.'),
('sopot-or-1','Baltique Sopot — sala operacyjna 1','Baltique Sopot, ul. Poniatowskiego 7','baltique_sopot','or_general','general','future','07:00:00','19:00:00',110,'Docelowa sala operacyjna Sopot.'),
('sopot-or-2','Baltique Sopot — sala operacyjna 2','Baltique Sopot, ul. Poniatowskiego 7','baltique_sopot','or_general','general','future','07:00:00','19:00:00',120,'Docelowa druga sala operacyjna Sopot.'),
('sopot-local-1','Baltique Sopot — sala zabiegowa 1','Baltique Sopot, ul. Poniatowskiego 7','baltique_sopot','procedure_local','local','future','07:00:00','19:00:00',130,'Docelowa sala zabiegowa do znieczuleń miejscowych.'),
('sopot-local-2','Baltique Sopot — sala zabiegowa 2','Baltique Sopot, ul. Poniatowskiego 7','baltique_sopot','procedure_local','local','future','07:00:00','19:00:00',140,'Docelowa druga sala zabiegowa do znieczuleń miejscowych.')
ON DUPLICATE KEY UPDATE name=VALUES(name), location_label=VALUES(location_label), location_kind=VALUES(location_kind), room_type=VALUES(room_type), anesthesia_scope=VALUES(anesthesia_scope), status=VALUES(status), default_starts_at=VALUES(default_starts_at), default_ends_at=VALUES(default_ends_at), sort_order=VALUES(sort_order), notes=VALUES(notes), updated_at=NOW();

INSERT INTO operating_room_weekday_rules (room_id, weekday, enabled, starts_at, ends_at, allows_emergency, notes)
SELECT r.id, d.weekday,
  CASE WHEN r.slug IN ('luxmed-gdansk-or-1','luxmed-gdansk-local-1') AND d.weekday IN (2,3,4) THEN 1 ELSE 0 END AS enabled,
  '07:00:00','19:00:00',
  CASE WHEN d.weekday = 7 THEN 1 ELSE 0 END AS allows_emergency,
  CASE WHEN d.weekday = 7 THEN 'Niedziela domyślnie wyłączona; możliwa do włączenia przez administratora dla sytuacji szczególnych.' ELSE 'Reguła pracy sali 07:00–19:00.' END
FROM operating_rooms r
JOIN (SELECT 1 weekday UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7) d
ON 1=1
ON DUPLICATE KEY UPDATE enabled=VALUES(enabled), starts_at=VALUES(starts_at), ends_at=VALUES(ends_at), allows_emergency=VALUES(allows_emergency), notes=VALUES(notes), updated_at=NOW();

INSERT INTO surgery_profiles (slug, name, category, default_room_type, anesthesia_type, clean_class, duration_minutes, prep_minutes, anesthesia_minutes, pacu_minutes, buffer_minutes, stay_nights, active, sort_order) VALUES
('powiekszenie-piersi-implantami','Powiększenie piersi implantami','chirurgia_piersi','or_general','general','clean',60,20,20,90,30,1,1,10),
('redukcja-piersi','Redukcja piersi','chirurgia_piersi','or_general','general','clean',180,20,20,90,30,1,1,20),
('mastopeksja','Mastopeksja','chirurgia_piersi','or_general','general','clean',120,20,20,90,30,1,1,30),
('podniesienie-z-powiekszeniem-piersi','Podniesienie z powiększeniem piersi','chirurgia_piersi','or_general','general','clean',120,20,20,90,30,1,1,40),
('abdominoplastyka','Abdominoplastyka','chirurgia_sylwetki','or_general','general','clean',180,25,20,120,30,1,1,50),
('liposukcja','Liposukcja','chirurgia_sylwetki','or_general','general','clean',60,15,15,90,30,0,1,60),
('blefaroplastyka','Blefaroplastyka','chirurgia_twarzy','procedure_local','local','clean',30,10,0,30,15,0,1,70),
('rhinoplastyka','Rhinoplastyka','chirurgia_twarzy','or_general','general','clean',180,25,20,120,30,1,1,80),
('lifting-twarzy','Lifting twarzy','chirurgia_twarzy','or_general','general','clean',480,30,25,120,30,1,1,90),
('ginekomastia','Ginekomastia','chirurgia_klatki','or_general','general','clean',90,20,15,90,30,0,1,100),
('plastyka-blizny','Plastyka blizny','chirurgia_rekonstrukcyjna','procedure_local','local','clean',45,10,0,30,15,0,1,110)
ON DUPLICATE KEY UPDATE name=VALUES(name), category=VALUES(category), default_room_type=VALUES(default_room_type), anesthesia_type=VALUES(anesthesia_type), clean_class=VALUES(clean_class), duration_minutes=VALUES(duration_minutes), prep_minutes=VALUES(prep_minutes), anesthesia_minutes=VALUES(anesthesia_minutes), pacu_minutes=VALUES(pacu_minutes), buffer_minutes=VALUES(buffer_minutes), stay_nights=VALUES(stay_nights), active=VALUES(active), sort_order=VALUES(sort_order), updated_at=NOW();

INSERT INTO app_settings (setting_key, setting_value, created_at, updated_at) VALUES
('core_version','0.4.0',NOW(),NOW()),
('theatreflow_enabled','1',NOW(),NOW()),
('theatreflow_default_deposit_percent','10',NOW(),NOW()),
('theatreflow_deposit_due_days','14',NOW(),NOW()),
('theatreflow_balance_due_days_before','14',NOW(),NOW()),
('theatreflow_sunday_enabled_by_default','0',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();

SET FOREIGN_KEY_CHECKS = 1;
