import os
import sys
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_ORDER_HEA_2666.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 = 100

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_mysql(dt_str: str) -> datetime:
    for fmt in ("%d/%m/%Y %H:%M:%S", "%d/%m/%Y"):
        try:
            return datetime.strptime(dt_str, fmt)
        except ValueError:
            continue
    raise ValueError(f"Format de date inconnu : {dt_str}")

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

    with open(csv_path, newline='', encoding='utf-8') as f:
        reader = csv.reader(f, delimiter=';')
        headers = next(reader)
        cols = [h.strip().strip('"') for h in headers]

        placeholders = ", ".join(["%s"] * len(cols))
        column_list = ", ".join(cols)

        # On déclare CMD_CMD_ID UNIQUE dans la table orders
        # Puis, en cas de doublon, on met à jour uniquement si DATE_CREATION est plus récente
        update_clause = []
        for c in cols:
            if c == "CMD_CMD_DATE_CREATION":
                update_clause.append(
                    f"{c}=IF(VALUES({c}) > {c}, VALUES({c}), {c})"
                )
            elif c != "CMD_CMD_ID":  # on ne touche pas à la clé
                update_clause.append(f"{c}=VALUES({c})")
        update_sql = ", ".join(update_clause)

        sql = (
            f"INSERT INTO orders ({column_list}) "
            f"VALUES ({placeholders}) "
            f"ON DUPLICATE KEY UPDATE {update_sql}"
        )

        batch = []
        for row in reader:
            data = []
            for idx, val in enumerate(row):
                v = val.strip().strip('"')
                col = cols[idx]
                if col in ("CMD_CMD_DATE_CREATION", "CMD_DATE_DEMANDE"):
                    dt = parse_date_mysql(v)
                    fmt = "%Y-%m-%d %H:%M:%S" if col=="CMD_CMD_DATE_CREATION" else "%Y-%m-%d"
                    data.append(dt.strftime(fmt))
                elif col in ("CMD_CLT_ID","CMD_CMD_ID","CAG_CAG_USR_ID"):
                    data.append(int(v) if v else None)
                elif col == "CMD_CMD_PRIX_HT":
                    data.append(float(v.replace(",", ".")) if v else None)
                else:
                    data.append(v or None)
            batch.append(tuple(data))

            if len(batch) >= BATCH_SIZE:
                cursor.executemany(sql, batch)
                conn.commit()
                batch.clear()

        if batch:
            cursor.executemany(sql, batch)
            conn.commit()

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

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