-- Baltique OS Core 0.9.2 — ClinicFlow Architect
-- Configurable centres, rooms/resources, staff work rules, exceptions and manual slot locks.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS clinic_resources (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  location_id BIGINT UNSIGNED NULL,
  operating_room_id BIGINT UNSIGNED NULL,
  resource_key VARCHAR(120) NOT NULL,
  name VARCHAR(190) NOT NULL,
  resource_type ENUM('consultation_room','treatment_room','physio_room','aesthetic_room','online_room','operating_room','local_anesthesia_room','pacu','other') NOT NULL DEFAULT 'consultation_room',
  status ENUM('active','future','inactive','archived') NOT NULL DEFAULT 'active',
  capacity INT UNSIGNED NOT NULL DEFAULT 1,
  floor_label VARCHAR(60) NULL,
  layout_x INT NOT NULL DEFAULT 10,
  layout_y INT NOT NULL DEFAULT 10,
  layout_w INT NOT NULL DEFAULT 180,
  layout_h INT NOT NULL DEFAULT 110,
  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_clinic_resources_key (resource_key),
  KEY idx_clinic_resources_location (location_id, status, sort_order),
  KEY idx_clinic_resources_operating_room (operating_room_id),
  KEY idx_clinic_resources_type_status (resource_type, status),
  CONSTRAINT fk_clinic_resources_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinic_work_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  resource_id BIGINT UNSIGNED NOT NULL,
  provider_id BIGINT UNSIGNED NULL,
  weekday TINYINT UNSIGNED NOT NULL,
  starts_at TIME NOT NULL,
  ends_at TIME NOT NULL,
  slot_minutes INT UNSIGNED NOT NULL DEFAULT 30,
  bookable_online TINYINT(1) NOT NULL DEFAULT 0,
  valid_from DATE NULL,
  valid_to DATE NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  legacy_schedule_id BIGINT UNSIGNED NULL,
  notes VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_cwr_resource_weekday (resource_id, weekday, active),
  KEY idx_cwr_provider_weekday (provider_id, weekday, active),
  UNIQUE KEY uq_cwr_legacy_schedule (legacy_schedule_id),
  CONSTRAINT fk_cwr_resource FOREIGN KEY (resource_id) REFERENCES clinic_resources(id) ON DELETE CASCADE,
  CONSTRAINT fk_cwr_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinic_schedule_exceptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  resource_id BIGINT UNSIGNED NULL,
  provider_id BIGINT UNSIGNED NULL,
  exception_date DATE NOT NULL,
  starts_at TIME NULL,
  ends_at TIME NULL,
  exception_type ENUM('extra','block','closed','staff_unavailable','room_block','vip_hold','holiday','note') NOT NULL DEFAULT 'block',
  reason VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_cse_date (exception_date, active),
  KEY idx_cse_resource_date (resource_id, exception_date),
  KEY idx_cse_provider_date (provider_id, exception_date),
  CONSTRAINT fk_cse_resource FOREIGN KEY (resource_id) REFERENCES clinic_resources(id) ON DELETE CASCADE,
  CONSTRAINT fk_cse_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE CASCADE,
  CONSTRAINT fk_cse_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 clinic_layout_versions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  location_id BIGINT UNSIGNED NULL,
  name VARCHAR(190) NOT NULL,
  status ENUM('draft','live','archived') NOT NULL DEFAULT 'draft',
  notes 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_clv_location_status (location_id, status),
  CONSTRAINT fk_clv_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  CONSTRAINT fk_clv_user FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed consultation resources from existing locations.
INSERT INTO clinic_resources (location_id, resource_key, name, resource_type, status, sort_order, layout_x, layout_y, layout_w, layout_h, notes, created_at, updated_at)
SELECT l.id,
       CONCAT('loc_', l.id, '_room_1'),
       CONCAT(l.name, ' — gabinet 1'),
       CASE WHEN l.kind='online' THEN 'online_room' ELSE 'consultation_room' END,
       CASE WHEN l.status IN ('future','inactive') THEN l.status ELSE 'active' END,
       100 + l.id,
       30 + (l.id * 20), 30 + (l.id * 20), 210, 115,
       'Zasób startowy utworzony automatycznie z lokalizacji. Możesz zmienić nazwę, typ i układ w ClinicFlow Architect.',
       NOW(), NOW()
FROM locations l
WHERE NOT EXISTS (SELECT 1 FROM clinic_resources cr WHERE cr.resource_key = CONCAT('loc_', l.id, '_room_1'));

-- Seed resources from TheatreFlow operating rooms if available. This statement is safe only when operating_rooms exists; upgrade script runs it conditionally.

INSERT INTO app_settings (setting_key, setting_value, created_at, updated_at) VALUES
('core_version','0.9.2',NOW(),NOW()),
('clinicflow_architect_enabled','1',NOW(),NOW()),
('clinicflow_default_work_start','07:00',NOW(),NOW()),
('clinicflow_default_work_end','19:00',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value), updated_at=NOW();
