from sqlalchemy.orm import joinedload
from db import db
from typing import List, Optional, Dict
from utils.test_helpers import sync
from utils.list_utils import exists
from workers.base_worker import BaseWorker
from utils import list_utils
from utils.snowflake.gras_data.product_families_importer import ProductFamiliesImporter
from utils.snowflake.models import GRASProductsQueryResult, GRASProductModel, GRASTrack
from models.product_family import ProductFamily, ProjectProductFamily
from models.product_track import ProductTrack
from models.projects import Project
from external_api.tracks_search.search_service import DelphiSearchService
from external_api.tracks_search.schemas import DelphiTrackSearchQueryParams


class ProductFamiliesWorker(BaseWorker):
    delphi_search_service = DelphiSearchService()

    def execute(self):
        product_families = ProductFamiliesImporter().fetch()

        if not product_families:
            self.logger.error("Products Import error", "No products returned from GRAS")
        else:
            self.logger.info("Products Import", f"Importing {len(product_families)} products")

        mapped_product_families = self.__map_product_families(product_families)
        for project_external_id in mapped_product_families.keys():
            products = mapped_product_families.get(project_external_id, [])
            projects = (
                db.session.query(Project)
                .filter(Project.gras_project_code == project_external_id)
                .options(
                    joinedload(Project.project_product_families),
                )
                .all()
            )
            if not projects:
                continue

            # Right now we can have multiple Project with the same external gras id,
            # so we need to handle this behavior
            for product in products:
                self.__assign_product_family_to_multiple_projects(projects, product)

        self.logger.success("Products Import", "Finished importing products")

        db.session.execute("REFRESH MATERIALIZED VIEW projects_isrcs;")

        self.logger.success("Products Import", "projects_isrcs view refreshed")

        db.session.commit()
        db.session.close()

    def __map_product_families(
        self, product_families: List[GRASProductsQueryResult]
    ) -> Dict[str, List[GRASProductModel]]:
        products: List[GRASProductModel] = []
        for item in product_families:
            current_product = self.__product_from_item(item)
            products.append(current_product)
        return list_utils.group_by(products, lambda x: x.project_id)

    @sync
    async def __search_tracks_by_product(self, product_id: int):
        query_params = DelphiTrackSearchQueryParams(
            product_id=product_id,
            sort_order="asc",
            sort_by="name",
            group_by="isrc"
        )
        response = await self.delphi_search_service.search(query_params)
        return [
            GRASTrack(track.isrc, track.name, track.details, track.type, track.release_date) for track in response.items
        ]

    def __product_from_item(self, item: GRASProductsQueryResult) -> GRASProductModel:
        return GRASProductModel(
            id=item.product_id,
            title=item.product_title,
            project_id=item.project_id,
            type=item.product_type,
            tracks=self.__search_tracks_by_product(item.product_id)
        )

    def __product_track(self, track: GRASTrack):
        track_model = ProductTrack()
        track_model.isrc = track.isrc
        track_model.title = track.name
        track_model.title_supplementary = track.details
        track_model.type = track.type
        track_model.release_date = track.release_date
        return track_model

    def __product_from_model(self, product: GRASProductModel):
        product_model = ProductFamily()
        product_model.external_id = product.id
        product_model.title = product.title
        product_model.type = product.type
        product_model.tracks = [self.__product_track(track) for track in product.tracks if track.isrc is not None]
        return product_model

    def __get_product_family(self, external_product_id: str) -> Optional[ProductFamily]:
        return (
            db.session.query(ProductFamily)
            .options(
                joinedload(ProductFamily.tracks),
            )
            .filter(ProductFamily.external_id == external_product_id)
            .first()
        )

    def __assign_product_family_to_multiple_projects(self, projects: List[Project], product: GRASProductModel):
        product_family = self.__get_product_family(external_product_id=str(product.id))

        if not product_family:
            product_family = self.__product_from_model(product)
            db.session.add(product_family)

        old_tracks_ids = set([item.isrc for item in product_family.tracks])
        new_tracks_ids = set([item.isrc for item in product.tracks if item.isrc is not None])

        to_delete = old_tracks_ids - new_tracks_ids
        to_insert = new_tracks_ids - old_tracks_ids

        [db.session.delete(track) for track in product_family.tracks if track.isrc in to_delete]

        tracks_to_insert = filter(lambda track: track.isrc is not None and track.isrc in to_insert, product.tracks)

        for track in tracks_to_insert:
            track = self.__product_track(track)
            track.product = product_family
            db.session.add(track)

        db.session.flush()

        for project in projects:
            if not exists(project.project_product_families, lambda x: x.product_family_id == product_family.id):
                project_product_family = ProjectProductFamily(project=project, product_family=product_family)
                db.session.add(project_product_family)
                project.project_product_families.append(project_product_family)