src/utils/db_helpers.py

import sqlite3
import os
import pandas as pd 
import logging 

logger = logging.getLogger(__name__)
def import_sql_to_db(sql_path, db_path="output.db"):

    if not os.path.exists(sql_path):
        raise FileNotFoundError(f"{sql_path} not found")

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    with open(sql_path, "r", encoding="utf-8") as f:
        sql_script = f.read()

    try:
        cursor.executescript(sql_script)  # ✅ executes full SQL dump
        conn.commit()
        print(f"✅ Database created at {db_path}") 

        return db_path
    except Exception as e:
        print(f"❌ Error: {e}")
    finally:
        conn.close() 


def convert_csv_to_sqlite(csv_path, db_path="output.db", table_name=None):

    if table_name is None:
        table_name = os.path.splitext(os.path.basename(csv_path))[0]

    df = pd.read_csv(csv_path)

    conn = sqlite3.connect(db_path)
    df.to_sql(table_name, conn, if_exists="replace", index=False)
    conn.close()

    print(f"✅ CSV converted to SQLite DB ({table_name} table)") 
    return db_path

def convert_to_sqlite(input_path, output_db="converted.db"):
    ext = os.path.splitext(input_path)[1].lower() 

    if os.path.exists(output_db): 
        os.remove(output_db)

    if ext == ".sql":
        return import_sql_to_db(input_path, output_db)

    elif ext in [".db", ".sqlite"]:
        import shutil
        shutil.copy(input_path, output_db)
        print(f"✅ Copied DB to {output_db}") 
        return output_db

    elif ext == ".csv":
        return convert_csv_to_sqlite(input_path, output_db)

    else:
        raise ValueError(f"❌ Unsupported format: {ext}")