"""Business logic services for artist roster operations."""

from typing import Any

import pandas as pd
from snowflake.snowpark.session import Session

from common import queries
from common.db import execute_non_query, execute_query
from common.types import (
    AddArtistForm,
    ArtistRow,
    RosterFilters,
    VendorRow,
    VendorWithBrandRow,
)


def search_vendors(
    session: Session, vendor_id: int | None = None, vendor_name: str | None = None
) -> list[VendorRow]:
    """
    Search for vendors by ID or name.

    Args:
        session: Active Snowpark session
        vendor_id: Optional vendor ID for exact match
        vendor_name: Optional vendor name for pattern match

    Returns:
        List of VendorRow objects
    """
    params = {"vendor_id": vendor_id, "vendor_name": vendor_name}
    df = execute_query(session, queries.VENDOR_SEARCH_QUERY, params)

    if df.empty:
        return []

    return [VendorRow(**row) for row in df.to_dict("records")]  # type: ignore[misc]


def search_artists(
    session: Session,
    artist_uuid: str | None = None,
    artist_name: str | None = None,
    spotify_id: str | None = None,
) -> list[ArtistRow]:
    """
    Search for artists by UUID, name, or Spotify ID.

    Args:
        session: Active Snowpark session
        artist_uuid: Optional artist UUID for exact match
        artist_name: Optional artist name for pattern match
        spotify_id: Optional Spotify ID for exact match

    Returns:
        List of ArtistRow objects
    """
    params = {
        "artist_uuid": artist_uuid,
        "artist_name": artist_name,
        "spotify_id": spotify_id,
    }
    df = execute_query(session, queries.ARTIST_SEARCH_QUERY, params)

    if df.empty:
        return []

    return [ArtistRow(**row) for row in df.to_dict("records")]  # type: ignore[misc]


def get_all_roster_vendors(session: Session) -> list[VendorWithBrandRow]:
    """
    Get all unique vendors from roster tables with their brands.
    Each vendor has exactly one brand.
    Returns list of vendors sorted by name.

    Args:
        session: Active Snowpark session

    Returns:
        List of VendorWithBrandRow objects sorted by vendor name
    """
    df = execute_query(session, queries.GET_ALL_ROSTER_VENDORS_QUERY)

    if df.empty:
        return []

    return [VendorWithBrandRow(**row) for row in df.to_dict("records")]  # type: ignore[misc]


def is_vendor_non_sme(session: Session, vendor_id: int) -> bool:
    """
    Check if a vendor is non-SME brand.

    DEPRECATED: Use is_vendor_sme() instead for accurate brand detection.
    A vendor is non-SME if it has at least one row in ARTIST_ROSTER table.

    Args:
        session: Active Snowpark session
        vendor_id: Vendor ID to check

    Returns:
        True if vendor is non-SME, False if SME
    """
    params = {"vendor_id": vendor_id}
    df = execute_query(session, queries.BRAND_DETECTION_QUERY, params)
    return not df.empty


def is_vendor_sme(session: Session, vendor_id: int) -> bool:
    """
    Check if a vendor has SME (Sony Music) brand.

    Args:
        session: Active Snowpark session
        vendor_id: Vendor ID to check

    Returns:
        True if vendor has Sony Music brand, False otherwise
    """
    params = {"vendor_id": vendor_id}
    df = execute_query(session, queries.IS_VENDOR_SME_QUERY, params)
    return not df.empty


