-- ============================================================
-- Research Survey Management System - Database Schema
-- Database: research_survey_system
-- ============================================================

CREATE DATABASE IF NOT EXISTS research_survey_system
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE research_survey_system;

-- ============================================================
-- 1. Users & Authentication
-- ============================================================

CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'researcher', 'analyst', 'viewer') NOT NULL DEFAULT 'viewer',
    department VARCHAR(100) DEFAULT NULL,
    avatar_url VARCHAR(255) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_role (role),
    INDEX idx_users_email (email),
    INDEX idx_users_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_sessions (
    session_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(255) NOT NULL UNIQUE,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    last_activity DATETIME DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_sessions_token (session_token),
    INDEX idx_sessions_user (user_id),
    INDEX idx_sessions_expires (expires_at),
    INDEX idx_sessions_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS research_projects (
    project_id INT AUTO_INCREMENT PRIMARY KEY,
    project_name VARCHAR(200) NOT NULL,
    description TEXT DEFAULT NULL,
    created_by INT NOT NULL,
    status ENUM('active', 'completed', 'archived', 'draft') NOT NULL DEFAULT 'draft',
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    methodology_notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_projects_status (status),
    INDEX idx_projects_creator (created_by),
    FULLTEXT INDEX ft_projects (project_name, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_members (
    project_member_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    user_id INT NOT NULL,
    role_in_project ENUM('owner', 'researcher', 'analyst', 'viewer') NOT NULL DEFAULT 'viewer',
    invited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    joined_at DATETIME DEFAULT NULL,
    FOREIGN KEY (project_id) REFERENCES research_projects(project_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    UNIQUE INDEX idx_project_member_unique (project_id, user_id),
    INDEX idx_pm_project (project_id),
    INDEX idx_pm_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. Survey Management
-- ============================================================

CREATE TABLE IF NOT EXISTS surveys (
    survey_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT DEFAULT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    survey_type ENUM('quantitative', 'qualitative', 'mixed') NOT NULL DEFAULT 'quantitative',
    status ENUM('draft', 'active', 'closed', 'archived') NOT NULL DEFAULT 'draft',
    is_anonymous TINYINT(1) NOT NULL DEFAULT 0,
    allow_multiple_submissions TINYINT(1) NOT NULL DEFAULT 0,
    requires_login TINYINT(1) NOT NULL DEFAULT 1,
    max_responses INT DEFAULT NULL,
    time_limit_minutes INT DEFAULT NULL,
    welcome_message TEXT DEFAULT NULL,
    thank_you_message TEXT DEFAULT NULL,
    created_by INT NOT NULL,
    start_date DATETIME DEFAULT NULL,
    end_date DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES research_projects(project_id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_surveys_status (status),
    INDEX idx_surveys_project (project_id),
    INDEX idx_surveys_creator (created_by),
    INDEX idx_surveys_type (survey_type),
    FULLTEXT INDEX ft_surveys (title, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS survey_questions (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    question_text TEXT NOT NULL,
    question_type ENUM(
        'rating_scale', 'likert_scale', 'multiple_choice_single',
        'multiple_choice_multiple', 'dropdown', 'ranking',
        'yes_no', 'date', 'time', 'numerical',
        'text_short', 'text_long', 'rich_text',
        'file_upload', 'matrix_single', 'matrix_multiple'
    ) NOT NULL,
    question_order INT NOT NULL DEFAULT 0,
    is_required TINYINT(1) NOT NULL DEFAULT 1,
    validation_rules JSON DEFAULT NULL,
    settings JSON DEFAULT NULL,
    weight DECIMAL(5,2) DEFAULT NULL,
    help_text TEXT DEFAULT NULL,
    randomize_options TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    INDEX idx_questions_survey (survey_id),
    INDEX idx_questions_order (survey_id, question_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS question_options (
    option_id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    option_text VARCHAR(500) NOT NULL,
    option_value VARCHAR(100) DEFAULT NULL,
    option_order INT NOT NULL DEFAULT 0,
    score_value DECIMAL(5,2) DEFAULT NULL,
    is_na_option TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES survey_questions(question_id) ON DELETE CASCADE,
    INDEX idx_options_question (question_id),
    INDEX idx_options_order (question_id, option_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS survey_participants (
    participant_id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    user_id INT DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    anonymous_identifier VARCHAR(100) DEFAULT NULL,
    status ENUM('invited', 'started', 'completed', 'partial', 'disqualified') NOT NULL DEFAULT 'invited',
    invited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME DEFAULT NULL,
    completed_at DATETIME DEFAULT NULL,
    time_spent_seconds INT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_participants_survey (survey_id),
    INDEX idx_participants_status (status),
    INDEX idx_participants_user (user_id),
    INDEX idx_participants_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. Data Collection & Analytics
-- ============================================================

CREATE TABLE IF NOT EXISTS survey_responses (
    response_id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    participant_id INT DEFAULT NULL,
    session_token VARCHAR(255) DEFAULT NULL,
    status ENUM('in_progress', 'completed', 'disqualified', 'expired') NOT NULL DEFAULT 'in_progress',
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME DEFAULT NULL,
    time_spent_seconds INT DEFAULT NULL,
    is_flagged TINYINT(1) NOT NULL DEFAULT 0,
    flag_reason TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    FOREIGN KEY (participant_id) REFERENCES survey_participants(participant_id) ON DELETE SET NULL,
    INDEX idx_responses_survey (survey_id),
    INDEX idx_responses_status (status),
    INDEX idx_responses_participant (participant_id),
    INDEX idx_responses_completed (completed_at),
    INDEX idx_responses_flagged (is_flagged)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS question_responses (
    question_response_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    response_id INT NOT NULL,
    question_id INT NOT NULL,
    option_id INT DEFAULT NULL,
    response_value TEXT DEFAULT NULL,
    response_numeric DECIMAL(15,5) DEFAULT NULL,
    response_date DATETIME DEFAULT NULL,
    file_url VARCHAR(500) DEFAULT NULL,
    file_type VARCHAR(50) DEFAULT NULL,
    sentiment_score DECIMAL(5,2) DEFAULT NULL,
    time_spent_seconds INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (response_id) REFERENCES survey_responses(response_id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES survey_questions(question_id) ON DELETE CASCADE,
    FOREIGN KEY (option_id) REFERENCES question_options(option_id) ON DELETE SET NULL,
    INDEX idx_qr_response (response_id),
    INDEX idx_qr_question (question_id),
    INDEX idx_qr_option (option_id),
    INDEX idx_qr_numeric (response_numeric),
    FULLTEXT INDEX ft_qr_value (response_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS survey_analytics (
    analytics_id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    total_responses INT NOT NULL DEFAULT 0,
    completion_rate DECIMAL(5,2) DEFAULT 0.00,
    average_time_spent INT DEFAULT 0,
    total_views INT NOT NULL DEFAULT 0,
    total_invitations_sent INT NOT NULL DEFAULT 0,
    last_response_date DATETIME DEFAULT NULL,
    analytics_data JSON DEFAULT NULL,
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    UNIQUE INDEX idx_analytics_survey (survey_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS response_analytics (
    response_analytics_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    analytics_type ENUM(
        'frequency', 'mean', 'median', 'mode', 'std_deviation',
        'variance', 'range', 'correlation', 'cross_tabulation',
        'nps', 'csat', 'sentiment', 'keyword_freq', 'theme_count'
    ) NOT NULL,
    analytics_data JSON NOT NULL,
    sample_size INT NOT NULL DEFAULT 0,
    confidence_interval DECIMAL(5,2) DEFAULT NULL,
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES survey_questions(question_id) ON DELETE CASCADE,
    INDEX idx_ra_question (question_id),
    INDEX idx_ra_type (analytics_type),
    INDEX idx_ra_calculated (calculated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. Qualitative Analysis Tables
-- ============================================================

CREATE TABLE IF NOT EXISTS thematic_codes (
    code_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT DEFAULT NULL,
    survey_id INT DEFAULT NULL,
    code_name VARCHAR(200) NOT NULL,
    code_description TEXT DEFAULT NULL,
    color_code VARCHAR(7) DEFAULT '#3498db',
    parent_code_id INT DEFAULT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES research_projects(project_id) ON DELETE CASCADE,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    FOREIGN KEY (parent_code_id) REFERENCES thematic_codes(code_id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_codes_project (project_id),
    INDEX idx_codes_survey (survey_id),
    INDEX idx_codes_parent (parent_code_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS coding_assignments (
    assignment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    question_response_id BIGINT NOT NULL,
    code_id INT NOT NULL,
    coder_id INT NOT NULL,
    confidence_score DECIMAL(3,2) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    is_auto_generated TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_response_id) REFERENCES question_responses(question_response_id) ON DELETE CASCADE,
    FOREIGN KEY (code_id) REFERENCES thematic_codes(code_id) ON DELETE CASCADE,
    FOREIGN KEY (coder_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_ca_response (question_response_id),
    INDEX idx_ca_code (code_id),
    INDEX idx_ca_coder (coder_id),
    UNIQUE INDEX idx_ca_unique (question_response_id, code_id, coder_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. Audit & Error Logging
-- ============================================================

CREATE TABLE IF NOT EXISTS audit_log (
    log_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    action_type VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT DEFAULT NULL,
    old_value JSON DEFAULT NULL,
    new_value JSON DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_action (action_type),
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. System Events
-- ============================================================

CREATE TABLE IF NOT EXISTS system_events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    event_type ENUM('scheduled', 'manual', 'triggered') NOT NULL DEFAULT 'scheduled',
    status ENUM('pending', 'running', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    started_at DATETIME DEFAULT NULL,
    completed_at DATETIME DEFAULT NULL,
    result_data JSON DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_events_status (status),
    INDEX idx_events_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;