import os
import sqlite3

DB_PATH = r"C:\Temp\projectTheCityOfMcAllen\DB\uglyMexicans.db"


def ensure_database_exists():
    """Create the SQLite file and directory if they do not exist."""
    db_dir = os.path.dirname(DB_PATH)
    if not os.path.exists(db_dir):
        os.makedirs(db_dir)

    if not os.path.exists(DB_PATH):
        print("Database does not exist. Creating new database file...")
        open(DB_PATH, "w").close()
    else:
        print("Database already exists.")


def table_exists(cursor, table_name):
    cursor.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name=?;",
        (table_name,)
    )
    return cursor.fetchone() is not None


def rebuild_table(cursor, table_name, new_schema_sql, insert_sql=None):
    """Rebuilds a table safely with a new schema."""
    if table_exists(cursor, table_name):
        print(f"Rebuilding existing table: {table_name}")

        # Rename old table
        cursor.execute(f"ALTER TABLE {table_name} RENAME TO {table_name}_old;")

        # Create new table
        cursor.execute(new_schema_sql)

        # Copy data if insert SQL provided
        if insert_sql:
            cursor.execute(insert_sql)

        # Drop old table
        cursor.execute(f"DROP TABLE {table_name}_old;")

    else:
        print(f"Creating new table: {table_name}")
        cursor.execute(new_schema_sql)


def rebuild_all_tables():
    ensure_database_exists()

    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()

    print("Connected to database.")

    # -------------------------
    # PHYSICAL TABLE
    # -------------------------
    physical_schema = """
        CREATE TABLE physically (
            name TEXT,
            face TEXT,
            eyes TEXT,
            nose TEXT,
            ears TEXT,
            teeth TEXT,
            mouth TEXT,
            chin TEXT,
            profile TEXT,
            throat TEXT,
            neck TEXT
        );
    """

    physical_insert = """
        INSERT INTO physically (name, face, eyes, nose, ears, teeth, mouth, chin, profile, throat, neck)
        SELECT name, face, eyes, nose, ears, teeth, mouth, chin, profile, throat, neck
        FROM physically_old;
    """

    rebuild_table(cur, "physically", physical_schema, physical_insert)

    # -------------------------
    # EMOTIONAL TABLE
    # -------------------------
    emotional_schema = """
        CREATE TABLE emotional (
            name TEXT,
            logicTestPasses INTEGER,
            logicTestFailures INTEGER
        );
    """

    emotional_insert = """
        INSERT INTO emotional (name, logicTestPasses, logicTestFailures)
        SELECT name, logicTestPasses, logicTestFailures
        FROM emotional_old;
    """

    rebuild_table(cur, "emotional", emotional_schema, emotional_insert)

    # -------------------------
    # PSYCHOLOGICAL TABLE
    # -------------------------
    psychological_schema = """
        CREATE TABLE psychological (
            name TEXT,
            highest_level_of_education TEXT
        );
    """

    psychological_insert = """
        INSERT INTO psychological (name, highest_level_of_education)
        SELECT name, highest_level_of_education
        FROM psychological_old;
    """

    rebuild_table(cur, "psychological", psychological_schema, psychological_insert)

    # -------------------------
    # SPIRITUAL TABLE
    # -------------------------
    spiritual_schema = """
        CREATE TABLE spiritual (
            name TEXT,
            religion TEXT
        );
    """

    spiritual_insert = """
        INSERT INTO spiritual (name, religion)
        SELECT name, religion
        FROM spiritual_old;
    """

    rebuild_table(cur, "spiritual", spiritual_schema, spiritual_insert)

    conn.commit()
    conn.close()

    print("All tables created or rebuilt successfully.")


if __name__ == "__main__":
    rebuild_all_tables()