# DbComparer/main.py - VERSIONE FINALE CORRETTA
import os
import sys
if hasattr(sys.stdout, 'reconfigure'):
    sys.stdout.reconfigure(encoding='utf-8', errors='replace')
if hasattr(sys.stderr, 'reconfigure'):
    sys.stderr.reconfigure(encoding='utf-8', errors='replace')
import argparse
from datetime import datetime
import warnings
import json
from dataclasses import dataclass
import threading
import re

# Import per l'interfaccia grafica
import tkinter as tk
from tkinter import ttk, filedialog, scrolledtext

import sqlite3
import pyodbc
import pandas as pd
import numpy as np

CONFIG_FILE = "dbcomparer_config.json"


@dataclass
class DbConfig:
    """Configurazione per una singola fonte dati."""
    db_type: str
    path: str
    table: str
    index_col: str | list[str]

    @property
    def index_cols(self) -> list[str]:
        return self.index_col if isinstance(self.index_col, list) else [self.index_col]

    @property
    def index_label(self) -> str:
        return ", ".join(self.index_cols)


def get_db_connection(db_type: str, db_path: str):
    """Crea e restituisce una connessione al database in base al tipo."""
    if not os.path.exists(db_path):
        raise FileNotFoundError(f"File database non trovato: {db_path}")

    if db_type == 'sqlite':
        return sqlite3.connect(db_path)
    if db_type == 'access':
        conn_str = (
            r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
            r'DBQ=' + db_path + ';'
        )
        return pyodbc.connect(conn_str)
    
    raise ValueError(f"Tipo di database non supportato: {db_type}")


def get_tables_from_db(db_type: str, db_path: str) -> list[str]:
    """Restituisce un elenco di tabelle da un database."""
    if not db_path or not os.path.exists(db_path):
        return []
    try:
        with get_db_connection(db_type, db_path) as conn:
            cursor = conn.cursor()
            if db_type == 'sqlite':
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
                tables = [row[0] for row in cursor.fetchall()]
            elif db_type == 'access':
                tables = [row.table_name for row in cursor.tables(tableType='TABLE')]
            return tables
    except Exception as e:
        print(f"ERRORE nel leggere le tabelle da {db_path}: {e}")
        return []


def get_primary_key(db_type: str, db_path: str, table_name: str) -> list[str]:
    """Rileva automaticamente la chiave primaria di una tabella. Restituisce lista di colonne (vuota se non trovata)."""
    if not db_path or not os.path.exists(db_path) or not table_name:
        return []
    try:
        with get_db_connection(db_type, db_path) as conn:
            cursor = conn.cursor()
            if db_type == 'sqlite':
                cursor.execute(f"PRAGMA table_info([{table_name}]);")
                pk_cols = [(row[5], row[1]) for row in cursor.fetchall() if row[5] > 0]
                return [col for _, col in sorted(pk_cols)]
            elif db_type == 'access':
                rows = list(cursor.statistics(table_name, unique=True))
                # Cerca indice named 'PrimaryKey' o il primo indice univoco
                pk_rows = [r for r in rows if r.index_name and r.non_unique == 0 and r.column_name]
                primary = [r for r in pk_rows if r.index_name.lower() == 'primarykey']
                candidates = primary if primary else pk_rows
                seen = {}
                for r in candidates:
                    if r.index_name not in seen:
                        seen[r.index_name] = []
                    seen[r.index_name].append(r.column_name)
                if seen:
                    return list(seen.values())[0]
    except Exception as e:
        print(f"Avviso: impossibile rilevare PK da {table_name}: {e}")
    return []


def get_columns_from_table(db_type: str, db_path: str, table_name: str) -> list[str]:
    """Restituisce un elenco di colonne da una tabella specifica."""
    if not db_path or not os.path.exists(db_path) or not table_name:
        return []
    try:
        with get_db_connection(db_type, db_path) as conn:
            cursor = conn.cursor()
            if db_type == 'sqlite':
                cursor.execute(f"PRAGMA table_info({table_name});")
                columns = [row[1] for row in cursor.fetchall()]
            elif db_type == 'access':
                # Metodo più robusto: leggi colonne da una query invece dei metadata
                # per evitare problemi di encoding UTF-16 corrotti
                try:
                    cursor.execute(f"SELECT TOP 1 * FROM [{table_name}]")
                    columns = [column[0] for column in cursor.description]
                except Exception as e_query:
                    # Fallback: prova con cursor.columns se la query fallisce
                    print(f"Avviso: query fallita per {table_name}, uso cursor.columns(): {e_query}")
                    columns = [row.column_name for row in cursor.columns(table=table_name)]
            return columns
    except Exception as e:
        print(f"ERRORE nel leggere le colonne da {table_name} in {db_path}: {e}")
        return []


def get_data(config: DbConfig, logger=print) -> tuple[pd.DataFrame, list[str]]:
    """
    Legge i dati da una fonte (SQLite o Access) e li restituisce come DataFrame.
    """
    # VALIDAZIONE INPUT
    if not config.path:
        raise ValueError(f"Percorso database {config.db_type.upper()} non specificato")
    if not config.table:
        raise ValueError(f"Nome tabella {config.db_type.upper()} non specificato")
    if not config.index_col:
        raise ValueError(f"Colonna indice {config.db_type.upper()} non specificata")

    logger(f"Lettura dati da {config.db_type.upper()}: {config.path} (Tabella: {config.table})")
    try:
        with get_db_connection(config.db_type, config.path) as conn:
            with warnings.catch_warnings():
                warnings.simplefilter("ignore", UserWarning)
                df = pd.read_sql_query(f"SELECT * FROM [{config.table}]", conn)

    except (pd.io.sql.DatabaseError, pyodbc.Error, sqlite3.Error) as e:
        raise ConnectionError(f"Impossibile leggere la tabella '{config.table}' da {config.db_type.upper()}: {e}")

    logger(f"Trovati {len(df)} record in {config.table}.")
    # Match case-insensitive: trova il nome reale della colonna nel DataFrame
    df_cols_lower = {c.lower(): c for c in df.columns}
    resolved = []
    missing = []
    for c in config.index_cols:
        real = df_cols_lower.get(c.lower())
        if real:
            resolved.append(real)
        else:
            missing.append(c)
    if missing:
        raise ValueError(
            f"Colonne indice {missing} non trovate nella tabella '{config.table}' del database {config.db_type.upper()}.\n"
            f"Colonne disponibili: {list(df.columns)}"
        )

    df.set_index(resolved if len(resolved) > 1 else resolved[0], inplace=True)
    if isinstance(df.index, pd.MultiIndex):
        df.index = pd.MultiIndex.from_tuples(
            [tuple(str(v).strip() for v in idx) for idx in df.index],
            names=df.index.names
        )
    else:
        df.index = df.index.astype(str).str.strip()

    return df, list(df.columns)


