-- SuperApp Database Schema
-- Tables required ONLY for missing SuperApp functionality
-- (Scholarships, Colleges, Schools, News already work via reverse proxy)

-- Study sessions table (for academic tools)
CREATE TABLE IF NOT EXISTS study_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    subject VARCHAR(255),
    duration_minutes INT DEFAULT 25,
    break_duration INT DEFAULT 5,
    actual_duration INT,
    status ENUM('active', 'completed', 'paused', 'cancelled') DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_status (user_id, status),
    INDEX idx_created_at (created_at)
);

-- User assignments table (for academic tools)
CREATE TABLE IF NOT EXISTS user_assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    subject VARCHAR(255),
    due_date DATE NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_status (user_id, status),
    INDEX idx_due_date (due_date),
    INDEX idx_priority (priority)
);

-- User tasks table (for productivity)
CREATE TABLE IF NOT EXISTS user_tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    due_date DATE NULL,
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_status (user_id, status),
    INDEX idx_due_date (due_date),
    INDEX idx_priority (priority)
);

-- Budget categories and expenses (for financial tools)
CREATE TABLE IF NOT EXISTS budget_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    icon VARCHAR(100),
    color VARCHAR(20),
    monthly_limit DECIMAL(10,2),
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
);

CREATE TABLE IF NOT EXISTS budget_expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    category_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description VARCHAR(255),
    expense_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES budget_categories(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, expense_date),
    INDEX idx_category_date (category_id, expense_date)
);

-- Dictionary cache (for study aids)
CREATE TABLE IF NOT EXISTS dictionary_cache (
    id INT AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(255) UNIQUE NOT NULL,
    phonetic VARCHAR(255),
    definitions JSON,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_word (word)
);

-- User preferences for SuperApp
CREATE TABLE IF NOT EXISTS user_superapp_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    preferred_currency VARCHAR(10) DEFAULT 'USD',
    default_study_duration INT DEFAULT 25,
    default_break_duration INT DEFAULT 5,
    notification_settings JSON,
    theme_preferences JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Insert some sample data for scholarships
INSERT INTO scholarships (title, slug, description, amount, currency, deadline, eligibility_criteria, organization, scholarship_type, category, is_active) VALUES
('Merit Excellence Scholarship', 'merit-excellence-scholarship', 'A scholarship awarded to students with outstanding academic performance and leadership qualities.', 5000.00, 'USD', '2024-12-31', 'Minimum 3.5 GPA, Leadership experience, Community service involvement', 'Education Foundation', 'merit', 'Academic Excellence', TRUE),

('Need-Based Financial Aid', 'need-based-financial-aid', 'Financial assistance for students from low-income families to pursue higher education.', 3000.00, 'USD', '2024-11-30', 'Family income below $40,000, Full-time enrollment, Academic good standing', 'Community Development Fund', 'need_based', 'Financial Aid', TRUE),

('STEM Innovation Grant', 'stem-innovation-grant', 'Supporting students pursuing degrees in Science, Technology, Engineering, and Mathematics.', 7500.00, 'USD', '2025-01-15', 'STEM field enrollment, Innovation project proposal, Minimum 3.0 GPA', 'Tech Innovation Institute', 'academic', 'STEM', TRUE),

('Women in Tech Scholarship', 'women-in-tech-scholarship', 'Empowering women to excel in technology and computer science fields.', 4000.00, 'USD', '2024-12-15', 'Female student, Computer Science/IT major, Essay submission', 'Women Tech Alliance', 'minority', 'Technology', TRUE),

('Athletic Excellence Award', 'athletic-excellence-award', 'Recognition and financial support for student-athletes with exceptional sports performance.', 6000.00, 'USD', '2025-02-28', 'Varsity team participation, Academic good standing, Coach recommendation', 'Sports Development Council', 'athletic', 'Sports', TRUE);

-- Insert default budget categories
INSERT INTO budget_categories (user_id, name, icon, color, is_default) VALUES
(0, 'Food & Dining', 'restaurant', '#FF6B6B', TRUE),
(0, 'Transportation', 'directions_car', '#4ECDC4', TRUE),
(0, 'Education', 'school', '#45B7D1', TRUE),
(0, 'Entertainment', 'movie', '#FFA07A', TRUE),
(0, 'Healthcare', 'local_hospital', '#98D8C8', TRUE),
(0, 'Shopping', 'shopping_cart', '#F7DC6F', TRUE),
(0, 'Bills & Utilities', 'receipt', '#BB8FCE', TRUE),
(0, 'Savings', 'savings', '#85C1E9', TRUE);
