import csv
import os
from sqlalchemy.orm import Session
from api.v1.modules.a76.layouts_csv.common import csv_reader as common_csv_reader
from .models import CartaPorte

def seed_carta_porte(db: Session):
    csv_path = os.path.join(os.path.dirname(__file__), "cartaPorte.csv")
    if not os.path.exists(csv_path):
        print(f"File not found: {csv_path}")
        return

    # Check if already seeded to avoid duplicates
    if db.query(CartaPorte).first():
        print("Carta Porte catalog already has data, skipping...")
        return

    print("Seeding Carta Porte catalog (this might take a while)...")
    
    encoding = common_csv_reader.detect_text_encoding(csv_path)
    with open(csv_path, mode='r', encoding=encoding) as f:
        # User provided comma-separated data
        reader = csv.DictReader(f)
        
        batch_size = 1000
        batch = []
        
        for row in reader:
            # SYSID	CODIGO	DESCRIPCION	PALABRASSIMILARES	MATPELIGROSO	FECHAINICIOVIGENCIA	FECHAFINVIGENCIA
            obj = CartaPorte(
                id=int(row['SYSID']),
                code=row['CODIGO'],
                description=row['DESCRIPCION'],
                similar_words=row.get('PALABRASSIMILARES'),
                is_hazardous=int(row['MATPELIGROSO']) if row['MATPELIGROSO'].isdigit() else 0,
                start_date=row.get('FECHAINICIOVIGENCIA'),
                end_date=row.get('FECHAFINVIGENCIA')
            )
            batch.append(obj)
            
            if len(batch) >= batch_size:
                db.bulk_save_objects(batch)
                db.commit()
                batch = []                

        if batch:
            db.bulk_save_objects(batch)
            db.commit()            
