"""SQL query builders for Seated List Export app."""

TABLE = "FAN_LIVE_EVENTS.PROD.RAW_SEATED_OPT_INS"


def artist_list_query() -> str:
    return f"""
        SELECT DISTINCT ARTIST_NAME
        FROM {TABLE}
        ORDER BY ARTIST_NAME
    """


def source_list_query() -> str:
    return f"""
        SELECT DISTINCT SOURCE
        FROM {TABLE}
        ORDER BY SOURCE
    """


def export_query(
    artist_name: str,
    start_date: str,
    end_date: str,
    label: str,
    sources: list[str] | None = None,
) -> str:
    """Build the CRM template export query.

    Uses bind-style quoting for safety since Snowpark session.sql()
    doesn't support named binds on all paths. Values are escaped inline.
    """
    artist_esc = artist_name.replace("'", "''")
    label_esc = label.replace("'", "''")

    source_clause = ""
    if sources:
        escaped = ", ".join(f"'{s.replace(chr(39), chr(39)*2)}'" for s in sources)
        source_clause = f"AND SOURCE IN ({escaped})"

    return f"""
        WITH ranked AS (
            SELECT *,
                ROW_NUMBER() OVER (
                    PARTITION BY EMAIL
                    ORDER BY
                        -- Prefer rows with more populated fields
                        (CASE WHEN FIRST_NAME IS NOT NULL THEN 1 ELSE 0 END
                       + CASE WHEN LAST_NAME IS NOT NULL THEN 1 ELSE 0 END
                       + CASE WHEN PHONE_NUMBER IS NOT NULL THEN 1 ELSE 0 END
                       + CASE WHEN VENUE_CITY IS NOT NULL THEN 1 ELSE 0 END
                       + CASE WHEN VENUE_STATE IS NOT NULL THEN 1 ELSE 0 END
                       + CASE WHEN VENUE_COUNTRY IS NOT NULL THEN 1 ELSE 0 END
                       + CASE WHEN VENUE_POSTAL_CODE IS NOT NULL THEN 1 ELSE 0 END) DESC,
                        INSERTED_AT DESC
                ) AS rn
            FROM {TABLE}
            WHERE ARTIST_NAME = '{artist_esc}'
              AND REPORT_DATE BETWEEN '{start_date}' AND '{end_date}'
              {source_clause}
        )
        SELECT
            EMAIL                   AS "Email (Required)",
            'TRUE'                  AS "Opt-in (Required)",
            ARTIST_NAME             AS "Mailing List",
            'Seated List'           AS "File Source Description",
            COALESCE(VENUE_COUNTRY, COUNTRY_CODE) AS "Territory(2 digit ISO) (Required)",
            '{label_esc}'           AS "Label (Required)",
            FIRST_NAME              AS "First Name",
            LAST_NAME               AS "Last Name",
            PHONE_NUMBER            AS "Mobile Phone",
            NULL                    AS "Birthdate (MM/DD/YYYY)",
            NULL                    AS "Birthday (MM/DD) US ONLY",
            NULL                    AS "Gender",
            NULL                    AS "Address 1",
            VENUE_CITY              AS "City",
            VENUE_STATE             AS "State",
            VENUE_COUNTRY           AS "Country/Region",
            VENUE_POSTAL_CODE       AS "Postal Code",
            NULL                    AS "Preferred Language",
            NULL                    AS "Facebook Page",
            NULL                    AS "Twitter Handle"
        FROM ranked
        WHERE rn = 1
        ORDER BY EMAIL
    """


def summary_query(
    artist_name: str,
    start_date: str,
    end_date: str,
) -> str:
    """Row count breakdown by SOURCE for the selected artist/date range."""
    artist_esc = artist_name.replace("'", "''")
    return f"""
        SELECT
            SOURCE,
            COUNT(*) AS ROW_COUNT
        FROM {TABLE}
        WHERE ARTIST_NAME = '{artist_esc}'
          AND REPORT_DATE BETWEEN '{start_date}' AND '{end_date}'
        GROUP BY SOURCE
        ORDER BY ROW_COUNT DESC
    """


def date_range_query(artist_name: str) -> str:
    """Get min/max REPORT_DATE for an artist to set sensible date picker defaults."""
    artist_esc = artist_name.replace("'", "''")
    return f"""
        SELECT
            MIN(REPORT_DATE) AS MIN_DATE,
            MAX(REPORT_DATE) AS MAX_DATE
        FROM {TABLE}
        WHERE ARTIST_NAME = '{artist_esc}'
    """