-- NSDA Admission System Database Schema

CREATE DATABASE IF NOT EXISTS chandan1_nsda;
USE chandan1_nsda;

-- Applicants table
CREATE TABLE applicants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stp_registration VARCHAR(50) DEFAULT 'STP- KHU-001752',
    course VARCHAR(50),
    batch VARCHAR(50),
    date_of_enrollment DATE,
    trainee_name_english VARCHAR(255),
    trainee_name_bangla VARCHAR(255),
    date_of_birth DATE,
    identification_type ENUM('NID', 'Birth Certificate'),
    identification_number VARCHAR(100),
    gender ENUM('Male', 'Female', 'Others'),
    contact_number VARCHAR(20),
    email VARCHAR(255),
    pwd ENUM('Yes', 'No'),
    disability_type VARCHAR(255),
    permanent_division VARCHAR(100),
    permanent_district VARCHAR(100),
    permanent_upazilla VARCHAR(100),
    permanent_address TEXT,
    permanent_area ENUM('Rural', 'Urban'),
    present_division VARCHAR(100),
    present_district VARCHAR(100),
    present_upazilla VARCHAR(100),
    present_address TEXT,
    religion VARCHAR(100),
    ethnicity VARCHAR(100),
    educational_qualification VARCHAR(255),
    marital_status VARCHAR(50),
    father_name_english VARCHAR(255),
    father_name_bangla VARCHAR(255),
    mother_name_english VARCHAR(255),
    mother_name_bangla VARCHAR(255),
    emergency_contact VARCHAR(20),
    family_members INT,
    income_source VARCHAR(255),
    monthly_income DECIMAL(10,2),
    income_per_person DECIMAL(10,2),
    has_bank_account ENUM('Yes', 'No'),
    bank_name VARCHAR(255),
    bank_branch VARCHAR(255),
    account_number VARCHAR(50),
    past_training ENUM('Yes', 'No'),
    training_course VARCHAR(255),
    training_duration INT,
    training_provider VARCHAR(255),
    received_certificate ENUM('Yes', 'No'),
    employment_status ENUM('Unemployed', 'Self Employed', 'Wage Employed'),
    nature_of_work VARCHAR(255),
    monthly_income_work DECIMAL(10,2),
    how_learned VARCHAR(255),
    photo VARCHAR(255),
    cv VARCHAR(255),
    nid_birth_certificate VARCHAR(255),
    academic_certificate VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Payments table
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    applicant_id INT,
    contact_number VARCHAR(20),
    payment_method ENUM('bKash', 'Nagad', 'Rocket', 'Upay'),
    sender_number VARCHAR(20),
    amount DECIMAL(10,2) DEFAULT 100.00,
    status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (applicant_id) REFERENCES applicants(id)
);