-- =============================================
-- Question-Answer System Database Schema
-- Similar structure to quiz system but for Q&A
-- =============================================

-- Table for storing Q&A questions (open-ended questions)
CREATE TABLE `qa_questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `topic_id` int(11) NOT NULL,
  `question_text` text NOT NULL,
  `expected_answer` text DEFAULT NULL COMMENT 'Expected/model answer for reference',
  `answer_keywords` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`answer_keywords`)) COMMENT 'Keywords that should be present in a good answer',
  `difficulty` enum('easy','medium','hard','all') DEFAULT 'medium',
  `explanation` text DEFAULT NULL COMMENT 'Detailed explanation of the answer',
  `marks` int(11) NOT NULL DEFAULT 1 COMMENT 'Marks/points for this question',
  `time_limit` int(11) DEFAULT 300 COMMENT 'Time limit in seconds (default 5 minutes)',
  `is_ai_generated` tinyint(1) DEFAULT 0,
  `ai_metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`ai_metadata`)),
  `user_id` int(11) DEFAULT NULL COMMENT 'User who generated this AI question (NULL for system/topic questions)',
  `ai_model` varchar(50) DEFAULT NULL COMMENT 'AI model used to generate this question',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_qa_questions_topic` (`topic_id`),
  KEY `idx_qa_questions_difficulty` (`difficulty`),
  KEY `idx_qa_questions_user` (`user_id`),
  KEY `idx_qa_questions_ai_generated` (`is_ai_generated`),
  FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Q&A questions - open-ended questions for practice';

-- Table for Q&A sessions (similar to quiz_sessions)
CREATE TABLE `qa_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(50) NOT NULL,
  `qa_type` enum('topic','random','practice','ai_topic_qa') NOT NULL,
  `qa_id` varchar(100) DEFAULT NULL COMMENT 'Generated Q&A session ID',
  `course_id` varchar(50) DEFAULT NULL,
  `topic_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`topic_ids`)),
  `total_questions` int(11) NOT NULL DEFAULT 0,
  `answered_questions` int(11) NOT NULL DEFAULT 0,
  `total_marks_possible` int(11) DEFAULT 0 COMMENT 'Total marks possible',
  `marks_obtained` decimal(8,2) DEFAULT 0.00 COMMENT 'Total marks obtained (AI evaluated)',
  `human_marks_obtained` decimal(8,2) DEFAULT NULL COMMENT 'Human-evaluated marks',
  `score_percentage` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Auto-calculated percentage',
  `human_score_percentage` decimal(5,2) DEFAULT NULL COMMENT 'Human-evaluated percentage',
  `time_taken` int(11) DEFAULT 0 COMMENT 'Total time taken in seconds',
  `completed_at` datetime DEFAULT NULL,
  `is_ai_generated` tinyint(1) DEFAULT 0,
  `ai_qa_metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`ai_qa_metadata`)),
  `evaluation_status` enum('pending','ai_evaluated','human_reviewed','final') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_qa_sessions_user` (`user_id`),
  KEY `idx_qa_sessions_type` (`qa_type`),
  KEY `idx_qa_sessions_course` (`course_id`),
  KEY `idx_qa_sessions_completed` (`completed_at`),
  KEY `idx_qa_sessions_evaluation` (`evaluation_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Q&A practice sessions';

-- Table for Q&A responses (user answers to questions)
CREATE TABLE `qa_responses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `user_answer` text NOT NULL COMMENT 'User provided answer',
  `ai_evaluation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`ai_evaluation`)) COMMENT 'AI evaluation results with score and feedback',
  `human_evaluation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`human_evaluation`)) COMMENT 'Human evaluation if reviewed',
  `ai_score` decimal(5,2) DEFAULT NULL COMMENT 'AI-assigned score (0-100)',
  `human_score` decimal(5,2) DEFAULT NULL COMMENT 'Human-assigned score (0-100)',
  `final_score` decimal(5,2) DEFAULT NULL COMMENT 'Final score (human takes precedence)',
  `time_taken` int(11) DEFAULT 0 COMMENT 'Time taken to answer in seconds',
  `topic_id` int(11) DEFAULT NULL,
  `answer_quality` enum('excellent','good','average','poor','insufficient') DEFAULT NULL COMMENT 'AI-assessed answer quality',
  `feedback` text DEFAULT NULL COMMENT 'Feedback for improvement',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_qa_responses_session` (`session_id`),
  KEY `idx_qa_responses_question` (`question_id`),
  KEY `idx_qa_responses_topic` (`topic_id`),
  KEY `idx_qa_responses_quality` (`answer_quality`),
  FOREIGN KEY (`session_id`) REFERENCES `qa_sessions` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`question_id`) REFERENCES `qa_questions` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User responses to Q&A questions with AI evaluation';

