-- Create Database
CREATE DATABASE IF NOT EXISTS leave_management_system;
USE leave_management_system;

-- Users/Employees Table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    department VARCHAR(100),
    designation VARCHAR(100),
    role ENUM('employee', 'sectional_head', 'vice_principal', 'principal') DEFAULT 'employee',
    casual_leaves_used INT DEFAULT 0,
    medical_leaves_used INT DEFAULT 0,
    duty_leaves_used INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Leave Applications Table
CREATE TABLE IF NOT EXISTS leave_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    employee_id VARCHAR(50) NOT NULL,
    leave_type ENUM('casual', 'medical', 'duty', 'short') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_half_day BOOLEAN DEFAULT FALSE,
    half_day_session ENUM('first_half', 'second_half', 'none') DEFAULT 'none',
    short_leave_time VARCHAR(50),
    reason TEXT NOT NULL,
    number_of_days DECIMAL(4,1) NOT NULL,
    apply_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    approved_by INT,
    approved_date TIMESTAMP NULL,
    admin_comments TEXT,
    year INT NOT NULL,
    month INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Leave Balance Tracking
CREATE TABLE IF NOT EXISTS leave_balance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    year INT NOT NULL,
    casual_leaves_total INT DEFAULT 20,
    casual_leaves_used DECIMAL(4,1) DEFAULT 0,
    medical_leaves_total INT DEFAULT 21,
    medical_leaves_used DECIMAL(4,1) DEFAULT 0,
    duty_leaves_used DECIMAL(4,1) DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_year (user_id, year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Short Leave Monthly Tracking
CREATE TABLE IF NOT EXISTS short_leave_tracking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    year INT NOT NULL,
    month INT NOT NULL,
    short_leaves_count INT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_month (user_id, year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default admin user (password: admin123)
INSERT INTO users (employee_id, full_name, email, password, role, designation) 
VALUES ('ADMIN001', 'System Administrator', 'admin@school.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'principal', 'Principal')
ON DUPLICATE KEY UPDATE email=email;

-- Create indexes for better performance
CREATE INDEX idx_leave_user_id ON leave_applications(user_id);
CREATE INDEX idx_leave_status ON leave_applications(status);
CREATE INDEX idx_leave_date ON leave_applications(start_date, end_date);
CREATE INDEX idx_leave_year ON leave_applications(year);
