import os
import sys
import uuid
import asyncio
import openpyxl
from sqlalchemy import select

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

from app.core.database import AsyncSessionLocal
from app.models.dealer_models import SpecialFeatureMaster, SpecialFeatureOptionMaster

EXCEL_PATH = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..', 'special_features.xlsx'))

async def sync_special_features():
    if not os.path.exists(EXCEL_PATH):
        print(f"Excel file not found at {EXCEL_PATH}")
        return

    print(f"Loading Excel file from: {EXCEL_PATH}")
    wb = openpyxl.load_workbook(EXCEL_PATH)
    ws = wb['Features'] if 'Features' in wb.sheetnames else wb.active
    
    excel_rows = []
    for idx, r in enumerate(list(ws.iter_rows(values_only=True))[1:], start=2):
        if not any(r):
            continue
        section = str(r[0] or '').strip()
        if section.upper() in ('DEALER/CUSTOMER', 'DEALER / CUSTOMER'):
            section = 'Dealer / Customer'
        elif section.upper() in ('INTERIOR FEATURES', 'INTERIOR'):
            section = 'Interior Features'
        elif section.upper() in ('EXTERIOR SPECIFICATIONS', 'EXTERIOR'):
            section = 'Exterior Specifications'
        elif section.upper() in ('ADDITIONAL SPECIFICATION OPTIONS', 'ADDITIONAL SPECIFICATIONS'):
            section = 'Additional specification options'
        
        feature_name = str(r[1] or '').strip()
        input_type = str(r[2] or '').strip().upper()
        order = int(r[3]) if r[3] is not None and str(r[3]).strip().isdigit() else 0
        status = str(r[4] or '').strip().lower() == 'active'
        raw_opts = str(r[5] or '').strip() if r[5] is not None else ''
        options = [o.strip() for o in raw_opts.split('|') if o.strip()] if raw_opts else []
        
        excel_rows.append({
            'section': section,
            'feature_name': feature_name,
            'input_type': input_type,
            'sequence_no': order,
            'is_active': status,
            'options': options
        })

    print(f"Parsed {len(excel_rows)} features from Excel.")

    async with AsyncSessionLocal() as db:
        # Load all existing features
        result = await db.execute(select(SpecialFeatureMaster))
        existing_features = {f.feature_name.lower().strip(): f for f in result.scalars().all()}
        
        processed_feature_ids = set()
        
        for r in excel_rows:
            fkey = r['feature_name'].lower().strip()
            if fkey in existing_features:
                feat = existing_features[fkey]
                feat.section = r['section']
                feat.feature_name = r['feature_name']
                feat.input_type = r['input_type']
                feat.sequence_no = r['sequence_no']
                feat.is_active = r['is_active']
                print(f"[UPDATE] Feature '{feat.feature_name}' (Seq: {feat.sequence_no}, Type: {feat.input_type}, Section: {feat.section})")
            else:
                feat = SpecialFeatureMaster(
                    feature_id=uuid.uuid4(),
                    section=r['section'],
                    feature_name=r['feature_name'],
                    input_type=r['input_type'],
                    sequence_no=r['sequence_no'],
                    is_active=r['is_active']
                )
                db.add(feat)
                await db.flush()
                print(f"[CREATE] Feature '{feat.feature_name}' (Seq: {feat.sequence_no}, Type: {feat.input_type}, Section: {feat.section})")
                
            processed_feature_ids.add(feat.feature_id)
            
            # Sync options for this feature
            opt_res = await db.execute(
                select(SpecialFeatureOptionMaster).where(SpecialFeatureOptionMaster.feature_id == feat.feature_id)
            )
            existing_options = {o.option_value.lower().strip(): o for o in opt_res.scalars().all()}
            excel_opt_keys = set()
            
            for seq, opt_name in enumerate(r['options'], start=1):
                okey = opt_name.lower().strip()
                excel_opt_keys.add(okey)
                if okey in existing_options:
                    opt = existing_options[okey]
                    opt.option_value = opt_name
                    opt.sequence_no = seq
                    opt.is_active = True
                else:
                    opt = SpecialFeatureOptionMaster(
                        option_id=uuid.uuid4(),
                        feature_id=feat.feature_id,
                        option_value=opt_name,
                        sequence_no=seq,
                        is_active=True
                    )
                    db.add(opt)
                    print(f"   + Option: '{opt_name}'")
                    
            # Deactivate options that were removed from Excel
            for okey, opt in existing_options.items():
                if okey not in excel_opt_keys:
                    opt.is_active = False
                    print(f"   - Option deactivated: '{opt.option_value}'")

        # Deactivate features not present in Excel
        for fkey, feat in existing_features.items():
            if feat.feature_id not in processed_feature_ids and feat.is_active:
                feat.is_active = False
                print(f"[DEACTIVATE] Feature not in Excel: '{feat.feature_name}'")

        await db.commit()
        print("\nSpecial features successfully synchronized with Excel!")

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