import sys
import os
import asyncio
import pandas as pd
import math
import uuid
from datetime import datetime

# Add the root backend directory to sys.path
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from app.core.database import AsyncSessionLocal
import app.models.core_models
import app.models.rm_models
import app.models.bom_models
import app.models.vendor_portal
import app.models.sales_models
from app.models.sales_models import BuildOrder, CustomerMaster, TransportMaster, CustomerTypeMaster
from sqlalchemy import select

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

def safe_date(val):
    if pd.isna(val):
        return None
    try:
        return pd.to_datetime(val)
    except:
        return None

def safe_int(val):
    if pd.isna(val):
        return None
    try:
        return int(float(val))
    except:
        return None

def map_status(raw_status):
    raw = str(raw_status).upper().strip()
    if 'DISP' in raw: return 'DISPATCHED'
    if 'WIP' in raw: return 'IN_PRODUCTION'
    if 'HOLD' in raw: return 'HOLD'
    if 'PRE' in raw or 'WAITING' in raw: return 'PRE_PRODUCTION'
    if 'PDI' in raw: return 'PDI'
    return 'PRE_PRODUCTION'

async def seed_data():
    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')
    df = pd.read_excel(excel_path)
    
    async with AsyncSessionLocal() as db:
        
        # Ensure a default CustomerType exists
        res_ctype = await db.execute(select(CustomerTypeMaster).where(CustomerTypeMaster.type_code == 'REGULAR'))
        ctype = res_ctype.scalars().first()
        if not ctype:
            ctype = CustomerTypeMaster(type_code='REGULAR', name='Regular Customer')
            db.add(ctype)
            await db.flush()

        customers = {}
        transports = {}
        
        # Prefetch existing customers and transports to avoid many SELECTs
        res_cust = await db.execute(select(CustomerMaster))
        for c in res_cust.scalars().all():
            if c.name:
                customers[c.name.upper()] = c.customer_id
            
        res_tpt = await db.execute(select(TransportMaster))
        for t in res_tpt.scalars().all():
            transports[t.name.upper()] = t.transport_id
            
        count = 0
            
        for idx, row in df.iterrows():
            chassis = safe_str(row.get('Chassis no.'))
            if not chassis:
                continue
                
            cust_name = safe_str(row.get('CUSTOMER NAME')) or "UNKNOWN CUSTOMER"
            cust_key = cust_name.upper()
            
            if cust_key not in customers:
                c_id = f"CUST-{uuid.uuid4().hex[:6].upper()}"
                new_cust = CustomerMaster(
                    customer_id=c_id,
                    type_code='REGULAR',
                    name=cust_name
                )
                db.add(new_cust)
                await db.flush()
                customers[cust_key] = c_id
                
            customer_id = customers[cust_key]
            
            # Handle Transport
            rec_tpt = safe_str(row.get(' REC.TPT'))
            disp_tpt = safe_str(row.get('Dispatch TPT'))
            
            def get_tpt_id(t_name):
                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
                return transports[k]
                
            rt_id = get_tpt_id(rec_tpt)
            dt_id = get_tpt_id(disp_tpt)
            
            order = BuildOrder(
                customer_id=customer_id,
                chassis_no=chassis,
                chassis_model=safe_str(row.get('MODAL')),
                body_type=safe_str(row.get('BODY TYPE')),
                bus_use=safe_str(row.get('SCHOOL/STAFF')),
                status=map_status(row.get('STATUS')),
                chassis_receive_date=safe_date(row.get('CHASSIS RECEIVE DATE')),
                induction_date=safe_date(row.get('INDUCTION DATE')),
                dispatch_date=safe_date(row.get('Dispatch Date')),
                da_lite_status=safe_str(row.get('DA-lite status')) or 'OK',
                document_pending=safe_str(row.get('Document pending')),
                seat_fitting_notes=safe_str(row.get('Seat fitting pending')),
                ac_communication=safe_str(row.get('AC Comunication')),
                driver_request_notes=safe_str(row.get('Driver request')),
                pdi_inspector=safe_str(row.get('PDI Inspector Name')),
                yard_location=safe_str(row.get('YARD CHASSIS LOCATION')),
                assigned_to_name=safe_str(row.get('Responsibility  person')),
                remarks=safe_str(row.get('Remark'))
            )
            db.add(order)
            count += 1
            
        await db.commit()
        print(f"✅ Successfully seeded {count} build orders from the Production Report.")

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