-- SkatePro Academy Database Setup
-- Run this in your MySQL to create all tables

CREATE DATABASE IF NOT EXISTS skatepro_academy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE skatepro_academy;

-- Users table (Admin, Assistant Coach, Student)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(15),
    role ENUM('admin', 'assistant', 'student') NOT NULL DEFAULT 'student',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Students table
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id VARCHAR(20) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    date_of_birth DATE,
    gender ENUM('male', 'female', 'other'),
    phone VARCHAR(15),
    email VARCHAR(100),
    address TEXT,
    guardian_name VARCHAR(100),
    guardian_phone VARCHAR(15),
    guardian_relationship VARCHAR(20),
    batch_id INT DEFAULT 1,
    batch_name VARCHAR(50),
    skill_level ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',
    monthly_fee DECIMAL(10,2) DEFAULT 2000,
    joining_date DATE,
    fee_status ENUM('paid', 'due', 'pending') DEFAULT 'pending',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Attendance table
CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    batch_id INT DEFAULT 1,
    date DATE NOT NULL,
    status ENUM('present', 'absent', 'holiday') NOT NULL DEFAULT 'absent',
    marked_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_attendance (student_id, date)
);

-- Timing records table
CREATE TABLE IF NOT EXISTS timing_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    distance VARCHAR(20) NOT NULL,
    time_seconds DECIMAL(8,2) NOT NULL,
    time_display VARCHAR(20) NOT NULL,
    record_date DATE NOT NULL,
    notes TEXT,
    recorded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sprint races table
CREATE TABLE IF NOT EXISTS sprint_races (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    distance VARCHAR(20) NOT NULL,
    race_date DATE NOT NULL,
    status ENUM('pending', 'ongoing', 'completed') DEFAULT 'pending',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sprint race rounds table
CREATE TABLE IF NOT EXISTS sprint_rounds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    race_id INT NOT NULL,
    round_number INT NOT NULL,
    started_at TIME,
    completed_at TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sprint race participants/results table
CREATE TABLE IF NOT EXISTS sprint_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    round_id INT NOT NULL,
    student_id INT NOT NULL,
    position INT,
    time_seconds DECIMAL(8,2),
    time_display VARCHAR(20),
    status ENUM('finished', 'dnf', 'pending') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Fee receipts table
CREATE TABLE IF NOT EXISTS fee_receipts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    receipt_number VARCHAR(30) UNIQUE NOT NULL,
    student_id INT NOT NULL,
    month_year VARCHAR(10) NOT NULL,
    monthly_fee DECIMAL(10,2) NOT NULL,
    late_fee DECIMAL(10,2) DEFAULT 0,
    gst_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL,
    amount_paid DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(20),
    payment_date DATE,
    notes TEXT,
    status ENUM('paid', 'pending', 'cancelled') DEFAULT 'paid',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Equipment table
CREATE TABLE IF NOT EXISTS equipment (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    unit_cost DECIMAL(10,2) NOT NULL,
    current_stock INT DEFAULT 0,
    min_stock INT DEFAULT 5,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Equipment transactions (buy/sell)
CREATE TABLE IF NOT EXISTS equipment_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    equipment_id INT NOT NULL,
    type ENUM('buy', 'sell') NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    buyer_name VARCHAR(100),
    student_id INT,
    transaction_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Expenses table
CREATE TABLE IF NOT EXISTS expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category ENUM('rent', 'salary', 'electricity', 'water', 'maintenance', 'transport', 'food', 'other') NOT NULL DEFAULT 'other',
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    expense_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Batches table
CREATE TABLE IF NOT EXISTS batches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    time_slot VARCHAR(30),
    coach_name VARCHAR(100),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert only admin and assistant login (NO demo students)
INSERT IGNORE INTO users (username, password_hash, name, role) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin User', 'admin'),
('assistant', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Coach Rahul', 'assistant');
-- Password for admin and assistant: admin123

-- Insert default batches
INSERT IGNORE INTO batches (id, name, time_slot) VALUES
(1, 'Morning Batch A', '6:00 AM - 8:00 AM'),
(2, 'Evening Batch B', '4:00 PM - 6:00 PM'),
(3, 'Weekend Batch', '9:00 AM - 12:00 PM');
