-- Migration: Add Priority to Tickets Table
-- Date: October 20, 2025
-- Purpose: Fix disconnect where users select priority but it was never saved

-- Add priority column to tickets table (idempotent)
SET @db := DATABASE();
SET @has_priority := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='tickets' AND COLUMN_NAME='priority'
);

SET @sql := IF(@has_priority=0,
  "ALTER TABLE `tickets` ADD COLUMN `priority` ENUM('low','medium','high','urgent') DEFAULT 'medium' AFTER `status`",
  'SELECT 1'
);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Set existing tickets to default medium priority
-- Set existing tickets to default if column exists
SET @has_priority := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='tickets' AND COLUMN_NAME='priority'
);
SET @sql := IF(@has_priority=1,
  "UPDATE `tickets` SET `priority`='medium' WHERE `priority` IS NULL",
  'SELECT 1'
);
PREPARE s2 FROM @sql; EXECUTE s2; DEALLOCATE PREPARE s2;

-- Verification query (uncomment to test)
-- SELECT id, user_id, subject, status, priority, created_at FROM tickets LIMIT 10;
