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;
