"""Migrate collaborators from Altafonte."""

import logging

import sqlalchemy

from collaborator.api import app
from collaborator.models.rds.collaborator_persister import CollaboratorPersister
from collaborator.utils.typing import ACCOUNT_TYPE_VENDOR, Account
from scripts import script_util
from scripts.altafonte_migration.utils import SF_ABO_SCHEMA, SF_ART_REL_SCHEMA


def get_all_collaborators_with_splits():
    """Get all collaborators that are asigned a split."""
    snowflake_conn = script_util.snowflake_connection()

    select_sql = f"""
        SELECT
            ANY_VALUE(su.username) AS name,
            ANY_VALUE(u.pde_vendor_id) AS vendor_id,
            CASE
                WHEN
                    --- Splits that exclude producer rights
                    SUM(COALESCE(b.exclude_producer_rights, 0))
                    --- Splits that include producer rights
                    > SUM(CASE
                        WHEN COALESCE(b.exclude_producer_rights, 0) = 1 THEN 0
                        ELSE 1
                    END)
                THEN FALSE
                ELSE TRUE
            END AS performance_rights,
            NULL AS participant_id,
            NULL AS recipient_id,
            CONCAT('ABO-', su.user_id) AS internal_id,
            ANY_VALUE(su.currency) AS currency
        FROM {SF_ABO_SCHEMA}.beneficiaries b
        LEFT JOIN {SF_ABO_SCHEMA}.users su
            ON b.user_id = su.user_id AND su._fivetran_deleted = FALSE
        LEFT JOIN {SF_ABO_SCHEMA}.users u
            ON su.account_id = u.user_ID AND u._fivetran_deleted = FALSE
        LEFT JOIN {SF_ART_REL_SCHEMA}.vendor v
            ON u.pde_vendor_id = v.vendor_id AND v._fivetran_deleted = FALSE
        WHERE b._fivetran_deleted = FALSE
        GROUP BY su.user_id, v.name
        ORDER BY v.name
        ;
    """

    return (
        snowflake_conn.execute(
            sqlalchemy.text(select_sql),
        )
        .mappings()
        .all()
    )


def insert_collaborators_using_persister(collaborators):
    """Insert a list of collaborators one by one using the persister."""
    logging.info(
        f"Inserting {len(collaborators)} collaborators using CollaboratorPersister"
    )

    filtered_collaborators = list(
        filter(lambda collab: collab["vendor_id"], collaborators)
    )
    diff = len(collaborators) - len(filtered_collaborators)
    logging.warning(f"Found {diff} collaborators without vendor_id")
    for collab in filtered_collaborators:
        collaborator_name = collab["name"]
        account = Account(id=collab["vendor_id"], type=ACCOUNT_TYPE_VENDOR)
        internal_id = collab["internal_id"]
        performance_rights = collab.get("performance_rights", True)

        with app.app_context():
            CollaboratorPersister.create_collaborator(
                collaborator_name=collaborator_name,
                account=account,
                subaccount_id=None,
                participant_id=None,
                collaborator_type=None,
                currency=collab["currency"],
                description=None,
                internal_id=internal_id,
                performance_rights=performance_rights,
            )
    logging.info(f"{len(filtered_collaborators)} Collaborators inserted")


logging.info("Migrating collaborators from Altafonte...")
logging.info("Getting all collaborators with splits...")
collaborators = get_all_collaborators_with_splits()
logging.info(f"Found {len(collaborators)} collaborators")

logging.info("Inserting collaborators...")
insert_collaborators_using_persister(collaborators)