CREATE DATABASE IF NOT EXISTS bodhitree_survey;
USE bodhitree_survey;

-- Table 1: mentors
CREATE TABLE IF NOT EXISTS mentors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    mentor_code VARCHAR(50) UNIQUE NOT NULL,
    mentor_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_mentor_code (mentor_code)
);

-- Table 2: survey_responses
CREATE TABLE IF NOT EXISTS survey_responses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    survey_type ENUM('student_6_10', 'student_11_12', 'parent_6_10', 'parent_11_12', 'teacher', 'school_admin') NOT NULL,
    mentor_id INT,
    device_fingerprint VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    -- User Info
    respondent_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    
    -- Survey Responses (JSON storage for flexibility)
    responses JSON NOT NULL,
    
    -- Completion Tracking
    completion_percentage INT DEFAULT 0,
    is_completed BOOLEAN DEFAULT FALSE,
    time_taken_seconds INT,
    
    -- Quality Flags
    is_bot_suspected BOOLEAN DEFAULT FALSE,
    is_duplicate BOOLEAN DEFAULT FALSE,
    quality_score DECIMAL(3,2) DEFAULT 1.00,
    
    -- Freebie Delivery
    freebie_delivered BOOLEAN DEFAULT FALSE,
    freebie_delivered_at TIMESTAMP NULL,
    
    -- WhatsApp Results
    whatsapp_results_sent BOOLEAN DEFAULT FALSE,
    whatsapp_sent_at TIMESTAMP NULL,
    
    -- Timestamps
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    last_activity_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (mentor_id) REFERENCES mentors(id) ON DELETE SET NULL,
    INDEX idx_survey_type (survey_type),
    INDEX idx_mentor_id (mentor_id),
    INDEX idx_completed (is_completed),
    INDEX idx_device_fingerprint (device_fingerprint)
);

-- Table 3: digital_freebies
CREATE TABLE IF NOT EXISTS digital_freebies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    freebie_type ENUM('student_6_10', 'student_11_12', 'parent_6_10', 'parent_11_12', 'teacher', 'school_admin') NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    file_path VARCHAR(255) NOT NULL,
    file_size_kb INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_freebie_type (freebie_type)
);

-- Table 4: survey_sessions
CREATE TABLE IF NOT EXISTS survey_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    session_token VARCHAR(64) UNIQUE NOT NULL,
    device_fingerprint VARCHAR(255),
    survey_type ENUM('student_6_10', 'student_11_12', 'parent_6_10', 'parent_11_12', 'teacher', 'school_admin'),
    mentor_id INT,
    current_question INT DEFAULT 1,
    responses_temp JSON,
    is_expired BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    FOREIGN KEY (mentor_id) REFERENCES mentors(id) ON DELETE SET NULL,
    INDEX idx_session_token (session_token),
    INDEX idx_device_fingerprint (device_fingerprint)
);

-- Table 5: analytics_metrics
CREATE TABLE IF NOT EXISTS analytics_metrics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    metric_date DATE NOT NULL,
    survey_type ENUM('student_6_10', 'student_11_12', 'parent_6_10', 'parent_11_12', 'teacher', 'school_admin'),
    mentor_id INT,
    
    -- Engagement Metrics
    total_started INT DEFAULT 0,
    total_completed INT DEFAULT 0,
    total_abandoned INT DEFAULT 0,
    avg_completion_time_seconds INT,
    avg_completion_percentage DECIMAL(5,2),
    
    -- Quality Metrics
    bot_suspected_count INT DEFAULT 0,
    duplicate_count INT DEFAULT 0,
    high_quality_responses INT DEFAULT 0,
    
    -- Conversion Metrics
    email_capture_rate DECIMAL(5,2),
    phone_capture_rate DECIMAL(5,2),
    freebie_download_rate DECIMAL(5,2),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (mentor_id) REFERENCES mentors(id) ON DELETE CASCADE,
    UNIQUE KEY unique_daily_metric (metric_date, survey_type, mentor_id),
    INDEX idx_metric_date (metric_date)
);

-- Table 6: admin_users
CREATE TABLE IF NOT EXISTS admin_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    email VARCHAR(100),
    role ENUM('super_admin', 'admin', 'viewer') DEFAULT 'admin',
    is_active BOOLEAN DEFAULT TRUE,
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username)
);

-- Insert a default admin user (password: admin123)
INSERT INTO admin_users (username, password_hash, full_name, email, role)
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'admin@bodhitree.com', 'super_admin');

-- Insert a default mentor
INSERT INTO mentors (mentor_code, mentor_name, email, phone)
VALUES ('MENTOR001', 'Test Mentor', 'mentor@bodhitree.com', '9876543210');
