-- Migration: Logbook verification/signature + messages
-- Date: 2026-02-26

-- Add verification/signature fields to logs
ALTER TABLE logs
  ADD COLUMN IF NOT EXISTS dpjp_signature LONGTEXT DEFAULT NULL AFTER status,
  ADD COLUMN IF NOT EXISTS dpjp_verified BOOLEAN DEFAULT FALSE AFTER dpjp_signature,
  ADD COLUMN IF NOT EXISTS dpjp_verified_by INT DEFAULT NULL AFTER dpjp_verified,
  ADD COLUMN IF NOT EXISTS dpjp_verified_at TIMESTAMP NULL AFTER dpjp_verified_by,
  ADD COLUMN IF NOT EXISTS supervisor_note TEXT DEFAULT NULL AFTER dpjp_verified_at;

ALTER TABLE logs
  ADD INDEX IF NOT EXISTS idx_logs_verified (dpjp_verified),
  ADD INDEX IF NOT EXISTS idx_logs_verified_by (dpjp_verified_by),
  ADD CONSTRAINT fk_logs_verified_by
    FOREIGN KEY (dpjp_verified_by) REFERENCES users(id) ON DELETE SET NULL;

-- Messages table (for logbook/portfolio/user messages)
CREATE TABLE IF NOT EXISTS messages (
  id INT PRIMARY KEY AUTO_INCREMENT,
  sender_id INT NOT NULL,
  recipient_id INT NOT NULL,
  target_type ENUM('logbook', 'portfolio', 'user') NOT NULL,
  target_id INT DEFAULT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_messages_recipient (recipient_id, created_at),
  INDEX idx_messages_target (target_type, target_id)
);
