BEGIN;

USE aj_test_db_1;
/**
if the query fails in workbench, the second time it runs, it will say that the tables already exists ...
- ahhh .. mysql DDL statements cause implicit commit (data definition language: create, alter structures ... )
 */
DROP TABLE IF EXISTS user_group, user_group_xref, contacts, contact_group_xref,contact_group,
  email_template, email_campaign, email_campaign_contacts, email_campaign_history, email_campaign_status_types, email_campaign_contact_status_types,
  email_campaign_contacts_send_queue ;


CREATE TABLE user_group (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name TEXT not null,
  PRIMARY KEY (id)
);

CREATE TABLE user_group_xref (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL REFERENCES users(id),
  group_id INT UNSIGNED NOT NULL REFERENCES user_group(id),
  date_added DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

CREATE TABLE contacts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email TEXT not null,
  first_name TEXT,
  last_name TEXT,
  owner_id INT UNSIGNED NOT NULL REFERENCES users(id),
  PRIMARY KEY (id)
);

CREATE TABLE contact_field_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) not null,
  description TEXT,
  db_type TEXT,

  UNIQUE (name),
  PRIMARY KEY (id)
);

CREATE TABLE contact_fields (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) not null,
  type_id TEXT NOT NULL,
  description TEXT,
  is_editable BOOLEAN DEFAULT TRUE,
  is_visible BOOLEAN DEFAULT TRUE,
  is_immutable BOOLEAN DEFAULT FALSE,
  sort INT,

  UNIQUE (name),
  PRIMARY KEY (id)
);
INSERT INTO contact_field_types (name, db_type) VALUES ('text', 'text');
INSERT INTO contact_field_types (name, db_type) VALUES ('numeric', 'numeric');
INSERT INTO contact_field_types (name, db_type) VALUES ('image', 'text');

INSERT INTO contact_fields (name, type_id, description, is_immutable) VALUES ('email', (SELECT id FROM contact_field_types WHERE name = 'text'), 'Email Address', 1);
INSERT INTO contact_fields (name, type_id, description, is_immutable) VALUES ('first_name', (SELECT id FROM contact_field_types WHERE name = 'text'), 'First Name', 1);
INSERT INTO contact_fields (name, type_id, description, is_immutable) VALUES ('last_name', (SELECT id FROM contact_field_types WHERE name = 'text'), 'Last Name', 1);

CREATE TABLE contact_group (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name TEXT not null,
  description TEXT,
  owner_id INT UNSIGNED NOT NULL REFERENCES users(id),
  /**
  filter out these groups from UI etc
  - these are groups created for sending an email
    - if a group is selected to send to .. we'll create an internal group and copy the contacts to it..??
    - naming convention: internal_email_campaign_{campaign_id}_group_{group_id}
  is_email_campaign_group BOOLEAN DEFAULT 1,
  */
  PRIMARY KEY (id)
);

CREATE TABLE contact_group_xref (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  group_id INT UNSIGNED NOT NULL REFERENCES user_group(id),
  contact_id INT UNSIGNED NOT NULL REFERENCES contacts(id),
  PRIMARY KEY (id)
);


CREATE TABLE email_template (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name TEXT not null,
  created_by_id INT UNSIGNED NOT NULL REFERENCES users(id),
  html TEXT not null,
  thumbnail TEXT,
  date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  date_modified DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  modified_by_id INT UNSIGNED NOT NULL REFERENCES users(id),
  PRIMARY KEY (id)
);


CREATE TABLE email_template_edit_tags (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) not null,
  type TEXT not null,
  template_id INT UNSIGNED NOT NULL REFERENCES email_template(id),
  value TEXT,
  PRIMARY KEY (id),
  UNIQUE (name, template_id)
);

/*pending, sending, sent, canceled*/
CREATE TABLE email_campaign_status_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) not null UNIQUE, /*NOT using TEXT because I won't be able to index the column*/
  description TEXT,
  PRIMARY KEY (id)
);

