import sqlite3
import os
from pathlib import Path

def init_database():
    # Get the database path
    database_dir = Path(__file__).parent.parent.parent / "database"
    schema_file = database_dir / "schema.sql"
    sample_data_file = database_dir / "seeds" / "sample_data.sql"
    
    db_path = os.getenv("DATABASE_URL", "sqlite:///./know_foolery.db")
    if db_path.startswith("sqlite:///"):
        db_file = db_path.replace("sqlite:///", "")
    else:
        db_file = "know_foolery.db"
    
    # Create database and tables
    conn = sqlite3.connect(db_file)
    
    try:
        # Execute schema
        if schema_file.exists():
            with open(schema_file, 'r') as f:
                schema_sql = f.read()
                conn.executescript(schema_sql)
                print("Database schema created successfully")
        
        # Insert sample data
        if sample_data_file.exists():
            with open(sample_data_file, 'r') as f:
                sample_sql = f.read()
                conn.executescript(sample_sql)
                print("Sample data inserted successfully")
        
        conn.commit()
        print("Database initialization completed")
        
    except Exception as e:
        print(f"Error initializing database: {e}")
        conn.rollback()
        
    finally:
        conn.close()

if __name__ == "__main__":
    init_database()