-- net new connector report
select companies.name, companies.email, connectors.name, company_connectors.created_at  from company_connectors
join companies on company_connectors.company_id = companies.id
join connectors on connectors.id = company_connectors.connector_id
join summaries on summaries.company_id=company_connectors.company_id
where
  company_connectors.created_at > CURRENT_DATE - INTERVAL '8 days'
  and company_connectors.synced = TRUE
  and email  NOT LIKE '%@test.%' and email NOT LIKE '%avalara%'
GROUP BY companies.name, companies.email, connectors.name, company_connectors.created_at;


-- connector usage report
-- ?? TBD - reduce to subscriptions purchased in period??
select usage.company_name, usage.connector_name,
    count(subscriptions.id) as "subscriptions",
    sum(subscriptions.total_price) as "subscription total"
FROM   dblink('dbname=trustfilestorage user=deploy password=4BFkh59jDd9YCSu',
                        'SELECT  account_id, company_name, connector_name, created_at from company_daily_usage
                        where created_at > CURRENT_DATE - INTERVAL ''8 days''
                          and usage = ''CONNECTOR_ACTIVE''
                  ')
            AS   usage(account_id INT, company_name TEXT, connector_name TEXT, usage_date TIMESTAMP)
JOIN merchants on merchants.account_id = usage.account_id
LEFT OUTER JOIN subscriptions on subscriptions.account_id = usage.account_id
where  email  NOT LIKE '%test.%' and email NOT LIKE '%avalara%'
  and (subscriptions.expired_at ISNULL OR subscriptions.expired_at > now())
  and (subscriptions.revoked_at ISNULL and subscriptions.cancelled_at ISNULL )
  --and (subscriptions.created_at > CURRENT_DATE - INTERVAL '7 days' or subscriptions.id ISNULL )

GROUP BY usage.company_name, usage.connector_name
;