-- Support Tickets System Migration
-- Run: npx prisma db push (recommended)
-- Or run this SQL manually

-- Support Tickets Table
CREATE TABLE IF NOT EXISTS support_tickets (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  ticketNumber VARCHAR(50) NOT NULL,
  userId VARCHAR(36) NOT NULL,
  characterId VARCHAR(100),
  category VARCHAR(20) NOT NULL DEFAULT 'GENERAL',
  priority VARCHAR(20) NOT NULL DEFAULT 'MEDIUM',
  status VARCHAR(30) NOT NULL DEFAULT 'OPEN',
  subject VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  assignedToId VARCHAR(36),
  resolvedAt DATETIME,
  closedAt DATETIME,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_ticket_number (ticketNumber),
  INDEX idx_status (status),
  INDEX idx_category (category),
  INDEX idx_userId (userId)
);

-- Ticket Messages Table
CREATE TABLE IF NOT EXISTS ticket_messages (
  id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
  ticketId VARCHAR(36) NOT NULL,
  userId VARCHAR(36) NOT NULL,
  message TEXT NOT NULL,
  isStaff TINYINT(1) DEFAULT 0,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ticketId (ticketId)
);

-- Alternative: Use Prisma (recommended)
-- Run: npx prisma db push
-- This will automatically create the tables based on schema.prisma

SELECT 'Support tickets migration ready - use npx prisma db push' as status;
