"""SQL query templates for Artist Roster operations.

Environment Configuration:
- Centralized in common.environment module
- Local runs: target=qa (default in Makefile)
- Snowflake deployment: Detects schema from current Snowflake context
"""

from common.environment import SCHEMA_NAME

# Roster view query template - 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
# WHERE clause, LIMIT and OFFSET are injected dynamically
ROSTER_VIEW_QUERY_TEMPLATE = 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 {{where_clause}}
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 (? > 0 AND VENDOR_ID = ?)
   OR (? != '' AND NAME ILIKE CONCAT('%', ?, '%'))
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 (? != '' AND ID = ?)
   OR (? != '' AND NAME ILIKE CONCAT('%', ?, '%'))
   OR (? != '' AND 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 = ?
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 = ?
  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 = ?
  AND GLOBAL_PARTICIPANT_ID = ?
  AND SUBACCOUNT_ID = ?
UNION ALL
SELECT 'LOCAL_REP' as existing_roster
FROM {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP
WHERE VENDOR_ID = ?
  AND GLOBAL_PARTICIPANT_ID = ?
  AND 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 = ?
  AND GLOBAL_PARTICIPANT_ID = ?
  AND SUBACCOUNT_ID = ?
LIMIT 1
"""

DUPLICATE_CHECK_LOCAL_REP = f"""
SELECT 1 FROM {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP
WHERE VENDOR_ID = ?
  AND GLOBAL_PARTICIPANT_ID = ?
  AND SUBACCOUNT_ID = ?
  AND COUNTRY_CODE = ?
LIMIT 1
"""

DUPLICATE_CHECK_ARTIST_ROSTER = f"""
SELECT 1 FROM {SCHEMA_NAME}.ARTIST_ROSTER
WHERE VENDOR_ID = ?
  AND GLOBAL_PARTICIPANT_ID = ?
  AND 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
    ? AS GLOBAL_PARTICIPANT_ID,
    ? AS VENDOR_ID,
    ? AS SUBACCOUNT_ID,
    ? AS STATUS,
    ? 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
    ? AS GLOBAL_PARTICIPANT_ID,
    ? AS VENDOR_ID,
    ? AS SUBACCOUNT_ID,
    ? 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
    ? AS GLOBAL_PARTICIPANT_ID,
    ? AS VENDOR_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 = ?
  AND GLOBAL_PARTICIPANT_ID = ?
  AND SUBACCOUNT_ID = ?
"""

DELETE_FROM_LOCAL_REP = f"""
DELETE FROM {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP
WHERE VENDOR_ID = ?
  AND GLOBAL_PARTICIPANT_ID = ?
  AND SUBACCOUNT_ID = ?
  AND COUNTRY_CODE = ?
"""

DELETE_FROM_LOCAL_REP_ALL_COUNTRIES = f"""
DELETE FROM {SCHEMA_NAME}.ARTIST_ROSTER_LOCAL_REP
WHERE VENDOR_ID = ?
  AND GLOBAL_PARTICIPANT_ID = ?
  AND SUBACCOUNT_ID = ?
"""

DELETE_FROM_ARTIST_ROSTER = f"""
DELETE FROM {SCHEMA_NAME}.ARTIST_ROSTER
WHERE VENDOR_ID = ?
  AND GLOBAL_PARTICIPANT_ID = ?
  AND SUBACCOUNT_ID = ?
"""

# Virtual Participant queries
CHECK_VP_NAME_EXISTS = f"""
SELECT 1 AS name_exists
FROM {SCHEMA_NAME}.VIRTUAL_PARTICIPANT
WHERE UPPER(NAME) = UPPER(?)
LIMIT 1
"""

CHECK_SPOTIFY_IN_GLOBAL_PARTICIPANT = """
SELECT 1 AS exists_in_global
FROM orchard_app_reporting.delphi_prod.GLOBAL_PARTICIPANT
WHERE SPOTIFY_ID = ?
  AND NOT _FIVETRAN_DELETED
LIMIT 1
"""

CHECK_SPOTIFY_IN_VIRTUAL_PARTICIPANT = f"""
SELECT 1 AS exists_in_vp
FROM {SCHEMA_NAME}.VIRTUAL_PARTICIPANT
WHERE SPOTIFY_ID = ?
LIMIT 1
"""

GET_VIRTUAL_PARTICIPANT_BY_NAME = f"""
SELECT ID
FROM {SCHEMA_NAME}.VIRTUAL_PARTICIPANT
WHERE UPPER(NAME) = UPPER(?)
LIMIT 1
"""

# Search pending artists in VIRTUAL_PARTICIPANT
SEARCH_PENDING_ARTISTS = f"""
SELECT ID, NAME, TYPE, SPOTIFY_ID, CREATED_AT, CREATED_BY
FROM {SCHEMA_NAME}.VIRTUAL_PARTICIPANT
WHERE TYPE = 'PENDING_ARTIST'
  {{name_filter}}
ORDER BY NAME
LIMIT 50
"""

# Check Spotify ID uniqueness in VP excluding a specific record
CHECK_SPOTIFY_IN_VIRTUAL_PARTICIPANT_EXCLUDING = f"""
SELECT 1 AS exists_in_vp
FROM {SCHEMA_NAME}.VIRTUAL_PARTICIPANT
WHERE SPOTIFY_ID = ?
  AND ID != ?
LIMIT 1
"""

# Update SPOTIFY_ID on a VIRTUAL_PARTICIPANT record
UPDATE_VIRTUAL_PARTICIPANT_SPOTIFY_ID = f"""
UPDATE {SCHEMA_NAME}.VIRTUAL_PARTICIPANT
SET SPOTIFY_ID = ?,
    UPDATED_AT = CURRENT_TIMESTAMP(),
    UPDATED_BY = ?
WHERE ID = ?
  AND TYPE = 'PENDING_ARTIST'
"""