"""SQL query templates."""

GET_ACCOUNTS_ELIGIBLE_FOR_PAYMENT = """
SELECT DISTINCT
    a.account_id,
    a.account_name,
    'active' AS eligibility_status,
    ati.country_of_tax_residence,
    apt.currency_code,
    apt.payment_entity_id,
    apt.payment_minimum,
    apt.payment_schedule,
    la.current_balance,
    la.contracts_payable,
    ap.payoneer_program_id
FROM account AS a
    INNER JOIN account_payee AS ap ON a.account_id = ap.account_id
    INNER JOIN account_tax_info AS ati ON a.account_id = ati.account_id
    INNER JOIN account_payment_term AS apt ON a.account_id = apt.account_id
    INNER JOIN (
        SELECT
            account_id,
            SUM(current_balance) AS current_balance,
            JSON_ARRAYAGG(JSON_OBJECT(
                    "contract_id", contract_id,
                    "current_balance", current_balance,
                    "currency_code", currency_code
            )) AS contracts_payable
        FROM (
            SELECT lac.account_id, lac.contract_id, lac.currency_code, current_balance
            FROM ledger_account_contract_current_balance AS laccb
                INNER JOIN ledger_account_contract lac ON
                    lac.ledger_account_contract_id = laccb.ledger_account_contract_id
            WHERE lac.current_balance > 0
        ) AS payable_balances
       GROUP BY account_id
    ) AS la ON a.account_id = la.account_id
    INNER JOIN payment_minimum AS pm ON apt.currency_code = pm.currency_code
    INNER JOIN payment_group AS pg ON pg.payment_group_id = {payment_group_id}
    INNER JOIN abacus_state AS ap_st_1
        ON ap_st_1.parent_table_name = 'account_payee'
            AND ap_st_1.parent_table_id = ap.account_payee_id
            AND ap_st_1.action_name = 'payment_eligibility'
    INNER JOIN abacus_state AS ap_st_2
        ON ap_st_2.parent_table_name = 'account_payee'
            AND ap_st_2.parent_table_id = ap.account_payee_id
            AND ap_st_2.action_name = 'tax_eligibility'
    LEFT OUTER JOIN payment_hold AS ph ON a.account_id = ph.account_id
    LEFT OUTER JOIN payment_group_payment_account AS pa ON pa.account_id = a.account_id
        AND pa.prior_payment_group_payment_id IS NULL
        AND pa.deleted_at IS NULL
        AND pa.deleted_by IS NULL
    LEFT OUTER JOIN abacus_state AS pa_st
        ON pa_st.parent_table_name = 'payment_group_payment_account'
            AND pa_st.parent_table_id = pa.payment_group_payment_account_id
            AND pa_st.action_name = 'send_payments'
WHERE la.current_balance >= apt.payment_minimum
    AND la.current_balance >= pm.check_amount   -- temporarily use check min -dczinsky
    AND ap_st_1.action_status = 'approved'
    AND ap_st_2.action_status = 'complete'
    AND (
        pa.payment_group_payment_account_id IS NULL -- no previous payment exists
        OR pa_st.abacus_state_id IS NULL -- the payment was never sent
        OR pa_st.action_status IN ('complete', 'rejected') -- valid payoneer response
        OR (
            pa_st.action_status = 'error'
            AND pa_st.last_modified < ap_st_1.last_modified
        ) -- the last payment failed before the account became payment eligible
    )
    AND (
        pg.group_criteria->'$.currency_codes' IS NULL
        OR pg.group_criteria->'$.currency_codes'
            LIKE CONCAT('%%', apt.currency_code, '%%')
    )
    AND (
        pg.group_criteria->'$.reference_payment_entities' IS NULL
        OR JSON_CONTAINS(
            pg.group_criteria->'$.reference_payment_entities', CAST(apt.payment_entity_id as CHAR)  # noqa: E501
        )
    )
    AND (
        pg.group_criteria->'$.payment_schedules' IS NULL
        OR pg.group_criteria->'$.payment_schedules'
            LIKE CONCAT('%%', apt.payment_schedule, '%%')
    )
    AND (
        pg.group_criteria->'$.account_id' IS NULL
        OR CAST(pg.group_criteria->'$.account_id' AS UNSIGNED) = a.account_id
    )
    AND (
        ph.payment_hold_id IS NULL
        OR
        (
            ph.start_date <= DATE(NOW())
            AND ph.is_on_hold = 0
        )
    )
    AND ap.payoneer_program_id IS NOT NULL
    AND ap.payoneer_payee_id IS NOT NULL;
"""


