"""SQL Queries."""

GET_CONTRACT_VAT_INFO_BY_CONTRACT_IDS = """
    SELECT
        ac.account_id,
        ac.contract_id,
        ati.country_of_tax_residence,
        ati.is_sba_signed AS account_is_sba_signed,
        ref_client.tax_rate AS client_tax_rate,
        CASE
            WHEN ati.is_sba_signed = ref_supplier.is_sba_signed
            THEN ref_supplier.tax_rate
        END AS supplier_tax_rate
    FROM account_tax_info AS ati
        INNER JOIN account_contract AS ac ON ati.account_id = ac.account_id
        INNER JOIN reference_vat_rate AS ref_client
            ON ati.country_of_tax_residence = ref_client.country_of_tax_residence
                AND ref_client.vat_type = 'client'
        INNER JOIN reference_vat_rate AS ref_supplier
            ON ati.country_of_tax_residence = ref_supplier.country_of_tax_residence
                AND ref_supplier.vat_type = 'supplier'
    WHERE ac.contract_id IN :contract_ids
        AND ref_client.is_tax_applicable = 1
        AND ref_supplier.is_tax_applicable = 1
    GROUP BY ac.account_id, ac.contract_id, client_tax_rate, supplier_tax_rate;
"""

CAN_CONTRACTS_BE_DELETED = """
    SELECT lac.contract_id FROM ledger_account_contract lac WHERE lac.contract_id IN :contract_ids
    UNION
    SELECT ca.contract_id FROM contract_advance ca WHERE ca.contract_id IN :contract_ids
    UNION
    SELECT wa.contract_id FROM worksheet_adjustment wa WHERE wa.contract_id IN :contract_ids
    UNION
    SELECT rcc.contract_id
        FROM ledger_accounting_run_balance larb
        INNER JOIN accounting_run ar ON larb.accounting_run_id=ar.accounting_run_id
        INNER JOIN run_controller_contract rcc ON rcc.run_controller_id = ar.run_controller_id
    WHERE rcc.contract_id IN :contract_ids
    AND larb.ledger_accounting_run_balance_id IS NOT NULL
    AND rcc.contract_id = larb.contract_id
"""  # noqa: E501

