import os
import sys
import asyncio
import paramiko
import mysql.connector

# Pour pouvoir importer vos modules utilitaires
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from utils.utils import map_csv_file
from mapping.products import product_stock_map
from api.products import get_all_variants, update_stock

# Constantes
MAX_CONCURRENT_TASKS = 8

# Répertoires de travail
base_dir = os.path.dirname(__file__)
mapping_dir = os.path.abspath(os.path.join(base_dir, '..', 'files', 'products'))
os.makedirs(mapping_dir, exist_ok=True)

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

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

def download_csv_via_sftp(remote_folder: str, file_name: str, local_folder: str) -> str:
    local_path = os.path.join(local_folder, file_name)
    tr = paramiko.Transport((SFTP_HOST, SFTP_PORT))
    tr.connect(username=SFTP_USERNAME, password=SFTP_PASSWORD)
    sftp = paramiko.SFTPClient.from_transport(tr)
    try:
        sftp.get(os.path.join(remote_folder, file_name), local_path)
        print(f"✅ CSV téléchargé : {file_name}")
    finally:
        sftp.close()
        tr.close()
    return local_path

def download_stock_file() -> str:
    return download_csv_via_sftp(REMOTE_FOLDER_SWING, STOCK_FILENAME, mapping_dir)

def load_product_ref_map() -> dict[str,int]:
    conn = mysql.connector.connect(
        host=DB_HOST, user=DB_USER, password=DB_PASS,
        database=DB_NAME, charset='utf8mb4'
    )
    cursor = conn.cursor()
    cursor.execute("SELECT PDT_PDT_REFERENCE, PDT_PDT_ID FROM products")
    ref_map = {r[0]: r[1] for r in cursor.fetchall() if r[0]}
    cursor.close()
    conn.close()
    return ref_map

async def process_variant(variant, ref_map, stock_map, sem, token_index):
    sku             = variant.get('sku')
    inventory_item  = variant.get('inventory_item_id')
    product_id      = ref_map.get(sku)
    if product_id is None:
        print(f"⚠️ Aucun produit pour la référence « {sku} »")
        return
    stock = stock_map.get(product_id, 0)
    async with sem:
        try:
            # on passe désormais token_index
            resp = await update_stock(inventory_item, stock, token_index)
            print(f"✅ [{sku}] stock mis à jour (token #{token_index}) → {resp}")
        except Exception as e:
            print(f"❌ Erreur update_stock({sku}, token {token_index}): {e}")

async def main():
    # 1) charger ref_map + stock_map
    ref_map   = load_product_ref_map()
    csv_path  = download_stock_file()
    records   = map_csv_file(csv_path, product_stock_map)
    stock_map = {int(r['product_id']): r['product_stock'] for r in records}

    # 2) récupérer variantes Shopify
    all_variants = await get_all_variants()
    sem = asyncio.Semaphore(MAX_CONCURRENT_TASKS)

    # 3) lancer les tâches en parallèle avec token_index cyclé
    tasks = []
    for idx, variant in enumerate(all_variants):
        token_index = idx % MAX_CONCURRENT_TASKS
        tasks.append(process_variant(variant, ref_map, stock_map, sem, token_index))

    await asyncio.gather(*tasks)
    print(f"✅ Total variants traités : {len(all_variants)}")

if __name__ == "__main__":
    asyncio.run(main())
    sys.exit(0)