dummy_data.sql

-- MySQL Dummy Data for Exam Checker System
-- Run these commands in MySQL to populate the database with test data

-- Create database if it doesn't exist
CREATE DATABASE IF NOT EXISTS ExamChecker;
USE ExamChecker;

-- Insert Users
INSERT INTO Users (Id, Username, Email, FullName, UserType, PasswordHash, IsActive, CreatedAt, UpdatedAt) VALUES
('user1', 'examiner1', 'examiner1@example.com', 'John Examiner', 'examiner', 'hashed_password_1', 1, NOW(), NOW()),
('user2', 'moderator1', 'moderator1@example.com', 'Jane Moderator', 'moderator', 'hashed_password_2', 1, NOW(), NOW()),
('user3', 'admin1', 'admin1@example.com', 'Admin User', 'admin', 'hashed_password_3', 1, NOW(), NOW());

-- Insert Exam Configurations
INSERT INTO ExamConfigurations (ExamCode, ExamName, Subject, TotalQuestions, TotalExamMarks, BestOfN, MarkingScheme, CreatedAt, UpdatedAt) VALUES
('ENG-101', 'English Literature', 'English', 26, 100, 3, '{"passingMarks": 40, "negativeMarking": false, "timeLimit": 180}', NOW(), NOW()),
('MAT-201', 'Mathematics', 'Mathematics', 26, 100, 3, '{"passingMarks": 40, "negativeMarking": true, "timeLimit": 180}', NOW(), NOW()),
('PHY-301', 'Physics', 'Physics', 26, 100, 3, '{"passingMarks": 40, "negativeMarking": false, "timeLimit": 180}', NOW(), NOW()),
('CHE-401', 'Chemistry', 'Chemistry', 26, 100, 3, '{"passingMarks": 40, "negativeMarking": false, "timeLimit": 180}', NOW(), NOW()),
('BIO-102', 'Biology', 'Biology', 26, 100, 3, '{"passingMarks": 40, "negativeMarking": false, "timeLimit": 180}', NOW(), NOW());

-- Insert Answer Sheets
INSERT INTO AnswerSheets (Id, Name, PaperCode, Code, Url, TotalPages, Status, ExaminerId, ModeratorId, Subject, MaxMarks, MarksObtained, CreatedAt, UpdatedAt) VALUES
('pdf1', 'English Paper - Student 1', 'ENG-101', 'C001', 'https://www.nextias.com/newuploads/Nextias/2025/4/shakti-dubey-essay-1-booklet-1745331781333.pdf', 10, 'pending', 'user1', NULL, 'English', 100, 0, NOW(), NOW()),
('pdf2', 'Mathematics Paper - Student 2', 'MAT-201', 'C002', 'https://www.nextias.com/newuploads/Nextias/2025/4/shakti-dubey-essay-1-booklet-1745331781333.pdf', 10, 'pending', 'user1', NULL, 'Mathematics', 100, 0, NOW(), NOW()),
('pdf3', 'Physics Paper - Student 3', 'PHY-301', 'C003', 'https://www.nextias.com/newuploads/Nextias/2025/4/shakti-dubey-essay-1-booklet-1745331781333.pdf', 10, 'checked', 'user1', 'user2', 'Physics', 100, 85, NOW(), NOW()),
('pdf4', 'Chemistry Paper - Student 4', 'CHE-401', 'C004', 'https://www.nextias.com/newuploads/Nextias/2025/4/shakti-dubey-essay-1-booklet-1745331781333.pdf', 10, 'moderated', 'user1', 'user2', 'Chemistry', 100, 92, NOW(), NOW()),
('pdf5', 'Biology Paper - Student 5', 'BIO-102', 'C005', 'https://www.nextias.com/newuploads/Nextias/2025/4/shakti-dubey-essay-1-booklet-1745331781333.pdf', 10, 'pending', 'user1', NULL, 'Biology', 100, 0, NOW(), NOW());

