import asyncio
import pandas as pd
import uuid
import math
from datetime import datetime
from sqlalchemy import select

import sys
import os
sys.path.insert(0, r'd:\audi\backend')

from app.core.database import AsyncSessionLocal
from app.models.sales_models import TransportMaster

def safe_str(val):
    if pd.isna(val) or str(val).strip() == '' or str(val).lower() == 'nan':
        return None
    return str(val).strip()

async def seed_data():
    df = pd.read_excel(r'd:\audi dev\docs\PRODUCTION REPORT 30-JUNE-2026 (1).xlsx')
    
    async with AsyncSessionLocal() as db:
        transports = {}
        
        res_tpt = await db.execute(select(TransportMaster))
        for t in res_tpt.scalars().all():
            if t.name:
                transports[t.name.upper()] = t.transport_id
            
        count = 0
            
        for idx, row in df.iterrows():
            rec_tpt = safe_str(row.get(' REC.TPT'))
            disp_tpt = safe_str(row.get('Dispatch TPT'))
            
            def get_tpt_id(t_name):
                nonlocal count
                if not t_name: return None
                k = t_name.upper()
                if k not in transports:
                    t_id = f"TPT-{uuid.uuid4().hex[:6].upper()}"
                    nt = TransportMaster(transport_id=t_id, name=t_name)
                    db.add(nt)
                    transports[k] = t_id
                    count += 1
                return transports[k]
                
            get_tpt_id(rec_tpt)
            get_tpt_id(disp_tpt)
            
        await db.commit()
        print(f"✅ Successfully seeded {count} new transporters from the Production Report.")

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