def check_sme_cross_roster_conflict(
    session: Session,
    vendor_id: int,
    artist_uuid: str,
    subaccount_id: int,
    target_roster: str,
) -> tuple[bool, str | None]:
    """
    Check if artist exists in opposite SME roster.

    Business rule: SME artists cannot be in both MAIN_REP and LOCAL_REP.

    Args:
        session: Active Snowpark session
        vendor_id: Vendor ID
        artist_uuid: Artist UUID
        subaccount_id: Subaccount ID
        target_roster: Target roster type ('MAIN_REP' or 'LOCAL_REP')

    Returns:
        Tuple of (has_conflict: bool, existing_roster: str | None)
    """
    params = {
        "vendor_id": vendor_id,
        "artist_uuid": artist_uuid,
        "subaccount_id": subaccount_id,
    }

    df = execute_query(session, queries.CHECK_SME_ROSTER_EXISTENCE, params)

    if df.empty:
        return False, None

    # Get existing roster types (check for uppercase column name from Snowflake)
    col_name = (
        "EXISTING_ROSTER" if "EXISTING_ROSTER" in df.columns else "existing_roster"
    )
    existing_rosters = df[col_name].tolist()

    # Check for conflict with target roster
    if target_roster == "MAIN_REP" and "LOCAL_REP" in existing_rosters:
        return True, "LOCAL_REP"
    elif target_roster == "LOCAL_REP" and "MAIN_REP" in existing_rosters:
        return True, "MAIN_REP"

    return False, None


def check_duplicate(session: Session, form: AddArtistForm) -> bool:
    """
    Check if artist roster entry already exists.

    Args:
        session: Active Snowpark session
        form: Add artist form data

    Returns:
        True if duplicate exists, False otherwise
    """
    params: dict[str, Any] = {
        "vendor_id": form.vendor_id,
        "artist_uuid": form.artist_uuid,
        "subaccount_id": form.subaccount_id,
    }

    if form.roster_type == "MAIN_REP":
        query = queries.DUPLICATE_CHECK_MAIN_REP
    elif form.roster_type == "LOCAL_REP":
        query = queries.DUPLICATE_CHECK_LOCAL_REP
        params["country_code"] = form.country_code
    else:  # ARTIST_ROSTER
        query = queries.DUPLICATE_CHECK_ARTIST_ROSTER

    df = execute_query(session, query, params)
    return not df.empty


def validate_add_artist(session: Session, form: AddArtistForm) -> list[str]:
    """
    Validate add artist form data.

    Args:
        session: Active Snowpark session
        form: Add artist form data

    Returns:
        List of validation error messages (empty if valid)
    """
    errors = form.validate_fields()

    # Check vendor exists
    vendors = search_vendors(session, vendor_id=form.vendor_id)
    if not vendors:
        errors.append(f"Vendor ID {form.vendor_id} does not exist")
        return errors  # Stop validation if vendor doesn't exist

    # Check artist exists
    artists = search_artists(session, artist_uuid=form.artist_uuid)
    if not artists:
        errors.append(f"Artist UUID {form.artist_uuid} does not exist")
        return errors  # Stop validation if artist doesn't exist

    # Brand detection and roster type validation
    is_sme = is_vendor_sme(session, form.vendor_id)

    if is_sme:
        # SME vendors can only use MAIN_REP or LOCAL_REP
        if form.roster_type not in ["MAIN_REP", "LOCAL_REP"]:
            errors.append(
                f"Vendor {form.vendor_id} is SME brand (Sony Music) and can only add to MAIN_REP or LOCAL_REP"
            )

        # Check cross-roster conflict for SME vendors
        if form.roster_type in ["MAIN_REP", "LOCAL_REP"]:
            has_conflict, existing_roster = check_sme_cross_roster_conflict(
                session,
                form.vendor_id,
                form.artist_uuid,
                form.subaccount_id,
                form.roster_type,
            )
            if has_conflict:
                errors.append(
                    f"Artist already exists in {existing_roster} for this vendor. "
                    f"Cannot add to {form.roster_type}. Use update functionality in the future."
                )
    else:
        # Non-SME vendors can only use ARTIST_ROSTER
        if form.roster_type != "ARTIST_ROSTER":
            errors.append(
                f"Vendor {form.vendor_id} is non-SME brand and can only add to ARTIST_ROSTER"
            )

    return errors