-- Table for Q&A analytics (performance tracking)
CREATE TABLE `qa_analytics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(50) NOT NULL,
  `topic_id` int(11) NOT NULL,
  `total_questions_attempted` int(11) DEFAULT 0,
  `total_questions_answered` int(11) DEFAULT 0,
  `average_score` decimal(5,2) DEFAULT 0.00,
  `average_time_per_question` decimal(8,2) DEFAULT 0.00 COMMENT 'Average seconds per question',
  `improvement_trend` decimal(5,2) DEFAULT 0.00 COMMENT 'Score improvement over time',
  `last_practiced_at` timestamp NULL DEFAULT NULL,
  `strengths` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`strengths`)) COMMENT 'Identified strengths',
  `weaknesses` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`weaknesses`)) COMMENT 'Areas for improvement',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_qa_analytics_user_topic` (`user_id`, `topic_id`),
  KEY `idx_qa_analytics_topic` (`topic_id`),
  KEY `idx_qa_analytics_last_practiced` (`last_practiced_at`),
  FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Q&A performance analytics per user and topic';

-- Insert sample Q&A questions for testing
INSERT INTO `qa_questions` (`topic_id`, `question_text`, `expected_answer`, `answer_keywords`, `difficulty`, `explanation`, `marks`, `time_limit`) VALUES
(125, '**Question:** Explain the concept of percentage change and provide the mathematical formula for calculating it.', 
'Percentage change is a way to express the change between an old value and a new value as a percentage of the old value. The formula is: Percentage Change = ((New Value - Old Value) / Old Value) × 100%', 
'["percentage change", "formula", "new value", "old value", "mathematical", "calculation"]', 
'medium', 
'The percentage change formula helps compare changes relative to the original value. A positive result indicates an increase, while a negative result indicates a decrease.', 
2, 300),

(126, '**Question:** Define velocity in physics and explain how it differs from speed. Include the vector nature in your explanation.', 
'Velocity is a vector quantity that describes the rate of change of displacement with respect to time. Unlike speed (which is scalar), velocity has both magnitude and direction. The formula is v = Δs/Δt where Δs is displacement and Δt is time.', 
'["velocity", "vector", "displacement", "time", "direction", "magnitude", "speed", "scalar"]', 
'medium', 
'Understanding the vector nature of velocity is crucial in physics. While speed only tells us how fast an object moves, velocity tells us both how fast and in which direction.', 
3, 360),

(127, '**Question:** Derive and explain the formula for the range of a projectile launched at an angle θ with initial velocity u.', 
'For projectile motion, the range R is the horizontal distance traveled. Using kinematic equations and considering both horizontal and vertical components of motion, the range formula is: R = (u² sin(2θ))/g, where u is initial velocity, θ is launch angle, and g is acceleration due to gravity.', 
'["range", "projectile", "horizontal", "kinematic", "sin(2θ)", "initial velocity", "launch angle", "gravity"]', 
'hard', 
'The derivation involves analyzing horizontal and vertical motion separately, finding the time of flight, and then calculating the horizontal distance covered during this time.', 
4, 480);

-- Add indexes for better performance
ALTER TABLE `qa_questions` ADD INDEX `idx_qa_questions_marks` (`marks`);
ALTER TABLE `qa_sessions` ADD INDEX `idx_qa_sessions_marks` (`marks_obtained`);
ALTER TABLE `qa_responses` ADD INDEX `idx_qa_responses_scores` (`ai_score`, `human_score`, `final_score`);
