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 (
"melody.bradley@cherokee1.org","messelman@eaaofmichigan.org","jeanette.nino@mcallenisd.net","terica@educateplainfield.org","scerbo.j@deptford.k12.nj.us","ivy.johnson@acboe.net","laura.worshim@hsv-k12.org","maromine@interact.ccsd.net","dennispaula4@yahoo.com","mmiller@avsd.org","christian.sawyer@mnps.org","magarci2@garlandisd.net","slscott@lawtonps.org","jnerby@nisdtx.org","jamie.allbach@asheville.k12.nc.us","kirklac@pitt.k12.nc.us","sneuman@topeka.k12.ks.us","lewispm@scsk12.org","myra.reyes@mcallenisd.net","lsphillips@greenville.k12.sc.us","kbates@k12.wv.us","tracy.moruss@bhas.org","mooremt@rss.k12.nc.us","klybarger@lcisd.org","kcurtis@lubbockisd.org","kperkins@lisd.org","judy.murray@comalisd.org","michelle@lmisd.net","kromar@lmisd.net","rborow@houstonisd.org","rowelde@tulsaschools.org","lwaldron@manchestertwp.org","joellen.fisk@dilleyisd.net","melissa.foreman@paisd.org","eespolt@ponderisd.net","nezzie.moffett@killeenisd.org","gnetland@redlake.k12.mn.us","eherr@kcsd.us","ayanna.glover@ocsd5.net","mizellk@bcsdschools.net","cpaul@summit-academy.com","uanjum@universalpsa.org","jehawkins@burke.k12.nc.us","bgallagher@westamptonschools.org","vrichard@pgcps.org","lois.davies@ocps.net","krosenberg@atlanta.k12.ga.us","hwalker@asdms.us","deadeturk@kearneycats.com","pepper.moon@haralson.k12.ga.us","prior@fultonschools.org","joy.byne@henry.k12.ga.us","mhalwachs@highmountschool.com","wbjohnson@rcps.info","gephartk@sps186.org","brenda.speir@sccpss.com","dthomas@lindencity.org","cnevels@gcs.k12.in.us","monica.almeida@stlucieschools.org","awimberly@rcps.info","margaret.stafford@jppss.k12.la.us","garagusp@osceola.k12.fl.us","wendy.bray@ucf.edu","biancaj@pgcps.org","jane.comeaux@jppss.k12.la.us","arivera@duesd.org","lherron@midfield.k12.al.us","reed.lura@lee.k12.al.us","dawn.rondholz@sbcusd.com","roshal.anglin@mps.k12.al.us","vicki.green@redclay.k12.de.us","virginia_h_pribbenow@fc.dekalb.k12.ga.us","twallace@lafourche.k12.la.us")
);

## 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`;