def _normalize_series(series: pd.Series) -> pd.Series:
    """Converte una serie a stringa, normalizzando i valori vuoti/nulli a una stringa vuota."""
    # astype(object) necessario per datetime64: fillna('') su datetime produce NaN float,
    # e NaN != NaN è sempre True causando falsi positivi su tutte le righe NULL.
    result = series.astype(object).fillna('').astype(str).str.strip()
    # Normalizza '123.0' → '123': pandas legge interi con NULL come float64
    result = result.str.replace(r'^(\d+)\.0$', r'\1', regex=True)
    return result


def _sanitize_for_report(value):
    """Pulisce un valore per la scrittura sicura nel report Excel."""
    if value is None or pd.isna(value):
        return "<NULL>"
    
    str_value = str(value).replace('\r', ' ').replace('\n', ' ')
    
    # Limita la lunghezza per evitare errori Excel
    if len(str_value) > 30000:
        str_value = str_value[:30000] + "... [TRONCATO]"
    
    # Rimuove caratteri problematici per Excel
    problematic_chars = ['\x00', '\x01', '\x02', '\x03', '\x04', '\x05', '\x06', '\x07', '\x08', 
                         '\x0b', '\x0c', '\x0e', '\x0f', '\x10', '\x11', '\x12', '\x13', '\x14',
                         '\x15', '\x16', '\x17', '\x18', '\x19', '\x1a', '\x1b', '\x1c', '\x1d', 
                         '\x1e', '\x1f']
    
    for char in problematic_chars:
        str_value = str_value.replace(char, ' ')
    
    return str_value


def _represent_value_for_report(original_value, normalized_value=None):
    """Rappresenta un valore nel report mostrando il valore originale in modo chiaro."""
    if original_value is None or pd.isna(original_value):
        return "<NULL>"
    
    try:
        orig_str = str(original_value).strip()
    except Exception:
        return "<ERRORE_CONVERSIONE>"
    
    if orig_str == "":
        return "<EMPTY>"
        
    if normalized_value is not None and orig_str != str(normalized_value).strip():
        result = f"{orig_str} (norm: '{normalized_value}')"
        if len(result) > 25000:
            return orig_str[:25000] + "... [TRONCATO]"
        return result
    
    if len(orig_str) > 30000:
        return orig_str[:30000] + "... [TRONCATO]"
    
    return orig_str


def _are_null_equivalents(val1, val2):
    """Verifica se due valori rappresentano entrambi l'assenza di informazione."""
    try:
        null_equivalents = {
            None, '', 'null', 'none', 'n/a', 'na', 'not available', 
            'not applicable', 'tbd', 'to be determined', 'unknown', 
            'undefined', '--', '---', '#n/a', '#null', 'nil', 'void'
        }
        
        def normalize_for_null_check(val):
            if pd.isna(val) or val is None:
                return None
            return str(val).lower().strip()
        
        norm_val1 = normalize_for_null_check(val1)
        norm_val2 = normalize_for_null_check(val2)
        
        if norm_val1 is None and norm_val2 is None:
            return True
        
        if norm_val1 is None and norm_val2 in null_equivalents:
            return True
        if norm_val2 is None and norm_val1 in null_equivalents:
            return True
        
        if norm_val1 in null_equivalents and norm_val2 in null_equivalents:
            return True
            
        return False
        
    except Exception:
        return False


def _are_dates_equivalent(val1, val2):
    """Verifica se due valori rappresentano la stessa data in formati diversi."""
    try:
        if pd.isna(val1) or pd.isna(val2) or val1 is None or val2 is None:
            return False
        
        str1 = str(val1).strip()
        str2 = str(val2).strip()
        
        if str1 == str2:
            return True
        
        date_patterns = [
            r'^\d{1,2}/\d{1,2}/\d{4}$',
            r'^\d{4}-\d{1,2}-\d{1,2}',
            r'^\d{1,2}-\d{1,2}-\d{4}',
        ]
        
        is_date1 = any(re.match(pattern, str1) for pattern in date_patterns)
        is_date2 = any(re.match(pattern, str2) for pattern in date_patterns)
        
        if not (is_date1 and is_date2):
            return False
        
        date_formats = [
            '%d/%m/%Y', '%m/%d/%Y',
            '%Y-%m-%d', '%Y-%m-%d %H:%M:%S',
            '%d-%m-%Y', '%m-%d-%Y',
        ]
        
        date1 = None
        date2 = None
        
        for fmt in date_formats:
            try:
                date1 = datetime.strptime(str1, fmt)
                break
            except ValueError:
                continue
        
        for fmt in date_formats:
            try:
                date2 = datetime.strptime(str2, fmt)
                break
            except ValueError:
                continue
        
        if date1 and date2:
            return date1.date() == date2.date()
        
        return False
        
    except Exception:
        return False


def _are_numbers_equivalent(val1, val2, tolerance=1e-9):
    """Verifica se due valori numerici sono equivalenti entro una certa tolleranza."""
    try:
        num1 = pd.to_numeric(val1, errors='raise')
        num2 = pd.to_numeric(val2, errors='raise')
        return abs(num1 - num2) < tolerance
    except (ValueError, TypeError):
        return False


def _are_texts_substantially_equal(val1, val2):
    """Verifica se due testi sono sostanzialmente uguali (uno potrebbe essere troncato)."""
    try:
        if pd.isna(val1) or pd.isna(val2) or val1 is None or val2 is None:
            return False
        
        str1 = str(val1).strip()
        str2 = str(val2).strip()
        
        if str1 == str2:
            return True
        
        if len(str1) > len(str2) and len(str2) > 50:
            return str1.startswith(str2)
        elif len(str2) > len(str1) and len(str1) > 50:
            return str2.startswith(str1)
        
        return False
    except Exception:
        return False


def _classify_difference(val1, val2, val1_norm, val2_norm):
    """Classifica il tipo di differenza per il reporting."""
    val1_is_null = val1 is None or pd.isna(val1)
    val2_is_null = val2 is None or pd.isna(val2)
    
    if val1_is_null and val2_is_null:
        return "BOTH_NULL"
    elif val1_is_null and not val2_is_null:
        return "NULL_vs_VALUE"
    elif not val1_is_null and val2_is_null:
        return "VALUE_vs_NULL"
    else:
        val1_str = str(val1).strip()
        val2_str = str(val2).strip()
        
        if val1_str == val2_str:
            return "WHITESPACE_DIFF"
        else:
            return "VALUE_DIFF"


