-- ============================================================
-- Research Survey Management System - Sample Data
-- ============================================================

USE research_survey_system;

-- ============================================================
-- Admin User (password: admin123)
-- ============================================================
INSERT INTO users (username, email, password_hash, full_name, role, department, is_active) VALUES
('admin', 'admin@research.edu', '$2y$10$PE7jpkBtjjVSTy38/.v1t.dxYzRXjM3apDHUH/LhUGDwkAE4EQRA.', 'Dr. Sarah Johnson', 'admin', 'Research Department', 1),
('researcher1', 'researcher1@research.edu', '$2y$10$PE7jpkBtjjVSTy38/.v1t.dxYzRXjM3apDHUH/LhUGDwkAE4EQRA.', 'Prof. Michael Chen', 'researcher', 'Social Sciences', 1),
('researcher2', 'researcher2@research.edu', '$2y$10$PE7jpkBtjjVSTy38/.v1t.dxYzRXjM3apDHUH/LhUGDwkAE4EQRA.', 'Dr. Emily Williams', 'researcher', 'Psychology', 1),
('analyst1', 'analyst1@research.edu', '$2y$10$PE7jpkBtjjVSTy38/.v1t.dxYzRXjM3apDHUH/LhUGDwkAE4EQRA.', 'James Rodriguez', 'analyst', 'Data Analytics', 1),
('viewer1', 'viewer1@research.edu', '$2y$10$PE7jpkBtjjVSTy38/.v1t.dxYzRXjM3apDHUH/LhUGDwkAE4EQRA.', 'Lisa Thompson', 'viewer', 'Administration', 1);

-- ============================================================
-- Research Projects
-- ============================================================
INSERT INTO research_projects (project_name, description, created_by, status, start_date, end_date, methodology_notes) VALUES
('Customer Satisfaction Study 2026', 'Annual customer satisfaction survey across all product lines', 2, 'active', '2026-01-15', '2026-12-15', 'Mixed methods approach combining quantitative ratings with qualitative feedback'),
('Employee Engagement Survey', 'Bi-annual employee engagement and workplace culture assessment', 3, 'active', '2026-03-01', '2026-09-30', 'Anonymous survey with Likert scales and open-ended questions'),
('Healthcare Patient Experience', 'Patient satisfaction and experience survey for regional hospitals', 2, 'draft', '2026-06-01', '2026-11-30', 'Cross-sectional study using validated instruments'),
('Digital Transformation Impact', 'Research on digital transformation impact on business processes', 3, 'active', '2026-02-01', '2026-08-31', 'Longitudinal study with multiple measurement points');

-- ============================================================
-- Surveys
-- ============================================================
INSERT INTO surveys (project_id, title, description, survey_type, status, is_anonymous, requires_login, created_by, start_date, end_date, welcome_message, thank_you_message) VALUES
(1, 'Customer Satisfaction Q1 2026', 'First quarter customer satisfaction survey measuring product quality and service experience', 'quantitative', 'active', 1, 0, 2, '2026-01-15 00:00:00', '2026-04-15 00:00:00', 'Welcome to our Customer Satisfaction Survey. Your feedback helps us improve!', 'Thank you for your valuable feedback!'),
(1, 'Customer Feedback - Open Ended', 'In-depth qualitative feedback on customer experiences and suggestions', 'qualitative', 'active', 1, 0, 2, '2026-01-20 00:00:00', '2026-12-15 00:00:00', 'Tell us about your experience in your own words.', 'We appreciate your detailed feedback!'),
(2, 'Employee Engagement 2026', 'Comprehensive employee engagement survey', 'mixed', 'active', 1, 1, 3, '2026-03-01 00:00:00', '2026-04-30 00:00:00', 'This survey helps us understand how to make our workplace better.', 'Thank you for your honest feedback!'),
(3, 'Patient Experience Survey', 'Hospital patient experience and satisfaction measurement', 'quantitative', 'draft', 0, 0, 2, '2026-06-01 00:00:00', '2026-11-30 00:00:00', 'Your experience matters to us.', 'Thank you for helping us improve patient care.'),
(4, 'Digital Transformation Readiness', 'Assessment of organizational readiness for digital transformation', 'mixed', 'active', 0, 1, 3, '2026-02-01 00:00:00', '2026-08-31 00:00:00', 'This survey measures our digital transformation readiness.', 'Thank you for your participation!');

-- ============================================================
-- Survey Questions & Options
-- ============================================================

-- Survey 1: Customer Satisfaction Q1 2026
INSERT INTO survey_questions (survey_id, question_text, question_type, question_order, is_required, help_text) VALUES
(1, 'Overall, how satisfied are you with our product?', 'rating_scale', 1, 1, 'Rate from 1 (Very Dissatisfied) to 10 (Very Satisfied)'),
(1, 'How likely are you to recommend our product to others?', 'likert_scale', 2, 1, '0 = Not likely at all, 10 = Extremely likely'),
(1, 'Which aspects of our product do you find most valuable?', 'multiple_choice_multiple', 3, 0, 'Select all that apply'),
(1, 'How long have you been using our product?', 'dropdown', 4, 1, NULL),
(1, 'Please share any additional comments or suggestions:', 'text_long', 5, 0, NULL);