-- Insert Answer Sheet Pages
INSERT INTO AnswerSheetPages (AnswerSheetId, PageNumber, IsBlank, HasDrawings, HasMarks, HasText, CreatedAt, UpdatedAt) VALUES
-- PDF1 Pages
('pdf1', 1, 0, 0, 1, 0, NOW(), NOW()),
('pdf1', 2, 0, 1, 0, 1, NOW(), NOW()),
('pdf1', 3, 0, 0, 1, 0, NOW(), NOW()),
('pdf1', 4, 0, 1, 0, 1, NOW(), NOW()),
('pdf1', 5, 0, 0, 1, 0, NOW(), NOW()),
('pdf1', 6, 0, 1, 0, 1, NOW(), NOW()),
('pdf1', 7, 0, 0, 1, 0, NOW(), NOW()),
('pdf1', 8, 0, 1, 0, 1, NOW(), NOW()),
('pdf1', 9, 0, 0, 1, 0, NOW(), NOW()),
('pdf1', 10, 0, 1, 0, 1, NOW(), NOW()),
-- PDF2 Pages
('pdf2', 1, 0, 0, 1, 0, NOW(), NOW()),
('pdf2', 2, 0, 1, 0, 1, NOW(), NOW()),
('pdf2', 3, 0, 0, 1, 0, NOW(), NOW()),
('pdf2', 4, 0, 1, 0, 1, NOW(), NOW()),
('pdf2', 5, 0, 0, 1, 0, NOW(), NOW()),
('pdf2', 6, 0, 1, 0, 1, NOW(), NOW()),
('pdf2', 7, 0, 0, 1, 0, NOW(), NOW()),
('pdf2', 8, 0, 1, 0, 1, NOW(), NOW()),
('pdf2', 9, 0, 0, 1, 0, NOW(), NOW()),
('pdf2', 10, 0, 1, 0, 1, NOW(), NOW()),
-- PDF3 Pages
('pdf3', 1, 0, 0, 1, 0, NOW(), NOW()),
('pdf3', 2, 0, 1, 0, 1, NOW(), NOW()),
('pdf3', 3, 0, 0, 1, 0, NOW(), NOW()),
('pdf3', 4, 0, 1, 0, 1, NOW(), NOW()),
('pdf3', 5, 0, 0, 1, 0, NOW(), NOW()),
('pdf3', 6, 0, 1, 0, 1, NOW(), NOW()),
('pdf3', 7, 0, 0, 1, 0, NOW(), NOW()),
('pdf3', 8, 0, 1, 0, 1, NOW(), NOW()),
('pdf3', 9, 0, 0, 1, 0, NOW(), NOW()),
('pdf3', 10, 0, 1, 0, 1, NOW(), NOW()),
-- PDF4 Pages
('pdf4', 1, 0, 0, 1, 0, NOW(), NOW()),
('pdf4', 2, 0, 1, 0, 1, NOW(), NOW()),
('pdf4', 3, 0, 0, 1, 0, NOW(), NOW()),
('pdf4', 4, 0, 1, 0, 1, NOW(), NOW()),
('pdf4', 5, 0, 0, 1, 0, NOW(), NOW()),
('pdf4', 6, 0, 1, 0, 1, NOW(), NOW()),
('pdf4', 7, 0, 0, 1, 0, NOW(), NOW()),
('pdf4', 8, 0, 1, 0, 1, NOW(), NOW()),
('pdf4', 9, 0, 0, 1, 0, NOW(), NOW()),
('pdf4', 10, 0, 1, 0, 1, NOW(), NOW()),
-- PDF5 Pages
('pdf5', 1, 0, 0, 1, 0, NOW(), NOW()),
('pdf5', 2, 0, 1, 0, 1, NOW(), NOW()),
('pdf5', 3, 0, 0, 1, 0, NOW(), NOW()),
('pdf5', 4, 0, 1, 0, 1, NOW(), NOW()),
('pdf5', 5, 0, 0, 1, 0, NOW(), NOW()),
('pdf5', 6, 0, 1, 0, 1, NOW(), NOW()),
('pdf5', 7, 0, 0, 1, 0, NOW(), NOW()),
('pdf5', 8, 0, 1, 0, 1, NOW(), NOW()),
('pdf5', 9, 0, 0, 1, 0, NOW(), NOW()),
('pdf5', 10, 0, 1, 0, 1, NOW(), NOW());

-- Insert Page Overlays (sample data for first few pages)
INSERT INTO PageOverlays (AnswerSheetId, PageNumber, OverlayData, Version, CreatedAt, UpdatedAt) VALUES
('pdf1', 1, '{"paths": [{"type": "path", "d": "M10,10 L20,20", "strokeWidth": 2}], "placedMarks": [{"type": "number", "x": 30, "y": 30, "question": 1, "value": 2, "sign": "+"}], "textInputs": [], "blank": false}', 1, NOW(), NOW()),
('pdf1', 2, '{"paths": [{"type": "oval", "x1": 15, "y1": 15, "x2": 25, "y2": 25, "strokeWidth": 2}], "placedMarks": [{"type": "tick", "x": 40, "y": 40, "question": 2}], "textInputs": [{"x": 50, "y": 50, "value": "Good work", "fontSize": 12}], "blank": false}', 1, NOW(), NOW()),
('pdf2', 1, '{"paths": [{"type": "rect", "x1": 10, "y1": 10, "x2": 30, "y2": 30, "strokeWidth": 2}], "placedMarks": [{"type": "number", "x": 35, "y": 35, "question": 1, "value": 8, "sign": "+"}], "textInputs": [], "blank": false}', 1, NOW(), NOW()),
('pdf3', 1, '{"paths": [{"type": "line", "x1": 10, "y1": 10, "x2": 40, "y2": 40, "strokeWidth": 2}], "placedMarks": [{"type": "number", "x": 45, "y": 45, "question": 1, "value": 10, "sign": "+"}], "textInputs": [{"x": 60, "y": 60, "value": "Excellent", "fontSize": 12}], "blank": false}', 1, NOW(), NOW());