def run_comparison(sqlite_config: DbConfig, access_config: DbConfig, manual_mappings: list = None, 
                  smart_options: dict = None, logger=print):
    """Esegue la logica di confronto completa e logga i risultati."""
    try:
        # 1. Caricamento dati
        df_sqlite, sqlite_cols = get_data(sqlite_config, logger)
        df_access, access_cols = get_data(access_config, logger)

        # Controllo unicità indice — obbligatorio per un confronto corretto
        if not df_sqlite.index.is_unique:
            dup = df_sqlite.index.duplicated().sum()
            raise ValueError(
                f"La chiave '{sqlite_config.index_label}' NON è univoca in DB1 "
                f"({dup} duplicati su {len(df_sqlite)} righe).\n"
                f"Scegli una colonna (o combinazione di colonne) che identifichi univocamente ogni riga."
            )
        if not df_access.index.is_unique:
            dup = df_access.index.duplicated().sum()
            raise ValueError(
                f"La chiave '{access_config.index_label}' NON è univoca in DB2 "
                f"({dup} duplicati su {len(df_access)} righe).\n"
                f"Scegli una colonna (o combinazione di colonne) che identifichi univocamente ogni riga."
            )

        # 2. Riepilogo a livello di record
        logger("\n--- RIEPILOGO CONTEGGIO RECORD ---")
        sqlite_ids = set(df_sqlite.index)
        access_ids = set(df_access.index)
        common_ids = sqlite_ids.intersection(access_ids)
        only_in_sqlite = sqlite_ids.difference(access_ids)
        only_in_access = access_ids.difference(sqlite_ids)
        logger(f"Record Totali in SQLite: {len(sqlite_ids)}")
        logger(f"Record Totali in Access: {len(access_ids)}")
        logger(f"Record in Comune: {len(common_ids)}")
        logger(f"Record SOLO in SQLite: {len(only_in_sqlite)}")
        if only_in_sqlite: logger(f"  Esempi: {list(only_in_sqlite)[:5]}")
        logger(f"Record SOLO in Access: {len(only_in_access)}")
        if only_in_access: logger(f"  Esempi: {list(only_in_access)[:5]}")

        # 3. Mappatura colonne
        logger("\n--- COSTRUZIONE MAPPA DI CONFRONTO AUTOMATICA ---")
        sqlite_cols_norm = {c.lower(): c for c in sqlite_cols}
        access_cols_norm = {c.lower(): c for c in access_cols}
        common_cols_norm = set(sqlite_cols_norm.keys()).intersection(set(access_cols_norm.keys()))
        for c in sqlite_config.index_cols:
            common_cols_norm.discard(c.lower())
        for c in access_config.index_cols:
            common_cols_norm.discard(c.lower())
        
        column_mapping = {sqlite_cols_norm[c_norm]: access_cols_norm[c_norm] for c_norm in common_cols_norm}
        logger(f"Trovate {len(column_mapping)} colonne comuni con mappatura automatica.")

        if manual_mappings:
            logger("Applicazione di mappature manuali...")
            for sqlite_col, access_col in manual_mappings:
                if sqlite_col and access_col:
                    column_mapping[sqlite_col] = access_col
                    logger(f"  - Mappato manualmente: '{sqlite_col}' -> '{access_col}'")

        unmatched_sqlite_norm = set(sqlite_cols_norm.keys()) - {c.lower() for c in column_mapping.keys()} - {c.lower() for c in sqlite_config.index_cols}
        unmatched_access_norm = set(access_cols_norm.keys()) - {c.lower() for c in column_mapping.values()} - {c.lower() for c in access_config.index_cols}

        if unmatched_sqlite_norm:
            original_case_cols = [sqlite_cols_norm[c] for c in unmatched_sqlite_norm]
            logger(f"  - Colonne SOLO in SQLite: {original_case_cols}")
        if unmatched_access_norm:
            original_case_cols = [access_cols_norm[c] for c in unmatched_access_norm]
            logger(f"  - Colonne SOLO in Access: {original_case_cols}")

        # 4. Confronto campo per campo
        logger("\n--- CONFRONTO CAMPO PER CAMPO (SUI RECORD E COLONNE COMUNI) ---")
        discrepancies = []
        if not common_ids:
            logger("Nessun record in comune. Salto del confronto campo per campo.")
        else:
            df_common_sqlite = df_sqlite.loc[list(common_ids)].copy()
            df_common_access = df_access.loc[list(common_ids)].copy()

            for sqlite_col, access_col in column_mapping.items():
                logger(f"Confronto: '{sqlite_col}' (SQLite) vs '{access_col}' (Access)...")
                
                s1 = df_common_sqlite[sqlite_col]
                s2 = df_common_access[access_col]

                s1_norm = _normalize_series(s1)
                s2_norm = _normalize_series(s2)
                diff_mask = s1_norm != s2_norm

                # Se vengono trovate differenze, esegui controlli intelligenti aggiuntivi
                if diff_mask.any():
                    smart_exceptions = pd.Series(False, index=diff_mask.index)
                    different_indices = diff_mask[diff_mask].index
                    
                    smart_opts = smart_options or {}
                    
                    for idx in different_indices:
                        val1, val2 = s1.loc[idx], s2.loc[idx]
                        
                        if smart_opts.get('ignore_null_equivalents', True) and _are_null_equivalents(val1, val2):
                            smart_exceptions.loc[idx] = True
                            continue
                        
                        if smart_opts.get('ignore_date_formats', True) and _are_dates_equivalent(val1, val2):
                            smart_exceptions.loc[idx] = True
                            continue
                        
                        if smart_opts.get('ignore_number_precision', True) and _are_numbers_equivalent(val1, val2):
                            smart_exceptions.loc[idx] = True
                            continue
                        
                        if smart_opts.get('ignore_text_truncation', True) and _are_texts_substantially_equal(val1, val2):
                            smart_exceptions.loc[idx] = True
                            continue
                    
                    diff_mask = diff_mask & ~smart_exceptions
                    
                    if smart_exceptions.any():
                        logger(f"    Ignorate {smart_exceptions.sum()} differenze di formato (date/numeri/troncamenti/null equivalenti)")

                if diff_mask.any():
                    logger(f"  -> TROVATE {diff_mask.sum()} DISCREPANZE!")
                    for record_id in diff_mask[diff_mask].index:
                        try:
                            val1_orig = s1.loc[record_id]
                            val2_orig = s2.loc[record_id]
                            val1_norm = s1_norm.loc[record_id]
                            val2_norm = s2_norm.loc[record_id]

                            # Usa DB1/DB2 per evitare conflitti quando i tipi sono uguali
                            id_str = " | ".join(str(v) for v in record_id) if isinstance(record_id, tuple) else str(record_id)
                            discrepancy_record = {
                                'ID': _sanitize_for_report(id_str),
                                'Colonna': _sanitize_for_report(sqlite_col),
                                f'Valore_DB1 ({sqlite_config.db_type.upper()})': _sanitize_for_report(_represent_value_for_report(val1_orig, val1_norm)),
                                f'Valore_DB2 ({access_config.db_type.upper()})': _sanitize_for_report(_represent_value_for_report(val2_orig, val2_norm))
                            }
                            
                            discrepancies.append(discrepancy_record)
                            
                        except Exception as e_inner:
                            logger(f"    [!!!] ERRORE INTERNO per ID {record_id}: {e_inner}")
                            discrepancies.append({
                                'ID': str(record_id),
                                'Colonna': str(sqlite_col),
                                f'Valore_DB1 ({sqlite_config.db_type.upper()})': f"<ERRORE: {str(e_inner)[:100]}>",
                                f'Valore_DB2 ({access_config.db_type.upper()})': f"<ERRORE: {str(e_inner)[:100]}>"
                            })
                else:
                    logger("  -> OK. Nessuna discrepanza.")

        # 5. Genera Report
        if discrepancies:
            logger(f"\n--- RILEVATE {len(discrepancies)} DISCREPANZE! ---")
            
            logger("Prime 3 discrepanze:")
            for i, disc in enumerate(discrepancies[:3]):
                logger(f"  {i+1}. ID={disc.get('ID')}, Colonna={disc.get('Colonna')}")
                sqlite_val = str(disc.get(f'Valore_{sqlite_config.db_type.upper()}', ''))[:100]
                access_val = str(disc.get(f'Valore_{access_config.db_type.upper()}', ''))[:100]
                logger(f"      SQLite={sqlite_val}{'...' if len(sqlite_val) >= 100 else ''}")
                logger(f"      Access={access_val}{'...' if len(access_val) >= 100 else ''}")
            
            try:
                df_report = pd.DataFrame(discrepancies)
                logger(f"DataFrame creato con shape: {df_report.shape}")
                logger(f"Colonne nel DataFrame: {list(df_report.columns)}")

                # Calcola conteggio discrepanze per colonna
                column_counts = df_report['Colonna'].value_counts().reset_index()
                column_counts.columns = ['Colonna', 'Numero_Discrepanze']
                total_discrepancies = len(df_report)
                column_counts['Percentuale'] = (column_counts['Numero_Discrepanze'] / total_discrepancies * 100).round(2)
                column_counts['Percentuale'] = column_counts['Percentuale'].astype(str) + '%'

                timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

                # Excel ha un limite di 1.048.576 righe per foglio
                EXCEL_MAX_ROWS = 1048576
                num_rows = len(df_report)

                if num_rows >= EXCEL_MAX_ROWS:
                    logger(f"\nATTENZIONE: {num_rows:,} discrepanze superano il limite Excel di {EXCEL_MAX_ROWS:,} righe.")
                    logger("Salvataggio diretto in formato CSV...")
                    csv_path = f"confronto_{sqlite_config.db_type}_{sqlite_config.table}_vs_{access_config.db_type}_{access_config.table}_{timestamp}.csv"
                    df_report.to_csv(csv_path, index=False, encoding='utf-8-sig')
                    logger(f"Report completo salvato come CSV in: {csv_path}")

                    # Crea anche un file Excel di riepilogo (senza le discrepanze dettagliate)
                    summary_path = f"riepilogo_{sqlite_config.db_type}_{sqlite_config.table}_vs_{access_config.db_type}_{access_config.table}_{timestamp}.xlsx"
                    try:
                        with pd.ExcelWriter(summary_path, engine='openpyxl') as writer:
                            # Riepilogo
                            summary_data = {
                                'Metrica': [
                                    f'Record Totali DB1 ({sqlite_config.db_type.upper()})',
                                    f'Record Totali DB2 ({access_config.db_type.upper()})',
                                    'Record in Comune',
                                    f'Record Solo DB1 ({sqlite_config.db_type.upper()})',
                                    f'Record Solo DB2 ({access_config.db_type.upper()})',
                                    'Discrepanze Trovate',
                                    'Colonne Mappate',
                                    f'Colonne Solo DB1 ({sqlite_config.db_type.upper()})',
                                    f'Colonne Solo DB2 ({access_config.db_type.upper()})'
                                ],
                                'Valore': [
                                    len(sqlite_ids),
                                    len(access_ids),
                                    len(common_ids),
                                    len(only_in_sqlite),
                                    len(only_in_access),
                                    len(discrepancies),
                                    len(column_mapping),
                                    len(unmatched_sqlite_norm),
                                    len(unmatched_access_norm)
                                ]
                            }
                            pd.DataFrame(summary_data).to_excel(writer, sheet_name='Riepilogo', index=False)

                            # Foglio discrepanze per colonna
                            if not column_counts.empty:
                                column_counts.to_excel(writer, sheet_name='Discrepanze_per_Colonna', index=False)

                            # Colonne non mappate
                            if unmatched_sqlite_norm or unmatched_access_norm:
                                unmapped_data = []
                                sqlite_only_cols = [sqlite_cols_norm[c] for c in unmatched_sqlite_norm]
                                for col in sqlite_only_cols:
                                    unmapped_data.append({
                                        'Database': f'DB1 ({sqlite_config.db_type.upper()})',
                                        'Colonna': col,
                                        'Stato': f'Non presente in DB2 ({access_config.db_type.upper()})'
                                    })
                                access_only_cols = [access_cols_norm[c] for c in unmatched_access_norm]
                                for col in access_only_cols:
                                    unmapped_data.append({
                                        'Database': f'DB2 ({access_config.db_type.upper()})',
                                        'Colonna': col,
                                        'Stato': f'Non presente in DB1 ({sqlite_config.db_type.upper()})'
                                    })
                                if unmapped_data:
                                    df_unmapped = pd.DataFrame(unmapped_data)
                                    df_unmapped.to_excel(writer, sheet_name='Colonne_Non_Mappate', index=False)

                        logger(f"File Excel di riepilogo salvato in: {summary_path}")
                    except Exception as summary_error:
                        logger(f"ERRORE nel salvare il riepilogo Excel: {summary_error}")

                else:
                    # Numero di righe accettabile, crea file Excel normale
                    report_path = f"confronto_{sqlite_config.db_type}_{sqlite_config.table}_vs_{access_config.db_type}_{access_config.table}_{timestamp}.xlsx"
                    try:
                        with pd.ExcelWriter(report_path, engine='openpyxl') as writer:
                            df_report.to_excel(writer, sheet_name='Discrepanze', index=False)

                            # Riepilogo base con etichette dinamiche
                            summary_data = {
                                'Metrica': [
                                    f'Record Totali DB1 ({sqlite_config.db_type.upper()})',
                                    f'Record Totali DB2 ({access_config.db_type.upper()})',
                                    'Record in Comune',
                                    f'Record Solo DB1 ({sqlite_config.db_type.upper()})',
                                    f'Record Solo DB2 ({access_config.db_type.upper()})',
                                    'Discrepanze Trovate',
                                    'Colonne Mappate',
                                    f'Colonne Solo DB1 ({sqlite_config.db_type.upper()})',
                                    f'Colonne Solo DB2 ({access_config.db_type.upper()})'
                                ],
                                'Valore': [
                                    len(sqlite_ids),
                                    len(access_ids),
                                    len(common_ids),
                                    len(only_in_sqlite),
                                    len(only_in_access),
                                    len(discrepancies),
                                    len(column_mapping),
                                    len(unmatched_sqlite_norm),
                                    len(unmatched_access_norm)
                                ]
                            }
                            pd.DataFrame(summary_data).to_excel(writer, sheet_name='Riepilogo', index=False)

                            # Foglio discrepanze per colonna
                            if not column_counts.empty:
                                column_counts.to_excel(writer, sheet_name='Discrepanze_per_Colonna', index=False)

                            # Foglio dettaglio colonne non mappate
                            if unmatched_sqlite_norm or unmatched_access_norm:
                                unmapped_data = []

                                # Colonne solo in DB1
                                sqlite_only_cols = [sqlite_cols_norm[c] for c in unmatched_sqlite_norm]
                                for col in sqlite_only_cols:
                                    unmapped_data.append({
                                        'Database': f'DB1 ({sqlite_config.db_type.upper()})',
                                        'Colonna': col,
                                        'Stato': f'Non presente in DB2 ({access_config.db_type.upper()})'
                                    })

                                # Colonne solo in DB2
                                access_only_cols = [access_cols_norm[c] for c in unmatched_access_norm]
                                for col in access_only_cols:
                                    unmapped_data.append({
                                        'Database': f'DB2 ({access_config.db_type.upper()})',
                                        'Colonna': col,
                                        'Stato': f'Non presente in DB1 ({sqlite_config.db_type.upper()})'
                                    })

                                if unmapped_data:
                                    df_unmapped = pd.DataFrame(unmapped_data)
                                    df_unmapped.to_excel(writer, sheet_name='Colonne_Non_Mappate', index=False)

                        logger(f"Report delle discrepanze salvato in: {report_path}")

                    except Exception as excel_error:
                        logger(f"ERRORE nel salvare il file Excel: {excel_error}")
                        logger("Tentativo di salvataggio in formato CSV...")

                        csv_path = f"confronto_{sqlite_config.db_type}_{sqlite_config.table}_vs_{access_config.db_type}_{access_config.table}_{timestamp}.csv"
                        try:
                            df_report.to_csv(csv_path, index=False, encoding='utf-8-sig')
                            logger(f"Report salvato come CSV in: {csv_path}")
                        except Exception as csv_error:
                            logger(f"ERRORE anche nel salvare il CSV: {csv_error}")
                            logger("Salvataggio report fallito.")
                        
            except Exception as df_error:
                logger(f"ERRORE nella creazione del DataFrame: {df_error}")
                logger("Impossibile creare il report.")
        else:
            logger("\n--- NESSUNA DISCREPANZA TROVATA! CONGRATULAZIONI! ---")

    except (FileNotFoundError, ValueError, ConnectionError, Exception) as e:
        logger(f"\n!!! ERRORE DURANTE L'ESECUZIONE: {e} !!!")