INSERT INTO email_campaign_status_types (name, description) VALUES ('working', 'campaign is being worked on');
INSERT INTO email_campaign_status_types (name, description) VALUES ('scheduled', 'campaign is done and scheduled for sending');
INSERT INTO email_campaign_status_types (name, description) VALUES ('sending', 'campaign is being sent');
INSERT INTO email_campaign_status_types (name, description) VALUES ('sent', 'campaign has completed');
INSERT INTO email_campaign_status_types (name, description) VALUES ('paused', 'campaign was halted after it started sending');
INSERT INTO email_campaign_status_types (name, description) VALUES ('canceled', 'campaign was canceled');

CREATE TABLE email_campaign_contact_status_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) not null UNIQUE, /*NOT using TEXT because I won't be able to index the column*/
  description TEXT,
  PRIMARY KEY (id)
);

INSERT INTO email_campaign_contact_status_types (name, description) VALUES ('pending', 'not sent');
INSERT INTO email_campaign_contact_status_types (name, description) VALUES ('sent', 'sent');
INSERT INTO email_campaign_contact_status_types (name, description) VALUES ('canceled', 'canceled');
INSERT INTO email_campaign_contact_status_types (name, description) VALUES ('hard_bounce', 'hard_bounce');
INSERT INTO email_campaign_contact_status_types (name, description) VALUES ('soft_bounce', 'soft_bounce');


/*
allow "publishing" of this campaign so that others can use it
show alert(this will create a TEMPLATE from this campaign and allow others to use it)
*/
CREATE TABLE email_campaign (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  template_id INT UNSIGNED NOT NULL REFERENCES email_template(id),
  user_id INT UNSIGNED NOT NULL REFERENCES users(id),
  /*resending: how not to resend to a previous email when there are multiple levels of parents
  -- - don't want to copy the contact details forward - would use up too much memory ..
  */
  parent_campaign_id INT UNSIGNED REFERENCES email_campaign(id),
  marketing_type_id INT UNSIGNED REFERENCES marketing_types(id),
  name TEXT not null,
  subject TEXT not null,
  html TEXT not null,
  original_html TEXT not null,
  prepped_html TEXT not null, /*html updated with special attributes - for sending - e.g. <a tags updated with the uid attribute, so that they can be replaced later with the tracking url */
  send_date DATETIME,
  status_id INT UNSIGNED NOT NULL REFERENCES email_campaign_status_types(id),
  date_sent DATETIME,
  date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  date_modified DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  is_disabled BOOL NOT NULL DEFAULT FALSE,
  PRIMARY KEY (id)
);

CREATE TABLE email_campaign_edit_tags (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) not null ,
  type TEXT not null,
  campaign_id INT UNSIGNED NOT NULL REFERENCES email_template(id),
  value TEXT,
  PRIMARY KEY (id),
  UNIQUE (name, campaign_id)
);

/*todo - normalized: separate table for each contact type (email, id, group)*/
CREATE TABLE email_campaign_contacts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  campaign_id INT UNSIGNED NOT NULL REFERENCES email_campaign(id),
  contact_id INT UNSIGNED REFERENCES contacts(id),
  /*
  --could be a lot of email addresses ..?? -- create groups first ??
  --email address may not yet be in the system ... add "prompt" to "add to my contacts?"
   */
  contact_email TEXT REFERENCES contacts(email),

  /**
    See contact_group.is_email_campaign_group .. NOT USED
    - if a group is selected to send to .. we'll create an internal group and copy the contacts to it..??
    - naming convention: internal_email_campaign_{campaign_id}_group_{group_id}
   */
  contact_group_id INT UNSIGNED REFERENCES contact_group(id),

  PRIMARY KEY (id)
  /*
  -- my sql does not support the "check" .. need to use a trigger instead
  -- CHECK (NOT(c IS NULL AND d IS NULL))
  */
);
DELIMITER ;;
CREATE TRIGGER has_campaign_contact BEFORE INSERT ON email_campaign_contacts 
	FOR EACH ROW BEGIN
		IF (NEW.contact_id IS NULL AND NEW.contact_email IS NULL AND NEW.contact_group_id IS NULL) THEN
		  SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = 'Campaign contact insert must have one of contact_id, contact_email, or contact_group_id';
		END IF;
	END ;;
