-- Baltique OS Core 2.7.1
-- Plain Language Navigation & Clinical Labels

CREATE TABLE IF NOT EXISTS enterprise_plain_language_registry (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  term_key VARCHAR(190) NOT NULL,
  term_type VARCHAR(40) NOT NULL,
  function_key VARCHAR(80) NULL,
  module_key VARCHAR(120) NULL,
  route_path VARCHAR(255) NULL,
  label_pl VARCHAR(255) NOT NULL,
  label_en VARCHAR(255) NOT NULL,
  description_pl TEXT NULL,
  description_en TEXT NULL,
  legacy_label VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  version VARCHAR(40) NOT NULL DEFAULT '2.7.1',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_plain_language_term(term_key,term_type),
  KEY idx_plain_language_module(module_key,active),
  KEY idx_plain_language_route(route_path,active),
  KEY idx_plain_language_function(function_key,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_plain_language_audit_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uid VARCHAR(64) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'running',
  files_scanned INT UNSIGNED NOT NULL DEFAULT 0,
  labels_checked INT UNSIGNED NOT NULL DEFAULT 0,
  findings_total INT UNSIGNED NOT NULL DEFAULT 0,
  summary_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_plain_language_audit_uid(run_uid),
  KEY idx_plain_language_audit_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_plain_language_findings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  finding_key VARCHAR(190) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'info',
  finding_status VARCHAR(40) NOT NULL DEFAULT 'open',
  source_type VARCHAR(40) NOT NULL DEFAULT 'file',
  source_path VARCHAR(255) NULL,
  line_number INT UNSIGNED NULL,
  legacy_label VARCHAR(255) NULL,
  recommended_label VARCHAR(255) NULL,
  message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_plain_language_findings_run(run_id,severity,finding_status),
  KEY idx_plain_language_findings_source(source_path,line_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings(setting_key,setting_value,created_at,updated_at)
VALUES
 ('enterprise_plain_language_active','1',NOW(),NOW()),
 ('enterprise_plain_language_version','2.7.1',NOW(),NOW()),
 ('enterprise_plain_language_legacy_hidden_default','1',NOW(),NOW()),
 ('core_version_271','1',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),updated_at=NOW();
