"""
Exporter: legge un Parquet via DuckDB e scrive una tabella in MS Access.

Metodi supportati (job['method']):
  'pyodbc'        — INSERT via pyodbc/ODBC (default). Lento su tabelle grandi.
  'csv_transfer'  — COPY TO CSV + Access.Application.DoCmd.TransferText. Veloce.
"""

import pyodbc
import duckdb
import tempfile
from pathlib import Path
from core.schema import access_type, coerce_value


CHUNK_SIZE = 50000  # righe per batch INSERT (metodo pyodbc)


# ---------------------------------------------------------------------------
# Metodo pyodbc
# ---------------------------------------------------------------------------

def _access_conn(accdb_path: str) -> pyodbc.Connection:
    driver = '{Microsoft Access Driver (*.mdb, *.accdb)}'
    conn_str = f'DRIVER={driver};DBQ={accdb_path};'
    return pyodbc.connect(conn_str, autocommit=False)


def _table_exists(cur: pyodbc.Cursor, table: str) -> bool:
    rows = cur.tables(table=table, tableType='TABLE').fetchall()
    return len(rows) > 0


def _drop_table(cur: pyodbc.Cursor, table: str):
    try:
        cur.execute(f'DROP TABLE [{table}]')
        cur.commit()
    except Exception:
        pass


def _create_table(cur: pyodbc.Cursor, table: str, columns: list[tuple]):
    col_defs = ', '.join(
        f'[{name}] {access_type(dtype)}'
        for name, dtype in columns
    )
    cur.execute(f'CREATE TABLE [{table}] ({col_defs})')
    cur.commit()


def _insert_batch(cur: pyodbc.Cursor, table: str, col_names: list[str], rows: list):
    placeholders = ', '.join(['?'] * len(col_names))
    cols = ', '.join(f'[{c}]' for c in col_names)
    sql = f'INSERT INTO [{table}] ({cols}) VALUES ({placeholders})'
    coerced = [
        tuple(coerce_value(v) for v in row)
        for row in rows
    ]
    cur.executemany(sql, coerced)
    cur.commit()


def _export_pyodbc(con, query, target, table, write_cols, col_types, mode):
    result = con.execute(query)

    acc = _access_conn(target)
    cur = acc.cursor()

    if mode == 'replace':
        if _table_exists(cur, table):
            _drop_table(cur, table)
        _create_table(cur, table, col_types)
    else:
        cur.execute(f'DELETE FROM [{table}]')
        cur.commit()

    total = 0
    while True:
        batch = result.fetchmany(CHUNK_SIZE)
        if not batch:
            break
        _insert_batch(cur, table, write_cols, batch)
        total += len(batch)

    cur.close()
    acc.close()
    return total


# ---------------------------------------------------------------------------
# Metodo csv_transfer
# ---------------------------------------------------------------------------

def _export_csv_transfer(con, query, source, target, table, write_cols, col_map):
    """
    Esporta via CSV temporaneo + Access.Application.DoCmd.TransferText.
    Molto più veloce di pyodbc per tabelle grandi.
    Richiede Microsoft Access installato sulla macchina.
    """
    import win32com.client

    tmp_dir  = Path(tempfile.gettempdir())
    csv_path = tmp_dir / f'_pta_{table}.csv'
    ini_path = tmp_dir / 'schema.ini'

    # Rinomina colonne: parquet_col AS access_col
    select_parts = []
    for acc_col in write_cols:
        parquet_col = col_map.get(acc_col, acc_col) if col_map else acc_col
        if acc_col == parquet_col:
            select_parts.append(f'"{parquet_col}"')
        else:
            select_parts.append(f'"{parquet_col}" AS "{acc_col}"')
    select_sql = ', '.join(select_parts)

    try:
        # 1. Esporta CSV con DuckDB
        con.execute(f"""
            COPY (SELECT {select_sql} FROM '{source}')
            TO '{csv_path.as_posix()}'
            (FORMAT CSV, HEADER true, DELIMITER ',')
        """)

        # 2. schema.ini — definisce encoding e separatore per Access
        ini_content = (
            f'[{csv_path.name}]\n'
            f'CharacterSet=UTF-8\n'
            f'Format=Delimited(;)\n'
            f'ColNameHeader=True\n'
        )
        ini_path.write_text(ini_content, encoding='utf-8')

        # 3. Conta righe (per il return)
        total = con.execute(f"SELECT COUNT(*) FROM '{csv_path.as_posix()}'").fetchone()[0]

        # 4. TransferText via Access.Application
        app = win32com.client.Dispatch('Access.Application')
        app.Visible = False
        try:
            app.OpenCurrentDatabase(str(target))
            # acImportDelim=0, no spec, table, csv, has_header
            app.DoCmd.TransferText(0, None, table, str(csv_path), True)
            app.CloseCurrentDatabase()
        finally:
            app.Quit()
            del app

    finally:
        # 5. Pulizia file temporanei
        for f in (csv_path, ini_path):
            if f.exists():
                try:
                    f.unlink()
                except Exception:
                    pass

    return total


# ---------------------------------------------------------------------------
# Entry point
# ---------------------------------------------------------------------------

def export(job: dict):
    """
    Esegue un singolo job di export Parquet -> Access.

    job keys:
      source      — path Parquet
      target      — path accdb
      table       — nome tabella Access target
      method      — 'pyodbc' (default) | 'csv_transfer'
      mode        — 'append' (default) | 'replace'  [solo pyodbc]
      columns     — lista colonne Access da scrivere (opzionale)
      column_map  — dict {access_col: parquet_col}  (opzionale)
    """
    source  = job['source']
    target  = job['target']
    table   = job['table']
    method  = job.get('method', 'pyodbc')
    mode    = job.get('mode', 'append')
    columns = job.get('columns')
    col_map = job.get('column_map')

    con = duckdb.connect()
    rel = con.sql(f"SELECT * FROM '{source}'")
    parquet_cols = {name: dtype for name, dtype in zip(rel.columns, rel.dtypes)}

    # Determina write_cols e query SELECT
    if columns:
        write_cols = []
        select_exprs = []
        for acc_col in columns:
            parquet_col = col_map.get(acc_col, acc_col) if col_map else acc_col
            if parquet_col in parquet_cols:
                write_cols.append(acc_col)
                select_exprs.append(f'"{parquet_col}"')
        query = f"SELECT {', '.join(select_exprs)} FROM '{source}'"
        col_types = [(acc_col, str(parquet_cols.get(
            col_map.get(acc_col, acc_col) if col_map else acc_col, 'VARCHAR'
        ))) for acc_col in write_cols]
    else:
        write_cols = rel.columns
        select_exprs = [f'"{c}"' for c in write_cols]
        query = f"SELECT {', '.join(select_exprs)} FROM '{source}'"
        col_types = [(c, str(d)) for c, d in zip(rel.columns, rel.dtypes)]

    if method == 'csv_transfer':
        total = _export_csv_transfer(con, query, source, target, table, write_cols, col_map)
    else:
        total = _export_pyodbc(con, query, target, table, write_cols, col_types, mode)

    con.close()
    return total