init_db.py

import os
import psycopg
from dotenv import load_dotenv

load_dotenv()

DATABASE_URL = os.getenv("DATABASE_URL")

if not DATABASE_URL:
    raise ValueError("DATABASE_URL environment variable is not set")

print(f"Connecting to database...")

def init_db():
    try:
        with psycopg.connect(DATABASE_URL) as conn:
            with conn.cursor() as cur:
                print("Connected! Enabling vector extension...")
                cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")
                
                print("Creating students table...")
                cur.execute("""
                    CREATE TABLE IF NOT EXISTS students (
                        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                        name VARCHAR(255) NOT NULL,
                        roll_number VARCHAR(100) UNIQUE NOT NULL,
                        -- Note: We use an arbitrary vector dimension size
                        -- However, we must specify the exact dimension if we want pgvector HNSW index.
                        -- dlib face embeddings (used by face_recognition) are 128 dimensions.
                        -- deepface (VGG-Face) is 2622, Facenet is 128, ArcFace is 512, Datashape is 512.
                        -- By default deepface uses VGG-Face (2622) or Facenet (128). Let's use 128 (Facenet or Dlib) or we can leave it dynamic without dimension limit.
                        -- Since we switched to deepface, let's just use `vector` (dynamic)
                        face_encoding vector NOT NULL,
                        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                    );
                """)
                
                print("Creating attendance table...")
                cur.execute("""
                    CREATE TABLE IF NOT EXISTS attendance (
                        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                        student_id UUID REFERENCES students(id) ON DELETE CASCADE,
                        timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
                        status VARCHAR(50) DEFAULT 'Present'
                    );
                """)
                
                conn.commit()
                print("Database initialized successfully!")
                
    except Exception as e:
        print(f"Error initializing database: {e}")

if __name__ == "__main__":
    init_db()