-- Create database with optimized settings
CREATE DATABASE IF NOT EXISTS admission_system 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE admission_system;

-- Users table with indexing for fast lookups
CREATE TABLE users (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(191) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('student','admin') DEFAULT 'student',
    is_verified BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP NULL,
    login_attempts TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_active (is_active)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

-- User profiles with optimized storage
CREATE TABLE user_profiles (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    dob DATE NOT NULL,
    gender ENUM('male','female','other') NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address TEXT,
    specialization ENUM('science','commerce','arts') NOT NULL,
    tenth_marks DECIMAL(5,2) CHECK (tenth_marks >= 0 AND tenth_marks <= 100),
    twelfth_marks DECIMAL(5,2) CHECK (twelfth_marks >= 0 AND twelfth_marks <= 100),
    tenth_certificate VARCHAR(255),
    twelfth_certificate VARCHAR(255),
    is_complete BOOLEAN DEFAULT FALSE,
    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_specialization (specialization),
    INDEX idx_marks (twelfth_marks)
) ENGINE=InnoDB;

-- Courses with availability tracking
CREATE TABLE courses (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    description TEXT,
    specialization ENUM('science','commerce','arts') NOT NULL,
    min_marks DECIMAL(5,2) NOT NULL,
    max_seats INT UNSIGNED NOT NULL,
    available_seats INT UNSIGNED NOT NULL,
    duration VARCHAR(50),
    fees DECIMAL(10,2),
    status ENUM('active','inactive','full') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_specialization_status (specialization, status),
    INDEX idx_marks_seats (min_marks, available_seats)
) ENGINE=InnoDB;

-- Applications with efficient querying
CREATE TABLE applications (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    course_id INT UNSIGNED NOT NULL,
    application_number VARCHAR(50) UNIQUE NOT NULL,
    application_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending','under_review','approved','rejected','waitlisted') DEFAULT 'pending',
    priority TINYINT DEFAULT 1,
    remarks TEXT,
    reviewed_by INT UNSIGNED NULL,
    reviewed_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    INDEX idx_user_status (user_id, status),
    INDEX idx_course_status (course_id, status),
    INDEX idx_application_date (application_date),
    UNIQUE KEY unique_user_course (user_id, course_id)
) ENGINE=InnoDB PARTITION BY HASH(MONTH(application_date)) PARTITIONS 12;

-- Audit log for security
CREATE TABLE audit_logs (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_action (user_id, action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- Insert sample courses
INSERT INTO courses (course_code, course_name, description, specialization, min_marks, max_seats, available_seats, duration, fees) VALUES
('CS101', 'B.Tech Computer Science', 'Computer Science Engineering with AI/ML specializations', 'science', 75.0, 120, 120, '4 Years', 80000.00),
('ME102', 'B.Tech Mechanical', 'Mechanical Engineering with Robotics', 'science', 70.0, 80, 80, '4 Years', 75000.00),
('BT103', 'B.Tech Biotechnology', 'Biotechnology with research focus', 'science', 65.0, 60, 60, '4 Years', 85000.00),
('BCOM201', 'B.Com (Hons)', 'Bachelor of Commerce Honors', 'commerce', 60.0, 150, 150, '3 Years', 45000.00),
('BBA202', 'BBA', 'Business Administration', 'commerce', 65.0, 100, 100, '3 Years', 60000.00),
('BA301', 'BA English', 'Bachelor of Arts in English Literature', 'arts', 55.0, 120, 120, '3 Years', 35000.00),
('ECO302', 'BA Economics', 'Economics with quantitative methods', 'arts', 60.0, 80, 80, '3 Years', 40000.00);
