"""Contract Queries."""


ACTIVE_LABEL_CONTRACTS_SQL = """
SELECT
v.`vendor_id` as label_id,
vcc.`id` as contract_id
FROM `vendor` v
JOIN `vendor_contract` vcc ON v.`vendor_id` = vcc.`vendor_id`
LEFT JOIN `vendor_contract` vcc2 ON vcc.`vendor_id` = vcc2.`vendor_id`
AND vcc2.`cont_start` <= CURDATE()
AND vcc.`cont_start` < vcc2.`cont_start`
AND (
    (
        vcc2.`contract_type` = 'per_release_term'
        AND (
            vcc2.`cont_start` = '0000-00-00'
            OR vcc2.`release_term` <= 0
            OR (vcc2.`cont_start`
                + interval vcc2.`release_term` month) > CURDATE()
        )
    ) OR (
        COALESCE(vcc2.`contract_type`, 'null') <> 'per_release_term'
        AND (
            vcc2.`cont_end` >= CURDATE()
            OR COALESCE(vcc2.`cont_end`, '0000-00-00') = '0000-00-00'
        )
    )
)
WHERE vcc2.`id` IS NULL
AND vcc.`cont_start` <= CURDATE()
AND v.`status` IN ('signed', 'deletion')
AND (
    (
        vcc.`contract_type` = 'per_release_term'
        AND (
            vcc.`cont_start` = '0000-00-00'
            OR vcc.`release_term` <= 0
            OR (vcc.`cont_start`
                + interval vcc.`release_term` month) > CURDATE()
        )
    )
    OR (
        COALESCE(vcc.`contract_type`, 'null') <> 'per_release_term'
        AND (
            vcc.`cont_end` >= CURDATE()
            OR COALESCE(vcc.`cont_end`, '0000-00-00') = '0000-00-00'
        )
    )
)
GROUP BY v.`vendor_id`
"""

LABEL_CONTRACT_SQL = """
SELECT
vc.`id`,
vc.`digital_split`,
IF(vc.`advance_recoupable_percentage` IS NULL,
    1, vc.`advance_recoupable_percentage`) as advance_recoupable_percentage,
vc.`oms_type`,
vc.`ringtone_publishing_type`,
vc.`physical_track_publishing_type`,
vc.`oms_fee_percentage`,
(
    SELECT
    count(*) as total
    FROM `vendor_contract`
    where `vendor_id` = vc.`vendor_id`
    AND `cont_start` < vc.`cont_start`
) as previous_contracts,
vc.`payment_interval`,
vc.`apply_fx_spread`,
vc.`currency_id`,
vc.`qualification_level`,
vc.`contract_type`,
(
    SELECT
    GROUP_CONCAT(
        CAST(
            CONCAT( `dms_split`, ':', `dms_customer_id`) AS CHAR
        ) SEPARATOR ','
    ) AS dms_split
    FROM `vendor_dms_contract`
    WHERE `contract_id` = vc.`id`
    AND `dms_split` IS NOT NULL
) AS dms_split,
(
    SELECT
    GROUP_CONCAT(
        CAST(
            CONCAT(`dms_master_split`, ':', `dms_master_id`) AS CHAR
        ) SEPARATOR ','
    ) AS dms_master_split
    FROM `vendor_dms_master_contract`
    WHERE `vendor_contract_id` = vc.`id`
    AND `dms_master_split` IS NOT NULL
) AS dms_master_split,
(
    SELECT
    GROUP_CONCAT(
        CAST(
            CONCAT(`territory_split`, ':', `country_id`) AS CHAR
        ) SEPARATOR ','
    ) AS territory_split
    FROM `vendor_territory_contract`
    WHERE `vendor_contract_id` = vc.`id`
    AND `territory_split` IS NOT NULL
) AS territory_split,
(
    SELECT
    GROUP_CONCAT(
        CAST(
            CONCAT(`territory_transaction_split`,
            ':',
            `territory_id`,
            '_',
            `transaction_type`) AS CHAR
        ) SEPARATOR ','
    ) AS transaction_territory_split
    FROM `vendor_tt_territory_contract`
    WHERE `vendor_contract_id` = vc.`id`
    AND `territory_transaction_split` IS NOT NULL
) AS transaction_territory_split,
(
    SELECT
    GROUP_CONCAT(
        CAST(
            CONCAT(`transaction_split`, ':', `transaction_type` )
            AS CHAR
        ) SEPARATOR ','
    ) as transaction_type_split
    FROM `vendor_transaction_type_contract`
    WHERE `vendor_contract_id` = vc.`id`
    AND `transaction_split` IS NOT NULL
) AS transaction_type_split
FROM `vendor_contract` vc
WHERE vc.`id` = {:d}
ORDER BY vc.`id`
"""