#!/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_LIN_2660.CSV"

# ————— Config BDD —————
DB_HOST    = 'fz321.myd.infomaniak.com'
DB_NAME    = 'fz321_emde_b2b'
DB_USER    = 'fz321_emde_ref'
DB_PASS    = '4eDCSYh#a612_%'
BATCH_SIZE = 1000  # Nombre de lignes par batch

def download_csv_via_sftp(remote_path: str) -> str:
    """Télécharge le fichier remote_path en local et renvoie son chemin."""
    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 lignes téléchargé dans {local_path}")
    finally:
        sftp.close()
        transport.close()

    return local_path

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

    insert_sql = """
      INSERT INTO invoice_items
        (LFC_LFC_ID, LFC_PDT_ID, LFC_CLC_QTE_UB, LFC_LFC_FCC_ID,
         LFC_CLC_MONTANT_HT, LFC_CLC_PRIX_UNIT_NET,
         LFC_CLC_PRIX_R1, LFC_CLC_PRIX_R2, LFC_CLC_PRIX_R3)
      VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)
    """

    # 1) Lire et parser tout le CSV
    all_rows = []
    with open(csv_path, newline='', encoding='utf-8') as f:
        reader = csv.reader(f, delimiter=';')
        next(reader)  # ignorer l’entête
        for row in reader:
            lfc_id, pdt_id, qte_ub, fcc_id, montant, unitnet, r1, r2, r3 = [v.strip().strip('"') for v in row]
            all_rows.append({
                "lfc_id":   int(lfc_id),
                "pdt_id":   int(pdt_id),
                "qte_ub":   int(qte_ub),
                "fcc_id":   int(fcc_id),
                "mont_ht":  float(montant.replace(',', '.')) if montant else 0.0,
                "unit_net": float(unitnet.replace(',', '.')) if unitnet else 0.0,
                "r1":       float(r1.replace(',', '.'))       if r1       else 0.0,
                "r2":       float(r2.replace(',', '.'))       if r2       else 0.0,
                "r3":       float(r3.replace(',', '.'))       if r3       else 0.0,
            })

    if not all_rows:
        print("⚠️ Aucun enregistrement trouvé dans le CSV.")
        cursor.close()
        conn.close()
        return

    # 2) Récupérer d’un coup tous les IDs déjà importés
    ids = [r["lfc_id"] for r in all_rows]
    # découper en sous-listes pour éviter trop gros IN ()
    existing_ids = set()
    CHUNK = 500
    for i in range(0, len(ids), CHUNK):
        chunk = ids[i:i+CHUNK]
        sql = f"SELECT LFC_LFC_ID FROM invoice_items WHERE LFC_LFC_ID IN ({','.join(['%s']*len(chunk))})"
        cursor.execute(sql, chunk)
        existing_ids.update(r[0] for r in cursor.fetchall())

    # 3) Filtrer les lignes à insérer
    new_data = []
    for r in all_rows:
        if r["lfc_id"] not in existing_ids:
            new_data.append((
                r["lfc_id"], r["pdt_id"], r["qte_ub"], r["fcc_id"],
                r["mont_ht"], r["unit_net"], r["r1"], r["r2"], r["r3"]
            ))

    if not new_data:
        print("ℹ️ Aucune nouvelle ligne à insérer.")
        cursor.close()
        conn.close()
        return

    # 4) Insert en batch
    total = len(new_data)
    inserted = 0
    for i in range(0, total, BATCH_SIZE):
        batch = new_data[i:i+BATCH_SIZE]
        cursor.executemany(insert_sql, batch)
        conn.commit()
        inserted += cursor.rowcount
        print(f"➕ Batch inséré : {i} → {i+len(batch)-1}")

    print(f"✅ Import terminé, {inserted} nouvelles lignes insérées.")
    cursor.close()
    conn.close()

if __name__ == "__main__":
    local_csv = download_csv_via_sftp(REMOTE_CSV_PATH)
    try:
        import_invoice_lines(local_csv)
    finally:
        os.remove(local_csv)