-- month aggregate data
-- purchases in the month

-- net new connector report
select companies.name, companies.email, connectors.name as "connector", 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 > '05/01/2016'
  and company_connectors.deleted_at ISNULL
  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;


-- punt report


select usage.company_name, merchants.email,
  (case when usage.connector_name = 'xero'  then 1 else 0 end) AS "xero",
  (case when usage.connector_name = 'amazon'  then 1 else 0 end) AS "amazon",
  (case when usage.connector_name = 'shopify'  then 1 else 0 end) AS "shopify",
  (case when usage.connector_name = 'weebly'  then 1 else 0 end) AS "weebly",
  (case when usage.connector_name = 'freshbooks'  then 1 else 0 end) AS "freshbooks",
  (case when usage.connector_name = 'etsyv2'  then 1 else 0 end) AS "etsyv2",
  (case when usage.connector_name = 'qbo'  then 1 else 0 end) AS "qbo",
  (case when usage.connector_name = 'prestashop'  then 1 else 0 end) AS "prestashop",
  sum(subscriptions.total_price) as "purchased?"
  --(case when subscriptions.id NOTNULL  and subscriptions.total_price > 0 then true else false end) as "purchased"

FROM   dblink('dbname=trustfilestorage user=deploy password=4BFkh59jDd9YCSu',
                        'SELECT  account_id, company_name, connector_name, created_at from company_daily_usage
                        where created_at > ''05/01/2016''
                          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 = merchants.account_id
where  email  NOT LIKE '%test.%' and email NOT LIKE '%avalara%' and email NOT LIKE '%sush.io%'
GROUP BY usage.company_name, merchants.email, usage.connector_name, subscriptions.total_price, subscriptions.id
;



-- connector usage report



select usage.company_name, merchants.email,
  (case when usage.connector_name = 'xero'  then 1 else 0 end) AS "xero",
  (case when usage.connector_name = 'amazon'  then 1 else 0 end) AS "amazon",
  (case when usage.connector_name = 'shopify'  then 1 else 0 end) AS "shopify",
  (case when usage.connector_name = 'weebly'  then 1 else 0 end) AS "weebly",
  (case when usage.connector_name = 'freshbooks'  then 1 else 0 end) AS "freshbooks",
  (case when usage.connector_name = 'etsyv2'  then 1 else 0 end) AS "etsyv2",
  (case when usage.connector_name = 'qbo'  then 1 else 0 end) AS "qbo",
  (case when usage.connector_name = 'prestashop'  then 1 else 0 end) AS "prestashop",
 -- (case when subscriptions.created_at > '05/01/2016' then subscriptions.total_price else 0 end)

  --sum( subscriptions.total_price) as "purchases in the month"
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
INNER JOIN (select companies.account_id,
  sum  (case when (subscriptions.created_at > '05/01/2016'
         and (subscriptions.expired_at ISNULL OR subscriptions.expired_at > now())
         and (subscriptions.revoked_at ISNULL and subscriptions.cancelled_at ISNULL ))
    then subscriptions.total_price else 0  end) AS "$"
from companies
  LEFT OUTER JOIN subscriptions on subscriptions.account_id = companies.account_id
  GROUP BY companies.account_id) purchases
  on (purchases.account_id = usage.account_id)
where  email  NOT LIKE '%test.%' and email NOT LIKE '%avalara%' and email NOT LIKE '%sush.io%'

GROUP BY usage.company_name, merchants.email, usage.connector_name
;


-- recent purchases by account_id
select companies.account_id,
  sum  (case when (subscriptions.created_at > '05/01/2016'
         and (subscriptions.expired_at ISNULL OR subscriptions.expired_at > now())
         and (subscriptions.revoked_at ISNULL and subscriptions.cancelled_at ISNULL ))
    then subscriptions.total_price else 0  end) AS "subscription"
from companies
LEFT OUTER JOIN
  subscriptions on subscriptions.account_id = companies.account_id
--where
    -- (subscriptions.expired_at ISNULL OR subscriptions.expired_at > now())
    -- and (subscriptions.revoked_at ISNULL and subscriptions.cancelled_at ISNULL )
    -- and subscriptions.created_at > '05/01/2016'
  GROUP BY companies.account_id;


select id, account_id from companies where name = 'Maurice Cole Surfboards USA'; -- 9169	8294

select * from subscriptions where account_id = 8294



-- original connector usage report
select usage.company_name, merchants.email, usage.connector_name, sum( subscriptions.total_price) as "purchases in the month"
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 '8 days' or subscriptions.id ISNULL )

GROUP BY usage.company_name, usage.connector_name, merchants.email
;


select id, account_id from companies where name = 'Maurice Cole Surfboards USA'; -- 9169	8294

select * from subscriptions where account_id = 8294