-- Database Schema for Admission Portal

CREATE DATABASE IF NOT EXISTS admission_system;
USE admission_system;

-- Users Table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    cnic VARCHAR(20) NOT NULL UNIQUE, -- National ID
    phone VARCHAR(20) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('student', 'admin') DEFAULT 'student',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Programs Table
CREATE TABLE programs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    program_name VARCHAR(100) NOT NULL,
    degree_level ENUM('Bachelor', 'Master', 'PhD', 'Diploma') NOT NULL,
    duration VARCHAR(50),
    department VARCHAR(100),
    fee_per_semester DECIMAL(10, 2),
    is_active BOOLEAN DEFAULT TRUE
);

-- Applications Table
CREATE TABLE applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    program_id INT NOT NULL,
    status ENUM('Draft', 'Submitted', 'Under Review', 'Accepted', 'Rejected') DEFAULT 'Draft',
    submission_date TIMESTAMP NULL,
    payment_status ENUM('Pending', 'Paid', 'Verification Pending') DEFAULT 'Pending',
    payment_method VARCHAR(50),
    transaction_id VARCHAR(100),
    payment_date TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (program_id) REFERENCES programs(id)
);

-- Personal Details Table
CREATE TABLE personal_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    father_name VARCHAR(100),
    dob DATE,
    gender ENUM('Male', 'Female', 'Other'),
    address TEXT,
    city VARCHAR(50),
    country VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Academic Records Table
CREATE TABLE academic_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    degree_title VARCHAR(100) NOT NULL, -- e.g., Matric, Intermediate, BS
    institute VARCHAR(150),
    passing_year YEAR,
    obtained_marks DECIMAL(10, 2),
    total_marks DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Documents Table
CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    document_type VARCHAR(50), -- Photo, CNIC, Transcript
    file_path VARCHAR(255),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Insert Dummy Programs
INSERT INTO programs (program_name, degree_level, duration, department, fee_per_semester) VALUES
('BS Computer Science', 'Bachelor', '4 Years', 'Computer Science', 120000.00),
('BS Software Engineering', 'Bachelor', '4 Years', 'Software Engineering', 125000.00),
('BBA', 'Bachelor', '4 Years', 'Business Administration', 110000.00),
('MS Computer Science', 'Master', '2 Years', 'Computer Science', 90000.00);
