from sqlalchemy.orm import Session
from app.models.visafly_config import VisaflyConfig
from app.schemas.visafly_config import VisaflyConfigCreate
from typing import List

class VisaflyConfigService:

    @staticmethod
    def create(db: Session, obj_in: VisaflyConfigCreate) -> VisaflyConfig:
        db_obj = VisaflyConfig(**obj_in.dict())
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj

    @staticmethod
    def get_submission_countries(db: Session) -> List[dict]:
        rows = db.query(
            VisaflyConfig.submission_country,
            VisaflyConfig.submission_country_code
        ).distinct().all()
        return [{"country": r[0], "country_code": r[1]} for r in rows]

    @staticmethod
    def get_cities_by_country(db: Session, country_code: str) -> List[dict]:
        rows = db.query(
            VisaflyConfig.submission_city,
            VisaflyConfig.submission_city_code
        ).filter(VisaflyConfig.submission_country_code == country_code).distinct().all()
        return [{"city": r[0], "city_code": r[1]} for r in rows]

    @staticmethod
    def get_travel_countries_by_city(db: Session, city_code: str) -> List[dict]:
        rows = db.query(
            VisaflyConfig.travel_country,
            VisaflyConfig.travel_country_code,
            VisaflyConfig.visa_type,
            VisaflyConfig.provider,
            VisaflyConfig.field_requirement_type
        ).filter(VisaflyConfig.submission_city_code == city_code).all()

        return [
            {
                "travel_country": r[0],
                "travel_country_code": r[1],
                "visa_type": r[2],
                "provider": r[3],
                "field_requirement_type": r[4]
            } for r in rows
        ]