import asyncio
import os
import sys

# Add backend directory to sys.path
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select
from app.core.database import AsyncSessionLocal
from app.models.dealer_models import (
    BodyTypeMaster, SpecialFeatureMaster, SpecialFeatureOptionMaster, SeatModelConfiguration
)

CSV_DATA = [
    {"body": "STARLINE/LEGACY", "seat": "HHR", "layout": "2x2", "model": "SKYLINE", "supplier": "DIVERSITECH", "dims": "16 INCH (850x850)"},
    {"body": "PRO WIDER / VOLVO FACE / HD BAGGAD FACE", "seat": "HHR", "layout": "3x2", "model": "SKYLINE", "supplier": "DIVERSITECH", "dims": "16 INCH (1250x850)"},
    {"body": "SKYLINE /SKYLINE 02 / SKYLINE 03 /PRO AMTS / PRO FACE", "seat": "HHR", "layout": "2x2", "model": "VE3100", "supplier": "DIVERSITECH", "dims": "17 INCH (930x930)"},
    {"body": "AX MODAL", "seat": "LOW BACK", "layout": "3x2", "model": "VECV SCB AX", "supplier": "DIVERSITECH", "dims": "380 MM (1000x700)"},
    {"body": "SKYLINE /SKYLINE 02 / SKYLINE 03 /PRO AMTS / PRO FACE", "seat": "HHR", "layout": "2x2", "model": "930 SWANKY", "supplier": "KAIZEN", "dims": "17 INCH (930x930)"},
    {"body": "PRO WIDER / VOLVO FACE / HD BAGGAD FACE", "seat": "HHR", "layout": "3x2", "model": "KAIZEN HHR", "supplier": "KAIZEN", "dims": "16 INCH (1250x850)"},
    {"body": "SKYLINE /SKYLINE 02 / SKYLINE 03 /PRO AMTS / PRO FACE", "seat": "PB", "layout": "2x2", "model": "930 SWANKY", "supplier": "KAIZEN", "dims": "17 INCH (930x930)"},
    {"body": "PRO WIDER / VOLVO FACE / HD BAGGAD FACE", "seat": "PB", "layout": "3x2", "model": "KAIZEN PB", "supplier": "KAIZEN", "dims": "16 INCH (1250x850)"},
    {"body": "SKYLINE /SKYLINE 02 / SKYLINE 03 /PRO AMTS / PRO FACE", "seat": "PB", "layout": "2x2", "model": "910 DX", "supplier": "UNO MINDA", "dims": "18 INCH (910x910)"},
    {"body": "SKYLINE /SKYLINE 02 / SKYLINE 03 /PRO AMTS / PRO FACE", "seat": "PB", "layout": "2x2", "model": "ECO 40", "supplier": "PINNACLE", "dims": "18 INCH (940x940)"},
    {"body": "STARLINE/LEGACY", "seat": "PB", "layout": "3x2", "model": "ECO 40", "supplier": "PINNACLE", "dims": "16 INCH (1250x850)"},
    {"body": "AX MODAL", "seat": "PB", "layout": "2x2", "model": "B2", "supplier": "MOBI", "dims": "17 INCH (900x900)"},
]

async def seed():
    async with AsyncSessionLocal() as db:
        print("Clearing old SeatModelConfiguration data...")
        await db.execute(SeatModelConfiguration.__table__.delete())
        await db.commit()
        
        # Load all body types
        body_types = (await db.execute(select(BodyTypeMaster))).scalars().all()
        bt_map = {}
        for bt in body_types:
            for part in [p.strip().upper() for p in bt.name.split('/')]:
                bt_map[part] = bt.body_type_id
                
        # Load all feature options for Seat Model, Supplier, and Dimensions
        feats = (await db.execute(select(SpecialFeatureMaster))).scalars().all()
        model_feat = next((f for f in feats if 'seat model' in f.feature_name.lower()), None)
        supp_feat = next((f for f in feats if 'seat supplier' in f.feature_name.lower()), None)
        dim_feat = next((f for f in feats if 'seat dimensions' in f.feature_name.lower()), None)
        
        if not model_feat or not supp_feat or not dim_feat:
            print("Could not find the requisite features in DB!")
            return
            
        opts = (await db.execute(select(SpecialFeatureOptionMaster))).scalars().all()
        
        model_opt_map = {o.option_value.strip().upper(): o.option_id for o in opts if o.feature_id == model_feat.feature_id}
        supp_opt_map = {o.option_value.strip().upper(): o.option_id for o in opts if o.feature_id == supp_feat.feature_id}
        dim_opt_map = {o.option_value.strip().upper(): o.option_id for o in opts if o.feature_id == dim_feat.feature_id}
        
        for row in CSV_DATA:
            body_parts = [p.strip().upper() for p in row["body"].split('/')]
            
            for bp in body_parts:
                bt_id = bt_map.get(bp)
                if not bt_id:
                    print(f"Body type {bp} not found!")
                    continue
                    
                m_opt = model_opt_map.get(row["model"].strip().upper())
                s_opt = supp_opt_map.get(row["supplier"].strip().upper())
                d_opt = dim_opt_map.get(row["dims"].strip().upper())
                
                if not m_opt:
                    print(f"Model opt {row['model']} not found!")
                    continue
                if not s_opt:
                    print(f"Supplier opt {row['supplier']} not found!")
                    continue
                if not d_opt:
                    print(f"Dimensions opt {row['dims']} not found!")
                    continue
                    
                print(f"Inserting: {bp} | {row['seat']} | {row['layout']} -> {row['model']}")
                
                db.add(SeatModelConfiguration(
                    body_type_id=bt_id,
                    seat_type=row["seat"],
                    layout_size=row["layout"].lower(),
                    seat_model_option_id=m_opt,
                    supplier_option_id=s_opt,
                    dimensions_option_id=d_opt
                ))
                
        await db.commit()
        print("Done seeding!")

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