import sqlite3
import csv
import os

# UNC or local path supported
CSV_PATH = r"c:\Temp\projectAniston\TXT\importTableBravo.txt"
DB_PATH = r"C:\Temp\projectAniston\DB\database.db"


def ensure_db():
    """Create database and Bravo table if missing."""
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)

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

    cur.execute("""
        CREATE TABLE IF NOT EXISTS Bravo (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            unc_path TEXT,
            description TEXT,
            notes TEXT
        )
    """)

    conn.commit()
    conn.close()
    print("[INFO] Bravo table ready.")


def import_csv():
    """Import CSV rows into Bravo table."""
    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(f"[INFO] Reading CSV: {CSV_PATH}")

    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}")

        imported = 0
        skipped = 0

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

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

            cur.execute("""
                INSERT INTO Bravo (unc_path, description, notes)
                VALUES (?, ?, ?)
            """, row)

            imported += 1

    conn.commit()
    conn.close()

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


def main():
    print("[INFO] Initializing...")
    ensure_db()
    import_csv()


if __name__ == "__main__":
    main()