#!/usr/bin/env python3
"""
Simple script to create test students using direct database connection.
"""

import sqlite3
import uuid
from datetime import date

def create_test_students():
    """Create test students directly in the database"""
    
    # Connect to the database
    conn = sqlite3.connect('test.db')
    cursor = conn.cursor()
    
    test_students = [
        {
            "first_name": "John",
            "last_name": "Doe",
            "email": "john.doe@student.edu",
            "student_id": "STU001",
            "year_of_study": 2,
            "program": "Computer Science",
            "current_semester": "Fall 2024",
            "student_category": "regular"
        },
        {
            "first_name": "Jane",
            "last_name": "Smith",
            "email": "jane.smith@student.edu",
            "student_id": "STU002",
            "year_of_study": 1,
            "program": "Mathematics",
            "current_semester": "Fall 2024",
            "student_category": "regular"
        },
        {
            "first_name": "Michael",
            "last_name": "Johnson",
            "email": "michael.johnson@student.edu",
            "student_id": "STU003",
            "year_of_study": 3,
            "program": "Physics",
            "current_semester": "Fall 2024",
            "student_category": "regular"
        },
        {
            "first_name": "Sarah",
            "last_name": "Williams",
            "email": "sarah.williams@student.edu",
            "student_id": "STU004",
            "year_of_study": 2,
            "program": "Chemistry",
            "current_semester": "Fall 2024",
            "student_category": "regular"
        },
        {
            "first_name": "David",
            "last_name": "Brown",
            "email": "david.brown@student.edu",
            "student_id": "STU005",
            "year_of_study": 1,
            "program": "Biology",
            "current_semester": "Fall 2024",
            "student_category": "regular"
        }
    ]
    
    try:
        # Check if students already exist
        cursor.execute("SELECT COUNT(*) FROM students")
        existing_count = cursor.fetchone()[0]
        print(f"Found {existing_count} existing students in the database")
        
        if existing_count >= 5:
            print("Sufficient test students already exist. Skipping creation.")
            return
        
        created_count = 0
        for student_data in test_students:
            # Check if student already exists
            cursor.execute("SELECT id FROM students WHERE email = ?", (student_data["email"],))
            existing_student = cursor.fetchone()
            
            if existing_student:
                print(f"Student {student_data['email']} already exists, skipping...")
                continue
            
            # Create user record first
            user_id = str(uuid.uuid4())
            cursor.execute("""
                INSERT INTO users (id, first_name, last_name, email, password_hash, user_type, is_active, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))
            """, (
                user_id,
                student_data["first_name"],
                student_data["last_name"],
                student_data["email"],
                "test_password_hash",
                "student",
                True
            ))
            
            # Create student record
            cursor.execute("""
                INSERT INTO students (id, student_id, year_of_study, program, enrollment_date, current_semester, student_category, is_active)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                user_id,
                student_data["student_id"],
                student_data["year_of_study"],
                student_data["program"],
                date.today().isoformat(),
                student_data["current_semester"],
                student_data["student_category"],
                True
            ))
            
            created_count += 1
            print(f"Created student: {student_data['first_name']} {student_data['last_name']} ({student_data['email']})")
        
        conn.commit()
        print(f"\nSuccessfully created {created_count} test students!")
        
        # Show final count
        cursor.execute("SELECT COUNT(*) FROM students")
        total_count = cursor.fetchone()[0]
        print(f"Total students in database: {total_count}")
        
    except Exception as e:
        conn.rollback()
        print(f"Error creating test students: {e}")
        import traceback
        traceback.print_exc()
    finally:
        conn.close()

if __name__ == "__main__":
    print("Creating test students for the tutor management system...")
    create_test_students()
    print("Done!")