-- =====================================================================
-- SIMRS RS Bitung Health Center — perubahan struktur database 06-10-2026
-- (struktur saja, tanpa data). MariaDB / MySQL, InnoDB, utf8mb4.
--
-- Sumber migrasi:
--   2026-10-06-090000_CreateInpatientMedicalRecords
--   2026-10-06-130000_AddChartFieldsToInpatientVitalSigns
--
-- Prasyarat: tabel inpatient_admissions, visits, antenatal_visits,
--            pharmacy_dispensing_items sudah ada.
-- Urutan perintah di bawah sudah sesuai ketergantungan foreign key.
-- =====================================================================

-- ---------------------------------------------------------------------
-- 1. RM 7.3 — Pemantauan Tanda Vital Rawat Inap (input perawat)
--    (sudah termasuk kolom spo2, bab, bak, diet dari migrasi 130000)
-- ---------------------------------------------------------------------
CREATE TABLE `inpatient_vital_signs` (
  `id`                     INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inpatient_admission_id` INT(11) UNSIGNED NOT NULL,
  `visit_id`               INT(11) UNSIGNED NOT NULL,
  `recorded_at`            DATETIME NOT NULL,
  `blood_pressure`         VARCHAR(20) NULL,
  `pulse_rate`             SMALLINT(5) UNSIGNED NULL,
  `respiration_rate`       SMALLINT(5) UNSIGNED NULL,
  `temperature`            DECIMAL(4,1) NULL,
  `spo2`                   TINYINT(3) UNSIGNED NULL,
  `pupil`                  VARCHAR(60) NULL,
  `urine_input_ml`         SMALLINT(5) UNSIGNED NULL,
  `urine_output_ml`        SMALLINT(5) UNSIGNED NULL,
  `bab`                    VARCHAR(40) NULL,
  `bak`                    VARCHAR(40) NULL,
  `diet`                   VARCHAR(100) NULL,
  `pain_score`             TINYINT(2) UNSIGNED NULL,
  `notes`                  TEXT NULL,
  `prefill_source`         VARCHAR(30) NULL COMMENT 'skrining / skrining_igd / irdo (isian pertama)',
  `nurse_name`             VARCHAR(100) NULL,
  `created_by`             VARCHAR(100) NULL,
  `created_at`             DATETIME NULL,
  `updated_at`             DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `inpatient_admission_id_recorded_at` (`inpatient_admission_id`, `recorded_at`),
  KEY `inpatient_vital_signs_visit_id_foreign` (`visit_id`),
  CONSTRAINT `inpatient_vital_signs_inpatient_admission_id_foreign`
    FOREIGN KEY (`inpatient_admission_id`) REFERENCES `inpatient_admissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `inpatient_vital_signs_visit_id_foreign`
    FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- 2. RM 7.1 — Pengkajian Dokter Rawat Inap
--    1 pemeriksaan dokter per 1 input tanda vital (urutan perawat → dokter dikunci)
-- ---------------------------------------------------------------------
CREATE TABLE `inpatient_doctor_exams` (
  `id`                      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inpatient_admission_id`  INT(11) UNSIGNED NOT NULL,
  `visit_id`                INT(11) UNSIGNED NOT NULL,
  `inpatient_vital_sign_id` INT(11) UNSIGNED NOT NULL COMMENT 'tanda vital yang mendahului pemeriksaan ini',
  `examined_at`             DATETIME NOT NULL,
  `chief_complaint`         TEXT NULL,
  `past_medical_history`    TEXT NULL,
  `allergy_history`         TEXT NULL,
  `medication_history`      TEXT NULL,
  `nutrition_status`        VARCHAR(20) NULL,
  `anamnesis`               TEXT NULL,
  `physical_exam`           TEXT NULL,
  `diagnosis`               TEXT NULL,
  `therapy_plan`            TEXT NULL,
  `supporting_exam_plan`    TEXT NULL,
  `referral_consult`        TEXT NULL COMMENT 'JSON kode dirujuk/konsul',
  `referral_other_note`     VARCHAR(255) NULL,
  `doctor_name`             VARCHAR(100) NULL,
  `created_by`              VARCHAR(100) NULL,
  `created_at`              DATETIME NULL,
  `updated_at`              DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `inpatient_vital_sign_id` (`inpatient_vital_sign_id`),
  KEY `inpatient_admission_id_examined_at` (`inpatient_admission_id`, `examined_at`),
  KEY `inpatient_doctor_exams_visit_id_foreign` (`visit_id`),
  CONSTRAINT `inpatient_doctor_exams_inpatient_admission_id_foreign`
    FOREIGN KEY (`inpatient_admission_id`) REFERENCES `inpatient_admissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `inpatient_doctor_exams_visit_id_foreign`
    FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `inpatient_doctor_exams_inpatient_vital_sign_id_foreign`
    FOREIGN KEY (`inpatient_vital_sign_id`) REFERENCES `inpatient_vital_signs` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- 3. Tindakan / obat / konsultasi pemeriksaan dokter rawat inap (harga snapshot)
-- ---------------------------------------------------------------------
CREATE TABLE `inpatient_order_items` (
  `id`                       INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inpatient_doctor_exam_id` INT(11) UNSIGNED NOT NULL,
  `item_type`                ENUM('tindakan','obat','konsultasi') NOT NULL,
  `reference_id`             INT(11) UNSIGNED NULL,
  `kode_snapshot`            VARCHAR(50) NULL,
  `uraian_snapshot`          VARCHAR(255) NOT NULL,
  `harga_snapshot`           DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `quantity`                 INT(11) NOT NULL DEFAULT 1,
  `dosage`                   VARCHAR(100) NULL,
  `notes`                    VARCHAR(255) NULL,
  `created_at`               DATETIME NULL,
  `updated_at`               DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `inpatient_order_items_inpatient_doctor_exam_id_foreign` (`inpatient_doctor_exam_id`),
  CONSTRAINT `inpatient_order_items_inpatient_doctor_exam_id_foreign`
    FOREIGN KEY (`inpatient_doctor_exam_id`) REFERENCES `inpatient_doctor_exams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- 4. RM 7.4 — Resume Medis Rawat Inap (1 per episode rawat inap)
-- ---------------------------------------------------------------------
CREATE TABLE `inpatient_discharge_summaries` (
  `id`                        INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inpatient_admission_id`    INT(11) UNSIGNED NOT NULL,
  `visit_id`                  INT(11) UNSIGNED NOT NULL,
  `discharged_at`             DATETIME NOT NULL,
  `admission_diagnosis`       TEXT NULL,
  `history_summary`           TEXT NULL,
  `physical_exam`             TEXT NULL,
  `key_findings`              TEXT NULL,
  `therapy_during_stay`       TEXT NULL,
  `consultation_results`      TEXT NULL,
  `diagnosis_primary`         TEXT NULL,
  `icd10_primary`             VARCHAR(20) NULL,
  `diagnosis_secondary`       TEXT NULL COMMENT 'JSON [{text, icd10}] maks 4',
  `procedures`                TEXT NULL COMMENT 'JSON [{text, icd9}] maks 4',
  `allergy`                   TEXT NULL,
  `pending_results`           TEXT NULL,
  `diet`                      TEXT NULL,
  `instructions`              TEXT NULL,
  `discharge_condition`       VARCHAR(30) NULL,
  `discharge_condition_other` VARCHAR(150) NULL,
  `follow_up`                 VARCHAR(30) NULL,
  `follow_up_other`           VARCHAR(150) NULL,
  `control_date`              DATE NULL,
  `doctor_name`               VARCHAR(100) NULL,
  `created_by`                VARCHAR(100) NULL,
  `created_at`                DATETIME NULL,
  `updated_at`                DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `inpatient_admission_id` (`inpatient_admission_id`),
  KEY `inpatient_discharge_summaries_visit_id_foreign` (`visit_id`),
  CONSTRAINT `inpatient_discharge_summaries_inpatient_admission_id_foreign`
    FOREIGN KEY (`inpatient_admission_id`) REFERENCES `inpatient_admissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `inpatient_discharge_summaries_visit_id_foreign`
    FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- 5. Terapi pulang RM 7.4
--    (nama FK dipendekkan: nama otomatis melebihi batas 64 karakter)
-- ---------------------------------------------------------------------
CREATE TABLE `inpatient_discharge_medications` (
  `id`                             INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inpatient_discharge_summary_id` INT(11) UNSIGNED NOT NULL,
  `obat_reference_id`              INT(11) UNSIGNED NULL,
  `kode_snapshot`                  VARCHAR(50) NULL,
  `uraian_snapshot`                VARCHAR(255) NOT NULL,
  `harga_snapshot`                 DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `quantity`                       INT(11) NOT NULL DEFAULT 1,
  `dosage`                         VARCHAR(100) NULL,
  `frequency`                      VARCHAR(100) NULL,
  `route`                          VARCHAR(100) NULL,
  `created_at`                     DATETIME NULL,
  `updated_at`                     DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `inpatient_discharge_meds_summary_fk` (`inpatient_discharge_summary_id`),
  CONSTRAINT `inpatient_discharge_meds_summary_fk`
    FOREIGN KEY (`inpatient_discharge_summary_id`) REFERENCES `inpatient_discharge_summaries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
-- 6. Poli Obgyn — Dirujuk / Konsul (termasuk Rawat Inap)
-- ---------------------------------------------------------------------
ALTER TABLE `antenatal_visits`
  ADD COLUMN `referral_consult`    TEXT NULL AFTER `other_notes`,
  ADD COLUMN `referral_other_note` VARCHAR(255) NULL AFTER `referral_consult`;

-- ---------------------------------------------------------------------
-- 7. Apotek — sumber resep dari rawat inap
-- ---------------------------------------------------------------------
ALTER TABLE `pharmacy_dispensing_items`
  MODIFY `source_type` ENUM('outpatient','igd_vital','igd_pulang','ranap','ranap_pulang') NOT NULL;

-- ---------------------------------------------------------------------
-- (Opsional) Bila server tujuan memakai `php spark migrate`, catat migrasi
-- di atas agar tidak dijalankan ulang. Sesuaikan nilai batch dengan
-- batch terakhir di tabel migrations server tujuan + 1.
-- ---------------------------------------------------------------------
-- INSERT INTO `migrations` (`version`, `class`, `group`, `namespace`, `time`, `batch`) VALUES
--   ('2026-10-06-090000', 'App\\Database\\Migrations\\CreateInpatientMedicalRecords', 'default', 'App', UNIX_TIMESTAMP(), <batch>),
--   ('2026-10-06-130000', 'App\\Database\\Migrations\\AddChartFieldsToInpatientVitalSigns', 'default', 'App', UNIX_TIMESTAMP(), <batch>);

-- ---------------------------------------------------------------------
-- (Opsional) Pembatalan / rollback — urutan kebalikan.
-- PERHATIAN: menghapus seluruh data rekam medis rawat inap.
-- ---------------------------------------------------------------------
-- DELETE FROM `pharmacy_dispensing_items` WHERE `source_type` IN ('ranap','ranap_pulang');
-- ALTER TABLE `pharmacy_dispensing_items` MODIFY `source_type` ENUM('outpatient','igd_vital','igd_pulang') NOT NULL;
-- ALTER TABLE `antenatal_visits` DROP COLUMN `referral_consult`, DROP COLUMN `referral_other_note`;
-- DROP TABLE IF EXISTS `inpatient_discharge_medications`;
-- DROP TABLE IF EXISTS `inpatient_discharge_summaries`;
-- DROP TABLE IF EXISTS `inpatient_order_items`;
-- DROP TABLE IF EXISTS `inpatient_doctor_exams`;
-- DROP TABLE IF EXISTS `inpatient_vital_signs`;