import sqlite3
import os
from werkzeug.security import generate_password_hash, check_password_hash

# Path to the SQLite database file in persistent storage
DB_PATH = "/data/users.db"

def get_db_connection():
    """Establishes a connection to the SQLite database."""
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def create_user_table():
    """Creates the users table if it doesn't already exist."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE NOT NULL,
            enrollment_no TEXT UNIQUE,
            password_hash TEXT NOT NULL,
            role TEXT NOT NULL CHECK(role IN ('student', 'faculty')),
            enrolled_by INTEGER,
            FOREIGN KEY(enrolled_by) REFERENCES users(id)
        )
    ''')
    conn.commit()
    
    # Add a default faculty user if one doesn't exist for initial setup
    cursor.execute("SELECT * FROM users WHERE role = 'faculty'")
    if cursor.fetchone() is None:
        print("Creating default faculty user...")
        cursor.execute('''
            INSERT INTO users (email, password_hash, role)
            VALUES (?,?,?)
        ''', ('faculty@ggits.net', generate_password_hash('ggits@123'), 'faculty'))
        conn.commit()
        print("Default faculty user created: faculty@ggits.net / ggits@123")
        
    conn.close()

def add_student(email, enrollment_no, password, faculty_id):
    """Adds a new student to the database, enrolled by a faculty member."""
    if not email.endswith('@ggits.net'):
        return "Error: Email must be a @ggits.net address."
    
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute('''
            INSERT INTO users (email, enrollment_no, password_hash, role, enrolled_by)
            VALUES (?,?,?,?,?)
        ''', (email, enrollment_no, generate_password_hash(password), 'student', faculty_id))
        conn.commit()
        return f"Student {email} enrolled successfully."
    except sqlite3.IntegrityError as e:
        if 'email' in str(e):
            return "Error: A user with this email already exists."
        if 'enrollment_no' in str(e):
            return "Error: A user with this enrollment number already exists."
        return "Error: Could not add student due to a database constraint."
    finally:
        conn.close()

def verify_user(identifier, password):
    """
    Verifies a user's credentials. The identifier can be an email or enrollment number.
    Returns the user's data if successful, otherwise None.
    """
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Check if identifier is email or enrollment number
    if '@' in identifier:
        cursor.execute("SELECT * FROM users WHERE email =?", (identifier,))
    else:
        cursor.execute("SELECT * FROM users WHERE enrollment_no =?", (identifier,))
        
    user = cursor.fetchone()
    conn.close()
    
    if user and check_password_hash(user['password_hash'], password):
        return dict(user)
    return None