-- Baltique OS Core 0.78.8 — Enterprise Document & Consent Gate
-- Referential migration. The PHP upgrade runner performs safe ALTER/repair steps
-- for partially-created tables and hosting-specific MySQL variants.

CREATE TABLE IF NOT EXISTS enterprise_document_requirement_blueprints (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  blueprint_key VARCHAR(160) NOT NULL DEFAULT 'operative_document_gate',
  requirement_key VARCHAR(160) NOT NULL,
  requirement_label VARCHAR(255) NOT NULL,
  domain_key VARCHAR(80) NOT NULL DEFAULT 'documents',
  stage_key VARCHAR(120) NOT NULL DEFAULT 'documents_and_consents',
  severity VARCHAR(40) NOT NULL DEFAULT 'warning',
  required_flag TINYINT(1) NOT NULL DEFAULT 1,
  patient_facing TINYINT(1) NOT NULL DEFAULT 1,
  role_key VARCHAR(80) NOT NULL DEFAULT 'coordinator',
  action_key VARCHAR(160) NOT NULL DEFAULT 'collect_document_evidence',
  document_type_pattern VARCHAR(255) NULL,
  consent_purpose_code VARCHAR(120) NULL,
  evidence_table_hint VARCHAR(120) NULL,
  sort_order INT NOT NULL DEFAULT 100,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_doc_req_blueprint (blueprint_key, requirement_key),
  KEY idx_enterprise_doc_req_blueprint_active (active, stage_key, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_case_document_requirements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_spine_id BIGINT UNSIGNED NOT NULL,
  requirement_key VARCHAR(160) NOT NULL,
  requirement_label VARCHAR(255) NOT NULL,
  domain_key VARCHAR(80) NOT NULL DEFAULT 'documents',
  stage_key VARCHAR(120) NOT NULL DEFAULT 'documents_and_consents',
  severity VARCHAR(40) NOT NULL DEFAULT 'warning',
  required_flag TINYINT(1) NOT NULL DEFAULT 1,
  patient_facing TINYINT(1) NOT NULL DEFAULT 1,
  status VARCHAR(40) NOT NULL DEFAULT 'missing',
  readiness_status VARCHAR(40) NOT NULL DEFAULT 'warn',
  role_key VARCHAR(80) NOT NULL DEFAULT 'coordinator',
  action_key VARCHAR(160) NULL,
  document_type_pattern VARCHAR(255) NULL,
  source_table VARCHAR(120) NULL,
  source_id BIGINT UNSIGNED NULL,
  evidence_hash VARCHAR(128) NULL,
  evidence_label VARCHAR(255) NULL,
  blocker_reason TEXT NULL,
  context_json JSON NULL,
  last_checked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_case_doc_req (case_spine_id, requirement_key),
  KEY idx_enterprise_case_doc_req_status (status, readiness_status, severity),
  KEY idx_enterprise_case_doc_req_case (case_spine_id, status),
  KEY idx_enterprise_case_doc_req_source (source_table, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_case_document_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  case_spine_id BIGINT UNSIGNED NOT NULL,
  requirement_key VARCHAR(160) NULL,
  event_type VARCHAR(80) NOT NULL,
  from_status VARCHAR(40) NULL,
  to_status VARCHAR(40) NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  note TEXT NULL,
  details_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_enterprise_case_doc_event_case (case_spine_id, created_at),
  KEY idx_enterprise_case_doc_event_requirement (requirement_key, created_at),
  KEY idx_enterprise_case_doc_event_type (event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_document_gate_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uuid VARCHAR(80) NOT NULL,
  run_type VARCHAR(80) NOT NULL DEFAULT 'manual_sync',
  scanned_cases INT UNSIGNED NOT NULL DEFAULT 0,
  evaluated_requirements INT UNSIGNED NOT NULL DEFAULT 0,
  ready_cases INT UNSIGNED NOT NULL DEFAULT 0,
  hold_cases INT UNSIGNED NOT NULL DEFAULT 0,
  no_go_cases INT UNSIGNED NOT NULL DEFAULT 0,
  created_actions INT UNSIGNED NOT NULL DEFAULT 0,
  updated_actions INT UNSIGNED NOT NULL DEFAULT 0,
  closed_actions INT UNSIGNED NOT NULL DEFAULT 0,
  details_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_doc_gate_run (run_uuid),
  KEY idx_enterprise_doc_gate_run_type (run_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO enterprise_document_requirement_blueprints
(blueprint_key,requirement_key,requirement_label,domain_key,stage_key,severity,required_flag,patient_facing,role_key,action_key,document_type_pattern,consent_purpose_code,evidence_table_hint,sort_order,active,created_at,updated_at)
VALUES
('operative_document_gate','patient_identity_confirmation','Potwierdzenie tożsamości pacjenta','documents','documents_and_consents','critical',1,1,'coordinator','collect_identity_confirmation','identity|tozsamosc|tożsamość|patient_identity|id','identity_confirmation','operative_signed_document_archives',10,1,NOW(),NOW()),
('operative_document_gate','surgical_consent_signed','Zgoda na zabieg / procedurę','consent','documents_and_consents','critical',1,1,'coordinator','collect_surgical_consent','surgical|operation|operative|procedure|zabieg|operac|zgoda','surgical_consent','operative_signed_document_archives',20,1,NOW(),NOW()),
('operative_document_gate','anesthesia_consent_signed','Zgoda / ankieta anestezjologiczna','consent','documents_and_consents','critical',1,1,'anesthesiologist','collect_anesthesia_consent','anesthesia|anestezj|znieczul|asa|ankieta','anesthesia_consent','operative_signed_document_archives',30,1,NOW(),NOW()),
('operative_document_gate','risk_information_ack','Potwierdzenie informacji o ryzykach i powikłaniach','consent','documents_and_consents','critical',1,1,'doctor','collect_risk_acknowledgement','risk|powikl|powikł|complication|ryzyk|informacja','risk_information','operative_signed_document_archives',40,1,NOW(),NOW()),
('operative_document_gate','preop_instructions_ack','Potwierdzenie zaleceń przedoperacyjnych','documents','preop_workflow','warning',1,1,'coordinator','collect_preop_instruction_ack','preop|przedoper|zalecen|zalecenia|instructions|preparation','preop_instructions','operative_signed_document_archives',50,1,NOW(),NOW()),
('operative_document_gate','financial_terms_ack','Potwierdzenie warunków rozliczenia','finance','payments','warning',1,1,'reception','collect_financial_terms_ack','finance|payment|platn|płatn|rozlicz|koszt|terms','financial_terms','operative_signed_document_archives',60,1,NOW(),NOW()),
('operative_document_gate','privacy_data_processing_ack','RODO / przetwarzanie danych','consent','documents_and_consents','warning',1,1,'coordinator','collect_privacy_ack','privacy|rodo|gdpr|data|dane|przetwarz','privacy_data_processing','consent_evidence_records',70,1,NOW(),NOW()),
('operative_document_gate','photo_marketing_scope','Zakres zgód zdjęciowych / marketingowych','consent','experience','info',0,1,'coordinator','review_photo_marketing_scope','photo|zdjec|zdjęc|wizerunek|marketing|image','photo_marketing','consent_evidence_records',80,1,NOW(),NOW())
ON DUPLICATE KEY UPDATE requirement_label=VALUES(requirement_label), domain_key=VALUES(domain_key), stage_key=VALUES(stage_key), severity=VALUES(severity), required_flag=VALUES(required_flag), patient_facing=VALUES(patient_facing), role_key=VALUES(role_key), action_key=VALUES(action_key), document_type_pattern=VALUES(document_type_pattern), consent_purpose_code=VALUES(consent_purpose_code), evidence_table_hint=VALUES(evidence_table_hint), sort_order=VALUES(sort_order), active=1, updated_at=NOW();