def save_app_config(config_data):
    """Salva la configurazione dell'app su un file JSON."""
    try:
        with open(CONFIG_FILE, 'w') as f:
            json.dump(config_data, f, indent=4)
    except Exception as e:
        print(f"Errore durante il salvataggio della configurazione: {e}")


def load_app_config():
    """Carica la configurazione dell'app da un file JSON."""
    if not os.path.exists(CONFIG_FILE):
        return {}
    try:
        with open(CONFIG_FILE, 'r') as f:
            return json.load(f)
    except Exception as e:
        print(f"Errore durante il caricamento della configurazione: {e}")
        return {}


class App(tk.Tk):
    def __init__(self):
        super().__init__()
        self.title("DbComparer - Versione Finale con Confronto Intelligente")
        self.geometry("900x700")

        # Frame principale
        main_frame = ttk.Frame(self, padding="10")
        main_frame.pack(fill=tk.BOTH, expand=True)

        # --- Sezione Database 1 ---
        self.db1_frame = ttk.LabelFrame(main_frame, text="Database 1", padding="10")
        self.db1_frame.pack(fill=tk.X, pady=5)
        self.db1_type = tk.StringVar(value="sqlite")
        self.db1_path = tk.StringVar()
        self.db1_table = tk.StringVar()
        self.db1_index = tk.StringVar()
        self.create_db_widgets(self.db1_frame, "db1", self.db1_type, self.db1_path, self.db1_table, self.db1_index)

        # --- Sezione Database 2 ---
        self.db2_frame = ttk.LabelFrame(main_frame, text="Database 2", padding="10")
        self.db2_frame.pack(fill=tk.X, pady=5)
        self.db2_type = tk.StringVar(value="access")
        self.db2_path = tk.StringVar()
        self.db2_table = tk.StringVar()
        self.db2_index = tk.StringVar()
        self.create_db_widgets(self.db2_frame, "db2", self.db2_type, self.db2_path, self.db2_table, self.db2_index)

        # --- Opzioni di Confronto Intelligente ---
        self.smart_comparison_frame = ttk.LabelFrame(main_frame, text="Opzioni Confronto Intelligente", padding="10")
        self.smart_comparison_frame.pack(fill=tk.X, pady=5)
        
        self.ignore_date_formats = tk.BooleanVar(value=True)
        self.ignore_number_precision = tk.BooleanVar(value=True) 
        self.ignore_text_truncation = tk.BooleanVar(value=True)
        self.ignore_null_equivalents = tk.BooleanVar(value=True)
        
        ttk.Checkbutton(self.smart_comparison_frame, 
                       text="Ignora differenze di formato delle date (30/05/2021 ≈ 2021-05-30)", 
                       variable=self.ignore_date_formats).pack(anchor=tk.W)
        ttk.Checkbutton(self.smart_comparison_frame, 
                       text="Ignora differenze di precisione numerica (1.0 ≈ 1.000)", 
                       variable=self.ignore_number_precision).pack(anchor=tk.W)
        ttk.Checkbutton(self.smart_comparison_frame, 
                       text="Ignora troncamenti di testo lunghi", 
                       variable=self.ignore_text_truncation).pack(anchor=tk.W)
        ttk.Checkbutton(self.smart_comparison_frame, 
                       text="Ignora valori null equivalenti (NULL ≈ N/A ≈ TBD)", 
                       variable=self.ignore_null_equivalents).pack(anchor=tk.W)

        # --- Sezione Mappatura Manuale ---
        self.mapping_frame = ttk.LabelFrame(main_frame, text="Mappatura Manuale Colonne", padding="10")
        self.mapping_frame.pack(fill=tk.X, pady=5)
        self.manual_mappings_container = ttk.Frame(self.mapping_frame)
        self.manual_mappings_container.pack(fill=tk.X, expand=True)
        ttk.Button(self.mapping_frame, text="Aggiungi Mappatura", command=self.add_mapping_row).pack(pady=5)
        self.manual_mappings = []

        # --- Pulsante e Output ---
        self.compare_button = ttk.Button(main_frame, text="Confronta Database", command=self.start_comparison)
        self.compare_button.pack(pady=10)

        self.output_text = scrolledtext.ScrolledText(main_frame, wrap=tk.WORD, height=15)
        self.output_text.pack(fill=tk.BOTH, expand=True)

        self.load_initial_config()

        self.protocol("WM_DELETE_WINDOW", self.on_closing)

    def create_db_widgets(self, parent, db_label, type_var, path_var, table_var, index_var):
        # Riga Tipo Database
        ttk.Label(parent, text="Tipo:").grid(row=0, column=0, sticky=tk.W, padx=5, pady=2)
        type_combo = ttk.Combobox(parent, textvariable=type_var, values=["sqlite", "access"], state="readonly", width=15)
        type_combo.grid(row=0, column=1, sticky=tk.W, pady=2)
        parent.type_combo = type_combo

        # Riga File
        ttk.Label(parent, text="File DB:").grid(row=1, column=0, sticky=tk.W, padx=5, pady=2)
        ttk.Entry(parent, textvariable=path_var, width=60).grid(row=1, column=1, sticky=tk.EW)
        ttk.Button(parent, text="Sfoglia...", command=lambda: self.browse_file(parent, db_label, type_var, path_var, table_var, index_var)).grid(row=1, column=2, padx=5)

        # Riga Tabella
        ttk.Label(parent, text="Tabella:").grid(row=2, column=0, sticky=tk.W, padx=5, pady=2)
        table_combo = ttk.Combobox(parent, textvariable=table_var, state="readonly")
        table_combo.grid(row=2, column=1, sticky=tk.EW)
        parent.table_combo = table_combo
        table_combo.bind("<<ComboboxSelected>>", lambda event: self.update_columns_for_table(parent, db_label, type_var, path_var, table_var, index_var))

        # Riga Indice (Entry editabile: supporta chiave composta "col1, col2, col3")
        ttk.Label(parent, text="Colonna Indice:").grid(row=3, column=0, sticky=tk.W, padx=5, pady=2)
        index_entry = ttk.Entry(parent, textvariable=index_var)
        index_entry.grid(row=3, column=1, sticky=tk.EW)
        parent.index_combo = index_entry  # mantenuto come .index_combo per compatibilità

        parent.columnconfigure(1, weight=1)

    def browse_file(self, parent_frame, db_label, type_var, path_var, table_var, index_var):
        # Ottieni il tipo di database selezionato
        db_type = type_var.get()

        # Imposta i filtri file in base al tipo
        if db_type == "sqlite":
            filetypes = [("SQLite DB", "*.db;*.sqlite;*.sqlite3"), ("All files", "*.*")]
        elif db_type == "access":
            filetypes = [("Access DB", "*.accdb;*.mdb"), ("All files", "*.*")]
        else:
            filetypes = [("All files", "*.*")]

        filepath = filedialog.askopenfilename(filetypes=filetypes)
        if filepath:
            path_var.set(filepath)
            self.log_message(f"Caricamento tabelle da {db_type.upper()}: {filepath}")

            tables = get_tables_from_db(db_type, filepath)
            if not tables:
                self.log_message(f"ATTENZIONE: Nessuna tabella trovata in {filepath}")
                parent_frame.table_combo['values'] = []
                table_var.set("")
                index_var.set("")
                return

            parent_frame.table_combo['values'] = tables
            self.log_message(f"Trovate {len(tables)} tabelle: {', '.join(tables[:5])}{'...' if len(tables) > 5 else ''}")

            if tables:
                table_var.set(tables[0])
                self.update_columns_for_table(parent_frame, db_label, type_var, path_var, table_var, index_var)

    def update_columns_for_table(self, parent_frame, db_label, type_var, path_var, table_var, index_var_tk_var):
        """Aggiorna il combobox delle colonne indice in base alla tabella selezionata."""
        db_type = type_var.get()
        db_path = path_var.get()
        table_name = table_var.get()

        columns = get_columns_from_table(db_type, db_path, table_name)

        current_index_val = index_var_tk_var.get()
        current_cols = [c.strip() for c in current_index_val.split(",")] if current_index_val else []
        if current_index_val and all(c in columns for c in current_cols):
            pass  # mantieni il valore corrente
        else:
            pk = get_primary_key(db_type, db_path, table_name)
            if pk and all(c in columns for c in pk):
                index_var_tk_var.set(", ".join(pk))
                if len(pk) > 1:
                    self.log_message(f"Chiave primaria composta rilevata automaticamente: {pk}")
                else:
                    self.log_message(f"Chiave primaria rilevata automaticamente: {pk[0]}")
            else:
                index_var_tk_var.set("")
                if columns:
                    self.log_message(f"Nessuna chiave primaria trovata per '{table_name}'. Seleziona manualmente la colonna indice.")

        for mapping_info in self.manual_mappings[:]:
            self.remove_mapping_row(mapping_info['frame'])

    def add_mapping_row(self, sqlite_col_val=None, access_col_val=None):
        """Aggiunge una riga per la mappatura manuale alla GUI."""
        row_frame = ttk.Frame(self.manual_mappings_container)
        row_frame.pack(fill=tk.X, pady=2)

        sqlite_map_var = tk.StringVar()
        if sqlite_col_val: sqlite_map_var.set(sqlite_col_val)
        
        access_map_var = tk.StringVar()
        if access_col_val: access_map_var.set(access_col_val)

        sqlite_cols, access_cols = self._get_available_columns_for_mapping()
        sqlite_combo = ttk.Combobox(row_frame, textvariable=sqlite_map_var, values=sqlite_cols, state="readonly")
        sqlite_combo.pack(side=tk.LEFT, fill=tk.X, expand=True, padx=5)

        ttk.Label(row_frame, text="->").pack(side=tk.LEFT)

        access_combo = ttk.Combobox(row_frame, textvariable=access_map_var, values=access_cols, state="readonly")
        access_combo.pack(side=tk.LEFT, fill=tk.X, expand=True, padx=5)

        remove_button = ttk.Button(row_frame, text="X", width=3, command=lambda: self.remove_mapping_row(row_frame))
        remove_button.pack(side=tk.RIGHT)

        mapping_info = {'frame': row_frame, 'sqlite_var': sqlite_map_var, 'access_var': access_map_var}
        self.manual_mappings.append(mapping_info)

    def remove_mapping_row(self, row_frame):
        """Rimuove una riga di mappatura dalla GUI e dalla lista interna."""
        self.manual_mappings = [m for m in self.manual_mappings if m['frame'] != row_frame]
        row_frame.destroy()

    def _get_available_columns_for_mapping(self):
        """Restituisce le colonne disponibili per la mappatura, escludendo quelle indice."""
        all_db1_cols = get_columns_from_table(self.db1_type.get(), self.db1_path.get(), self.db1_table.get())
        all_db2_cols = get_columns_from_table(self.db2_type.get(), self.db2_path.get(), self.db2_table.get())

        if not all_db1_cols or not all_db2_cols:
            return [], []

        # Ottieni le colonne indice correnti
        db1_index_col = self.db1_index.get()
        db2_index_col = self.db2_index.get()

        # Mappature automatiche (escludendo già le colonne indice)
        db1_norm = {c.lower(): c for c in all_db1_cols if c != db1_index_col}
        db2_norm = {c.lower(): c for c in all_db2_cols if c != db2_index_col}
        auto_mapped_db1 = {db1_norm[c] for c in db1_norm if c in db2_norm}
        auto_mapped_db2 = {db2_norm[c] for c in db2_norm if c in db1_norm}

        # Mappature manuali esistenti
        manual_mapped_db1 = {m['sqlite_var'].get() for m in self.manual_mappings if m['sqlite_var'].get()}
        manual_mapped_db2 = {m['access_var'].get() for m in self.manual_mappings if m['access_var'].get()}

        # Filtra le colonne disponibili escludendo indice, auto-mappate e già mappate manualmente
        available_db1 = sorted([c for c in all_db1_cols
                                 if c != db1_index_col
                                 and c not in auto_mapped_db1
                                 and c not in manual_mapped_db1])
        available_db2 = sorted([c for c in all_db2_cols
                                 if c != db2_index_col
                                 and c not in auto_mapped_db2
                                 and c not in manual_mapped_db2])

        return available_db1, available_db2

    def load_initial_config(self):
        """Carica la configurazione iniziale e popola i campi della GUI."""
        config = load_app_config()
        if not config:
            return

        def index_to_str(val) -> str:
            if isinstance(val, list):
                return ", ".join(val)
            return str(val) if val else ''

        # Carica DB1 (con retrocompatibilità per vecchi config "sqlite")
        if 'db1' in config:
            db1_cfg = config['db1']
        elif 'sqlite' in config:  # Retrocompatibilità
            db1_cfg = config['sqlite']
            db1_cfg['type'] = 'sqlite'
        else:
            db1_cfg = None

        if db1_cfg:
            self.db1_type.set(db1_cfg.get('type', 'sqlite'))
            self.db1_path.set(db1_cfg.get('path', ''))
            if self.db1_path.get():
                db_type = self.db1_type.get()
                tables = get_tables_from_db(db_type, self.db1_path.get())
                self.db1_frame.table_combo['values'] = tables
                if db1_cfg.get('table') in tables:
                    self.db1_table.set(db1_cfg.get('table'))
                    self.update_columns_for_table(self.db1_frame, 'db1', self.db1_type, self.db1_path, self.db1_table, self.db1_index)
                    self.db1_index.set(index_to_str(db1_cfg.get('index', '')))

        # Carica DB2 (con retrocompatibilità per vecchi config "access")
        if 'db2' in config:
            db2_cfg = config['db2']
        elif 'access' in config:  # Retrocompatibilità
            db2_cfg = config['access']
            db2_cfg['type'] = 'access'
        else:
            db2_cfg = None

        if db2_cfg:
            self.db2_type.set(db2_cfg.get('type', 'access'))
            self.db2_path.set(db2_cfg.get('path', ''))
            if self.db2_path.get():
                db_type = self.db2_type.get()
                tables = get_tables_from_db(db_type, self.db2_path.get())
                self.db2_frame.table_combo['values'] = tables
                if db2_cfg.get('table') in tables:
                    self.db2_table.set(db2_cfg.get('table'))
                    self.update_columns_for_table(self.db2_frame, 'db2', self.db2_type, self.db2_path, self.db2_table, self.db2_index)
                    self.db2_index.set(index_to_str(db2_cfg.get('index', '')))

        if 'manual_mappings' in config:
            for mapping in config['manual_mappings']:
                self.add_mapping_row(mapping.get('sqlite_col'), mapping.get('access_col'))

        if 'smart_options' in config:
            smart_opts = config['smart_options']
            self.ignore_date_formats.set(smart_opts.get('ignore_date_formats', True))
            self.ignore_number_precision.set(smart_opts.get('ignore_number_precision', True))
            self.ignore_text_truncation.set(smart_opts.get('ignore_text_truncation', True))
            self.ignore_null_equivalents.set(smart_opts.get('ignore_null_equivalents', True))

    def log_message(self, message):
        self.output_text.insert(tk.END, message + "\n")
        self.output_text.see(tk.END)
        self.update_idletasks()

    def start_comparison(self):
        self.compare_button.config(state="disabled")
        self.output_text.delete(1.0, tk.END)
        self.log_message("Avvio del confronto...")

        try:
            # VALIDAZIONE INPUT
            if not self.db1_path.get():
                raise ValueError("Seleziona il file database 1")
            if not self.db1_table.get():
                raise ValueError("Seleziona la tabella per database 1")
            if not self.db1_index.get():
                raise ValueError("Seleziona la colonna indice per database 1")
            if not self.db2_path.get():
                raise ValueError("Seleziona il file database 2")
            if not self.db2_table.get():
                raise ValueError("Seleziona la tabella per database 2")
            if not self.db2_index.get():
                raise ValueError("Seleziona la colonna indice per database 2")

            def parse_index(raw: str) -> str | list[str]:
                parts = [c.strip() for c in raw.split(",") if c.strip()]
                return parts if len(parts) > 1 else parts[0]

            db1_config = DbConfig(self.db1_type.get(), self.db1_path.get(), self.db1_table.get(), parse_index(self.db1_index.get()))
            db2_config = DbConfig(self.db2_type.get(), self.db2_path.get(), self.db2_table.get(), parse_index(self.db2_index.get()))

            manual_mappings_data = [
                (m['sqlite_var'].get(), m['access_var'].get()) for m in self.manual_mappings
            ]

            smart_options_data = {
                'ignore_date_formats': self.ignore_date_formats.get(),
                'ignore_number_precision': self.ignore_number_precision.get(),
                'ignore_text_truncation': self.ignore_text_truncation.get(),
                'ignore_null_equivalents': self.ignore_null_equivalents.get()
            }

            config_to_save = {
                'db1': {
                    'type': db1_config.db_type, 'path': db1_config.path, 'table': db1_config.table, 'index': db1_config.index_col
                },
                'db2': {
                    'type': db2_config.db_type, 'path': db2_config.path, 'table': db2_config.table, 'index': db2_config.index_col
                },
                'manual_mappings': [{'sqlite_col': m[0], 'access_col': m[1]} for m in manual_mappings_data],
                'smart_options': smart_options_data
            }
            save_app_config(config_to_save)
            self.log_message("Configurazione salvata per il prossimo avvio.")

            comparison_thread = threading.Thread(
                target=run_comparison,
                args=(db1_config, db2_config, manual_mappings_data, smart_options_data, self.log_message),
                daemon=True
            )
            comparison_thread.start()
            self.monitor_thread(comparison_thread)

        except Exception as e:
            self.log_message(f"ERRORE DI CONFIGURAZIONE: {e}")
            self.compare_button.config(state="normal")

    def monitor_thread(self, thread):
        if thread.is_alive():
            self.after(100, lambda: self.monitor_thread(thread))
        else:
            self.log_message("\nConfronto completato.")
            self.compare_button.config(state="normal")

    def on_closing(self):
        """Gestisce la chiusura pulita dell'applicazione."""
        self.destroy()


