"""Subaccount Document Queries."""

SUBACCOUNT_DOCUMENT_SQL = """
SELECT
    s.subaccount_id AS label_id,
    s.subaccount_name AS label_name,
    s.subaccount_uuid,
    v.owner AS label_owner,
    v.status AS label_status,
    'Subaccount' 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,
    v.company AS parent_label,
    v.vendor_id AS parent_label_id,
    IF (s.date_deleted IS NULL, 1, 0) AS is_active
FROM
    subaccount s
JOIN vendor v
    ON v.vendor_id = s.vendor_id
LEFT JOIN country c
    ON c.id = s.country_id
LEFT JOIN orchadmin_users o
    ON o.id = v.assigned_to
WHERE s.subaccount_id=:subaccount_id;
"""


SUBACCOUNT_DOCUMENT_WITH_TENANT_UUIDS_SQL = """
SELECT
    s.subaccount_id AS label_id,
    s.subaccount_name AS label_name,
    s.subaccount_uuid,
    v.owner AS label_owner,
    v.status AS label_status,
    'Subaccount' 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,
    v.company AS parent_label,
    v.vendor_id AS parent_label_id,
    v.vendor_uuid AS vendorUUID,
    s.subaccount_uuid AS subaccountUUID,
    cb.uuid AS companyBrandUUID,
    pc.uuid AS parentCompanyUUID,
    IF (s.date_deleted IS NULL, 1, 0) AS is_active
FROM
    subaccount s
JOIN vendor v
    ON v.vendor_id = s.vendor_id
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 c.id = s.country_id
LEFT JOIN orchadmin_users o
    ON o.id = v.assigned_to
WHERE s.subaccount_id=:subaccount_id;
"""