-- Step 7: IPD running billing. Additive only; safe to run repeatedly.
-- Prerequisite: step5_complete_billing_upgrade.sql and step6_clinical_ipd_upgrade.sql.
SET @db := DATABASE();

CREATE TABLE IF NOT EXISTS ipd_billing_case (
  id INT NOT NULL AUTO_INCREMENT,
  admission_id INT NOT NULL,
  patient_id INT NOT NULL,
  invoice_id INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'OPEN',
  opened_by INT NULL,
  opened_at DATETIME NOT NULL,
  finalized_by INT NULL,
  finalized_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ipd_billing_admission (admission_id),
  UNIQUE KEY uq_ipd_billing_invoice (invoice_id),
  KEY ix_ipd_billing_patient_status (patient_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS ipd_charge_event (
  id BIGINT NOT NULL AUTO_INCREMENT,
  admission_id INT NOT NULL,
  patient_id INT NOT NULL,
  invoice_id INT NOT NULL,
  invoice_item_id BIGINT NOT NULL,
  charge_date DATE NOT NULL,
  charge_type VARCHAR(40) NOT NULL,
  source_type VARCHAR(40) NOT NULL,
  source_id INT NOT NULL,
  quantity DECIMAL(12,2) NOT NULL DEFAULT 1,
  unit_rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_by INT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ipd_charge_once (admission_id, charge_date, source_type, source_id),
  KEY ix_ipd_charge_invoice (invoice_id),
  KEY ix_ipd_charge_admission_date (admission_id, charge_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Links the existing invoice schema to an IPD admission without replacing invoice data.
SET @q := IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='invoice' AND column_name='admission_id')=0,
  'ALTER TABLE invoice ADD COLUMN admission_id INT NULL AFTER patient_id', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q := IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db AND table_name='invoice' AND index_name='invoice_admission_status_idx')=0,
  'ALTER TABLE invoice ADD KEY invoice_admission_status_idx (admission_id,billing_status)', 'SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;