import argparse
import os
import sqlite3
import openpyxl

def get_default_db():
    if os.path.exists("db.sqlite3"):
        return "db.sqlite3"
    elif os.path.exists("db.sqlite3"):
        return "db.sqlite3"
    elif os.path.exists(os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "db.sqlite3")):
        return os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "db.sqlite3")
    return "db.sqlite3"

def update_database(excel_file, db_file, table_name, ref_id, source_excel_column, target_db_column, ref_db_column="member_id"):
    if ref_db_column is None:
        ref_db_column = ref_id

    print(f"Loading file: {excel_file}...")
    try:
        if str(excel_file).lower().endswith('.csv'):
            import csv
            with open(excel_file, 'r', encoding='utf-8') as f:
                reader = csv.reader(f)
                rows = list(reader)
                if not rows:
                    print("Error: CSV file is empty.")
                    return
                headers = [str(col).strip() if col else f"COL_{idx}" for idx, col in enumerate(rows[0])]
                data_rows = rows[1:]
        else:
            wb = openpyxl.load_workbook(excel_file, data_only=True)
            ws = wb.active
            headers = [str(cell.value).strip() if cell.value is not None else f"COL_{idx}" for idx, cell in enumerate(ws[1])]
            data_rows = ws.iter_rows(min_row=2, values_only=True)
    except Exception as e:
        print(f"Error loading file: {e}")
        return

    if ref_id not in headers:
        print(f"Error: Reference column '{ref_id}' not found in Excel file. Available columns: {headers}")
        return
    if source_excel_column not in headers:
        print(f"Error: Source column '{source_excel_column}' not found in Excel file. Available columns: {headers}")
        return

    ref_col_idx = headers.index(ref_id)
    src_col_idx = headers.index(source_excel_column)

    print(f"Connecting to database: {db_file}...")
    if not os.path.exists(db_file):
        print(f"Error: Database file '{db_file}' not found.")
        return

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

    # Verify table and columns exist in DB
    try:
        cursor.execute(f"PRAGMA table_info({table_name})")
        columns = [col[1] for col in cursor.fetchall()]
        if not columns:
            print(f"Error: Table '{table_name}' does not exist in the database.")
            conn.close()
            return
        if ref_db_column not in columns:
            print(f"Error: Reference DB column '{ref_db_column}' not found in table '{table_name}'. Available columns: {columns}")
            conn.close()
            return
        if target_db_column not in columns:
            print(f"Error: Target DB column '{target_db_column}' not found in table '{table_name}'. Available columns: {columns}")
            conn.close()
            return
    except Exception as e:
        print(f"Error inspecting database table: {e}")
        conn.close()
        return

    # Loop through rows and update the database
    query = f"UPDATE {table_name} SET {target_db_column} = ? WHERE {ref_db_column} = ?"
    total_processed = 0
    total_updated = 0

    # Iterate starting from row 2 (data rows)
    for row in data_rows:
        if not row or ref_col_idx >= len(row) or src_col_idx >= len(row):
            continue

        val_ref = row[ref_col_idx]
        val_src = row[src_col_idx]

        if val_ref is None or str(val_ref).strip() == "":
            continue

        # Handle float values read from Excel integers (e.g., 101.0 -> '101' or 101)
        if isinstance(val_ref, float) and val_ref.is_integer():
            val_ref_int = int(val_ref)
            val_ref = str(val_ref_int)
        else:
            val_ref = str(val_ref).strip()

        # Execute update using both string and integer formats to maximize DB compatibility
        cursor.execute(query, (val_src, val_ref))
        if cursor.rowcount == 0 and val_ref.isdigit():
            cursor.execute(query, (val_src, int(val_ref)))

        total_processed += 1
        if cursor.rowcount > 0:
            total_updated += cursor.rowcount

    conn.commit()
    conn.close()

    print(f"Database update complete! Processed {total_processed} rows from Excel, updated {total_updated} rows in '{table_name}'.")

if __name__ == "__main__":
    import sys
    import openpyxl

    if "--list-tables" in sys.argv:
        db_idx = sys.argv.index("--db") + 1 if "--db" in sys.argv else None
        db_file = sys.argv[db_idx] if db_idx and db_idx < len(sys.argv) else get_default_db()
        
        if not os.path.exists(db_file):
            print(f"Error: Database file '{db_file}' not found.")
            sys.exit(1)
            
        print(f"Connecting to database: {db_file}...")
        conn = sqlite3.connect(db_file)
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
        tables = cursor.fetchall()
        print(f"\nTables in {db_file}:")
        for table in tables:
            print(f"  - {table[0]}")
        conn.close()
        sys.exit(0)

    if "--list-excel-columns" in sys.argv:
        excel_idx = sys.argv.index("--list-excel-columns") + 1
        if excel_idx < len(sys.argv) and not sys.argv[excel_idx].startswith("--"):
            excel_file = sys.argv[excel_idx]
        else:
            # Try to get it from the first positional argument
            excel_file = sys.argv[1] if len(sys.argv) > 1 and not sys.argv[1].startswith("--") else None

        if not excel_file or not os.path.exists(excel_file):
            print(f"Error: Please provide a valid Excel file path.")
            print(f"Usage: python update_column.py <excel_file> --list-excel-columns")
            sys.exit(1)
            
        print(f"Loading Excel file: {excel_file}...")
        try:
            wb = openpyxl.load_workbook(excel_file, data_only=True)
            ws = wb.active
            headers = [str(cell.value).strip() if cell.value is not None else f"COL_{idx}" for idx, cell in enumerate(ws[1])]
            print(f"\nColumns in {excel_file} (First Row):")
            for header in headers:
                print(f"  - {header}")
        except Exception as e:
            print(f"Error reading Excel file: {e}")
        sys.exit(0)

    parser = argparse.ArgumentParser(
        description="Update a single column in an SQLite table using values from an Excel column matched on ref_id. Use --list-tables or --list-excel-columns to inspect data."
    )
    parser.add_argument("excel_file", help="Path to the source Excel file (.xlsx or .xls)")
    parser.add_argument("table_name", help="Name of the SQLite database table to update (e.g., members_member)")
    parser.add_argument("ref_id", help="Column name used as matching reference ID in the Excel file")
    parser.add_argument("source_excel_column", help="Column name in Excel sheet containing new values")
    parser.add_argument("target_db_column", help="Column name in database table to be updated")
    parser.add_argument("--db", dest="db_file", default=get_default_db(), help="Path to SQLite database file (defaults to project db.sqlite3)")
    parser.add_argument("--ref-db-column", dest="ref_db_column", default=None, help="Column name used as matching reference ID in the database table (defaults to same as ref_id)")
    parser.add_argument("--list-tables", action="store_true", help="List all tables in the SQLite database and exit")
    parser.add_argument("--list-excel-columns", action="store_true", help="List all columns in the first row of the provided Excel file and exit")

    args = parser.parse_args()
    
    # Clean up arguments in case the user passed them as `key=value,`
    def clean_arg(val):
        if isinstance(val, str):
            val = val.rstrip(',')
            if '=' in val:
                val = val.split('=', 1)[1]
        return val

    print("crossed param")
    try:
        update_database(
            excel_file=clean_arg(args.excel_file),
            db_file=args.db_file,
            table_name=clean_arg(args.table_name),
            ref_id=clean_arg(args.ref_id),
            source_excel_column=clean_arg(args.source_excel_column),
            target_db_column=clean_arg(args.target_db_column),
            ref_db_column=clean_arg(args.ref_db_column)
        )
    except Exception as e:
        print(f"Error updating database: {e}")