-- Student Management System Database Schema
CREATE DATABASE IF NOT EXISTS student_management;
USE student_management;

-- Admin table for login
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Students table
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    fathers_name VARCHAR(100) NOT NULL,
    mothers_name VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender ENUM('Male', 'Female', 'Other') NOT NULL,
    joining_date DATE NOT NULL,
    total_fee DECIMAL(10,2) NOT NULL,
    contact_number VARCHAR(15),
    email VARCHAR(100),
    address TEXT,
    emergency_contact VARCHAR(15),
    student_id VARCHAR(20) UNIQUE NOT NULL,
    status ENUM('Active', 'Inactive', 'Graduated') DEFAULT 'Active',
    reference_name VARCHAR(100),
    exit_date DATE NULL,
    document_name ENUM('10th Marksheet', '12th Marksheet', 'Aadhar Card', 'Pan Card', 'Voter ID') NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Student fees history table
CREATE TABLE IF NOT EXISTS fee_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_date DATE NOT NULL,
    next_payment_date DATE NULL,
    payment_method ENUM('Cash', 'Bank Transfer', 'Cheque', 'Online') DEFAULT 'Cash',
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

-- Insert default admin (password: admin123)
INSERT INTO admins (username, password, email, full_name) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@school.com', 'System Administrator');

-- Create indexes for better performance
CREATE INDEX idx_student_name ON students(student_name);
CREATE INDEX idx_student_id ON students(student_id);
CREATE INDEX idx_joining_date ON students(joining_date);
CREATE INDEX idx_status ON students(status);
CREATE INDEX idx_fee_history_student ON fee_history(student_id);
CREATE INDEX idx_fee_history_date ON fee_history(payment_date);
