"""Vendor Document Queries."""

VENDOR_DOCUMENT_SQL = """
SELECT
    v.vendor_id AS label_id,
    v.company AS label_name,
    v.owner AS label_owner,
    v.status AS label_status,
    v.label_identifier AS label_type,
    c.name AS label_country,
    v.is_owned,
    CONCAT(o.f_name, ' ', o.l_name) AS client_manager,
    o.email AS assigned_to_email,
    vc.contact_id AS label_contact_id,
    vc.login AS label_contact_login,
    CONCAT(con.contact_first_name, ' ', con.contact_last_name)
        AS label_contact_name
FROM
    vendor v
LEFT JOIN country c
    ON v.country = c.id
LEFT JOIN orchadmin_users o
    ON o.id = v.assigned_to
LEFT JOIN vend_contact vc
    ON
        v.vendor_id = vc.vendor_id
        AND vc.master = 'Y'
        AND
        (
            vc.subaccount_id IS NULL
            OR vc.subaccount_id = ''
            OR vc.subaccount_id = 0
        )
LEFT JOIN contact con
    ON con.contact_id = vc.contact_id
WHERE v.vendor_id=:vendor_id;
"""

VENDOR_DOCUMENT_WITH_TENANT_UUIDS_SQL = """
SELECT
    v.vendor_id AS label_id,
    v.company AS label_name,
    v.owner AS label_owner,
    v.status AS label_status,
    v.label_identifier AS label_type,
    c.name AS label_country,
    v.is_owned,
    v.vendor_uuid AS vendorUUID,
    cb.uuid AS companyBrandUUID,
    pc.uuid AS parentCompanyUUID,
    CONCAT(o.f_name, ' ', o.l_name) AS client_manager,
    o.email AS assigned_to_email,
    vc.contact_id AS label_contact_id,
    vc.login AS label_contact_login,
    CONCAT(con.contact_first_name, ' ', con.contact_last_name)
        AS label_contact_name
FROM
    vendor v
INNER JOIN company_brand cb
    ON cb.id = v.company_brand_id
INNER JOIN parent_company pc
    ON pc.id = cb.parent_company_id
LEFT JOIN country c
    ON v.country = c.id
LEFT JOIN orchadmin_users o
    ON o.id = v.assigned_to
LEFT JOIN vend_contact vc
    ON
        v.vendor_id = vc.vendor_id
        AND vc.master = 'Y'
        AND
        (
            vc.subaccount_id IS NULL
            OR vc.subaccount_id = ''
            OR vc.subaccount_id = 0
        )
LEFT JOIN contact con
    ON con.contact_id = vc.contact_id
WHERE v.vendor_id=:vendor_id;
"""