"""Snowflake queries used to validate adjustments."""

GET_CLOSE_BALANCE_STATUSES = """
    SELECT
        sppe.reference_payment_entity_id AS payment_entity_id,
        s.action_status AS action_status
    FROM $db.$schema.STATEMENT_PERIOD_PAYMENT_ENTITY sppe
    JOIN $db.$schema.ABACUS_STATE s
        ON s.parent_table_name = 'statement_period_payment_entity'
        AND s.parent_table_id = sppe.statement_period_payment_entity_id
    WHERE sppe.statement_period_id = $statement_period_id
        AND s.action_name = 'close_balance'
"""

GET_ACCOUNTS = """
    SELECT
        account_id
    FROM $db.$schema.account
    WHERE account_id IN ($account_ids);
"""

GET_ACCOUNT_CONTRACTS = """
    SELECT
        account_id,
        contract_id
    FROM $db.$schema.account_contract
    WHERE $where_clause;
"""

GET_PAYMENT_ENTITIES = """
    SELECT
        account_id,
        payment_entity_id
    FROM $db.$schema.account_payment_term
    WHERE account_id IN ($account_ids);
"""

GET_DISPLAY_UPCS = """
    SELECT
        display_upc,
        listagg(upc, ',') as upcs
    FROM $db.art_relations_prod_art_relations.releases
    WHERE upc != display_upc AND display_upc IN ($display_upcs)
    GROUP BY display_upc
    ORDER BY display_upc;
"""

GET_CONTRACT_TERMS = """
    SELECT
        contract_id,
        term_type,
        attachments
    FROM $db.$schema.contract_term
    WHERE term_type = '$term_type'
        AND contract_id IN ($contract_ids)
        AND deleted_by IS NULL
        AND deleted_at IS NULL;
"""

GET_ACCOUNT_UPCS = """
    SELECT
        v.vendor_id AS vendor_id,
        r.upc AS upc,
        r.display_upc AS display_upc
    FROM $db.art_relations_prod_art_relations.releases r
    JOIN $db.art_relations_prod_art_relations.project p
        ON p.project_id = r.project_id
    JOIN $db.art_relations_prod_art_relations.vendor v
        ON v.vendor_id = p.vendor_id
    WHERE $where_clause;
"""

GET_STATEMENT_PERIODS = """
    SELECT
        statement_year,
        statement_month,
        statement_period_status
    FROM $db.$schema.statement_period
    WHERE statement_year IN ($statement_years);
"""

GET_ADJUSTMENT_TYPES = """
    SELECT type_name
    FROM $db.$schema.reference_adjustment_type;
"""