In all likelihood I have completely changed the PHP, (JS:CSS:HTML)=WEB, or SQL code below take what you can, enjoy ~Luke | Code Questions? Contact Me.

Below is nearly the entire web database code for managing AYSO Players, Volunteers, Teams, and more. This code would setup the entire database structure, this naturally excludes our region data. More documentation to come later.

-- $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ayso_rrdl$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
-- -------------------------------- Create The Data Lookup Area    +++++++++++++++++++++
CREATE DATABASE IF NOT EXISTS ayso_rrdl
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE ayso_rrdl;
 
-- -------------------------------- Lookup Tables Stagnant Values  +++++++++++++++++++++
-- Set Region Volunteer Roles                  =====================
/*
This table is used to store all the volunteer roles for the region. Lookup tables are
set with static values, if you change the insert order, check the joins and filters
that might use the sk instead of the code.
JOIN on the Role Code.
NOTE: some joins are on the Surrogate Key No. / record order #.
*/
DROP TABLE IF EXISTS ayso_rrdl.volunteer_role;
CREATE TABLE IF NOT EXISTS ayso_rrdl.volunteer_role (
  role_cd VARCHAR(2) NOT NULL COMMENT 'Region Volunteer Role Code (hc)',
  role_cat VARCHAR(20) DEFAULT NULL COMMENT 'Region Volunteer Role Category',
  role_name VARCHAR(20) NOT NULL COMMENT 'Region Volunteer Role Name',
  reg_req CHAR(1) NOT NULL COMMENT 'Volunteer Registration Required',
  sh_req CHAR(1) NOT NULL COMMENT 'Safe Haven Training and Certification Required',
  job_cert_req CHAR(1) NOT NULL COMMENT 'Job Training and Certification Required',
  plcmnt_pref CHAR(1) NOT NULL COMMENT 'Preferential Team Placement',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  role_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (role_sk),
  CONSTRAINT vr_rolecd UNIQUE(role_cd)
) DEFAULT CHARSET=utf8;
INSERT IGNORE INTO ayso_rrdl.volunteer_role
  (
  role_cd,role_name,role_cat,reg_req,sh_req,job_cert_req,plcmnt_pref
  )
  VALUES
  ('hc',  'Head Coach',    'Head Coach',      'Y','Y','Y','Y'),
  ('ac',  'Asst. Coach',    'Asst. Coach',      'Y','Y','Y','Y'),
  ('r1',  'Referee 1',    'Referee',        'Y','Y','Y','N'),
  ('r2',  'Referee 2',    '',            'Y','Y','Y','N'),
  ('r3',  'Referee 3',    '',            'Y','Y','Y','N'),
  ('tm',  'Team Manager',    'Team Manager',      'Y','Y','N','N'),
  ('rv',  'Registration',    'Registration',      'N','N','N','N'),
  ('s1',  'Safety 1',      'Safety Station',    'N','N','N','N'),
  ('s2',  'Safety 2',      '',            'N','N','N','N'),
  ('c1',  'Concessions 1',  'Concessions',      'N','N','N','N'),
  ('c2',  'Concessions 2',  '',            'N','N','N','N'),
  ('f1',  'Fields 1',      'Fields',        'N','N','N','N'),
  ('f2',  'Fields 2',      '',            'N','N','N','N'),
  ('ev',  'Special Events',  'Events',        'N','N','N','N'),
  ('bv',  'Board Member',    'Board Member',      'Y','Y','Y','Y'),
  ('dc',  'Division Coord.',  'Division Coord.',    'Y','Y','N','Y'),
  ('pp',  'Player Parent',  'Parent',        'N','N','N','N'),
  ('op',  'Other Person',    'Other',        'N','N','N','N')
;
 
-- Set Region Divisions                      =====================
/*
This table is used to store all of the region divisions, and other outside divisions.
Mainstream divisions usually have multiple Oregon school grades included.
JOIN on the Division Code.
*/
DROP TABLE IF EXISTS ayso_rrdl.division;
CREATE TABLE IF NOT EXISTS ayso_rrdl.division (
  division_cd VARCHAR(3) NOT NULL COMMENT 'Division Code',
  division_lg_cd VARCHAR(4) NOT NULL COMMENT 'Division Long Code',
  division_name VARCHAR(20) NOT NULL COMMENT 'Division Name',
  division_grade VARCHAR(30) NOT NULL COMMENT 'Division Grade Level(s)',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  division_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (division_sk),
  CONSTRAINT d_divcd UNIQUE(division_cd)
) DEFAULT CHARSET=utf8;
INSERT IGNORE INTO ayso_rrdl.division
  (
  division_cd,division_lg_cd,division_name,division_grade
  )
  VALUES
  ('U01','U-01','Under the age of 01','PreSchool'),
  ('U02','U-02','Under the age of 02','PreSchool'),
  ('U03','U-03','Under the age of 03','PreSchool'),
  ('U04','U-04','Under the age of 04','PreSchool'),
  ('U05','U-05','Under the age of 05','PreSchool'),
  ('U06','U-06','Under the age of 06','Kindergarden'),
  ('U07','U-07','Under the age of 07','1st'),
  ('U08','U-08','Under the age of 08','1st & 2nd'),
  ('U09','U-09','Under the age of 09','3rd'),
  ('U10','U-10','Under the age of 10','3rd & 4th'),
  ('U11','U-11','Under the age of 11','5th'),
  ('U12','U-12','Under the age of 12','5th & 6th'),
  ('U13','U-13','Under the age of 13','7th'),
  ('U14','U-14','Under the age of 14','7th & 8th'),
  ('U15','U-15','Under the age of 15','9th Freshman'),
  ('U16','U-16','Under the age of 16','10th Sophomore'),
  ('U17','U-17','Under the age of 17','11th Junior'),
  ('U18','U-18','Under the age of 18','12th Senior'),
  ('U19','U-19','Under the age of 19','9th - 13th'),
  ('NON','NONE','No Division',    'None')
;
 
-- Set The Gender Identities                  =====================
/*
This table is used to store all of the genders known or unknown. Includes adult and
child gender translations and names.
JOIN on the Child Gender Code.
JOIN on the Adult Gender Code.
*/
DROP TABLE IF EXISTS ayso_rrdl.gender;
CREATE TABLE IF NOT EXISTS ayso_rrdl.gender (
  kid_gender_cd CHAR(1) NOT NULL COMMENT 'Kid Gender Code (B or G)',
  adult_gender_cd CHAR(1) NOT NULL COMMENT 'Adult Gender Code (M or F)',
  kid_gender_cd_name VARCHAR(10) NOT NULL COMMENT 'Kid Gender Name',
  adult_gender_cd_name VARCHAR(10) NOT NULL COMMENT 'Adult Gender Name',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  gender_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (gender_sk),
  CONSTRAINT g_kgencd UNIQUE(kid_gender_cd),
  CONSTRAINT g_agencd UNIQUE(adult_gender_cd)
) DEFAULT CHARSET=utf8;
INSERT IGNORE INTO ayso_rrdl.gender
  (
  kid_gender_cd,adult_gender_cd,kid_gender_cd_name,adult_gender_cd_name
  )
  VALUES
  ('B',  'M',  'Boy',    'Male'),
  ('G',  'F',  'Girl',    'Female'),
  ('C',  'C',  'Coed',    'Coed'),
  ('O',  'O',  'Other',  'Other')
;
 
-- Set The Membership Year or Fall Spring Year          =====================
/*
This table is used to store all of the operating years after the year 2000. The date
ranges can be used for code identification, as they should not overlap. This table
has surrogate keys that are referenced in many scripts.
JOIN on the Membership Year Code.
JOIN on the Membership Year Full Year Code.
JOIN on the Start Date and End Date with a BETWEEN.
*/
DROP TABLE IF EXISTS ayso_rrdl.membership_year;
CREATE TABLE IF NOT EXISTS ayso_rrdl.membership_year (
  my_cd VARCHAR(4) NOT NULL COMMENT 'Membership Year Code Short',
  my_lg_cd VARCHAR(6) NOT NULL COMMENT 'Membership Year Code Long',
  start_date DATE NOT NULL COMMENT 'Membership Year Start',
  end_date DATE NOT NULL COMMENT 'Membership Year End',
  desc_name VARCHAR(100) DEFAULT NULL COMMENT 'Membership Year Description',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  my_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (my_sk),
  CONSTRAINT my_mycd UNIQUE(my_cd),
  CONSTRAINT my_mylgcd UNIQUE(my_lg_cd),
  CONSTRAINT my_mystdt UNIQUE(start_date),
  CONSTRAINT my_myeddt UNIQUE(end_date),
  CONSTRAINT my_mydtrg UNIQUE(start_date,end_date)
) DEFAULT CHARSET=utf8;
INSERT IGNORE INTO ayso_rrdl.membership_year
  (
  my_cd,my_lg_cd,start_date,end_date,desc_name,my_sk
  )
  VALUES
  ('FS90','FS1990','1990-08-01','1991-07-31','1990 Membership Year',-90),
  ('FS91','FS1991','1991-08-01','1992-07-31','1991 Membership Year',-91),
  ('FS92','FS1992','1992-08-01','1993-07-31','1992 Membership Year',-92),
  ('FS93','FS1993','1993-08-01','1994-07-31','1993 Membership Year',-93),
  ('FS94','FS1994','1994-08-01','1995-07-31','1994 Membership Year',-94),
  ('FS95','FS1995','1995-08-01','1996-07-31','1995 Membership Year',-95),
  ('FS96','FS1996','1996-08-01','1997-07-31','1996 Membership Year',-96),
  ('FS97','FS1997','1997-08-01','1998-07-31','1997 Membership Year',-97),
  ('FS98','FS1998','1998-08-01','1999-07-31','1998 Membership Year',-98),
  ('FS99','FS1999','1999-08-01','2000-07-31','1999 Membership Year',-99),
  ('FS00','FS2000','2000-08-01','2001-07-31','2000 Membership Year',-1),
  ('FS01','FS2001','2001-08-01','2002-07-31','2001 Membership Year',1),
  ('FS02','FS2002','2002-08-01','2003-07-31','2002 Membership Year',2),
  ('FS03','FS2003','2003-08-01','2004-07-31','2003 Membership Year',3),
  ('FS04','FS2004','2004-08-01','2005-07-31','2004 Membership Year',4),
  ('FS05','FS2005','2005-08-01','2006-07-31','2005 Membership Year',5),
  ('FS06','FS2006','2006-08-01','2007-07-31','2006 Membership Year',6),
  ('FS07','FS2007','2007-08-01','2008-07-31','2007 Membership Year',7),
  ('FS08','FS2008','2008-08-01','2009-07-31','2008 Membership Year',8),
  ('FS09','FS2009','2009-08-01','2010-07-31','2009 Membership Year',9),
  ('MY10','MY2010','2010-08-01','2011-07-31','2010 Membership Year',10),
  ('MY11','MY2011','2011-08-01','2012-07-31','2011 Membership Year',11),
  ('MY12','MY2012','2012-08-01','2013-07-31','2012 Membership Year',12),
  ('MY13','MY2013','2013-08-01','2014-07-31','2013 Membership Year',13),
  ('MY14','MY2014','2014-08-01','2015-07-31','2014 Membership Year',14),
  ('MY15','MY2015','2015-08-01','2016-07-31','2015 Membership Year',15),
  ('MY16','MY2016','2016-08-01','2017-07-31','2016 Membership Year',16),
  ('MY17','MY2017','2017-08-01','2018-07-31','2017 Membership Year',17),
  ('MY18','MY2018','2018-08-01','2019-07-31','2018 Membership Year',18),
  ('MY19','MY2019','2019-08-01','2020-07-31','2019 Membership Year',19)
;
 
-- Set Region Teams                        =====================
/*
This table is used to store all of the region teams in the programs that are included
in this information system. Mostly includes the primary program, but allows for other
programs. the Team ID is used for team information lookup & is distinct. Includes 
surrogate keys from Gender, Division, and Membership Year.
JOIN on the Team Code and Membership Year Surrogate Key.
JOIN on the Team Identity.
NOTE: many joins are on the Surrogate Key No. / record order #.
*/
DROP TABLE IF EXISTS ayso_rrdl.team;
CREATE TABLE IF NOT EXISTS ayso_rrdl.team (
  team_cd VARCHAR(10) NOT NULL COMMENT 'Region Team Code (U00B00XX)',
  team_name VARCHAR(20) NOT NULL COMMENT 'Region Team Name',
  division_sk INT(10) NOT NULL COMMENT 'Region Division Surrogate Key',
  gender_sk INT(10) NOT NULL COMMENT 'Team Gender Surrogate Key',
  my_sk INT(10) NOT NULL COMMENT 'Membership Year Surrogate Key',
  team_id VARCHAR(8) NOT NULL COMMENT 'Region Team Lookup ID (NRMYDDGN)',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  team_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (team_sk),
  CONSTRAINT t_teamyear UNIQUE(team_cd,my_sk),
  CONSTRAINT t_teamid UNIQUE(team_id)
) DEFAULT CHARSET=utf8;
INSERT IGNORE INTO ayso_rrdl.team
  (
  team_cd,team_name,division_sk,gender_sk,my_sk,team_id
  )
  VALUES
  ('U00O00',    'Other',    20,4,13,'')
;
 
-- Set The List of Region Board Members              =====================
/*
This table is used to store all of the region board member translations. Is added
to emails sent with specific board members, also used as a lookup list.
JOIN on the Position Code.
*/
DROP TABLE IF EXISTS ayso_rrdl.board;
CREATE TABLE IF NOT EXISTS ayso_rrdl.board (
  first_name VARCHAR(60) NOT NULL COMMENT 'Member First Name / Nickname',
  last_name VARCHAR(60) NOT NULL COMMENT 'Member Last Name',
  position_cd VARCHAR(30) NOT NULL COMMENT 'Region Position Code',
  position_title VARCHAR(80) NOT NULL COMMENT 'Region Position Title',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  gender_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (gender_sk),
  CONSTRAINT b_poscd UNIQUE(position_cd)
) DEFAULT CHARSET=utf8;
INSERT IGNORE INTO ayso_rrdl.board
  (
  position_cd,first_name,last_name,position_title
  )
  VALUES
  ('webmaster','Luke','Cotton','Webmaster')
;
 
-- $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ayso_rrdu$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
-- -------------------------------- Create The Data Upload Area  +++++++++++++++++++++
CREATE DATABASE IF NOT EXISTS ayso_rrdu
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE ayso_rrdu;
 
-- Define a Function Returning Numeric Values Only        =====================
/*
This function is required for many triggers and returns a varchar with only numbers.
*/
DROP FUNCTION IF EXISTS ayso_rrdu.DIGITZ;
DELIMITER //
CREATE FUNCTION ayso_rrdu.DIGITZ(INPUT VARCHAR(255))
   RETURNS VARCHAR(255)
