"""Vendor Label Info Page Query."""

VENDOR_LABEL_INFO_SQL = """
SELECT
    v.vendor_id,
    v.name,
    v.company,
    v.priority,
    v.website,
    v.label_identifier,
    v.owner,
    v.is_owned,
    v.is_distributor,
    o.f_name AS oa_admin_first,
    o.l_name AS oa_admin_last,
    v.quarterback_label_manager,
    v.assigned_reviewer,
    v.date_created,
    v.last_update,
    v.support_contact_email,
    v.contact_email,
    v.sap_vendor_id,
    v.est_total_releases AS est_releases,
    v.est_total_tracks AS est_tracks,
    v.primary_genre,
    g.genre,
    c.name AS country,
    c.country_code,
    v.country AS country_id,
    v.status,
    r.region,
    CAST(v.projected_first_year_revenue AS FLOAT) as projected_first_year_revenue,
    v.transfer_pricing_country,
    v.show_release_builder AS allow_ALW_release_builder,
    v.migrated_to_abacus,
    v.label_summary,
    v.wel_email_sender,
    v.wel_email_send_date,
    v.vendor_uuid,
    cb.name AS company_brand
FROM
    vendor v
LEFT JOIN orchadmin_users o
    ON o.id = v.assigned_to
LEFT JOIN genre g
    ON v.primary_genre = g.genre_id
LEFT JOIN region r
    ON v.region = r.region_id
LEFT JOIN country c
    ON v.country = c.id
LEFT JOIN company_brand cb
    ON v.company_brand_id = cb.id
WHERE v.vendor_id=:vendor_id;
"""

VENDOR_CONTRACT_CURRENCY_SQL = """
SELECT
    c.symbol_html_entity_code AS currency_symbol,
    c.ISO_4217_code AS currency_code
FROM vendor_contract vcon
INNER JOIN currencies c ON c.id = vcon.currency_id
WHERE vcon.vendor_id=:vendor_id AND vcon.cont_start <= CURDATE() AND vcon.vendor_type = "vendor"
AND (vcon.cont_end >= CURDATE() OR vcon.cont_end = "0000-00-00" OR vcon.cont_end IS NULL )
GROUP BY vcon.id ORDER BY vcon.cont_start DESC, vcon.cont_end DESC LIMIT 1;
"""

PRODUCT_MANAGER_SQL = """
SELECT oa.id AS product_manager
FROM orchadmin_users oa
JOIN product_manager_mapping_vendor pm
    ON pm.product_manager_id = oa.id
WHERE pm.vendor_id=:vendor_id
"""

COMPANY_BRAND_ID_SQL = """
SELECT cb.id AS company_brand_id
FROM company_brand cb
WHERE cb.uuid=:company_brand_uuid
"""

COMPANY_BRAND_ID_BY_NAME_SQL = """
SELECT cb.id AS company_brand_id
FROM company_brand cb
WHERE cb.name=:company_brand_name
"""

DELETE_PRODUCT_MANAGER_MAPPING_SQL = """
DELETE FROM product_manager_mapping_vendor
WHERE vendor_id=:vendor_id
"""

UPDATE_SOUNDSCAN_CODE = """
UPDATE soundscan_codes sc
SET sc.soundscan_code=:sc_code
WHERE sc.country_id=:country_id
AND sc.subaccount_id is NULL
AND sc.vendor_id=:vendor_id;
"""

UPDATE_COMPANY_BRAND_ID = """
UPDATE vendor v
SET v.company_brand_id=:company_brand_id
WHERE v.vendor_id=:vendor_id;
"""