
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;
