import sqlite3
import csv
import os

DB_PATH = r"C:\Temp\projectAniston\DB\database.db"
CSV_PATH = r"C:\Temp\projectAniston\TXT\importTableAlpha.txt"


def ensure_db():
    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()

    cur.execute("""
        CREATE TABLE IF NOT EXISTS Alpha (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            last_name TEXT,
            first_name TEXT,
            dob TEXT,
            ssn TEXT,
            address TEXT,
            email TEXT,
            phone TEXT,
            website TEXT,
            notes TEXT
        )
    """)

    conn.commit()
    conn.close()


def import_csv():
    if not os.path.exists(CSV_PATH):
        print(f"[ERROR] CSV file not found: {CSV_PATH}")
        return

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

    print("[INFO] Reading CSV...")

    with open(CSV_PATH, "r", encoding="utf-8-sig", newline="") as f:
        reader = csv.reader(f)

        header = next(reader, None)
        print(f"[INFO] Header detected: {header}")

        row_count = 0
        skipped = 0

        for row in reader:
            print(f"[DEBUG] Row read ({len(row)} columns): {row}")

            if len(row) != 9:
                print(f"[WARN] Skipping malformed row: {row}")
                skipped += 1
                continue

            cur.execute("""
                INSERT INTO Alpha (
                    last_name, first_name, dob, ssn,
                    address, email, phone, website, notes
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, row)

            row_count += 1

    conn.commit()
    conn.close()

    print(f"[INFO] Import complete.")
    print(f"[INFO] Rows imported: {row_count}")
    print(f"[INFO] Rows skipped: {skipped}")


def main():
    print("[INFO] Ensuring database and table exist...")
    ensure_db()

    print("[INFO] Importing CSV into Alpha table...")
    import_csv()


if __name__ == "__main__":
    main()