#!/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_CREDITNOTHE_2656.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  # lot pour executemany

def download_csv_via_sftp(remote_path: str) -> str:
    tmp = tempfile.NamedTemporaryFile(delete=False, suffix=".csv")
    local = tmp.name; tmp.close()
    tr = paramiko.Transport((SFTP_HOST, SFTP_PORT))
    tr.connect(username=SFTP_USERNAME, password=SFTP_PASSWORD)
    sftp = paramiko.SFTPClient.from_transport(tr)
    try:
        sftp.get(remote_path, local)
        print(f"✅ CSV téléchargé dans {local}")
    finally:
        sftp.close(); tr.close()
    return local

def parse_date(dt: str) -> str:
    """DD/MM/YYYY → YYYY-MM-DD"""
    for fmt in ("%d/%m/%Y %H:%M:%S", "%d/%m/%Y"):
        try:
            return datetime.strptime(dt, fmt).strftime("%Y-%m-%d")
        except ValueError:
            continue
    raise ValueError(f"Date invalide : {dt}")

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

    # 1) Récupérer tous les IDs de factures existantes
    cur.execute("SELECT FCC_FCC_ID FROM invoices")
    existing_invoices = {row[0] for row in cur.fetchall()}

    # 2) Lire le CSV et ne garder que les avoirs dont la facture existe
    to_insert = []
    with open(csv_path, newline='', encoding='utf-8') as f:
        reader = csv.reader(f, delimiter=';')
        next(reader)  # entête
        for row in reader:
            # strip & cleanup
            clt, avc, num, date, eche, ref, prix, fcc_ref, usr, solde = [v.strip().strip('"') for v in row]

            # convertir AVC_AVC_FCC_ID en int
            try:
                fcc_id = int(fcc_ref)
            except ValueError:
                continue  # si vide ou invalide

            # **Skip si la facture n’existe pas**
            if fcc_id not in existing_invoices:
                print(f"⏭ Avoir {avc} ignoré car facture {fcc_id} absente.")
                continue

            # parse et cast
            try:
                vals = (
                    int(clt),
                    int(avc),
                    num or None,
                    parse_date(date),
                    parse_date(eche),
                    ref or None,
                    float(prix.replace(',', '.')) if prix else 0.0,
                    fcc_id,
                    int(usr) if usr else None,
                    float(solde.replace(',', '.')) if solde else 0.0
                )
            except Exception as e:
                print(f"⚠️ Erreur ligne {avc}: {e}")
                continue

            to_insert.append(vals)

    if not to_insert:
        print("ℹ️ Aucun avoir à insérer.")
        cur.close(); conn.close()
        return

    # 3) INSERT par lots
    sql = """
      INSERT INTO credit_notes
        (AVC_CLT_ID, AVC_AVC_ID, AVC_AVC_NUM_AVOIR,
         AVC_AVC_DATE, AVC_AVC_DATE_ECHEANCE, AVC_AVC_REFERENCE,
         AVC_PRIX_HT, AVC_AVC_FCC_ID, CAG_CAG_USR_ID, AVC_AVC_SOLDE)
      VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
      ON DUPLICATE KEY UPDATE
        AVC_AVC_NUM_AVOIR      = VALUES(AVC_AVC_NUM_AVOIR),
        AVC_AVC_DATE           = VALUES(AVC_AVC_DATE),
        AVC_AVC_DATE_ECHEANCE  = VALUES(AVC_AVC_DATE_ECHEANCE),
        AVC_AVC_REFERENCE      = VALUES(AVC_AVC_REFERENCE),
        AVC_PRIX_HT            = VALUES(AVC_PRIX_HT),
        CAG_CAG_USR_ID         = VALUES(CAG_CAG_USR_ID),
        AVC_AVC_SOLDE          = VALUES(AVC_AVC_SOLDE)
    """
    for i in range(0, len(to_insert), BATCH_SIZE):
        batch = to_insert[i:i+BATCH_SIZE]
        cur.executemany(sql, batch)
        conn.commit()
        print(f"✅ Lot {i//BATCH_SIZE+1} inséré ({len(batch)} enregistrements)")

    cur.close()
    conn.close()
    print("✅ Import des avoirs terminé.")

if __name__ == "__main__":
    local = download_csv_via_sftp(REMOTE_CSV_PATH)
    try:
        import_credit_notes(local)
    finally:
        os.remove(local)