GET_ACCOUNTS_ELIGIBLE_FOR_PAYMENT_CLOSING_BALANCE = """
SELECT DISTINCT
    a.account_id,
    a.account_name,
    ati.country_of_tax_residence,
    apt.currency_code,
    apt.payment_entity_id,
    apt.payment_minimum,
    apt.payment_schedule,
    arrctr.contracts,
    ap.payoneer_program_id
FROM account AS a
    INNER JOIN account_payee AS ap ON a.account_id = ap.account_id
    INNER JOIN account_tax_info AS ati ON a.account_id = ati.account_id
    INNER JOIN account_payment_term AS apt ON a.account_id = apt.account_id
    INNER JOIN (
        SELECT
            account_id,
            JSON_ARRAYAGG(contract_id) AS contracts
        FROM account_contract AS ac
        GROUP BY account_id
    ) AS arrctr ON a.account_id = arrctr.account_id
    INNER JOIN payment_minimum AS pm ON apt.currency_code = pm.currency_code
    INNER JOIN payment_group AS pg ON pg.payment_group_id = :payment_group_id
    INNER JOIN abacus_state AS ap_st_1
        ON ap_st_1.parent_table_name = 'account_payee'
            AND ap_st_1.parent_table_id = ap.account_payee_id
            AND ap_st_1.action_name = 'payment_eligibility'
    INNER JOIN abacus_state AS ap_st_2
        ON ap_st_2.parent_table_name = 'account_payee'
            AND ap_st_2.parent_table_id = ap.account_payee_id
            AND ap_st_2.action_name = 'tax_eligibility'
    LEFT OUTER JOIN payment_hold AS ph ON a.account_id = ph.account_id
    LEFT OUTER JOIN payment_group_payment_account AS pa ON pa.account_id = a.account_id
        AND pa.prior_payment_group_payment_id IS NULL
        AND pa.deleted_at IS NULL
        AND pa.deleted_by IS NULL
    LEFT OUTER JOIN abacus_state AS pa_st
        ON pa_st.parent_table_name = 'payment_group_payment_account'
            AND pa_st.parent_table_id = pa.payment_group_payment_account_id
            AND pa_st.action_name = 'send_payments'
WHERE ap_st_1.action_status = 'approved'
    AND ap_st_2.action_status = 'complete'
    AND (
        pa.payment_group_payment_account_id IS NULL -- no previous payment exists
        OR pa_st.abacus_state_id IS NULL -- the payment was never sent
        OR pa_st.action_status IN ('complete', 'rejected') -- valid payoneer response
        OR (
            pa_st.action_status = 'error'
            AND pa_st.last_modified < ap_st_1.last_modified
        ) -- the last payment failed before the account became payment eligible
    )
    AND (
        pg.group_criteria->'$.currency_codes' IS NULL
        OR pg.group_criteria->'$.currency_codes'
            LIKE CONCAT('%%', apt.currency_code, '%%')
    )
    AND (
        pg.group_criteria->'$.reference_payment_entities' IS NULL
        OR JSON_CONTAINS(
            pg.group_criteria->'$.reference_payment_entities', CAST(apt.payment_entity_id as CHAR)  # noqa: E501
        )
    )
    AND (
        pg.group_criteria->'$.payment_schedules' IS NULL
        OR pg.group_criteria->'$.payment_schedules'
            LIKE CONCAT('%%', apt.payment_schedule, '%%')
    )
    AND (
        pg.group_criteria->'$.account_id' IS NULL
        OR CAST(pg.group_criteria->'$.account_id' AS UNSIGNED) = a.account_id
    )
    AND (
        pg.group_criteria->'$.account_ids' IS NULL
        OR a.account_id MEMBER OF(pg.group_criteria->'$.account_ids')
    )
    AND (
        pg.group_criteria->'$.reference_agreement_types' IS NULL
        OR JSON_CONTAINS(
            pg.group_criteria->'$.reference_agreement_types', CAST(apt.agreement_type_id as CHAR)  # noqa: E501
        )
    )
    AND (
        ph.payment_hold_id IS NULL
        OR
        (
            ph.start_date <= DATE(NOW())
            AND ph.is_on_hold = 0
        )
    )
    AND ap.payoneer_program_id IS NOT NULL
    AND ap.reference_payment_type_id IN :bulk_payouts_payoneer_payment_types;
"""