import sys
import os
import asyncio
import uuid
import shutil
import pandas as pd
from sqlalchemy import select

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 SeatingLayoutMaster

def safe_copy(src, dst):
    if os.path.exists(src):
        shutil.copy2(src, dst)
        return True
    return False

async def seed_seating_layouts(excel_path=None, pdf_source_dir=None):
    # Find project root (assumes we're in backend/scripts)
    backend_dir = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
    project_root = os.path.abspath(os.path.join(backend_dir, '..'))

    # Autodetect Excel Path
    if not excel_path:
        possible_excel_paths = [
            os.path.join(project_root, 'docs', 'Variant list.xlsx'),
            os.path.join(project_root, 'Variant list.xlsx'),
            os.path.join(backend_dir, 'docs', 'Variant list.xlsx'),
            os.path.join(backend_dir, 'Variant list.xlsx')
        ]
        for p in possible_excel_paths:
            if os.path.exists(p):
                excel_path = p
                break

    if not excel_path or not os.path.exists(excel_path):
        print(f"Error: Could not find 'Variant list.xlsx' in any expected locations.")
        print("Please provide the path manually using --excel <path>")
        sys.exit(1)
        
    print(f"Using Excel file: {excel_path}")

    # Autodetect PDF dir
    if not pdf_source_dir:
        possible_pdf_dirs = [
            os.path.join(project_root, 'Seat layout'),
            os.path.join(project_root, 'docs', 'Seat layout'),
            os.path.join(backend_dir, 'Seat layout'),
            os.path.join(backend_dir, 'docs', 'Seat layout')
        ]
        for p in possible_pdf_dirs:
            if os.path.exists(p):
                pdf_source_dir = p
                break
                
    if not pdf_source_dir or not os.path.exists(pdf_source_dir):
        print("Warning: Could not find 'Seat layout' folder. PDFs will not be copied.")
    else:
        print(f"Using PDF directory: {pdf_source_dir}")
        
    upload_dir = os.path.join(backend_dir, 'uploads', 'seating_layouts')
    os.makedirs(upload_dir, exist_ok=True)
    
    print("Reading excel...")
    try:
        df = pd.read_excel(excel_path, header=1)
    except Exception as e:
        print(f"Failed to read excel file: {e}")
        sys.exit(1)
    
    async with AsyncSessionLocal() as db:
        print("Clearing existing data...")
        await db.execute(SeatingLayoutMaster.__table__.delete())
        
        count = 0
        copied_pdfs = 0
        for _, row in df.iterrows():
            if pd.isna(row.get('Layout no. ')):
                continue
            
            layout_no = str(row['Layout no. ']).strip()
            chassis_model = str(row.get('Chassis model name ', '')).strip()
            body_type = str(row.get('Front FACE', '')).strip()
            segment = str(row.get('Body Type staff/school/RP', '')).strip()
            door_type = str(row.get('Door type', '')).strip()
            seat_type = str(row.get('Seat type', '')).strip()
            seating_type = str(row.get('seating type', '')).strip()
            seating_capacity = str(row.get('seat capacity', '')).strip()
            
            pdf_name = f"{layout_no}.pdf"
            
            has_pdf = False
            if pdf_source_dir:
                # 1. Check exact match first
                src_pdf = os.path.join(pdf_source_dir, pdf_name)
                dst_pdf = os.path.join(upload_dir, pdf_name)
                
                if safe_copy(src_pdf, dst_pdf):
                    has_pdf = True
                else:
                    # 2. Fallback to case-insensitive scan (fixes Linux case mismatch issues)
                    try:
                        for f in os.listdir(pdf_source_dir):
                            if f.lower() == pdf_name.lower():
                                if safe_copy(os.path.join(pdf_source_dir, f), dst_pdf):
                                    has_pdf = True
                                break
                    except Exception as e:
                        print(f"Failed to scan pdf directory: {e}")
            
            pdf_url = f"/uploads/seating_layouts/{pdf_name}" if has_pdf else None
            if has_pdf: copied_pdfs += 1
            
            layout = SeatingLayoutMaster(
                layout_id=uuid.uuid4(),
                layout_no=layout_no,
                chassis_model=chassis_model,
                body_type=body_type,
                segment=segment,
                door_type=door_type,
                seat_type=seat_type,
                seating_type=seating_type,
                seating_capacity=seating_capacity,
                pdf_url=pdf_url
            )
            db.add(layout)
            count += 1
            
        print("Committing to database...")
        await db.commit()
        print(f"Successfully seeded {count} seating layouts. Found and copied {copied_pdfs} PDFs.")

if __name__ == '__main__':
    import argparse
    parser = argparse.ArgumentParser(description="Seed seating layouts")
    parser.add_argument('--excel', help="Path to Variant list.xlsx")
    parser.add_argument('--pdf-dir', help="Path to Seat layout folder")
    args = parser.parse_args()
    
    asyncio.run(seed_seating_layouts(args.excel, args.pdf_dir))