CREATE TABLE IF NOT EXISTS patient_safety_mapping_imports (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    original_file_name VARCHAR(255) NULL,
    tahun VARCHAR(10) NULL,
    periode VARCHAR(80) NULL,
    total_rows INT UNSIGNED NOT NULL DEFAULT 0,
    total_mapped INT UNSIGNED NOT NULL DEFAULT 0,
    total_unmapped INT UNSIGNED NOT NULL DEFAULT 0,
    uploaded_by BIGINT UNSIGNED NULL,
    uploaded_by_name VARCHAR(190) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_psmi_tahun (tahun),
    KEY idx_psmi_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_safety_mapping_rows (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    import_id BIGINT UNSIGNED NOT NULL,
    no_urut INT UNSIGNED NULL,
    tanggal_insiden VARCHAR(80) NULL,
    waktu_insiden VARCHAR(80) NULL,
    unit_mapping VARCHAR(80) NULL,
    bagian_unit VARCHAR(120) NULL,
    area_lokasi VARCHAR(255) NULL,
    unit_penyebab VARCHAR(255) NULL,
    sasaran_kp VARCHAR(255) NULL,
    tipe_insiden VARCHAR(255) NULL,
    jenis_insiden VARCHAR(80) NULL,
    grading VARCHAR(80) NULL,
    insiden_terjadi LONGTEXT NULL,
    penyebab_insiden LONGTEXT NULL,
    kategori_risiko VARCHAR(190) NULL,
    penanganan LONGTEXT NULL,
    rekomendasi LONGTEXT NULL,
    pasien VARCHAR(190) NULL,
    no_rm VARCHAR(80) NULL,
    pelapor VARCHAR(190) NULL,
    penerima_laporan VARCHAR(190) NULL,
    status_mapping ENUM('mapped','unmapped','edited') NOT NULL DEFAULT 'mapped',
    raw_json LONGTEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_psmr_import (import_id),
    KEY idx_psmr_unit (unit_mapping),
    KEY idx_psmr_grade (grading),
    CONSTRAINT fk_ps_mapping_import FOREIGN KEY (import_id) REFERENCES patient_safety_mapping_imports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
