-- Staff Manager Module Schema
-- Extends the staff table and adds onboarding/offboarding tracking

-- Add columns to staff table for management
ALTER TABLE staff
  ADD COLUMN IF NOT EXISTS active TINYINT(1) NOT NULL DEFAULT 1 AFTER password_hash,
  ADD COLUMN IF NOT EXISTS onboarded_at DATETIME NULL AFTER active,
  ADD COLUMN IF NOT EXISTS onboarded_by INT NULL AFTER onboarded_at,
  ADD COLUMN IF NOT EXISTS offboarded_at DATETIME NULL AFTER onboarded_by,
  ADD COLUMN IF NOT EXISTS offboarded_by INT NULL AFTER offboarded_at,
  ADD COLUMN IF NOT EXISTS offboard_reason TEXT NULL AFTER offboarded_by,
  ADD COLUMN IF NOT EXISTS email_hash VARCHAR(64) NULL AFTER offboard_reason,
  ADD COLUMN IF NOT EXISTS discord_id VARCHAR(32) NULL AFTER email_hash,
  ADD COLUMN IF NOT EXISTS notes TEXT NULL AFTER discord_id;

-- Add foreign keys for onboarding/offboarding tracking
ALTER TABLE staff
  ADD CONSTRAINT fk_staff_onboarded_by FOREIGN KEY (onboarded_by) REFERENCES staff(id) ON DELETE SET NULL;

ALTER TABLE staff
  ADD CONSTRAINT fk_staff_offboarded_by FOREIGN KEY (offboarded_by) REFERENCES staff(id) ON DELETE SET NULL;

-- Index for active staff lookups
CREATE INDEX IF NOT EXISTS idx_staff_active ON staff(active);
