-- ========================================
-- TEAM VOTING / COLLABORATIVE CARD SORT SETUP
-- Copy and paste this into phpMyAdmin SQL tab
-- ========================================

-- 1. Create team_sessions table for collaborative card sorting
CREATE TABLE IF NOT EXISTS `team_sessions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `study_id` INT(11) NOT NULL,
  `session_name` VARCHAR(255) NOT NULL DEFAULT 'Team Session',
  `session_code` VARCHAR(20) NOT NULL COMMENT 'Join code for team members',
  `status` VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT 'active, analyzing, completed',
  `created_by` INT(11) NULL COMMENT 'User who created the team session',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finalized_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_session_code` (`session_code`),
  KEY `idx_study_id` (`study_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores team/collaborative card sort sessions';

-- 2. Create team_participants table to track team members
CREATE TABLE IF NOT EXISTS `team_participants` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `team_session_id` INT(11) NOT NULL,
  `participant_id` VARCHAR(100) NOT NULL COMMENT 'Unique identifier for team member',
  `display_name` VARCHAR(100) NULL,
  `joined_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_activity` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_participant` (`team_session_id`, `participant_id`),
  KEY `idx_team_session` (`team_session_id`),
  KEY `idx_participant` (`participant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tracks participants in team card sort sessions';

-- 3. Create team_card_votes table for real-time voting
CREATE TABLE IF NOT EXISTS `team_card_votes` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `team_session_id` INT(11) NOT NULL,
  `participant_id` VARCHAR(100) NOT NULL COMMENT 'Unique identifier for team member',
  `card_id` INT(11) NOT NULL,
  `category_name` VARCHAR(255) NOT NULL,
  `voted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_vote` (`team_session_id`, `participant_id`, `card_id`),
  KEY `idx_team_session` (`team_session_id`),
  KEY `idx_card` (`card_id`),
  KEY `idx_category` (`category_name`),
  KEY `idx_participant` (`participant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores individual votes/placements for team card sorting';

-- ========================================
-- ADD FOREIGN KEY CONSTRAINTS (OPTIONAL)
-- Only run these if you want referential integrity
-- Comment out if you get errors
-- ========================================

-- Add foreign key for team_sessions -> studies
-- ALTER TABLE `team_sessions` 
--   ADD CONSTRAINT `fk_team_sessions_study` 
--   FOREIGN KEY (`study_id`) REFERENCES `studies` (`id`) ON DELETE CASCADE;

-- Add foreign key for team_participants -> team_sessions
-- ALTER TABLE `team_participants` 
--   ADD CONSTRAINT `fk_team_participants_session` 
--   FOREIGN KEY (`team_session_id`) REFERENCES `team_sessions` (`id`) ON DELETE CASCADE;

-- Add foreign key for team_card_votes -> team_sessions
-- ALTER TABLE `team_card_votes` 
--   ADD CONSTRAINT `fk_team_votes_session` 
--   FOREIGN KEY (`team_session_id`) REFERENCES `team_sessions` (`id`) ON DELETE CASCADE;

-- Add foreign key for team_card_votes -> cards (only if cards table exists with id column)
-- ALTER TABLE `team_card_votes` 
--   ADD CONSTRAINT `fk_team_votes_card` 
--   FOREIGN KEY (`card_id`) REFERENCES `cards` (`id`) ON DELETE CASCADE;

-- ========================================
-- VERIFICATION QUERIES
-- Run these after creating tables to verify
-- ========================================

-- Check that all tables were created
SELECT 
  TABLE_NAME, 
  TABLE_ROWS, 
  CREATE_TIME 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME IN ('team_sessions', 'team_participants', 'team_card_votes')
ORDER BY TABLE_NAME;

-- Show table structures
DESCRIBE team_sessions;
DESCRIBE team_participants;
DESCRIBE team_card_votes;

-- Sample data for testing (optional - uncomment to use)
-- INSERT INTO `team_sessions` (`study_id`, `session_name`, `session_code`, `status`) 
-- VALUES (1, 'Test Team Session', 'TEAM123', 'active');