#!/usr/bin/env python3
import os
import csv
import tempfile
import paramiko
import mysql.connector
from datetime import datetime

# ————— Config SFTP —————
SFTP_HOST       = "emde.autarcia.com"
SFTP_PORT       = 22
SFTP_USERNAME   = "ftp-shoppingfeed"
SFTP_PASSWORD   = "5vm_*2I[f2yL2A6J!/dE"
REMOTE_CSV_PATH = "/exports/CSV/exp_swing/SW_HISTO_INVOICE_HEA_2640.CSV"

# ————— Config BDD —————
DB_HOST = 'fz321.myd.infomaniak.com'
DB_NAME = 'fz321_emde_b2b'
DB_USER = 'fz321_emde_ref'
DB_PASS = '4eDCSYh#a612_%'

# Taille des lots pour executemany
BATCH_SIZE = 1000

def download_csv_via_sftp(remote_path: str) -> str:
    tmp = tempfile.NamedTemporaryFile(delete=False, suffix=".csv")
    local_path = tmp.name
    tmp.close()

    transport = paramiko.Transport((SFTP_HOST, SFTP_PORT))
    transport.connect(username=SFTP_USERNAME, password=SFTP_PASSWORD)
    sftp = paramiko.SFTPClient.from_transport(transport)
    try:
        sftp.get(remote_path, local_path)
        print(f"✅ CSV téléchargé dans {local_path}")
    finally:
        sftp.close()
        transport.close()

    return local_path

def parse_date(dt_str: str) -> str:
    """Convertit 'DD/MM/YYYY' ou 'DD/MM/YYYY hh:mm:ss' en 'YYYY-MM-DD'."""
    for fmt in ("%d/%m/%Y %H:%M:%S", "%d/%m/%Y"):
        try:
            return datetime.strptime(dt_str, fmt).strftime("%Y-%m-%d")
        except ValueError:
            continue
    raise ValueError(f"Format de date inconnu : {dt_str}")

def import_invoices(csv_path: str):
    conn = mysql.connector.connect(
        host=DB_HOST, user=DB_USER, password=DB_PASS,
        database=DB_NAME, charset='utf8mb4'
    )
    cursor = conn.cursor()

    # ON DUPLICATE KEY UPDATE avec condition date plus récente
    upsert_sql = """
    INSERT INTO invoices
      (FCC_CLT_ID, FCC_FCC_ID, FCC_FCC_NUM_FACTURE,
       FCC_FCC_DATE, FCC_FCC_DATE_ECHEANCE, FCC_FCC_REFERENCE,
       FCC_FCC_PRIX_HT, FCC_FCC_TYPE, CAG_CAG_USR_ID, FCC_FCC_PAYEE)
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
    ON DUPLICATE KEY UPDATE
      FCC_CLT_ID          = VALUES(FCC_CLT_ID),
      FCC_FCC_NUM_FACTURE = VALUES(FCC_FCC_NUM_FACTURE),
      FCC_FCC_DATE        = IF(VALUES(FCC_FCC_DATE) > FCC_FCC_DATE, VALUES(FCC_FCC_DATE), FCC_FCC_DATE),
      FCC_FCC_DATE_ECHEANCE = VALUES(FCC_FCC_DATE_ECHEANCE),
      FCC_FCC_REFERENCE   = VALUES(FCC_FCC_REFERENCE),
      FCC_FCC_PRIX_HT     = VALUES(FCC_FCC_PRIX_HT),
      FCC_FCC_TYPE        = VALUES(FCC_FCC_TYPE),
      CAG_CAG_USR_ID      = VALUES(CAG_CAG_USR_ID),
      FCC_FCC_PAYEE       = VALUES(FCC_FCC_PAYEE)
    """

    # Lecture et conversion du CSV
    to_upsert = []
    with open(csv_path, newline='', encoding='utf-8') as f:
        reader = csv.reader(f, delimiter=';')
        headers = next(reader)  # ignore

        for row in reader:
            clt_id, fcc_id, num, date_str, echeance_str, ref, prix_str, typ, usr_id, payee = (
                v.strip().strip('"') for v in row
            )
            try:
                vals = (
                    int(clt_id),
                    int(fcc_id),
                    num or None,
                    parse_date(date_str),
                    parse_date(echeance_str),
                    ref or None,
                    float(prix_str.replace(',', '.')) if prix_str else 0.0,
                    typ or None,
                    int(usr_id) if usr_id else None,
                    int(payee) if payee else 0
                )
            except Exception as e:
                print(f"⚠️  Ligne ignorée ({row}): {e}")
                continue

            to_upsert.append(vals)

    # Exécution par lots
    for i in range(0, len(to_upsert), BATCH_SIZE):
        batch = to_upsert[i:i+BATCH_SIZE]
        cursor.executemany(upsert_sql, batch)
        conn.commit()
        print(f"✅ Lot {i//BATCH_SIZE+1} / {((len(to_upsert)-1)//BATCH_SIZE)+1} importé")

    cursor.close()
    conn.close()
    print("✅ Import global terminé.")

if __name__ == "__main__":
    local_csv = download_csv_via_sftp(REMOTE_CSV_PATH)
    import_invoices(local_csv)
    os.remove(local_csv)