"""SQL query templates."""

GET_ACCOUNT_LEDGER_ACCOUNTING_RUN_COMMIT = """
    SELECT DISTINCT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        ar.accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM accounting_run AS ar
        INNER JOIN abacus_event AS ae
            ON ae.target_id =
            CASE
                WHEN ae.target_type = 'accounting_period'
                    THEN  ar.accounting_period_id
                WHEN ae.target_type = 'accounting_run'
                    THEN ar.accounting_run_id
                END
        INNER JOIN ledger_account AS la ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND (ae.event_name LIKE '%commit%'
            OR ae.event_name = 'accounting_period_close'
        )
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_SEND_PAYMENTS = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM payment_group_payment AS pgp
        INNER JOIN abacus_event AS ae
            ON ae.target_id = pgp.payment_group_payment_id
                AND ae.target_type = 'payment_group_payment'
        INNER JOIN ledger_account AS la ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'send_payments'
    ORDER BY la.ledger_account_id DESC
"""


GET_ACCOUNT_LEDGER_PAYMENTS_RETURNED = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM payment_group_payment_account AS pgpa
        INNER JOIN abacus_event AS ae
            ON ae.target_id = pgpa.payment_group_payment_account_id
                AND ae.target_type = 'payment_group_payment_account'
        INNER JOIN ledger_account AS la ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'payment_returned'
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_RESERVE_RELEASED = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM statement_period AS sp
        INNER JOIN abacus_event AS ae
            ON ae.target_id = sp.statement_period_id
                AND ae.target_type = 'statement_period'
        INNER JOIN ledger_account AS la ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'release_reserves'
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_RESERVE_TAKEN = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM accounting_run AS ar
        INNER JOIN abacus_event AS ae
            ON ae.target_id = ar.accounting_run_id
                AND ae.target_type = 'accounting_run'
        INNER JOIN ledger_account AS la ON ae.abacus_event_id = la.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'take_reserves'
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_ADJUSTMENTS_APPLIED = """
    SELECT DISTINCT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM statement_period_adjustment_file AS spf
        INNER JOIN abacus_event AS ae
            ON ae.target_id =
            CASE
                WHEN ae.target_type = 'statement_period'
                    THEN spf.statement_period_id
                WHEN ae.target_type = 'statement_period_adjustment_file'
                    THEN spf.statement_period_adjustment_file_id
            END
        INNER JOIN ledger_account AS la ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'apply_pending_adjustments'
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_CONTRACT_ADVANCE_APPLIED = """
    SELECT DISTINCT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM ledger_contract_advance_applied AS lca
        INNER JOIN abacus_event AS ae
            ON ae.target_id =
                CASE
                    WHEN ae.target_type = 'contract_advance'
                        THEN lca.contract_advance_id
                    WHEN ae.target_type = 'worksheet_payment_contract_advance'
                        THEN lca.worksheet_payment_contract_advance_id
                END
        INNER JOIN ledger_account AS la ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name IN ('confirm_advance_payment', 'commit_advance_payment')
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_APPLY_ROYALTY_CORRECTIONS = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM statement_period AS sp
        INNER JOIN abacus_event AS ae
            ON ae.target_id = sp.statement_period_id
                AND ae.target_type = 'statement_period'
        INNER JOIN ledger_account AS la ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = :event_name
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_RETURN_ADVANCE_PAYMENT = """
    SELECT DISTINCT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM ledger_contract_advance_applied AS lca
        INNER JOIN abacus_event AS ae
            ON ae.target_id = lca.worksheet_payment_contract_advance_id
                AND ae.target_type = 'worksheet_payment_contract_advance'
        INNER JOIN ledger_account AS la ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'return_advance_payment'
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_COMMIT_BATCH_PAYMENT = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM ledger_account AS la
        INNER JOIN abacus_event AS ae ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'commit_batch_payment'
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_COMMIT_VAT_SUMMARY = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM ledger_account AS la
        INNER JOIN abacus_event AS ae ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'commit_vat_summary'
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_COMMIT_WITHHOLDING_TAX = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM ledger_account AS la
        INNER JOIN abacus_event AS ae ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'commit_withholding_tax'
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_RETURN_BATCH_PAYMENT = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM ledger_account AS la
        INNER JOIN abacus_event AS ae ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'return_batch_payment'
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_RETURN_VAT_SUMMARY = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM ledger_account AS la
        INNER JOIN abacus_event AS ae ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'return_vat_summary'
    ORDER BY la.ledger_account_id DESC
"""

GET_ACCOUNT_LEDGER_RETURN_WITHHOLDING_TAX = """
    SELECT
        la.ledger_account_id,
        la.account_id,
        la.contract_id,
        la.currency_code,
        DATE(ae.event_date) AS 'date',
        ae.statement_period_id,
        ae.event_name AS transaction_type,
        NULL AS accounting_period_id,
        la.previous_balance AS opening_balance,
        la.currency_amount AS amount,
        la.current_balance AS ending_balance
    FROM ledger_account AS la
        INNER JOIN abacus_event AS ae ON la.abacus_event_id = ae.abacus_event_id
    WHERE la.account_id = :account_id
        AND ae.event_name = 'return_withholding_tax'
    ORDER BY la.ledger_account_id DESC
"""