"""BVCS SQL Queries."""

POPULATE_BVCS_SQL = """
REPLACE INTO `booked_vendor_contract_snapshot`
(
    `period_id`,
    `insert_date`,
    `vendor_contract_id`,
    `vendor_id`,
    `cont_start`,
    `cont_end`,
    `cont_version`,
    `exclusive`,
    `orchrep_name`,
    `carve_out`,
    `qualification_level`,
    `territory_carve_out`,
    `dms_carve_out`,
    `dms_master_carve_out`,
    `encoding_fees_cap_back_ctlg`,
    `encoding_fees_cap_new_release`,
    `digital_split`,
    `physical_split`,
    `contract_type`,
    `release_term`,
    `advance_payment`,
    `advance_recoupable_percentage`,
    `dig_distribution_type`,
    `possible_track_restrictions`,
    `oms_type`,
    `oms_fee_percentage`,
    `negotiated_changes`,
    `negotiated_change_comments`,
    `contract_complete`,
    `signature_date`,
    `marketing_restrictions`,
    `orchard_assignment_right_restriction`,
    `currency_id`,
    `third_party_responsibility`,
    `third_party_responsibility_detail`,
    `possible_track_restriction_detail`,
    `is_amendment`,
    `extend_until_recouped`,
    `sync_admin_territory`,
    `sync_admin_commission`,
    `sync_admin_type_of_deal`,
    `royalty_collection_territory`,
    `royalty_collection_commission`,
    `publishing_admin_commission`,
    `publishing_admin_territory`,
    `publishing_admin_limit_grant_of_rights`,
    `publishing_admin_misc_provisions`,
    `parent_vendor_contract_id`,
    `vendor_type`,
    `vendor_proposed_term_id`,
    `sync_admin_response_time`,
    `ringtone_publishing_type`,
    `physical_track_publishing_type`,
    `unlimited_roll`,
    `rollover_length_in_months`,
    `can_terminate`,
    `youtube_composition_clause`,
    `sx_royalty_collection_commission`,
    `topspin_rate`,
    `topspin_rate_territory`,
    `notice_required_in_days`,
    `term_continues_until_recouped`,
    `brand_split`,
    `other_rights_option`,
    `other_rights_text`,
    `special_product_split`,
    `special_product_carve_out`,
    `payment_interval`,
    `pay_after`,
    `show_credit_card`,
    `opt_out`,
    `apply_fx_spread`,
    `reserve_rate`,
    `number_of_months_before_payout`,
    `number_of_installments`,
    `contract_terms`,
    `orchard_compilation_agreement`,
    `orchard_compilation_split`,
    `orchard_compilation_authorization_required`
)
SELECT
{:d} AS period_id,
NOW() AS insert_date,
`vendor_contracts`.*
FROM `vendor` v
LEFT JOIN
(
    SELECT
    vcc.`id`,
    vcc.`vendor_id`,
    vcc.`cont_start`,
    vcc.`cont_end`,
    vcc.`cont_version`,
    vcc.`exclusive`,
    vcc.`orchrep_name`,
    vcc.`carve_out`,
    vcc.`qualification_level`,
    vcc.`territory_carve_out`,
    vcc.`dms_carve_out`,
    vcc.`dms_master_carve_out`,
    vcc.`encoding_fees_cap_back_ctlg`,
    vcc.`encoding_fees_cap_new_release`,
    vcc.`digital_split`,
    vcc.`physical_split`,
    vcc.`contract_type`,
    vcc.`release_term`,
    vcc.`advance_payment`,
    vcc.`advance_recoupable_percentage`,
    vcc.`dig_distribution_type`,
    vcc.`possible_track_restrictions`,
    vcc.`oms_type`,
    vcc.`oms_fee_percentage`,
    vcc.`negotiated_changes`,
    vcc.`negotiated_change_comments`,
    vcc.`contract_complete`,
    vcc.`signature_date`,
    vcc.`marketing_restrictions`,
    vcc.`orchard_assignment_right_restriction`,
    vcc.`currency_id`,
    vcc.`third_party_responsibility`,
    vcc.`third_party_responsibility_detail`,
    vcc.`possible_track_restriction_detail`,
    vcc.`is_amendment`,
    vcc.`extend_until_recouped`,
    vcc.`sync_admin_territory`,
    vcc.`sync_admin_commission`,
    vcc.`sync_admin_type_of_deal`,
    vcc.`royalty_collection_territory`,
    vcc.`royalty_collection_commission`,
    vcc.`publishing_admin_commission`,
    vcc.`publishing_admin_territory`,
    vcc.`publishing_admin_limit_grant_of_rights`,
    vcc.`publishing_admin_misc_provisions`,
    vcc.`parent_vendor_contract_id`,
    vcc.`vendor_type`,
    vcc.`vendor_proposed_term_id`,
    vcc.`sync_admin_response_time`,
    vcc.`ringtone_publishing_type`,
    vcc.`physical_track_publishing_type`,
    vcc.`unlimited_roll`,
    vcc.`rollover_length_in_months`,
    vcc.`can_terminate`,
    vcc.`youtube_composition_clause`,
    vcc.`sx_royalty_collection_commission`,
    vcc.`topspin_rate`,
    vcc.`topspin_rate_territory`,
    vcc.`notice_required_in_days`,
    vcc.`term_continues_until_recouped`,
    vcc.`brand_split`,
    vcc.`other_rights_option`,
    vcc.`other_rights_text`,
    vcc.`special_product_split`,
    vcc.`special_product_carve_out`,
    vcc.`payment_interval`,
    vcc.`pay_after`,
    vcc.`show_credit_card`,
    vcc.`opt_out`,
    vcc.`apply_fx_spread`,
    vcc.`reserve_rate`,
    vcc.`number_of_months_before_payout`,
    vcc.`number_of_installments`,
    vcc.`contract_terms`,
    vcomp.`orchard_compilation_agreement`,
    vcomp.`orchard_compilation_split`,
    vcomp.`orchard_compilation_authorization_required`
    FROM `vendor` v
    INNER JOIN `vendor_contract` vcc ON v.`vendor_id` = vcc.`vendor_id`
    LEFT JOIN `vendor_contract_compilation` vcomp
        ON vcomp.`vendor_contract_id` = vcc.`id`
    LEFT JOIN `vendor_contract` vcc2 ON vcc.`vendor_id` = vcc2.`vendor_id`
        AND vcc2.`cont_start` <= NOW()
        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) > NOW()
            )
            OR (
                (
                    vcc2.`contract_type` <> 'per_release_term'
                    OR vcc2.`contract_type` IS NULL
                )
                AND (
                    vcc2.`cont_end` >= NOW()
                    OR vcc2.`cont_end` IS NULL
                    OR vcc2.`cont_end` = '0000-00-00'
                )
            )
        )
        AND vcc.`cont_start` < vcc2.`cont_start`
    WHERE vcc2.`id` IS NULL
    AND vcc.`cont_start` <= NOW()
    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) > NOW()
        )
        OR (
            (
                vcc.`contract_type` <> 'per_release_term'
                OR vcc.`contract_type` IS NULL
            )
            AND (
                vcc.`cont_end` >= NOW()
                OR vcc.`cont_end` IS NULL
                OR vcc.`cont_end` = '0000-00-00'
            )
        )
    )
    AND v.`status` IN ('signed', 'deletion')
    GROUP BY v.`vendor_id`
) AS vendor_contracts ON vendor_contracts.`vendor_id` = v.`vendor_id`
WHERE v.`status` IN ( 'signed', 'deletion' )
AND vendor_contracts.`id` IS NOT NULL
"""