BEGIN
   DECLARE output   VARCHAR(255) DEFAULT '';
   DECLARE iterator INT          DEFAULT 1;
   WHILE iterator < (LENGTH(INPUT) + 1) DO
      IF SUBSTRING(INPUT, iterator, 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
         SET output = CONCAT(output, SUBSTRING(INPUT, iterator, 1));
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
   RETURN output;
END //
DELIMITER ;
 
-- -------------------------------- eAYSO Volunteers Related    +++++++++++++++++++++
-- Build The Data Upload for Volunteer Data            =====================
/*
This table is used to store all of the region volunteers for a specific membership
year. Using the Full Name, Email, and the Membership Year to define an individual
along with the eAYSO Identity and the Membership Year as another means of
identification.
DO NOT JOIN with this table use the data store cleaned version.
*/
-- DROP TABLE IF EXISTS ayso_rrdu.eayso_volunteer;
CREATE TABLE IF NOT EXISTS ayso_rrdu.eayso_volunteer (
  ayso_section VARCHAR(100) DEFAULT NULL COMMENT 'Section No.',
  ayso_area VARCHAR(100) DEFAULT NULL COMMENT 'Area Letter',
  ayso_region VARCHAR(100) DEFAULT NULL COMMENT 'Region No.',
  eayso_id VARCHAR(100) NOT NULL COMMENT 'ID No.',
  start_date VARCHAR(100) DEFAULT NULL COMMENT 'Volunteer Span Start',
  end_date VARCHAR(100) DEFAULT NULL COMMENT 'Volunteer Span End',
  volunteer_position VARCHAR(200) DEFAULT NULL COMMENT 'Position(s)',
  last_name VARCHAR(100) NOT NULL COMMENT 'Last Name',
  first_name VARCHAR(100) NOT NULL COMMENT 'First Name',
  middle_name VARCHAR(100) DEFAULT NULL COMMENT 'Middle Name',
  suffix VARCHAR(100) DEFAULT NULL COMMENT 'Name Suffix',
  birth_date VARCHAR(100) DEFAULT NULL COMMENT 'Date of Birth',
  gender_cd VARCHAR(100) DEFAULT NULL COMMENT 'Gender',
  phscl_street VARCHAR(100) DEFAULT NULL COMMENT 'Home Address',
  phscl_apt VARCHAR(100) DEFAULT NULL COMMENT 'Home Apt. No.',
  phscl_city VARCHAR(100) DEFAULT NULL COMMENT 'Home City',
  phscl_state_cd VARCHAR(100) DEFAULT NULL COMMENT 'Home State Code',
  phscl_zip VARCHAR(100) DEFAULT NULL COMMENT 'Home Zip Code',
  mail_street VARCHAR(100) DEFAULT NULL COMMENT 'Mailing Location',
  mail_apt VARCHAR(100) DEFAULT NULL COMMENT 'Mailing Apt. No.',
  mail_city VARCHAR(100) DEFAULT NULL COMMENT 'Mailing City',
  mail_state_cd VARCHAR(100) DEFAULT NULL COMMENT 'Mailing State Code',
  mail_zip VARCHAR(100) DEFAULT NULL COMMENT 'Mailing Zip Code',
  prior_street VARCHAR(100) DEFAULT NULL COMMENT 'Old Address',
  prior_apt VARCHAR(100) DEFAULT NULL COMMENT 'Old Apt. No.',
  prior_city VARCHAR(100) DEFAULT NULL COMMENT 'Old City',
  prior_state_cd VARCHAR(100) DEFAULT NULL COMMENT 'Old State Code',
  prior_zip VARCHAR(100) DEFAULT NULL COMMENT 'Old Zip Code',
  ayso_phone VARCHAR(100) DEFAULT NULL COMMENT 'AYSO Phone',
  home_phone VARCHAR(100) DEFAULT NULL COMMENT 'Home Phone',
  work_phone VARCHAR(100) DEFAULT NULL COMMENT 'Work Phone',
  work_phone_ext VARCHAR(100) DEFAULT NULL COMMENT 'Work Phone Ext.',
  fax VARCHAR(100) DEFAULT NULL COMMENT 'Fax No.',
  cell_phone VARCHAR(100) DEFAULT NULL COMMENT 'Cell Phone',
  publish_home_phone VARCHAR(100) DEFAULT NULL COMMENT 'Dist. Home Phone?',
  publish_work_phone VARCHAR(100) DEFAULT NULL COMMENT 'Dist. Work Phone?',
  publish_cell_phone VARCHAR(100) DEFAULT NULL COMMENT 'Dist. Cell Phone?',
  employer VARCHAR(200) DEFAULT NULL COMMENT 'Employer',
  email VARCHAR(80) NOT NULL COMMENT 'Email',
  send_ayso_mail VARCHAR(100) DEFAULT NULL COMMENT 'AYSO Ads?',
  send_other_mail VARCHAR(100) DEFAULT NULL COMMENT 'Other Ads?',
  my_lg_cd VARCHAR(6) NOT NULL COMMENT 'Membership Year',
  memo_note VARCHAR(1000) DEFAULT NULL COMMENT 'memo_note/Comment',
  nick_name VARCHAR(100) DEFAULT NULL COMMENT 'Nick Name',
  maiden_name VARCHAR(100) DEFAULT NULL COMMENT 'Maiden Name',
  registered_by VARCHAR(100) DEFAULT NULL COMMENT 'Vol. Registered By _',
  registered_date VARCHAR(100) DEFAULT NULL COMMENT 'Vol. Registered Date',
  changed_by VARCHAR(100) DEFAULT NULL COMMENT 'Record Changed By _',
  changed_date VARCHAR(100) DEFAULT NULL COMMENT 'Record Changed Date',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  eayso_vol_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (eayso_vol_sk),
  CONSTRAINT ev_myeaysoid UNIQUE(first_name,last_name,email,my_lg_cd),
  CONSTRAINT ev_memid UNIQUE(eayso_id,my_lg_cd)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdu.eayso_volunteer;
-- ALTER TABLE ayso_rrdu.eayso_volunteer AUTO_INCREMENT=1;
 
-- -------------------------------- Volunteer Certification Related  +++++++++++++++++++++
-- Build The Data Upload for Volunteer Certifications      =====================
/*
This table is used to store all of the region volunteer certifications. The cert
is bound to one eAYSO identity that could have completed the same certification
multiple times. Only registered volunteers for the year uploaded will be associated
in the same upload. Membership Year is an unnecessary piece of the constraint, but
allows for backtrack.
DO NOT JOIN with this table use the data store cleaned version.
*/
-- DROP TABLE IF EXISTS ayso_rrdu.eayso_cert;
CREATE TABLE IF NOT EXISTS ayso_rrdu.eayso_cert (
  eayso_id VARCHAR(40) NOT NULL COMMENT 'eAYSO ID',
  full_name VARCHAR(100) DEFAULT NULL COMMENT 'Full Name',
  phscl_street VARCHAR(100) DEFAULT NULL COMMENT 'Home Address',
  phscl_city VARCHAR(100) DEFAULT NULL COMMENT 'Home City',
  phscl_state_cd VARCHAR(100) DEFAULT NULL COMMENT 'Home State Code',
  phscl_zip VARCHAR(100) DEFAULT NULL COMMENT 'Home Zip Code',
  home_phone VARCHAR(100) DEFAULT NULL COMMENT 'Home Phone',
  bus_phone VARCHAR(100) DEFAULT NULL COMMENT 'Business Phone',
  email VARCHAR(80) DEFAULT NULL COMMENT 'Email',
  cert VARCHAR(100) NOT NULL COMMENT 'Certification',
  gender_cd VARCHAR(100) DEFAULT NULL COMMENT 'Gender Code',
  S_A_R VARCHAR(100) DEFAULT NULL COMMENT 'Section Area Region',
  cert_date VARCHAR(40) DEFAULT NULL COMMENT 'Certification Date',
  recert_date VARCHAR(100) DEFAULT NULL COMMENT 'Re-Certification Date',
  first_name VARCHAR(100) NOT NULL COMMENT 'First Name',
  last_name VARCHAR(100) NOT NULL COMMENT 'Last Name',
  section_name VARCHAR(100) DEFAULT NULL COMMENT 'Section Name',
  area_name VARCHAR(100) DEFAULT NULL COMMENT 'Area Name',
  region_no VARCHAR(100) DEFAULT NULL COMMENT 'Region Number',
  my_lg_cd VARCHAR(6) NOT NULL COMMENT 'Membership Year',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  eayso_c_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (eayso_c_sk),
  CONSTRAINT ec_eaysoid UNIQUE(eayso_id,cert,cert_date,my_lg_cd)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdu.eayso_cert;
-- ALTER TABLE ayso_rrdu.eayso_cert AUTO_INCREMENT=1;
 
-- -------------------------------- Team Formation Related      +++++++++++++++++++++
-- Build The Data Upload for Team Rosters            =====================
/*
This table is used to store all of the team roster data. Table is often used for
eAYSO volunteer validation and comparison with eAYSO team data. The constrains relate
to one player on one team for one membership year.
DO NOT JOIN with this table use the data store cleaned version with eAYSO Identities.
*/
-- DROP TABLE IF EXISTS ayso_rrdu.eayso_team;
CREATE TABLE IF NOT EXISTS ayso_rrdu.eayso_team (
  region_no VARCHAR(100) DEFAULT NULL COMMENT 'Region Number',
  region_name VARCHAR(100) DEFAULT NULL COMMENT 'Region Name',
  team_cd VARCHAR(30) NOT NULL COMMENT 'Team Code',
  team_name VARCHAR(100) DEFAULT NULL COMMENT 'Team Name',
  hc_first_name VARCHAR(100) DEFAULT NULL COMMENT 'Head Coach First Name',
  hc_last_name VARCHAR(100) DEFAULT NULL COMMENT 'Head Coach Last Name',
  hc_eayso_id VARCHAR(100) DEFAULT NULL COMMENT 'Head Coach eAYSO ID',
  hc_phone VARCHAR(100) DEFAULT NULL COMMENT 'Head Coach Home Phone',
  hc_cell_phone VARCHAR(100) DEFAULT NULL COMMENT 'Head Coach Cell Phone',
  hc_email VARCHAR(100) DEFAULT NULL COMMENT 'Head Coach Email',
  hc_cert VARCHAR(100) DEFAULT NULL COMMENT 'Head Coach Max Certs',
  hc_cert_date VARCHAR(100) DEFAULT NULL COMMENT 'Head Coach SH Cert Date',
  ac_first_name VARCHAR(100) DEFAULT NULL COMMENT 'Asst. Coach First Name',
  ac_last_name VARCHAR(100) DEFAULT NULL COMMENT 'Asst. Coach Last Name',
  ac_eayso_id VARCHAR(100) DEFAULT NULL COMMENT 'Asst. Coach eAYSO ID',
  ac_phone VARCHAR(100) DEFAULT NULL COMMENT 'Asst. Coach Home Phone',
  ac_cell_phone VARCHAR(100) DEFAULT NULL COMMENT 'Asst. Coach Cell Phone',
  ac_email VARCHAR(100) DEFAULT NULL COMMENT 'Asst. Coach Email',
  ac_cert VARCHAR(100) DEFAULT NULL COMMENT 'Asst. Coach Max Certs',
  ac_cert_date VARCHAR(100) DEFAULT NULL COMMENT 'Asst. Coach SH Cert Date',
  tm_first_name VARCHAR(100) DEFAULT NULL COMMENT 'Team Manager First Name',
  tm_last_name VARCHAR(100) DEFAULT NULL COMMENT 'Team Manager Last Name',
  tm_eayso_id VARCHAR(100) DEFAULT NULL COMMENT 'Team Manager eAYSO ID',
  tm_phone VARCHAR(100) DEFAULT NULL COMMENT 'Team Manager Home Phone',
  tm_cell_phone VARCHAR(100) DEFAULT NULL COMMENT 'Team Manager Cell Phone',
  tm_email VARCHAR(100) DEFAULT NULL COMMENT 'Team Manager Email',
  tm_cert VARCHAR(100) DEFAULT NULL COMMENT 'Team Manager Max Certs',
  tm_cert_date VARCHAR(100) DEFAULT NULL COMMENT 'Team Manager SH Cert Date',
  sponsor VARCHAR(100) DEFAULT NULL COMMENT 'Team Sponsor',
  team_color VARCHAR(100) DEFAULT NULL COMMENT 'Team Color',
  player_eayso_id VARCHAR(30) NOT NULL COMMENT 'Player eAYSO ID',
  jersey_no VARCHAR(100) DEFAULT NULL COMMENT 'Player Jersey Number',
  player_first_name VARCHAR(100) DEFAULT NULL COMMENT 'Player First Name',
  player_last_name VARCHAR(100) DEFAULT NULL COMMENT 'Player Last Name',
  player_street VARCHAR(100) DEFAULT NULL COMMENT 'Player Physical Address',
  player_city VARCHAR(100) DEFAULT NULL COMMENT 'Player Physical City',
  player_state_cd VARCHAR(100) DEFAULT NULL COMMENT 'Player Physical State Code',
  player_zip VARCHAR(100) DEFAULT NULL COMMENT 'Player Physical Zip Code',
  player_mail_street VARCHAR(100) DEFAULT NULL COMMENT 'Player Mailing Address',
  player_mail_city VARCHAR(100) DEFAULT NULL COMMENT 'Player Mailing City',
  player_mail_state_cd VARCHAR(100) DEFAULT NULL COMMENT 'Player Mailing State Code',
  player_mail_zip VARCHAR(100) DEFAULT NULL COMMENT 'Player Mailing Zip Code',
  player_home_phone VARCHAR(100) DEFAULT NULL COMMENT 'Player Home Phone',
  player_dob VARCHAR(100) DEFAULT NULL COMMENT 'Player Date of Birth',
  player_age VARCHAR(100) DEFAULT NULL COMMENT 'Player Age',
  division_name VARCHAR(100) DEFAULT NULL COMMENT 'Player Division',
  my_cd VARCHAR(4) NOT NULL COMMENT 'Membership Year Code',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  eayso_team_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (eayso_team_sk),
  CONSTRAINT et_teammemid UNIQUE(team_cd,player_eayso_id,my_cd)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdu.eayso_team;
-- ALTER TABLE ayso_rrdu.eayso_team AUTO_INCREMENT=1;
 
-- -------------------------------- Team Formation Related      +++++++++++++++++++++
-- Build The Data Upload for Region Players            =====================
/*
This table is used to store all of the players either registered or unregistered for
the membership year. Restricted to one eAYSO identity per membership year, along with
a full name, and email per membership year.
DO NOT JOIN with this table use the data store cleaned version.
*/
-- DROP TABLE IF EXISTS ayso_rrdu.eayso_player;
CREATE TABLE IF NOT EXISTS ayso_rrdu.eayso_player (
  section_name VARCHAR(100) DEFAULT NULL COMMENT 'AYSO Section Name',
  area_name VARCHAR(100) DEFAULT NULL COMMENT 'AYSO Area Name',
  region_no VARCHAR(100) DEFAULT NULL COMMENT 'AYSO Region Number',
  eayso_id VARCHAR(20) NOT NULL COMMENT 'eAYSO ID Number',
  first_name VARCHAR(80) NOT NULL COMMENT 'Player First Name',
  middle_name VARCHAR(100) DEFAULT NULL COMMENT 'Player Middle Name',
  last_name VARCHAR(80) NOT NULL COMMENT 'Player Last Name',
  suffix VARCHAR(100) DEFAULT NULL COMMENT 'Suffix',
  aka VARCHAR(100) DEFAULT NULL COMMENT 'Player Nickname',
  phscl_street VARCHAR(100) DEFAULT NULL COMMENT 'Physical Address',
  phscl_city VARCHAR(100) DEFAULT NULL COMMENT 'Physical City',
  phscl_state_cd VARCHAR(100) DEFAULT NULL COMMENT 'Physical State Code',
  phscl_zip VARCHAR(100) DEFAULT NULL COMMENT 'Physical Zip Code',
  mail_street VARCHAR(100) DEFAULT NULL COMMENT 'Mailing Address',
  mail_city VARCHAR(100) DEFAULT NULL COMMENT 'Mailing City',
  mail_state_cd VARCHAR(100) DEFAULT NULL COMMENT 'Mailing State Code',
  mail_zip VARCHAR(100) DEFAULT NULL COMMENT 'Mailing Zip Code',
  location_cd VARCHAR(100) DEFAULT NULL COMMENT 'Location Code',
  home_phone VARCHAR(100) DEFAULT NULL COMMENT 'Player Home Phone',
  email VARCHAR(80) DEFAULT NULL COMMENT 'Player Related Email',
  gender_cd VARCHAR(100) DEFAULT NULL COMMENT 'Player Gender Code',
  dob VARCHAR(100) DEFAULT NULL COMMENT 'Player Date Of Birth',
  dob_verified VARCHAR(100) DEFAULT NULL COMMENT 'Date of Birth Verification',
  age VARCHAR(100) DEFAULT NULL COMMENT 'Calculated Age',
  player_status VARCHAR(100) DEFAULT NULL COMMENT 'Player Status',
  division_lg_cd VARCHAR(100) DEFAULT NULL COMMENT 'Calculated Division Long Code',
  height VARCHAR(100) DEFAULT NULL COMMENT 'Player Height',
  weight VARCHAR(100) DEFAULT NULL COMMENT 'Player Weight',
  years_of_exp VARCHAR(100) DEFAULT NULL COMMENT 'Player Years of Soccer',
  school VARCHAR(100) DEFAULT NULL COMMENT 'Player School',
  emrg_contact VARCHAR(100) DEFAULT NULL COMMENT 'Emergency Contact Name',
  emrg_contact_phone VARCHAR(100) DEFAULT NULL COMMENT 'Emergency Contact Phone',
  doctor VARCHAR(100) DEFAULT NULL COMMENT 'Player Doctor',
  doctor_phone VARCHAR(100) DEFAULT NULL COMMENT 'Doctor Phone',
  med_ins_carrier VARCHAR(100) DEFAULT NULL COMMENT 'Medical Insurance',
  hearing_impaired VARCHAR(100) DEFAULT NULL COMMENT 'Hearing Impaired',
  vip VARCHAR(100) DEFAULT NULL COMMENT 'Very Important Player',
  is_returning_player VARCHAR(100) DEFAULT NULL COMMENT 'Is Returning?',
  POSITION VARCHAR(100) DEFAULT NULL COMMENT 'Player Field Position',
  uniform_size VARCHAR(100) DEFAULT NULL COMMENT 'Player Uniform Size',
  jersey_size VARCHAR(100) DEFAULT NULL COMMENT 'Player Jersey Size',
  jersey_no VARCHAR(100) DEFAULT NULL COMMENT 'Player Jersey Number',
  shorts_size VARCHAR(100) DEFAULT NULL COMMENT 'Player Shorts Size',
  current_rating VARCHAR(100) DEFAULT NULL COMMENT 'Current Player Rating',
  next_year_rating VARCHAR(100) DEFAULT NULL COMMENT 'Next Year Forecasted Rating',
  one_year_rating VARCHAR(100) DEFAULT NULL COMMENT 'One Year Rating',
  two_year_rating VARCHAR(100) DEFAULT NULL COMMENT 'Two Year Rating',
  three_year_rating VARCHAR(100) DEFAULT NULL COMMENT 'Three Year Rating',
  remarks VARCHAR(1000) DEFAULT NULL COMMENT 'Remarks On Player',
  siblings VARCHAR(100) DEFAULT NULL COMMENT 'Player Siblings',
  team_cd VARCHAR(100) DEFAULT NULL COMMENT 'Team Code',
  team_name VARCHAR(100) DEFAULT NULL COMMENT 'Team Name',
  team_color VARCHAR(100) DEFAULT NULL COMMENT 'Team Color',
  check_no VARCHAR(100) DEFAULT NULL COMMENT 'Payment Check Number',
  amount VARCHAR(100) DEFAULT NULL COMMENT 'Amount',
  amount_paid VARCHAR(100) DEFAULT NULL COMMENT 'Amount Paid',
  amount_due VARCHAR(100) DEFAULT NULL COMMENT 'Amount Due',
  med_condition_desc VARCHAR(100) DEFAULT NULL COMMENT 'Medical Condition Desc',
  prm_relation VARCHAR(100) DEFAULT NULL COMMENT 'Primary Parent Relation',
  prm_first_name VARCHAR(100) DEFAULT NULL COMMENT 'Primary First Name',
  prm_middle_name VARCHAR(100) DEFAULT NULL COMMENT 'Primary Middle Name',
  prm_last_name VARCHAR(100) DEFAULT NULL COMMENT 'Primary Last Name',
  prm_suffix VARCHAR(100) DEFAULT NULL COMMENT 'Primary Suffix',
  prm_aka VARCHAR(100) DEFAULT NULL COMMENT 'Primary Nickname',
  prm_street VARCHAR(100) DEFAULT NULL COMMENT 'Primary Address',
  prm_city VARCHAR(100) DEFAULT NULL COMMENT 'Primary City',
  prm_state_cd VARCHAR(100) DEFAULT NULL COMMENT 'Primary State Code',
  prm_zip VARCHAR(100) DEFAULT NULL COMMENT 'Primary Zip Code',
  prm_home_phone VARCHAR(100) DEFAULT NULL COMMENT 'Primary Home Phone',
  prm_bus_phone VARCHAR(100) DEFAULT NULL COMMENT 'Primary Work Phone',
  prm_bus_ext VARCHAR(100) DEFAULT NULL COMMENT 'Primary Work Extension',
  prm_mobile_phone VARCHAR(100) DEFAULT NULL COMMENT 'Primary Cell Phone',
  prm_employer VARCHAR(100) DEFAULT NULL COMMENT 'Primary Employer Name',
  prm_email VARCHAR(100) DEFAULT NULL COMMENT 'Primary Email Address',
  prm_gender_cd VARCHAR(100) DEFAULT NULL COMMENT 'Primary Gender Code',
  scnd_relation VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Parent Relation',
  scnd_first_name VARCHAR(100) DEFAULT NULL COMMENT 'Secondary First Name',
  scnd_middle_name VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Middle Name',
  scnd_last_name VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Last Name',
  scnd_suffix VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Suffix',
  scnd_aka VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Nickname',
  scnd_street VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Address',
  scnd_city VARCHAR(100) DEFAULT NULL COMMENT 'Secondary City',
  scnd_state_cd VARCHAR(100) DEFAULT NULL COMMENT 'Secondary State Code',
  scnd_zip VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Zip Code',
  scnd_home_phone VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Home Phone',
  scnd_bus_phone VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Work Phone',
  scnd_bus_ext VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Work Phone Extension',
  scnd_mobile_phone VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Cell Phone',
  scnd_employer VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Employer Name',
  scnd_email VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Email Address',
  scnd_gender_cd VARCHAR(100) DEFAULT NULL COMMENT 'Secondary Gender Code',
  eayso_profile_email VARCHAR(100) DEFAULT NULL COMMENT 'eAYSO Login User Name',
  registered_by VARCHAR(100) DEFAULT NULL COMMENT 'Registrar',
  registered_date VARCHAR(100) DEFAULT NULL COMMENT 'Registration Date',
  created_by VARCHAR(100) DEFAULT NULL COMMENT 'Player Profile Creator',
  created_date VARCHAR(100) DEFAULT NULL COMMENT 'Created Date',
  changed_by VARCHAR(100) DEFAULT NULL COMMENT 'Recently Changed By',
  changed_date VARCHAR(100) DEFAULT NULL COMMENT 'Date of Change',
  my_cd VARCHAR(4) NOT NULL COMMENT 'Membership Year Code',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  eayso_player_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (eayso_player_sk),
  CONSTRAINT ep_myid UNIQUE(eayso_id,my_cd),
  CONSTRAINT ep_myeaysoid UNIQUE(first_name,last_name,email,my_cd)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdu.eayso_player;
-- ALTER TABLE ayso_rrdu.eayso_player AUTO_INCREMENT=1;
 
-- $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ayso_rrdi$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
-- -------------------------------- Create The DataInsert      +++++++++++++++++++++
CREATE DATABASE IF NOT EXISTS ayso_rrdi
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE ayso_rrdi;
 
-- Define a Function Returning Numeric Values Only        =====================
/*
This function is required for many triggers and returns a varchar with only numbers.
*/
DROP FUNCTION IF EXISTS ayso_rrdi.DIGITZ;
DELIMITER //
CREATE FUNCTION ayso_rrdi.DIGITZ(INPUT VARCHAR(255))
   RETURNS VARCHAR(255)
BEGIN
   DECLARE output   VARCHAR(255) DEFAULT '';
   DECLARE iterator INT          DEFAULT 1;
   WHILE iterator < (LENGTH(INPUT) + 1) DO
      IF SUBSTRING(INPUT, iterator, 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
         SET output = CONCAT(output, SUBSTRING(INPUT, iterator, 1));
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
   RETURN output;
END //
DELIMITER ;
 
-- -------------------------------- Team Jobs Related        +++++++++++++++++++++
-- Build The Data Insert for Region Volunteer Jobs        =====================
/*
This table is used to store the team volunteer worksheets. This worksheet can be
submitted as many times as someone wants, no constraints. The trigger related to this
table will replace old records.
DO NOT JOIN with this table use the data store cleaned version.
*/
-- DROP TABLE IF EXISTS ayso_rrdi.volunteers_worksheet;
CREATE TABLE IF NOT EXISTS ayso_rrdi.volunteers_worksheet (
  team_cd VARCHAR(20) DEFAULT NULL COMMENT 'Team Code and Name',
  operate CHAR(1) DEFAULT NULL COMMENT 'New Submission or Update',
  affected VARCHAR(100) DEFAULT NULL COMMENT 'Roles Submitted',
  hc_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Head Coach First Name',
  hc_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Head Coach Last Name',
  hc_phone VARCHAR(20) DEFAULT NULL COMMENT 'Head Coach Best Phone',
  hc_email VARCHAR(60) DEFAULT NULL COMMENT 'Head Coach Best Email',
  ac_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Asst. Coach First Name',
  ac_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Asst. Coach Last Name',
  ac_phone VARCHAR(20) DEFAULT NULL COMMENT 'Asst. Coach Best Phone',
  ac_email VARCHAR(60) DEFAULT NULL COMMENT 'Asst. Coach Best Email',
  r1_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Referee 1 First Name',
  r1_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Referee 1 Last Name',
  r1_phone VARCHAR(20) DEFAULT NULL COMMENT 'Referee 1 Best Phone',
  r1_email VARCHAR(60) DEFAULT NULL COMMENT 'Referee 1 Best Email',
  r2_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Referee 2 First Name',
  r2_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Referee 2 Last Name',
  r2_phone VARCHAR(20) DEFAULT NULL COMMENT 'Referee 2 Best Phone',
  r2_email VARCHAR(60) DEFAULT NULL COMMENT 'Referee 2 Best Email',
  r3_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Referee 3 First Name',
  r3_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Referee 3 Last Name',
  r3_phone VARCHAR(20) DEFAULT NULL COMMENT 'Referee 3 Best Phone',
  r3_email VARCHAR(60) DEFAULT NULL COMMENT 'Referee 3 Best Email',
  tm_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Team Manager First Name',
  tm_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Team Manager Last Name',
  tm_phone VARCHAR(20) DEFAULT NULL COMMENT 'Team Manager Best Phone',
  tm_email VARCHAR(60) DEFAULT NULL COMMENT 'Team Manager Best Email',
  rv_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Reg Vol First Name',
  rv_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Reg Vol Last Name',
  rv_phone VARCHAR(20) DEFAULT NULL COMMENT 'Reg Vol Best Phone',
  rv_email VARCHAR(60) DEFAULT NULL COMMENT 'Reg Vol Best Email',
  s1_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Safety 1 First Name',
  s1_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Safety 1 Last Name',
  s1_phone VARCHAR(20) DEFAULT NULL COMMENT 'Safety 1 Best Phone',
  s1_email VARCHAR(60) DEFAULT NULL COMMENT 'Safety 1 Best Email',
  s2_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Safety 2 First Name',
  s2_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Safety 2 Last Name',
  s2_phone VARCHAR(20) DEFAULT NULL COMMENT 'Safety 2 Best Phone',
  s2_email VARCHAR(60) DEFAULT NULL COMMENT 'Safety 2 Best Email',
  c1_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Concessions 1 First Name',
  c1_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Concessions 1 Last Name',
  c1_phone VARCHAR(20) DEFAULT NULL COMMENT 'Concessions 1 Best Phone',
  c1_email VARCHAR(60) DEFAULT NULL COMMENT 'Concessions 1 Best Email',
  c2_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Concessions 2 First Name',
  c2_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Concessions 2 Last Name',
  c2_phone VARCHAR(20) DEFAULT NULL COMMENT 'Concessions 2 Best Phone',
  c2_email VARCHAR(60) DEFAULT NULL COMMENT 'Concessions 2 Best Email',
  f1_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Fields 1 First Name',
  f1_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Fields 1 Last Name',
  f1_phone VARCHAR(20) DEFAULT NULL COMMENT 'Fields 1 Best Phone',
  f1_email VARCHAR(60) DEFAULT NULL COMMENT 'Fields 1 Best Email',
  f2_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Fields 2 First Name',
  f2_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Fields 2 Last Name',
  f2_phone VARCHAR(20) DEFAULT NULL COMMENT 'Fields 2 Best Phone',
  f2_email VARCHAR(60) DEFAULT NULL COMMENT 'Fields 2 Best Email',
  bv_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Board First Name',
  bv_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Board Last Name',
  bv_phone VARCHAR(20) DEFAULT NULL COMMENT 'Board Best Phone',
  bv_email VARCHAR(60) DEFAULT NULL COMMENT 'Board Best Email',
  ev_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Special Events First Name',
  ev_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Special Events Last Name',
  ev_phone VARCHAR(20) DEFAULT NULL COMMENT 'Special Events Best Phone',
  ev_email VARCHAR(60) DEFAULT NULL COMMENT 'Special Events Best Email',
  prac_loc VARCHAR(60) DEFAULT NULL COMMENT 'Team Practice Location',
  prac_time VARCHAR(60) DEFAULT NULL COMMENT 'Team Practice Time',
  uploader_name VARCHAR(120) DEFAULT NULL COMMENT 'Uploader Name',
  ticket VARCHAR(10) DEFAULT NULL COMMENT 'Submission ID',
  my_cd VARCHAR(4) DEFAULT NULL COMMENT 'Membership Year Code (MY00)',
  memo_note VARCHAR(1000) DEFAULT NULL COMMENT 'Comments or Other Notes',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  worksheet_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (worksheet_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdi.volunteers_worksheet;
-- ALTER TABLE ayso_rrdi.volunteers_worksheet AUTO_INCREMENT=1;
 
-- -------------------------------- Team Formation Related      +++++++++++++++++++++
-- Build The Data Insert for Region Player Formation Cards    =====================
/*
This table is used to store the player formation cards. No constraints on this table,
but the trigger will replace old records based on the eAYSO identifier.
DO NOT JOIN with this table use the data store cleaned version.
*/
-- Create player card table for team formation          =====================
-- DROP TABLE IF EXISTS ayso_rrdi.player_card;
CREATE TABLE IF NOT EXISTS ayso_rrdi.player_card (
  player_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Player First Name',
  player_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Player Last Name',
  eayso_id INT(8) DEFAULT NULL COMMENT 'Player eAYSO ID',
  player_contact_phone VARCHAR(12) DEFAULT NULL COMMENT 'Player Contact Phone',
  player_dob VARCHAR(10) DEFAULT NULL COMMENT 'Player Date of Birth',
  player_age VARCHAR(5) DEFAULT NULL COMMENT 'Player Age',
  player_division_cd VARCHAR(3) DEFAULT NULL COMMENT 'Player Division',
  player_school VARCHAR(20) DEFAULT NULL COMMENT 'Player Current or Future School',
  player_gender_cd CHAR(1) DEFAULT NULL COMMENT 'Player Gender Code',
  player_street VARCHAR(60) DEFAULT NULL COMMENT 'Player Street Address',
  player_city VARCHAR(20) DEFAULT NULL COMMENT 'Player City',
  player_height INT(3) DEFAULT NULL COMMENT 'Player Height in Inches',
  player_weight INT(3) DEFAULT NULL COMMENT 'Player Weight in Pounds',
  player_practice1 VARCHAR(20) DEFAULT NULL COMMENT 'Player Practice Zone Choice 1',
  player_practice2 VARCHAR(20) DEFAULT NULL COMMENT 'Player Practice Zone Choice 2',
  player_yrec INT(2) DEFAULT NULL COMMENT 'Player Years of Rec Soccer',
  player_yclub INT(2) DEFAULT NULL COMMENT 'Player Years of Club Soccer',
  player_request VARCHAR(100) DEFAULT NULL COMMENT 'Player Special Requests',
  parent1_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Primary Parent First Name',
  parent1_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Primary Parent Last Name',
  parent1_phone VARCHAR(12) DEFAULT NULL COMMENT 'Primary Parent Phone',
  parent1_email VARCHAR(80) DEFAULT NULL COMMENT 'Primary Parent Email',
  parent2_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Secondary Parent First Name',
  parent2_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Secondary Parent Last Name',
  parent2_phone VARCHAR(12) DEFAULT NULL COMMENT 'Secondary Parent Phone',
  parent2_email VARCHAR(80) DEFAULT NULL COMMENT 'Secondary Parent Email',
  parent3_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Other Parent First Name',
  parent3_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Other Parent Last Name',
  parent3_phone VARCHAR(12) DEFAULT NULL COMMENT 'Other Parent Phone',
  parent3_email VARCHAR(80) DEFAULT NULL COMMENT 'Other Parent Email',
  volunteer_select VARCHAR(20) DEFAULT NULL COMMENT 'Volunteering Parent',
  volunteer_choice1 VARCHAR(20) DEFAULT NULL COMMENT 'Chosen Volunteer Position 1',
  volunteer_choice2 VARCHAR(20) DEFAULT NULL COMMENT 'Chosen Volunteer Position 2',
  pc_id VARCHAR(10) DEFAULT NULL COMMENT 'Unique ID for Record',
  ticket VARCHAR(10) DEFAULT NULL COMMENT 'Ticket for Record',
  my_cd VARCHAR(4) DEFAULT NULL COMMENT 'Membership Year Code (MY00)',
  eayso_file VARCHAR(1000) DEFAULT NULL COMMENT 'Path of eAYSO Registration',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  pc_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (pc_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdi.player_card;
-- ALTER TABLE ayso_rrdi.player_card AUTO_INCREMENT=1;
 
-- Build The Data Insert for Team Rosters to be Returned    =====================
/*
This table is used to store the team roster return information, only the most recent
is needed. The player count is used in the trigger to load only the players updated.
DO NOT JOIN with this table use the data store cleaned version.
*/
-- DROP TABLE IF EXISTS ayso_rrdi.team_roster_return;
CREATE TABLE IF NOT EXISTS ayso_rrdi.team_roster_return (
  team_cd VARCHAR(30) NOT NULL COMMENT 'Team Code',
  player_count VARCHAR(4) NOT NULL COMMENT 'Roster Number of Players',
  p1_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 1 Name',
  p1_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 1 eAYSO ID',
  p1_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 1 Path Return or Leave',
  p1_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 1 Comments',
  p2_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 2 Name',
  p2_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 2 eAYSO ID',
  p2_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 2 Path Return or Leave',
  p2_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 2 Comments',
  p3_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 3 Name',
  p3_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 3 eAYSO ID',
  p3_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 3 Path Return or Leave',
  p3_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 3 Comments',
  p4_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 4 Name',
  p4_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 4 eAYSO ID',
  p4_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 4 Path Return or Leave',
  p4_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 4 Comments',
  p5_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 5 Name',
  p5_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 5 eAYSO ID',
  p5_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 5 Path Return or Leave',
  p5_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 5 Comments',
  p6_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 6 Name',
  p6_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 6 eAYSO ID',
  p6_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 6 Path Return or Leave',
  p6_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 6 Comments',
  p7_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 7 Name',
  p7_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 7 eAYSO ID',
  p7_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 7 Path Return or Leave',
  p7_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 7 Comments',
  p8_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 8 Name',
  p8_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 8 eAYSO ID',
  p8_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 8 Path Return or Leave',
  p8_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 8 Comments',
  p9_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 1 Name',
  p9_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 9 eAYSO ID',
  p9_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 9 Path Return or Leave',
  p9_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 9 Comments',
  p10_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 10 Name',
  p10_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 10 eAYSO ID',
  p10_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 10 Path Return or Leave',
  p10_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 10 Comments',
  p11_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 11 Name',
  p11_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 11 eAYSO ID',
  p11_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 11 Path Return or Leave',
  p11_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 11 Comments',
  p12_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 12 Name',
  p12_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 12 eAYSO ID',
  p12_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 12 Path Return or Leave',
  p12_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 12 Comments',
  p13_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 13 Name',
  p13_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 13 eAYSO ID',
  p13_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 13 Path Return or Leave',
  p13_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 13 Comments',
  p14_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 14 Name',
  p14_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 14 eAYSO ID',
  p14_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 14 Path Return or Leave',
  p14_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 14 Comments',
  p15_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 15 Name',
  p15_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 15 eAYSO ID',
  p15_path VARCHAR(10) DEFAULT NULL COMMENT 'Player 15 Path Return or Leave',
  p15_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 15 Comments',
  uniforms VARCHAR(10) DEFAULT NULL COMMENT 'Uniform Count',
  equip_comment VARCHAR(200) DEFAULT NULL COMMENT 'Equipment Comment',
  email VARCHAR(80) DEFAULT NULL COMMENT 'Submitter Email',
  my_cd VARCHAR(4) NOT NULL COMMENT 'Member Year Code',
  ticket VARCHAR(10) DEFAULT NULL COMMENT 'Ticket for Record',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  team_rr_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (team_rr_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdi.team_roster_return;
-- ALTER TABLE ayso_rrdi.team_roster_return AUTO_INCREMENT=1;
 
-- Build The Data Insert for Team Evaluations to be Returned  =====================
/*
This table is used to store the player evaluation return information. The player
count is used in the trigger to load only the players updated.
DO NOT JOIN with this table use the data store cleaned version.
*/
-- DROP TABLE IF EXISTS ayso_rrdi.team_evaluation;
CREATE TABLE IF NOT EXISTS ayso_rrdi.team_evaluation (
  team_cd VARCHAR(30) NOT NULL COMMENT 'Team Code',
  player_count VARCHAR(4) DEFAULT NULL COMMENT 'Roster Number of Players',
  p1_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 1 Name',
  p1_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 1 eAYSO ID',
  p1_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 1 Evaluation Score',
  p1_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 1 Comments',
  p2_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 2 Name',
  p2_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 2 eAYSO ID',
  p2_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 2 Evaluation Score',
  p2_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 2 Comments',
  p3_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 3 Name',
  p3_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 3 eAYSO ID',
  p3_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 3 Evaluation Score',
  p3_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 3 Comments',
  p4_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 4 Name',
  p4_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 4 eAYSO ID',
  p4_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 4 Evaluation Score',
  p4_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 4 Comments',
  p5_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 5 Name',
  p5_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 5 eAYSO ID',
  p5_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 5 Evaluation Score',
  p5_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 5 Comments',
  p6_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 6 Name',
  p6_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 6 eAYSO ID',
  p6_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 6 Evaluation Score',
  p6_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 6 Comments',
  p7_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 7 Name',
  p7_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 7 eAYSO ID',
  p7_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 7 Evaluation Score',
  p7_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 7 Comments',
  p8_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 8 Name',
  p8_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 8 eAYSO ID',
  p8_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 8 Evaluation Score',
  p8_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 8 Comments',
  p9_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 1 Name',
  p9_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 9 eAYSO ID',
  p9_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 9 Evaluation Score',
  p9_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 9 Comments',
  p10_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 10 Name',
  p10_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 10 eAYSO ID',
  p10_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 10 Evaluation Score',
  p10_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 10 Comments',
  p11_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 11 Name',
  p11_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 11 eAYSO ID',
  p11_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 11 Evaluation Score',
  p11_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 11 Comments',
  p12_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 12 Name',
  p12_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 12 eAYSO ID',
  p12_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 12 Evaluation Score',
  p12_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 12 Comments',
  p13_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 13 Name',
  p13_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 13 eAYSO ID',
  p13_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 13 Evaluation Score',
  p13_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 13 Comments',
  p14_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 14 Name',
  p14_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 14 eAYSO ID',
  p14_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 14 Evaluation Score',
  p14_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 14 Comments',
  p15_name VARCHAR(100) DEFAULT NULL COMMENT 'Player 15 Name',
  p15_eayso_id VARCHAR(10) DEFAULT NULL COMMENT 'Player 15 eAYSO ID',
  p15_eval VARCHAR(10) DEFAULT NULL COMMENT 'Player 15 Evaluation Score',
  p15_comment VARCHAR(200) DEFAULT NULL COMMENT 'Player 15 Comments',
  first_name VARCHAR(60) DEFAULT NULL COMMENT 'Volunteer First Legal Name',
  last_name VARCHAR(60) DEFAULT NULL COMMENT 'Volunteer Last Legal Name',
  email VARCHAR(80) DEFAULT NULL COMMENT 'Submitter Email',
  my_cd VARCHAR(4) NOT NULL COMMENT 'Member Year Code',
  ticket VARCHAR(10) DEFAULT NULL COMMENT 'Ticket for Record',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  team_eval_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (team_eval_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdi.team_evaluation;
-- ALTER TABLE ayso_rrdi.team_evaluation AUTO_INCREMENT=1;
 
-- -------------------------------- Database Jobs Related      +++++++++++++++++++++
-- Build The Data Insert for Team Evaluations to be Returned  =====================
/*
This table is used to store the job scheduler input. The data is moved to the data
market for processing.
DO NOT JOIN with this table use the data mart cleaned version.
*/
DROP TABLE IF EXISTS ayso_rrdi.scheduler;
CREATE TABLE IF NOT EXISTS ayso_rrdi.scheduler (
  scheduler VARCHAR(60) DEFAULT NULL COMMENT 'Scheduler Email Address',
  scheduled_date DATE NOT NULL COMMENT 'Scheduled Run Date',
  job_cd VARCHAR(4) NOT NULL COMMENT 'Run Job Code',
  my_cd VARCHAR(4) DEFAULT NULL COMMENT 'Membership Year Code',
  target VARCHAR(100) DEFAULT NULL COMMENT 'Intended Target(s)',
  subject VARCHAR(100) DEFAULT NULL COMMENT 'Email Subject',
  content VARCHAR(20) DEFAULT NULL DEFAULT 'text/plain' COMMENT 'Content Type',
  start_message VARCHAR(1000) DEFAULT NULL COMMENT 'Start of Email Message',
  mid_message text DEFAULT NULL COMMENT 'Middle of Email Message',
  end_message VARCHAR(1000) DEFAULT NULL COMMENT 'End of Email Message',
  file_path VARCHAR(200) DEFAULT NULL COMMENT 'File Path',
  ticket VARCHAR(10) DEFAULT NULL COMMENT 'Submission ID',
  approved CHAR(1) DEFAULT NULL COMMENT 'Allowed to be sent?',
  processed CHAR(1) DEFAULT 'N' COMMENT 'Scheduler processed the record?',
  added_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Added TS',
  sr_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (sr_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdi.scheduler;
-- ALTER TABLE ayso_rrdi.scheduler AUTO_INCREMENT=1;
 
-- -------------------------------- Mass Email Jobs Related      +++++++++++++++++++++
-- Build the Un-Sub List for Email Communications         =====================
/*
This table is used to store the Un-Subscribe input. The data is moved to the data
market for processing.
DO NOT JOIN with this table use the data market version.
*/
DROP TABLE IF EXISTS ayso_rrdi.un_sub;
CREATE TABLE IF NOT EXISTS ayso_rrdi.un_sub (
  email VARCHAR(60) DEFAULT NULL COMMENT 'Email Address',
  unsub_date DATE DEFAULT NULL COMMENT 'Un-Subscribe Date',
  my_cd VARCHAR(4) DEFAULT NULL COMMENT 'Membership Year Code',
  ticket VARCHAR(10) DEFAULT NULL COMMENT 'Submission ID',
  added_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Added TS',
  us_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (us_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdi.un_sub;
-- ALTER TABLE ayso_rrdi.un_sub AUTO_INCREMENT=1;
 
-- -------------------------------- Referee Scheduling Related    +++++++++++++++++++++
-- Build The Data Insert for the referee scheduling system.    =====================
/*
This table is used to store the referee data sheets. The data is moved to the data
store for processing.
DO NOT JOIN with this table use the data store cleaned version.
*/
DROP TABLE IF EXISTS ayso_rrdi.referee_data;
CREATE TABLE IF NOT EXISTS ayso_rrdi.referee_data (
  first_name VARCHAR(60) NOT NULL COMMENT 'Referee First Name',
  last_name VARCHAR(60) NOT NULL COMMENT 'Referee Last Name',
  gender_cd CHAR(1) DEFAULT NULL COMMENT 'Adult Gender Code',
  age_group VARCHAR(20) DEFAULT NULL COMMENT 'Age Grouping Above or Below 18',
  phone VARCHAR(16) DEFAULT NULL COMMENT 'Contact Phone No.',
  email VARCHAR(80) NOT NULL COMMENT 'Email Address',
  role_1 VARCHAR(60) DEFAULT NULL COMMENT 'Role/Volunteer Job 1',
  team_1 VARCHAR(60) DEFAULT NULL COMMENT 'Team Code and Team Name for Role 1',
  role_2 VARCHAR(60) DEFAULT NULL COMMENT 'Role/Volunteer Job 2',
  team_2 VARCHAR(60) DEFAULT NULL COMMENT 'Team Code and Team Name for Role 2',
  role_3 VARCHAR(60) DEFAULT NULL COMMENT 'Role/Volunteer Job 3',
  team_3 VARCHAR(60) DEFAULT NULL COMMENT 'Team Code and Team Name for Role 3',
  role_4 VARCHAR(60) DEFAULT NULL COMMENT 'Role/Volunteer Job 4',
  team_4 VARCHAR(60) DEFAULT NULL COMMENT 'Team Code and Team Name for Role 4',
  cert_level VARCHAR(10) DEFAULT NULL COMMENT 'Highest Certification',
  center_low VARCHAR(10) DEFAULT NULL COMMENT 'Lowest Centering Div Code',
  center_high VARCHAR(10) DEFAULT NULL COMMENT 'Highest Centering Div Code',
  asst_low VARCHAR(10) DEFAULT NULL COMMENT 'Lowest AR Div Code',
  asst_high VARCHAR(10) DEFAULT NULL COMMENT 'Highest AR Div Code',
  observer_friend VARCHAR(60) DEFAULT NULL COMMENT 'Observer/Assessor and Observed',
  classes VARCHAR(60) DEFAULT NULL COMMENT 'Training Classes?',
  u12_center INT(3) DEFAULT NULL COMMENT 'Count of Center U12 Experience',
  u14_center INT(3) DEFAULT NULL COMMENT 'Count of Center U14 Experience',
  center_count INT(3) DEFAULT NULL COMMENT 'Count of Center Experience',
  game_no INT(1) DEFAULT NULL COMMENT 'No. of Games per Saturday',
  absent VARCHAR(60) DEFAULT NULL COMMENT 'Saturdays Gone/Absent',
  comments VARCHAR(1000) DEFAULT NULL COMMENT 'Referee Comments',
  ticket VARCHAR(10) DEFAULT NULL COMMENT 'Submission ID',
  my_cd VARCHAR(4) NOT NULL COMMENT 'Membership Year Code',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  rd_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (rd_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrdi.referee_data;
-- ALTER TABLE ayso_rrdi.referee_data AUTO_INCREMENT=1;
 
-- $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ayso_rrds$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
-- -------------------------------- Create The Data Store      +++++++++++++++++++++
CREATE DATABASE IF NOT EXISTS ayso_rrds
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE ayso_rrds;
 
-- Define a Function Returning Numeric Values Only        =====================
/*
This function is required for many triggers and returns a varchar with only numbers.
*/
DROP FUNCTION IF EXISTS ayso_rrds.DIGITZ;
DELIMITER //
CREATE FUNCTION ayso_rrds.DIGITZ(INPUT VARCHAR(255))
   RETURNS VARCHAR(255)
BEGIN
   DECLARE output   VARCHAR(255) DEFAULT '';
   DECLARE iterator INT          DEFAULT 1;
   WHILE iterator < (LENGTH(INPUT) + 1) DO
      IF SUBSTRING(INPUT, iterator, 1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
         SET output = CONCAT(output, SUBSTRING(INPUT, iterator, 1));
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
   RETURN output;
END //
DELIMITER ;
 
-- -------------------------------- Volunteers Related        +++++++++++++++++++++
-- Setup for Data Storage Relating to Volunteers Data      =====================
/*
This table is used to store all of the region registered volunteers from eAYSO. Only
one record for a volunteer per membership year. Using the unique ID of the first,
last, email &amp; member year or the unique ID of the eAYSO identifier and member year.
The related trigger cleans up the data from the upload and places the record into
the table or deletes the old record and then places the new record.
JOIN on the eAYSO Identifier and the Membership Year Surrogate Key.
JOIN on the First Name, Last Name, Email Address, and the Member Year Surrogate Key.
NOTE: The above join can use the first three letters from the first three columns.
*/
-- DROP TABLE IF EXISTS ayso_rrds.registered_volunteer;
CREATE TABLE IF NOT EXISTS ayso_rrds.registered_volunteer (
  eayso_id INT(8) NOT NULL COMMENT 'ID No. &amp; Primary Key',
  first_name VARCHAR(40) NOT NULL COMMENT 'First Name',
  middle_name VARCHAR(40) DEFAULT NULL COMMENT 'Middle Name',
  last_name VARCHAR(40) NOT NULL COMMENT 'Last Name',
  suffix VARCHAR(40) DEFAULT NULL COMMENT 'Name Suffix',
  birth_date DATE DEFAULT NULL COMMENT 'Date of Birth',
  gender_sk INT(10) DEFAULT NULL COMMENT 'Team Gender Surrogate Key',
  street VARCHAR(60) DEFAULT NULL COMMENT 'Home Address',
  apt VARCHAR(10) DEFAULT NULL COMMENT 'Home Apt. No.',
  city VARCHAR(30) DEFAULT NULL COMMENT 'Home City',
  state_cd CHAR(2) DEFAULT NULL COMMENT 'Home State Code',
  zip VARCHAR(5) DEFAULT NULL COMMENT 'Home Zip Code',
  mail_street VARCHAR(60) DEFAULT NULL COMMENT 'Mailing Location',
  mail_apt VARCHAR(10) DEFAULT NULL COMMENT 'Mailing Apt. No.',
  mail_city VARCHAR(30) DEFAULT NULL COMMENT 'Mailing City',
  mail_state_cd CHAR(2) DEFAULT NULL COMMENT 'Mailing State Code',
  mail_zip VARCHAR(5) DEFAULT NULL COMMENT 'Mailing Zip Code',
  home_phone VARCHAR(10) DEFAULT NULL COMMENT 'Home Phone',
  work_phone VARCHAR(10) DEFAULT NULL COMMENT 'Work Phone',
  work_phone_ext VARCHAR(6) DEFAULT NULL COMMENT 'Work Phone Ext.',
  cell_phone VARCHAR(10) DEFAULT NULL COMMENT 'Cell Phone',
  email VARCHAR(60) NOT NULL COMMENT 'Email',
  my_sk INT(10) NOT NULL COMMENT 'Membership Year Surrogate Key',
  nick_name VARCHAR(40) DEFAULT NULL COMMENT 'Nick Name',
  maiden_name VARCHAR(40) DEFAULT NULL COMMENT 'Maiden Name',
  reg_date DATE DEFAULT NULL COMMENT 'Vol. Registered Date',
  change_date DATE DEFAULT NULL COMMENT 'eAYSO Record Changed Date',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  vol_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (vol_sk),
  CONSTRAINT rv_myeaysoid UNIQUE(eayso_id,my_sk),
  CONSTRAINT rv_memid UNIQUE(first_name,last_name,email,my_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrds.registered_volunteer;
-- ALTER TABLE ayso_rrds.registered_volunteer AUTO_INCREMENT=1;
DROP TRIGGER IF EXISTS ayso_rrdu.eaysovol_ai_regvol;
DELIMITER //
CREATE TRIGGER ayso_rrdu.eaysovol_ai_regvol
AFTER INSERT ON ayso_rrdu.eayso_volunteer FOR EACH ROW
BEGIN
  REPLACE INTO ayso_rrds.registered_volunteer
    (
    eayso_id,first_name,middle_name,last_name,suffix,birth_date,gender_sk,street,
    apt,city,state_cd,zip,mail_street,mail_apt,mail_city,mail_state_cd,
    mail_zip,home_phone,work_phone,work_phone_ext,cell_phone,email,my_sk,nick_name,
    maiden_name,reg_date,change_date,added_dt
    )
    VALUES
    (
      CASE
        WHEN TRIM(NEW.eayso_id) = '' OR NEW.eayso_id = NULL THEN NULL
        ELSE CAST(TRIM(NEW.eayso_id) AS UNSIGNED)
      END,
      CASE
        WHEN TRIM(NEW.first_name) = '' OR NEW.first_name = NULL THEN NULL
        ELSE TRIM(NEW.first_name)
      END,
      CASE
        WHEN TRIM(NEW.middle_name) = '' OR NEW.middle_name = NULL THEN NULL
        ELSE TRIM(NEW.middle_name)
      END,
      CASE
        WHEN TRIM(NEW.last_name) = '' OR NEW.last_name = NULL THEN NULL
        ELSE TRIM(NEW.last_name)
      END,
      CASE
        WHEN TRIM(NEW.suffix) = '' OR NEW.suffix = NULL THEN NULL
        ELSE TRIM(NEW.suffix)
      END,
      CASE
        WHEN TRIM(NEW.birth_date) = '' OR NEW.birth_date = NULL THEN NULL
        ELSE STR_TO_DATE(TRIM(NEW.birth_date),'%m/%d/%Y')
      END,
      CASE
        WHEN TRIM(NEW.gender_cd) = '' OR NEW.gender_cd = NULL THEN NULL
        ELSE (SELECT g.gender_sk FROM ayso_rrdl.gender g
            WHERE UPPER(TRIM(NEW.gender_cd)) = g.adult_gender_cd)
      END,
      CASE
        WHEN TRIM(NEW.phscl_street) = '' OR NEW.phscl_street = NULL THEN NULL
        ELSE TRIM(NEW.phscl_street)
      END,
      CASE
        WHEN TRIM(NEW.phscl_apt) = '' OR NEW.phscl_apt = NULL THEN NULL
        ELSE TRIM(NEW.phscl_apt)
      END,
      CASE
        WHEN TRIM(NEW.phscl_city) = '' OR NEW.phscl_city = NULL THEN NULL
        ELSE TRIM(NEW.phscl_city)
      END,
      CASE
        WHEN TRIM(NEW.phscl_state_cd) = '' OR NEW.phscl_state_cd = NULL THEN NULL
        ELSE TRIM(NEW.phscl_state_cd)
      END,
      CASE
        WHEN TRIM(NEW.phscl_zip) = '' OR NEW.phscl_zip = NULL THEN NULL
        ELSE DIGITZ(REPLACE(TRIM(NEW.phscl_zip),'\'',''))
      END,
      CASE
        WHEN TRIM(NEW.mail_street) = '' OR NEW.mail_street = NULL THEN 
          CASE
            WHEN TRIM(NEW.phscl_street) = '' OR NEW.phscl_street = NULL THEN NULL
            ELSE TRIM(NEW.phscl_street)
          END
        ELSE TRIM(NEW.mail_street)
      END,
      CASE
        WHEN TRIM(NEW.mail_street) = '' OR NEW.mail_street = NULL THEN
          CASE
            WHEN TRIM(NEW.phscl_apt) = '' OR NEW.phscl_apt = NULL THEN NULL
            ELSE TRIM(NEW.phscl_apt)
          END
        ELSE TRIM(NEW.mail_apt)
      END,
      CASE
        WHEN TRIM(NEW.mail_street) = '' OR NEW.mail_street = NULL THEN
          CASE
            WHEN TRIM(NEW.phscl_city) = '' OR NEW.phscl_city = NULL THEN NULL
            ELSE TRIM(NEW.phscl_city)
          END
        ELSE TRIM(NEW.mail_city)
      END,
      CASE
        WHEN TRIM(NEW.mail_street) = '' OR NEW.mail_street = NULL THEN
          CASE
            WHEN TRIM(NEW.phscl_state_cd) = '' OR NEW.phscl_state_cd = NULL THEN NULL
            ELSE TRIM(NEW.phscl_state_cd)
          END
        ELSE TRIM(NEW.mail_state_cd)
      END,
      CASE
        WHEN TRIM(NEW.mail_street) = '' OR NEW.mail_street = NULL THEN
          CASE
            WHEN TRIM(NEW.phscl_zip) = '' OR NEW.phscl_zip = NULL THEN NULL
            ELSE DIGITZ(REPLACE(TRIM(NEW.phscl_zip),'\'',''))
          END
        ELSE DIGITZ(REPLACE(TRIM(NEW.mail_zip),'\'',''))
      END,
      CASE
        WHEN TRIM(NEW.home_phone) = '' OR NEW.home_phone = NULL THEN NULL
        ELSE DIGITZ(TRIM(NEW.home_phone))
      END,
      CASE
        WHEN TRIM(NEW.work_phone) = '' OR NEW.work_phone = NULL THEN NULL
        ELSE DIGITZ(TRIM(NEW.work_phone))
      END,
      CASE
        WHEN TRIM(NEW.work_phone_ext) = '' OR NEW.work_phone_ext = NULL THEN NULL
        ELSE DIGITZ(TRIM(NEW.work_phone_ext))
      END,
      CASE
        WHEN TRIM(NEW.cell_phone) = '' OR NEW.cell_phone = NULL THEN NULL
        ELSE DIGITZ(TRIM(NEW.cell_phone))
      END,
      CASE
        WHEN TRIM(NEW.email) = '' OR NEW.email = NULL THEN NULL
        ELSE TRIM(NEW.email)
      END,
      CASE
        WHEN TRIM(NEW.my_lg_cd) = '' OR NEW.my_lg_cd = NULL THEN NULL
        ELSE (SELECT my.my_sk FROM ayso_rrdl.membership_year my
            WHERE UPPER(TRIM(NEW.my_lg_cd)) = my.my_lg_cd)
      END,
      CASE
        WHEN TRIM(NEW.nick_name) = '' OR NEW.nick_name = NULL THEN NULL
        ELSE TRIM(NEW.nick_name)
      END,
      CASE
        WHEN TRIM(NEW.maiden_name) = '' OR NEW.maiden_name = NULL THEN NULL
        ELSE TRIM(NEW.maiden_name)
      END,
      CASE
        WHEN TRIM(NEW.registered_date) = '' OR NEW.registered_date = NULL THEN NULL
        ELSE STR_TO_DATE(TRIM(NEW.registered_date),'%m/%d/%Y')
      END,
      CASE
        WHEN TRIM(NEW.changed_date) = '' OR NEW.changed_date = NULL THEN NULL
        ELSE STR_TO_DATE(TRIM(NEW.changed_date),'%m/%d/%Y')
      END,
      CURRENT_DATE
    )
  ;
END //
DELIMITER ;
 
-- -------------------------------- Team Jobs Related        +++++++++++++++++++++
-- Setup for Data Storage Relating to Team Volunteer Jobs    =====================
/*
This table is used to store all of the region volunteers from the volunteer worksheet.
Only one record for a volunteer job position for a team per membership year. Using
the unique identity of the role surrogate key, and the team surrogate key (including
the membership year distinction). The related trigger cleans up the data from the
upload and splits the original record into the table or deletes the old record(s)
and then places the new record(s) based on the roles uploaded.
FILTER on the Role Surrogate Key and the Member Year Surrogate Key.
JOIN on the Team Surrogate Key.
JOIN on the First Name, Last Name, Email Address, and the Member Year Surrogate Key.
NOTE: The above join can use the first three letters from the first three columns.
*/
-- DROP TABLE IF EXISTS ayso_rrds.volunteer_job;
CREATE TABLE IF NOT EXISTS ayso_rrds.volunteer_job (
  team_sk INT(10) NOT NULL COMMENT 'Team Surrogate Key',
  my_sk INT(10) NOT NULL COMMENT 'Membership Year Surrogate Key',
  role_sk INT(10) NOT NULL COMMENT 'Volunteer Role Surrogate Key',
  first_name VARCHAR(40) NOT NULL COMMENT 'First Name',
  last_name VARCHAR(40) NOT NULL COMMENT 'Last Name',
  phone VARCHAR(12) DEFAULT NULL COMMENT 'Best Phone',
  email VARCHAR(60) DEFAULT NULL COMMENT 'Best Email',
  ticket VARCHAR(10) DEFAULT NULL COMMENT 'Submission ID',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  vol_jobs_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (vol_jobs_sk),
  CONSTRAINT vj_teamrole UNIQUE(role_sk,team_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrds.volunteer_job;
-- ALTER TABLE ayso_rrds.volunteer_job AUTO_INCREMENT=1;
DROP TRIGGER IF EXISTS ayso_rrdi.volwork_ai_voljob;
DELIMITER //
CREATE TRIGGER ayso_rrdi.volwork_ai_voljob
AFTER INSERT ON ayso_rrdi.volunteers_worksheet FOR EACH ROW
BEGIN
-- Head Coach
  IF NEW.affected LIKE '%hc%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'hc' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.hc_first_name) = '' OR NEW.hc_first_name = NULL THEN NULL
          ELSE TRIM(NEW.hc_first_name)
        END,
        CASE
          WHEN TRIM(NEW.hc_last_name) = '' OR NEW.hc_last_name = NULL THEN NULL
          ELSE TRIM(NEW.hc_last_name)
        END,
        CASE
          WHEN TRIM(NEW.hc_phone) = '' OR NEW.hc_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.hc_phone))
        END,
        CASE
          WHEN TRIM(NEW.hc_email) = '' OR NEW.hc_email = NULL THEN NULL
          ELSE TRIM(NEW.hc_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Assistant Coach
  IF NEW.affected LIKE '%ac%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'ac' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.ac_first_name) = '' OR NEW.ac_first_name = NULL THEN NULL
          ELSE TRIM(NEW.ac_first_name)
        END,
        CASE
          WHEN TRIM(NEW.ac_last_name) = '' OR NEW.ac_last_name = NULL THEN NULL
          ELSE TRIM(NEW.ac_last_name)
        END,
        CASE
          WHEN TRIM(NEW.ac_phone) = '' OR NEW.ac_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ac_phone))
        END,
        CASE
          WHEN TRIM(NEW.ac_email) = '' OR NEW.ac_email = NULL THEN NULL
          ELSE TRIM(NEW.ac_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Referee Volunteer 1
  IF NEW.affected LIKE '%r1%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'r1' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.r1_first_name) = '' OR NEW.r1_first_name = NULL THEN NULL
          ELSE TRIM(NEW.r1_first_name)
        END,
        CASE
          WHEN TRIM(NEW.r1_last_name) = '' OR NEW.r1_last_name = NULL THEN NULL
          ELSE TRIM(NEW.r1_last_name)
        END,
        CASE
          WHEN TRIM(NEW.r1_phone) = '' OR NEW.r1_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.r1_phone))
        END,
        CASE
          WHEN TRIM(NEW.r1_email) = '' OR NEW.r1_email = NULL THEN NULL
          ELSE TRIM(NEW.r1_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Referee Volunteer 2
  IF NEW.affected LIKE '%r2%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'r2' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.r2_first_name) = '' OR NEW.r2_first_name = NULL THEN NULL
          ELSE TRIM(NEW.r2_first_name)
        END,
        CASE
          WHEN TRIM(NEW.r2_last_name) = '' OR NEW.r2_last_name = NULL THEN NULL
          ELSE TRIM(NEW.r2_last_name)
        END,
        CASE
          WHEN TRIM(NEW.r2_phone) = '' OR NEW.r2_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.r2_phone))
        END,
        CASE
          WHEN TRIM(NEW.r2_email) = '' OR NEW.r2_email = NULL THEN NULL
          ELSE TRIM(NEW.r2_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Referee Volunteer 3
  IF NEW.affected LIKE '%r3%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'r3' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.r3_first_name) = '' OR NEW.r3_first_name = NULL THEN NULL
          ELSE TRIM(NEW.r3_first_name)
        END,
        CASE
          WHEN TRIM(NEW.r3_last_name) = '' OR NEW.r3_last_name = NULL THEN NULL
          ELSE TRIM(NEW.r3_last_name)
        END,
        CASE
          WHEN TRIM(NEW.r3_phone) = '' OR NEW.r3_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.r3_phone))
        END,
        CASE
          WHEN TRIM(NEW.r3_email) = '' OR NEW.r3_email = NULL THEN NULL
          ELSE TRIM(NEW.r3_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Team Manager
  IF NEW.affected LIKE '%tm%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'tm' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.tm_first_name) = '' OR NEW.tm_first_name = NULL THEN NULL
          ELSE TRIM(NEW.tm_first_name)
        END,
        CASE
          WHEN TRIM(NEW.tm_last_name) = '' OR NEW.tm_last_name = NULL THEN NULL
          ELSE TRIM(NEW.tm_last_name)
        END,
        CASE
          WHEN TRIM(NEW.tm_phone) = '' OR NEW.tm_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.tm_phone))
        END,
        CASE
          WHEN TRIM(NEW.tm_email) = '' OR NEW.tm_email = NULL THEN NULL
          ELSE TRIM(NEW.tm_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Registration Volunteer
  IF NEW.affected LIKE '%rv%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'rv' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.rv_first_name) = '' OR NEW.rv_first_name = NULL THEN NULL
          ELSE TRIM(NEW.rv_first_name)
        END,
        CASE
          WHEN TRIM(NEW.rv_last_name) = '' OR NEW.rv_last_name = NULL THEN NULL
          ELSE TRIM(NEW.rv_last_name)
        END,
        CASE
          WHEN TRIM(NEW.rv_phone) = '' OR NEW.rv_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.rv_phone))
        END,
        CASE
          WHEN TRIM(NEW.rv_email) = '' OR NEW.rv_email = NULL THEN NULL
          ELSE TRIM(NEW.rv_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Safety Volunteer 1
  IF NEW.affected LIKE '%s1%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 's1' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.s1_first_name) = '' OR NEW.s1_first_name = NULL THEN NULL
          ELSE TRIM(NEW.s1_first_name)
        END,
        CASE
          WHEN TRIM(NEW.s1_last_name) = '' OR NEW.s1_last_name = NULL THEN NULL
          ELSE TRIM(NEW.s1_last_name)
        END,
        CASE
          WHEN TRIM(NEW.s1_phone) = '' OR NEW.s1_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.s1_phone))
        END,
        CASE
          WHEN TRIM(NEW.s1_email) = '' OR NEW.s1_email = NULL THEN NULL
          ELSE TRIM(NEW.s1_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Safety Volunteer 2
  IF NEW.affected LIKE '%s2%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 's2' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.s2_first_name) = '' OR NEW.s2_first_name = NULL THEN NULL
          ELSE TRIM(NEW.s2_first_name)
        END,
        CASE
          WHEN TRIM(NEW.s2_last_name) = '' OR NEW.s2_last_name = NULL THEN NULL
          ELSE TRIM(NEW.s2_last_name)
        END,
        CASE
          WHEN TRIM(NEW.s2_phone) = '' OR NEW.s2_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.s2_phone))
        END,
        CASE
          WHEN TRIM(NEW.s2_email) = '' OR NEW.s2_email = NULL THEN NULL
          ELSE TRIM(NEW.s2_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Concessions Volunteer 1
  IF NEW.affected LIKE '%c1%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'c1' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.c1_first_name) = '' OR NEW.c1_first_name = NULL THEN NULL
          ELSE TRIM(NEW.c1_first_name)
        END,
        CASE
          WHEN TRIM(NEW.c1_last_name) = '' OR NEW.c1_last_name = NULL THEN NULL
          ELSE TRIM(NEW.c1_last_name)
        END,
        CASE
          WHEN TRIM(NEW.c1_phone) = '' OR NEW.c1_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.c1_phone))
        END,
        CASE
          WHEN TRIM(NEW.c1_email) = '' OR NEW.c1_email = NULL THEN NULL
          ELSE TRIM(NEW.c1_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Concessions Volunteer 2
  IF NEW.affected LIKE '%c2%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'c2' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.c2_first_name) = '' OR NEW.c2_first_name = NULL THEN NULL
          ELSE TRIM(NEW.c2_first_name)
        END,
        CASE
          WHEN TRIM(NEW.c2_last_name) = '' OR NEW.c2_last_name = NULL THEN NULL
          ELSE TRIM(NEW.c2_last_name)
        END,
        CASE
          WHEN TRIM(NEW.c2_phone) = '' OR NEW.c2_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.c2_phone))
        END,
        CASE
          WHEN TRIM(NEW.c2_email) = '' OR NEW.c2_email = NULL THEN NULL
          ELSE TRIM(NEW.c2_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Fields Volunteer 1
  IF NEW.affected LIKE '%f1%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'f1' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.f1_first_name) = '' OR NEW.f1_first_name = NULL THEN NULL
          ELSE TRIM(NEW.f1_first_name)
        END,
        CASE
          WHEN TRIM(NEW.f1_last_name) = '' OR NEW.f1_last_name = NULL THEN NULL
          ELSE TRIM(NEW.f1_last_name)
        END,
        CASE
          WHEN TRIM(NEW.f1_phone) = '' OR NEW.f1_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.f1_phone))
        END,
        CASE
          WHEN TRIM(NEW.f1_email) = '' OR NEW.f1_email = NULL THEN NULL
          ELSE TRIM(NEW.f1_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Fields Volunteer 2
  IF NEW.affected LIKE '%f2%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'f2' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.f2_first_name) = '' OR NEW.f2_first_name = NULL THEN NULL
          ELSE TRIM(NEW.f2_first_name)
        END,
        CASE
          WHEN TRIM(NEW.f2_last_name) = '' OR NEW.f2_last_name = NULL THEN NULL
          ELSE TRIM(NEW.f2_last_name)
        END,
        CASE
          WHEN TRIM(NEW.f2_phone) = '' OR NEW.f2_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.f2_phone))
        END,
        CASE
          WHEN TRIM(NEW.f2_email) = '' OR NEW.f2_email = NULL THEN NULL
          ELSE TRIM(NEW.f2_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Board Member Volunteer
  IF NEW.affected LIKE '%bv%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'bv' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.bv_first_name) = '' OR NEW.bv_first_name = NULL THEN NULL
          ELSE TRIM(NEW.bv_first_name)
        END,
        CASE
          WHEN TRIM(NEW.bv_last_name) = '' OR NEW.bv_last_name = NULL THEN NULL
          ELSE TRIM(NEW.bv_last_name)
        END,
        CASE
          WHEN TRIM(NEW.bv_phone) = '' OR NEW.bv_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.bv_phone))
        END,
        CASE
          WHEN TRIM(NEW.bv_email) = '' OR NEW.bv_email = NULL THEN NULL
          ELSE TRIM(NEW.bv_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Special Events Volunteer
  IF NEW.affected LIKE '%ev%' THEN BEGIN
    REPLACE INTO ayso_rrds.volunteer_job
      (team_sk,my_sk,role_sk,first_name,last_name,phone,email,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
          WHERE 'ev' = vr.role_cd),
        CASE
          WHEN TRIM(NEW.ev_first_name) = '' OR NEW.ev_first_name = NULL THEN NULL
          ELSE TRIM(NEW.ev_first_name)
        END,
        CASE
          WHEN TRIM(NEW.ev_last_name) = '' OR NEW.ev_last_name = NULL THEN NULL
          ELSE TRIM(NEW.ev_last_name)
        END,
        CASE
          WHEN TRIM(NEW.ev_phone) = '' OR NEW.ev_phone = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ev_phone))
        END,
        CASE
          WHEN TRIM(NEW.ev_email) = '' OR NEW.ev_email = NULL THEN NULL
          ELSE TRIM(NEW.ev_email)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
END //
DELIMITER ;
 
-- -------------------------------- Volunteer Certification Related  +++++++++++++++++++++
-- Build The Data Store for Volunteer Certifications      =====================
/*
This table is used to store all of the region volunteer certifications. The cert
is bound to one eAYSO identity that could have completed the same certification
multiple times. Only registered volunteers for the year uploaded will be associated
in the same upload. Membership Year is an unnecessary piece of the constraint, but
allows for backtrack.
FILTER on the Certification and the Member Year Surrogate Key.
JOIN on the eAYSO Identifier and the Member Year Surrogate Key.
*/
-- DROP TABLE IF EXISTS ayso_rrds.volunteer_cert;
CREATE TABLE IF NOT EXISTS ayso_rrds.volunteer_cert (
  eayso_id INT(8) NOT NULL COMMENT 'eAYSO ID',
  first_name VARCHAR(60) NOT NULL COMMENT 'First Name',
  last_name VARCHAR(60) NOT NULL COMMENT 'Last Name',
  email VARCHAR(80) DEFAULT NULL COMMENT 'Email',
  cert VARCHAR(100) NOT NULL COMMENT 'Certification',
  cert_date DATE DEFAULT NULL COMMENT 'Certification Date',
  cert_my_sk INT(10) NOT NULL COMMENT 'Certification Membership Year SK',
  my_sk INT(10) NOT NULL COMMENT 'Membership Year Surrogate Key',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  vcert_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (vcert_sk),
  CONSTRAINT vc_eaysoid UNIQUE(eayso_id,cert,cert_date,my_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrds.volunteer_cert;
-- ALTER TABLE ayso_rrds.volunteer_cert AUTO_INCREMENT=1;
DROP TRIGGER IF EXISTS ayso_rrdu.eaysocert_ai_volcert;
DELIMITER //
CREATE TRIGGER ayso_rrdu.eaysocert_ai_volcert
AFTER INSERT ON ayso_rrdu.eayso_cert FOR EACH ROW
BEGIN
  REPLACE INTO ayso_rrds.volunteer_cert
    (
    eayso_id,first_name,last_name,email,cert,cert_date,cert_my_sk,my_sk,added_dt
    )
    VALUES
    (
      CASE
        WHEN TRIM(NEW.eayso_id) = '' OR NEW.eayso_id = NULL THEN NULL
        ELSE CAST(TRIM(NEW.eayso_id) AS UNSIGNED)
      END,
      CASE
        WHEN TRIM(NEW.first_name) = '' OR NEW.first_name = NULL THEN NULL
        ELSE TRIM(NEW.first_name)
      END,
      CASE
        WHEN TRIM(NEW.last_name) = '' OR NEW.last_name = NULL THEN NULL
        ELSE TRIM(NEW.last_name)
      END,
      CASE
        WHEN TRIM(NEW.email) = '' OR NEW.email = NULL THEN NULL
        ELSE TRIM(NEW.email)
      END,
      CASE
        WHEN TRIM(NEW.cert) = '' OR NEW.cert = NULL THEN NULL
        ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(NEW.cert)
          ,'Z-Online','Z')
          ,'Coach','Coa')
          ,'Referee','Ref')
          ,'Safe Haven','SH')
          ,'Intermediate','Int')
          ,'Advanced','Adv')
          ,'National','Nat')
          ,'Regional','Reg')
          ,'AYSOs','X')
          ,'Introduction','Intro')
          ,'Training','Train')
          ,'Webinar-','W-')
          ,'Course','Crs')
          ,'U-','')
          ,' ','')
      END,
      CASE
        WHEN TRIM(NEW.cert_date) = '' OR NEW.cert_date = NULL THEN NULL
        ELSE STR_TO_DATE(TRIM(NEW.cert_date),'%m/%d/%Y')
      END,
      CASE
        WHEN TRIM(NEW.my_lg_cd) = '' OR NEW.my_lg_cd = NULL THEN NULL
        ELSE (SELECT my.my_sk FROM ayso_rrdl.membership_year my
            WHERE STR_TO_DATE(TRIM(NEW.cert_date),'%m/%d/%Y')
              BETWEEN my.start_date AND my.end_date)
      END,
      CASE
        WHEN TRIM(NEW.my_lg_cd) = '' OR NEW.my_lg_cd = NULL THEN NULL
        ELSE (SELECT my.my_sk FROM ayso_rrdl.membership_year my
            WHERE UPPER(NEW.my_lg_cd) = my.my_lg_cd)
      END,
      CURRENT_DATE
    )
  ;
