database.sql

-- database.sql
-- Run this in your PhpMyAdmin or MySQL console to set up the database

CREATE DATABASE IF NOT EXISTS internship_system;
USE internship_system;

-- Table: users
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('Admin', 'Student') DEFAULT 'Student',
    phone VARCHAR(20) DEFAULT NULL,
    college VARCHAR(150) DEFAULT NULL,
    degree VARCHAR(100) DEFAULT NULL,
    cgpa DECIMAL(4,2) DEFAULT NULL,
    enrollment_no VARCHAR(50) DEFAULT NULL,
    linkedin VARCHAR(100) DEFAULT NULL,
    github VARCHAR(100) DEFAULT NULL,
    leetcode VARCHAR(100) DEFAULT NULL,
    codeforces VARCHAR(100) DEFAULT NULL,
    codechef VARCHAR(100) DEFAULT NULL,
    geeksforgeeks VARCHAR(100) DEFAULT NULL,
    hackerrank VARCHAR(100) DEFAULT NULL,
    resume_path VARCHAR(255) DEFAULT NULL
);

-- Table: internships
CREATE TABLE IF NOT EXISTS internships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    company VARCHAR(150) NOT NULL,
    location VARCHAR(150) NOT NULL,
    description TEXT NOT NULL,
    posted_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table: applications
CREATE TABLE IF NOT EXISTS applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    intern_id INT NOT NULL,
    status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (intern_id) REFERENCES internships(id) ON DELETE CASCADE
);

-- Insert a default admin for testing (Password is 'admin123')
-- The hash below is generated by password_hash('admin123', PASSWORD_DEFAULT)
INSERT INTO users (name, email, password, role) VALUES 
('System Admin', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin');