"""SQL query templates for Artist Roster operations."""

import os

# Schema configuration - defaults to QA, PROD inserts are BLOCKED
FANSIFTER_SCHEMA = os.getenv("FANSIFTER_SCHEMA", "qa")  # qa or prod
SCHEMA_NAME = f"fansifter_app_reporting.{FANSIFTER_SCHEMA}"

# Roster view query - union of all roster tables with vendor/artist lookups
# Groups by unique (vendor_id, artist_uuid, subaccount_id) with single rep_type
# REP_TYPE values match dbt-audience: 'MAIN', 'LOCAL', NULL
ROSTER_VIEW_QUERY = f"""
WITH main AS (
  SELECT VENDOR_ID,
         GLOBAL_PARTICIPANT_ID AS artist_uuid,
         SUBACCOUNT_ID,
         CREATED_AT,
         'MAIN' AS rep_type,
         STATUS       AS main_status,
         CAST(NULL AS VARCHAR) AS country_code,
         IS_ARTIST_TEAM
  FROM {SCHEMA_NAME}.ARTIST_ROSTER_MAIN_REP
), local AS (
  SELECT VENDOR_ID,
         GLOBAL_PARTICIPANT_ID AS artist_uuid,
         SUBACCOUNT_ID,
         CREATED_AT,
         'LOCAL' AS rep_type,
         CAST(NULL AS VARCHAR) AS main_status,
         COUNTRY_CODE,
         CAST(NULL AS BOOLEAN) AS IS_ARTIST_TEAM
  FROM {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP
), non_sme AS (
  SELECT VENDOR_ID,
         GLOBAL_PARTICIPANT_ID AS artist_uuid,
         SUBACCOUNT_ID,
         CREATED_AT,
         CAST(NULL AS VARCHAR) AS rep_type,
         CAST(NULL AS VARCHAR) AS main_status,
         CAST(NULL AS VARCHAR) AS country_code,
         CAST(NULL AS BOOLEAN) AS IS_ARTIST_TEAM
  FROM {SCHEMA_NAME}.ARTIST_ROSTER
), roster AS (
  SELECT * FROM main
  UNION ALL
  SELECT * FROM local
  UNION ALL
  SELECT * FROM non_sme
), aggregated AS (
  SELECT r.VENDOR_ID,
         r.artist_uuid,
         r.SUBACCOUNT_ID,
         MAX(r.rep_type) AS rep_type,
         MAX(r.main_status) AS main_status,
         LISTAGG(DISTINCT r.country_code, ', ') WITHIN GROUP (ORDER BY r.country_code) AS country_codes,
         MAX(r.IS_ARTIST_TEAM) AS is_artist_team,
         MIN(r.CREATED_AT) AS created_at
  FROM roster r
  GROUP BY r.VENDOR_ID, r.artist_uuid, r.SUBACCOUNT_ID
)
SELECT a.VENDOR_ID                AS vendor_id,
       v.NAME                     AS vendor_name,
       cb.DISPLAY_NAME            AS brand_name,
       a.artist_uuid,
       gp.NAME                    AS artist_name,
       a.SUBACCOUNT_ID           AS subaccount_id,
       a.rep_type,
       a.main_status,
       a.country_codes,
       a.is_artist_team,
       a.created_at
FROM aggregated a
LEFT JOIN orchard_app_reporting.delphi_prod.VENDOR v
       ON v.VENDOR_ID = a.VENDOR_ID
       AND NOT v._FIVETRAN_DELETED
LEFT JOIN orchard_app_reporting.delphi_prod.GLOBAL_PARTICIPANT gp
       ON gp.ID = a.artist_uuid
       AND NOT gp._FIVETRAN_DELETED
LEFT JOIN orchard_app_reporting.delphi_prod.COMPANY_BRAND_HAS_LABEL_VENDOR cblv
       ON cblv.VENDOR_ID = a.VENDOR_ID
       AND NOT cblv._FIVETRAN_DELETED
LEFT JOIN orchard_app_reporting.delphi_prod.COMPANY_BRAND cb
       ON cb.UUID = cblv.COMPANY_BRAND_UUID
       AND NOT cb._FIVETRAN_DELETED
WHERE (:vendor_id   IS NULL OR a.VENDOR_ID = :vendor_id)
  AND (:vendor_name IS NULL OR v.NAME ILIKE CONCAT('%', :vendor_name, '%'))
  AND (:artist_uuid IS NULL OR a.artist_uuid = :artist_uuid)
  AND (:artist_name IS NULL OR gp.NAME ILIKE CONCAT('%', :artist_name, '%'))
  AND (:subaccount_id IS NULL OR a.SUBACCOUNT_ID = :subaccount_id)
ORDER BY v.NAME, gp.NAME
LIMIT :limit OFFSET :offset
"""

# Vendor search query
VENDOR_SEARCH_QUERY = """
SELECT VENDOR_ID, NAME
FROM orchard_app_reporting.delphi_prod.VENDOR
WHERE (:vendor_id IS NOT NULL AND VENDOR_ID = :vendor_id)
   OR (:vendor_name IS NOT NULL AND NAME ILIKE CONCAT('%', :vendor_name, '%'))
ORDER BY NAME
LIMIT 50
"""

# Artist search query - supports UUID, name, and Spotify ID
ARTIST_SEARCH_QUERY = """
SELECT DISTINCT ID AS ARTIST_UUID, NAME AS ARTIST_NAME, SPOTIFY_ID
FROM orchard_app_reporting.delphi_prod.GLOBAL_PARTICIPANT
WHERE (:artist_uuid IS NOT NULL AND ID = :artist_uuid)
   OR (:artist_name IS NOT NULL AND NAME ILIKE CONCAT('%', :artist_name, '%'))
   OR (:spotify_id IS NOT NULL AND SPOTIFY_ID = :spotify_id)
ORDER BY NAME
LIMIT 50
"""

# Get all vendors from roster tables with brands (one brand per vendor)
GET_ALL_ROSTER_VENDORS_QUERY = f"""
WITH all_roster_vendors AS (
    SELECT DISTINCT VENDOR_ID FROM {SCHEMA_NAME}.ARTIST_ROSTER
    UNION
    SELECT DISTINCT VENDOR_ID FROM {SCHEMA_NAME}.ARTIST_ROSTER_MAIN_REP
    UNION
    SELECT DISTINCT VENDOR_ID FROM {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP
)
SELECT
    v.VENDOR_ID as vendor_id,
    v.NAME as vendor_name,
    cb.DISPLAY_NAME as brand_name
FROM all_roster_vendors arv
JOIN orchard_app_reporting.delphi_prod.VENDOR v
    ON v.VENDOR_ID = arv.VENDOR_ID
    AND NOT v._FIVETRAN_DELETED
LEFT JOIN orchard_app_reporting.delphi_prod.COMPANY_BRAND_HAS_LABEL_VENDOR cblv
    ON cblv.VENDOR_ID = v.VENDOR_ID
    AND NOT cblv._FIVETRAN_DELETED
LEFT JOIN orchard_app_reporting.delphi_prod.COMPANY_BRAND cb
    ON cb.UUID = cblv.COMPANY_BRAND_UUID
    AND NOT cb._FIVETRAN_DELETED
ORDER BY v.NAME
"""

# Brand detection query - check if vendor has rows in ARTIST_ROSTER
# DEPRECATED: Use IS_VENDOR_SME_QUERY instead
BRAND_DETECTION_QUERY = f"""
SELECT 1 AS is_non_sme
FROM {SCHEMA_NAME}.ARTIST_ROSTER
WHERE VENDOR_ID = :vendor_id
LIMIT 1
"""

# SME brand detection query - check if vendor has Sony Music brand
IS_VENDOR_SME_QUERY = """
SELECT 1 AS is_sme
FROM orchard_app_reporting.delphi_prod.VENDOR v
JOIN orchard_app_reporting.delphi_prod.COMPANY_BRAND_HAS_LABEL_VENDOR cblv
  ON cblv.VENDOR_ID = v.VENDOR_ID
  AND NOT cblv._FIVETRAN_DELETED
JOIN orchard_app_reporting.delphi_prod.COMPANY_BRAND cb
  ON cb.UUID = cblv.COMPANY_BRAND_UUID
  AND NOT cb._FIVETRAN_DELETED
WHERE v.VENDOR_ID = :vendor_id
  AND cb.DISPLAY_NAME = 'Sony Music'
  AND NOT v._FIVETRAN_DELETED
LIMIT 1
"""

# Check if artist exists in SME rosters (for cross-validation)
CHECK_SME_ROSTER_EXISTENCE = f"""
SELECT 'MAIN_REP' as existing_roster
FROM {SCHEMA_NAME}.ARTIST_ROSTER_MAIN_REP
WHERE VENDOR_ID = :vendor_id
  AND GLOBAL_PARTICIPANT_ID = :artist_uuid
  AND SUBACCOUNT_ID = :subaccount_id
UNION ALL
SELECT 'LOCAL_REP' as existing_roster
FROM {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP
WHERE VENDOR_ID = :vendor_id
  AND GLOBAL_PARTICIPANT_ID = :artist_uuid
  AND SUBACCOUNT_ID = :subaccount_id
"""

# Duplicate check queries - DEPRECATED: MERGE queries handle duplicates
DUPLICATE_CHECK_MAIN_REP = f"""
SELECT 1 FROM {SCHEMA_NAME}.ARTIST_ROSTER_MAIN_REP
WHERE VENDOR_ID = :vendor_id
  AND GLOBAL_PARTICIPANT_ID = :artist_uuid
  AND SUBACCOUNT_ID = :subaccount_id
LIMIT 1
"""

DUPLICATE_CHECK_LOCAL_REP = f"""
SELECT 1 FROM {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP
WHERE VENDOR_ID = :vendor_id
  AND GLOBAL_PARTICIPANT_ID = :artist_uuid
  AND SUBACCOUNT_ID = :subaccount_id
  AND COUNTRY_CODE = :country_code
LIMIT 1
"""

DUPLICATE_CHECK_ARTIST_ROSTER = f"""
SELECT 1 FROM {SCHEMA_NAME}.ARTIST_ROSTER
WHERE VENDOR_ID = :vendor_id
  AND GLOBAL_PARTICIPANT_ID = :artist_uuid
  AND SUBACCOUNT_ID = :subaccount_id
LIMIT 1
"""

# MERGE queries - prevent duplicates without race conditions
MERGE_MAIN_REP = f"""
MERGE INTO {SCHEMA_NAME}.ARTIST_ROSTER_MAIN_REP t
USING (
  SELECT
    :artist_uuid AS GLOBAL_PARTICIPANT_ID,
    :vendor_id AS VENDOR_ID,
    :subaccount_id AS SUBACCOUNT_ID,
    :status AS STATUS,
    :is_artist_team AS IS_ARTIST_TEAM
) s
ON (
  t.GLOBAL_PARTICIPANT_ID = s.GLOBAL_PARTICIPANT_ID
  AND t.VENDOR_ID = s.VENDOR_ID
  AND t.SUBACCOUNT_ID = s.SUBACCOUNT_ID
)
WHEN NOT MATCHED THEN INSERT
  (GLOBAL_PARTICIPANT_ID, VENDOR_ID, SUBACCOUNT_ID, STATUS, IS_ARTIST_TEAM)
VALUES
  (s.GLOBAL_PARTICIPANT_ID, s.VENDOR_ID, s.SUBACCOUNT_ID, s.STATUS, s.IS_ARTIST_TEAM)
"""

MERGE_LOCAL_REP = f"""
MERGE INTO {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP t
USING (
  SELECT
    :artist_uuid AS GLOBAL_PARTICIPANT_ID,
    :vendor_id AS VENDOR_ID,
    :subaccount_id AS SUBACCOUNT_ID,
    :country_code AS COUNTRY_CODE
) s
ON (
  t.GLOBAL_PARTICIPANT_ID = s.GLOBAL_PARTICIPANT_ID
  AND t.VENDOR_ID = s.VENDOR_ID
  AND t.SUBACCOUNT_ID = s.SUBACCOUNT_ID
  AND t.COUNTRY_CODE = s.COUNTRY_CODE
)
WHEN NOT MATCHED THEN INSERT
  (GLOBAL_PARTICIPANT_ID, VENDOR_ID, SUBACCOUNT_ID, COUNTRY_CODE)
VALUES
  (s.GLOBAL_PARTICIPANT_ID, s.VENDOR_ID, s.SUBACCOUNT_ID, s.COUNTRY_CODE)
"""

MERGE_ARTIST_ROSTER = f"""
MERGE INTO {SCHEMA_NAME}.ARTIST_ROSTER t
USING (
  SELECT
    :artist_uuid AS GLOBAL_PARTICIPANT_ID,
    :vendor_id AS VENDOR_ID,
    :subaccount_id AS SUBACCOUNT_ID
) s
ON (
  t.GLOBAL_PARTICIPANT_ID = s.GLOBAL_PARTICIPANT_ID
  AND t.VENDOR_ID = s.VENDOR_ID
  AND t.SUBACCOUNT_ID = s.SUBACCOUNT_ID
)
WHEN NOT MATCHED THEN INSERT
  (GLOBAL_PARTICIPANT_ID, VENDOR_ID, SUBACCOUNT_ID)
VALUES
  (s.GLOBAL_PARTICIPANT_ID, s.VENDOR_ID, s.SUBACCOUNT_ID)
"""

# DELETE queries for roster entry deletion
DELETE_FROM_MAIN_REP = f"""
DELETE FROM {SCHEMA_NAME}.ARTIST_ROSTER_MAIN_REP
WHERE VENDOR_ID = :vendor_id
  AND GLOBAL_PARTICIPANT_ID = :artist_uuid
  AND SUBACCOUNT_ID = :subaccount_id
"""

DELETE_FROM_LOCAL_REP = f"""
DELETE FROM {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP
WHERE VENDOR_ID = :vendor_id
  AND GLOBAL_PARTICIPANT_ID = :artist_uuid
  AND SUBACCOUNT_ID = :subaccount_id
  AND COUNTRY_CODE = :country_code
"""

DELETE_FROM_LOCAL_REP_ALL_COUNTRIES = f"""
DELETE FROM {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP
WHERE VENDOR_ID = :vendor_id
  AND GLOBAL_PARTICIPANT_ID = :artist_uuid
  AND SUBACCOUNT_ID = :subaccount_id
"""

DELETE_FROM_ARTIST_ROSTER = f"""
DELETE FROM {SCHEMA_NAME}.ARTIST_ROSTER
WHERE VENDOR_ID = :vendor_id
  AND GLOBAL_PARTICIPANT_ID = :artist_uuid
  AND SUBACCOUNT_ID = :subaccount_id
"""