holy_moly.sql

set @now = now();
set @six_months_ago = date_sub(now(), interval 6 month);
set @three_months_ago = date_sub(now(), interval 3 month);

create temporary table if not exists `marketing_contact` (
  id int(11) unique,
  first_name varchar(100),
  email varchar(100),
  index `marketing_contact_id` (id)
) as (
  select distinct `user`.`id`, `user`.`first_name`, `user`.`email`
  from `flo_user` user
  left join flo_invitation inv on inv.invitee_id = user.id
  where `gets_nightly_emails`
  and not `is_student`
  and (`accepted` is not null or inv.id is null)
  and `role` != "student" and `role` != "parent"
  and `user`.`email` not in (
"[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]","[email protected]")
);

## SOL
create temporary table if not exists `shadow` (
  id int(11) unique,
  first_name varchar(100),
  email varchar(100),
  index `marketing_contact_id` (id)
);

# SOL quote requests
insert ignore into `shadow`
select distinct `marketing_contact`.`id`, `marketing_contact`.`first_name`, `marketing_contact`.`email` from `marketing_contact`
where `marketing_contact`.`id` in (
  select `acting_user_id`
  from `flo_quoterequest`
  where `first_created` > @six_months_ago
);

# SOL N-Teacher
insert ignore into `shadow`
select distinct `marketing_contact`.`id`, `marketing_contact`.`first_name`, `marketing_contact`.`email` from `marketing_contact`
inner join `flo_subscribership`
on `marketing_contact`.`id` = `flo_subscribership`.`user_id`
inner join `flo_subscription`
on `flo_subscribership`.`subscription_id` = `flo_subscription`.`id`
where `flo_subscription`.`id` in (
  select `id` from `flo_subscription`
  where (`flo_subscription`.`site_quantity` = 0 or `flo_subscription`.`site_quantity` is null)
  and exists (select * from `flo_popayment` where `flo_subscription`.`id` = `flo_popayment`.`subscription_id`)
  and `flo_subscription`.`end_date` < @now and `flo_subscription`.`end_date` > @six_months_ago
);

# SOL site
insert ignore into `shadow`
select distinct `marketing_contact`.`id`, `marketing_contact`.`first_name`, `marketing_contact`.`email` from `marketing_contact`
inner join `flo_subscribership`
on `marketing_contact`.`id` = `flo_subscribership`.`user_id`
inner join `flo_subscription`
on `flo_subscribership`.`subscription_id` = `flo_subscription`.`id`
where `flo_subscription`.`site_quantity` is not null and `flo_subscription`.`site_quantity` > 0
and `flo_subscription`.`end_date` > @six_months_ago;

# SOL recurly
insert ignore into `shadow`
select distinct `marketing_contact`.`id`, `marketing_contact`.`first_name`, `marketing_contact`.`email` from `marketing_contact`
inner join `flo_subscribership`
on `marketing_contact`.`id` = `flo_subscribership`.`user_id`
inner join `flo_subscription`
on `flo_subscribership`.`subscription_id` = `flo_subscription`.`id`
where `flo_subscription`.`recurly_token` is not null and `flo_subscription`.`recurly_token` != ""
and `flo_subscription`.`end_date` > @three_months_ago and `flo_subscription`.`end_date` < @now;

## G3
create temporary table if not exists `active_recurly` (
  id int(11) unique,
  first_name varchar(100),
  email varchar(100),
  index `marketing_contact_id` (id)
);

# G3 recurly
insert ignore into `active_recurly`
select distinct `marketing_contact`.`id`, `marketing_contact`.`first_name`, `marketing_contact`.`email` from `marketing_contact`
inner join `flo_subscribership`
on `marketing_contact`.`id` = `flo_subscribership`.`user_id`
inner join `flo_subscription`
on `flo_subscribership`.`subscription_id` = `flo_subscription`.`id`
where `flo_subscription`.`recurly_token` is not null and `flo_subscription`.`recurly_token` != ""
and (`flo_subscription`.`end_date` > @now or `flo_subscription`.`end_date` is null)
and `marketing_contact`.`id` not in (select `id` from `shadow`);

## G4
create temporary table if not exists `active_n_teacher` (
  id int(11) unique,
  first_name varchar(100),
  email varchar(100),
  index `marketing_contact_id` (id)
);

# G4 N-Teacher
insert ignore into `active_n_teacher`
select distinct `marketing_contact`.`id`, `marketing_contact`.`first_name`, `marketing_contact`.`email` from `marketing_contact`
inner join `flo_subscribership`
on `marketing_contact`.`id` = `flo_subscribership`.`user_id`
inner join `flo_subscription`
on `flo_subscribership`.`subscription_id` = `flo_subscription`.`id`
where `flo_subscription`.`id` in (
  select `id` from `flo_subscription`
  where (`flo_subscription`.`site_quantity` = 0 or `flo_subscription`.`site_quantity` is null)
  and exists (select * from `flo_popayment` where `flo_subscription`.`id` = `flo_popayment`.`subscription_id`)
  and `flo_subscription`.`end_date` > @now
)
and `marketing_contact`.`id` not in (select `id` from `shadow`)
and `marketing_contact`.`id` not in (select `id` from `active_recurly`);

## G1
create temporary table if not exists `individual_trial` (
  id int(11) unique,
  first_name varchar(100),
  email varchar(100),
  index `marketing_contact_id` (id)
);

# G1 1 trial teacher
insert ignore into `individual_trial`
select distinct `marketing_contact`.`id`, `marketing_contact`.`first_name`, `marketing_contact`.`email` from `marketing_contact`
inner join `flo_subscribership`
on `marketing_contact`.`id` = `flo_subscribership`.`user_id`
inner join `flo_subscription`
on `flo_subscribership`.`subscription_id` = `flo_subscription`.`id`
where `flo_subscription`.`id` in (
  select `id` from `flo_subscription`
  where `teacher_quantity` = 1
  and (`flo_subscription`.`recurly_token` is null or `flo_subscription`.`recurly_token` = "")
  and not exists (
    select * from `flo_popayment`
    where `flo_popayment`.`subscription_id` = `flo_subscription`.`id`
  )
)
and `flo_subscription`.`end_date` > @now
and `marketing_contact`.`id` not in (select `id` from `shadow`)
and `marketing_contact`.`id` not in (select `id` from `active_recurly`)
and `marketing_contact`.`id` not in (select `id` from `active_n_teacher`);

create temporary table if not exists `everyone_else` (
  id int(11) unique,
  first_name varchar(100),
  email varchar(100),
  index `marketing_contact_id` (id)
);

insert into `everyone_else`
select distinct `marketing_contact`.`id`, `marketing_contact`.`first_name`, `marketing_contact`.`email` from `marketing_contact`
where `marketing_contact`.`id` not in (select `id` from `shadow`)
and `marketing_contact`.`id` not in (select `id` from `active_recurly`)
and `marketing_contact`.`id` not in (select `id` from `active_n_teacher`)
and `marketing_contact`.`id` not in (select `id` from `individual_trial`);

select * from `marketing_contact`;
select * from `active_recurly`;
select * from `active_n_teacher`;
select * from `individual_trial`;
select * from `everyone_else`;