database.py

import sqlite3
import os
from flask import g
from werkzeug.security import generate_password_hash

DATABASE = os.path.join(os.path.dirname(__file__), 'placement.db')


def ensure_column(db, table, column, definition):
    columns = {row['name'] for row in db.execute(f'PRAGMA table_info({table})').fetchall()}
    if column not in columns:
        db.execute(f'ALTER TABLE {table} ADD COLUMN {column} {definition}')


def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect(DATABASE)
        g.db.row_factory = sqlite3.Row
        g.db.execute('PRAGMA foreign_keys = ON')
    return g.db


def init_db():
    db = sqlite3.connect(DATABASE)
    db.row_factory = sqlite3.Row
    db.execute('PRAGMA foreign_keys = ON')

    # admin
    db.execute('''
        CREATE TABLE IF NOT EXISTS admin (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            email TEXT,
            is_active INTEGER DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    # company
    db.execute('''
        CREATE TABLE IF NOT EXISTS company (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            company_name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            hr_name TEXT,
            hr_contact TEXT,
            website TEXT,
            industry TEXT,
            description TEXT,
            address TEXT,
            approval_status TEXT DEFAULT 'pending',
            is_active INTEGER DEFAULT 1,
            is_blacklisted INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    # student
    db.execute('''
        CREATE TABLE IF NOT EXISTS student (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            roll_number TEXT UNIQUE,
            phone TEXT,
            department TEXT,
            year_of_study INTEGER,
            cgpa REAL,
            skills TEXT,
            resume_path TEXT,
            address TEXT,
            is_active INTEGER DEFAULT 1,
            is_blacklisted INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    # placement drives
    db.execute('''
        CREATE TABLE IF NOT EXISTS placement_drive (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            company_id INTEGER NOT NULL,
            job_title TEXT NOT NULL,
            job_description TEXT,
            required_skills TEXT,
            experience_required TEXT,
            package TEXT,
            location TEXT,
            job_type TEXT DEFAULT 'Full-time',
            eligibility_criteria TEXT,
            min_cgpa REAL DEFAULT 0.0,
            eligible_departments TEXT,
            application_deadline TEXT,
            drive_date TEXT,
            max_positions INTEGER DEFAULT 1,
            status TEXT DEFAULT 'pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (company_id) REFERENCES company(id)
        )
    ''')

    ensure_column(db, 'placement_drive', 'required_skills', 'TEXT')
    ensure_column(db, 'placement_drive', 'experience_required', 'TEXT')

    # applications (unique constraint prevents double applying)
    db.execute('''
        CREATE TABLE IF NOT EXISTS application (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id INTEGER NOT NULL,
            drive_id INTEGER NOT NULL,
            application_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            status TEXT DEFAULT 'applied',
            remarks TEXT,
            FOREIGN KEY (student_id) REFERENCES student(id),
            FOREIGN KEY (drive_id) REFERENCES placement_drive(id),
            UNIQUE(student_id, drive_id)
        )
    ''')

    db.execute('''
        CREATE TABLE IF NOT EXISTS notification (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            user_role TEXT NOT NULL,
            title TEXT NOT NULL,
            message TEXT NOT NULL,
            is_read INTEGER DEFAULT 0,
            link TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    ensure_column(db, 'notification', 'user_id', 'INTEGER')
    ensure_column(db, 'notification', 'user_role', "TEXT DEFAULT 'student'")
    ensure_column(db, 'notification', 'title', "TEXT DEFAULT 'Update'")
    ensure_column(db, 'notification', 'link', 'TEXT')
    db.execute('CREATE INDEX IF NOT EXISTS idx_notification_user ON notification(user_id, user_role)')

    # seed admin if first run
    existing = db.execute('SELECT id FROM admin WHERE username = ?', ('admin',)).fetchone()
    if not existing:
        hashed = generate_password_hash('admin123')
        db.execute(
            'INSERT INTO admin (username, password, email) VALUES (?, ?, ?)',
            ('admin', hashed, 'admin@placewise.com')
        )

    db.commit()
    db.close()