DELIMITER ;

CREATE TABLE email_campaign_contacts_ignored (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  campaign_id INT UNSIGNED NOT NULL REFERENCES email_campaign(id),
  contact_id INT UNSIGNED NOT NULL REFERENCES contacts(id),
  /*
  --could be a lot of email addresses ..?? -- create groups first ??
  --email address may not yet be in the system ... add "prompt" to "add to my contacts?"
   */
  contact_email TEXT REFERENCES contacts(email),
  contact_group_id INT UNSIGNED NOT NULL REFERENCES contact_group(id),

  PRIMARY KEY (id)/*,
  CONSTRAINT contact_group_id_fkey FOREIGN KEY (contact_group_id) REFERENCES contact_group (id),
  CONSTRAINT contact_id_fkey FOREIGN KEY (contact_id) REFERENCES contacts (id)
  */
);

CREATE TABLE email_campaign_contacts_send_queue (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  campaign_id INT UNSIGNED NOT NULL REFERENCES email_campaign(id),
  contact_id INT UNSIGNED NOT NULL REFERENCES contacts(id),
  send_status_id INT UNSIGNED NOT NULL REFERENCES email_campaign_contact_status_types(id),

  /*all contact table columns*/
  email TEXT,
  first_name TEXT,
  last_name TEXT,

  /**
  using this value in tracking links etc, instead of the contact id
  - also round trip the campaign_id in the tracking link - may make it faster to lookup the index of the INTEGER campaign id
  - hmmm can't use a function as a default value ..?? in MYSQL .. ??
  - can do this with a trigger
  - e.g.
    - CREATE TRIGGER before_insert_app_users
  BEFORE INSERT ON app_users
  FOR EACH ROW
  SET new.api_key = uuid();

  - I'll add uuid from PHP instead ...
   */
  /*send_uid uuid DEFAULT UUID(),*/
  send_uid char(36),
  PRIMARY KEY (id),
  UNIQUE (campaign_id, contact_id)
);

/*
CREATE TABLE email_campaign_history (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  campaign_id INT UNSIGNED NOT NULL REFERENCES email_campaign(id),
  /*
  --array of changed_data (first one is CREATED), CANCELED, MODIFIED,
  -- TYPE JSON is fine in mysql but failing in my version of doctrine
   */
  meta TEXT,
  date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by_id INT UNSIGNED NOT NULL REFERENCES users(id),
  PRIMARY KEY (id)
);
*/

/**
 - VIEW, CLICK, (SEND, SOFT_BOUNCE, HARD_BOUNCE ... hmmm I also have these as queue contact status types ... so not adding here, for now)
 */
CREATE TABLE email_campaign_tracking_types(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name TEXT NOT NULL,
  description TEXT NOT NULL,

  PRIMARY KEY (id)
);

INSERT INTO email_campaign_tracking_types (name, description) VALUES ('view', 'VIEW');
INSERT INTO email_campaign_tracking_types (name, description) VALUES ('click', 'CLICK');

CREATE TABLE email_campaign_tracking_links (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  campaign_id INT UNSIGNED NOT NULL REFERENCES email_campaign(id),
  url TEXT,
  label TEXT,
  uid CHAR (36), /*may use php uniqid() or guid with hyphens, etc  ... will use this in the tracking links*/

  UNIQUE (uid),
  PRIMARY KEY (id)
);

