database.py

import sqlite3
import os
from datetime import datetime

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DATABASE_PATH = os.path.join(BASE_DIR, 'coursefinder.db')
print(f"📁 Database path: {DATABASE_PATH}")

def init_db():
    """Initialize the database with required tables"""
    try:
        conn = sqlite3.connect(DATABASE_PATH)
        cursor = conn.cursor()
        
        # Check if tables exist
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        existing_tables = cursor.fetchall()
        print(f"📊 Existing tables: {existing_tables}")
        
        # Users table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            first_name TEXT NOT NULL,
            last_name TEXT NOT NULL,
            interests TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_login TIMESTAMP
        )
        ''')
        print("✅ Users table created/checked")
        
        # Bookmarks table
        cursor.execute('''
         CREATE TABLE IF NOT EXISTS bookmarks (
         id INTEGER PRIMARY KEY AUTOINCREMENT,
         user_id INTEGER NOT NULL,
         user_email TEXT NOT NULL,
         course_title TEXT NOT NULL,
         course_url TEXT,
         platform TEXT,
         difficulty TEXT,
         rating TEXT,
         bookmarked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
)
''')

        print("✅ Bookmarks table created/checked")
        
        # User search history
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS search_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            search_query TEXT NOT NULL,
            searched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
        )
        ''')
        
        # User recommendations history
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS recommendations_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            course_title TEXT NOT NULL,
            course_url TEXT,
            platform TEXT,
            difficulty TEXT,
            rating TEXT,
            searched_query TEXT,
            recommended_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
        )
        ''')
        

         #skill gap
        cursor.execute('''
         CREATE TABLE IF NOT EXISTS skill_gap_analysis (
         id INTEGER PRIMARY KEY AUTOINCREMENT,
         user_id INTEGER NOT NULL,
         job_title TEXT NOT NULL,
         category TEXT NOT NULL,
         user_skills TEXT,
         matched_count INTEGER DEFAULT 0,
         missing_count INTEGER DEFAULT 0,
         analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         FOREIGN KEY (user_id) REFERENCES users(id)
         );
        ''')


        conn.commit()
        
        # Verify tables were created
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        final_tables = cursor.fetchall()
        print(f"📊 Final tables: {final_tables}")
        
        conn.close()
        print("✅ Database initialized successfully")
        
    except Exception as e:
        print(f"❌ Database initialization error: {str(e)}")
        raise

def get_db_connection():
    """Get database connection"""
    try:
        conn = sqlite3.connect(DATABASE_PATH)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA foreign_keys = ON")  # Enable foreign keys
        print(f"📡 Database connection established to: {DATABASE_PATH}")
        return conn
    except Exception as e:
        print(f"❌ Database connection error: {str(e)}")
        raise

# Initialize database on import
init_db()