def add_artist_to_roster(session: Session, form: AddArtistForm) -> None:
    """
    Add artist to roster table using MERGE to prevent duplicates.

    Args:
        session: Active Snowpark session
        form: Add artist form data

    Raises:
        ValueError: If validation fails or PROD writes are blocked
    """
    # CRITICAL: Block all PROD writes for safety
    if queries.FANSIFTER_SCHEMA == "prod":
        raise ValueError(
            "PROD writes are BLOCKED for safety. "
            "Use QA schema for testing. To enable PROD writes, set FANSIFTER_SCHEMA=prod environment variable."
        )

    # Validate first
    errors = validate_add_artist(session, form)
    if errors:
        raise ValueError("; ".join(errors))

    # Prepare parameters
    params: dict[str, Any] = {
        "vendor_id": form.vendor_id,
        "artist_uuid": form.artist_uuid,
        "subaccount_id": form.subaccount_id,
    }

    # Select appropriate merge query and add type-specific parameters
    if form.roster_type == "MAIN_REP":
        query = queries.MERGE_MAIN_REP
        params["status"] = form.status
        params["is_artist_team"] = form.is_artist_team
    elif form.roster_type == "LOCAL_REP":
        query = queries.MERGE_LOCAL_REP
        params["country_code"] = form.country_code
    else:  # ARTIST_ROSTER
        query = queries.MERGE_ARTIST_ROSTER

    # Execute merge
    execute_non_query(session, query, params)


def get_roster_view(session: Session, filters: RosterFilters) -> pd.DataFrame:
    """
    Get roster view with filters.

    Args:
        session: Active Snowpark session
        filters: Search filters

    Returns:
        Pandas DataFrame with roster results
    """
    params = {
        "vendor_id": filters.vendor_id,
        "vendor_name": filters.vendor_name,
        "artist_uuid": filters.artist_uuid,
        "artist_name": filters.artist_name,
        "subaccount_id": filters.subaccount_id,
        "limit": filters.limit,
        "offset": filters.offset,
    }

    return execute_query(session, queries.ROSTER_VIEW_QUERY, params)


def delete_roster_entry(
    session: Session,
    vendor_id: int,
    artist_uuid: str,
    subaccount_id: int,
    roster_type: str,
    country_code: str | None = None,
    delete_all_countries: bool = False,
) -> None:
    """
    Delete artist from roster table.

    Args:
        session: Active Snowpark session
        vendor_id: Vendor ID
        artist_uuid: Artist UUID
        subaccount_id: Subaccount ID
        roster_type: Roster type ('MAIN', 'LOCAL', or None for ARTIST_ROSTER)
        country_code: Country code for LOCAL_REP single country deletion
        delete_all_countries: If True, delete all LOCAL_REP countries

    Raises:
        ValueError: If validation fails or PROD writes are blocked
    """
    # CRITICAL: Block all PROD writes for safety
    if queries.FANSIFTER_SCHEMA == "prod":
        raise ValueError(
            "PROD writes are BLOCKED for safety. "
            "Use QA schema for testing. To enable PROD writes, set FANSIFTER_SCHEMA=prod environment variable."
        )

    # Prepare base parameters
    params: dict[str, Any] = {
        "vendor_id": vendor_id,
        "artist_uuid": artist_uuid,
        "subaccount_id": subaccount_id,
    }

    # Select appropriate delete query
    if roster_type == "MAIN":
        query = queries.DELETE_FROM_MAIN_REP
    elif roster_type == "LOCAL":
        if delete_all_countries:
            query = queries.DELETE_FROM_LOCAL_REP_ALL_COUNTRIES
        else:
            if not country_code:
                raise ValueError(
                    "country_code is required for LOCAL_REP single country deletion"
                )
            query = queries.DELETE_FROM_LOCAL_REP
            params["country_code"] = country_code
    else:  # roster_type is None (ARTIST_ROSTER)
        query = queries.DELETE_FROM_ARTIST_ROSTER

    # Execute delete
    execute_non_query(session, query, params)