from flask import Flask, render_template, request, redirect, url_for, session, flash, g
import sqlite3
import os
import re
from datetime import datetime

app = Flask(__name__)
app.secret_key = 'student_system_secret_key_2024'

# Database path
DATABASE = os.path.join(os.path.dirname(__file__), 'database.db')

# ─────────────────────────────────────────
# Database Helpers
# ─────────────────────────────────────────

def get_db():
    """Open a new database connection if not already open for this request."""
    if 'db' not in g:
        g.db = sqlite3.connect(DATABASE)
        g.db.row_factory = sqlite3.Row  # rows behave like dicts
    return g.db

@app.teardown_appcontext
def close_db(error):
    """Close database connection at end of request."""
    db = g.pop('db', None)
    if db is not None:
        db.close()

def init_db():
    """Create tables if they don't exist."""
    db = sqlite3.connect(DATABASE)
    cursor = db.cursor()

    # Users table (admin login)
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL
        )
    ''')

    # Students table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS students (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id  TEXT NOT NULL UNIQUE,
            full_name   TEXT NOT NULL,
            father_name TEXT NOT NULL,
            mother_name TEXT NOT NULL,
            gender      TEXT NOT NULL,
            dob         TEXT NOT NULL,
            email       TEXT NOT NULL UNIQUE,
            phone       TEXT NOT NULL,
            course      TEXT NOT NULL,
            branch      TEXT NOT NULL,
            year_sem    TEXT NOT NULL,
            address     TEXT NOT NULL,
            city        TEXT NOT NULL,
            state       TEXT NOT NULL,
            pin_code    TEXT NOT NULL,
            created_at  TEXT NOT NULL
        )
    ''')

    # Insert default admin user (password: admin123)
    cursor.execute('''
        INSERT OR IGNORE INTO users (username, password)
        VALUES (?, ?)
    ''', ('admin', 'admin123'))

    db.commit()
    db.close()
    print("Database initialised successfully.")

# ─────────────────────────────────────────
# Auth helpers
# ─────────────────────────────────────────

def login_required(f):
    """Decorator: redirect to login if session missing."""
    from functools import wraps
    @wraps(f)
    def decorated(*args, **kwargs):
        if 'user' not in session:
            flash('Please log in to access this page.', 'warning')
            return redirect(url_for('login'))
        return f(*args, **kwargs)
    return decorated

# ─────────────────────────────────────────
# Validation helpers
# ─────────────────────────────────────────

def validate_student(data):
    """Server-side validation; returns list of error strings."""
    errors = []

    if not data.get('student_id', '').strip():
        errors.append('Student ID is required.')
    if not data.get('full_name', '').strip():
        errors.append('Full Name is required.')
    if not data.get('father_name', '').strip():
        errors.append('Father Name is required.')
    if not data.get('mother_name', '').strip():
        errors.append('Mother Name is required.')
    if data.get('gender') not in ('Male', 'Female', 'Other'):
        errors.append('Please select a valid Gender.')

    # Date of birth
    dob = data.get('dob', '').strip()
    if not dob:
        errors.append('Date of Birth is required.')
    else:
        try:
            datetime.strptime(dob, '%Y-%m-%d')
        except ValueError:
            errors.append('Date of Birth format is invalid.')

    # Email
    email = data.get('email', '').strip()
    if not email:
        errors.append('Email is required.')
    elif not re.match(r'^[\w.+-]+@[\w-]+\.[a-zA-Z]{2,}$', email):
        errors.append('Email address is invalid.')

    # Phone (10 digits)
    phone = data.get('phone', '').strip()
    if not phone:
        errors.append('Phone Number is required.')
    elif not re.match(r'^\d{10}$', phone):
        errors.append('Phone Number must be exactly 10 digits.')

    if not data.get('course', '').strip():
        errors.append('Course is required.')
    if not data.get('branch', '').strip():
        errors.append('Branch is required.')
    if not data.get('year_sem', '').strip():
        errors.append('Year / Semester is required.')
    if not data.get('address', '').strip():
        errors.append('Address is required.')
    if not data.get('city', '').strip():
        errors.append('City is required.')
    if not data.get('state', '').strip():
        errors.append('State is required.')

    pin = data.get('pin_code', '').strip()
    if not pin:
        errors.append('Pin Code is required.')
    elif not re.match(r'^\d{6}$', pin):
        errors.append('Pin Code must be exactly 6 digits.')

    return errors

# ─────────────────────────────────────────
# Routes
# ─────────────────────────────────────────

@app.route('/')
def index():
    return redirect(url_for('login'))


# ── LOGIN ──────────────────────────────────
@app.route('/login', methods=['GET', 'POST'])
def login():
    if 'user' in session:
        return redirect(url_for('dashboard'))

    if request.method == 'POST':
        username = request.form.get('username', '').strip()
        password = request.form.get('password', '').strip()

        db = get_db()
        user = db.execute(
            'SELECT * FROM users WHERE username=? AND password=?',
            (username, password)
        ).fetchone()

        if user:
            session['user'] = username
            flash(f'Welcome back, {username}! 👋', 'success')
            return redirect(url_for('dashboard'))
        else:
            flash('Invalid username or password.', 'danger')

    return render_template('login.html')


# ── LOGOUT ─────────────────────────────────
@app.route('/logout')
@login_required
def logout():
    session.pop('user', None)
    flash('You have been logged out.', 'info')
    return redirect(url_for('login'))


