-- =====================================================
-- Ankisha Typing Master - MySQL Database Setup
-- =====================================================

-- Create Database
CREATE DATABASE IF NOT EXISTS ankisha_typing_master CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create User (Change password as needed)
CREATE USER IF NOT EXISTS 'ankisha_user'@'localhost' IDENTIFIED BY 'Ankisha@2025#Secure';
GRANT ALL PRIVILEGES ON ankisha_typing_master.* TO 'ankisha_user'@'localhost';
FLUSH PRIVILEGES;

-- Use the database
USE ankisha_typing_master;

-- =====================================================
-- Table: users
-- Stores both student and admin accounts
-- =====================================================
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('student', 'admin') DEFAULT 'student',
    photo LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Table: typing_results
-- Stores all typing test results (practice and final)
-- =====================================================
CREATE TABLE IF NOT EXISTS typing_results (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    language VARCHAR(50) NOT NULL,
    font VARCHAR(50) NOT NULL,
    mode ENUM('practice', 'final') NOT NULL,
    time_duration INT NOT NULL,
    text_typed LONGTEXT NOT NULL,
    speed_wpm DECIMAL(10, 2) NOT NULL,
    accuracy DECIMAL(10, 2) NOT NULL,
    errors INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_mode (mode),
    INDEX idx_speed_wpm (speed_wpm),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Table: settings
-- Stores application settings like institute logo
-- =====================================================
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value LONGTEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Insert Default Admin Account
-- Email: admin@ankisha.com
-- Password: admin123
-- =====================================================
INSERT INTO users (id, name, email, password, role, created_at) 
VALUES (
    UUID(),
    'Admin',
    'admin@ankisha.com',
    '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5NU7DCBJ.Z6re',
    'admin',
    NOW()
) ON DUPLICATE KEY UPDATE id=id;

-- =====================================================
-- Database Setup Complete
-- =====================================================

-- Connection Details:
-- Port: 3306
-- Database: ankisha_typing_master
-- Username: ankisha_user
-- Password: Ankisha@2025#Secure
-- 
-- Update these in your .env file:
-- DB_HOST=localhost
-- DB_PORT=3306
-- DB_NAME=ankisha_typing_master
-- DB_USER=ankisha_user
-- DB_PASSWORD=Ankisha@2025#Secure