CREATE TABLE email_campaign_tracking (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  contact_send_queue_id INT UNSIGNED NOT NULL REFERENCES email_campaign_contacts_send_queue(id),
  tracking_type_id INT UNSIGNED NOT NULL REFERENCES email_campaign_tracking_types(id),
  link_id INT UNSIGNED REFERENCES email_campaign_tracking_links(id), /*only if link click .. not normalized*/
  date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  /**
   - TODO: parse this into IP, ADDRESS, etc ..
   */
  user_agent TEXT,
  ip CHAR(45),
  country TEXT,
  region TEXT,
  city TEXT,
  zip TEXT,
  longitude TEXT,
  latitude TEXT,

  PRIMARY KEY (id)
);

/*
CREATE TABLE email_campaign_tracking_summary(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  campaign_id INT UNSIGNED NOT NULL REFERENCES email_campaign(id),
  tracking_type_id INT UNSIGNED NOT NULL REFERENCES email_campaign_tracking_types(id),
  tracking_type_value INT
);
*/


CREATE TABLE categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name TEXT NOT NULL,
  path CHAR(255) NOT NULL,

  date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  date_modified DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by INT UNSIGNED NOT NULL REFERENCES users(id),
  owner_id INT UNSIGNED REFERENCES users(id), /*access if owned or owner_id = null*/

  UNIQUE (path),
  PRIMARY KEY (id)
);

CREATE TABLE asset_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) not null UNIQUE, /*NOT using TEXT because I won't be able to index the column*/
  description TEXT,
  PRIMARY KEY (id)
);

INSERT INTO asset_types (name, description) VALUES ('image', 'Image');
INSERT INTO asset_types (name, description) VALUES ('pdf', 'PDF');
INSERT INTO asset_types (name, description) VALUES ('email', 'Email');

/*columns should be similar to "assets" */
CREATE TABLE asset_versions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) not null, /*NOT using TEXT because I won't be able to index the column*/
  filename CHAR(255) UNIQUE,
  description TEXT,
  asset_id INT UNSIGNED NOT NULL REFERENCES assets(id),
  type_id INT UNSIGNED NOT NULL REFERENCES asset_types(id),

  external_resource_id INT UNSIGNED, /*if referencing and email or something saved in a different table ...*/

  date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by INT UNSIGNED NOT NULL REFERENCES users(id),
  owner_id INT UNSIGNED REFERENCES users(id),
  is_active BOOL NOT NULL DEFAULT FALSE,

  PRIMARY KEY (id)
);

/*will have the same data as the CURRENT version*/
CREATE TABLE assets (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) not null UNIQUE, /*NOT using TEXT because I won't be able to index the column*/
  description TEXT,
  filename CHAR(255) UNIQUE,
  thumbnail CHAR(255),
  type_id INT UNSIGNED NOT NULL REFERENCES asset_types(id),

  external_resource_id INT UNSIGNED, /*if referencing and email or something saved in a different table ...*/

  date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  date_modified DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by INT UNSIGNED NOT NULL REFERENCES users(id),
  owner_id INT UNSIGNED REFERENCES users(id),

  PRIMARY KEY (id)
);

CREATE TABLE asset_categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id INT UNSIGNED NOT NULL REFERENCES categories(id),
  asset_id INT UNSIGNED NOT NULL REFERENCES assets(id),

  PRIMARY KEY (id)
);

/*
- users can unsubscribe from these types
- select a type when sending a campaign ..
*/
CREATE TABLE marketing_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) not null UNIQUE, /*NOT using TEXT because I won't be able to index the column*/
  description TEXT,
  PRIMARY KEY (id)
);

INSERT INTO marketing_types (name, description) VALUES ('all', 'All');
INSERT INTO marketing_types (name, description) VALUES ('general', 'General');
INSERT INTO marketing_types (name, description) VALUES ('sales', 'Sales');