# ── DASHBOARD ──────────────────────────────
@app.route('/dashboard')
@login_required
def dashboard():
    db = get_db()

    total      = db.execute('SELECT COUNT(*) FROM students').fetchone()[0]
    male       = db.execute("SELECT COUNT(*) FROM students WHERE gender='Male'").fetchone()[0]
    female     = db.execute("SELECT COUNT(*) FROM students WHERE gender='Female'").fetchone()[0]
    courses    = db.execute('SELECT COUNT(DISTINCT course) FROM students').fetchone()[0]

    # Recent 5 students
    recent = db.execute(
        'SELECT student_id, full_name, course, branch FROM students ORDER BY id DESC LIMIT 5'
    ).fetchall()

    stats = {
        'total':   total,
        'male':    male,
        'female':  female,
        'courses': courses,
    }
    return render_template('dashboard.html', stats=stats, recent=recent)


# ── ADD STUDENT ────────────────────────────
@app.route('/add_student', methods=['GET', 'POST'])
@login_required
def add_student():
    if request.method == 'POST':
        data = {k: v.strip() for k, v in request.form.items()}
        errors = validate_student(data)

        if errors:
            for e in errors:
                flash(e, 'danger')
            return render_template('add_student.html', form=data)

        db = get_db()

        # Check duplicate student_id
        dup_id = db.execute('SELECT id FROM students WHERE student_id=?', (data['student_id'],)).fetchone()
        if dup_id:
            flash('Student ID already exists. Use a unique ID.', 'danger')
            return render_template('add_student.html', form=data)

        # Check duplicate email
        dup_email = db.execute('SELECT id FROM students WHERE email=?', (data['email'],)).fetchone()
        if dup_email:
            flash('Email address already registered.', 'danger')
            return render_template('add_student.html', form=data)

        db.execute('''
            INSERT INTO students
                (student_id, full_name, father_name, mother_name, gender, dob,
                 email, phone, course, branch, year_sem, address, city, state,
                 pin_code, created_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        ''', (
            data['student_id'], data['full_name'], data['father_name'],
            data['mother_name'], data['gender'], data['dob'],
            data['email'], data['phone'], data['course'], data['branch'],
            data['year_sem'], data['address'], data['city'], data['state'],
            data['pin_code'], datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        ))
        db.commit()
        flash(f'Student "{data["full_name"]}" added successfully! ✅', 'success')
        return redirect(url_for('view_students'))

    return render_template('add_student.html', form={})


# ── VIEW STUDENTS ──────────────────────────
@app.route('/view_students')
@login_required
def view_students():
    db  = get_db()
    q   = request.args.get('q', '').strip()
    col = request.args.get('col', 'full_name')

    allowed_cols = {'full_name', 'student_id', 'course', 'phone'}
    if col not in allowed_cols:
        col = 'full_name'

    if q:
        students = db.execute(
            f'SELECT * FROM students WHERE {col} LIKE ? ORDER BY id DESC',
            (f'%{q}%',)
        ).fetchall()
    else:
        students = db.execute('SELECT * FROM students ORDER BY id DESC').fetchall()

    return render_template('view_students.html', students=students, q=q, col=col)


# ── EDIT STUDENT ───────────────────────────
@app.route('/edit_student/<int:sid>', methods=['GET', 'POST'])
@login_required
def edit_student(sid):
    db      = get_db()
    student = db.execute('SELECT * FROM students WHERE id=?', (sid,)).fetchone()

    if not student:
        flash('Student not found.', 'danger')
        return redirect(url_for('view_students'))

    if request.method == 'POST':
        data   = {k: v.strip() for k, v in request.form.items()}
        errors = validate_student(data)

        if errors:
            for e in errors:
                flash(e, 'danger')
            return render_template('edit_student.html', student=data, sid=sid)

        # Duplicate checks (exclude current record)
        dup_id = db.execute(
            'SELECT id FROM students WHERE student_id=? AND id!=?',
            (data['student_id'], sid)
        ).fetchone()
        if dup_id:
            flash('Student ID already used by another record.', 'danger')
            return render_template('edit_student.html', student=data, sid=sid)

        dup_email = db.execute(
            'SELECT id FROM students WHERE email=? AND id!=?',
            (data['email'], sid)
        ).fetchone()
        if dup_email:
            flash('Email already used by another record.', 'danger')
            return render_template('edit_student.html', student=data, sid=sid)

        db.execute('''
            UPDATE students SET
                student_id=?, full_name=?, father_name=?, mother_name=?,
                gender=?, dob=?, email=?, phone=?, course=?, branch=?,
                year_sem=?, address=?, city=?, state=?, pin_code=?
            WHERE id=?
        ''', (
            data['student_id'], data['full_name'], data['father_name'],
            data['mother_name'], data['gender'], data['dob'],
            data['email'], data['phone'], data['course'], data['branch'],
            data['year_sem'], data['address'], data['city'], data['state'],
            data['pin_code'], sid
        ))
        db.commit()
        flash(f'Student "{data["full_name"]}" updated successfully! ✅', 'success')
        return redirect(url_for('view_students'))

    return render_template('edit_student.html', student=student, sid=sid)


# ── DELETE STUDENT ─────────────────────────
@app.route('/delete_student/<int:sid>', methods=['POST'])
@login_required
def delete_student(sid):
    db      = get_db()
    student = db.execute('SELECT full_name FROM students WHERE id=?', (sid,)).fetchone()

    if student:
        db.execute('DELETE FROM students WHERE id=?', (sid,))
        db.commit()
        flash(f'Student "{student["full_name"]}" deleted successfully.', 'success')
    else:
        flash('Student not found.', 'danger')

    return redirect(url_for('view_students'))


# ─────────────────────────────────────────
# Entry point
# ─────────────────────────────────────────
init_db()

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=7860, debug=True)