-- Baltique OS 2.10.0 — clinical operations hardening.
-- The PHP upgrade performs idempotent room consolidation and seed governance.

CREATE TABLE IF NOT EXISTS enterprise_clinical_catalog_sources (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_key VARCHAR(140) NOT NULL,
  source_type VARCHAR(80) NOT NULL,
  authority_name VARCHAR(255) NOT NULL,
  source_url VARCHAR(1000) NOT NULL,
  scope_note TEXT NULL,
  verification_status ENUM('official_source','local_copy_verified','pending_review','retired') NOT NULL DEFAULT 'pending_review',
  checked_at DATETIME NULL,
  checked_by BIGINT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(id), UNIQUE KEY uq_clinical_catalog_source(source_key),
  KEY idx_clinical_catalog_source_status(verification_status,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_product_variants_2100 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  variant_key VARCHAR(190) NOT NULL,
  base_item_key VARCHAR(140) NULL,
  product_domain ENUM('medication','suture','needle','syringe','implant','dressing','consumable','equipment_accessory','specification_template') NOT NULL,
  generic_name VARCHAR(255) NOT NULL,
  brand_name VARCHAR(255) NULL,
  manufacturer_name VARCHAR(255) NULL,
  manufacturer_ref VARCHAR(190) NULL,
  gtin VARCHAR(32) NULL,
  udi_di VARCHAR(190) NULL,
  regulatory_identifier VARCHAR(190) NULL,
  size_label VARCHAR(190) NULL,
  package_label VARCHAR(255) NULL,
  material VARCHAR(190) NULL,
  suture_usp VARCHAR(40) NULL,
  needle_geometry VARCHAR(120) NULL,
  needle_point VARCHAR(120) NULL,
  needle_length_mm DECIMAL(8,2) NULL,
  gauge_label VARCHAR(40) NULL,
  volume_ml DECIMAL(10,3) NULL,
  connector_type VARCHAR(120) NULL,
  sterile TINYINT(1) NOT NULL DEFAULT 0,
  single_use TINYINT(1) NOT NULL DEFAULT 0,
  source_key VARCHAR(140) NULL,
  source_url VARCHAR(1000) NULL,
  source_checked_at DATETIME NULL,
  approval_status ENUM('reference_only','pending_local_approval','approved','restricted','rejected','archived') NOT NULL DEFAULT 'pending_local_approval',
  approved_by BIGINT NULL,
  approved_at DATETIME NULL,
  ordering_enabled TINYINT(1) NOT NULL DEFAULT 0,
  prescribing_enabled TINYINT(1) NOT NULL DEFAULT 0,
  metadata_json JSON NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(id), UNIQUE KEY uq_product_variant_2100(variant_key),
  KEY idx_product_variant_domain(product_domain,approval_status,active),
  KEY idx_product_variant_gtin(gtin), KEY idx_product_variant_ref(manufacturer_name,manufacturer_ref)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_verified_suppliers_2100 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  supplier_key VARCHAR(140) NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  legal_name VARCHAR(255) NULL,
  supplier_kind ENUM('manufacturer','authorized_distributor','pharmaceutical_wholesaler','medical_wholesaler','registry_source','other') NOT NULL DEFAULT 'other',
  country_code CHAR(2) NOT NULL DEFAULT 'PL',
  tax_id VARCHAR(32) NULL,
  registry_name VARCHAR(255) NULL,
  registry_identifier VARCHAR(190) NULL,
  public_contact_url VARCHAR(1000) NULL,
  registry_source_url VARCHAR(1000) NULL,
  verification_status ENUM('official_contact_only','registry_verified','contract_verified','pending_local_contract_verification','suspended','archived') NOT NULL DEFAULT 'pending_local_contract_verification',
  verified_by BIGINT NULL,
  verified_at DATETIME NULL,
  ordering_enabled TINYINT(1) NOT NULL DEFAULT 0,
  metadata_json JSON NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(id), UNIQUE KEY uq_verified_supplier_2100(supplier_key),
  KEY idx_verified_supplier_2100(verification_status,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_product_supplier_links_2100 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  variant_id BIGINT UNSIGNED NOT NULL,
  supplier_id BIGINT UNSIGNED NOT NULL,
  supplier_sku VARCHAR(190) NULL,
  contract_reference VARCHAR(190) NULL,
  package_label VARCHAR(255) NULL,
  availability_status ENUM('unverified','available','temporarily_unavailable','discontinued','restricted') NOT NULL DEFAULT 'unverified',
  approval_status ENUM('draft','approved','suspended','archived') NOT NULL DEFAULT 'draft',
  approved_by BIGINT NULL,
  approved_at DATETIME NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(id), UNIQUE KEY uq_product_supplier_2100(variant_id,supplier_id),
  KEY idx_product_supplier_status(approval_status,availability_status,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_catalog_import_batches_2100 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  import_uid CHAR(32) NOT NULL,
  source_key VARCHAR(140) NOT NULL,
  file_name VARCHAR(255) NULL,
  file_sha256 CHAR(64) NULL,
  import_mode ENUM('dry_run','staged','approved','rejected') NOT NULL DEFAULT 'dry_run',
  row_count INT UNSIGNED NOT NULL DEFAULT 0,
  accepted_count INT UNSIGNED NOT NULL DEFAULT 0,
  rejected_count INT UNSIGNED NOT NULL DEFAULT 0,
  report_json JSON NULL,
  created_by BIGINT NULL,
  approved_by BIGINT NULL,
  approved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id), UNIQUE KEY uq_catalog_import_uid_2100(import_uid),
  KEY idx_catalog_import_source_2100(source_key,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
