database.sql

CREATE DATABASE IF NOT EXISTS community_certificate;
USE community_certificate;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(15),
    user_type ENUM('admin', 'user') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE applications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    application_no VARCHAR(20) UNIQUE,
    certificate_type ENUM('Caste', 'Income', 'Native', 'Aadhar', 'Ration') NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    father_name VARCHAR(100) NOT NULL,
    mother_name VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender ENUM('Male', 'Female', 'Other') NOT NULL,
    address TEXT NOT NULL,
    city VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    pincode VARCHAR(10) NOT NULL,
    additional_data JSON,
    document_path VARCHAR(255),
    status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    applied_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_date TIMESTAMP NULL,
    certificate_path VARCHAR(255),
    remarks TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert admin user with password: admin123 (plain text - INSECURE)
INSERT INTO users (username, password, email, full_name, user_type) VALUES
('admin', 'admin123', 'admin@community.gov', 'System Administrator', 'admin');

-- Test user with password: user123 (plain text - INSECURE)
INSERT INTO users (username, password, email, full_name, phone, user_type) VALUES
('testuser', 'user123', 'user@example.com', 'Test User', '1234567890', 'user');