-- Insert Question Marks Summary
INSERT INTO QuestionMarksSummary (AnswerSheetId, QuestionNumber, TotalMarks, MaxMarks, MarksObtained, IsSubQuestion, ExaminerTotal, ModeratorTotal, CreatedAt, UpdatedAt) VALUES
-- PDF1 Questions
('pdf1', '1', 12, 12, 8, 0, 8, 8, NOW(), NOW()),
('pdf1', '2', 12, 12, 6, 0, 6, 6, NOW(), NOW()),
('pdf1', '3', 12, 12, 9, 0, 9, 9, NOW(), NOW()),
('pdf1', '4', 12, 12, 7, 0, 7, 7, NOW(), NOW()),
('pdf1', '5', 12, 12, 10, 0, 10, 10, NOW(), NOW()),
-- PDF2 Questions
('pdf2', '1', 12, 12, 5, 0, 5, 5, NOW(), NOW()),
('pdf2', '2', 12, 12, 8, 0, 8, 8, NOW(), NOW()),
('pdf2', '3', 12, 12, 6, 0, 6, 6, NOW(), NOW()),
('pdf2', '4', 12, 12, 9, 0, 9, 9, NOW(), NOW()),
('pdf2', '5', 12, 12, 7, 0, 7, 7, NOW(), NOW()),
-- PDF3 Questions (checked)
('pdf3', '1', 12, 12, 11, 0, 11, 11, NOW(), NOW()),
('pdf3', '2', 12, 12, 9, 0, 9, 9, NOW(), NOW()),
('pdf3', '3', 12, 12, 10, 0, 10, 10, NOW(), NOW()),
('pdf3', '4', 12, 12, 8, 0, 8, 8, NOW(), NOW()),
('pdf3', '5', 12, 12, 12, 0, 12, 12, NOW(), NOW()),
-- PDF4 Questions (moderated)
('pdf4', '1', 12, 12, 12, 0, 12, 12, NOW(), NOW()),
('pdf4', '2', 12, 12, 11, 0, 11, 11, NOW(), NOW()),
('pdf4', '3', 12, 12, 10, 0, 10, 10, NOW(), NOW()),
('pdf4', '4', 12, 12, 9, 0, 9, 9, NOW(), NOW()),
('pdf4', '5', 12, 12, 11, 0, 11, 11, NOW(), NOW());

-- Insert Exam Questions
INSERT INTO ExamQuestions (ExamConfigurationId, QuestionNumber, QuestionText, MaxMarks, QuestionType, IsSubQuestion, CreatedAt, UpdatedAt) VALUES
(1, '1', 'Question 1: Explain the theme of love in Shakespeare\'s sonnets', 12, 'essay', 0, NOW(), NOW()),
(1, '2', 'Question 2: Analyze the character development in Macbeth', 12, 'essay', 0, NOW(), NOW()),
(1, '3', 'Question 3: What is the significance of the green light in The Great Gatsby?', 12, 'essay', 0, NOW(), NOW()),
(1, '4', 'Question 4: Compare and contrast two poems from the syllabus', 12, 'essay', 0, NOW(), NOW()),
(1, '5', 'Question 5: Discuss the role of symbolism in modern literature', 12, 'essay', 0, NOW(), NOW());

-- Insert Answer Keys
INSERT INTO AnswerKeys (ExamConfigurationId, QuestionNumber, AnswerText, MarkingPoints, SampleAnswers, Keywords, MaxMarks, CreatedAt, UpdatedAt) VALUES
(1, '1', 'The theme of love in Shakespeare\'s sonnets explores various aspects of romantic love, including its beauty, transience, and power to immortalize the beloved.', '{"points": ["Introduction (2 marks)", "Theme analysis (4 marks)", "Examples from sonnets (4 marks)", "Conclusion (2 marks)"]}', '{"examples": ["Sonnet 18: Shall I compare thee to a summer\'s day", "Sonnet 116: Let me not to the marriage of true minds"]}', 'love, theme, sonnets, Shakespeare, romantic, immortalize', 12, NOW(), NOW()),
(1, '2', 'Macbeth\'s character development shows a tragic fall from noble warrior to tyrannical murderer, driven by ambition and the influence of Lady Macbeth.', '{"points": ["Initial state (2 marks)", "Transformation (4 marks)", "Tragic flaw (3 marks)", "Final state (3 marks)"]}', '{"examples": ["Act 1: Noble warrior", "Act 2: First murder", "Act 5: Tyrant and downfall"]}', 'Macbeth, character, development, tragedy, ambition, downfall', 12, NOW(), NOW());

