-- Database untuk Logbook PPDS-2 (Program Pendidikan Dokter Spesialis)

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  role ENUM('admin', 'user') DEFAULT 'user',
  specialization VARCHAR(100),
  semester INT CHECK (semester BETWEEN 1 AND 5),
  institution VARCHAR(100),
  email VARCHAR(100),
  phone VARCHAR(20),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE log_categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  category_name VARCHAR(100) NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE activity_types (
  id INT PRIMARY KEY AUTO_INCREMENT,
  type_name VARCHAR(100) NOT NULL,
  category_id INT NOT NULL,
  description TEXT,
  FOREIGN KEY (category_id) REFERENCES log_categories(id) ON DELETE CASCADE
);

CREATE TABLE logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  activity_type_id INT NOT NULL,
  log_date DATE NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  location VARCHAR(100),
  supervisor_name VARCHAR(100),
  teaching_method VARCHAR(100),
  case_count INT DEFAULT 1,
  status ENUM('draft', 'submitted', 'approved') DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (activity_type_id) REFERENCES activity_types(id) ON DELETE CASCADE,
  INDEX idx_user_date (user_id, log_date)
);

CREATE TABLE procedures (
  id INT PRIMARY KEY AUTO_INCREMENT,
  log_id INT NOT NULL,
  procedure_name VARCHAR(255) NOT NULL,
  procedure_count INT DEFAULT 1,
  notes TEXT,
  FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE
);

CREATE TABLE case_details (
  id INT PRIMARY KEY AUTO_INCREMENT,
  log_id INT NOT NULL,
  diagnosis VARCHAR(255),
  patient_age INT,
  patient_gender ENUM('Laki-laki', 'Perempuan'),
  treatment_type VARCHAR(100),
  outcome VARCHAR(100),
  notes TEXT,
  FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE
);

CREATE TABLE generic_menu_tables (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  table_key VARCHAR(50) NOT NULL,
  rows_json LONGTEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_generic_menu_user_table (user_id, table_key),
  INDEX idx_generic_menu_user (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Insert kategori aktivitas standar
INSERT INTO log_categories (category_name, description) VALUES
('Kasus Klinis', 'Kasus pasien yang ditangani'),
('Prosedur', 'Prosedur medis yang dilakukan'),
('Pembelajaran', 'Sesi pembelajaran dan diskusi'),
('Penelitian', 'Kegiatan penelitian'),
('Seminar', 'Kehadiran dalam seminar dan workshop'),
('Publikasi', 'Publikasi jurnal dan makalah');

-- Insert tipe aktivitas
INSERT INTO activity_types (type_name, category_id, description) VALUES
('Kasus Rawat Inap', 1, 'Pasien yang dirawat inap'),
('Kasus Rawat Jalan', 1, 'Pasien rawat jalan'),
('Kasus Gawat Darurat', 1, 'Pasien gawat darurat'),
('Operasi/Bedah', 2, 'Prosedur operasi'),
('Prosedur Diagnostik', 2, 'Prosedur diagnostik'),
('Prosedur Terapeutik', 2, 'Prosedur terapeutik'),
('Diskusi Kasus', 3, 'Diskusi kasus klinis'),
('Pemresentasi Ilmiah', 3, 'Presentasi ilmiah'),
('Kuliah/Ceramah', 3, 'Menghadiri kuliah'),
('Penelitian Klinis', 4, 'Penelitian klinis'),
('Penelitian Dasar', 4, 'Penelitian laboratorium'),
('Seminar Nasional', 5, 'Seminar tingkat nasional'),
('Seminar Internasional', 5, 'Seminar tingkat internasional'),
('Workshop', 5, 'Workshop atau pelatihan'),
('Publikasi Jurnal', 6, 'Publikasi di jurnal'),
('Publikasi Konferensi', 6, 'Publikasi di konferensi');

-- Data user contoh untuk testing
INSERT INTO users (username, password, full_name, role, specialization, semester, institution, email, phone) VALUES
('admin', '$2y$10$abcdefghijklmnopqrstuvwxyz123456789', 'Administrator', 'admin', 'System Admin', NULL, 'Rumah Sakit', 'admin@example.com', '081234567890'),
('zulfadli', '$2y$10$abcdefghijklmnopqrstuvwxyz123456789', 'dr. Zulfadli, Sp.OG', 'user', 'Obstetri dan Ginekologi', 2, 'RSUP. Dr. M. Djamil Padang', 'zulfadli@example.com', '082312345678');