GET_CONTRACT_DATA = """
    SELECT
        JSON_OBJECT(
            'contract_id', c.contract_id,
            'contract_name', c.contract_name,
            'contract_type', c.contract_type,
            'initial_start_date', c.initial_start_date,
            'execution_date', c.execution_date,
            'term_start', c.term_start,
            'term_end', c.term_end,
            'summary_note', c.summary_note,
            'general_note', c.general_note,
            'is_excluded_from_accounting_run', c.is_excluded_from_accounting_run,
            'is_paythrough_contract', c.is_paythrough_contract,
            'reference_signing_entity_id', c.reference_signing_entity_id,
            'created_by', c.created_by,
            'created_at', c.created_at,
            'last_modified_by', c.last_modified_by,
            'last_modified', c.last_modified
        ) AS contract,
        JSON_OBJECT(
            'account_id', a.account_id,
            'account_name', a.account_name
        ) AS account,
        JSON_OBJECT(
            'contract_lifecycle_id', cl.contract_lifecycle_id,
            'lifecycle_status', cl.lifecycle_status,
            'lifecycle_term_start', cl.lifecycle_term_start,
            'lifecycle_term_end', cl.lifecycle_term_end,
            'renewal_effective', cl.renewal_effective,
            'termination_notice_deadline', cl.termination_notice_deadline,
            'termination_notice_received', cl.termination_notice_received,
            'termination_effective', cl.termination_effective,
            'collection_start', cl.collection_start,
            'collection_end', cl.collection_end,
            'last_renewed', cl.last_renewed
        ) AS lifecycle,
            JSON_OBJECT(
            'contract_lifecycle_schedule_id', cls.contract_lifecycle_schedule_id,
            'renewal_type', cls.renewal_type,
            'schedule_end', cls.schedule_end,
            'termination_notice_detail_id', cls.termination_notice_detail_id,
            'renewal_offset_detail_id', cls.renewal_offset_detail_id,
            'collection_period_detail_id', cls.collection_period_detail_id
        ) AS lifecycle_schedule,
        JSON_OBJECT(
            'contract_flowthrough_id', cf.contract_flowthrough_id,
            'flowthrough_rate', cf.flowthrough_rate,
            'flowthrough_status', cf.flowthrough_status,
            'has_automatic_shutoff', cf.has_automatic_shutoff,
            'recoupment_cap', cf.recoupment_cap,
            'previous_flowthrough_status', cf.previous_flowthrough_status
        ) AS flowthrough,
        JSON_ARRAYAGG(JSON_OBJECT(
            'contract_advance_id', ca.contract_advance_id,
            'advance_description', ca.advance_description,
            'amount', ca.amount,
            'vat_amount', ca.vat_amount,
            'withholding_tax_amount', ca.withholding_tax_amount,
            'amount_after_withholding_and_vat', ca.amount_after_withholding_and_vat,
            'us_source_income_rate', ca.us_source_income_rate,
            'ca.currency_code', ca.currency_code,
            'milestone', ca.milestone,
            'milestone_description', ca.milestone_description,
            'milestone_date', ca.milestone_date,
            'advance_status', ca.advance_status,
            'note', ca.note,
            'reference_payment_type_id', ca.reference_payment_type_id,
            'reference_advance_payment_method_id', ca.reference_advance_payment_method_id
        )) AS advances,
        JSON_ARRAYAGG(JSON_OBJECT(
            'contract_term_id', ct.contract_term_id,
            'contract_term_name', ct.contract_term_name,
            'term_type', ct.term_type,
            'attachments', ct.attachments,
            'attachments_relations', ct.attachments_relations,
            'is_base_term', ct.is_base_term
        )) AS terms,
        JSON_ARRAYAGG(JSON_OBJECT(
            'contract_term_condition_id', ctc.contract_term_condition_id,
            'contract_term_id', ctc.contract_term_id,
            'contract_term_condition_name', ctc.contract_term_condition_name,
            'conditions', ctc.conditions,
            'term_rate', ctc.term_rate,
            'commission', ctc.commission,
            'priority', ctc.priority
        )) AS term_conditions,
        JSON_ARRAYAGG(JSON_OBJECT(
            'contract_mechanical_deduction_id', cmd.contract_mechanical_deduction_id,
            'territory', cmd.territory,
            'mechanical_type', cmd.mechanical_type,
            'admin_type', cmd.admin_type,
            'admin_fee', cmd.admin_fee
        )) AS mechanical_deductions,
        JSON_OBJECT(
            'contract_reserve_id', cr.contract_reserve_id,
            'reserve_rate', cr.reserve_rate,
            'reserve_release_offset_in_months', cr.reserve_release_offset_in_months,
            'installments_in_months', cr.installments_in_months
        ) AS reserve,
        JSON_ARRAYAGG(JSON_OBJECT(
            'contract_exclusion_id', ce.contract_exclusion_id,
            'exclusions', ce.exclusions
        )) AS exclusions,
        JSON_ARRAYAGG(JSON_OBJECT(
            'contract_party_id', cp.contract_party_id,
            'target_type', cp.target_type,
            'target_id', cp.target_id
        )) AS parties,
        JSON_OBJECT(
            'run_controller_id', rc.run_controller_id,
            'run_controller_name', rc.run_controller_name
        ) AS run_controller
    FROM contract c
    -- Account
    LEFT JOIN account_contract ac ON ac.contract_id = c.contract_id
    LEFT JOIN account a ON a.account_id = ac.account_id
    -- Lifecycle
    LEFT JOIN contract_lifecycle cl ON cl.contract_id = c.contract_id
    LEFT JOIN contract_lifecycle_schedule cls ON cls.contract_id = c.contract_id
    -- Flowthrough
    LEFT JOIN contract_flowthrough cf ON cf.contract_id = c.contract_id
    -- Advances
    LEFT JOIN contract_advance ca ON ca.contract_id = c.contract_id
    -- Terms
    LEFT JOIN contract_term ct ON ct.contract_id = c.contract_id
    LEFT JOIN contract_term_condition ctc ON ctc.contract_term_id = ct.contract_term_id
    -- Mechanical deductions
    LEFT JOIN contract_mechanical_deduction cmd ON cmd.contract_id = c.contract_id
    -- Reserve
    LEFT JOIN contract_reserve cr ON cr.contract_id = c.contract_id
    -- Exclusion
    LEFT JOIN contract_exclusion ce ON ce.contract_id = c.contract_id
    -- Party
    LEFT JOIN contract_party cp ON cp.contract_id = c.contract_id
    -- Run controller
    LEFT JOIN run_controller_contract rcc ON rcc.contract_id = c.contract_id
    LEFT JOIN run_controller rc ON rc.run_controller_id = rcc.run_controller_contract_id
    WHERE c.contract_id = :contract_id
"""  # noqa: E501