CREATE TABLE unsubscribe (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  marketing_type_id  INT UNSIGNED REFERENCES marketing_types(id),
  contact_id  INT UNSIGNED REFERENCES contacts(id),
  is_active BOOLEAN NOT NULL DEFAULT TRUE, /*maintain a history .. the "active" one is current - need index*/

  date_added DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  added_by INT UNSIGNED NOT NULL REFERENCES users(id),

  INDEX(is_active),
  INDEX(marketing_type_id),
  PRIMARY KEY (id)
);

/*Programs*/
CREATE TABLE program (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name CHAR(255) NOT NULL UNIQUE,
  type  TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
  start_date DATETIME NOT NULL,
  re_evaluate_contacts BOOLEAN NOT NULL DEFAULT TRUE,

  date_added DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  added_by INT UNSIGNED NOT NULL REFERENCES users(id),

  INDEX(is_active),
  INDEX(is_deleted),
  PRIMARY KEY (id)
);

CREATE TABLE program_drip (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  program_id  INT UNSIGNED REFERENCES program(id),
  name CHAR(255) NOT NULL,
  send_date DATETIME,
  send_interval INT,
  send_interval_type TEXT,
  sort INT,
  is_deleted BOOLEAN NOT NULL DEFAULT FALSE,

  /*for "fixed" programs, I want to know if the drip was processed, regardless of the date ...
  - don't want to be concerned with the date being passed and the drip not being processed yet
  */
  is_finished BOOLEAN NOT NULL DEFAULT FALSE COMMENT "for fixed programs - has the drip been processed?",

  INDEX(is_deleted),
  PRIMARY KEY (id)
);
/*
@todo: would need to handle
- groups in the condition or action have been deleted
- contact fields in action or condition have been deleted
- ... throw PHP event when field or group deleted
-     check for groups in programs, etc ... alert the user. Can't just delete the group from the condition, etc
*/
CREATE TABLE program_drip_item (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  drip_id  INT UNSIGNED REFERENCES program_drip(id),
  name CHAR(255) NOT NULL,
  type CHAR(50),
  `condition` TEXT, /*json - condition is a keyword in mysql using backtick to quote the column name*/
  condition_description TEXT,
  resource_id INT UNSIGNED,
  resource_thumbnail TEXT,
  action_config TEXT, /*JSON*/
  action_config_description TEXT,
  sort INT,
  is_deleted BOOLEAN NOT NULL DEFAULT FALSE,

  INDEX(is_deleted),
  PRIMARY KEY (id)
);

/*
- instead of using queues ... in the previous sense, we'll just maintain a list of all contacts in all programs, and whether they were sent
 - that way, we won't have duplicate contacts in different queues .. essentially the same thing though ..
    - except we won't process a queue at a time, we'll process a drip at a time
      - process a program
      - process each drip
      - process each item - condition - add all contacts meeting this condition

- multiple workers for a program
  - worker to find contacts to add to the drip (first drip) ... any item of the drip
      - different sub processes for each item??
      - if the same "condition"/drip within x timeframe .. reuse the contacts?? -cache in redis for x seconds ..??
  - worker to send contacts for the current drip
      - foreach item in the drip, send all contacts associated with the item - if the criteria is met
          - get all contacts associated with the program
          - if next drip to process = this drip, send them... if criteria met for the item
            - get the ID's of the drips ... ordered my "sort-order"
            - find the next drip - drip that follows the contact's last processed drip

- different sub process for each program

##########
# contacts associated with any item in the previous drip, that meet the current item criteria ...

*/

CREATE TABLE program_item_contacts_queue(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_id  INT UNSIGNED REFERENCES program_drip_item(id),
  contact_id  INT UNSIGNED REFERENCES contacts(id),
  processed BOOLEAN DEFAULT FALSE,
  date_processed DATETIME,
  processed_resource_id INT UNSIGNED, /*0 = actually not scheduled for this contact/item */

  PRIMARY KEY (id),
  INDEX (item_id, contact_id),
  INDEX (processed),
  INDEX (date_processed)
);


COMMIT;