"""Ledger Query Template."""

LEDGER_ACCOUNT_CONTRACT_QUERY = """
    SELECT DISTINCT
        ledger_account_contract_id,
        account_id,
        contract_id,
        currency_code,
        date,
        statement_period_id,
        transaction_type,
        accounting_period_id,
        ledger.previous_balance AS opening_balance,
        ledger.currency_amount AS amount,
        ledger.current_balance AS ending_balance
    FROM (
    (
        SELECT DISTINCT
            lac.*,
            DATE(ae.event_date) AS date,
            ae.statement_period_id AS statement_period_id,
            ae.event_name AS transaction_type,
            ar.accounting_period_id as accounting_period_id
        FROM ledger_account_contract as lac
        INNER JOIN abacus_event AS ae ON ae.abacus_event_id = lac.abacus_event_id
        INNER JOIN accounting_run AS ar 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
        WHERE lac.account_id = :account_id AND (
            ae.event_name LIKE '%commit%'
            OR
            ae.event_name IN ('accounting_period_close','take_reserves')
        )
    )  # accounting_run_ledger_query
    UNION
    (
        SELECT DISTINCT
            lac.*,
            DATE(ae.event_date) AS date,
            ae.statement_period_id AS statement_period_id,
            ae.event_name AS transaction_type,
            NULL as accounting_period_id
        FROM ledger_account_contract as lac
        INNER JOIN abacus_event AS ae ON ae.abacus_event_id = lac.abacus_event_id
        INNER JOIN payment_group_payment AS pgp ON ae.target_id = pgp.payment_group_payment_id
        WHERE lac.account_id = :account_id AND
            ae.target_type = 'payment_group_payment' AND
            ae.event_name IN ('send_payments')
    )  # payment_group_payment_ledger_query
    UNION
    (
        SELECT DISTINCT
            lac.*,
            DATE(ae.event_date) AS date,
            ae.statement_period_id AS statement_period_id,
            ae.event_name AS transaction_type,
            NULL as accounting_period_id
        FROM ledger_account_contract as lac
        INNER JOIN abacus_event AS ae ON ae.abacus_event_id = lac.abacus_event_id
        INNER JOIN payment_group_payment_account AS pgpa ON ae.target_id = pgpa.payment_group_payment_account_id
        WHERE lac.account_id = :account_id AND
            ae.target_type = 'payment_group_payment_account' AND
            ae.event_name IN ('payment_returned')
    )  # payment_group_payment_account_ledger_query
    UNION
    (
        SELECT DISTINCT
            lac.*,
            DATE(ae.event_date) AS date,
            ae.statement_period_id AS statement_period_id,
            ae.event_name AS transaction_type,
            NULL as accounting_period_id
        FROM ledger_account_contract as lac
        INNER JOIN abacus_event AS ae ON ae.abacus_event_id = lac.abacus_event_id
        INNER JOIN statement_period AS sp ON ae.target_id = sp.statement_period_id
        WHERE lac.account_id = :account_id AND
            ae.target_type = 'statement_period' AND
            ae.event_name IN ('release_reserves', 'apply_royalty_correction', 'apply_royalty_reversal')
    )  # statement_period_ledger_query
    UNION
    (
        SELECT DISTINCT
            lac.*,
            DATE(ae.event_date) AS date,
            ae.statement_period_id AS statement_period_id,
            ae.event_name AS transaction_type,
            NULL as accounting_period_id
        FROM ledger_account_contract as lac
        INNER JOIN abacus_event AS ae ON ae.abacus_event_id = lac.abacus_event_id
        INNER JOIN statement_period_adjustment_file AS spaf ON ae.target_id = CASE
            WHEN ae.target_type = 'statement_period'
                THEN spaf.statement_period_id
            WHEN ae.target_type = 'statement_period_adjustment_file'
                THEN spaf.statement_period_adjustment_file_id
            END
        WHERE lac.account_id = :account_id AND
            ae.event_name IN ('apply_pending_adjustments')
    )  # statement_period_adjustment_file_ledger_query
    UNION
    (
        SELECT DISTINCT
            lac.*,
            DATE(ae.event_date) AS date,
            ae.statement_period_id AS statement_period_id,
            ae.event_name AS transaction_type,
            NULL as accounting_period_id
        FROM ledger_account_contract as lac
        INNER JOIN abacus_event AS ae ON ae.abacus_event_id = lac.abacus_event_id
        INNER JOIN ledger_contract_advance_applied AS lcaa ON  ae.target_id = CASE
            WHEN ae.target_type = 'contract_advance'
                THEN lcaa.contract_advance_id
            WHEN ae.target_type = 'worksheet_payment_contract_advance'
                THEN lcaa.worksheet_payment_contract_advance_id
            END
        WHERE lac.account_id = :account_id AND
            ae.event_name IN ('confirm_advance_payment','commit_advance_payment','return_advance_payment')
    )  # ledger_contract_advance_applied_ledger_query
    UNION
    (
        SELECT DISTINCT
            lac.*,
            DATE(ae.event_date) AS date,
            ae.statement_period_id AS statement_period_id,
            ae.event_name AS transaction_type,
            NULL as accounting_period_id
        FROM ledger_account_contract as lac
        INNER JOIN abacus_event AS ae ON ae.abacus_event_id = lac.abacus_event_id
        WHERE lac.account_id = :account_id AND ae.event_name IN (
            'commit_batch_payment','commit_vat_summary',
            'commit_withholding_tax','return_batch_payment',
            'return_vat_summary','return_withholding_tax',
            'commit_custom_payment','return_custom_payment'
        )
    )
) AS ledger
    WHERE {where_clause}
    ORDER BY {sort_by} {sort_order}
"""