def main_cli():
    """Funzione per l'esecuzione da riga di comando."""
    parser = argparse.ArgumentParser(description="Confronta due database (SQLite e/o MS Access).")

    parser.add_argument('--config', help="Percorso al file di configurazione JSON (es. dbcomparer_config.json).")

    # Nuovi parametri generici
    parser.add_argument('--db1-type', choices=['sqlite', 'access'], help="Tipo del database 1 (sqlite o access).")
    parser.add_argument('--db1-path', help="Percorso del database 1.")
    parser.add_argument('--db1-table', help="Nome della tabella del database 1.")
    parser.add_argument('--db1-index', help="Colonna indice per la tabella del database 1.")

    parser.add_argument('--db2-type', choices=['sqlite', 'access'], help="Tipo del database 2 (sqlite o access).")
    parser.add_argument('--db2-path', help="Percorso del database 2.")
    parser.add_argument('--db2-table', help="Nome della tabella del database 2.")
    parser.add_argument('--db2-index', help="Colonna indice per la tabella del database 2.")

    # Vecchi parametri per retrocompatibilità (deprecati)
    parser.add_argument('--sqlite-db', help="[DEPRECATO] Usa --db1-type sqlite --db1-path invece.")
    parser.add_argument('--sqlite-table', help="[DEPRECATO] Usa --db1-table invece.")
    parser.add_argument('--sqlite-index', help="[DEPRECATO] Usa --db1-index invece.")
    parser.add_argument('--access-db', help="[DEPRECATO] Usa --db2-type access --db2-path invece.")
    parser.add_argument('--access-table', help="[DEPRECATO] Usa --db2-table invece.")
    parser.add_argument('--access-index', help="[DEPRECATO] Usa --db2-index invece.")

    args = parser.parse_args()

    # Modalità --config: legge tutto dal file JSON
    if args.config:
        config_path = args.config
        if not os.path.exists(config_path):
            parser.error(f"File di configurazione non trovato: {config_path}")
        try:
            with open(config_path, 'r') as f:
                cfg = json.load(f)
        except Exception as e:
            parser.error(f"Errore nella lettura del file di configurazione: {e}")

        db1_raw = cfg.get('db1') or cfg.get('sqlite', {})
        db2_raw = cfg.get('db2') or cfg.get('access', {})

        db1_type  = db1_raw.get('type', 'sqlite')
        db1_path  = db1_raw.get('path', '')
        db1_table = db1_raw.get('table', '')
        db1_index = db1_raw.get('index', '')
        if not db1_index:
            pk = get_primary_key(db1_type, db1_path, db1_table)
            if pk:
                db1_index = pk if len(pk) > 1 else pk[0]
                print(f"PK rilevata automaticamente per DB1: {db1_index}")
            else:
                parser.error(f"Nessuna chiave primaria trovata per DB1:{db1_table}. Specificare 'index' nel config.")

        db2_type  = db2_raw.get('type', 'access')
        db2_path  = db2_raw.get('path', '')
        db2_table = db2_raw.get('table', '')
        db2_index = db2_raw.get('index', '')
        if not db2_index:
            pk = get_primary_key(db2_type, db2_path, db2_table)
            if pk:
                db2_index = pk if len(pk) > 1 else pk[0]
                print(f"PK rilevata automaticamente per DB2: {db2_index}")
            else:
                parser.error(f"Nessuna chiave primaria trovata per DB2:{db2_table}. Specificare 'index' nel config.")

        db1_cfg = DbConfig(db_type=db1_type, path=db1_path, table=db1_table, index_col=db1_index)
        db2_cfg = DbConfig(db_type=db2_type, path=db2_path, table=db2_table, index_col=db2_index)

        manual_mappings = [
            (m.get('sqlite_col', ''), m.get('access_col', ''))
            for m in cfg.get('manual_mappings', [])
        ]
        smart_options = cfg.get('smart_options', {})

        run_comparison(db1_cfg, db2_cfg, manual_mappings, smart_options)
        return

    # Modalità argomenti espliciti
    # Gestione retrocompatibilità: se usati vecchi parametri, mapparli ai nuovi
    if args.sqlite_db or args.sqlite_table or args.sqlite_index:
        print("ATTENZIONE: I parametri --sqlite-* sono deprecati. Usa --db1-type, --db1-path, --db1-table, --db1-index invece.")
        db1_type = 'sqlite'
        db1_path = args.sqlite_db
        db1_table = args.sqlite_table
        db1_index = args.sqlite_index
    else:
        if not all([args.db1_type, args.db1_path, args.db1_table, args.db1_index]):
            parser.error("Specificare --config oppure --db1-type, --db1-path, --db1-table, --db1-index")
        db1_type = args.db1_type
        db1_path = args.db1_path
        db1_table = args.db1_table
        db1_index = args.db1_index

    if args.access_db or args.access_table or args.access_index:
        print("ATTENZIONE: I parametri --access-* sono deprecati. Usa --db2-type, --db2-path, --db2-table, --db2-index invece.")
        db2_type = 'access'
        db2_path = args.access_db
        db2_table = args.access_table
        db2_index = args.access_index
    else:
        if not all([args.db2_type, args.db2_path, args.db2_table, args.db2_index]):
            parser.error("Specificare --config oppure --db2-type, --db2-path, --db2-table, --db2-index")
        db2_type = args.db2_type
        db2_path = args.db2_path
        db2_table = args.db2_table
        db2_index = args.db2_index

    db1_cfg = DbConfig(db1_type, db1_path, db1_table, db1_index)
    db2_cfg = DbConfig(db2_type, db2_path, db2_table, db2_index)
    run_comparison(db1_cfg, db2_cfg)


if __name__ == "__main__":
    if len(sys.argv) > 1:
        main_cli()
    else:
        app = App()
        app.mainloop()