import csv
import os
import sys
import uuid
import asyncio

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.dealer_models import SpecialFeatureMaster, SpecialFeatureOptionMaster

CSV_PATH = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..', 'special_features (1).csv'))

async def run():
    if not os.path.exists(CSV_PATH):
        print(f"CSV file not found at {CSV_PATH}")
        return

    rows = []
    with open(CSV_PATH, mode='r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        for r in reader:
            rows.append({
                'section': r['Section'].strip(),
                'feature_name': r['Feature Name'].strip(),
                'input_type': r['Input Type'].strip(),
                'sequence_no': int(r['Order']) if r['Order'].strip().isdigit() else 0,
                'is_active': r['Status'].strip().lower() == 'active',
                'options': [opt.strip() for opt in r['Options'].split('|') if opt.strip()] if r['Options'].strip() else []
            })

    async with AsyncSessionLocal() as db:
        # Fetch all existing features
        result = await db.execute(select(SpecialFeatureMaster))
        existing_features = {f.feature_name.lower(): f for f in result.scalars().all()}

        processed_feature_ids = set()

        for r in rows:
            fname_lower = r['feature_name'].lower()
            if fname_lower in existing_features:
                feat = existing_features[fname_lower]
                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"Updated feature: {r['feature_name']}")
            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"Added feature: {r['feature_name']}")
            
            processed_feature_ids.add(feat.feature_id)

            # Update options for this feature
            opt_result = await db.execute(select(SpecialFeatureOptionMaster).where(SpecialFeatureOptionMaster.feature_id == feat.feature_id))
            existing_options = {o.option_value.lower(): o for o in opt_result.scalars().all()}

            for idx, opt_text in enumerate(r['options'], start=1):
                opt_lower = opt_text.lower()
                if opt_lower in existing_options:
                    opt = existing_options[opt_lower]
                    opt.option_value = opt_text
                    opt.sequence_no = idx
                    opt.is_active = True
                else:
                    opt = SpecialFeatureOptionMaster(
                        option_id=uuid.uuid4(),
                        feature_id=feat.feature_id,
                        option_value=opt_text,
                        sequence_no=idx,
                        is_active=True
                    )
                    db.add(opt)
            
            # Deactivate options not present in CSV
            csv_opt_lowers = {opt.lower() for opt in r['options']}
            for opt in existing_options.values():
                if opt.option_value.lower() not in csv_opt_lowers:
                    opt.is_active = False

        # Deactivate features not present in CSV
        for fname, feat in existing_features.items():
            if feat.feature_id not in processed_feature_ids:
                feat.is_active = False
                print(f"Deactivated feature not in CSV: {feat.feature_name}")

        await db.commit()
        print("Special features update completed successfully!")

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