Registration Application Database Design

-- Applicant Table
CREATE TABLE applicants (
    applicant_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender VARCHAR(20),
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(50),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Application Table
CREATE TABLE applications (
    application_id SERIAL PRIMARY KEY,
    applicant_id INTEGER REFERENCES applicants(applicant_id),
    application_date DATE NOT NULL,
    status VARCHAR(50) NOT NULL,
    program_type VARCHAR(100),
    term VARCHAR(50),
    year INTEGER,
    submission_date TIMESTAMP,
    review_date TIMESTAMP,
    decision_date TIMESTAMP
);

-- Document Table
CREATE TABLE documents (
    document_id SERIAL PRIMARY KEY,
    application_id INTEGER REFERENCES applications(application_id),
    document_type VARCHAR(100) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path TEXT NOT NULL,
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    verified BOOLEAN DEFAULT FALSE,
    verification_date TIMESTAMP
);

-- Payment Table
CREATE TABLE payments (
    payment_id SERIAL PRIMARY KEY,
    application_id INTEGER REFERENCES applications(application_id),
    amount DECIMAL(10,2) NOT NULL,
    payment_date TIMESTAMP NOT NULL,
    payment_method VARCHAR(50),
    transaction_id VARCHAR(100),
    status VARCHAR(50) NOT NULL,
    receipt_number VARCHAR(100)
);

-- Education Table
CREATE TABLE education (
    education_id SERIAL PRIMARY KEY,
    applicant_id INTEGER REFERENCES applicants(applicant_id),
    institution_name VARCHAR(255) NOT NULL,
    degree VARCHAR(100),
    field_of_study VARCHAR(255),
    start_date DATE,
    end_date DATE,
    gpa DECIMAL(3,2),
    country VARCHAR(100)
);

-- Reference Table
CREATE TABLE references (
    reference_id SERIAL PRIMARY KEY,
    application_id INTEGER REFERENCES applications(application_id),
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(50),
    relationship VARCHAR(100),
    institution VARCHAR(255),
    position VARCHAR(255),
    submission_status VARCHAR(50) DEFAULT 'Pending'
);

-- Test Score Table
CREATE TABLE test_scores (
    test_score_id SERIAL PRIMARY KEY,
    applicant_id INTEGER REFERENCES applicants(applicant_id),
    test_type VARCHAR(100) NOT NULL,
    score VARCHAR(50),
    test_date DATE,
    reporting_date DATE,
    valid_until DATE
);