-- Options for Question 3 (Multiple Choice)
INSERT INTO question_options (question_id, option_text, option_value, option_order, score_value) VALUES
(3, 'Ease of Use', 'ease_of_use', 1, NULL),
(3, 'Customer Support', 'support', 2, NULL),
(3, 'Features & Functionality', 'features', 3, NULL),
(3, 'Value for Money', 'value', 4, NULL),
(3, 'Reliability', 'reliability', 5, NULL);

-- Options for Question 4 (Dropdown)
INSERT INTO question_options (question_id, option_text, option_value, option_order, score_value) VALUES
(4, 'Less than 1 month', '<1', 1, NULL),
(4, '1-6 months', '1-6', 2, NULL),
(4, '6-12 months', '6-12', 3, NULL),
(4, '1-2 years', '1-2', 4, NULL),
(4, 'More than 2 years', '>2', 5, NULL);

-- Survey 2: Customer Feedback - Open Ended
INSERT INTO survey_questions (survey_id, question_text, question_type, question_order, is_required) VALUES
(2, 'What do you like most about our product/service?', 'text_long', 1, 1),
(2, 'What improvements would you suggest?', 'text_long', 2, 1),
(2, 'Describe a specific experience where we exceeded your expectations:', 'rich_text', 3, 0),
(2, 'Upload any relevant files (screenshots, documents):', 'file_upload', 4, 0);

-- Survey 3: Employee Engagement 2026
INSERT INTO survey_questions (survey_id, question_text, question_type, question_order, is_required) VALUES
(3, 'I feel valued at my workplace', 'likert_scale', 1, 1),
(3, 'I have the resources I need to do my job effectively', 'likert_scale', 2, 1),
(3, 'My work gives me a sense of accomplishment', 'rating_scale', 3, 1),
(3, 'Which department do you work in?', 'dropdown', 4, 1),
(3, 'What would improve your job satisfaction the most?', 'text_long', 5, 0),
(3, 'Please upload any supporting documents:', 'file_upload', 6, 0);

-- Options for Question 4 (Department dropdown)
INSERT INTO question_options (question_id, option_text, option_value, option_order) VALUES
(10, 'Engineering', 'engineering', 1),
(10, 'Marketing', 'marketing', 2),
(10, 'Sales', 'sales', 3),
(10, 'Human Resources', 'hr', 4),
(10, 'Finance', 'finance', 5),
(10, 'Operations', 'operations', 6);

-- Survey 5: Digital Transformation Readiness
INSERT INTO survey_questions (survey_id, question_text, question_type, question_order, is_required) VALUES
(5, 'Rate your department''s digital maturity level:', 'rating_scale', 1, 1),
(5, 'Which digital tools do you currently use?', 'multiple_choice_multiple', 2, 1),
(5, 'What are the biggest barriers to digital adoption?', 'multiple_choice_multiple', 3, 1),
(5, 'How would you rate your digital skills?', 'likert_scale', 4, 1),
(5, 'Describe your vision for digital transformation:', 'text_long', 5, 1);

-- Options for Question 15 (Tools)
INSERT INTO question_options (question_id, option_text, option_value, option_order) VALUES
(15, 'Cloud Storage', 'cloud', 1),
(15, 'Project Management Software', 'pm_tools', 2),
(15, 'CRM System', 'crm', 3),
(15, 'Analytics Platforms', 'analytics', 4),
(15, 'Collaboration Tools', 'collab', 5);

-- Options for Question 16 (Barriers)
INSERT INTO question_options (question_id, option_text, option_value, option_order) VALUES
(16, 'Budget Constraints', 'budget', 1),
(16, 'Lack of Training', 'training', 2),
(16, 'Resistance to Change', 'resistance', 3),
(16, 'Legacy Systems', 'legacy', 4),
(16, 'Security Concerns', 'security', 5);

-- ============================================================
-- Sample Participants
-- ============================================================
INSERT INTO survey_participants (survey_id, email, status, invited_at, completed_at, time_spent_seconds) VALUES
(1, 'customer1@email.com', 'completed', '2026-01-20 10:00:00', '2026-01-20 10:15:00', 900),
(1, 'customer2@email.com', 'completed', '2026-01-21 14:00:00', '2026-01-21 14:20:00', 1200),
(1, 'customer3@email.com', 'started', '2026-01-22 09:00:00', NULL, NULL),
(1, 'customer4@email.com', 'completed', '2026-01-23 11:00:00', '2026-01-23 11:18:00', 1080),
(1, 'customer5@email.com', 'invited', '2026-01-24 08:00:00', NULL, NULL),
(3, 'employee1@company.com', 'completed', '2026-03-01 09:00:00', '2026-03-01 09:25:00', 1500),
(3, 'employee2@company.com', 'completed', '2026-03-02 10:00:00', '2026-03-02 10:22:00', 1320),
(3, 'employee3@company.com', 'partial', '2026-03-03 11:00:00', NULL, NULL);

