-- Complete Question Pattern Templates Setup
-- This file contains all SQL commands needed to set up the template system

-- Drop table if exists (for clean installation)
DROP TABLE IF EXISTS `question_pattern_templates`;

-- Create the question_pattern_templates table
CREATE TABLE `question_pattern_templates` (
  `template_id` int(11) NOT NULL AUTO_INCREMENT,
  `template_name` varchar(255) NOT NULL,
  `template_description` text,
  `total_marks` int(11) NOT NULL DEFAULT 0,
  `sections` longtext NOT NULL COMMENT 'JSON array of sections with questions',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`template_id`),
  UNIQUE KEY `template_name` (`template_name`),
  KEY `idx_active` (`is_active`),
  KEY `idx_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default templates
INSERT INTO `question_pattern_templates` (`template_name`, `template_description`, `total_marks`, `sections`, `is_active`) VALUES
('CI Template', 'Continuous Internal Assessment Pattern: Part A (6×2=12), Part B (3×16=48)', 60, '[
  {
    "section_name": "Part A",
    "num_questions": 6,
    "marks_per_question": 2,
    "section_total": 12,
    "question_start_num": 1,
    "question_end_num": 6,
    "questions": [
      {"question_no": 1, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 1", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 2, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 2", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 3, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 3", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 4, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 4", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 5, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 5", "co_codes": "CO2", "po_codes": "PO1"},
      {"question_no": 6, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 6", "co_codes": "CO2", "po_codes": "PO1"}
    ]
  },
  {
    "section_name": "Part B",
    "num_questions": 3,
    "marks_per_question": 16,
    "section_total": 48,
    "question_start_num": 7,
    "question_end_num": 9,
    "questions": [
      {"question_no": "7-a", "question_type": "alpha", "cognitive_level": "Understanding", "question_text": "Question 7 (a)", "co_codes": "CO1", "po_codes": "PO2,PO3", "is_sub_question": true},
      {"question_no": "7-b", "question_type": "alpha", "cognitive_level": "Understanding", "question_text": "Question 7 (b)", "co_codes": "CO1", "po_codes": "PO2,PO3", "is_sub_question": true},
      {"question_no": "8-a", "question_type": "alpha", "cognitive_level": "Understanding", "question_text": "Question 8 (a)", "co_codes": "CO1", "po_codes": "PO2,PO3", "is_sub_question": true},
      {"question_no": "8-b", "question_type": "alpha", "cognitive_level": "Understanding", "question_text": "Question 8 (b)", "co_codes": "CO1", "po_codes": "PO2,PO3", "is_sub_question": true},
      {"question_no": "9-a", "question_type": "alpha", "cognitive_level": "Understanding", "question_text": "Question 9 (a)", "co_codes": "CO2", "po_codes": "PO2,PO3", "is_sub_question": true},
      {"question_no": "9-b", "question_type": "alpha", "cognitive_level": "Understanding", "question_text": "Question 9 (b)", "co_codes": "CO2", "po_codes": "PO2,PO3", "is_sub_question": true}
    ]
  }
]', 1),

('SEM Template', 'Semester End Examination Pattern: Part A (10×2=20), Part B (5×5=25), Part C (1×25=25)', 70, '[
  {
    "section_name": "Part A",
    "num_questions": 10,
    "marks_per_question": 2,
    "section_total": 20,
    "question_start_num": 1,
    "question_end_num": 10,
    "questions": [
      {"question_no": 1, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 1", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 2, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 2", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 3, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 3", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 4, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 4", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 5, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 5", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 6, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 6", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 7, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 7", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 8, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 8", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 9, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 9", "co_codes": "CO1", "po_codes": "PO1"},
      {"question_no": 10, "question_type": "numeric", "cognitive_level": "Remembering", "question_text": "Question 10", "co_codes": "CO1", "po_codes": "PO1"}
    ]
  },
  {
    "section_name": "Part B",
    "num_questions": 5,
    "marks_per_question": 5,
    "section_total": 25,
    "question_start_num": 11,
    "question_end_num": 15,
    "questions": [
      {"question_no": 11, "question_type": "numeric", "cognitive_level": "Understanding", "question_text": "Question 11", "co_codes": "CO1", "po_codes": "PO2,PO3"},
      {"question_no": 12, "question_type": "numeric", "cognitive_level": "Understanding", "question_text": "Question 12", "co_codes": "CO1", "po_codes": "PO2,PO3"},
      {"question_no": 13, "question_type": "numeric", "cognitive_level": "Understanding", "question_text": "Question 13", "co_codes": "CO1", "po_codes": "PO2,PO3"},
      {"question_no": 14, "question_type": "numeric", "cognitive_level": "Understanding", "question_text": "Question 14", "co_codes": "CO1", "po_codes": "PO2,PO3"},
      {"question_no": 15, "question_type": "numeric", "cognitive_level": "Understanding", "question_text": "Question 15", "co_codes": "CO1", "po_codes": "PO2,PO3"}
    ]
  },
  {
    "section_name": "Part C",
    "num_questions": 1,
    "marks_per_question": 25,
    "section_total": 25,
    "question_start_num": 16,
    "question_end_num": 16,
    "questions": [
      {"question_no": 16, "question_type": "numeric", "cognitive_level": "Analyzing", "question_text": "Question 16", "co_codes": "CO1", "po_codes": "PO4,PO5"}
    ]
  }
]', 1),

('Custom Template', 'Customizable template for flexible question patterns', 70, '[
  {
    "section_name": "Part A",
    "num_questions": 10,
    "marks_per_question": 2,
    "section_total": 20,
    "question_start_num": 1,
    "question_end_num": 10,
    "questions": []
  },
  {
    "section_name": "Part B",
    "num_questions": 10,
    "marks_per_question": 5,
    "section_total": 50,
    "question_start_num": 11,
    "question_end_num": 20,
    "questions": []
  }
]', 1);

-- Add foreign key constraint for created_by (if users table exists)
-- ALTER TABLE `question_pattern_templates` ADD CONSTRAINT `fk_template_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- Verify table creation
SELECT 'Table created successfully' as status;
SELECT COUNT(*) as template_count FROM question_pattern_templates;