-- ========================================
-- USER-CREATED CATEGORIES TRACKING
-- Copy and paste this into phpMyAdmin SQL tab
-- ========================================

-- Create table to track user-created categories during card sorting
CREATE TABLE IF NOT EXISTS `user_created_categories` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `study_id` INT(11) NOT NULL,
  `session_id` INT(11) NOT NULL COMMENT 'Individual session that created this category',
  `team_session_id` INT(11) NULL COMMENT 'Team session ID if this was created in team mode',
  `category_name` VARCHAR(255) NOT NULL COMMENT 'The custom category name user typed in',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_study_id` (`study_id`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_team_session_id` (`team_session_id`),
  KEY `idx_category_name` (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tracks user-created categories during card sorting sessions';

-- Verify the table was created
DESCRIBE `user_created_categories`;

-- Show how to query user-created categories
-- Example: Find all custom categories for a study
-- SELECT category_name, COUNT(*) as usage_count
-- FROM user_created_categories
-- WHERE study_id = YOUR_STUDY_ID
-- GROUP BY category_name
-- ORDER BY usage_count DESC;