-- ========================================
-- TREE TESTING DATABASE SETUP
-- Copy and paste this into phpMyAdmin SQL tab
-- ========================================

-- 1. Create tree_nodes table for hierarchical structure
CREATE TABLE IF NOT EXISTS `tree_nodes` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `study_id` INT(11) NOT NULL,
  `parent_id` INT(11) NULL DEFAULT NULL COMMENT 'NULL for root nodes',
  `title` VARCHAR(255) NOT NULL,
  `sequence` INT(11) NOT NULL DEFAULT 0 COMMENT 'Order among siblings',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_study_id` (`study_id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_sequence` (`sequence`),
  CONSTRAINT `fk_tree_nodes_study` FOREIGN KEY (`study_id`) REFERENCES `studies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tree_nodes_parent` FOREIGN KEY (`parent_id`) REFERENCES `tree_nodes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores hierarchical tree structure for tree testing';

-- 2. Create tree_tasks table for what participants need to find
CREATE TABLE IF NOT EXISTS `tree_tasks` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `study_id` INT(11) NOT NULL,
  `title` VARCHAR(255) NOT NULL COMMENT 'Task description shown to participant',
  `correct_node_id` INT(11) NULL COMMENT 'The correct destination node',
  `sequence` INT(11) NOT NULL DEFAULT 0 COMMENT 'Task order',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_study_id` (`study_id`),
  KEY `idx_sequence` (`sequence`),
  KEY `idx_correct_node` (`correct_node_id`),
  CONSTRAINT `fk_tree_tasks_study` FOREIGN KEY (`study_id`) REFERENCES `studies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tree_tasks_node` FOREIGN KEY (`correct_node_id`) REFERENCES `tree_nodes` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tasks for tree testing studies';

-- 3. Create tree_paths table to record navigation paths
CREATE TABLE IF NOT EXISTS `tree_paths` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `session_id` INT(11) NOT NULL,
  `task_id` INT(11) NOT NULL,
  `node_id` INT(11) NOT NULL COMMENT 'Node that was clicked/expanded',
  `sequence` INT(11) NOT NULL DEFAULT 0 COMMENT 'Order of clicks in this task',
  `action` VARCHAR(50) NOT NULL DEFAULT 'click' COMMENT 'click, expand, collapse, select',
  `timestamp` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_task_id` (`task_id`),
  KEY `idx_node_id` (`node_id`),
  KEY `idx_sequence` (`sequence`),
  CONSTRAINT `fk_tree_paths_session` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tree_paths_task` FOREIGN KEY (`task_id`) REFERENCES `tree_tasks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tree_paths_node` FOREIGN KEY (`node_id`) REFERENCES `tree_nodes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Records participant navigation paths during tree testing';

-- 4. Create tree_task_results table for task completion tracking
CREATE TABLE IF NOT EXISTS `tree_task_results` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `session_id` INT(11) NOT NULL,
  `task_id` INT(11) NOT NULL,
  `selected_node_id` INT(11) NULL COMMENT 'Where participant ended up',
  `correct_node_id` INT(11) NULL COMMENT 'Snapshot of correct answer',
  `is_correct` TINYINT(1) NOT NULL DEFAULT 0,
  `time_seconds` INT(11) NULL COMMENT 'Time taken to complete task',
  `path_length` INT(11) NULL COMMENT 'Number of clicks to reach answer',
  `first_click_node_id` INT(11) NULL COMMENT 'First node clicked for first-click analysis',
  `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_task_id` (`task_id`),
  KEY `idx_is_correct` (`is_correct`),
  CONSTRAINT `fk_tree_results_session` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tree_results_task` FOREIGN KEY (`task_id`) REFERENCES `tree_tasks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tree_results_selected` FOREIGN KEY (`selected_node_id`) REFERENCES `tree_nodes` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_tree_results_first` FOREIGN KEY (`first_click_node_id`) REFERENCES `tree_nodes` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores task completion results for tree testing';

-- ========================================
-- 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 ('tree_nodes', 'tree_tasks', 'tree_paths', 'tree_task_results')
ORDER BY TABLE_NAME;

-- Verify foreign key constraints
SELECT 
  CONSTRAINT_NAME,
  TABLE_NAME,
  COLUMN_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('tree_nodes', 'tree_tasks', 'tree_paths', 'tree_task_results')
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME, CONSTRAINT_NAME;