-- Baltique OS Core 0.11.0 — Inventory / Procedure Catalog / Sync
-- Reference migration. Prefer /upgrade_0_11_0.php because it conditionally adds columns.

CREATE TABLE IF NOT EXISTS inventory_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  catalog_item_id BIGINT UNSIGNED NULL,
  sku VARCHAR(120) NOT NULL,
  name VARCHAR(190) NOT NULL,
  item_type VARCHAR(60) NOT NULL DEFAULT 'product',
  category VARCHAR(90) NOT NULL DEFAULT 'product_accessory',
  track_stock TINYINT(1) NOT NULL DEFAULT 1,
  default_unit VARCHAR(30) NOT NULL DEFAULT 'szt.',
  price_net_cents INT UNSIGNED NOT NULL DEFAULT 0,
  price_gross_cents INT UNSIGNED NOT NULL DEFAULT 0,
  vat_rate_key VARCHAR(60) NULL,
  vat_rate_basis_points INT UNSIGNED NOT NULL DEFAULT 2300,
  min_stock_alert DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  active TINYINT(1) NOT NULL DEFAULT 1,
  source VARCHAR(60) NOT NULL DEFAULT 'manual',
  source_url TEXT NULL,
  metadata_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inventory_items_sku (sku),
  KEY idx_inventory_items_catalog (catalog_item_id),
  KEY idx_inventory_items_active (active, item_type, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_stock (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  inventory_item_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  qty_on_hand DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  qty_reserved DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  reorder_level DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  updated_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inventory_stock_item_location (inventory_item_id, location_id),
  KEY idx_inventory_stock_item (inventory_item_id),
  KEY idx_inventory_stock_location (location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_reservations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  surgery_case_id BIGINT UNSIGNED NULL,
  appointment_id BIGINT UNSIGNED NULL,
  inventory_item_id BIGINT UNSIGNED NOT NULL,
  catalog_item_id BIGINT UNSIGNED NULL,
  payment_order_id BIGINT UNSIGNED NULL,
  quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
  status VARCHAR(40) NOT NULL DEFAULT 'reserved',
  reserved_at DATETIME NULL,
  fulfilled_at DATETIME NULL,
  released_at DATETIME NULL,
  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_inventory_res_patient (patient_id),
  KEY idx_inventory_res_surgery (surgery_case_id),
  KEY idx_inventory_res_item (inventory_item_id),
  KEY idx_inventory_res_status (status, reserved_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS procedure_accessory_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  surgery_profile_id BIGINT UNSIGNED NULL,
  procedure_slug VARCHAR(160) NULL,
  procedure_name VARCHAR(190) NULL,
  accessory_item_id BIGINT UNSIGNED NOT NULL,
  required TINYINT(1) NOT NULL DEFAULT 1,
  default_quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
  include_in_payment TINYINT(1) NOT NULL DEFAULT 1,
  reserve_stock TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_user_id BIGINT UNSIGNED NULL,
  updated_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_proc_acc_profile (surgery_profile_id, active),
  KEY idx_proc_acc_slug (procedure_slug, active),
  KEY idx_proc_acc_item (accessory_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_sync_sources (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_key VARCHAR(80) NOT NULL,
  name VARCHAR(190) NOT NULL,
  source_type VARCHAR(60) NOT NULL DEFAULT 'shop_products',
  source_url TEXT NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  sync_interval_hours INT UNSIGNED NOT NULL DEFAULT 24,
  last_synced_at DATETIME NULL,
  last_status VARCHAR(40) NULL,
  last_error TEXT NULL,
  last_hash VARCHAR(80) NULL,
  settings_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_catalog_sync_key (source_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS catalog_sync_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_id BIGINT UNSIGNED NULL,
  source_key VARCHAR(80) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'running',
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME NULL,
  duration_ms INT UNSIGNED NULL,
  found_count INT UNSIGNED NOT NULL DEFAULT 0,
  imported_count INT UNSIGNED NOT NULL DEFAULT 0,
  source_hash VARCHAR(80) NULL,
  error_message TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  KEY idx_catalog_sync_runs_source (source_key, started_at),
  KEY idx_catalog_sync_runs_status (status, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
