import asyncio
import sys
import os
import uuid
import pandas as pd

sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy import select
from app.core.database import AsyncSessionLocal
from app.models.sales_models import CustomerMaster, CustomerTypeMaster

def safe_str(val):
    if pd.isna(val):
        return None
    s = str(val).strip()
    return s if s else None

async def run():
    async with AsyncSessionLocal() as db:
        # Ensure DEALER type exists
        type_code = 'DEALER'
        existing_type = await db.execute(select(CustomerTypeMaster).where(CustomerTypeMaster.type_code == type_code))
        if not existing_type.scalar_one_or_none():
            db.add(CustomerTypeMaster(type_code=type_code, name='Dealer'))
            await db.commit()

        # Load production sheet
        project_root = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..'))
        excel_path = os.path.join(project_root, 'docs', 'PRODUCTION REPORT 30-JUNE-2026 (1).xlsx')
        
        if not os.path.exists(excel_path):
            print(f"Excel file not found at {excel_path}")
            return
            
        df = pd.read_excel(excel_path)
        
        # Extract unique dealer names
        # Assuming the CUSTOMER NAME column contains "Dealer Name / Client Name" or just "Dealer Name"
        unique_dealers = set()
        for idx, row in df.iterrows():
            cust_name_raw = safe_str(row.get('CUSTOMER NAME'))
            if cust_name_raw:
                dealer_name = cust_name_raw.split('/')[0].strip().upper()
                if dealer_name:
                    unique_dealers.add(dealer_name)

        if not unique_dealers:
            print("No dealers found in the production sheet.")
            return

        print(f"Found {len(unique_dealers)} unique dealers in the sheet.")

        # Pre-fetch existing dealers to avoid inserting duplicates
        existing_res = await db.execute(select(CustomerMaster).where(CustomerMaster.type_code == type_code))
        existing_dealers = {c.name.upper() for c in existing_res.scalars().all()}

        inserted = 0
        for d_name in unique_dealers:
            if d_name not in existing_dealers:
                dealer = CustomerMaster(
                    customer_id=f"DLR-{uuid.uuid4().hex[:6].upper()}",
                    name=d_name,
                    type_code=type_code,
                    is_active=True,
                    portal_enabled=False  # Do not set portal credentials right now
                )
                db.add(dealer)
                inserted += 1
                existing_dealers.add(d_name)

        if inserted > 0:
            await db.commit()
            print(f"Successfully inserted {inserted} new dealers.")
        else:
            print("All dealers from the sheet already exist. No new dealers inserted.")

if __name__ == '__main__':
    asyncio.run(run())