CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(80) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('admin','registrasi','perawat','dokter','farmasi','laboratorium') NOT NULL,active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE patients (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,rm_number VARCHAR(30) UNIQUE,nik VARCHAR(16) UNIQUE,full_name VARCHAR(160) NOT NULL,birth_date DATE NULL,sex ENUM('L','P') NULL,phone VARCHAR(40),address TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE visits (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,patient_id BIGINT UNSIGNED NOT NULL,visit_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,queue_number INT NOT NULL,clinic VARCHAR(80) NOT NULL DEFAULT 'Umum',payment_type ENUM('umum','bpjs','lainnya') NOT NULL DEFAULT 'umum',status ENUM('menunggu','triase','dokter','farmasi','selesai','batal') NOT NULL DEFAULT 'menunggu',created_by BIGINT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(patient_id) REFERENCES patients(id),FOREIGN KEY(created_by) REFERENCES users(id),INDEX(visit_at,status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE vitals (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,visit_id BIGINT UNSIGNED NOT NULL UNIQUE,systolic SMALLINT UNSIGNED NULL,diastolic SMALLINT UNSIGNED NULL,pulse SMALLINT UNSIGNED NULL,temperature DECIMAL(4,1) NULL,respiration SMALLINT UNSIGNED NULL,spo2 TINYINT UNSIGNED NULL,weight DECIMAL(5,2) NULL,height DECIMAL(5,2) NULL,complaint TEXT,author_id BIGINT UNSIGNED NOT NULL,recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(visit_id) REFERENCES visits(id),FOREIGN KEY(author_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE clinical_notes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,visit_id BIGINT UNSIGNED NOT NULL UNIQUE,subjective TEXT NOT NULL,objective TEXT NOT NULL,assessment TEXT NOT NULL,plan TEXT NOT NULL,diagnosis_code VARCHAR(20) NULL,diagnosis_text VARCHAR(255) NULL,procedure_text TEXT NULL,author_id BIGINT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(visit_id) REFERENCES visits(id),FOREIGN KEY(author_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE medicines (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(160) NOT NULL,unit VARCHAR(30) NOT NULL DEFAULT 'tablet',stock INT UNSIGNED NOT NULL DEFAULT 0,active TINYINT(1) NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE prescriptions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,visit_id BIGINT UNSIGNED NOT NULL,medicine_id BIGINT UNSIGNED NOT NULL,quantity INT UNSIGNED NOT NULL,directions VARCHAR(255) NOT NULL,status ENUM('pending','dispensed') NOT NULL DEFAULT 'pending',prescriber_id BIGINT UNSIGNED NOT NULL,dispensed_by BIGINT UNSIGNED NULL,dispensed_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(visit_id) REFERENCES visits(id),FOREIGN KEY(medicine_id) REFERENCES medicines(id),FOREIGN KEY(prescriber_id) REFERENCES users(id),FOREIGN KEY(dispensed_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE lab_orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,visit_id BIGINT UNSIGNED NOT NULL,test_name VARCHAR(160) NOT NULL,result_text TEXT NULL,status ENUM('requested','completed') NOT NULL DEFAULT 'requested',ordered_by BIGINT UNSIGNED NOT NULL,completed_by BIGINT UNSIGNED NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,completed_at DATETIME NULL,FOREIGN KEY(visit_id) REFERENCES visits(id),FOREIGN KEY(ordered_by) REFERENCES users(id),FOREIGN KEY(completed_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NULL,action VARCHAR(100) NOT NULL,subject_type VARCHAR(50),subject_id BIGINT UNSIGNED NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(created_at,user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ekstensi SIM-RME RS: jalankan setelah schema.sql (database baru)
ALTER TABLE users MODIFY role ENUM('admin','registrasi','perawat','dokter','farmasi','laboratorium','admisi','kasir','manajemen') NOT NULL;
CREATE TABLE wards (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL UNIQUE,active TINYINT(1) NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE beds (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ward_id BIGINT UNSIGNED NOT NULL,bed_code VARCHAR(40) NOT NULL UNIQUE,class_name VARCHAR(50) NOT NULL DEFAULT 'Kelas 3',status ENUM('available','occupied','maintenance') NOT NULL DEFAULT 'available',FOREIGN KEY(ward_id) REFERENCES wards(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE admissions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,patient_id BIGINT UNSIGNED NOT NULL,bed_id BIGINT UNSIGNED NOT NULL,admitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,discharged_at DATETIME NULL,status ENUM('active','discharged') NOT NULL DEFAULT 'active',admission_reason TEXT NOT NULL,discharge_summary TEXT NULL,created_by BIGINT UNSIGNED NOT NULL,discharged_by BIGINT UNSIGNED NULL,FOREIGN KEY(patient_id) REFERENCES patients(id),FOREIGN KEY(bed_id) REFERENCES beds(id),FOREIGN KEY(created_by) REFERENCES users(id),FOREIGN KEY(discharged_by) REFERENCES users(id),INDEX(patient_id,status),INDEX(bed_id,status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE inpatient_notes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,admission_id BIGINT UNSIGNED NOT NULL,recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,note_type ENUM('cppt','nursing','discharge_plan') NOT NULL DEFAULT 'cppt',subjective TEXT NOT NULL,objective TEXT NOT NULL,assessment TEXT NOT NULL,plan TEXT NOT NULL,author_id BIGINT UNSIGNED NOT NULL,FOREIGN KEY(admission_id) REFERENCES admissions(id),FOREIGN KEY(author_id) REFERENCES users(id),INDEX(admission_id,recorded_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE surgery_cases (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,patient_id BIGINT UNSIGNED NOT NULL,admission_id BIGINT UNSIGNED NULL,procedure_name VARCHAR(200) NOT NULL,scheduled_at DATETIME NOT NULL,status ENUM('planned','completed','cancelled') NOT NULL DEFAULT 'planned',clinical_note TEXT NULL,created_by BIGINT UNSIGNED NOT NULL,FOREIGN KEY(patient_id) REFERENCES patients(id),FOREIGN KEY(admission_id) REFERENCES admissions(id),FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE charges (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,patient_id BIGINT UNSIGNED NOT NULL,visit_id BIGINT UNSIGNED NULL,admission_id BIGINT UNSIGNED NULL,description VARCHAR(200) NOT NULL,amount DECIMAL(14,2) NOT NULL,status ENUM('unpaid','paid','void') NOT NULL DEFAULT 'unpaid',created_by BIGINT UNSIGNED NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,paid_at DATETIME NULL,FOREIGN KEY(patient_id) REFERENCES patients(id),FOREIGN KEY(visit_id) REFERENCES visits(id),FOREIGN KEY(admission_id) REFERENCES admissions(id),FOREIGN KEY(created_by) REFERENCES users(id),CHECK(amount>=0),INDEX(patient_id,status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SIMRS Tipe C v0.4: jalankan hanya SETELAH schema-full.sql v0.3, pada database UJI.
ALTER TABLE users MODIFY role ENUM('admin','registrasi','perawat','dokter','farmasi','laboratorium','admisi','kasir','manajemen','igd','radiologi') NOT NULL;
CREATE TABLE emergency_visits (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, patient_id BIGINT UNSIGNED NOT NULL, arrival_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, triage_level ENUM('resusitasi','emergensi','urgent','less_urgent','non_urgent') NOT NULL, chief_complaint TEXT NOT NULL, vital_summary TEXT NULL, status ENUM('active','referred','admitted','discharged') NOT NULL DEFAULT 'active', disposition_note TEXT NULL, created_by BIGINT UNSIGNED NOT NULL, closed_by BIGINT UNSIGNED NULL, closed_at DATETIME NULL, FOREIGN KEY(patient_id) REFERENCES patients(id), FOREIGN KEY(created_by) REFERENCES users(id), FOREIGN KEY(closed_by) REFERENCES users(id), INDEX(status,arrival_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE radiology_orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, patient_id BIGINT UNSIGNED NOT NULL, visit_id BIGINT UNSIGNED NULL, admission_id BIGINT UNSIGNED NULL, modality VARCHAR(80) NOT NULL, exam_name VARCHAR(160) NOT NULL, clinical_indication TEXT NOT NULL, status ENUM('requested','reported') NOT NULL DEFAULT 'requested', report_text TEXT NULL, ordered_by BIGINT UNSIGNED NOT NULL, reported_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, reported_at DATETIME NULL, FOREIGN KEY(patient_id) REFERENCES patients(id), FOREIGN KEY(visit_id) REFERENCES visits(id), FOREIGN KEY(admission_id) REFERENCES admissions(id), FOREIGN KEY(ordered_by) REFERENCES users(id), FOREIGN KEY(reported_by) REFERENCES users(id), INDEX(status,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE satusehat_outbox (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, resource_type VARCHAR(60) NOT NULL, local_type VARCHAR(60) NOT NULL, local_id BIGINT UNSIGNED NOT NULL, status ENUM('draft','ready','sent','failed') NOT NULL DEFAULT 'draft', payload_json JSON NULL, external_id VARCHAR(120) NULL, last_error VARCHAR(500) NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(created_by) REFERENCES users(id), UNIQUE KEY uq_resource (resource_type,local_type,local_id), INDEX(status,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
