-- who purchased from the paywall
select companies.name, companies.email, date_trunc('Month', companies."createdAt") as "signedup_at",
  summaries.filed_return_count, summaries.gross_tax_collected, summaries.gross_sales,
  summaries.file_count, summaries.connector_count, summaries.item_count, summaries.pastdue_return_count,
  summaries.bank_accounts
from companies
join summaries ON summaries.company_id =  companies.id
where
  companies.account_id in (
    SELECT account_licenses.account_id
    FROM account_licenses
    WHERE account_licenses.created_at > '05/01/2016'
          and account_licenses.trial = false
          and (account_licenses.license_id = 47 or account_licenses.license_id = 1)
          --AND account_licenses.trial = FALSE
  );


-- users who didn't buy - need all sessions
select companies.name, companies.email, date_trunc('Month', companies."createdAt") as "signedup_at",
  summaries.filed_return_count, summaries.gross_tax_collected, summaries.gross_sales,
  summaries.file_count, summaries.connector_count, summaries.item_count, summaries.pastdue_return_count,
  summaries.bank_accounts
from companies
join summaries ON summaries.company_id =  companies.id
join users on users.account_id = companies.account_id
where
  companies.email NOT LIKE '%@test.%' AND companies.email NOT LIKE '%avalara%'
  and users.account_id NOT IN -- valid subscriptions
  (
    SELECT DISTINCT account_licenses.account_id
    FROM account_licenses
    WHERE
      account_licenses.revoke = FALSE
      AND
      (account_licenses.expiration_date ISNULL or account_licenses.expiration_date > now() )
  )
  and companies.id in (  -- all recent sessions
    select STORAGE.target_company_id FROM   dblink('dbname=trustfilestorage user=deploy password=4BFkh59jDd9YCSu', 'SELECT target_company_id
                        FROM audit_logs
                        where
                        created_at > ''05/01/2016''
                        and target_company_id = source_company_id')
    AS STORAGE ( target_company_id INT)
    JOIN merchants ON merchants.id = STORAGE.target_company_id
    WHERE merchants.email NOT LIKE '%@test.%' AND merchants.email NOT LIKE '%avalara%'
  );



-- paywall funnel - need better "who viewed the paywall" numbers

select "date",
        sum("sessions") as "viewed paywall",
        sum("subscribe") as "clicked subscribe",
        sum("purchase") as "purchased"

from
  (
    (SELECT
       date_trunc('Day', storage.created_at) AS "date",
       count(storage.target_company_id) AS "sessions",
       0                                         AS "subscribe",
       0                                         AS "purchase"
     FROM   dblink('dbname=trustfilestorage user=deploy password=4BFkh59jDd9YCSu','SELECT  created_at, target_company_id
                        FROM audit_logs
                        where
                        created_at > now() - INTERVAL ''7 days''
                        and target_company_id = source_company_id')
            AS     storage(created_at TIMESTAMP, target_company_id INT)
       JOIN merchants on merchants.id = storage.target_company_id
        where merchants.email NOT LIKE '%@test.%' AND merchants.email NOT LIKE '%avalara%'
     GROUP BY 1
     ORDER BY 1)

    UNION

    (SELECT
       date_trunc('Day', subscriptions.created_at) AS "date",
       0                                      AS "sessions",
       count(subscriptions.id)               AS "subscribe",
       0                                         AS "purchase"
     FROM subscriptions
       JOIN companies ON companies.account_id = subscriptions.account_id
     WHERE
       companies.email NOT LIKE '%@test.%' AND companies.email NOT LIKE '%avalara%'
       AND subscriptions.created_at > now() - INTERVAL '7 days'
     GROUP BY 1
     ORDER BY 1)

     UNION

    (SELECT
       date_trunc('Day', subscriptions.created_at) AS "date",
       0                                      AS "sessions",
       0                                      AS "subscribe",
       count(subscriptions.id)               AS "purchase"
     FROM subscriptions
       JOIN companies ON companies.account_id = subscriptions.account_id
     WHERE
       companies.email NOT LIKE '%@test.%' AND companies.email NOT LIKE '%avalara%'
       AND subscriptions.created_at > now() - INTERVAL '7 days'
     GROUP BY 1
     ORDER BY 1
      )

 ) a
GROUP BY "date"
ORDER BY "date";



-- users that logged but didn't buy
SELECT
       date_trunc('Day', storage.created_at) as "Day",
       merchants.name
     FROM   dblink('dbname=trustfilestorage user=deploy password=4BFkh59jDd9YCSu','SELECT  created_at, target_company_id
                        FROM audit_logs
                        where
                        created_at > now() - INTERVAL ''7 days''
                        and target_company_id = source_company_id')
            AS     storage(created_at TIMESTAMP, target_company_id INT)
       JOIN merchants on merchants.id = storage.target_company_id
        where merchants.email NOT LIKE '%@test.%' AND merchants.email NOT LIKE '%avalara%'
GROUP BY storage.created_at, merchants.name


select * from subscriptions where created_at > '05/01/2016'

select account_id from companies where name = '05-02@test.co'; --8323
select * from account_licenses where account_id = 8323;


select account_id from companies where name = 'First Botany';
select * from account_licenses where account_id = 2268;
select * from subscriptions where account_id = 2268;


select id, account_id from companies where name = '05-02@test.co';
select * from account_licenses where account_id = 8323;
select * from subscriptions where account_id = 8323;
select * from summaries where company_id = 9198;


-- people that viewed the paywall.  unlicensed access
SELECT
       date_trunc('Day', storage.created_at) AS "date",
       count(storage.target_company_id) AS "sessions",
       0                                         AS "subscribe",
       0                                         AS "purchase"
     FROM   dblink('dbname=trustfilestorage user=deploy password=4BFkh59jDd9YCSu','SELECT  created_at, target_company_id
                        FROM audit_logs
                        where
                        created_at > now() - INTERVAL ''7 days''
                        and target_company_id = source_company_id')
            AS     storage(created_at TIMESTAMP, target_company_id INT)
       JOIN merchants on merchants.id = storage.target_company_id

        where merchants.email NOT LIKE '%@test.%' AND merchants.email NOT LIKE '%avalara%'
     GROUP BY 1
     ORDER BY 1


SELECT
       date_trunc('Day', storage.created_at) AS "date",
       merchants.id AS "id",
       account_licenses.id,
       account_licenses.trial,
       account_licenses.expiration_date,
       account_licenses.license_id

     FROM   dblink('dbname=trustfilestorage user=deploy password=4BFkh59jDd9YCSu','SELECT  created_at, target_company_id
                        FROM audit_logs
                        where
                        created_at > now() - INTERVAL ''7 days''
                        and target_company_id = source_company_id')
            AS     storage(created_at TIMESTAMP, target_company_id INT)
       JOIN merchants on merchants.id = storage.target_company_id
       LEFT OUTER JOIN account_licenses on account_licenses.account_id = merchants.account_id
        where merchants.email NOT LIKE '%@test.%' AND merchants.email NOT LIKE '%avalara%'


select * from licenses;


select id, account_id from companies where name = 'foru International Corporation';  --6879	7070
select * from summaries where company_id = 6879;
select * from subscriptions where account_id = 2268;