-- Karate Coaching Management System Database Schema
-- Run this SQL file to create the database and tables

CREATE DATABASE IF NOT EXISTS karate_coaching;
USE karate_coaching;

-- Users table (for authentication)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contact_number VARCHAR(15) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'student') NOT NULL DEFAULT 'student',
    is_active BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_contact (contact_number),
    INDEX idx_role (role),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Students table
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    father_name VARCHAR(100),
    date_of_birth DATE,
    gender ENUM('male', 'female', 'other'),
    email VARCHAR(100),
    address TEXT,
    profile_photo VARCHAR(255),
    belt_level VARCHAR(50),
    class_timing VARCHAR(50),
    monthly_fee DECIMAL(10, 2) DEFAULT 0.00,
    registration_date DATE DEFAULT (CURRENT_DATE),
    status ENUM('pending', 'active', 'inactive') DEFAULT 'pending',
    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_status (status),
    INDEX idx_name (full_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fee payments table
CREATE TABLE IF NOT EXISTS fee_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_month VARCHAR(20) NOT NULL,
    payment_year INT NOT NULL,
    payment_mode ENUM('cash', 'online', 'card', 'upi') DEFAULT 'cash',
    receipt_number VARCHAR(50) UNIQUE NOT NULL,
    remarks TEXT,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_student (student_id),
    INDEX idx_date (payment_date),
    INDEX idx_receipt (receipt_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user
-- Username: admin / Password: admin123
INSERT INTO users (contact_number, password, role, is_active)
VALUES ('admin', '$2a$10$rZ3qKqGqKqGqKqGqKqGqKuZ3qKqGqKqGqKqGqKqGqKqGqKqGqKqGq', 'admin', TRUE)
ON DUPLICATE KEY UPDATE contact_number = contact_number;

-- Note: The default admin password is 'admin123'. Please change it after first login.
-- To generate a new bcrypt hash for password, use an online bcrypt generator or Node.js:
-- const bcrypt = require('bcryptjs');
-- const hash = bcrypt.hashSync('your_password', 10);