-- DOJ License Manager v4 upgrade
-- Run in phpMyAdmin if upgrading from v1/v2/v3

-- Add rank to users
ALTER TABLE users ADD COLUMN IF NOT EXISTS rank_title ENUM('clerk','magistrate','judge','justice','ass_chief_justice','chief_justice') DEFAULT 'clerk';

-- Replace image_token with image_filename in permits
ALTER TABLE permits
  ADD COLUMN IF NOT EXISTS image_filename VARCHAR(150) NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS image_last_rendered TIMESTAMP NULL,
  ADD COLUMN IF NOT EXISTS signed_by VARCHAR(120) NULL,
  ADD COLUMN IF NOT EXISTS signed_rank VARCHAR(100) NULL;

-- Migrate old tokens to dummy filenames (you'll need to re-render these)
UPDATE permits SET image_filename = CONCAT('permit_', id, '.png') WHERE image_filename = '';

-- Drop old token column (optional, safe to keep)
-- ALTER TABLE permits DROP COLUMN IF EXISTS image_token;

-- Replace image_token with image_filename in inspections
ALTER TABLE inspections
  ADD COLUMN IF NOT EXISTS inspection_type ENUM('food_safety','cleanliness','workplace_safety') NOT NULL DEFAULT 'food_safety',
  ADD COLUMN IF NOT EXISTS image_filename VARCHAR(150) NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS image_last_rendered TIMESTAMP NULL,
  ADD COLUMN IF NOT EXISTS signed_by VARCHAR(120) NULL,
  ADD COLUMN IF NOT EXISTS signed_rank VARCHAR(100) NULL;

UPDATE inspections SET image_filename = CONCAT('inspection_', id, '.png') WHERE image_filename = '';

-- Remove email from businesses
ALTER TABLE businesses DROP COLUMN IF EXISTS email;

-- Set admin rank
UPDATE users SET rank_title='chief_justice' WHERE username='admin';