END //
DELIMITER ;
 
-- -------------------------------- Volunteer Certification Related  +++++++++++++++++++++
-- Setup for Grouped Data Storage Volunteer Certifications    =====================
/*
This table is used to store all of the evaluated region volunteer certifications from
eAYSO. Only one record for a volunteer per membership year. Using the unique ID of
the first, last, email &amp; member year or the unique ID of the eAYSO identifier and
member year. The related trigger script cleans up the data from the upload and
evaluates it for matching texts in the certifications for one volunteer then returns
the Yes or No answer to the certification category.
FILTER on the certification and the Member Year Surrogate Key.
JOIN on the eAYSO Identifier and the Member Year Surrogate Key.
JOIN on the First Name, Last Name, Email Address, and the Member Year Surrogate Key.
NOTE: The above join can use the first three letters from the first three columns.
*/
-- DROP TABLE IF EXISTS ayso_rrds.certifications;
CREATE TABLE IF NOT EXISTS ayso_rrds.certifications (
  eayso_id INT(8) NOT NULL COMMENT 'eAYSO ID',
  first_name VARCHAR(40) NOT NULL COMMENT 'First Name',
  last_name VARCHAR(40) NOT NULL COMMENT 'Last Name',
  email VARCHAR(60) DEFAULT NULL COMMENT 'Email',
  cu06_cert CHAR(1) DEFAULT NULL COMMENT 'Under 6 Coach Certification',
  cu08_cert CHAR(1) DEFAULT NULL COMMENT 'Under 8 Coach Certification',
  cu10_cert CHAR(1) DEFAULT NULL COMMENT 'Under 10 Coach Certification',
  cu12_cert CHAR(1) DEFAULT NULL COMMENT 'Under 12 Coach Certification',
  cint_cert CHAR(1) DEFAULT NULL COMMENT 'Intermediate Coach Certification',
  cadv_cert CHAR(1) DEFAULT NULL COMMENT 'Advanced Coach Certification',
  cnat_cert CHAR(1) DEFAULT NULL COMMENT 'National Coach Certification',
  csh_cert CHAR(1) DEFAULT NULL COMMENT 'Safe Haven Coach Certification',
  csh_date DATE DEFAULT NULL COMMENT 'Safe Haven Coach Cert Date',
  ru08_cert CHAR(1) DEFAULT NULL COMMENT 'U08 Referee Certification',
  rast_cert CHAR(1) DEFAULT NULL COMMENT 'Assistant Referee Certification',
  rreg_cert CHAR(1) DEFAULT NULL COMMENT 'Regional Referee Certification',
  rint_cert CHAR(1) DEFAULT NULL COMMENT 'Intermediate Referee Certification',
  radv_cert CHAR(1) DEFAULT NULL COMMENT 'Advanced Referee Certification',
  rnat_cert CHAR(1) DEFAULT NULL COMMENT 'National Referee Certification',
  rsh_cert CHAR(1) DEFAULT NULL COMMENT 'Safe Haven Referee Certification',
  rsh_date DATE DEFAULT NULL COMMENT 'Safe Haven Referee Cert Date',
  concuss_cert CHAR(1) DEFAULT NULL COMMENT 'Concussion Training for MY',
  my_sk INT(10) NOT NULL COMMENT 'Membership Year Surrogate Key',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  cert_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (cert_sk),
  CONSTRAINT c_memid UNIQUE(first_name,last_name,email,my_sk),
  CONSTRAINT c_aysoid UNIQUE(eayso_id,my_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrds.certifications;
-- ALTER TABLE ayso_rrds.certifications AUTO_INCREMENT=1;
 
-- -------------------------------- Team Formation Related      +++++++++++++++++++++
-- Setup for Data Storage Relating to Team Formation Cards    =====================
/*
This table is used to store all of the Player Cards submitted for team formation.
The table allows for some duplicates, and is not reliant on the applicants ability to
enter a correct eAYSO Identifier. It does have a unique identity with Player First
Name, Player Last Name, Parent 1 Email Address, and the Member Year Surrogate Key.
FILTER on the Division or Gender, and the Member Year Surrogate Key.
JOIN on the eAYSO Identifier and the Member Year Surrogate Key. {Errors Warning}
JOIN on the First Name, Last Name, Email Address, and the Member Year Surrogate Key.
NOTE: The above join can use the first three letters from the first three columns.
*/
-- DROP TABLE IF EXISTS ayso_rrds.player_card;
CREATE TABLE IF NOT EXISTS ayso_rrds.player_card (
  player_first_name VARCHAR(40) NOT NULL COMMENT 'Player First Name',
  player_last_name VARCHAR(40) NOT NULL COMMENT 'Player Last Name',
  eayso_id INT(8) NOT NULL COMMENT 'Player eAYSO ID',
  player_contact_phone VARCHAR(10) DEFAULT NULL COMMENT 'Player Contact Phone',
  player_dob DATE DEFAULT NULL COMMENT 'Player Date of Birth',
  player_age DECIMAL(2,2) DEFAULT NULL COMMENT 'Player Age',
  player_division_sk INT(10) DEFAULT NULL COMMENT 'Player Division',
  player_school VARCHAR(20) DEFAULT NULL COMMENT 'Player Current or Future School',
  player_gender_sk INT(10) DEFAULT NULL COMMENT 'Player Gender Surrogate Key',
  player_street VARCHAR(60) DEFAULT NULL COMMENT 'Player Street Address',
  player_city VARCHAR(20) DEFAULT NULL COMMENT 'Player City',
  player_height INT(3) DEFAULT NULL COMMENT 'Player Height in Inches',
  player_weight INT(3) DEFAULT NULL COMMENT 'Player Weight in Pounds',
  player_practice1 VARCHAR(20) DEFAULT NULL COMMENT 'Player Practice Zone Choice 1',
  player_practice2 VARCHAR(20) DEFAULT NULL COMMENT 'Player Practice Zone Choice 2',
  player_yrec INT(2) DEFAULT NULL COMMENT 'Player Years of Rec Soccer',
  player_yclub INT(2) DEFAULT NULL COMMENT 'Player Years of Club Soccer',
  player_request VARCHAR(100) DEFAULT NULL COMMENT 'Player Special Requests',
  parent1_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Primary Parent First Name',
  parent1_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Primary Parent Last Name',
  parent1_phone VARCHAR(12) DEFAULT NULL COMMENT 'Primary Parent Phone',
  parent1_email VARCHAR(80) NOT NULL COMMENT 'Primary Parent Email',
  parent2_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Secondary Parent First Name',
  parent2_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Secondary Parent Last Name',
  parent2_phone VARCHAR(12) DEFAULT NULL COMMENT 'Secondary Parent Phone',
  parent2_email VARCHAR(80) DEFAULT NULL COMMENT 'Secondary Parent Email',
  parent3_first_name VARCHAR(40) DEFAULT NULL COMMENT 'Other Parent First Name',
  parent3_last_name VARCHAR(40) DEFAULT NULL COMMENT 'Other Parent Last Name',
  parent3_phone VARCHAR(12) DEFAULT NULL COMMENT 'Other Parent Phone',
  parent3_email VARCHAR(80) DEFAULT NULL COMMENT 'Other Parent Email',
  volunteer_select VARCHAR(20) DEFAULT NULL COMMENT 'Volunteering Parent',
  volunteer_choice1 VARCHAR(20) DEFAULT NULL COMMENT 'Chosen Volunteer Position 1',
  volunteer_choice2 VARCHAR(20) DEFAULT NULL COMMENT 'Chosen Volunteer Position 2',
  ticket VARCHAR(10) DEFAULT NULL COMMENT 'Ticket for Record',
  my_sk INT(10) NOT NULL COMMENT 'Membership Year Surrogate Key',
  eayso_file VARCHAR(1000) DEFAULT NULL COMMENT 'URL of eAYSO Registration',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  pccln_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (pccln_sk),
  CONSTRAINT pcc_memid UNIQUE(player_first_name,player_last_name,parent1_email,my_sk)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrds.player_card;
-- ALTER TABLE ayso_rrds.player_card AUTO_INCREMENT=1;
DROP TRIGGER IF EXISTS ayso_rrdi.playercard_ai_pcclean;
DELIMITER //
CREATE TRIGGER ayso_rrdi.playercard_ai_pcclean
AFTER INSERT ON ayso_rrdi.player_card FOR EACH ROW
BEGIN
  REPLACE INTO ayso_rrds.player_card
    (
    player_first_name,player_last_name,eayso_id,player_contact_phone,player_dob,
    player_age,player_division_sk,player_school,player_gender_sk,player_street,
    player_city,player_height,player_weight,player_practice1,player_practice2,
    player_yrec,player_yclub,player_request,parent1_first_name,parent1_last_name,
    parent1_phone,parent1_email,parent2_first_name,parent2_last_name,parent2_phone,
    parent2_email,parent3_first_name,parent3_last_name,parent3_phone,parent3_email,
    volunteer_select,volunteer_choice1,volunteer_choice2,ticket,my_sk,eayso_file,
    added_dt
    )
    VALUES
    (
      CASE
        WHEN TRIM(NEW.player_first_name) = '' OR NEW.player_first_name = NULL THEN NULL
        ELSE TRIM(NEW.player_first_name)
      END,
      CASE
        WHEN TRIM(NEW.player_last_name) = '' OR NEW.player_last_name = NULL THEN NULL
        ELSE TRIM(NEW.player_last_name)
      END,
      CASE
        WHEN TRIM(NEW.eayso_id) = '' OR NEW.eayso_id = NULL THEN NULL
        ELSE CAST(TRIM(NEW.eayso_id) AS UNSIGNED)
      END,
      CASE
        WHEN TRIM(NEW.player_contact_phone) = '' OR NEW.player_contact_phone = NULL THEN NULL
        ELSE DIGITZ(TRIM(NEW.player_contact_phone))
      END,
      CASE
        WHEN TRIM(NEW.player_dob) = '' OR NEW.player_dob = NULL THEN NULL
        ELSE STR_TO_DATE(TRIM(NEW.player_dob),'%m/%d/%Y')
      END,
      CASE
        WHEN TRIM(NEW.player_age) = '' OR NEW.player_age = NULL THEN NULL
        ELSE CAST(TRIM(NEW.player_age) AS DECIMAL(2,2))
      END,
      CASE
        WHEN TRIM(NEW.player_division_cd) = '' OR NEW.player_division_cd = NULL THEN NULL
        ELSE (SELECT d.division_sk FROM ayso_rrdl.division d
            WHERE LEFT(UPPER(TRIM(NEW.player_division_cd)),3) = d.division_cd)
      END,
      CASE
        WHEN TRIM(NEW.player_school) = '' OR NEW.player_school = NULL THEN NULL
        ELSE TRIM(NEW.player_school)
      END,
      CASE
        WHEN TRIM(NEW.player_gender_cd) = '' OR NEW.player_gender_cd = NULL THEN NULL
        ELSE (SELECT g.gender_sk FROM ayso_rrdl.gender g
            WHERE UPPER(TRIM(NEW.player_gender_cd)) = g.kid_gender_cd)
      END,
      CASE
        WHEN TRIM(NEW.player_street) = '' OR NEW.player_street = NULL THEN NULL
        ELSE TRIM(NEW.player_street)
      END,
      CASE
        WHEN TRIM(NEW.player_city) = '' OR NEW.player_city = NULL THEN NULL
        ELSE TRIM(NEW.player_city)
      END,
      CASE
        WHEN TRIM(NEW.player_height) = '' OR NEW.player_height = NULL THEN NULL
        ELSE NEW.player_height
      END,
      CASE
        WHEN TRIM(NEW.player_weight) = '' OR NEW.player_weight = NULL THEN NULL
        ELSE NEW.player_weight
      END,
      CASE
        WHEN TRIM(NEW.player_practice1) = '' OR NEW.player_practice1 = NULL THEN NULL
        ELSE TRIM(LEFT(NEW.player_practice1,2))
      END,
      CASE
        WHEN TRIM(NEW.player_practice2) = '' OR NEW.player_practice2 = NULL THEN NULL
        ELSE TRIM(LEFT(NEW.player_practice2,2))
      END,
      CASE
        WHEN TRIM(NEW.player_yrec) = '' OR NEW.player_yrec = NULL THEN NULL
        ELSE TRIM(NEW.player_yrec)
      END,
      CASE
        WHEN TRIM(NEW.player_yclub) = '' OR NEW.player_yclub = NULL THEN NULL
        ELSE TRIM(NEW.player_yclub)
      END,
      CASE
        WHEN TRIM(NEW.player_request) = '' OR NEW.player_request = NULL THEN NULL
        ELSE TRIM(NEW.player_request)
      END,
      CASE
        WHEN TRIM(NEW.parent1_first_name) = '' OR NEW.parent1_first_name = NULL THEN NULL
        ELSE TRIM(NEW.parent1_first_name)
      END,
      CASE
        WHEN TRIM(NEW.parent1_last_name) = '' OR NEW.parent1_last_name = NULL THEN NULL
        ELSE TRIM(NEW.parent1_last_name)
      END,
      CASE
        WHEN TRIM(NEW.parent1_phone) = '' OR NEW.parent1_phone = NULL THEN NULL
        ELSE DIGITZ(TRIM(NEW.parent1_phone))
      END,
      CASE
        WHEN TRIM(NEW.parent1_email) = '' OR NEW.parent1_email = NULL THEN NULL
        ELSE TRIM(NEW.parent1_email)
      END,
      CASE
        WHEN TRIM(NEW.parent2_first_name) = '' OR NEW.parent2_first_name = NULL THEN NULL
        ELSE TRIM(NEW.parent2_first_name)
      END,
      CASE
        WHEN TRIM(NEW.parent2_last_name) = '' OR NEW.parent2_last_name = NULL THEN NULL
        ELSE TRIM(NEW.parent2_last_name)
      END,
      CASE
        WHEN TRIM(NEW.parent2_phone) = '' OR NEW.parent2_phone = NULL THEN NULL
        ELSE DIGITZ(TRIM(NEW.parent2_phone))
      END,
      CASE
        WHEN TRIM(NEW.parent2_email) = '' OR NEW.parent2_email = NULL THEN NULL
        ELSE TRIM(NEW.parent2_email)
      END,
      CASE
        WHEN TRIM(NEW.parent3_first_name) = '' OR NEW.parent3_first_name = NULL THEN NULL
        ELSE TRIM(NEW.parent3_first_name)
      END,
      CASE
        WHEN TRIM(NEW.parent3_last_name) = '' OR NEW.parent3_last_name = NULL THEN NULL
        ELSE TRIM(NEW.parent3_last_name)
      END,
      CASE
        WHEN TRIM(NEW.parent3_phone) = '' OR NEW.parent3_phone = NULL THEN NULL
        ELSE DIGITZ(TRIM(NEW.parent3_phone))
      END,
      CASE
        WHEN TRIM(NEW.parent3_email) = '' OR NEW.parent3_email = NULL THEN NULL
        ELSE TRIM(NEW.parent3_email)
      END,
      CASE
        WHEN TRIM(NEW.volunteer_select) = '' OR NEW.volunteer_select = NULL THEN NULL
        ELSE TRIM(NEW.volunteer_select)
      END,
      CASE
        WHEN TRIM(NEW.volunteer_choice1) = '' OR NEW.volunteer_choice1 = NULL THEN NULL
        ELSE (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
            WHERE TRIM(LEFT(NEW.volunteer_choice1,3)) = LEFT(vr.role_cat,3))
      END,
      CASE
        WHEN TRIM(NEW.volunteer_choice2) = '' OR NEW.volunteer_choice2 = NULL THEN NULL
        ELSE (SELECT vr.role_sk FROM ayso_rrdl.volunteer_role vr
            WHERE TRIM(LEFT(NEW.volunteer_choice2,3)) = LEFT(vr.role_cat,3))
      END,
      CASE
        WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
        ELSE TRIM(NEW.ticket)
      END,
      CASE
        WHEN TRIM(NEW.my_cd) = '' OR NEW.my_cd = NULL THEN NULL
        ELSE (SELECT my.my_sk FROM ayso_rrdl.membership_year my
            WHERE UPPER(TRIM(NEW.my_cd)) = my.my_cd)
      END,
      CASE
        WHEN TRIM(NEW.eayso_file) = '' OR NEW.eayso_file = NULL THEN NULL
        ELSE TRIM(NEW.eayso_file)
      END,
      CURRENT_DATE
    )
  ;
END //
DELIMITER ;
 
-- -------------------------------- Team Formation Related      +++++++++++++++++++++
-- Setup for Data Storage Relating to Team Rosters        =====================
/*
This table is used to store all of the Team Roster data from eAYSO. The table allows
for one player with one team in one membership year only. A player can be on multiple
teams, but should only show once for a specific team.
FILTER on the Team Surrogate Key.
JOIN on the eAYSO Identifier and the Member Year Surrogate Key. {Duplicates}
JOIN on the Team Surrogate Key. {Duplicates}
*/
-- DROP TABLE IF EXISTS ayso_rrds.team_roster;
CREATE TABLE IF NOT EXISTS ayso_rrds.team_roster (
  team_sk INT(10) NOT NULL COMMENT 'Team Surrogate Key',
  hc_eayso_id INT(8) DEFAULT NULL COMMENT 'Head Coach eAYSO ID',
  ac_eayso_id INT(8) DEFAULT NULL COMMENT 'Asst. Coach eAYSO ID',
  tm_eayso_id INT(8) DEFAULT NULL COMMENT 'Team Manager eAYSO ID',
  player_eayso_id INT(8) NOT NULL COMMENT 'Player eAYSO ID',
  my_sk INT(10) NOT NULL COMMENT 'Membership Year Surrogate Key',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  tr_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (tr_sk),
  CONSTRAINT et_teammemid UNIQUE(team_sk,player_eayso_id)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrds.team_roster;
-- ALTER TABLE ayso_rrds.team_roster AUTO_INCREMENT=1;
DROP TRIGGER IF EXISTS ayso_rrdu.eaysoteam_ai_troster;
DELIMITER //
CREATE TRIGGER ayso_rrdu.eaysoteam_ai_troster
AFTER INSERT ON ayso_rrdu.eayso_team FOR EACH ROW
BEGIN
  REPLACE INTO ayso_rrds.team_roster
    (
    team_sk,hc_eayso_id,ac_eayso_id,tm_eayso_id,player_eayso_id,my_sk,added_dt
    )
    VALUES
    (
      CASE
        WHEN TRIM(NEW.team_cd) = '' OR NEW.team_cd = NULL THEN NULL
        ELSE (SELECT t.team_sk FROM ayso_rrdl.team t
            WHERE NEW.team_cd = t.team_cd AND 
            CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk)
      END,
      CASE
        WHEN TRIM(NEW.hc_eayso_id) = '' OR NEW.hc_eayso_id = NULL THEN NULL
        ELSE CAST(DIGITZ(TRIM(NEW.hc_eayso_id)) AS UNSIGNED)
      END,
      CASE
        WHEN TRIM(NEW.ac_eayso_id) = '' OR NEW.ac_eayso_id = NULL THEN NULL
        ELSE CAST(DIGITZ(TRIM(NEW.ac_eayso_id)) AS UNSIGNED)
      END,
      CASE
        WHEN TRIM(NEW.tm_eayso_id) = '' OR NEW.tm_eayso_id = NULL THEN NULL
        ELSE CAST(DIGITZ(TRIM(NEW.tm_eayso_id)) AS UNSIGNED)
      END,
      CASE
        WHEN TRIM(NEW.player_eayso_id) = '' OR NEW.player_eayso_id = NULL THEN NULL
        ELSE CAST(DIGITZ(TRIM(NEW.player_eayso_id)) AS UNSIGNED)
      END,
      CASE
        WHEN TRIM(NEW.my_cd) = '' OR NEW.my_cd = NULL THEN NULL
        ELSE (SELECT my.my_sk FROM ayso_rrdl.membership_year my
            WHERE UPPER(NEW.my_cd) = my.my_cd)
      END,
      CURRENT_DATE
    )
  ;
END //
DELIMITER ;
 
-- -------------------------------- Team Formation Related      +++++++++++++++++++++
-- Setup for Data Storage Relating to Player Evaluations    =====================
/*
This table is used to store all of the Player Evaluation data from input. A player
can have multiple evaluations, but only one per input. The trigger is setup to use the
roster count in order to split one insert into many records, one for each player.
FILTER on the Team Surrogate Key.
JOIN on the eAYSO Identifier and the Member Year Surrogate Key. {Duplicates}
JOIN on the Team Surrogate Key. {Duplicates}
*/
-- DROP TABLE IF EXISTS ayso_rrds.player_eval;
CREATE TABLE IF NOT EXISTS ayso_rrds.player_eval (
  team_sk INT(10) NOT NULL COMMENT 'Team Surrogate Key',
  eayso_id INT(8) NOT NULL COMMENT 'Player eAYSO ID',
  player_eval INT(2) DEFAULT NULL COMMENT 'Evaluation Score',
  player_comment VARCHAR(200) DEFAULT NULL COMMENT 'Comment on Player',
  my_sk INT(10) NOT NULL COMMENT 'Membership Year Surrogate Key',
  ticket VARCHAR(8) DEFAULT NULL COMMENT 'Submission Ticket',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  player_eval_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (player_eval_sk),
  CONSTRAINT pe_evalmemid UNIQUE(team_sk,eayso_id,ticket)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrds.player_eval;
-- ALTER TABLE ayso_rrds.player_eval AUTO_INCREMENT=1;
 
-- Insert trigger for player evals                ====================
DROP TRIGGER IF EXISTS ayso_rrdi.teval_ai_peval;
DELIMITER //
CREATE TRIGGER ayso_rrdi.teval_ai_peval
AFTER INSERT ON ayso_rrdi.team_evaluation FOR EACH ROW
BEGIN
-- Players 1-4
  IF NEW.player_count = 4 OR NEW.player_count = 5 OR NEW.player_count = 6
  OR NEW.player_count = 7 OR NEW.player_count = 8 OR NEW.player_count = 9
  OR NEW.player_count = 10 OR NEW.player_count = 11 OR NEW.player_count = 12
  OR NEW.player_count = 13 OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p1_eayso_id) = '' OR NEW.p1_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p1_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p1_eval) = '' OR NEW.p1_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p1_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p1_comment) = '' OR NEW.p1_comment = NULL THEN NULL
          ELSE TRIM(NEW.p1_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p2_eayso_id) = '' OR NEW.p2_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p2_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p2_eval) = '' OR NEW.p2_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p2_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p2_comment) = '' OR NEW.p2_comment = NULL THEN NULL
          ELSE TRIM(NEW.p2_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p3_eayso_id) = '' OR NEW.p3_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p3_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p3_eval) = '' OR NEW.p3_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p3_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p3_comment) = '' OR NEW.p3_comment = NULL THEN NULL
          ELSE TRIM(NEW.p3_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p4_eayso_id) = '' OR NEW.p4_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p4_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p4_eval) = '' OR NEW.p4_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p4_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p4_comment) = '' OR NEW.p4_comment = NULL THEN NULL
          ELSE TRIM(NEW.p4_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 5
  IF NEW.player_count = 5 OR NEW.player_count = 6 OR NEW.player_count = 7
  OR NEW.player_count = 8 OR NEW.player_count = 9 OR NEW.player_count = 10
  OR NEW.player_count = 11 OR NEW.player_count = 12 OR NEW.player_count = 13
  OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p5_eayso_id) = '' OR NEW.p5_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p5_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p5_eval) = '' OR NEW.p5_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p5_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p5_comment) = '' OR NEW.p5_comment = NULL THEN NULL
          ELSE TRIM(NEW.p5_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 6
  IF NEW.player_count = 6 OR NEW.player_count = 7 OR NEW.player_count = 8
  OR NEW.player_count = 9 OR NEW.player_count = 10 OR NEW.player_count = 11
  OR NEW.player_count = 12 OR NEW.player_count = 13 OR NEW.player_count = 14
  OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p6_eayso_id) = '' OR NEW.p6_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p6_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p6_eval) = '' OR NEW.p6_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p6_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p6_comment) = '' OR NEW.p6_comment = NULL THEN NULL
          ELSE TRIM(NEW.p6_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 7
  IF NEW.player_count = 7 OR NEW.player_count = 8 OR NEW.player_count = 9 OR
  NEW.player_count = 10 OR NEW.player_count = 11 OR NEW.player_count = 12 OR
  NEW.player_count = 13 OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p7_eayso_id) = '' OR NEW.p7_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p7_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p7_eval) = '' OR NEW.p7_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p7_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p7_comment) = '' OR NEW.p7_comment = NULL THEN NULL
          ELSE TRIM(NEW.p7_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 8
  IF NEW.player_count = 8 OR NEW.player_count = 9 OR NEW.player_count = 10
  OR NEW.player_count = 11 OR NEW.player_count = 12 OR NEW.player_count = 13
  OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p8_eayso_id) = '' OR NEW.p8_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p8_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p8_eval) = '' OR NEW.p8_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p8_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p8_comment) = '' OR NEW.p8_comment = NULL THEN NULL
          ELSE TRIM(NEW.p8_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 9
  IF NEW.player_count = 9 OR NEW.player_count = 10 OR NEW.player_count = 11
  OR NEW.player_count = 12 OR NEW.player_count = 13 OR NEW.player_count = 14
  OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p9_eayso_id) = '' OR NEW.p9_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p9_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p9_eval) = '' OR NEW.p9_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p9_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p9_comment) = '' OR NEW.p9_comment = NULL THEN NULL
          ELSE TRIM(NEW.p9_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 10
  IF NEW.player_count = 10 OR NEW.player_count = 11 OR NEW.player_count = 12
  OR NEW.player_count = 13 OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p10_eayso_id) = '' OR NEW.p10_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p10_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p10_eval) = '' OR NEW.p10_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p10_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p10_comment) = '' OR NEW.p10_comment = NULL THEN NULL
          ELSE TRIM(NEW.p10_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 11
  IF NEW.player_count = 11 OR NEW.player_count = 12 OR NEW.player_count = 13
  OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p11_eayso_id) = '' OR NEW.p11_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p11_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p11_eval) = '' OR NEW.p11_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p11_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p11_comment) = '' OR NEW.p11_comment = NULL THEN NULL
          ELSE TRIM(NEW.p11_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 12
  IF NEW.player_count = 12 OR NEW.player_count = 13 OR NEW.player_count = 14
  OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p12_eayso_id) = '' OR NEW.p12_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p12_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p12_eval) = '' OR NEW.p12_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p12_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p12_comment) = '' OR NEW.p12_comment = NULL THEN NULL
          ELSE TRIM(NEW.p12_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 13
  IF NEW.player_count = 13 OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p13_eayso_id) = '' OR NEW.p13_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p13_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p13_eval) = '' OR NEW.p13_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p13_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p13_comment) = '' OR NEW.p13_comment = NULL THEN NULL
          ELSE TRIM(NEW.p13_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 14
  IF NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p14_eayso_id) = '' OR NEW.p14_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p14_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p14_eval) = '' OR NEW.p14_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p14_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p14_comment) = '' OR NEW.p14_comment = NULL THEN NULL
          ELSE TRIM(NEW.p14_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 15
  IF NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_eval
      (team_sk,my_sk,eayso_id,player_eval,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p15_eayso_id) = '' OR NEW.p15_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p15_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p15_eval) = '' OR NEW.p15_eval = NULL THEN NULL
          ELSE CAST(DIGITZ(TRIM(NEW.p15_eval)) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p15_comment) = '' OR NEW.p15_comment = NULL THEN NULL
          ELSE TRIM(NEW.p15_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
END //
DELIMITER ;
 
-- -------------------------------- Team Formation Related      +++++++++++++++++++++
-- Setup for Data Storage Relating to Player Drops        =====================
/*
This table is used to store all of the Player Drop data from input. A player can have
one result for this table per membership year and team. The trigger is setup to use the
roster count in order to split one insert into many records, one for each player.
FILTER on the Team Surrogate Key.
JOIN on the eAYSO Identifier and the Member Year Surrogate Key. {Duplicates}
JOIN on the Team Surrogate Key. {Duplicates}
*/
-- DROP TABLE IF EXISTS ayso_rrds.player_drop;
CREATE TABLE IF NOT EXISTS ayso_rrds.player_drop (
  team_sk INT(10) NOT NULL COMMENT 'Team Surrogate Key',
  eayso_id INT(8) NOT NULL COMMENT 'Player eAYSO ID',
  player_path CHAR(1) DEFAULT NULL COMMENT 'Chosen Path',
  player_comment VARCHAR(200) DEFAULT NULL COMMENT 'Comment on Player',
  my_sk INT(10) NOT NULL COMMENT 'Membership Year Surrogate Key',
  ticket VARCHAR(8) DEFAULT NULL COMMENT 'Submission Ticket',
  added_dt DATE DEFAULT NULL COMMENT 'Added Date',
  alter_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Altered TS',
  player_drop_sk INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Automatic Surrogate Key',
  PRIMARY KEY (player_drop_sk),
  CONSTRAINT pd_dropmemid UNIQUE(team_sk,eayso_id)
) DEFAULT CHARSET=utf8;
-- TRUNCATE TABLE ayso_rrds.player_drop;
-- ALTER TABLE ayso_rrds.player_drop AUTO_INCREMENT=1;
DROP TRIGGER IF EXISTS ayso_rrdi.tdrops_ai_pdrops;
DELIMITER //
CREATE TRIGGER ayso_rrdi.tdrops_ai_pdrops
AFTER INSERT ON ayso_rrdi.team_roster_return FOR EACH ROW
BEGIN
-- Players 1-4
  IF NEW.player_count = 4 OR NEW.player_count = 5 OR NEW.player_count = 6
  OR NEW.player_count = 7 OR NEW.player_count = 8 OR NEW.player_count = 9
  OR NEW.player_count = 10 OR NEW.player_count = 11 OR NEW.player_count = 12
  OR NEW.player_count = 13 OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p1_eayso_id) = '' OR NEW.p1_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p1_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p1_path) = '' OR NEW.p1_path = NULL THEN NULL
          ELSE TRIM(NEW.p1_path)
        END,
        CASE
          WHEN TRIM(NEW.p1_comment) = '' OR NEW.p1_comment = NULL THEN NULL
          ELSE TRIM(NEW.p1_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p2_eayso_id) = '' OR NEW.p2_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p2_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p2_path) = '' OR NEW.p2_path = NULL THEN NULL
          ELSE TRIM(NEW.p2_path)
        END,
        CASE
          WHEN TRIM(NEW.p2_comment) = '' OR NEW.p2_comment = NULL THEN NULL
          ELSE TRIM(NEW.p2_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p3_eayso_id) = '' OR NEW.p3_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p3_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p3_path) = '' OR NEW.p3_path = NULL THEN NULL
          ELSE TRIM(NEW.p3_path)
        END,
        CASE
          WHEN TRIM(NEW.p3_comment) = '' OR NEW.p3_comment = NULL THEN NULL
          ELSE TRIM(NEW.p3_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p4_eayso_id) = '' OR NEW.p4_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p4_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p4_path) = '' OR NEW.p4_path = NULL THEN NULL
          ELSE TRIM(NEW.p4_path)
        END,
        CASE
          WHEN TRIM(NEW.p4_comment) = '' OR NEW.p4_comment = NULL THEN NULL
          ELSE TRIM(NEW.p4_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 5
  IF NEW.player_count = 5 OR NEW.player_count = 6 OR NEW.player_count = 7 
  OR NEW.player_count = 8 OR NEW.player_count = 9 OR NEW.player_count = 10
  OR NEW.player_count = 11 OR NEW.player_count = 12 OR NEW.player_count = 13
  OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p5_eayso_id) = '' OR NEW.p5_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p5_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p5_path) = '' OR NEW.p5_path = NULL THEN NULL
          ELSE TRIM(NEW.p5_path)
        END,
        CASE
          WHEN TRIM(NEW.p5_comment) = '' OR NEW.p5_comment = NULL THEN NULL
          ELSE TRIM(NEW.p5_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 6
  IF NEW.player_count = 6 OR NEW.player_count = 7 OR NEW.player_count = 8
  OR NEW.player_count = 9 OR NEW.player_count = 10 OR NEW.player_count = 11 
  OR NEW.player_count = 12 OR NEW.player_count = 13 OR NEW.player_count = 14
  OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p6_eayso_id) = '' OR NEW.p6_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p6_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p6_path) = '' OR NEW.p6_path = NULL THEN NULL
          ELSE TRIM(NEW.p6_path)
        END,
        CASE
          WHEN TRIM(NEW.p6_comment) = '' OR NEW.p6_comment = NULL THEN NULL
          ELSE TRIM(NEW.p6_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 7
  IF NEW.player_count = 7 OR NEW.player_count = 8 OR NEW.player_count = 9 OR
  NEW.player_count = 10 OR NEW.player_count = 11 OR NEW.player_count = 12 OR
  NEW.player_count = 13 OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p7_eayso_id) = '' OR NEW.p7_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p7_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p7_path) = '' OR NEW.p7_path = NULL THEN NULL
          ELSE TRIM(NEW.p7_path)
        END,
        CASE
          WHEN TRIM(NEW.p7_comment) = '' OR NEW.p7_comment = NULL THEN NULL
          ELSE TRIM(NEW.p7_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 8
  IF NEW.player_count = 8 OR NEW.player_count = 9 OR NEW.player_count = 10
  OR NEW.player_count = 11 OR NEW.player_count = 12 OR NEW.player_count = 13
  OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p8_eayso_id) = '' OR NEW.p8_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p8_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p8_path) = '' OR NEW.p8_path = NULL THEN NULL
          ELSE TRIM(NEW.p8_path)
        END,
        CASE
          WHEN TRIM(NEW.p8_comment) = '' OR NEW.p8_comment = NULL THEN NULL
          ELSE TRIM(NEW.p8_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 9
  IF NEW.player_count = 9 OR NEW.player_count = 10 OR NEW.player_count = 11
  OR NEW.player_count = 12 OR NEW.player_count = 13 OR NEW.player_count = 14
  OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p9_eayso_id) = '' OR NEW.p9_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p9_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p9_path) = '' OR NEW.p9_path = NULL THEN NULL
          ELSE TRIM(NEW.p9_path)
        END,
        CASE
          WHEN TRIM(NEW.p9_comment) = '' OR NEW.p9_comment = NULL THEN NULL
          ELSE TRIM(NEW.p9_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 10
  IF NEW.player_count = 10 OR NEW.player_count = 11 OR NEW.player_count = 12
  OR NEW.player_count = 13 OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p10_eayso_id) = '' OR NEW.p10_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p10_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p10_path) = '' OR NEW.p10_path = NULL THEN NULL
          ELSE TRIM(NEW.p10_path)
        END,
        CASE
          WHEN TRIM(NEW.p10_comment) = '' OR NEW.p10_comment = NULL THEN NULL
          ELSE TRIM(NEW.p10_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 11
  IF NEW.player_count = 11 OR NEW.player_count = 12 OR NEW.player_count = 13
  OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p11_eayso_id) = '' OR NEW.p11_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p11_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p11_path) = '' OR NEW.p11_path = NULL THEN NULL
          ELSE TRIM(NEW.p11_path)
        END,
        CASE
          WHEN TRIM(NEW.p11_comment) = '' OR NEW.p11_comment = NULL THEN NULL
          ELSE TRIM(NEW.p11_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 12
  IF NEW.player_count = 12 OR NEW.player_count = 13 OR NEW.player_count = 14
  OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p12_eayso_id) = '' OR NEW.p12_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p12_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p12_path) = '' OR NEW.p12_path = NULL THEN NULL
          ELSE TRIM(NEW.p12_path)
        END,
        CASE
          WHEN TRIM(NEW.p12_comment) = '' OR NEW.p12_comment = NULL THEN NULL
          ELSE TRIM(NEW.p12_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 13
  IF NEW.player_count = 13 OR NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd) = my.my_cd),
        CASE
          WHEN TRIM(NEW.p13_eayso_id) = '' OR NEW.p13_eayso_id = NULL THEN NULL
          ELSE CAST(TRIM(NEW.p13_eayso_id) AS UNSIGNED)
        END,
        CASE
          WHEN TRIM(NEW.p13_path) = '' OR NEW.p13_path = NULL THEN NULL
          ELSE TRIM(NEW.p13_path)
        END,
        CASE
          WHEN TRIM(NEW.p13_comment) = '' OR NEW.p13_comment = NULL THEN NULL
          ELSE TRIM(NEW.p13_comment)
        END,
        CASE
          WHEN TRIM(NEW.ticket) = '' OR NEW.ticket = NULL THEN NULL
          ELSE DIGITZ(TRIM(NEW.ticket))
        END,
        CURRENT_DATE
      )
    ;
  END;
  END IF;
-- Player 14
  IF NEW.player_count = 14 OR NEW.player_count = 15
  THEN BEGIN
    REPLACE INTO ayso_rrds.player_drop
      (team_sk,my_sk,eayso_id,player_path,player_comment,ticket,added_dt)
      VALUES
      (
        (SELECT t.team_sk FROM ayso_rrdl.team t
          WHERE UPPER(LEFT(NEW.team_cd,6)) = t.team_cd AND 
          CAST(RIGHT(TRIM(NEW.my_cd),2) AS UNSIGNED) = t.my_sk),
        (SELECT my.my_sk FROM ayso_rrdl.membership_year my
          WHERE UPPER(NEW.my_cd