import csv
import os
import sqlite3

# Define configuration paths
file_path = r"c:\projectOrganizer"
db_name = "softwareWrapper.db"
db_full_path = os.path.join(file_path, db_name)

# Ensure the target directory exists
os.makedirs(file_path, exist_ok=True)

# Pre-loaded dataset including the two dollar bill denomination
new_incoming_data = {
    "one": [
        (1, "K07231915B", 1, 100),
        (2, "E44645752C", 1, 100),
        (3, "I01937216B", 1, 100),
        (4, "D74268512A", 1, 100),
        (5, "K93368049A", 1, 100),
        (6, "K95021538A", 1, 100),
        (7, "K33292782A", 1, 100),
        (8, "K04979601C", 1, 100),
    ],
    "two": [
        (1, "", None, None),
        (2, "", None, None),
        (3, "", None, None),
        (4, "", None, None),
    ],
    "five": [
        (1, "", None, None),
        (2, "", None, None),
        (3, "", None, None),
        (4, "", None, None),
    ],
    "ten": [
        (1, "", None, None),
        (2, "", None, None),
        (3, "", None, None),
        (4, "", None, None),
    ],
    "twenty": [
        (1, "PG60125589D", 20, 2000),
        (2, "MD10079777E", 20, 2000),
        (3, "PL04570941J", 20, 2000),
        (4, "PF52321810B", 20, 2000),
    ],
    "hundred": [
        (1, "", None, None),
        (2, "", None, None),
        (3, "", None, None),
        (4, "", None, None),
    ],
}


def process_with_duplicate_check(incoming_data):
  """Checks text files for duplicates, updates logs, and imports ONLY records

  that contain valid serial numbers into SQLite.
  """
  conn = sqlite3.connect(db_full_path)
  cursor = conn.cursor()

  print(
      f"Processing text files and syncing valid serial numbers to"
      f" {db_full_path}...\n"
  )

  for table_name, rows in incoming_data.items():
    txt_filename = f"{table_name}.txt"
    txt_full_path = os.path.join(file_path, txt_filename)

    # Step 1: Load existing records from the text file if it exists
    existing_rows = []
    existing_serials = set()

    if os.path.exists(txt_full_path):
      with open(txt_full_path, mode="r", encoding="utf-8") as f:
        reader = csv.reader(f)
        for row in reader:
          if row:
            r_id, serial, v1, v2 = row[0], row[1], row[2], row[3]
            existing_rows.append((r_id, serial, v1, v2))
            if serial.strip():
              existing_serials.add(serial.strip().upper())

    # Step 2: Filter incoming rows for duplicates
    added_count = 0
    skipped_count = 0
    next_row_id = len(existing_rows) + 1

    for row in rows:
      _, serial_number, val1, val2 = row
      clean_serial = serial_number.strip().upper() if serial_number else ""

      if clean_serial:
        if clean_serial in existing_serials:
          print(
              f"  [Skipped Duplicate] Table '{table_name}': Serial"
              f" '{serial_number}' already exists."
          )
          skipped_count += 1
        else:
          v1_str = str(val1) if val1 is not None else ""
          v2_str = str(val2) if val2 is not None else ""
          existing_rows.append(
              (str(next_row_id), clean_serial, v1_str, v2_str)
          )
          existing_serials.add(clean_serial)
          next_row_id += 1
          added_count += 1

    # Step 3: Write the updated text ledger
    with open(txt_full_path, mode="w", newline="", encoding="utf-8") as f:
      writer = csv.writer(f)
      writer.writerows(existing_rows)

    print(
        f"-> Updated {txt_filename}: Added {added_count} new, Skipped"
        f" {skipped_count} duplicates."
    )

    # Step 4: Create table in database and import ONLY rows with valid serial numbers
    cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {table_name} (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                row_id INTEGER,
                serial_number TEXT,
                val1 INTEGER,
                val2 INTEGER
            )
        """)

    cursor.execute(f"DELETE FROM {table_name}")

    parsed_db_rows = []
    for r in existing_rows:
      r_id = int(r[0])
      serial = r[1]
      # REQUIREMENT: Only import into DB if the record has a valid serial number
      if serial and serial.strip() != "":
        v1 = int(r[2]) if r[2] != "" and r[2] is not None else None
        v2 = int(r[3]) if r[3] != "" and r[3] is not None else None
        parsed_db_rows.append((r_id, serial.strip().upper(), v1, v2))

    if parsed_db_rows:
      cursor.executemany(
          f"""
                INSERT INTO {table_name} (row_id, serial_number, val1, val2)
                VALUES (?, ?, ?, ?)
            """,
          parsed_db_rows,
      )
      print(
          f"-> Imported {len(parsed_db_rows)} valid records into table"
          f" '{table_name}'\n"
      )
    else:
      print(
          f"-> No valid serial numbers found for table '{table_name}'; table"
          f" left empty.\n"
      )

  conn.commit()
  conn.close()
  print("Process completed successfully!")


if __name__ == "__main__":
  process_with_duplicate_check(new_incoming_data)