#!/usr/bin/env python3
import os
import sys
import asyncio
import paramiko

# 1) Make sure this directory is first on sys.path
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
sys.path.insert(0, SCRIPT_DIR)
sys.path.insert(1, os.path.join(SCRIPT_DIR, '..'))

from utils.utils import map_csv_file
from mapping.collectionsERP import product_to_collections_map as collections_mapping
from api.collections import (
    get_all_smart_collections,
    create_smart_collection,
    update_smart_collection
)


async def process_collections():
    host = "emde.autarcia.com"
    port = 22
    username = "ftp-shoppingfeed"
    password = "5vm_*2I[f2yL2A6J!/dE"

    # 2) Match the real filename on the server (case-sensitive)
    remote_path = "/exports/CSV/shopify/DATA_RUBRIQ_ID_SHOP_2941.CSV"
    print(f"🔍 Checking for remote file {remote_path}…")

    # set up SFTP
    transport = paramiko.Transport((host, port))
    transport.connect(username=username, password=password)
    sftp = paramiko.SFTPClient.from_transport(transport)
    try:
        sftp.stat(remote_path)
    except FileNotFoundError:
        print(f"❌ Remote file not found: {remote_path}")
        sftp.close()
        transport.close()
        return

    # download into `files/collections/`
    local_dir = os.path.join(SCRIPT_DIR, '..', 'files', 'collections')
    os.makedirs(local_dir, exist_ok=True)
    local_csv = os.path.join(local_dir, os.path.basename(remote_path))
    print(f"⬇️  Downloading to {local_csv}")
    sftp.get(remote_path, local_csv)
    sftp.close()
    transport.close()

    # 3) Debug: report how many CSV rows you parsed
    records = map_csv_file(local_csv, collections_mapping)
    print(f"🗂  Parsed {len(records)} records from CSV")

    existing = await get_all_smart_collections()
    print(f"🔁 Fetched {len(existing)} existing smart collections")
    lookup = {c["title"]: c for c in existing if c.get("title")}

    for rec in records:
        print(rec)
        title = rec.get("collectionTitle")
        if not title:
            sys.exit(1)
            print("⚠️  Skipping record with no title:", rec)
            continue

        if title in lookup:
            cid = lookup[title]["id"]
            result = await update_smart_collection(cid, rec)
            if "smart_collection" in result:
                print(f"✅ Updated '{title}'")
            else:
                print(f"❌ Failed update '{title}':", result)
        else:
            result = await create_smart_collection(rec)
            if "smart_collection" in result:
                print(f"✅ Created '{title}'")
            else:
                print(f"❌ Failed create '{title}':", result)


async def main():
    await process_collections()


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