LABEL_NO_CONTRACT_SQL = """
SELECT
v.`vendor_id`
FROM `vendor` v
LEFT JOIN (
    SELECT vcc.*
    FROM `vendor` v
    INNER 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` <= NOW()
        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) > NOW()
            )
            OR (
                (
                    vcc2.`contract_type` <> 'per_release_term'
                    OR vcc2.`contract_type` IS NULL
                )
                AND (
                    vcc2.`cont_end` >= NOW()
                    OR vcc2.`cont_end` IS NULL
                    OR vcc2.`cont_end` = '0000-00-00'
                )
            )
        )
        AND vcc.`cont_start` < vcc2.`cont_start`
    WHERE 1
    AND vcc2.`id` IS NULL
    AND vcc.`cont_start` <= NOW()
    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) > NOW()
        )
        OR (
            (
                vcc.`contract_type` <> 'per_release_term'
                OR vcc.`contract_type` IS NULL
            )
            AND (
                vcc.`cont_end` >= NOW()
                OR vcc.`cont_end` IS NULL
                OR vcc.`cont_end` = '0000-00-00'
            )
        )
    )
    AND v.`status` IN ( 'signed', 'deletion' )
    GROUP BY v.`vendor_id`
) AS vendor_contracts ON vendor_contracts.`vendor_id` = v.`vendor_id`
WHERE v.`status` IN ( 'signed', 'deletion' )
AND vendor_contracts.`id` IS NULL
"""

