-- Migration 003: Feedback System
-- This migration adds the feedback tables and is_admin column
-- Note: These are also added to database.js initialization for new databases

-- =============================================================================
-- ADD is_admin COLUMN TO USERS TABLE
-- =============================================================================
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_admin BOOLEAN DEFAULT FALSE;

-- =============================================================================
-- CREATE FEEDBACK TABLE
-- =============================================================================
CREATE TABLE IF NOT EXISTS feedback (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type ENUM('bug', 'feature') NOT NULL,
  area VARCHAR(100) NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NOT NULL,
  status ENUM('submitted', 'acknowledged', 'in_progress', 'completed', 'wont_fix') DEFAULT 'submitted',
  priority ENUM('high', 'medium', 'low') DEFAULT 'medium',
  queue_position INT DEFAULT 0,
  admin_notes TEXT NULL,
  admin_response TEXT NULL,
  completion_message VARCHAR(500) NULL,
  canvas_image_path VARCHAR(255) NULL,
  project_json_path VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  completed_at TIMESTAMP NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_feedback_user_id (user_id),
  INDEX idx_feedback_status (status),
  INDEX idx_feedback_queue_position (queue_position),
  INDEX idx_feedback_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- CREATE FEEDBACK_USER_VIEWS TABLE
-- =============================================================================
CREATE TABLE IF NOT EXISTS feedback_user_views (
  user_id INT PRIMARY KEY,
  last_viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- CREATE FEEDBACK_ADMIN_VIEWS TABLE
-- =============================================================================
CREATE TABLE IF NOT EXISTS feedback_admin_views (
  user_id INT PRIMARY KEY,
  last_viewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================================================
-- SET ADMIN USER (run manually after migration)
-- =============================================================================
-- To make a user an admin, run:
-- UPDATE users SET is_admin = TRUE WHERE username = 'your_admin_username';
