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;