REPLACE_FROM_PREVIOUS_PERIOD_SQL = """
REPLACE INTO `booked_vendor_contract_snapshot`
(
    `period_id`,
    `insert_date`,
    `vendor_contract_id`,
    `vendor_id`,
    `cont_start`,
    `cont_end`,
    `cont_version`,
    `exclusive`,
    `orchrep_name`,
    `carve_out`,
    `qualification_level`,
    `territory_carve_out`,
    `dms_carve_out`,
    `dms_master_carve_out`,
    `encoding_fees_cap_back_ctlg`,
    `encoding_fees_cap_new_release`,
    `digital_split`,
    `physical_split`,
    `contract_type`,
    `release_term`,
    `advance_payment`,
    `advance_recoupable_percentage`,
    `dig_distribution_type`,
    `possible_track_restrictions`,
    `oms_type`,
    `oms_fee_percentage`,
    `negotiated_changes`,
    `negotiated_change_comments`,
    `contract_complete`,
    `signature_date`,
    `marketing_restrictions`,
    `orchard_assignment_right_restriction`,
    `currency_id`,
    `third_party_responsibility`,
    `third_party_responsibility_detail`,
    `possible_track_restriction_detail`,
    `is_amendment`,
    `extend_until_recouped`,
    `sync_admin_territory`,
    `sync_admin_commission`,
    `sync_admin_type_of_deal`,
    `royalty_collection_territory`,
    `royalty_collection_commission`,
    `publishing_admin_commission`,
    `publishing_admin_territory`,
    `publishing_admin_limit_grant_of_rights`,
    `publishing_admin_misc_provisions`,
    `parent_vendor_contract_id`,
    `vendor_type`,
    `vendor_proposed_term_id`,
    `sync_admin_response_time`,
    `ringtone_publishing_type`,
    `unlimited_roll`,
    `rollover_length_in_months`,
    `can_terminate`,
    `youtube_composition_clause`,
    `sx_royalty_collection_commission`,
    `topspin_rate`,
    `topspin_rate_territory`,
    `notice_required_in_days`,
    `term_continues_until_recouped`,
    `brand_split`,
    `other_rights_option`,
    `other_rights_text`,
    `special_product_split`,
    `special_product_carve_out`,
    `payment_interval`,
    `pay_after`,
    `show_credit_card`,
    `opt_out`,
    `apply_fx_spread`,
    `orchard_compilation_agreement`,
    `orchard_compilation_split`,
    `orchard_compilation_authorization_required`,
    `physical_track_publishing_type`,
    `reserve_rate`,
    `number_of_months_before_payout`,
    `number_of_installments`,
    `contract_terms`
)
SELECT
{period_id} AS period_id,
NOW() AS insert_date,
`vendor_contract_id`,
`vendor_id`,
`cont_start`,
`cont_end`,
`cont_version`,
`exclusive`,
`orchrep_name`,
`carve_out`,
`qualification_level`,
`territory_carve_out`,
`dms_carve_out`,
`dms_master_carve_out`,
`encoding_fees_cap_back_ctlg`,
`encoding_fees_cap_new_release`,
`digital_split`,
`physical_split`,
`contract_type`,
`release_term`,
`advance_payment`,
`advance_recoupable_percentage`,
`dig_distribution_type`,
`possible_track_restrictions`,
`oms_type`,
`oms_fee_percentage`,
`negotiated_changes`,
`negotiated_change_comments`,
`contract_complete`,
`signature_date`,
`marketing_restrictions`,
`orchard_assignment_right_restriction`,
`currency_id`,
`third_party_responsibility`,
`third_party_responsibility_detail`,
`possible_track_restriction_detail`,
`is_amendment`,
`extend_until_recouped`,
`sync_admin_territory`,
`sync_admin_commission`,
`sync_admin_type_of_deal`,
`royalty_collection_territory`,
`royalty_collection_commission`,
`publishing_admin_commission`,
`publishing_admin_territory`,
`publishing_admin_limit_grant_of_rights`,
`publishing_admin_misc_provisions`,
`parent_vendor_contract_id`,
`vendor_type`,
`vendor_proposed_term_id`,
`sync_admin_response_time`,
`ringtone_publishing_type`,
`unlimited_roll`,
`rollover_length_in_months`,
`can_terminate`,
`youtube_composition_clause`,
`sx_royalty_collection_commission`,
`topspin_rate`,
`topspin_rate_territory`,
`notice_required_in_days`,
`term_continues_until_recouped`,
`brand_split`,
`other_rights_option`,
`other_rights_text`,
`special_product_split`,
`special_product_carve_out`,
`payment_interval`,
`pay_after`,
`show_credit_card`,
`opt_out`,
`apply_fx_spread`,
`orchard_compilation_agreement`,
`orchard_compilation_split`,
`orchard_compilation_authorization_required`,
`physical_track_publishing_type`,
`reserve_rate`,
`number_of_months_before_payout`,
`number_of_installments`,
`contract_terms`
FROM `booked_vendor_contract_snapshot`
WHERE `period_id` = ({last_period_id})
AND `vendor_contract_id` IS NOT NULL
AND `vendor_id` in ({label_ids})
"""

LABELS_NO_BVCS_SQL = """
SELECT
v.`vendor_id`
FROM `vendor` v
LEFT JOIN `booked_vendor_contract_snapshot` bvcs
ON bvcs.`vendor_id` = v.`vendor_id`
AND bvcs.`period_id` = {:d}
WHERE v.`status` IN ('signed', 'deletion')
AND bvcs.`contract_snapshot_id` IS NULL
"""

POPULATE_DEFAULT_CONTRACTS_SQL = """
REPLACE INTO `booked_vendor_contract_snapshot`
(`period_id`,`insert_date`,`vendor_id`)
VALUES {}
"""