-- ============================================================
-- Sample Responses
-- ============================================================
INSERT INTO survey_responses (survey_id, participant_id, status, started_at, completed_at, time_spent_seconds) VALUES
(1, 1, 'completed', '2026-01-20 10:00:00', '2026-01-20 10:15:00', 900),
(1, 2, 'completed', '2026-01-21 14:00:00', '2026-01-21 14:20:00', 1200),
(1, 4, 'completed', '2026-01-23 11:00:00', '2026-01-23 11:18:00', 1080),
(3, 6, 'completed', '2026-03-01 09:00:00', '2026-03-01 09:25:00', 1500),
(3, 7, 'completed', '2026-03-02 10:00:00', '2026-03-02 10:22:00', 1320);

-- Question Responses for Survey 1
INSERT INTO question_responses (response_id, question_id, option_id, response_value, response_numeric) VALUES
-- Response 1: Customer 1
(1, 1, NULL, '8', 8),
(1, 2, NULL, '9', 9),
(1, 3, 1, 'ease_of_use', NULL),
(1, 3, 3, 'features', NULL),
(1, 4, 15, '1-2 years', NULL),
(1, 5, NULL, 'Great product! Very intuitive interface and excellent customer support.', NULL),
-- Response 2: Customer 2
(2, 1, NULL, '7', 7),
(2, 2, NULL, '8', 8),
(2, 3, 2, 'support', NULL),
(2, 3, 4, 'value', NULL),
(2, 4, 16, '>2 years', NULL),
(2, 5, NULL, 'Good product overall. Would like to see more integration options.', NULL),
-- Response 3: Customer 4
(3, 1, NULL, '9', 9),
(3, 2, NULL, '10', 10),
(3, 3, 1, 'ease_of_use', NULL),
(3, 3, 3, 'features', NULL),
(3, 3, 5, 'reliability', NULL),
(3, 4, 15, '1-2 years', NULL),
(3, 5, NULL, 'Excellent product! The recent updates have been fantastic.', NULL);

-- Question Responses for Survey 3 (Employee Engagement)
INSERT INTO question_responses (response_id, question_id, response_value, response_numeric) VALUES
(4, 6, '4', 4),
(4, 7, '5', 5),
(4, 8, '8', 8),
(4, 11, 'Better career development opportunities and more flexible working hours.', NULL),
(5, 6, '3', 3),
(5, 7, '4', 4),
(5, 8, '7', 7),
(5, 11, 'More team building activities and better communication from management.', NULL);

-- Employee department selections
INSERT INTO question_responses (response_id, question_id, option_id, response_value) VALUES
(4, 10, 10, 'Engineering'),
(5, 10, 11, 'Marketing');

-- ============================================================
-- Sample Analytics Data
-- ============================================================
INSERT INTO survey_analytics (survey_id, total_responses, completion_rate, average_time_spent, total_views, total_invitations_sent, last_response_date, analytics_data) VALUES
(1, 3, 60.00, 1060, 50, 5, '2026-01-23 11:18:00', JSON_OBJECT('completed', 3, 'in_progress', 1, 'disqualified', 0)),
(3, 2, 66.67, 1410, 30, 3, '2026-03-02 10:22:00', JSON_OBJECT('completed', 2, 'in_progress', 1, 'disqualified', 0));

-- Sample Theme Codes
INSERT INTO thematic_codes (project_id, code_name, code_description, color_code, created_by) VALUES
(1, 'Product Quality', 'References to product quality and performance', '#2ecc71', 2),
(1, 'Customer Support', 'Mentions of support team interactions', '#3498db', 2),
(1, 'Pricing', 'Comments about pricing and value', '#e74c3c', 2),
(2, 'Workplace Culture', 'References to company culture and environment', '#9b59b6', 3),
(2, 'Career Growth', 'Mentions of professional development', '#f39c12', 3),
(2, 'Compensation', 'Comments about salary and benefits', '#e67e22', 3);

-- ============================================================
-- Project Members
-- ============================================================
INSERT INTO project_members (project_id, user_id, role_in_project, joined_at) VALUES
(1, 2, 'owner', NOW()),
(1, 4, 'analyst', NOW()),
(1, 5, 'viewer', NOW()),
(2, 3, 'owner', NOW()),
(2, 4, 'analyst', NOW()),
(3, 2, 'owner', NOW()),
(4, 3, 'owner', NOW()),
(4, 4, 'researcher', NOW());