-- Insert Question Marks Config
INSERT INTO QuestionMarksConfig (ExamConfigurationId, QuestionNumber, MaxMarks, PassingMarks, NegativeMarking, NegativeMarks, IsCompulsory, QuestionWeight, CreatedAt, UpdatedAt) VALUES
(1, '1', 12, 6, 0, 0, 1, 1.00, NOW(), NOW()),
(1, '2', 12, 6, 0, 0, 1, 1.00, NOW(), NOW()),
(1, '3', 12, 6, 0, 0, 1, 1.00, NOW(), NOW()),
(1, '4', 12, 6, 0, 0, 1, 1.00, NOW(), NOW()),
(1, '5', 12, 6, 0, 0, 1, 1.00, NOW(), NOW());

-- Insert Answer Sheet Assignments
INSERT INTO AnswerSheetAssignments (AnswerSheetId, UserId, AssignmentType, AssignedAt, Status, Notes) VALUES
('pdf1', 'user1', 'examiner', NOW(), 'assigned', 'English paper assignment'),
('pdf2', 'user1', 'examiner', NOW(), 'assigned', 'Mathematics paper assignment'),
('pdf3', 'user1', 'examiner', NOW(), 'completed', 'Physics paper - completed'),
('pdf3', 'user2', 'moderator', NOW(), 'completed', 'Physics paper - moderated'),
('pdf4', 'user1', 'examiner', NOW(), 'completed', 'Chemistry paper - completed'),
('pdf4', 'user2', 'moderator', NOW(), 'completed', 'Chemistry paper - moderated'),
('pdf5', 'user1', 'examiner', NOW(), 'assigned', 'Biology paper assignment');

-- Insert Audit Logs
INSERT INTO AuditLogs (AnswerSheetId, PageNumber, UserId, UserType, Action, ActionDetails, Timestamp, IpAddress, UserAgent) VALUES
('pdf1', 1, 'user1', 'examiner', 'view', '{"action": "page_viewed", "page": 1}', NOW(), '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'),
('pdf1', 1, 'user1', 'examiner', 'mark', '{"action": "mark_placed", "question": 1, "marks": 8}', NOW(), '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'),
('pdf3', 1, 'user1', 'examiner', 'complete', '{"action": "paper_completed", "total_marks": 85}', NOW(), '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'),
('pdf3', 1, 'user2', 'moderator', 'moderate', '{"action": "paper_moderated", "total_marks": 85}', NOW(), '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36');

-- Insert User Sessions
INSERT INTO UserSessions (UserId, SessionToken, DeviceInfo, IpAddress, UserAgent, LoginTime, LastActivity, IsActive) VALUES
('user1', 'session_token_1', '{"device": "Windows", "browser": "Chrome"}', '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36', NOW(), NOW(), 1),
('user2', 'session_token_2', '{"device": "Mac", "browser": "Safari"}', '127.0.0.1', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36', NOW(), NOW(), 1);

-- Show summary of inserted data
SELECT 'Users' as TableName, COUNT(*) as RecordCount FROM Users
UNION ALL
SELECT 'AnswerSheets', COUNT(*) FROM AnswerSheets
UNION ALL
SELECT 'AnswerSheetPages', COUNT(*) FROM AnswerSheetPages
UNION ALL
SELECT 'PageOverlays', COUNT(*) FROM PageOverlays
UNION ALL
SELECT 'QuestionMarksSummary', COUNT(*) FROM QuestionMarksSummary
UNION ALL
SELECT 'ExamQuestions', COUNT(*) FROM ExamQuestions
UNION ALL
SELECT 'AnswerKeys', COUNT(*) FROM AnswerKeys
UNION ALL
SELECT 'QuestionMarksConfig', COUNT(*) FROM QuestionMarksConfig
UNION ALL
SELECT 'AnswerSheetAssignments', COUNT(*) FROM AnswerSheetAssignments
UNION ALL
SELECT 'AuditLogs', COUNT(*) FROM AuditLogs
UNION ALL
SELECT 'UserSessions', COUNT(*) FROM UserSessions;