From 2956fc8b709c43761428cd8118bd75f8324f2635 Mon Sep 17 00:00:00 2001
From: Steve Sutton <steve@gaslightmedia.com>
Date: Tue, 26 Jul 2016 15:12:06 -0400
Subject: [PATCH] Leads table updates for stats.

Chuck and I discussed the tables for the leads stats need to change.
We're removing the original tables and using one called
{prefix}lead_stats which will store the daily weekly monthly stats.
Then we could very simply add more types like yearly or whatever.
---
 index.php                                     |  2 +-
 ..._V0.0.4.sql => create_database_V0.0.7.sql} | 30 +++--------------
 setup/databaseScripts/dbVersions.php          |  1 +
 .../update_database_V0.0.7.sql                | 33 +++++++++++++++++++
 4 files changed, 40 insertions(+), 26 deletions(-)
 rename setup/databaseScripts/{create_database_V0.0.4.sql => create_database_V0.0.7.sql} (83%)
 create mode 100644 setup/databaseScripts/update_database_V0.0.7.sql

diff --git a/index.php b/index.php
index 7408dbe..7e53dfd 100644
--- a/index.php
+++ b/index.php
@@ -38,7 +38,7 @@
  *  version from this plugin.
  */
 define('GLM_MEMBERS_LEADS_PLUGIN_VERSION', '0.0.1');
-define('GLM_MEMBERS_LEADS_PLUGIN_DB_VERSION', '0.0.6');
+define('GLM_MEMBERS_LEADS_PLUGIN_DB_VERSION', '0.0.7');
 
 // This is the minimum version of the GLM Members DB plugin require for this plugin.
 define('GLM_MEMBERS_LEADS_PLUGIN_MIN_MEMBERS_REQUIRED_VERSION', '2.0.5');
diff --git a/setup/databaseScripts/create_database_V0.0.4.sql b/setup/databaseScripts/create_database_V0.0.7.sql
similarity index 83%
rename from setup/databaseScripts/create_database_V0.0.4.sql
rename to setup/databaseScripts/create_database_V0.0.7.sql
index c6ed18c..cee28fa 100644
--- a/setup/databaseScripts/create_database_V0.0.4.sql
+++ b/setup/databaseScripts/create_database_V0.0.7.sql
@@ -4,7 +4,7 @@
 -- Database Creation Script
 --
 -- This file is called to create a new set of tables for this
--- add-on for the most receint database version for this add-on.
+-- add-on for the most recent database version for this add-on.
 --
 -- There should only be one such file in this directory
 --
@@ -130,33 +130,13 @@ CREATE TABLE {prefix}searches (
 
 ----
 
--- Lead Stats Date Data - Totals of lead stats for 1 day - Preserved for 2 years
-CREATE TABLE {prefix}lead_stats_date (
+-- Lead Stats Data - Totals of lead stats - Preserved for 2 years
+CREATE TABLE {prefix}lead_stats (
   id INT NOT NULL AUTO_INCREMENT,
+  stat_type TINYTEXT NULL,                  -- Type of stat Daily, Weekly, Monthly...
   stat_date DATE NULL,                      -- Date for which these stats are accumulated
   leads_count INTEGER NULL,                 -- Number of leads
   PRIMARY KEY (id),
+  INDEX (stat_type(10)),
   INDEX (stat_date)
 );
-
-----
-
--- Lead Stats Week Data - Totals of lead stats for 1 week - Preserved for 2 years - (generated daily?)
-CREATE TABLE {prefix}lead_stats_week (
-  id INT NOT NULL AUTO_INCREMENT,
-  stat_week DATE NULL,                      -- First date of week for which these stats are accumulated
-  leads_count INTEGER NULL,                 -- Number of leads
-  PRIMARY KEY (id),
-  INDEX (stat_week)
-);
-
-----
-
--- Lead Stats Month Data - Totals of lead stats for 1 month - Preserved indefinately - (generated daily?)
-CREATE TABLE {prefix}lead_stats_month (
-  id INT NOT NULL AUTO_INCREMENT,
-  stat_month DATE NULL,                     -- First date of month for which these stats are accumulated
-  leads_count INTEGER NULL,                 -- Number of leads
-  PRIMARY KEY (id),
-  INDEX (stat_month)
-);
diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php
index 7ffdae4..5452f62 100644
--- a/setup/databaseScripts/dbVersions.php
+++ b/setup/databaseScripts/dbVersions.php
@@ -20,5 +20,6 @@ $glmMembersLeadsDbVersions = array(
     '0.0.4' => array('version' => '0.0.4', 'tables' => 10, 'date' => '7/21/2016'),
     '0.0.5' => array('version' => '0.0.5', 'tables' => 10, 'date' => '7/22/2016'),
     '0.0.6' => array('version' => '0.0.6', 'tables' => 11, 'date' => '7/22/2016'),
+    '0.0.7' => array('version' => '0.0.7', 'tables' => 9,  'date' => '7/26/2016'),
 );
 
diff --git a/setup/databaseScripts/update_database_V0.0.7.sql b/setup/databaseScripts/update_database_V0.0.7.sql
new file mode 100644
index 0000000..c903bd3
--- /dev/null
+++ b/setup/databaseScripts/update_database_V0.0.7.sql
@@ -0,0 +1,33 @@
+-- Gaslight Media Members Database  - Events Add-On
+-- File Created: 16/07/19 16:16:16
+-- Database Version: 0.0.3
+-- Database Update From Previous Version Script
+--
+-- To permit each query below to be executed separately,
+-- all queries must be separated by a line with four dashes
+
+-- Lead Stats Data - Totals of lead stats - Preserved for 2 years
+CREATE TABLE {prefix}lead_stats (
+  id INT NOT NULL AUTO_INCREMENT,
+  stat_type TINYTEXT NULL,                  -- Type of stat Daily, Weekly, Monthly...
+  stat_date DATE NULL,                      -- Date for which these stats are accumulated
+  leads_count INTEGER NULL,                 -- Number of leads
+  PRIMARY KEY (id),
+  INDEX (stat_type(10)),
+  INDEX (stat_date)
+);
+
+----
+
+-- Drop table
+DROP TABLE {prefix}lead_stats_date;
+
+----
+
+-- Drop table
+DROP TABLE {prefix}lead_stats_week;
+
+----
+
+-- Drop table
+DROP TABLE {prefix}lead_stats_month;
-- 
2.17.1