-- Migration: Add Portfolio tables for PPDS-2 E-Logbook
-- Purpose: Support portfolio submissions for OK, Poliklinik, and Rawat Inap/Bangsal

-- Portfolio table
CREATE TABLE IF NOT EXISTS portfolio (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  divisi VARCHAR(100) NOT NULL,
  semester INT NOT NULL CHECK (semester BETWEEN 1 AND 5),
  setting_type ENUM('OK', 'Poliklinik', 'Rawat_Inap') NOT NULL,
  nomor INT NOT NULL,
  tanggal DATE NOT NULL,
  identifikasi_pasien TEXT,
  diagnosis VARCHAR(255),
  tatalaksana TEXT,
  kompetenszi ENUM('Level I', 'Level II', 'Level III', 'Level IV'),
  supervisor_name VARCHAR(100),
  dpjp_signature LONGBLOB,
  dpjp_verified BOOLEAN DEFAULT FALSE,
  dpjp_verified_by INT,
  dpjp_verified_at TIMESTAMP NULL,
  trainee_signature LONGBLOB,
  status ENUM('draft', 'submitted', 'verified') 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 (dpjp_verified_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_user_divisi_semester (user_id, divisi, semester),
  INDEX idx_setting_type (setting_type),
  INDEX idx_tanggal (tanggal)
);

-- Portfolio counter per user, divisi, semester, and setting
CREATE TABLE IF NOT EXISTS portfolio_counter (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  divisi VARCHAR(100) NOT NULL,
  semester INT NOT NULL,
  setting_type ENUM('OK', 'Poliklinik', 'Rawat_Inap') NOT NULL,
  counter INT DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_counter (user_id, divisi, semester, setting_type),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create function to auto-increment nomor for portfolio
DELIMITER //
CREATE TRIGGER IF NOT EXISTS portfolio_before_insert
BEFORE INSERT ON portfolio
FOR EACH ROW
BEGIN
  DECLARE next_nomor INT;
  
  -- Get next number for this user, divisi, semester, and setting
  SELECT COALESCE(counter, 0) + 1 INTO next_nomor
  FROM portfolio_counter
  WHERE user_id = NEW.user_id 
    AND divisi = NEW.divisi 
    AND semester = NEW.semester 
    AND setting_type = NEW.setting_type;
  
  SET NEW.nomor = next_nomor;
  
  -- Update or insert counter
  INSERT INTO portfolio_counter (user_id, divisi, semester, setting_type, counter)
  VALUES (NEW.user_id, NEW.divisi, NEW.semester, NEW.setting_type, next_nomor)
  ON DUPLICATE KEY UPDATE counter = next_nomor;
END //
DELIMITER ;
