-- Baltique OS Core 0.13.0 — Implant Registry / Issue Documents / Stock Alert Emails

CREATE TABLE IF NOT EXISTS patient_implant_cards (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  surgery_case_id BIGINT UNSIGNED NULL,
  inventory_item_id BIGINT UNSIGNED NOT NULL,
  inventory_lot_id BIGINT UNSIGNED NULL,
  movement_id BIGINT UNSIGNED NULL,
  reservation_id BIGINT UNSIGNED NULL,
  catalog_item_id BIGINT UNSIGNED NULL,
  implant_name VARCHAR(190) NOT NULL,
  manufacturer VARCHAR(190) NULL,
  lot_number VARCHAR(120) NULL,
  serial_number VARCHAR(120) NULL,
  side VARCHAR(60) NULL,
  anatomical_location VARCHAR(160) NULL,
  quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
  unit VARCHAR(30) NOT NULL DEFAULT 'szt.',
  implanted_at DATE NULL,
  issued_at DATETIME NULL,
  implant_status VARCHAR(40) NOT NULL DEFAULT 'active',
  document_id BIGINT UNSIGNED NULL,
  document_number VARCHAR(120) 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_implant_cards_patient (patient_id, implanted_at),
  KEY idx_implant_cards_surgery (surgery_case_id),
  KEY idx_implant_cards_lot (lot_number, serial_number),
  KEY idx_implant_cards_status (implant_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_issue_documents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  document_number VARCHAR(120) NOT NULL,
  patient_id BIGINT UNSIGNED NULL,
  surgery_case_id BIGINT UNSIGNED NULL,
  movement_id BIGINT UNSIGNED NULL,
  reservation_id BIGINT UNSIGNED NULL,
  document_type VARCHAR(60) NOT NULL DEFAULT 'issue',
  document_html MEDIUMTEXT NULL,
  issued_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_issue_document_number (document_number),
  KEY idx_issue_doc_patient (patient_id, created_at),
  KEY idx_issue_doc_movement (movement_id),
  KEY idx_issue_doc_surgery (surgery_case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_alert_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  alert_type VARCHAR(80) NOT NULL DEFAULT 'low_stock',
  recipient_email VARCHAR(190) NULL,
  item_count INT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL DEFAULT 'queued',
  message MEDIUMTEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_inventory_alert_logs_type (alert_type, created_at),
  KEY idx_inventory_alert_logs_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
