# Introduction
A Data Subject Access Request (DSAR) is a request made by a fan to inquire about what personal data we hold on them.
Fans contact Legal, Legal contacts CRM, and CRM compiles a report by pulling data from multiple sources.

# Goal
Add new Streamlit app page so the CRM team can:

1. Search for a fan by email address
1.1 Search must support case-insensitive email lookup and partial name matching
2. View all relevant data organized by the 3 report sections in a single interface
3. Export the full report in the Excel format
3.1 CRM user can export the full report as an Excel workbook matching the current format
(3-tab structure, named `DSAR – [fan email]`) - Export should only include non-blank PII fields

# Report sections
Tab 1 — Export History
Data sources to consolidate: Salesforce exports, Hightouch and Fansifter.
These must be unified into a single view to ensure complete results.

Tab 2 — PII (Fan Profile)
Only fields with values should be shown — blank fields should be excluded from the output.
Required fields: First Name, Last Name, Full Name, Mobile Phone, Address 1, Address 2, City, State ,Country / Region,
Postal Code, Birthday, Birthdate and Age.
If the fan is deleted: display the deletion date in this section.

Tab 3 — Fan Active Subscriptions
Two required fields: Created Date and Artist name

# Data sources(all from Snowflake's Delphi account)
1. Fan profile- preference_center.prod.fan_profile.
1.1 Fan deletion data- preference_center.prod.deleted_profile. Use query like this:
    select email, deleted_at from preference_center.prod.deleted_profile where email='<fan_email>'
2. Fan active subscriptions
2.1 From Fansifter preference_center.prod.fan_subscription(is_active=True, created_at for start date) combined with
preference_center.prod.fan_mailing_list to get name.
3. Fan exports
3.1 Fansifter exports can be achieved using query like this:
    SELECT distinct ex.reason, ex.snapshot_id, id.name as exporter_name, pro.first_name, pro.email, ex.justification,
    max(ex.created_at) as last_export_date from
    fansifter_pg_reporting.prod_ows_dmp_public.audience_export ex
    inner join fansifter_app_reporting.prod.audience_fan snap on ex.snapshot_id=snap.snapshot_id
    inner join preference_center.prod.fan_profile pro on snap.fan_id=sha2(lower(pro.email), 256)
    left join FACTS.PROD.IDENTITY on ex.created_by=id.id
    where ex.status='COMPLETED' and not ex._fivetran_deleted
    and not pro.deleted
    and snap.fan_id=sha2(lower('myemail@gmail.com'),256)
    group by all;
3.2 Salesforce exports can be achieved using this query:
    SELECT
        eh.created_date                  AS export_date,
        e.business_justification_c       AS business_reason,
        e.recipient_email_c,
        e.recipient_name_c,
        e.export_type_c,
        e.exported_by_c,
        eh.first_name_c,
        eh.last_name_c,
        eh.email_c
    FROM delphi_crm_data.raw_salesforce_sales_cloud.export_c e
    INNER JOIN delphi_crm_data.raw_salesforce_sales_cloud.export_history_c eh
        ON eh.export_id_c = e.id
    WHERE lower(eh.email_c)='<fan_email>'
3.3 Hightouch exports can be achieved using this query:
SELECT
    sr.model_name,
    sr.destination,
    sr.started_at        AS export_date,
    cl.op_type,
    cl.fields
FROM delphi_crm_data.hightouch_audit.sync_changelog cl
INNER JOIN delphi_crm_data.hightouch_audit.sync_runs sr
    ON sr.sync_id = cl.sync_id
    AND sr.sync_run_id = cl.sync_run_id
WHERE lower(cl.fields:EMAIL_C::string) = '<fan_email>'
and cl.status='succeeded'
ORDER BY sr.started_at DESC;
3.4 Only following columns are required for export: export date, business reason(for Hightouch data
 if value from destination is sfmc then here should be "email audience" otherwise "retargeting"),
 recepient name and fan email.