--
-- Host: foo    Database: foo
-- ------------------------------------------------------


--
-- Table structure for table `2015_iec_lku_income_limit`
--

CREATE TABLE `2015_iec_lku_income_limit` (
  `income_limit_id` int(11) NOT NULL AUTO_INCREMENT,
  `income_limit_year` int(10) unsigned DEFAULT NULL,
  `state_id` int(11) DEFAULT NULL,
  `cbsa_sub` varchar(32) DEFAULT NULL,
  `area` varchar(128) DEFAULT NULL,
  `lim30_p1` int(11) DEFAULT NULL,
  `lim30_p2` int(11) DEFAULT NULL,
  `lim30_p3` int(11) DEFAULT NULL,
  `lim30_p4` int(11) DEFAULT NULL,
  `lim30_p5` int(11) DEFAULT NULL,
  `lim30_p6` int(11) DEFAULT NULL,
  `lim30_p7` int(11) DEFAULT NULL,
  `lim30_p8` int(11) DEFAULT NULL,
  `lim30_p9` int(11) DEFAULT NULL,
  `lim30_p10` int(11) DEFAULT NULL,
  `lim30_p11` int(11) DEFAULT NULL,
  `lim30_p12` int(11) DEFAULT NULL,
  `lim50_p1` int(11) DEFAULT NULL,
  `lim50_p2` int(11) DEFAULT NULL,
  `lim50_p3` int(11) DEFAULT NULL,
  `lim50_p4` int(11) DEFAULT NULL,
  `lim50_p5` int(11) DEFAULT NULL,
  `lim50_p6` int(11) DEFAULT NULL,
  `lim50_p7` int(11) DEFAULT NULL,
  `lim50_p8` int(11) DEFAULT NULL,
  `lim50_p9` int(11) DEFAULT NULL,
  `lim50_p10` int(11) DEFAULT NULL,
  `lim50_p11` int(11) DEFAULT NULL,
  `lim50_p12` int(11) DEFAULT NULL,
  `lim60_p1` int(11) DEFAULT NULL,
  `lim60_p2` int(11) DEFAULT NULL,
  `lim60_p3` int(11) DEFAULT NULL,
  `lim60_p4` int(11) DEFAULT NULL,
  `lim60_p5` int(11) DEFAULT NULL,
  `lim60_p6` int(11) DEFAULT NULL,
  `lim60_p7` int(11) DEFAULT NULL,
  `lim60_p8` int(11) DEFAULT NULL,
  `lim60_p9` int(11) DEFAULT NULL,
  `lim60_p10` int(11) DEFAULT NULL,
  `lim60_p11` int(11) DEFAULT NULL,
  `lim60_p12` int(11) DEFAULT NULL,
  `lim80_p1` int(11) DEFAULT NULL,
  `lim80_p2` int(11) DEFAULT NULL,
  `lim80_p3` int(11) DEFAULT NULL,
  `lim80_p4` int(11) DEFAULT NULL,
  `lim80_p5` int(11) DEFAULT NULL,
  `lim80_p6` int(11) DEFAULT NULL,
  `lim80_p7` int(11) DEFAULT NULL,
  `lim80_p8` int(11) DEFAULT NULL,
  `lim80_p9` int(11) DEFAULT NULL,
  `lim80_p10` int(11) DEFAULT NULL,
  `lim80_p11` int(11) DEFAULT NULL,
  `lim80_p12` int(11) DEFAULT NULL,
  `lim120_p1` int(11) DEFAULT NULL,
  `lim120_p2` int(11) DEFAULT NULL,
  `lim120_p3` int(11) DEFAULT NULL,
  `lim120_p4` int(11) DEFAULT NULL,
  `lim120_p5` int(11) DEFAULT NULL,
  `lim120_p6` int(11) DEFAULT NULL,
  `lim120_p7` int(11) DEFAULT NULL,
  `lim120_p8` int(11) DEFAULT NULL,
  `lim120_p9` int(11) DEFAULT NULL,
  `lim120_p10` int(11) DEFAULT NULL,
  `lim120_p11` int(11) DEFAULT NULL,
  `lim120_p12` int(11) DEFAULT NULL,
  `state` varchar(32) DEFAULT NULL,
  `home_flag` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`income_limit_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2623 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_asc_fieldoffice_county`
--

CREATE TABLE `aaq_asc_fieldoffice_county` (
  `fieldoffice_id` int(10) unsigned NOT NULL,
  `county_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`fieldoffice_id`,`county_id`),
  KEY `fk_aaq_asc_fieldoffice_county_1` (`fieldoffice_id`),
  KEY `fk_aaq_asc_fieldoffice_county_2` (`county_id`),
  CONSTRAINT `fk_aaq_asc_fieldoffice_county_1` FOREIGN KEY (`fieldoffice_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_asc_fieldoffice_county_2` FOREIGN KEY (`county_id`) REFERENCES `lku_county` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_asc_grantee_state`
--

CREATE TABLE `aaq_asc_grantee_state` (
  `granteeid` int(10) unsigned NOT NULL,
  `stateid` char(2) NOT NULL,
  PRIMARY KEY (`granteeid`,`stateid`),
  KEY `fk_aaq_asc_grantee_state_1` (`granteeid`),
  KEY `fk_aaq_asc_grantee_state_2` (`stateid`),
  CONSTRAINT `fk_aaq_asc_grantee_state_1` FOREIGN KEY (`granteeid`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_asc_grantee_state_2` FOREIGN KEY (`stateid`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_asc_pool_documents`
--

CREATE TABLE `aaq_asc_pool_documents` (
  `pool_id` int(10) unsigned NOT NULL,
  `document_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`pool_id`,`document_id`),
  KEY `fk_aaq_asc_pool_docs_2` (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_asc_pool_programs`
--

CREATE TABLE `aaq_asc_pool_programs` (
  `pool_id` int(10) unsigned NOT NULL,
  `program_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`pool_id`,`program_id`),
  KEY `fk_aaq_asc_pool_programs_1` (`pool_id`),
  KEY `fk_aaq_asc_pool_programs_2` (`program_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_asc_question_programs`
--

CREATE TABLE `aaq_asc_question_programs` (
  `question_id` int(10) unsigned NOT NULL,
  `program_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`question_id`,`program_id`),
  KEY `fk_aaq_asc_question_programs_1` (`question_id`),
  KEY `fk_aaq_asc_question_programs_2` (`program_id`),
  CONSTRAINT `fk_aaq_asc_question_programs_1` FOREIGN KEY (`question_id`) REFERENCES `aaq_tbl_question` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_asc_question_programs_2` FOREIGN KEY (`program_id`) REFERENCES `aaq_lku_pool` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_asc_question_relationship`
--

CREATE TABLE `aaq_asc_question_relationship` (
  `question_id` int(10) unsigned NOT NULL,
  `relationship_id` int(10) unsigned NOT NULL,
  `is_master` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`question_id`,`relationship_id`),
  KEY `fk_aaq_asc_question_relationship1_idx` (`question_id`),
  KEY `fk_aaq_asc_question_relationship2_idx` (`relationship_id`),
  CONSTRAINT `fk_aaq_asc_question_relationship1` FOREIGN KEY (`question_id`) REFERENCES `aaq_tbl_question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_asc_question_relationship2` FOREIGN KEY (`relationship_id`) REFERENCES `aaq_tbl_relationship` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_asc_question_revision_info`
--

CREATE TABLE `aaq_asc_question_revision_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question_revision_id` int(10) unsigned NOT NULL,
  `additional_info_id` int(10) unsigned DEFAULT NULL,
  `attachment_id` int(10) unsigned DEFAULT NULL,
  `answer_id` int(10) unsigned DEFAULT NULL,
  `create_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_aaq_asc_question_revision_info_1` (`question_revision_id`),
  KEY `FK_aaq_asc_question_revision_info_2` (`additional_info_id`),
  KEY `FK_aaq_asc_question_revision_info_3` (`attachment_id`),
  KEY `FK_aaq_asc_question_revision_info_4` (`answer_id`),
  CONSTRAINT `FK_aaq_asc_question_revision_info_1` FOREIGN KEY (`question_revision_id`) REFERENCES `aaq_tbl_question_revision` (`id`),
  CONSTRAINT `FK_aaq_asc_question_revision_info_2` FOREIGN KEY (`additional_info_id`) REFERENCES `aaq_tbl_additional_info` (`id`),
  CONSTRAINT `FK_aaq_asc_question_revision_info_3` FOREIGN KEY (`attachment_id`) REFERENCES `aaq_tbl_attachment` (`id`),
  CONSTRAINT `FK_aaq_asc_question_revision_info_4` FOREIGN KEY (`answer_id`) REFERENCES `aaq_tbl_answer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_asc_reopened_question`
--

CREATE TABLE `aaq_asc_reopened_question` (
  `original_question_id` int(10) unsigned NOT NULL,
  `reopened_question_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`original_question_id`,`reopened_question_id`),
  KEY `fk_aaq_asc_reopened_question1_idx` (`original_question_id`),
  KEY `fk_aaq_asc_reopened_question2_idx` (`reopened_question_id`),
  CONSTRAINT `fk_aaq_asc_reopened_question1` FOREIGN KEY (`original_question_id`) REFERENCES `aaq_tbl_question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_asc_reopened_question2` FOREIGN KEY (`reopened_question_id`) REFERENCES `aaq_tbl_question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_asc_subquestion_answer`
--

CREATE TABLE `aaq_asc_subquestion_answer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(10) unsigned NOT NULL,
  `subquestionanswer_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_asc_subtopic_pool`
--

CREATE TABLE `aaq_asc_subtopic_pool` (
  `subtopic_id` int(10) unsigned NOT NULL,
  `pool_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`subtopic_id`,`pool_id`),
  KEY `fk_aaq_asc_subtopic_pool_1` (`subtopic_id`),
  KEY `fk_aaq_asc_subtopic_pool_2` (`pool_id`),
  CONSTRAINT `fk_aaq_asc_subtopic_pool_1` FOREIGN KEY (`subtopic_id`) REFERENCES `aaq_lku_subtopic` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_asc_subtopic_pool_2` FOREIGN KEY (`pool_id`) REFERENCES `aaq_lku_pool` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_lku_permission`
--

CREATE TABLE `aaq_lku_permission` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `order_by` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_lku_pool`
--

CREATE TABLE `aaq_lku_pool` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `acronym` varchar(10) DEFAULT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `pool_type_id` int(10) unsigned NOT NULL,
  `active` tinyint(4) NOT NULL DEFAULT '0',
  `active_um` tinyint(1) NOT NULL DEFAULT '0',
  `order_by` int(11) DEFAULT '0',
  `default_group_id` int(10) unsigned NOT NULL DEFAULT '3',
  PRIMARY KEY (`id`),
  KEY `aaq_lku_topic_1` (`pool_type_id`),
  CONSTRAINT `fk_aaq_lku_pool_1` FOREIGN KEY (`pool_type_id`) REFERENCES `aaq_lku_pool_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8 COMMENT='List of question pool';

--
-- Table structure for table `aaq_lku_pool_type`
--

CREATE TABLE `aaq_lku_pool_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Question pool type look up table';

--
-- Table structure for table `aaq_lku_relation_type`
--

CREATE TABLE `aaq_lku_relation_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `order_by` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_lku_requestor_type`
--

CREATE TABLE `aaq_lku_requestor_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `short_name` varchar(45) NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_lku_role`
--

CREATE TABLE `aaq_lku_role` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL COMMENT 'Drafter\nReviewer\nQuestion Pool Manager\nManager (FO only)\nOverall Manager',
  `order_by` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_lku_status`
--

CREATE TABLE `aaq_lku_status` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `ix_na_status_order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_lku_subquestion`
--

CREATE TABLE `aaq_lku_subquestion` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pool_id` int(10) unsigned NOT NULL,
  `subquestion` varchar(250) DEFAULT NULL,
  `sort` int(11) unsigned NOT NULL,
  `abbr` varchar(50) NOT NULL,
  `description` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IX_aaq_lku_subquestion_pool_id` (`pool_id`),
  CONSTRAINT `FK_aaq_lku_subquestion_pool_id` FOREIGN KEY (`pool_id`) REFERENCES `aaq_lku_pool` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_lku_subquestion_answer`
--

CREATE TABLE `aaq_lku_subquestion_answer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subquestion_id` int(10) unsigned NOT NULL,
  `answer` varchar(250) NOT NULL,
  `sort` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_lku_subtopic`
--

CREATE TABLE `aaq_lku_subtopic` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `topic_id` int(10) unsigned NOT NULL,
  `order_by` int(10) NOT NULL DEFAULT '0',
  `is_other` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10023 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_lku_topic`
--

CREATE TABLE `aaq_lku_topic` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `order_by` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_log_search`
--

CREATE TABLE `aaq_log_search` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `searchtext` text NOT NULL,
  `searchfilters` text NOT NULL,
  `userid` varchar(255) NOT NULL,
  `isexport` bit(1) NOT NULL,
  `createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=57492 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_additional_info`
--

CREATE TABLE `aaq_tbl_additional_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(10) unsigned NOT NULL,
  `additional_info` text NOT NULL,
  `create_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_aaq_asc_additional_info_1` (`question_id`),
  CONSTRAINT `fk_aaq_asc_additional_info_1` FOREIGN KEY (`question_id`) REFERENCES `aaq_tbl_question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9235 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_answer`
--

CREATE TABLE `aaq_tbl_answer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(10) unsigned NOT NULL,
  `answer` text NOT NULL,
  `note` text,
  `version` int(10) DEFAULT NULL COMMENT 'NULL until sent to a reviewer or answered',
  `unofficial` tinyint(1) NOT NULL DEFAULT '0',
  `created_by_id` char(35) NOT NULL,
  `update_date` datetime NOT NULL,
  `answer_by_phone` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_aaq_tbl_answer_1` (`question_id`),
  KEY `fk_aaq_tbl_answer_2` (`created_by_id`),
  CONSTRAINT `fk_aaq_tbl_answer_1` FOREIGN KEY (`question_id`) REFERENCES `aaq_tbl_question` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_answer_2` FOREIGN KEY (`created_by_id`) REFERENCES `tusers` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=211404 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_answer_attachment`
--

CREATE TABLE `aaq_tbl_answer_attachment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(10) unsigned NOT NULL,
  `name` varchar(256) CHARACTER SET latin1 NOT NULL,
  `path_name` varchar(256) CHARACTER SET latin1 NOT NULL,
  `date_uploaded` datetime NOT NULL,
  `mime_type` varchar(256) CHARACTER SET latin1 NOT NULL,
  `uploaded_by_id` char(35) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_aaq_tbl_answer_attachment_1` (`question_id`),
  KEY `fk_aaq_tbl_answer_attachment_2` (`uploaded_by_id`),
  CONSTRAINT `fk_aaq_tbl_answer_attachment_1` FOREIGN KEY (`question_id`) REFERENCES `aaq_tbl_question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_tbl_answer_attachment_2` FOREIGN KEY (`uploaded_by_id`) REFERENCES `tusers` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6143 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_assignment_history`
--

CREATE TABLE `aaq_tbl_assignment_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(10) unsigned NOT NULL,
  `group_id` int(10) unsigned NOT NULL,
  `assignee_org_id` int(10) unsigned DEFAULT NULL,
  `assignee_id` char(35) DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  `status_id` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `fk_aaq_tbl_assignment_history_1` (`question_id`),
  KEY `fk_aaq_tbl_assignment_history_2` (`group_id`),
  KEY `fk_aaq_tbl_assignment_history_3` (`assignee_org_id`),
  KEY `fk_aaq_tbl_assignment_history_4` (`assignee_id`),
  CONSTRAINT `fk_aaq_tbl_assignment_history_1` FOREIGN KEY (`question_id`) REFERENCES `aaq_tbl_question` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_assignment_history_2` FOREIGN KEY (`group_id`) REFERENCES `c_torgtype` (`orgTypeID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_assignment_history_3` FOREIGN KEY (`assignee_org_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_assignment_history_4` FOREIGN KEY (`assignee_id`) REFERENCES `tusers` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=366309 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_attachment`
--

CREATE TABLE `aaq_tbl_attachment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(10) unsigned DEFAULT NULL COMMENT 'for attachment submitted by requestor along with a question',
  `additional_info_id` int(10) unsigned DEFAULT NULL COMMENT 'for attachment submitted by requestor along with an additional information',
  `name` varchar(256) CHARACTER SET latin1 NOT NULL,
  `path_name` varchar(256) CHARACTER SET latin1 NOT NULL,
  `date_uploaded` datetime NOT NULL,
  `mime_type` varchar(256) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk__tbl_attachment_1` (`question_id`),
  KEY `fk__tbl_attachment_2` (`additional_info_id`),
  CONSTRAINT `fk__tbl_attachment_1` FOREIGN KEY (`question_id`) REFERENCES `aaq_tbl_question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk__tbl_attachment_2` FOREIGN KEY (`additional_info_id`) REFERENCES `aaq_tbl_additional_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16652 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_documents`
--

CREATE TABLE `aaq_tbl_documents` (
  `document_id` int(11) NOT NULL AUTO_INCREMENT,
  `document_name` varchar(256) NOT NULL,
  `path_name` varchar(256) NOT NULL,
  `date_uploaded` datetime NOT NULL,
  `mime_type` varchar(256) NOT NULL,
  `uploaded_by_id` char(35) NOT NULL,
  PRIMARY KEY (`document_id`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_emailtext`
--

CREATE TABLE `aaq_tbl_emailtext` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emailName` varchar(45) DEFAULT NULL,
  `emailsubject` varchar(80) NOT NULL,
  `emailstatustext` varchar(120) NOT NULL,
  `emailstatuscolor` varchar(6) NOT NULL DEFAULT 'fffec5',
  `emailbodytext` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_question`
--

CREATE TABLE `aaq_tbl_question` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) NOT NULL,
  `question` text NOT NULL,
  `rid` char(35) DEFAULT NULL COMMENT 'string for URL of public side view for question detail; generated by createUUID() in CF',
  `requestor_fname` varchar(50) NOT NULL,
  `requestor_lname` varchar(50) NOT NULL,
  `requestor_countyid` int(10) unsigned DEFAULT NULL,
  `requestor_stateid` char(2) NOT NULL,
  `requestor_phone` varchar(20) NOT NULL,
  `requestor_email` varchar(255) NOT NULL,
  `requestor_type_id` int(10) unsigned NOT NULL COMMENT '"I am a" value',
  `requestor_type_other` varchar(200) DEFAULT NULL COMMENT 'string value when "I am a" value is Other',
  `requestor_org_id` int(10) unsigned DEFAULT NULL COMMENT 'Grantee, TA Provider, or HUD orgid value',
  `requestor_org_name` varchar(200) DEFAULT NULL COMMENT '"What is the name of your organization?" field value (when "I am a" is Prospective Grantee)',
  `requestor_ta_other` varchar(200) DEFAULT NULL,
  `requestor_userID` char(35) DEFAULT NULL,
  `fieldoffice_id` int(10) unsigned NOT NULL COMMENT 'Field Office of requestor - determined based on city, state, and zip',
  `pool_id` int(10) unsigned NOT NULL COMMENT '"My Quesiton is Related To" field value',
  `related_program_id` int(10) unsigned DEFAULT NULL COMMENT 'For system and cross cutting federal, a primarily related program is required. This value will be NULL if pool_id is one of program id.',
  `subtopic_id` int(10) unsigned DEFAULT NULL,
  `group_id` int(10) unsigned NOT NULL DEFAULT '3' COMMENT 'orgtypeID of a question is currently assigned to; TA Provider (3) when submitted',
  `assignee_org_id` int(10) unsigned DEFAULT NULL,
  `assignee_id` char(35) DEFAULT NULL COMMENT 'userID of a question is currently assigned to; the value will be NULL in case of either "Unassigned Draft" or "Unassigned Reviewer" status',
  `status_id` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'current status; gets value 1 (Unassigned draft) when created',
  `create_date` datetime NOT NULL,
  `update_date` datetime NOT NULL,
  `answered_date` datetime DEFAULT NULL,
  `old_id` int(10) DEFAULT NULL,
  `coc_no` varchar(45) DEFAULT NULL,
  `requestor_coc_other` varchar(200) DEFAULT NULL,
  `env_review_type` varchar(200) DEFAULT NULL,
  `responsible_entity` varchar(45) DEFAULT NULL,
  `hmis_type` varchar(200) DEFAULT NULL,
  `esnaps_app_name` varchar(300) DEFAULT NULL,
  `esnaps_app_no` varchar(300) DEFAULT NULL,
  `esnaps_project_name` varchar(300) DEFAULT NULL,
  `daysOpen` int(11) DEFAULT NULL,
  `daysInactive` int(11) DEFAULT NULL,
  `idis_user_id` varchar(50) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_aaq_tbl_question_2` (`related_program_id`),
  KEY `fk_aaq_tbl_question_3` (`requestor_org_id`),
  KEY `fk_aaq_tbl_question_4` (`requestor_type_id`),
  KEY `fk_aaq_tbl_question_1` (`pool_id`),
  KEY `fk_aaq_tbl_question_6` (`subtopic_id`),
  KEY `fk_aaq_tbl_question_7` (`group_id`),
  KEY `fk_aaq_tbl_question_8` (`assignee_id`),
  KEY `fk_aaq_tbl_question_9` (`status_id`),
  KEY `fk_aaq_tbl_question_10` (`fieldoffice_id`),
  KEY `fk_aaq_tbl_question_5` (`rid`),
  KEY `fk_aaq_tbl_question_11` (`requestor_countyid`),
  KEY `fk_aaq_tbl_question_12` (`requestor_stateid`),
  KEY `fk_aaq_tbl_question_13` (`assignee_org_id`),
  CONSTRAINT `fk_aaq_tbl_question_1` FOREIGN KEY (`pool_id`) REFERENCES `aaq_lku_pool` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_10` FOREIGN KEY (`fieldoffice_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_11` FOREIGN KEY (`requestor_countyid`) REFERENCES `lku_county` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_12` FOREIGN KEY (`requestor_stateid`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_13` FOREIGN KEY (`assignee_org_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_2` FOREIGN KEY (`related_program_id`) REFERENCES `aaq_lku_pool` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_3` FOREIGN KEY (`requestor_org_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_4` FOREIGN KEY (`requestor_type_id`) REFERENCES `aaq_lku_requestor_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_5` FOREIGN KEY (`rid`) REFERENCES `tredirects` (`redirectID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_6` FOREIGN KEY (`subtopic_id`) REFERENCES `aaq_lku_subtopic` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_7` FOREIGN KEY (`group_id`) REFERENCES `c_torgtype` (`orgTypeID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_8` FOREIGN KEY (`assignee_id`) REFERENCES `tusers` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_9` FOREIGN KEY (`status_id`) REFERENCES `aaq_lku_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=88250 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_question_history`
--

CREATE TABLE `aaq_tbl_question_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(10) unsigned NOT NULL,
  `answer_id` int(10) unsigned DEFAULT NULL COMMENT 'if Drafted or Reviewed',
  `additional_info_id` int(10) unsigned DEFAULT NULL COMMENT 'if Requestor submitted additional information',
  `change_name` varchar(100) DEFAULT NULL COMMENT 'Drafted\nReviewed\nRequestor updated Question\nQuestion Pool Changed\nQuestion on Hold\nQuestion Closed\nAnswered\n',
  `change_detail` text COMMENT 'if Question Pool Changed,\n"Changed Quesiton Pool from (original pool) to (new pool)"\nif Question on Hold,\nexplanation text\nif Question Closed,\nexplanation text;\nNULL otherwise\n',
  `changed_by_id` char(35) DEFAULT NULL,
  `changed_by_name` varchar(100) DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_aaq_asc_question_history_1` (`question_id`),
  KEY `fk_aaq_asc_question_history_2` (`answer_id`),
  KEY `fk_aaq_asc_question_history_3` (`additional_info_id`),
  KEY `fk_aaq_asc_question_history_4` (`changed_by_id`),
  CONSTRAINT `fk_aaq_tbl_question_history_1` FOREIGN KEY (`question_id`) REFERENCES `aaq_tbl_question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_tbl_question_history_2` FOREIGN KEY (`answer_id`) REFERENCES `aaq_tbl_answer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_tbl_question_history_3` FOREIGN KEY (`additional_info_id`) REFERENCES `aaq_tbl_additional_info` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_question_history_4` FOREIGN KEY (`changed_by_id`) REFERENCES `tusers` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=712612 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_question_revision`
--

CREATE TABLE `aaq_tbl_question_revision` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(10) unsigned NOT NULL,
  `question_revision` mediumtext NOT NULL,
  `create_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_aaq_tbl_question_revision_1` (`question_id`),
  CONSTRAINT `FK_aaq_tbl_question_revision_1` FOREIGN KEY (`question_id`) REFERENCES `aaq_tbl_question` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_relationship`
--

CREATE TABLE `aaq_tbl_relationship` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `relation_type_id` int(10) unsigned NOT NULL,
  `created_date` datetime NOT NULL,
  `lastupdated` datetime NOT NULL,
  `createdby` char(35) NOT NULL,
  `updatedby` char(35) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE_aaq_tbl_relationship_name` (`name`,`relation_type_id`),
  KEY `fk_aaq_tbl_relationship1_idx` (`relation_type_id`),
  KEY `fk_aaq_tbl_relationship2_idx` (`createdby`),
  KEY `fk_aaq_tbl_relationship3_idx` (`updatedby`),
  CONSTRAINT `fk_aaq_tbl_relationship1` FOREIGN KEY (`relation_type_id`) REFERENCES `aaq_lku_relation_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_relationship2` FOREIGN KEY (`createdby`) REFERENCES `tusers` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaq_tbl_relationship3` FOREIGN KEY (`updatedby`) REFERENCES `tusers` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=811 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_uploadfilestatus`
--

CREATE TABLE `aaq_tbl_uploadfilestatus` (
  `status_id` int(11) NOT NULL AUTO_INCREMENT,
  `status_code` int(11) DEFAULT NULL,
  `status_msg` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`status_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Table structure for table `aaq_tbl_user_role`
--

CREATE TABLE `aaq_tbl_user_role` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` char(35) NOT NULL,
  `role_id` int(10) unsigned NOT NULL COMMENT 'drafter, reviewer, pool manager, manager, overall manager',
  `permission_id` int(10) unsigned NOT NULL DEFAULT '1' COMMENT '1: User; 2: Manager',
  `pool_id` int(10) unsigned DEFAULT NULL,
  `fieldoffice_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_aaq_tbl_user_role_1` (`user_id`),
  KEY `fk_aaq_tbl_user_role_2` (`role_id`),
  KEY `fk_aaq_tbl_user_role_3` (`pool_id`),
  KEY `fk_aaq_tbl_user_role_4` (`fieldoffice_id`),
  KEY `fk_aaq_tbl_user_role_5` (`permission_id`),
  CONSTRAINT `fk_aaq_tbl_user_role_1` FOREIGN KEY (`user_id`) REFERENCES `tusers` (`UserID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_tbl_user_role_2` FOREIGN KEY (`role_id`) REFERENCES `aaq_lku_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_tbl_user_role_3` FOREIGN KEY (`pool_id`) REFERENCES `aaq_lku_pool` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_tbl_user_role_4` FOREIGN KEY (`fieldoffice_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_aaq_tbl_user_role_5` FOREIGN KEY (`permission_id`) REFERENCES `aaq_lku_permission` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4718 DEFAULT CHARSET=utf8;

--
-- Table structure for table `c_acoc`
--

CREATE TABLE `c_acoc` (
  `coc_no` char(10) NOT NULL,
  `org_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`coc_no`,`org_id`),
  KEY `fk_c_acoc_1` (`coc_no`),
  KEY `fk_c_acoc_2` (`org_id`),
  CONSTRAINT `fk_c_acoc_1` FOREIGN KEY (`coc_no`) REFERENCES `c_tcoc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_c_acoc_2` FOREIGN KEY (`org_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `c_tcategories`
--

CREATE TABLE `c_tcategories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categoryID` char(35) DEFAULT '0',
  `masterNameLong` varchar(255) NOT NULL DEFAULT '0',
  `masterNameShort` varchar(20) DEFAULT '0',
  `rsc_programID` int(10) unsigned DEFAULT NULL,
  `rsc_reportID` int(10) unsigned DEFAULT NULL,
  `rsc_ccrID` int(10) unsigned DEFAULT NULL,
  `faq_contentID` char(35) DEFAULT NULL,
  `faq_contentTitle` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=utf8 COMMENT='Mapping for various plugin names to a common ''Master'' version of a Program/Resporting System/CCR name';

--
-- Table structure for table `c_tcoc`
--

CREATE TABLE `c_tcoc` (
  `coc_no` char(10) NOT NULL,
  `coc_name` varchar(100) NOT NULL,
  `state_id` char(2) NOT NULL,
  `fieldoffice_id` int(10) unsigned DEFAULT NULL,
  `filename` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`coc_no`),
  KEY `fk_c_tcoc_1` (`state_id`),
  KEY `fk_c_tcoc_2` (`fieldoffice_id`),
  CONSTRAINT `fk_c_tcoc_1` FOREIGN KEY (`state_id`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_c_tcoc_2` FOREIGN KEY (`fieldoffice_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `c_tcoc_names`
--

CREATE TABLE `c_tcoc_names` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `coc_no` char(10) NOT NULL,
  `coc_name` varchar(100) NOT NULL,
  `year` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_c_tcoc_names_1_idx` (`coc_no`),
  CONSTRAINT `fk_c_tcoc_names_1` FOREIGN KEY (`coc_no`) REFERENCES `c_tcoc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4820 DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`cbirdsong`@`localhost`*/ /*!50003 TRIGGER trg_cocnames_insert AFTER INSERT ON c_tcoc_names 
FOR EACH ROW
BEGIN

IF (new.year = (SELECT max(year) FROM c_tcoc_names WHERE coc_no = new.coc_no)) THEN
	UPDATE c_tcoc SET 
	coc_name = new.coc_name
	WHERE coc_no = new.coc_no;
END IF ;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`cbirdsong`@`localhost`*/ /*!50003 TRIGGER trg_cocnames_update AFTER UPDATE ON c_tcoc_names 
FOR EACH ROW
BEGIN

IF (new.year = (SELECT max(year) FROM c_tcoc_names WHERE coc_no = new.coc_no)) THEN
	UPDATE c_tcoc SET 
	coc_name = new.coc_name
	WHERE coc_no = new.coc_no;
END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `c_tcoc_orgtypes`
--

CREATE TABLE `c_tcoc_orgtypes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `coc_no` char(10) NOT NULL,
  `orgtypeid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_c_tcoc_orgtypes_1_idx` (`coc_no`),
  KEY `fk_c_tcoc_orgtypes_2_idx` (`orgtypeid`),
  CONSTRAINT `fk_c_tcoc_orgtypes_1` FOREIGN KEY (`coc_no`) REFERENCES `c_tcoc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_c_tcoc_orgtypes_2` FOREIGN KEY (`orgtypeid`) REFERENCES `c_torgtype` (`orgTypeID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1014 DEFAULT CHARSET=utf8;

--
-- Table structure for table `c_torganizations`
--

CREATE TABLE `c_torganizations` (
  `orgid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `orgname` varchar(255) NOT NULL,
  `orgshortname` varchar(50) DEFAULT NULL COMMENT 'abbreviation of organization name',
  `orgtypeid` int(10) unsigned DEFAULT NULL COMMENT 'Type of Organization (FK referencing c_torgtype)',
  `orgtypes` varchar(45) DEFAULT NULL,
  `sortorder` int(10) unsigned DEFAULT '0',
  `updatedDate` datetime DEFAULT NULL,
  `updatedBy` varchar(100) DEFAULT NULL,
  `granteekey` char(6) DEFAULT NULL,
  `grantee_state` varchar(100) DEFAULT NULL,
  `grantee_code` char(7) DEFAULT NULL,
  `coc_no` varchar(45) DEFAULT NULL,
  `coc_no_coc` varchar(100) DEFAULT NULL,
  `coc_no_hmis` varchar(100) DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  `fieldoffice_id` int(10) unsigned DEFAULT NULL,
  `change_to_orgid` int(10) DEFAULT NULL,
  `orgtypes_string` varchar(255) DEFAULT NULL,
  `hud_region` int(10) unsigned DEFAULT NULL,
  `filename` varchar(255) DEFAULT NULL,
  `pha_code` varchar(45) DEFAULT NULL,
  `officeid` int(11) DEFAULT NULL,
  PRIMARY KEY (`orgid`),
  KEY `orgname` (`orgname`) USING BTREE,
  KEY `idx_grantee_state` (`grantee_state`),
  KEY `idx_coc_no` (`coc_no`),
  KEY `idx_coc_no_coc` (`coc_no_coc`),
  KEY `idx_coc_no_hmis` (`coc_no_hmis`),
  KEY `idx_orgtype` (`orgtypes`),
  KEY `idx_grante_code` (`grantee_code`),
  KEY `fk_c_torganizations_1` (`fieldoffice_id`),
  KEY `fk_c_torganizations_hudregion` (`hud_region`),
  KEY `idx_c_torganizations_active` (`active`),
  KEY `idx_c_torganizations__grantee_code__active` (`grantee_code`,`active`),
  KEY `fk_c_torganizations_office` (`officeid`),
  CONSTRAINT `fk_c_torganizations_office` FOREIGN KEY (`officeid`) REFERENCES `hudx_offices` (`id`),
  CONSTRAINT `fk_c_torganizations_1` FOREIGN KEY (`fieldoffice_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_c_torganizations_hudregion` FOREIGN KEY (`hud_region`) REFERENCES `lku_hud_region` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=16842 DEFAULT CHARSET=utf8;

--
-- Table structure for table `c_torganizations_location`
--

CREATE TABLE `c_torganizations_location` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `orgid` int(10) unsigned NOT NULL,
  `street` varchar(500) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` char(2) DEFAULT NULL,
  `zip` char(10) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `ext` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_c_torganizations_location_1_idx` (`orgid`),
  CONSTRAINT `fk_c_torganizations_location_1` FOREIGN KEY (`orgid`) REFERENCES `c_torganizations` (`orgid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;

--
-- Table structure for table `c_torgtype`
--

CREATE TABLE `c_torgtype` (
  `orgTypeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `orgType` varchar(100) DEFAULT NULL,
  `shortname` varchar(45) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL,
  `showinUM` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`orgTypeID`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

--
-- Table structure for table `c_tusersmemb_subscribe`
--

CREATE TABLE `c_tusersmemb_subscribe` (
  `UserID` char(35) NOT NULL DEFAULT '',
  `GroupID` char(35) NOT NULL DEFAULT '',
  `Subscribe` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`UserID`,`GroupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `c_tuserspwupdateinfo`
--

CREATE TABLE `c_tuserspwupdateinfo` (
  `UserID` char(35) NOT NULL,
  `PasswordLastUpdateByID` char(35) NOT NULL,
  PRIMARY KEY (`UserID`,`PasswordLastUpdateByID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_asc_deadline_topic`
--

CREATE TABLE `cal_asc_deadline_topic` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `deadline_id` int(10) unsigned NOT NULL,
  `topic_id` int(10) unsigned NOT NULL,
  `topic_type_id` int(10) unsigned NOT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_asc_deadline_topic_1` (`deadline_id`),
  KEY `fk_cal_asc_deadline_topic_1` (`deadline_id`),
  CONSTRAINT `fk_cal_asc_deadline_topic_1` FOREIGN KEY (`deadline_id`) REFERENCES `cal_tbl_deadline` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_asc_event_host`
--

CREATE TABLE `cal_asc_event_host` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(10) unsigned NOT NULL,
  `host_id` int(10) unsigned NOT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_asc_event_host_1` (`event_id`),
  KEY `idx_cal_asc_event_host_2` (`host_id`),
  KEY `fk_cal_asc_event_host_1` (`event_id`),
  KEY `fk_cal_asc_event_host_2` (`host_id`),
  CONSTRAINT `fk_cal_asc_event_host_1` FOREIGN KEY (`event_id`) REFERENCES `cal_tbl_event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_asc_event_host_2` FOREIGN KEY (`host_id`) REFERENCES `cal_tbl_host` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1876 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_asc_event_resource`
--

CREATE TABLE `cal_asc_event_resource` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(10) unsigned NOT NULL,
  `resource_id` int(10) unsigned NOT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_asc_event_resource_1` (`event_id`),
  KEY `fk_cal_asc_event_resource_1` (`event_id`),
  KEY `fk_cal_asc_event_resource_2` (`resource_id`),
  CONSTRAINT `fk_cal_asc_event_resource_1` FOREIGN KEY (`event_id`) REFERENCES `cal_tbl_event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_asc_event_resource_2` FOREIGN KEY (`resource_id`) REFERENCES `cal_tbl_resource` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3965 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_asc_event_speaker_facilitator`
--

CREATE TABLE `cal_asc_event_speaker_facilitator` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(10) unsigned NOT NULL,
  `speaker_facilitator_id` int(10) unsigned NOT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_asc_event_speaker_1` (`event_id`),
  KEY `idx_cal_asc_event_speaker_2` (`speaker_facilitator_id`),
  KEY `fk_cal_asc_event_speaker_1` (`event_id`),
  KEY `fk_cal_asc_event_speaker_2` (`speaker_facilitator_id`),
  CONSTRAINT `fk_cal_asc_event_speaker_1` FOREIGN KEY (`event_id`) REFERENCES `cal_tbl_event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_asc_event_speaker_2` FOREIGN KEY (`speaker_facilitator_id`) REFERENCES `cal_tbl_speaker_facilitator` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_asc_event_sponsor`
--

CREATE TABLE `cal_asc_event_sponsor` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(10) unsigned NOT NULL,
  `sponsor_id` int(10) unsigned NOT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_asc_event_sponsor_1` (`event_id`),
  KEY `idx_cal_asc_event_sponsor_2` (`sponsor_id`),
  KEY `fk_cal_asc_event_sponsor_1` (`event_id`),
  KEY `fk_cal_asc_event_sponsor_2` (`sponsor_id`),
  CONSTRAINT `fk_cal_asc_event_sponsor_1` FOREIGN KEY (`event_id`) REFERENCES `cal_tbl_event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_asc_event_sponsor_2` FOREIGN KEY (`sponsor_id`) REFERENCES `cal_tbl_sponsor` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_asc_event_topic`
--

CREATE TABLE `cal_asc_event_topic` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(10) unsigned NOT NULL,
  `topic_id` int(10) unsigned NOT NULL,
  `topic_type_id` int(10) unsigned NOT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_asc_event_topic_1` (`event_id`),
  KEY `idx_cal_asc_event_topic_2` (`topic_type_id`),
  KEY `idx_cal_asc_event_topic_3` (`topic_id`),
  KEY `fk_cal_asc_event_topic_1` (`event_id`),
  CONSTRAINT `fk_cal_asc_event_topic_1` FOREIGN KEY (`event_id`) REFERENCES `cal_tbl_event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=604791 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_asc_hybrid_event_meeting`
--

CREATE TABLE `cal_asc_hybrid_event_meeting` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `LMS_course_id` bigint(20) unsigned NOT NULL,
  `LMS_class_cd` varchar(150) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `hybrid_event_id` int(10) DEFAULT NULL,
  `meeting_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_asc_hybrid_event_meeting_1` (`LMS_class_cd`)
) ENGINE=InnoDB AUTO_INCREMENT=9048 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_asc_learning_plan_event`
--

CREATE TABLE `cal_asc_learning_plan_event` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `learning_plan_id` int(10) unsigned NOT NULL,
  `event_id` int(10) unsigned NOT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_asc_learn_plan_event_1` (`learning_plan_id`),
  KEY `idx_cal_asc_learn_plan_event_2` (`event_id`),
  KEY `fk_cal_asc_learn_plan_event_1` (`learning_plan_id`),
  KEY `fk_cal_asc_learn_plan_event_2` (`event_id`),
  CONSTRAINT `fk_cal_asc_learn_plan_event_1` FOREIGN KEY (`learning_plan_id`) REFERENCES `cal_tbl_learning_plan` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_asc_learn_plan_event_2` FOREIGN KEY (`event_id`) REFERENCES `cal_tbl_event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=33628 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_asc_learning_plan_resource`
--

CREATE TABLE `cal_asc_learning_plan_resource` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `learning_plan_id` int(10) unsigned NOT NULL,
  `resource_id` int(10) unsigned NOT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_asc_learning_plan_resource_1` (`learning_plan_id`),
  KEY `fk_cal_asc_learning_plan_resource_1` (`learning_plan_id`),
  KEY `fk_cal_asc_learning_plan_resource_2` (`resource_id`),
  CONSTRAINT `fk_cal_asc_learning_plan_resource_1` FOREIGN KEY (`learning_plan_id`) REFERENCES `cal_tbl_learning_plan` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_asc_learning_plan_resource_2` FOREIGN KEY (`resource_id`) REFERENCES `cal_tbl_resource` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_asc_meeting_resource`
--

CREATE TABLE `cal_asc_meeting_resource` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `meeting_id` int(10) unsigned NOT NULL,
  `resource_id` int(10) unsigned NOT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_asc_meeting_resource_1` (`meeting_id`),
  KEY `fk_cal_asc_meeting_resource_1` (`meeting_id`),
  KEY `fk_cal_asc_meeting_resource_2` (`resource_id`),
  CONSTRAINT `fk_cal_asc_meeting_resource_1` FOREIGN KEY (`meeting_id`) REFERENCES `cal_tbl_meeting` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_asc_meeting_resource_2` FOREIGN KEY (`resource_id`) REFERENCES `cal_tbl_resource` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3548 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_lku_event_type`
--

CREATE TABLE `cal_lku_event_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(100) NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_lku_resource_type`
--

CREATE TABLE `cal_lku_resource_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(100) NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_lku_topic_type`
--

CREATE TABLE `cal_lku_topic_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(100) NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_contact`
--

CREATE TABLE `cal_tbl_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fname` varchar(45) NOT NULL,
  `lname` varchar(500) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=492 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_deadline`
--

CREATE TABLE `cal_tbl_deadline` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT 'name of deadline',
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `url_link_display` varchar(255) DEFAULT NULL COMMENT 'view webcast, live session',
  `description` text NOT NULL,
  `display` bit(1) NOT NULL DEFAULT b'0',
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_event`
--

CREATE TABLE `cal_tbl_event` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(512) NOT NULL COMMENT 'name of event, course or conference',
  `teaser` varchar(1000) NOT NULL,
  `description` text NOT NULL,
  `isHUD` bit(1) NOT NULL DEFAULT b'0',
  `isConference` bit(1) NOT NULL DEFAULT b'0',
  `isFeaturedCourse` bit(1) NOT NULL DEFAULT b'0',
  `isHybridCourse` bit(1) NOT NULL DEFAULT b'0',
  `url` varchar(255) DEFAULT NULL,
  `url_link_display` varchar(255) DEFAULT NULL COMMENT 'view webcast, live session',
  `contact_id` int(10) unsigned DEFAULT NULL,
  `series_id` int(10) unsigned DEFAULT NULL,
  `conference_id` int(10) unsigned DEFAULT NULL,
  `event_type_id` int(10) unsigned DEFAULT NULL,
  `LMS_course_cd` varchar(250) DEFAULT NULL,
  `LMS_course_id` bigint(20) unsigned DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL COMMENT 'originally intended for LMS only but also using for Non-HUD now for published',
  `LMS_course_type` varchar(255) DEFAULT NULL,
  `SESUrl` varchar(1000) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `isLearningPlanOnly` bit(1) DEFAULT b'0',
  `posted_on_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_tbl_event_1` (`conference_id`),
  KEY `idx_cal_tbl_event_2` (`series_id`),
  KEY `fk_cal_tbl_event_1` (`contact_id`),
  KEY `fk_cal_tbl_event_2` (`series_id`),
  KEY `fk_cal_tbl_event_3` (`conference_id`),
  KEY `fk_cal_tbl_event_4` (`event_type_id`),
  CONSTRAINT `fk_cal_tbl_event_1` FOREIGN KEY (`contact_id`) REFERENCES `cal_tbl_contact` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_tbl_event_2` FOREIGN KEY (`series_id`) REFERENCES `cal_tbl_series` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_tbl_event_3` FOREIGN KEY (`conference_id`) REFERENCES `cal_tbl_event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_tbl_event_4` FOREIGN KEY (`event_type_id`) REFERENCES `cal_lku_event_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2110 DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`mysite_cms`@`%`*/ /*!50003 TRIGGER cal_tbl_event_delete
AFTER DELETE ON cal_tbl_event
FOR EACH ROW
	INSERT INTO delete_log(deleted_id, deleted_date, deleted_table)
    VALUES (Old.id, now(), 'cal_tbl_event'); */;;
DELIMITER ;

--
-- Table structure for table `cal_tbl_host`
--

CREATE TABLE `cal_tbl_host` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1876 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_last_updated`
--

CREATE TABLE `cal_tbl_last_updated` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `area` varchar(45) NOT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `area_UNIQUE` (`area`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_learning_plan`
--

CREATE TABLE `cal_tbl_learning_plan` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(400) NOT NULL,
  `summary` varchar(1000) DEFAULT NULL,
  `description` text,
  `SESUrl` varchar(1000) DEFAULT NULL,
  `status` varchar(20) NOT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `isFeaturedCourse` bit(1) NOT NULL DEFAULT b'0',
  `posted_on_date` datetime DEFAULT NULL,
  `type` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`mysite_cms`@`%`*/ /*!50003 TRIGGER cal_tbl_learning_plan_delete
AFTER DELETE ON cal_tbl_learning_plan
FOR EACH ROW
	INSERT INTO delete_log(deleted_id, deleted_date, deleted_table)
    VALUES (Old.id, now(), 'cal_tbl_learning_plan'); */;;
DELIMITER ;

--
-- Table structure for table `cal_tbl_lms_workflow_form`
--

CREATE TABLE `cal_tbl_lms_workflow_form` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `LMS_request_id` bigint(20) NOT NULL COMMENT 'the request id from LMS',
  `question` varchar(500) NOT NULL,
  `answer` varchar(2000) DEFAULT NULL,
  `LMS_last_modified` datetime NOT NULL COMMENT 'stored as UTC date',
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cal_tbl_lms_workflow_form_1` (`LMS_request_id`),
  CONSTRAINT `fk_cal_tbl_lms_workflow_form_1` FOREIGN KEY (`LMS_request_id`) REFERENCES `cal_tbl_lms_workflow_request` (`LMS_request_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=31100 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_lms_workflow_request`
--

CREATE TABLE `cal_tbl_lms_workflow_request` (
  `LMS_request_id` bigint(20) NOT NULL COMMENT 'the request id from LMS',
  `request_dt` date NOT NULL,
  `status` varchar(45) NOT NULL,
  `last_approval_dt` date DEFAULT NULL,
  `username` varchar(60) NOT NULL,
  `LMS_course_id` bigint(20) NOT NULL,
  `LMS_class_id` bigint(20) DEFAULT NULL,
  `LMS_last_modified` datetime NOT NULL COMMENT 'stored as UTC date',
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`LMS_request_id`),
  UNIQUE KEY `idx_unique_cal_tbl_lms_workflow_request_1` (`LMS_request_id`),
  KEY `idx_cal_tbl_lms_workflow_request_1` (`LMS_course_id`),
  KEY `idx_cal_tbl_lms_workflow_request_2` (`LMS_class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_location`
--

CREATE TABLE `cal_tbl_location` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `address1` varchar(250) DEFAULT NULL,
  `address2` varchar(250) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state_abbrev` char(2) DEFAULT NULL,
  `zip` varchar(10) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `contact_id` int(10) unsigned DEFAULT NULL,
  `lodging_info` varchar(2000) DEFAULT NULL,
  `lodging_resource_id` int(10) unsigned DEFAULT NULL,
  `LMS_location_id` bigint(20) unsigned DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cal_tbl_location_2` (`contact_id`),
  KEY `fk_cal_tbl_location_3` (`lodging_resource_id`),
  CONSTRAINT `fk_cal_tbl_location_2` FOREIGN KEY (`contact_id`) REFERENCES `cal_tbl_contact` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_tbl_location_3` FOREIGN KEY (`lodging_resource_id`) REFERENCES `cal_tbl_resource` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=397 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_meeting`
--

CREATE TABLE `cal_tbl_meeting` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(10) unsigned NOT NULL,
  `name` varchar(512) NOT NULL COMMENT 'name of class',
  `summary` varchar(1000) DEFAULT NULL,
  `description` text,
  `timezone_cd` varchar(30) DEFAULT NULL,
  `timezone_display` varchar(30) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `url_link_display` varchar(255) DEFAULT NULL COMMENT 'view webcast, live session',
  `location_id` int(10) unsigned DEFAULT NULL,
  `registration_open` datetime DEFAULT NULL,
  `registration_deadline` datetime DEFAULT NULL,
  `registration_url` varchar(255) DEFAULT NULL,
  `registration_info` varchar(300) DEFAULT NULL,
  `LMS_class_cd` varchar(255) DEFAULT NULL,
  `LMS_class_id` bigint(20) unsigned DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `SESUrl` varchar(1000) DEFAULT NULL,
  `contactInfo` varchar(1000) DEFAULT NULL,
  `isPrivateClass` bit(1) DEFAULT b'0',
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `enrollment` int(10) unsigned DEFAULT NULL,
  `max_enrollment` int(10) unsigned DEFAULT NULL,
  `server_start` datetime DEFAULT NULL COMMENT 'start datetime based on where the server is; used when needing effect the website behaivior based on class start',
  `server_end` datetime DEFAULT NULL COMMENT 'end datetime based on where the server is; used when needing effect the website behaivior based on class end',
  `timezone_start` datetime DEFAULT NULL COMMENT 'start datetime based on where the class is; used for display and search',
  `timezone_end` datetime DEFAULT NULL COMMENT 'end datetime based on where the class is; used for display and search',
  `posted_on_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cal_tbl_meeting_1` (`event_id`),
  KEY `fk_cal_tbl_meeting_2` (`location_id`),
  KEY `idx_cal_tbl_meeting_1` (`server_start`),
  KEY `idx_cal_tbl_meeting_2` (`timezone_start`),
  CONSTRAINT `fk_cal_tbl_meeting_1` FOREIGN KEY (`event_id`) REFERENCES `cal_tbl_event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_cal_tbl_meeting_2` FOREIGN KEY (`location_id`) REFERENCES `cal_tbl_location` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1726 DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`mysite_cms`@`%`*/ /*!50003 TRIGGER cal_tbl_meeting_delete
AFTER DELETE ON cal_tbl_meeting
FOR EACH ROW
	INSERT INTO delete_log(deleted_id, deleted_date, deleted_table)
    VALUES (Old.id, now(), 'cal_tbl_meeting'); */;;
DELIMITER ;

--
-- Table structure for table `cal_tbl_resource`
--

CREATE TABLE `cal_tbl_resource` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(250) NOT NULL,
  `url` varchar(500) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `format` varchar(10) DEFAULT NULL,
  `embed_code` text,
  `resource_type_id` int(10) unsigned NOT NULL,
  `SESUrl` varchar(200) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `order_by` int(11) unsigned NOT NULL DEFAULT '0',
  `isRecording` bit(1) DEFAULT b'0',
  PRIMARY KEY (`id`),
  KEY `fk_cal_tbl_resource_1` (`resource_type_id`),
  CONSTRAINT `fk_cal_tbl_resource_1` FOREIGN KEY (`resource_type_id`) REFERENCES `cal_lku_resource_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3105 DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_series`
--

CREATE TABLE `cal_tbl_series` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_session`
--

CREATE TABLE `cal_tbl_session` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `meeting_id` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `start_time` varchar(45) DEFAULT NULL,
  `end_time` varchar(45) DEFAULT NULL,
  `LMSid` bigint(20) unsigned DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cal_tbl_session_1` (`meeting_id`),
  KEY `fk_cal_tbl_session_1` (`meeting_id`),
  CONSTRAINT `fk_cal_tbl_session_1` FOREIGN KEY (`meeting_id`) REFERENCES `cal_tbl_meeting` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_speaker_facilitator`
--

CREATE TABLE `cal_tbl_speaker_facilitator` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fname` varchar(45) NOT NULL,
  `lname` varchar(45) NOT NULL,
  `title` varchar(75) DEFAULT NULL,
  `association` varchar(100) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `bio` text,
  `type_cd` char(1) NOT NULL COMMENT 'S = speacker, F = facilitator',
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `cal_tbl_sponsor`
--

CREATE TABLE `cal_tbl_sponsor` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `url` varchar(250) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `cal_vw_classes`
--

/*!50001 DROP VIEW IF EXISTS `cal_vw_classes`*/;
/*!50001 CREATE TABLE `cal_vw_classes` (
  `eventID` tinyint NOT NULL,
  `isHudEvent` tinyint NOT NULL,
  `courseTypeID` tinyint NOT NULL,
  `courseName` tinyint NOT NULL,
  `classID` tinyint NOT NULL,
  `serverStart` tinyint NOT NULL,
  `serverEnd` tinyint NOT NULL,
  `classStart` tinyint NOT NULL,
  `classEnd` tinyint NOT NULL,
  `timezone` tinyint NOT NULL,
  `courseTeaser` tinyint NOT NULL,
  `locationID` tinyint NOT NULL,
  `locationCity` tinyint NOT NULL,
  `locationStateAbbrev` tinyint NOT NULL,
  `city` tinyint NOT NULL,
  `stateAbbrev` tinyint NOT NULL,
  `courseType` tinyint NOT NULL,
  `className` tinyint NOT NULL,
  `classSummary` tinyint NOT NULL,
  `classDescription` tinyint NOT NULL,
  `lmsClassID` tinyint NOT NULL,
  `hosts` tinyint NOT NULL,
  `isPublished` tinyint NOT NULL,
  `SESUrl` tinyint NOT NULL,
  `isOnline` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `cal_vw_courses`
--

/*!50001 DROP VIEW IF EXISTS `cal_vw_courses`*/;
/*!50001 CREATE TABLE `cal_vw_courses` (
  `courseID` tinyint NOT NULL,
  `topicTypeList` tinyint NOT NULL,
  `topicList` tinyint NOT NULL,
  `courseTypeID` tinyint NOT NULL,
  `courseName` tinyint NOT NULL,
  `courseTeaser` tinyint NOT NULL,
  `isFeaturedCourse` tinyint NOT NULL,
  `createdDate` tinyint NOT NULL,
  `lastUpdated` tinyint NOT NULL,
  `lmsCourseType` tinyint NOT NULL,
  `LMSCourseCd` tinyint NOT NULL,
  `courseType` tinyint NOT NULL,
  `courseSESUrl` tinyint NOT NULL,
  `isPublished` tinyint NOT NULL,
  `isLearningPlan` tinyint NOT NULL,
  `postedDate` tinyint NOT NULL,
  `courseDescription` tinyint NOT NULL,
  `isHUD` tinyint NOT NULL,
  `updated_date` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `cal_vw_deadline_topics`
--

/*!50001 DROP VIEW IF EXISTS `cal_vw_deadline_topics`*/;
/*!50001 CREATE TABLE `cal_vw_deadline_topics` (
  `deadlineID` tinyint NOT NULL,
  `topicTypeID` tinyint NOT NULL,
  `topicTypeName` tinyint NOT NULL,
  `topicID` tinyint NOT NULL,
  `topicAcronym` tinyint NOT NULL,
  `topicName` tinyint NOT NULL,
  `topicFullName` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `cal_vw_deadlines`
--

/*!50001 DROP VIEW IF EXISTS `cal_vw_deadlines`*/;
/*!50001 CREATE TABLE `cal_vw_deadlines` (
  `deadlineID` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `startDate` tinyint NOT NULL,
  `endDate` tinyint NOT NULL,
  `description` tinyint NOT NULL,
  `deadlineUrl` tinyint NOT NULL,
  `url_link_display` tinyint NOT NULL,
  `isPublished` tinyint NOT NULL,
  `lastUpdated` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `cal_vw_event_topics`
--

/*!50001 DROP VIEW IF EXISTS `cal_vw_event_topics`*/;
/*!50001 CREATE TABLE `cal_vw_event_topics` (
  `eventID` tinyint NOT NULL,
  `topicTypeID` tinyint NOT NULL,
  `topicTypeName` tinyint NOT NULL,
  `topicID` tinyint NOT NULL,
  `topicAcronym` tinyint NOT NULL,
  `topicName` tinyint NOT NULL,
  `topicFullName` tinyint NOT NULL,
  `order_by` tinyint NOT NULL,
  `masterNameShort` tinyint NOT NULL,
  `masterNameLong` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `cal_vw_nonhud_events`
--

/*!50001 DROP VIEW IF EXISTS `cal_vw_nonhud_events`*/;
/*!50001 CREATE TABLE `cal_vw_nonhud_events` (
  `eventID` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `description` tinyint NOT NULL,
  `lastUpdated` tinyint NOT NULL,
  `serverStart` tinyint NOT NULL,
  `serverEnd` tinyint NOT NULL,
  `classStart` tinyint NOT NULL,
  `classEnd` tinyint NOT NULL,
  `status` tinyint NOT NULL,
  `eventTypeID` tinyint NOT NULL,
  `isPublished` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `coc_asc_assess_group_option`
--

CREATE TABLE `coc_asc_assess_group_option` (
  `option_group` varchar(45) NOT NULL,
  `option_id` int(10) unsigned NOT NULL,
  `order_by` int(11) NOT NULL,
  PRIMARY KEY (`option_group`,`option_id`),
  KEY `fk_coc_asc_assess_group_option_1` (`option_id`),
  CONSTRAINT `fk_coc_asc_assess_group_option_1` FOREIGN KEY (`option_id`) REFERENCES `coc_lku_assess_option` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_asc_assess_question_respondent_type`
--

CREATE TABLE `coc_asc_assess_question_respondent_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question_id` int(10) unsigned NOT NULL,
  `respondent_type_id` int(10) unsigned NOT NULL,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`),
  KEY `fk_coc_asc_assess_question_respondent_type_1` (`question_id`),
  KEY `fk_coc_asc_assess_question_respondent_type_2` (`respondent_type_id`),
  CONSTRAINT `fk_coc_asc_assess_question_respondent_type_1` FOREIGN KEY (`question_id`) REFERENCES `coc_tbl_assess_question` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_coc_asc_assess_question_respondent_type_2` FOREIGN KEY (`respondent_type_id`) REFERENCES `coc_lku_assess_respondent_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1078 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_lku_assess_domain`
--

CREATE TABLE `coc_lku_assess_domain` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `order_by` int(11) NOT NULL,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_lku_assess_element`
--

CREATE TABLE `coc_lku_assess_element` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `domain_id` int(10) unsigned NOT NULL,
  `display_id` varchar(20) DEFAULT NULL,
  `order_by` int(11) NOT NULL,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`),
  KEY `fk_coc_lku_assess_element_1` (`domain_id`),
  CONSTRAINT `fk_coc_lku_assess_element_1` FOREIGN KEY (`domain_id`) REFERENCES `coc_lku_assess_domain` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_lku_assess_importance`
--

CREATE TABLE `coc_lku_assess_importance` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `importance` varchar(45) NOT NULL,
  `level` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_lku_assess_option`
--

CREATE TABLE `coc_lku_assess_option` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(1000) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_lku_assess_question_type`
--

CREATE TABLE `coc_lku_assess_question_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_lku_assess_respondent_type`
--

CREATE TABLE `coc_lku_assess_respondent_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(50) DEFAULT NULL,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_lku_contact_type`
--

CREATE TABLE `coc_lku_contact_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_tbl_assess_action_plan`
--

CREATE TABLE `coc_tbl_assess_action_plan` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `assessment_id` int(10) unsigned NOT NULL,
  `description` varchar(1000) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_coc_tbl_action_plan_file_1` (`assessment_id`),
  CONSTRAINT `fk_coc_tbl_action_plan_file_1` FOREIGN KEY (`assessment_id`) REFERENCES `coc_tbl_assessment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=387 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_tbl_assess_question`
--

CREATE TABLE `coc_tbl_assess_question` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `question` varchar(255) NOT NULL,
  `display_id` varchar(20) DEFAULT NULL,
  `parent_question_id` int(10) unsigned NOT NULL,
  `domain_id` int(10) unsigned NOT NULL,
  `element_id` int(10) unsigned NOT NULL,
  `order_by` int(11) NOT NULL,
  `response_column` varchar(45) DEFAULT NULL,
  `question_type_id` int(10) unsigned DEFAULT NULL,
  `option_group` varchar(45) DEFAULT NULL,
  `importance_id` int(10) unsigned DEFAULT NULL,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`),
  KEY `fk_coc_tbl_assess_question_1` (`domain_id`),
  KEY `fk_coc_tbl_assess_question_2` (`element_id`),
  KEY `fk_coc_tbl_assess_question_3` (`question_type_id`),
  KEY `fk_coc_tbl_assess_question_4` (`option_group`),
  KEY `fk_coc_tbl_assess_question_5` (`importance_id`),
  CONSTRAINT `fk_coc_tbl_assess_question_1` FOREIGN KEY (`domain_id`) REFERENCES `coc_lku_assess_domain` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_coc_tbl_assess_question_2` FOREIGN KEY (`element_id`) REFERENCES `coc_lku_assess_element` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_coc_tbl_assess_question_3` FOREIGN KEY (`question_type_id`) REFERENCES `coc_lku_assess_question_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_coc_tbl_assess_question_4` FOREIGN KEY (`option_group`) REFERENCES `coc_asc_assess_group_option` (`option_group`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_coc_tbl_assess_question_5` FOREIGN KEY (`importance_id`) REFERENCES `coc_lku_assess_importance` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=213 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_tbl_assess_respondent`
--

CREATE TABLE `coc_tbl_assess_respondent` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` varchar(100) NOT NULL,
  `assessment_id` int(10) unsigned NOT NULL,
  `respondent_type_id` int(10) unsigned NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `organization` varchar(255) DEFAULT NULL,
  `final_assessment` bit(1) NOT NULL DEFAULT b'0',
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_coc_tbl_assess_respondent_uid` (`uid`),
  KEY `idx_coc_tbl_assess_respondent_type_id` (`respondent_type_id`),
  KEY `idx_coc_tbl_assess_respondent_final_assessment` (`final_assessment`),
  KEY `fk_coc_tbl_assess_respondent_assessment_id` (`assessment_id`),
  KEY `fk_coc_tbl_assess_respondent_respondent_type_id` (`respondent_type_id`),
  CONSTRAINT `fk_coc_tbl_assess_respondent_assessment_id` FOREIGN KEY (`assessment_id`) REFERENCES `coc_tbl_assessment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_coc_tbl_assess_respondent_respondent_type_id` FOREIGN KEY (`respondent_type_id`) REFERENCES `coc_lku_assess_respondent_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5370 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_tbl_assess_response`
--

CREATE TABLE `coc_tbl_assess_response` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `respondent_id` int(10) unsigned NOT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `finalized` tinyint(4) NOT NULL DEFAULT '0',
  `route` text,
  `logins` int(11) NOT NULL DEFAULT '0',
  `ip` varchar(15) DEFAULT NULL,
  `browser` text,
  `highPage` varchar(8) DEFAULT NULL,
  `final_assessment_drop` bit(1) NOT NULL DEFAULT b'0',
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `createdBy` text,
  `modified_by` char(50) DEFAULT NULL,
  `q1` tinyint(4) DEFAULT NULL,
  `q2` tinyint(4) DEFAULT NULL,
  `q3` tinyint(4) DEFAULT NULL,
  `q4` tinyint(4) DEFAULT NULL,
  `q5` tinyint(4) DEFAULT NULL,
  `q6` tinyint(4) DEFAULT NULL,
  `q7` tinyint(4) DEFAULT NULL,
  `q8` tinyint(4) DEFAULT NULL,
  `q9` tinyint(4) DEFAULT NULL,
  `q10` tinyint(4) DEFAULT NULL,
  `q11` tinyint(4) DEFAULT NULL,
  `q12` tinyint(4) DEFAULT NULL,
  `q13` tinyint(4) DEFAULT NULL,
  `q14` tinyint(4) DEFAULT NULL,
  `q15` tinyint(4) DEFAULT NULL,
  `q16` tinyint(4) DEFAULT NULL,
  `q17` tinyint(4) DEFAULT NULL,
  `q18` tinyint(4) DEFAULT NULL,
  `q19` tinyint(4) DEFAULT NULL,
  `q20` tinyint(4) DEFAULT NULL,
  `q21` tinyint(4) DEFAULT NULL,
  `q22` tinyint(4) DEFAULT NULL,
  `q23` tinyint(4) DEFAULT NULL,
  `q24` tinyint(4) DEFAULT NULL,
  `q25` tinyint(4) DEFAULT NULL,
  `q26` tinyint(4) DEFAULT NULL,
  `q27` tinyint(4) DEFAULT NULL,
  `q28` tinyint(4) DEFAULT NULL,
  `q29` tinyint(4) DEFAULT NULL,
  `q30` tinyint(4) DEFAULT NULL,
  `q31` tinyint(4) DEFAULT NULL,
  `q32` tinyint(4) DEFAULT NULL,
  `q33` tinyint(4) DEFAULT NULL,
  `q34` tinyint(4) DEFAULT NULL,
  `q35` tinyint(4) DEFAULT NULL,
  `q36` tinyint(4) DEFAULT NULL,
  `q37` tinyint(4) DEFAULT NULL,
  `q38` tinyint(4) DEFAULT NULL,
  `q39` tinyint(4) DEFAULT NULL,
  `q40` tinyint(4) DEFAULT NULL,
  `q41` tinyint(4) DEFAULT NULL,
  `q42` tinyint(4) DEFAULT NULL,
  `q43` tinyint(4) DEFAULT NULL,
  `q44` tinyint(4) DEFAULT NULL,
  `q45` tinyint(4) DEFAULT NULL,
  `q46` tinyint(4) DEFAULT NULL,
  `q47` tinyint(4) DEFAULT NULL,
  `q48` tinyint(4) DEFAULT NULL,
  `q49` tinyint(4) DEFAULT NULL,
  `q50` tinyint(4) DEFAULT NULL,
  `q51` tinyint(4) DEFAULT NULL,
  `q52` tinyint(4) DEFAULT NULL,
  `q53` tinyint(4) DEFAULT NULL,
  `q54` tinyint(4) DEFAULT NULL,
  `q55` tinyint(4) DEFAULT NULL,
  `q56` tinyint(4) DEFAULT NULL,
  `q57` tinyint(4) DEFAULT NULL,
  `q58` tinyint(4) DEFAULT NULL,
  `q59` tinyint(4) DEFAULT NULL,
  `q60` tinyint(4) DEFAULT NULL,
  `q61` tinyint(4) DEFAULT NULL,
  `q62` tinyint(4) DEFAULT NULL,
  `q63` tinyint(4) DEFAULT NULL,
  `q64a` tinyint(4) DEFAULT NULL,
  `q64b` tinyint(4) DEFAULT NULL,
  `q64c` tinyint(4) DEFAULT NULL,
  `q64d` tinyint(4) DEFAULT NULL,
  `q64e` tinyint(4) DEFAULT NULL,
  `q64f` tinyint(4) DEFAULT NULL,
  `q64g` tinyint(4) DEFAULT NULL,
  `q64h` tinyint(4) DEFAULT NULL,
  `q65` tinyint(4) DEFAULT NULL,
  `q66` tinyint(4) DEFAULT NULL,
  `q67` tinyint(4) DEFAULT NULL,
  `q68` tinyint(4) DEFAULT NULL,
  `q69a` tinyint(4) DEFAULT NULL,
  `q69b` tinyint(4) DEFAULT NULL,
  `q69c` tinyint(4) DEFAULT NULL,
  `q69d` tinyint(4) DEFAULT NULL,
  `q69e` tinyint(4) DEFAULT NULL,
  `q70a` tinyint(4) DEFAULT NULL,
  `q70b` tinyint(4) DEFAULT NULL,
  `q70c` tinyint(4) DEFAULT NULL,
  `q70d` tinyint(4) DEFAULT NULL,
  `q70e` tinyint(4) DEFAULT NULL,
  `q70f` tinyint(4) DEFAULT NULL,
  `q70g` tinyint(4) DEFAULT NULL,
  `q70h` tinyint(4) DEFAULT NULL,
  `q70i` tinyint(4) DEFAULT NULL,
  `q70j` tinyint(4) DEFAULT NULL,
  `q70k` tinyint(4) DEFAULT NULL,
  `q70l` tinyint(4) DEFAULT NULL,
  `q70m` tinyint(4) DEFAULT NULL,
  `q70n` tinyint(4) DEFAULT NULL,
  `q70text` text,
  `q71` tinyint(4) DEFAULT NULL,
  `q72` tinyint(4) DEFAULT NULL,
  `q73` tinyint(4) DEFAULT NULL,
  `q74` tinyint(4) DEFAULT NULL,
  `q75` tinyint(4) DEFAULT NULL,
  `q76` tinyint(4) DEFAULT NULL,
  `q77` tinyint(4) DEFAULT NULL,
  `q78` tinyint(4) DEFAULT NULL,
  `q79a` tinyint(4) DEFAULT NULL,
  `q79b` tinyint(4) DEFAULT NULL,
  `q79c` tinyint(4) DEFAULT NULL,
  `q79d` tinyint(4) DEFAULT NULL,
  `q79e` tinyint(4) DEFAULT NULL,
  `q79f` tinyint(4) DEFAULT NULL,
  `q79g` tinyint(4) DEFAULT NULL,
  `q79h` tinyint(4) DEFAULT NULL,
  `q79i` tinyint(4) DEFAULT NULL,
  `q79j` tinyint(4) DEFAULT NULL,
  `q79k` tinyint(4) DEFAULT NULL,
  `q79l` tinyint(4) DEFAULT NULL,
  `q79m` tinyint(4) DEFAULT NULL,
  `q79text` text,
  `q80` tinyint(4) DEFAULT NULL,
  `q81` tinyint(4) DEFAULT NULL,
  `q82` tinyint(4) DEFAULT NULL,
  `q83` tinyint(4) DEFAULT NULL,
  `q84` tinyint(4) DEFAULT NULL,
  `q85` tinyint(4) DEFAULT NULL,
  `q86` tinyint(4) DEFAULT NULL,
  `q87` tinyint(4) DEFAULT NULL,
  `q88` tinyint(4) DEFAULT NULL,
  `q89` tinyint(4) DEFAULT NULL,
  `q90` tinyint(4) DEFAULT NULL,
  `q91` text,
  `q92` varchar(10) DEFAULT NULL,
  `q93` tinyint(4) DEFAULT NULL,
  `q94` tinyint(4) DEFAULT NULL,
  `q95` tinyint(4) DEFAULT NULL,
  `q96` tinyint(4) DEFAULT NULL,
  `q97` tinyint(4) DEFAULT NULL,
  `q98` tinyint(4) DEFAULT NULL,
  `q99` tinyint(4) DEFAULT NULL,
  `q100` tinyint(4) DEFAULT NULL,
  `q101` tinyint(4) DEFAULT NULL,
  `q102` tinyint(4) DEFAULT NULL,
  `q103` tinyint(4) DEFAULT NULL,
  `q104` tinyint(4) DEFAULT NULL,
  `q105` tinyint(4) DEFAULT NULL,
  `q106` tinyint(4) DEFAULT NULL,
  `q107` tinyint(4) DEFAULT NULL,
  `q108` tinyint(4) DEFAULT NULL,
  `q109` tinyint(4) DEFAULT NULL,
  `q110` tinyint(4) DEFAULT NULL,
  `q111` tinyint(4) DEFAULT NULL,
  `q112` tinyint(4) DEFAULT NULL,
  `q113` tinyint(4) DEFAULT NULL,
  `q114a` tinyint(4) DEFAULT NULL,
  `q114b` tinyint(4) DEFAULT NULL,
  `q114c` tinyint(4) DEFAULT NULL,
  `q114d` tinyint(4) DEFAULT NULL,
  `q114e` tinyint(4) DEFAULT NULL,
  `q115a` tinyint(4) DEFAULT NULL,
  `q115b` tinyint(4) DEFAULT NULL,
  `q115c` tinyint(4) DEFAULT NULL,
  `q115d` tinyint(4) DEFAULT NULL,
  `q115e` tinyint(4) DEFAULT NULL,
  `q115f` tinyint(4) DEFAULT NULL,
  `q115text` text,
  `q116` tinyint(4) DEFAULT NULL,
  `q117` tinyint(4) DEFAULT NULL,
  `q118` tinyint(4) DEFAULT NULL,
  `q119` tinyint(4) DEFAULT NULL,
  `q120a` tinyint(4) DEFAULT NULL,
  `q120b` tinyint(4) DEFAULT NULL,
  `q120c` tinyint(4) DEFAULT NULL,
  `q120d` tinyint(4) DEFAULT NULL,
  `q120e` tinyint(4) DEFAULT NULL,
  `q121` tinyint(4) DEFAULT NULL,
  `q122` tinyint(4) DEFAULT NULL,
  `q123` tinyint(4) DEFAULT NULL,
  `q124` tinyint(4) DEFAULT NULL,
  `q125` tinyint(4) DEFAULT NULL,
  `q126` tinyint(4) DEFAULT NULL,
  `q127` tinyint(4) DEFAULT NULL,
  `q128` tinyint(4) DEFAULT NULL,
  `q129` tinyint(4) DEFAULT NULL,
  `q130` tinyint(4) DEFAULT NULL,
  `q131` tinyint(4) DEFAULT NULL,
  `q132` tinyint(4) DEFAULT NULL,
  `q133` tinyint(4) DEFAULT NULL,
  `q134` tinyint(4) DEFAULT NULL,
  `q135` tinyint(4) DEFAULT NULL,
  `q136a` tinyint(4) DEFAULT NULL,
  `q136b` tinyint(4) DEFAULT NULL,
  `q136c` tinyint(4) DEFAULT NULL,
  `q136d` tinyint(4) DEFAULT NULL,
  `q136e` tinyint(4) DEFAULT NULL,
  `q136f` tinyint(4) DEFAULT NULL,
  `q136g` tinyint(4) DEFAULT NULL,
  `q137a` tinyint(4) DEFAULT NULL,
  `q137b` tinyint(4) DEFAULT NULL,
  `q137c` tinyint(4) DEFAULT NULL,
  `q137d` tinyint(4) DEFAULT NULL,
  `q137e` tinyint(4) DEFAULT NULL,
  `q137f` tinyint(4) DEFAULT NULL,
  `q137g` tinyint(4) DEFAULT NULL,
  `q137h` tinyint(4) DEFAULT NULL,
  `q138` tinyint(4) DEFAULT NULL,
  `q139` tinyint(4) DEFAULT NULL,
  `q140` tinyint(4) DEFAULT NULL,
  `q141` tinyint(4) DEFAULT NULL,
  `q142` tinyint(4) DEFAULT NULL,
  `q143` tinyint(4) DEFAULT NULL,
  `q144` tinyint(4) DEFAULT NULL,
  `q145` tinyint(4) DEFAULT NULL,
  `q146` tinyint(4) DEFAULT NULL,
  `q147` tinyint(4) DEFAULT NULL,
  `q148` tinyint(4) DEFAULT NULL,
  `q149` tinyint(4) DEFAULT NULL,
  `q150` tinyint(4) DEFAULT NULL,
  `q151` tinyint(4) DEFAULT NULL,
  `q152` tinyint(4) DEFAULT NULL,
  `q153` tinyint(4) DEFAULT NULL,
  `q154` tinyint(4) DEFAULT NULL,
  `q155` tinyint(4) DEFAULT NULL,
  `q156` tinyint(4) DEFAULT NULL,
  `q157` tinyint(4) DEFAULT NULL,
  `q158` tinyint(4) DEFAULT NULL,
  `q159` tinyint(4) DEFAULT NULL,
  `q160` tinyint(4) DEFAULT NULL,
  `q161` tinyint(4) DEFAULT NULL,
  `q162` tinyint(4) DEFAULT NULL,
  `q163` tinyint(4) DEFAULT NULL,
  `q164` tinyint(4) DEFAULT NULL,
  `q165` tinyint(4) DEFAULT NULL,
  `q166` tinyint(4) DEFAULT NULL,
  `q167` tinyint(4) DEFAULT NULL,
  `q168` tinyint(4) DEFAULT NULL,
  `q169` tinyint(4) DEFAULT NULL,
  `q170` tinyint(4) DEFAULT NULL,
  `q171` tinyint(4) DEFAULT NULL,
  `q172` tinyint(4) DEFAULT NULL,
  `q173` tinyint(4) DEFAULT NULL,
  `q174` tinyint(4) DEFAULT NULL,
  `q175` tinyint(4) DEFAULT NULL,
  `q176` tinyint(4) DEFAULT NULL,
  `q177` tinyint(4) DEFAULT NULL,
  `q178` tinyint(4) DEFAULT NULL,
  `q179` tinyint(4) DEFAULT NULL,
  `q180` tinyint(4) DEFAULT NULL,
  `q181` tinyint(4) DEFAULT NULL,
  `q182` tinyint(4) DEFAULT NULL,
  `q183` tinyint(4) DEFAULT NULL,
  `q184` tinyint(4) DEFAULT NULL,
  `q185` tinyint(4) DEFAULT NULL,
  `q186a` tinyint(4) DEFAULT NULL,
  `q186b` tinyint(4) DEFAULT NULL,
  `q186c` tinyint(4) DEFAULT NULL,
  `q186d` tinyint(4) DEFAULT NULL,
  `q186e` tinyint(4) DEFAULT NULL,
  `q186f` tinyint(4) DEFAULT NULL,
  `q186g` tinyint(4) DEFAULT NULL,
  `q186h` tinyint(4) DEFAULT NULL,
  `q186i` tinyint(4) DEFAULT NULL,
  `q186j` tinyint(4) DEFAULT NULL,
  `q186k` tinyint(4) DEFAULT NULL,
  `q186l` tinyint(4) DEFAULT NULL,
  `q186m` tinyint(4) DEFAULT NULL,
  `q186n` tinyint(4) DEFAULT NULL,
  `q186text` text,
  `q187` tinyint(4) DEFAULT NULL,
  `q188` tinyint(4) DEFAULT NULL,
  `q189` tinyint(4) DEFAULT NULL,
  `q190` tinyint(4) DEFAULT NULL,
  `q191` tinyint(4) DEFAULT NULL,
  `q192` tinyint(4) DEFAULT NULL,
  `q193` tinyint(4) DEFAULT NULL,
  `q194` tinyint(4) DEFAULT NULL,
  `q195` tinyint(4) DEFAULT NULL,
  `q196` tinyint(4) DEFAULT NULL,
  `q197` tinyint(4) DEFAULT NULL,
  `q198` tinyint(4) DEFAULT NULL,
  `q199` tinyint(4) DEFAULT NULL,
  `q200` tinyint(4) DEFAULT NULL,
  `q201` tinyint(4) DEFAULT NULL,
  `q202` tinyint(4) DEFAULT NULL,
  `q203` tinyint(4) DEFAULT NULL,
  `q204` tinyint(4) DEFAULT NULL,
  `q205` tinyint(4) DEFAULT NULL,
  `q206` tinyint(4) DEFAULT NULL,
  `q207` tinyint(4) DEFAULT NULL,
  `q208` tinyint(4) DEFAULT NULL,
  `q209` tinyint(4) DEFAULT NULL,
  `qD1E1text` text,
  `qD1E2text` text,
  `qD1E3text` text,
  `qD1E4text` text,
  `qD1E5text` text,
  `qD2E1text` text,
  `qD2E2text` text,
  `qD2E3text` text,
  `qD3E1text` text,
  `qD3E2text` text,
  `qD3E3text` text,
  `qD4E1text` text,
  `qD4E2text` text,
  `qD4E3text` text,
  `qD4E4text` text,
  PRIMARY KEY (`id`),
  KEY `idx_coc_tbl_assess_response_finalized` (`finalized`),
  KEY `idx_coc_tbl_assess_response_final_assessment_drop` (`final_assessment_drop`),
  KEY `fk_coc_tbl_assess_response_1` (`respondent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4558 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `coc_tbl_assess_rpt_period`
--

CREATE TABLE `coc_tbl_assess_rpt_period` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_tbl_assessment`
--

CREATE TABLE `coc_tbl_assessment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(45) DEFAULT NULL,
  `coc_no` varchar(10) NOT NULL,
  `reporting_period_id` int(10) unsigned NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_coc_tbl_assessment_1` (`reporting_period_id`),
  KEY `fk_coc_tbl_assessment_2` (`coc_no`),
  CONSTRAINT `fk_coc_tbl_assessment_1` FOREIGN KEY (`reporting_period_id`) REFERENCES `coc_tbl_assess_rpt_period` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_coc_tbl_assessment_2` FOREIGN KEY (`coc_no`) REFERENCES `coc_tbl_coc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=448 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_tbl_coc`
--

CREATE TABLE `coc_tbl_coc` (
  `coc_no` char(10) NOT NULL,
  `coc_name` varchar(100) NOT NULL,
  `state_id` char(2) NOT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  PRIMARY KEY (`coc_no`),
  KEY `fk_coc_tbl_coc_1` (`state_id`),
  CONSTRAINT `fk_coc_tbl_coc_1` FOREIGN KEY (`state_id`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_tbl_contact`
--

CREATE TABLE `coc_tbl_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `coc_no` varchar(10) NOT NULL,
  `type_id` int(10) unsigned NOT NULL,
  `title` varchar(45) DEFAULT NULL,
  `first_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_coc_tbl_contact_1` (`coc_no`),
  KEY `fk_coc_tbl_contact_2` (`type_id`),
  CONSTRAINT `fk_coc_tbl_contact_1` FOREIGN KEY (`coc_no`) REFERENCES `coc_tbl_coc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_coc_tbl_contact_2` FOREIGN KEY (`type_id`) REFERENCES `coc_lku_contact_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=440 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_tbl_contact_password`
--

CREATE TABLE `coc_tbl_contact_password` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `contact_id` int(10) unsigned NOT NULL,
  `password` varchar(45) NOT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_coc_tbl_contact_password_1` (`contact_id`),
  CONSTRAINT `fk_coc_tbl_contact_password_1` FOREIGN KEY (`contact_id`) REFERENCES `coc_tbl_contact` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=439 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_tbl_lead_email`
--

CREATE TABLE `coc_tbl_lead_email` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sent_to` varchar(1000) NOT NULL,
  `subject` varchar(400) NOT NULL,
  `message` varchar(2000) NOT NULL,
  `attachments` varchar(1000) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `modified_by` char(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

--
-- Table structure for table `coc_tbl_report`
--

CREATE TABLE `coc_tbl_report` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `identifier` varchar(1000) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8;

--
-- Table structure for table `delete_log`
--

CREATE TABLE `delete_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted_id` varchar(35) DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `deleted_table` varchar(100) DEFAULT NULL,
  `deleted_type` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Table structure for table `event`
--

CREATE TABLE `event` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `start_date` date NOT NULL,
  `length` decimal(4,1) unsigned NOT NULL,
  `city` varchar(32) NOT NULL,
  `state_id` char(2) NOT NULL,
  `type_id` int(10) unsigned NOT NULL,
  `type_other` varchar(64) DEFAULT NULL,
  `purpose` mediumtext NOT NULL,
  `requesting_official` varchar(32) NOT NULL,
  `hud_sponsor` varchar(32) NOT NULL,
  `contract_responsible_party_id` int(11) unsigned NOT NULL,
  `hotel_room_blocks` tinyint(1) unsigned NOT NULL,
  `food_beverage` tinyint(1) unsigned NOT NULL,
  `contract_support_description` mediumtext NOT NULL,
  `hud_attendee_count` int(10) unsigned NOT NULL,
  `other_federal_agency_attendee_count` int(10) unsigned NOT NULL,
  `training_materials_cost` decimal(10,2) unsigned NOT NULL,
  `facility_rental_cost` decimal(10,2) unsigned NOT NULL,
  `av_equipment_cost` decimal(10,2) unsigned NOT NULL,
  `ta_provider_labor_travel` decimal(10,2) unsigned NOT NULL,
  `hud_travel_cost` decimal(10,2) unsigned NOT NULL,
  `hud_traveler_count` int(10) unsigned NOT NULL,
  `approved` tinyint(1) unsigned DEFAULT NULL COMMENT 'approved = 1 (true), denied = 0 (false), new = NULL',
  `decision_date` date DEFAULT NULL COMMENT 'used with "approved"\nindicates when the event was approved or denied',
  `requester` int(10) unsigned DEFAULT NULL,
  `request_date` date NOT NULL,
  `requesting_office` int(11) unsigned DEFAULT NULL,
  `requesting_official_email` varchar(100) DEFAULT NULL,
  `non_federal_agency_attendee_count` int(10) DEFAULT NULL,
  `staffTravelComments` mediumtext,
  `approvalNeededBy` date DEFAULT NULL,
  `approval_urgent` int(11) DEFAULT NULL,
  `approval_urgent_other` varchar(200) DEFAULT NULL,
  `requesting_official_name` varchar(100) DEFAULT NULL,
  `contact_phone` varchar(100) DEFAULT NULL,
  `estimatedCostsComments` mediumtext,
  `requestor_userID` varchar(100) DEFAULT NULL,
  `eventContractDate` date DEFAULT NULL COMMENT 'added to hold req/task 4.5 Hotel Contract Deadline',
  `eventContractDateFL` varchar(3) DEFAULT NULL COMMENT 'added to hold req/task 4.5 Hotel Contract Deadline boolean',
  PRIMARY KEY (`id`),
  KEY `fk_event_event_type` (`type_id`),
  KEY `fk_event_1` (`state_id`),
  KEY `fk_event_2` (`contract_responsible_party_id`),
  KEY `fk_approval_urgent` (`approval_urgent`),
  KEY `fk_event_requestingoffice` (`requesting_office`),
  CONSTRAINT `fk_approval_urgent` FOREIGN KEY (`approval_urgent`) REFERENCES `event_approvalby_urgentreasons` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_event_1` FOREIGN KEY (`state_id`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_event_2` FOREIGN KEY (`contract_responsible_party_id`) REFERENCES `event_contract_responsible_party` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_event_event_type` FOREIGN KEY (`type_id`) REFERENCES `event_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_event_requestingoffice` FOREIGN KEY (`requesting_office`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=294 DEFAULT CHARSET=utf8;

--
-- Table structure for table `event_approvalby_urgentreasons`
--

CREATE TABLE `event_approvalby_urgentreasons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `urgentReason` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `event_contract_responsible_party`
--

CREATE TABLE `event_contract_responsible_party` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `order_by` int(11) NOT NULL DEFAULT '0',
  `is_hud` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_event_contract_responsible_party` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='On form: Responsibility of Contract Arrangements';

--
-- Table structure for table `event_contract_support`
--

CREATE TABLE `event_contract_support` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `order_by` int(11) NOT NULL DEFAULT '0',
  `is_other` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_event_contract_support` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Table structure for table `event_event_contract_support`
--

CREATE TABLE `event_event_contract_support` (
  `event_event_contract_support_id` int(10) unsigned NOT NULL,
  `event_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`event_event_contract_support_id`,`event_id`),
  KEY `fk_event_event_contract_support_1` (`event_id`),
  KEY `fk_event_event_contract_support_2` (`event_event_contract_support_id`),
  CONSTRAINT `fk_event_event_contract_support_1` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_event_event_contract_support_2` FOREIGN KEY (`event_event_contract_support_id`) REFERENCES `event_contract_support` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `event_messagelog`
--

CREATE TABLE `event_messagelog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `eventid` int(10) unsigned NOT NULL,
  `recipients` text,
  `message` text,
  `sent_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_eventid` (`eventid`),
  CONSTRAINT `fk_eventid` FOREIGN KEY (`eventid`) REFERENCES `event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=287 DEFAULT CHARSET=utf8;

--
-- Table structure for table `event_requestingoffice`
--

CREATE TABLE `event_requestingoffice` (
  `requestingOfficeID` int(11) NOT NULL AUTO_INCREMENT,
  `requestingOfficeName` varchar(100) DEFAULT NULL,
  `requestingOfficeType` varchar(45) DEFAULT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`requestingOfficeID`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8;

--
-- Table structure for table `event_type`
--

CREATE TABLE `event_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `order_by` int(11) NOT NULL DEFAULT '0',
  `is_other` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_event_type_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `event_workplans`
--

CREATE TABLE `event_workplans` (
  `event_workplans_id` int(11) NOT NULL AUTO_INCREMENT,
  `eventID` int(10) unsigned DEFAULT NULL,
  `fileName` varchar(250) DEFAULT NULL,
  `fileSize` int(10) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `deleted` tinyint(3) DEFAULT NULL,
  PRIMARY KEY (`event_workplans_id`),
  KEY `fk_event_workplans_eventid` (`eventID`),
  CONSTRAINT `fk_event_workplans_eventid` FOREIGN KEY (`eventID`) REFERENCES `event` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=847 DEFAULT CHARSET=utf8;

--
-- Table structure for table `faq_asc_parent_filters`
--

CREATE TABLE `faq_asc_parent_filters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentID` int(11) NOT NULL,
  `filterID` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_parent_mapping_idx` (`parentID`),
  KEY `fk_filter_mapping_idx` (`filterID`),
  CONSTRAINT `fk_filter_mapping` FOREIGN KEY (`filterID`) REFERENCES `faq_lku_filters` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_parent_mapping` FOREIGN KEY (`parentID`) REFERENCES `faq_parent_mapping` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=utf8;

--
-- Table structure for table `faq_documents`
--

CREATE TABLE `faq_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contentID` char(35) NOT NULL,
  `fileTitle` varchar(255) DEFAULT NULL,
  `filePath` varchar(255) DEFAULT NULL,
  `fileName` varchar(255) DEFAULT NULL,
  `contentType` varchar(45) DEFAULT NULL,
  `contentSubType` varchar(255) DEFAULT NULL,
  `isSection508` bit(1) DEFAULT NULL,
  `sortOrder` int(11) DEFAULT '1',
  `publishedStatus` int(11) DEFAULT '0' COMMENT 'publishStatus: -1 = DELETE, 0 = DRAFT, 1 = LIVE',
  `faq_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `faq_documents_idx` (`contentID`),
  KEY `FK8AF574CFB39B91FA` (`faq_id`),
  CONSTRAINT `FK8AF574CFB39B91FA` FOREIGN KEY (`faq_id`) REFERENCES `faq_tbl_faqs` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=605 DEFAULT CHARSET=utf8 COMMENT='Table to store documents associated with an FAQ	';

--
-- Table structure for table `faq_lku_filters`
--

CREATE TABLE `faq_lku_filters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contentID` char(35) NOT NULL COMMENT 'ContentID for the Node that will have the filters.',
  `filterText` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

--
-- Table structure for table `faq_parent_mapping`
--

CREATE TABLE `faq_parent_mapping` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contentID` char(35) NOT NULL,
  `parentContentID` char(35) NOT NULL,
  `nodePath` text NOT NULL,
  `textPath` text,
  `sortOrder` int(11) NOT NULL DEFAULT '999',
  `parentOrder` int(11) DEFAULT '1',
  `faq_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `faq_content_FK_idx` (`contentID`),
  KEY `FKD5D1282B39B91FA` (`faq_id`),
  CONSTRAINT `FKD5D1282B39B91FA` FOREIGN KEY (`faq_id`) REFERENCES `faq_tbl_faqs` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8435 DEFAULT CHARSET=utf8 COMMENT='This table tracks FAQs to multiple Parent holders.';

--
-- Table structure for table `faq_related_faqs`
--

CREATE TABLE `faq_related_faqs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contentID` char(35) DEFAULT NULL,
  `faqURL` text,
  `faqTitle` varchar(255) DEFAULT NULL,
  `faqContentID` char(35) DEFAULT NULL,
  `publishedStatus` int(11) DEFAULT '0' COMMENT 'publishStatus: -1 = DELETE, 0 = DRAFT, 1 = LIVE',
  `faq_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK2AF3EA3AB39B91FA` (`faq_id`),
  CONSTRAINT `FK2AF3EA3AB39B91FA` FOREIGN KEY (`faq_id`) REFERENCES `faq_tbl_faqs` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8 COMMENT='Table of Related FAQs for a given FAQ';

--
-- Table structure for table `faq_related_links`
--

CREATE TABLE `faq_related_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contentID` char(35) DEFAULT NULL,
  `linkURL` text,
  `linkTitle` varchar(255) DEFAULT NULL,
  `publishedStatus` int(11) DEFAULT '0' COMMENT 'publishStatus: -1 = DELETE, 0 = DRAFT, 1 = LIVE',
  `faq_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK33E1813CB39B91FA` (`faq_id`),
  CONSTRAINT `FK33E1813CB39B91FA` FOREIGN KEY (`faq_id`) REFERENCES `faq_tbl_faqs` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COMMENT='Table of external links for a FAQ';

--
-- Table structure for table `faq_tbl_faqs`
--

CREATE TABLE `faq_tbl_faqs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contentid` varchar(255) DEFAULT NULL,
  `faqid` varchar(255) DEFAULT NULL,
  `question` longtext,
  `answer` longtext,
  `publisheddate` datetime DEFAULT NULL,
  `createddate` datetime DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `questionshort` longtext,
  `answershort` longtext,
  `publishedStatus` int(11) DEFAULT NULL,
  `showDocuments` bit(1) DEFAULT NULL,
  `showLinks` bit(1) DEFAULT NULL,
  `showRelatedFAQs` bit(1) DEFAULT NULL,
  `showRelatedResources` bit(1) DEFAULT NULL,
  `createdby` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2068 DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `faq_vw_getsearchtitles`
--

/*!50001 DROP VIEW IF EXISTS `faq_vw_getsearchtitles`*/;
/*!50001 CREATE TABLE `faq_vw_getsearchtitles` (
  `contentID` tinyint NOT NULL,
  `id` tinyint NOT NULL,
  `rootID` tinyint NOT NULL,
  `searchLabel` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `faq_vw_relatedprograms`
--

/*!50001 DROP VIEW IF EXISTS `faq_vw_relatedprograms`*/;
/*!50001 CREATE TABLE `faq_vw_relatedprograms` (
  `contentID` tinyint NOT NULL,
  `filters` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `gt_asc_grantee_jurisdiction`
--

CREATE TABLE `gt_asc_grantee_jurisdiction` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grantee_id` int(10) unsigned DEFAULT NULL,
  `program_id` int(10) unsigned DEFAULT NULL,
  `coc_no` char(10) DEFAULT NULL,
  `year` int(4) unsigned DEFAULT NULL,
  `nationwide` bit(1) DEFAULT b'0',
  `state` char(2) DEFAULT NULL,
  `county_id` int(10) unsigned DEFAULT NULL,
  `city_id` int(10) unsigned DEFAULT NULL,
  `lastupdated` datetime DEFAULT NULL,
  `updatedby_name` varchar(200) DEFAULT NULL,
  `lastaproved` datetime DEFAULT NULL,
  `approvedby_name` varchar(200) DEFAULT NULL,
  `active` bit(1) DEFAULT b'0',
  `request_id` int(10) unsigned DEFAULT NULL,
  `remove_request_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gt_asc_grantee_jurisdiction_unique` (`grantee_id`,`program_id`,`coc_no`,`year`,`nationwide`,`state`,`county_id`,`city_id`),
  KEY `fk_gt_asc_grantee_jurisdiction_1_idx` (`request_id`),
  KEY `fk_gt_asc_grantee_jurisdiction_2_idx` (`remove_request_id`),
  KEY `fk_gt_asc_grantee_jurisdiction_4_idx` (`coc_no`),
  KEY `fk_gt_asc_grantee_jurisdiction_5_idx` (`state`),
  KEY `fk_gt_asc_grantee_jurisdiction_6_idx` (`county_id`),
  KEY `fk_gt_asc_grantee_jurisdiction_7_idx` (`city_id`),
  KEY `fk_gt_asc_grantee_jurisdiction_3_idx` (`grantee_id`,`program_id`),
  CONSTRAINT `fk_gt_asc_grantee_jurisdiction_1` FOREIGN KEY (`request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_asc_grantee_jurisdiction_2` FOREIGN KEY (`remove_request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_asc_grantee_jurisdiction_4` FOREIGN KEY (`coc_no`) REFERENCES `c_tcoc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_asc_grantee_jurisdiction_5` FOREIGN KEY (`state`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_asc_grantee_jurisdiction_6` FOREIGN KEY (`county_id`) REFERENCES `lku_county` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_asc_grantee_jurisdiction_7` FOREIGN KEY (`city_id`) REFERENCES `lku_city_municipality` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=15100 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_asc_grantee_recipient`
--

CREATE TABLE `gt_asc_grantee_recipient` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grantee_id` int(10) unsigned NOT NULL,
  `program_id` int(10) unsigned NOT NULL,
  `recipient_grantee_id` int(10) unsigned DEFAULT NULL,
  `co_located` bit(1) DEFAULT b'0',
  `orgname` varchar(300) DEFAULT NULL,
  `website` varchar(300) DEFAULT NULL,
  `street` varchar(300) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` char(2) DEFAULT NULL,
  `zip` char(10) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `ext` varchar(10) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `assist_types` varchar(45) DEFAULT NULL,
  `lastupdated` datetime DEFAULT NULL,
  `updatedby_name` varchar(200) DEFAULT NULL,
  `lastapproved` datetime DEFAULT NULL,
  `approvedby_name` varchar(200) DEFAULT NULL,
  `active` bit(1) DEFAULT b'0',
  `request_id` int(10) unsigned DEFAULT NULL,
  `remove_request_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_asc_grantee_recipient_2_idx` (`request_id`),
  KEY `fk_gt_asc_grantee_recipient_3_idx` (`remove_request_id`),
  KEY `fk_gt_asc_grantee_recipient_4_idx` (`recipient_grantee_id`),
  KEY `fk_gt_asc_grantee_recipient_1_idx` (`grantee_id`,`program_id`),
  KEY `idx_gt_asc_grantee_recipient_active` (`active`),
  CONSTRAINT `fk_gt_asc_grantee_recipient_2` FOREIGN KEY (`request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_asc_grantee_recipient_3` FOREIGN KEY (`remove_request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_asc_grantee_recipient_4` FOREIGN KEY (`recipient_grantee_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1590 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_lku_contacttype`
--

CREATE TABLE `gt_lku_contacttype` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT 'Elected Official\nPoint of Contact\nCollaborative Applicant\nPOC for Homeless Persons\nHMIS Lead',
  `sort` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_lku_hopwa_assisttype`
--

CREATE TABLE `gt_lku_hopwa_assisttype` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `sort` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_lku_program`
--

CREATE TABLE `gt_lku_program` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `program_code` char(10) DEFAULT NULL,
  `description` text,
  `url` varchar(100) DEFAULT NULL,
  `sort` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_gt_lku_program_1` (`short_name`),
  KEY `idx_gt_lku_program_program_code` (`program_code`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_lku_program_subtype`
--

CREATE TABLE `gt_lku_program_subtype` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `program_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_lku_program_subtype_1_idx` (`program_id`),
  CONSTRAINT `fk_gt_lku_program_subtype_1` FOREIGN KEY (`program_id`) REFERENCES `gt_lku_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_lku_storytype`
--

CREATE TABLE `gt_lku_storytype` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_tbl_award`
--

CREATE TABLE `gt_tbl_award` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grantee_id` int(10) unsigned DEFAULT NULL,
  `program_id` int(10) unsigned DEFAULT NULL,
  `program_subtype_id` int(10) unsigned DEFAULT NULL,
  `coc_no` char(10) DEFAULT NULL,
  `project_name` varchar(500) DEFAULT NULL,
  `state` char(2) DEFAULT NULL,
  `year` int(4) DEFAULT NULL,
  `grant_no` varchar(20) DEFAULT NULL,
  `grant_amount` double(20,2) DEFAULT NULL,
  `obligated_amount` double(20,2) DEFAULT NULL,
  `expanded_amount` double(20,2) DEFAULT NULL,
  `lastupdated` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_tbl_award_2_idx` (`program_subtype_id`),
  KEY `fk_gt_tbl_award_3_idx` (`coc_no`),
  KEY `fk_gt_tbl_award_1_idx` (`program_id`,`grantee_id`),
  KEY `idx_gt_tbl_award_4` (`grant_no`),
  KEY `fk_gt_tbl_award_4_idx` (`grantee_id`),
  KEY `idx_gt_tbl_award_year` (`year`),
  CONSTRAINT `fk_gt_tbl_award_2` FOREIGN KEY (`program_subtype_id`) REFERENCES `gt_lku_program_subtype` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_award_3` FOREIGN KEY (`coc_no`) REFERENCES `c_tcoc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=117850 DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`mkim`@`%`*/ /*!50003 TRIGGER trg_gt_tbl_award_insert AFTER INSERT ON gt_tbl_award
FOR EACH ROW

BEGIN

IF ( new.coc_no IS NULL AND 
		0 = (SELECT COUNT(id)
			 FROM gt_asc_grantee_jurisdiction 
			 WHERE grantee_id = new.grantee_id 
				AND program_id = new.program_id
				AND year = new.year
				AND active = 1 
				AND grantee_id is not null) 
	) THEN
	INSERT INTO gt_asc_grantee_jurisdiction (
		`grantee_id`,
		`program_id`,
		`year`,
		`nationwide`,
		`state`,
		`county_id`,
		`city_id`,
		`lastupdated`,
		`updatedby_name`,
		`active`)
	SELECT 		
		`grantee_id`,
		`program_id`,
		new.year,
		`nationwide`,
		`state`,
		`county_id`,
		`city_id`,
		now(),
		'Trigger: trg_gt_tbl_award_insert',
		`active`
	FROM gt_asc_grantee_jurisdiction WHERE grantee_id = new.grantee_id 
						AND program_id = new.program_id
						AND year = (new.year - 1)
						AND active = 1
						AND grantee_id is not null; 

ELSEIF (new.grantee_id IS NULL AND 
			0 = (SELECT COUNT(id)
				 FROM gt_asc_grantee_jurisdiction 
				 WHERE coc_no = new.coc_no 
					AND year = new.year
					AND active = 1 
					AND coc_no is not null)
		) THEN
	INSERT INTO gt_asc_grantee_jurisdiction (
		`coc_no`,
		`year`,
		`nationwide`,
		`state`,
		`county_id`,
		`city_id`,
		`lastupdated`,
		`updatedby_name`,
		`active`)
	SELECT 		
		`coc_no`,
		new.year,
		`nationwide`,
		`state`,
		`county_id`,
		`city_id`,
		now(),
		'Trigger: trg_gt_tbl_award_insert',
		`active`
	FROM gt_asc_grantee_jurisdiction WHERE coc_no = new.coc_no 
						AND year = (new.year - 1)
						AND active = 1
						AND coc_no is not null; 

END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `gt_tbl_award_nsp`
--

CREATE TABLE `gt_tbl_award_nsp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grant_no` varchar(20) NOT NULL,
  `obligated_amount` double(20,2) NOT NULL,
  `expanded_amount` double(20,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_gt_tbl_award_nsp_1` (`grant_no`)
) ENGINE=InnoDB AUTO_INCREMENT=633 DEFAULT CHARSET=utf8 COMMENT='utility table for NSP award update';

--
-- Table structure for table `gt_tbl_changerequest`
--

CREATE TABLE `gt_tbl_changerequest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grantee_id` int(10) unsigned DEFAULT NULL,
  `program_id` int(10) unsigned DEFAULT NULL,
  `coc_no` char(10) DEFAULT NULL,
  `edited_id` int(10) unsigned DEFAULT NULL,
  `change_name` varchar(200) NOT NULL,
  `requesttype` char(10) NOT NULL,
  `value_at_request` text COMMENT 'value at the time of request',
  `value_at_approval` text COMMENT 'live value at the time of approval/deny',
  `value_new` text COMMENT 'proposed value',
  `requestor_comment` text,
  `reviewer_comment` text,
  `requestedby_id` char(35) NOT NULL,
  `requestedby_name` varchar(200) NOT NULL,
  `requested_date` datetime NOT NULL,
  `review_status` char(10) NOT NULL DEFAULT 'Pending' COMMENT 'Pending\nApproved\nDenied\nReviewed (for comments)',
  `reviewedby_name` varchar(200) DEFAULT NULL,
  `review_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_tbl_changerequest_1_idx` (`requestedby_id`),
  KEY `fk_gt_tbl_changerequest_1_idx1` (`program_id`),
  KEY `fk_gt_tbl_changerequest_3_idx` (`grantee_id`),
  KEY `fk_gt_tbl_changerequest_4_idx` (`coc_no`),
  CONSTRAINT `fk_gt_tbl_changerequest_1` FOREIGN KEY (`requestedby_id`) REFERENCES `tusers` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_changerequest_2` FOREIGN KEY (`program_id`) REFERENCES `gt_lku_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_changerequest_3` FOREIGN KEY (`grantee_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_changerequest_4` FOREIGN KEY (`coc_no`) REFERENCES `c_tcoc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6375 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_tbl_coc_map`
--

CREATE TABLE `gt_tbl_coc_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `coc_no` char(10) DEFAULT NULL,
  `year` int(4) NOT NULL,
  `state` char(2) NOT NULL,
  `filepath` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_tbl_coc_map_1_idx` (`coc_no`),
  KEY `fk_gt_tbl_coc_map_2_idx` (`state`),
  CONSTRAINT `fk_gt_tbl_coc_map_1` FOREIGN KEY (`coc_no`) REFERENCES `c_tcoc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_coc_map_2` FOREIGN KEY (`state`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5922 DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`mkim`@`%`*/ /*!50003 TRIGGER trg_gt_tbl_coc_map_insert AFTER INSERT ON gt_tbl_coc_map
FOR EACH ROW

BEGIN

IF ( 0 = (SELECT COUNT(id)
			 FROM gt_asc_grantee_jurisdiction 
			 WHERE coc_no = new.coc_no 
				AND year = new.year
				AND active = 1 
				AND grantee_id is null) 
	) THEN
	INSERT INTO gt_asc_grantee_jurisdiction (
		`coc_no`,
		`year`,
		`nationwide`,
		`state`,
		`county_id`,
		`city_id`,
		`lastupdated`,
		`updatedby_name`,
		`active`)
	SELECT 		
		`coc_no`,
		new.year,
		`nationwide`,
		`state`,
		`county_id`,
		`city_id`,
		now(),
		'Trigger: trg_gt_tbl_coc_map_insert',
		`active`
	FROM gt_asc_grantee_jurisdiction WHERE coc_no = new.coc_no 
						AND year = (new.year - 1)
						AND active = 1
						AND grantee_id is null; 

END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `gt_tbl_contact`
--

CREATE TABLE `gt_tbl_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grantee_id` int(10) unsigned DEFAULT NULL,
  `program_id` int(10) unsigned DEFAULT NULL,
  `coc_no` char(10) DEFAULT NULL,
  `contacttype_id` int(10) unsigned NOT NULL,
  `fname` varchar(100) DEFAULT NULL,
  `lname` varchar(100) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `contact_orgid` int(10) unsigned DEFAULT NULL,
  `co_located` bit(1) DEFAULT b'0',
  `department` varchar(200) DEFAULT NULL,
  `org_name` varchar(200) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `ext` varchar(10) DEFAULT NULL,
  `email` varchar(200) DEFAULT NULL,
  `street` varchar(300) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` char(2) DEFAULT NULL,
  `zip` char(10) DEFAULT NULL,
  `lastupdated` datetime DEFAULT NULL,
  `updatedby_name` varchar(200) DEFAULT NULL,
  `lastapproved` datetime DEFAULT NULL,
  `approvedby_name` varchar(200) DEFAULT NULL,
  `active` bit(1) DEFAULT b'0',
  `request_id` int(10) unsigned DEFAULT NULL,
  `remove_request_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_tbl_contact_1_idx` (`contacttype_id`),
  KEY `fk_gt_tbl_contact_2_idx` (`request_id`),
  KEY `fk_gt_tbl_contact_3_idx` (`remove_request_id`),
  KEY `fk_gt_tbl_contact_4_idx` (`grantee_id`),
  KEY `fk_gt_tbl_contact_5_idx` (`program_id`),
  KEY `fk_gt_tbl_contact_6_idx` (`coc_no`),
  KEY `fk_gt_tbl_contact_7_idx` (`state`),
  KEY `fk_gt_tbl_contact_8_idx` (`contact_orgid`),
  CONSTRAINT `fk_gt_tbl_contact_1` FOREIGN KEY (`contacttype_id`) REFERENCES `gt_lku_contacttype` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_contact_2` FOREIGN KEY (`request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_tbl_contact_3` FOREIGN KEY (`remove_request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_tbl_contact_4` FOREIGN KEY (`grantee_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_contact_5` FOREIGN KEY (`program_id`) REFERENCES `gt_lku_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_contact_6` FOREIGN KEY (`coc_no`) REFERENCES `c_tcoc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_contact_7` FOREIGN KEY (`state`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_contact_8` FOREIGN KEY (`contact_orgid`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=12364 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_tbl_grantee_location`
--

CREATE TABLE `gt_tbl_grantee_location` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grantee_id` int(10) unsigned NOT NULL,
  `program_id` int(10) unsigned NOT NULL,
  `co_located` bit(1) DEFAULT b'0',
  `street` varchar(300) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` char(2) DEFAULT NULL,
  `zip` char(10) DEFAULT NULL,
  `lastupdated` datetime DEFAULT NULL,
  `updatedby_name` varchar(200) DEFAULT NULL,
  `lastapproved` datetime DEFAULT NULL,
  `approvedby_name` varchar(200) DEFAULT NULL,
  `active` bit(1) DEFAULT b'0',
  `request_id` int(10) unsigned DEFAULT NULL,
  `remove_request_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_tbl_grantee_location_2_idx` (`request_id`),
  KEY `fk_gt_tbl_grantee_location_3_idx` (`state`),
  KEY `fk_gt_tbl_grantee_location_1_idx` (`grantee_id`,`program_id`),
  KEY `fk_gt_tbl_grantee_location_4_idx` (`remove_request_id`),
  CONSTRAINT `fk_gt_tbl_grantee_location_2` FOREIGN KEY (`request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_tbl_grantee_location_3` FOREIGN KEY (`state`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_grantee_location_4` FOREIGN KEY (`remove_request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=218 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_tbl_grantee_map`
--

CREATE TABLE `gt_tbl_grantee_map` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grantee_id` int(10) unsigned NOT NULL,
  `program_id` int(10) unsigned NOT NULL,
  `year` int(4) NOT NULL,
  `state` char(2) DEFAULT NULL,
  `filepath` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_tbl_grantee_map_1_idx` (`grantee_id`),
  KEY `fk_gt_tbl_grantee_map_2_idx` (`program_id`),
  KEY `fk_gt_tbl_grantee_map_3_idx` (`state`),
  CONSTRAINT `fk_gt_tbl_grantee_map_1` FOREIGN KEY (`grantee_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_grantee_map_2` FOREIGN KEY (`program_id`) REFERENCES `gt_lku_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_grantee_map_3` FOREIGN KEY (`state`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`mkim`@`%`*/ /*!50003 TRIGGER trg_gt_tbl_grantee_map_insert AFTER INSERT ON gt_tbl_grantee_map
FOR EACH ROW

BEGIN

IF ( 0 = (SELECT COUNT(id)
			 FROM gt_asc_grantee_jurisdiction 
			 WHERE grantee_id = new.grantee_id 
				AND program_id = new.program_id
				AND year = new.year
				AND active = 1 
				AND coc_no is null) 
	) THEN
	INSERT INTO gt_asc_grantee_jurisdiction (
		`grantee_id`,
		`program_id`,
		`year`,
		`nationwide`,
		`state`,
		`county_id`,
		`city_id`,
		`lastupdated`,
		`updatedby_name`,
		`active`)
	SELECT 		
		`grantee_id`,
		`program_id`,
		new.year,
		`nationwide`,
		`state`,
		`county_id`,
		`city_id`,
		now(),
		'Trigger: trg_gt_tbl_grantee_map_insert',
		`active`
	FROM gt_asc_grantee_jurisdiction WHERE grantee_id = new.grantee_id 
						AND program_id = new.program_id
						AND year = (new.year - 1)
						AND active = 1
						AND grantee_id is not null; 

END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `gt_tbl_granteeinfo`
--

CREATE TABLE `gt_tbl_granteeinfo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grantee_id` int(10) unsigned NOT NULL,
  `website` varchar(500) DEFAULT NULL,
  `lastupdated` datetime DEFAULT NULL,
  `updatedby_name` varchar(200) DEFAULT NULL,
  `lastapproved` datetime DEFAULT NULL,
  `approvedby_name` varchar(200) DEFAULT NULL,
  `active` bit(1) DEFAULT b'0',
  `request_id` int(10) unsigned DEFAULT NULL,
  `remove_request_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_tbl_granteeinfo_1_idx` (`request_id`),
  KEY `fk_gt_tbl_granteeinfo_2_idx` (`remove_request_id`),
  KEY `fk_gt_tbl_granteeinfo_3_idx` (`grantee_id`),
  CONSTRAINT `fk_gt_tbl_granteeinfo_1` FOREIGN KEY (`request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_tbl_granteeinfo_2` FOREIGN KEY (`remove_request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_tbl_granteeinfo_3` FOREIGN KEY (`grantee_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1443 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_tbl_note`
--

CREATE TABLE `gt_tbl_note` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grantee_id` int(10) DEFAULT NULL,
  `coc_no` char(10) DEFAULT NULL,
  `notes` mediumtext,
  `lastupdated` datetime DEFAULT NULL,
  `updatedby_name` varchar(200) DEFAULT NULL,
  `lastapproved` datetime DEFAULT NULL,
  `approvedby_name` varchar(200) DEFAULT NULL,
  `active` bit(1) DEFAULT NULL,
  `request_id` int(10) unsigned DEFAULT NULL,
  `remove_request_id` int(10) unsigned DEFAULT NULL,
  `original_approve_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_tbl_grantee_note_1_idx` (`request_id`),
  KEY `fk_gt_tbl_grantee_note_2_idx` (`remove_request_id`),
  CONSTRAINT `fk_gt_tbl_grantee_note_1` FOREIGN KEY (`request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_tbl_grantee_note_2` FOREIGN KEY (`remove_request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

--
-- Table structure for table `gt_tbl_story`
--

CREATE TABLE `gt_tbl_story` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grantee_id` int(10) unsigned DEFAULT NULL,
  `program_id` int(10) unsigned DEFAULT NULL,
  `coc_no` char(10) DEFAULT NULL,
  `storytype_id` int(10) unsigned NOT NULL,
  `title` varchar(200) NOT NULL,
  `description` text,
  `file_path` varchar(500) DEFAULT NULL,
  `file_name` varchar(200) DEFAULT NULL,
  `mime_type` varchar(200) DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `sort` int(10) DEFAULT NULL,
  `lastupdated` datetime DEFAULT NULL,
  `updatedby_name` varchar(200) DEFAULT NULL,
  `lastapproved` datetime DEFAULT NULL,
  `approvedby_name` varchar(200) DEFAULT NULL,
  `active` bit(1) DEFAULT b'0',
  `request_id` int(10) unsigned DEFAULT NULL,
  `remove_request_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_gt_tbl_story_1_idx` (`request_id`),
  KEY `fk_gt_tbl_story_2_idx` (`remove_request_id`),
  KEY `fk_gt_tbl_story_3_idx` (`storytype_id`),
  KEY `fk_gt_tbl_story_5_idx` (`coc_no`),
  KEY `fk_gt_tbl_story_4_idx` (`grantee_id`,`program_id`),
  CONSTRAINT `fk_gt_tbl_story_1` FOREIGN KEY (`request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_tbl_story_2` FOREIGN KEY (`remove_request_id`) REFERENCES `gt_tbl_changerequest` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_gt_tbl_story_3` FOREIGN KEY (`storytype_id`) REFERENCES `gt_lku_storytype` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_gt_tbl_story_5` FOREIGN KEY (`coc_no`) REFERENCES `c_tcoc` (`coc_no`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=328 DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `gt_vw_coc_page`
--

/*!50001 DROP VIEW IF EXISTS `gt_vw_coc_page`*/;
/*!50001 CREATE TABLE `gt_vw_coc_page` (
  `coc_no` tinyint NOT NULL,
  `filename` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `gt_vw_coc_report`
--

/*!50001 DROP VIEW IF EXISTS `gt_vw_coc_report`*/;
/*!50001 CREATE TABLE `gt_vw_coc_report` (
  `path` tinyint NOT NULL,
  `report_display_name` tinyint NOT NULL,
  `doctype` tinyint NOT NULL,
  `year` tinyint NOT NULL,
  `periodEnd` tinyint NOT NULL,
  `coc_no` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `gt_vw_coc_year`
--

/*!50001 DROP VIEW IF EXISTS `gt_vw_coc_year`*/;
/*!50001 CREATE TABLE `gt_vw_coc_year` (
  `coc_no` tinyint NOT NULL,
  `year` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `gt_vw_grantee_page`
--

/*!50001 DROP VIEW IF EXISTS `gt_vw_grantee_page`*/;
/*!50001 CREATE TABLE `gt_vw_grantee_page` (
  `grantee_id` tinyint NOT NULL,
  `filename` tinyint NOT NULL,
  `lastUpdate` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `gt_vw_grantee_program`
--

/*!50001 DROP VIEW IF EXISTS `gt_vw_grantee_program`*/;
/*!50001 CREATE TABLE `gt_vw_grantee_program` (
  `grantee_id` tinyint NOT NULL,
  `program_id` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `gt_vw_grantee_program_year`
--

/*!50001 DROP VIEW IF EXISTS `gt_vw_grantee_program_year`*/;
/*!50001 CREATE TABLE `gt_vw_grantee_program_year` (
  `grantee_id` tinyint NOT NULL,
  `year` tinyint NOT NULL,
  `program_id` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `gt_vw_grantee_report`
--

/*!50001 DROP VIEW IF EXISTS `gt_vw_grantee_report`*/;
/*!50001 CREATE TABLE `gt_vw_grantee_report` (
  `path` tinyint NOT NULL,
  `report_display_name` tinyint NOT NULL,
  `doctype` tinyint NOT NULL,
  `year` tinyint NOT NULL,
  `periodEnd` tinyint NOT NULL,
  `coc_no` tinyint NOT NULL,
  `grantee` tinyint NOT NULL,
  `grantee_id` tinyint NOT NULL,
  `program` tinyint NOT NULL,
  `program_id` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `gt_vw_grantee_report_year`
--

/*!50001 DROP VIEW IF EXISTS `gt_vw_grantee_report_year`*/;
/*!50001 CREATE TABLE `gt_vw_grantee_report_year` (
  `year` tinyint NOT NULL,
  `grantee_id` tinyint NOT NULL,
  `program_id` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `gt_vw_grantee_year`
--

/*!50001 DROP VIEW IF EXISTS `gt_vw_grantee_year`*/;
/*!50001 CREATE TABLE `gt_vw_grantee_year` (
  `grantee_id` tinyint NOT NULL,
  `grantee_name` tinyint NOT NULL,
  `year` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `gt_vw_hopwa_locations`
--

/*!50001 DROP VIEW IF EXISTS `gt_vw_hopwa_locations`*/;
/*!50001 CREATE TABLE `gt_vw_hopwa_locations` (
  `grantee_id` tinyint NOT NULL,
  `grantee_name` tinyint NOT NULL,
  `street` tinyint NOT NULL,
  `city` tinyint NOT NULL,
  `state` tinyint NOT NULL,
  `zip` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `hudx_office_categories`
--

CREATE TABLE `hudx_office_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `officeid` int(11) NOT NULL DEFAULT '0',
  `categoryid` char(35) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_hudx_offic_to_cats_offices` (`officeid`),
  KEY `fk_hudx_offic_to_cats_categories` (`categoryid`),
  CONSTRAINT `fk_hudx_offic_to_cats_categories` FOREIGN KEY (`categoryid`) REFERENCES `tcontentcategories` (`categoryID`),
  CONSTRAINT `fk_hudx_offic_to_cats_offices` FOREIGN KEY (`officeid`) REFERENCES `hudx_offices` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=168 DEFAULT CHARSET=utf8;

--
-- Table structure for table `hudx_offices`
--

CREATE TABLE `hudx_offices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `abbreviation` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Table structure for table `icfi_error_details`
--

CREATE TABLE `icfi_error_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `errorid` int(11) NOT NULL COMMENT 'FK to the Error',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user` char(35) DEFAULT NULL,
  `plugin` varchar(255) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `method` varchar(255) DEFAULT NULL,
  `message` varchar(255) DEFAULT NULL COMMENT 'exception message',
  `detail` varchar(255) DEFAULT NULL COMMENT 'exception detail',
  `line` int(11) DEFAULT NULL COMMENT 'exception line #',
  `template` varchar(255) DEFAULT NULL COMMENT 'file where the exception was thrown.',
  `rawurl` longtext,
  `rawform` longtext,
  `rawcgi` longtext,
  PRIMARY KEY (`id`),
  KEY `icfi_error_details_to_error_fk` (`errorid`),
  CONSTRAINT `icfi_error_details_to_error_fk` FOREIGN KEY (`errorid`) REFERENCES `icfi_errors` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `icfi_errors`
--

CREATE TABLE `icfi_errors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hash` varchar(255) NOT NULL COMMENT 'Unique hash of error',
  `rawdata` longtext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `icfi_errors_id_uindex` (`id`),
  UNIQUE KEY `icfi_errors_hash_uindex` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `iec_lku_annual_income_type`
--

CREATE TABLE `iec_lku_annual_income_type` (
  `annual_income_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `short_name` varchar(8) NOT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `order_by` int(11) DEFAULT '0',
  PRIMARY KEY (`annual_income_type_id`),
  KEY `order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_anticipated_income_type`
--

CREATE TABLE `iec_lku_anticipated_income_type` (
  `anticipated_income_type` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `order_by` int(11) DEFAULT '0',
  PRIMARY KEY (`anticipated_income_type`),
  KEY `order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_asset_type`
--

CREATE TABLE `iec_lku_asset_type` (
  `asset_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `order_by` int(11) DEFAULT '0',
  PRIMARY KEY (`asset_type_id`),
  KEY `order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_assistance_type`
--

CREATE TABLE `iec_lku_assistance_type` (
  `assistance_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` varchar(45) DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` varchar(45) DEFAULT NULL,
  `deleted` tinyint(1) unsigned DEFAULT NULL,
  `order_by` int(11) DEFAULT '0',
  PRIMARY KEY (`assistance_type_id`),
  KEY `order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_cdbg_objective`
--

CREATE TABLE `iec_lku_cdbg_objective` (
  `cdbg_objective_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cdbg_objective` varchar(64) DEFAULT NULL,
  `cdbg_objective_short_name` varchar(64) DEFAULT NULL,
  `order_by` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`cdbg_objective_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_esg_component`
--

CREATE TABLE `iec_lku_esg_component` (
  `esg_component_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `esg_component` varchar(64) DEFAULT NULL,
  `esg_component_short_name` varchar(64) DEFAULT NULL,
  `order_by` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`esg_component_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_flow`
--

CREATE TABLE `iec_lku_flow` (
  `flow_id` varchar(32) NOT NULL,
  `exclude_minors_under_15_from_household_income` tinyint(1) unsigned DEFAULT NULL,
  `order_by` int(11) DEFAULT '0',
  PRIMARY KEY (`flow_id`),
  KEY `order_by` (`order_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_frequency`
--

CREATE TABLE `iec_lku_frequency` (
  `frequency_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `multiplier` tinyint(3) unsigned DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `order_by` int(11) DEFAULT '0',
  PRIMARY KEY (`frequency_id`),
  KEY `order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_income_limit`
--

CREATE TABLE `iec_lku_income_limit` (
  `income_limit_id` int(11) NOT NULL AUTO_INCREMENT,
  `income_limit_year` int(10) unsigned DEFAULT NULL,
  `state_id` int(11) DEFAULT NULL,
  `cbsa_sub` varchar(32) DEFAULT NULL,
  `area` varchar(128) DEFAULT NULL,
  `lim30_p1` int(11) DEFAULT NULL,
  `lim30_p2` int(11) DEFAULT NULL,
  `lim30_p3` int(11) DEFAULT NULL,
  `lim30_p4` int(11) DEFAULT NULL,
  `lim30_p5` int(11) DEFAULT NULL,
  `lim30_p6` int(11) DEFAULT NULL,
  `lim30_p7` int(11) DEFAULT NULL,
  `lim30_p8` int(11) DEFAULT NULL,
  `lim30_p9` int(11) DEFAULT NULL,
  `lim30_p10` int(11) DEFAULT NULL,
  `lim30_p11` int(11) DEFAULT NULL,
  `lim30_p12` int(11) DEFAULT NULL,
  `lim50_p1` int(11) DEFAULT NULL,
  `lim50_p2` int(11) DEFAULT NULL,
  `lim50_p3` int(11) DEFAULT NULL,
  `lim50_p4` int(11) DEFAULT NULL,
  `lim50_p5` int(11) DEFAULT NULL,
  `lim50_p6` int(11) DEFAULT NULL,
  `lim50_p7` int(11) DEFAULT NULL,
  `lim50_p8` int(11) DEFAULT NULL,
  `lim50_p9` int(11) DEFAULT NULL,
  `lim50_p10` int(11) DEFAULT NULL,
  `lim50_p11` int(11) DEFAULT NULL,
  `lim50_p12` int(11) DEFAULT NULL,
  `lim60_p1` int(11) DEFAULT NULL,
  `lim60_p2` int(11) DEFAULT NULL,
  `lim60_p3` int(11) DEFAULT NULL,
  `lim60_p4` int(11) DEFAULT NULL,
  `lim60_p5` int(11) DEFAULT NULL,
  `lim60_p6` int(11) DEFAULT NULL,
  `lim60_p7` int(11) DEFAULT NULL,
  `lim60_p8` int(11) DEFAULT NULL,
  `lim60_p9` int(11) DEFAULT NULL,
  `lim60_p10` int(11) DEFAULT NULL,
  `lim60_p11` int(11) DEFAULT NULL,
  `lim60_p12` int(11) DEFAULT NULL,
  `lim80_p1` int(11) DEFAULT NULL,
  `lim80_p2` int(11) DEFAULT NULL,
  `lim80_p3` int(11) DEFAULT NULL,
  `lim80_p4` int(11) DEFAULT NULL,
  `lim80_p5` int(11) DEFAULT NULL,
  `lim80_p6` int(11) DEFAULT NULL,
  `lim80_p7` int(11) DEFAULT NULL,
  `lim80_p8` int(11) DEFAULT NULL,
  `lim80_p9` int(11) DEFAULT NULL,
  `lim80_p10` int(11) DEFAULT NULL,
  `lim80_p11` int(11) DEFAULT NULL,
  `lim80_p12` int(11) DEFAULT NULL,
  `lim120_p1` int(11) DEFAULT NULL,
  `lim120_p2` int(11) DEFAULT NULL,
  `lim120_p3` int(11) DEFAULT NULL,
  `lim120_p4` int(11) DEFAULT NULL,
  `lim120_p5` int(11) DEFAULT NULL,
  `lim120_p6` int(11) DEFAULT NULL,
  `lim120_p7` int(11) DEFAULT NULL,
  `lim120_p8` int(11) DEFAULT NULL,
  `lim120_p9` int(11) DEFAULT NULL,
  `lim120_p10` int(11) DEFAULT NULL,
  `lim120_p11` int(11) DEFAULT NULL,
  `lim120_p12` int(11) DEFAULT NULL,
  `state` varchar(32) DEFAULT NULL,
  `home_flag` tinyint(1) DEFAULT '0',
  `htf_flag` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`income_limit_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15765 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_income_limit_bak_04272016`
--

CREATE TABLE `iec_lku_income_limit_bak_04272016` (
  `income_limit_id` int(11) NOT NULL AUTO_INCREMENT,
  `income_limit_year` int(10) unsigned DEFAULT NULL,
  `state_id` int(11) DEFAULT NULL,
  `cbsa_sub` varchar(32) DEFAULT NULL,
  `area` varchar(128) DEFAULT NULL,
  `lim30_p1` int(11) DEFAULT NULL,
  `lim30_p2` int(11) DEFAULT NULL,
  `lim30_p3` int(11) DEFAULT NULL,
  `lim30_p4` int(11) DEFAULT NULL,
  `lim30_p5` int(11) DEFAULT NULL,
  `lim30_p6` int(11) DEFAULT NULL,
  `lim30_p7` int(11) DEFAULT NULL,
  `lim30_p8` int(11) DEFAULT NULL,
  `lim30_p9` int(11) DEFAULT NULL,
  `lim30_p10` int(11) DEFAULT NULL,
  `lim30_p11` int(11) DEFAULT NULL,
  `lim30_p12` int(11) DEFAULT NULL,
  `lim50_p1` int(11) DEFAULT NULL,
  `lim50_p2` int(11) DEFAULT NULL,
  `lim50_p3` int(11) DEFAULT NULL,
  `lim50_p4` int(11) DEFAULT NULL,
  `lim50_p5` int(11) DEFAULT NULL,
  `lim50_p6` int(11) DEFAULT NULL,
  `lim50_p7` int(11) DEFAULT NULL,
  `lim50_p8` int(11) DEFAULT NULL,
  `lim50_p9` int(11) DEFAULT NULL,
  `lim50_p10` int(11) DEFAULT NULL,
  `lim50_p11` int(11) DEFAULT NULL,
  `lim50_p12` int(11) DEFAULT NULL,
  `lim60_p1` int(11) DEFAULT NULL,
  `lim60_p2` int(11) DEFAULT NULL,
  `lim60_p3` int(11) DEFAULT NULL,
  `lim60_p4` int(11) DEFAULT NULL,
  `lim60_p5` int(11) DEFAULT NULL,
  `lim60_p6` int(11) DEFAULT NULL,
  `lim60_p7` int(11) DEFAULT NULL,
  `lim60_p8` int(11) DEFAULT NULL,
  `lim60_p9` int(11) DEFAULT NULL,
  `lim60_p10` int(11) DEFAULT NULL,
  `lim60_p11` int(11) DEFAULT NULL,
  `lim60_p12` int(11) DEFAULT NULL,
  `lim80_p1` int(11) DEFAULT NULL,
  `lim80_p2` int(11) DEFAULT NULL,
  `lim80_p3` int(11) DEFAULT NULL,
  `lim80_p4` int(11) DEFAULT NULL,
  `lim80_p5` int(11) DEFAULT NULL,
  `lim80_p6` int(11) DEFAULT NULL,
  `lim80_p7` int(11) DEFAULT NULL,
  `lim80_p8` int(11) DEFAULT NULL,
  `lim80_p9` int(11) DEFAULT NULL,
  `lim80_p10` int(11) DEFAULT NULL,
  `lim80_p11` int(11) DEFAULT NULL,
  `lim80_p12` int(11) DEFAULT NULL,
  `lim120_p1` int(11) DEFAULT NULL,
  `lim120_p2` int(11) DEFAULT NULL,
  `lim120_p3` int(11) DEFAULT NULL,
  `lim120_p4` int(11) DEFAULT NULL,
  `lim120_p5` int(11) DEFAULT NULL,
  `lim120_p6` int(11) DEFAULT NULL,
  `lim120_p7` int(11) DEFAULT NULL,
  `lim120_p8` int(11) DEFAULT NULL,
  `lim120_p9` int(11) DEFAULT NULL,
  `lim120_p10` int(11) DEFAULT NULL,
  `lim120_p11` int(11) DEFAULT NULL,
  `lim120_p12` int(11) DEFAULT NULL,
  `state` varchar(32) DEFAULT NULL,
  `home_flag` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`income_limit_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10477 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_page_meta_info`
--

CREATE TABLE `iec_lku_page_meta_info` (
  `page_meta_info_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `section` varchar(64) DEFAULT NULL,
  `item` varchar(64) DEFAULT NULL,
  `fw1_action` varchar(256) DEFAULT NULL,
  `form_id` varchar(256) DEFAULT NULL,
  `submit_btn_template` varchar(64) DEFAULT NULL,
  `section_title` varchar(128) DEFAULT NULL,
  `page_title` varchar(128) DEFAULT NULL,
  `left_nav_section` varchar(64) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  PRIMARY KEY (`page_meta_info_id`),
  KEY `idx_fw1_action` (`fw1_action`(255))
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8 COMMENT='Info about a page and its form';

--
-- Table structure for table `iec_lku_program`
--

CREATE TABLE `iec_lku_program` (
  `program_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `short_name` varchar(32) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `program_flow_path` varchar(16) DEFAULT NULL,
  `order_by` int(11) DEFAULT '0',
  PRIMARY KEY (`program_id`),
  KEY `order_by` (`order_by`),
  KEY `fk_iec_lku_program_1` (`program_flow_path`),
  CONSTRAINT `fk_iec_lku_program_1` FOREIGN KEY (`program_flow_path`) REFERENCES `iec_lku_flow` (`flow_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_program_assistance_type`
--

CREATE TABLE `iec_lku_program_assistance_type` (
  `pat_id` int(11) NOT NULL AUTO_INCREMENT,
  `program_id` int(11) DEFAULT NULL,
  `assistance_type_id` int(11) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `order_by` int(11) DEFAULT '0',
  PRIMARY KEY (`pat_id`),
  KEY `pat_program_id` (`program_id`),
  KEY `pat_assistance_type_id` (`assistance_type_id`),
  KEY `order_by` (`order_by`),
  CONSTRAINT `pat_assistance_type_id` FOREIGN KEY (`assistance_type_id`) REFERENCES `iec_lku_assistance_type` (`assistance_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `pat_program_id` FOREIGN KEY (`program_id`) REFERENCES `iec_lku_program` (`program_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_lku_status`
--

CREATE TABLE `iec_lku_status` (
  `status_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `order_by` int(11) DEFAULT '0',
  PRIMARY KEY (`status_id`),
  KEY `order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_tbl_1040`
--

CREATE TABLE `iec_tbl_1040` (
  `1040_id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `wages` decimal(10,2) DEFAULT NULL,
  `overtime` decimal(10,2) DEFAULT NULL,
  `raises` decimal(10,2) DEFAULT NULL,
  `taxable_interest` decimal(10,2) DEFAULT NULL,
  `ordinary_dividends` decimal(10,2) DEFAULT NULL,
  `taxable_refunds` decimal(10,2) DEFAULT NULL,
  `alimony_received` decimal(10,2) DEFAULT NULL,
  `business_income` decimal(10,2) DEFAULT NULL,
  `capital_gains` decimal(10,2) DEFAULT NULL,
  `other_gains` decimal(10,2) DEFAULT NULL,
  `taxable_amt_ira` decimal(10,2) DEFAULT NULL,
  `taxable_amt_pension` decimal(10,2) DEFAULT NULL,
  `rental_real_estate` decimal(10,2) DEFAULT NULL,
  `farm_income` decimal(10,2) DEFAULT NULL,
  `unemployment_comp` decimal(10,2) DEFAULT NULL,
  `taxable_social_security_amt` decimal(10,2) DEFAULT NULL,
  `other_income` decimal(10,2) DEFAULT NULL,
  `educator_exp` decimal(10,2) DEFAULT NULL,
  `certain_business_exp` decimal(10,2) DEFAULT NULL,
  `health_savings` decimal(10,2) DEFAULT NULL,
  `moving_exp` decimal(10,2) DEFAULT NULL,
  `deductible_self_emp_exp` decimal(10,2) DEFAULT NULL,
  `self_emp_sep` decimal(10,2) DEFAULT NULL,
  `self_emp_self` decimal(10,2) DEFAULT NULL,
  `penalty` decimal(10,2) DEFAULT NULL,
  `alimony_paid` decimal(10,2) DEFAULT NULL,
  `ira_deduction` decimal(10,2) DEFAULT NULL,
  `student_loans` decimal(10,2) DEFAULT NULL,
  `tuition_fees` decimal(10,2) DEFAULT NULL,
  `domestic_deduction` decimal(10,2) DEFAULT NULL,
  `other_deduction` decimal(10,2) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`1040_id`),
  KEY `1040_member_id` (`member_id`),
  CONSTRAINT `1040_member_id` FOREIGN KEY (`member_id`) REFERENCES `iec_tbl_member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=23652 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_tbl_acs`
--

CREATE TABLE `iec_tbl_acs` (
  `acs_id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `wages` decimal(10,2) DEFAULT NULL,
  `self_employment_income` decimal(10,2) DEFAULT NULL,
  `interest` decimal(10,2) DEFAULT NULL,
  `social_security` decimal(10,2) DEFAULT NULL,
  `ssi` decimal(10,2) DEFAULT NULL,
  `local_welfare_office` decimal(10,2) DEFAULT NULL,
  `retirement` decimal(10,2) DEFAULT NULL,
  `other_income` decimal(10,2) DEFAULT NULL,
  `annual_income_total` decimal(10,2) DEFAULT NULL COMMENT 'TODO: Compute',
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`acs_id`),
  KEY `acs_member_id` (`member_id`),
  CONSTRAINT `acs_member_id` FOREIGN KEY (`member_id`) REFERENCES `iec_tbl_member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3942 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_tbl_adjusted_income`
--

CREATE TABLE `iec_tbl_adjusted_income` (
  `adjusted_income_id` int(11) NOT NULL AUTO_INCREMENT,
  `household_id` int(11) DEFAULT NULL,
  `dependents` int(11) DEFAULT NULL,
  `ant_unreimbursed_cc_exp` decimal(10,2) DEFAULT NULL,
  `ant_unreimbursed_disability_exp` decimal(10,2) DEFAULT NULL,
  `child_care_expenses_deduction` decimal(10,2) DEFAULT NULL COMMENT 'todo: calculate:\n1.       If HOME 19 > Selected Member’s Annual Income, then #4 = Selected Member’s Annual Income\n2.       If HOME 19 < Selected Member’s Annual Income, then #4 = HOME 19 (Childcare Expenses)',
  `ant_unreimbursed_medical_exp` decimal(10,2) DEFAULT NULL,
  `adjusted_income` decimal(10,2) DEFAULT NULL,
  `status_id` int(11) DEFAULT NULL,
  `last_saved_section` varchar(64) DEFAULT NULL,
  `last_saved_item` varchar(64) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`adjusted_income_id`),
  KEY `adjusted_income_household` (`household_id`),
  KEY `adjusted_income_status` (`status_id`),
  CONSTRAINT `adjusted_income_household` FOREIGN KEY (`household_id`) REFERENCES `iec_tbl_household` (`household_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `adjusted_income_status` FOREIGN KEY (`status_id`) REFERENCES `iec_lku_status` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=18942 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_tbl_household`
--

CREATE TABLE `iec_tbl_household` (
  `household_id` int(11) NOT NULL AUTO_INCREMENT,
  `cdbg_objective_id` int(10) unsigned DEFAULT NULL,
  `beneficiary_id` varchar(45) NOT NULL,
  `site_user_id` char(35) DEFAULT NULL,
  `program_id` int(11) NOT NULL,
  `pat_id` int(11) DEFAULT NULL,
  `members_no` int(11) DEFAULT NULL COMMENT 'this is the authoritive member count, because it can be set before there are ever any member rows are created.',
  `state` varchar(45) DEFAULT NULL,
  `area_id` int(11) DEFAULT NULL,
  `area` varchar(128) DEFAULT NULL,
  `income_limit_pct` int(11) DEFAULT NULL,
  `income_limit_amt` decimal(10,2) DEFAULT NULL,
  `is_income_eligible_prev` tinyint(1) unsigned DEFAULT NULL,
  `date_income_eligible_comp` datetime DEFAULT NULL,
  `recertification_other_years` tinyint(1) unsigned DEFAULT NULL,
  `new_household` tinyint(1) unsigned DEFAULT NULL,
  `low_tax_credit_involved` tinyint(1) unsigned DEFAULT NULL,
  `determination_method` varchar(32) DEFAULT NULL COMMENT 'Only used by HOPWA:\nExamine source documentation\nObtain a written statement from the family \nObtain a written statement from a government program administrator',
  `hud_passbook_rate` float DEFAULT NULL,
  `annual_income_type_id` int(11) DEFAULT NULL,
  `part5_method` varchar(45) DEFAULT NULL COMMENT 'this stores the user''s choice of part 5: (short|guided)',
  `status_id` int(11) DEFAULT NULL,
  `is_eligible` tinyint(1) unsigned DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `last_saved_section` varchar(64) DEFAULT NULL,
  `last_saved_item` varchar(64) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `annual_income` decimal(10,2) DEFAULT NULL COMMENT 'TODO: Calculate',
  `income_definition` varchar(32) DEFAULT NULL,
  `esg_component_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`household_id`),
  KEY `household_program_id` (`program_id`),
  KEY `household_pat_id` (`pat_id`),
  KEY `household_status_id` (`status_id`),
  KEY `household_annual_income_type_id` (`annual_income_type_id`),
  KEY `household_area_id` (`area_id`),
  KEY `household_cdbg_objective_id` (`cdbg_objective_id`),
  KEY `household_user_id` (`site_user_id`),
  KEY `esg_component_id` (`esg_component_id`),
  CONSTRAINT `household_annual_income_type_id` FOREIGN KEY (`annual_income_type_id`) REFERENCES `iec_lku_annual_income_type` (`annual_income_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `household_area_id` FOREIGN KEY (`area_id`) REFERENCES `iec_lku_income_limit` (`income_limit_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `household_cdbg_objective_id` FOREIGN KEY (`cdbg_objective_id`) REFERENCES `iec_lku_cdbg_objective` (`cdbg_objective_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `household_esg_component_id` FOREIGN KEY (`esg_component_id`) REFERENCES `iec_lku_esg_component` (`esg_component_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `household_pat_id` FOREIGN KEY (`pat_id`) REFERENCES `iec_lku_program_assistance_type` (`pat_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `household_program_id` FOREIGN KEY (`program_id`) REFERENCES `iec_lku_program` (`program_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `household_status_id` FOREIGN KEY (`status_id`) REFERENCES `iec_lku_status` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `household_user_id` FOREIGN KEY (`site_user_id`) REFERENCES `tusers` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=112942 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_tbl_member`
--

CREATE TABLE `iec_tbl_member` (
  `member_id` int(11) NOT NULL AUTO_INCREMENT,
  `member_unique_id` varchar(8) NOT NULL,
  `household_id` int(11) DEFAULT NULL,
  `is_member_below_15` tinyint(1) unsigned DEFAULT NULL,
  `is_member_below_18` tinyint(1) unsigned DEFAULT NULL,
  `is_member_over_18` tinyint(1) unsigned DEFAULT NULL,
  `is_member_over_62` tinyint(1) unsigned DEFAULT NULL,
  `is_member_disabled` tinyint(1) unsigned DEFAULT NULL,
  `is_member_head` tinyint(1) unsigned DEFAULT NULL,
  `is_member_head_spouse` tinyint(1) unsigned DEFAULT NULL,
  `enabled_edu_cc_exp` tinyint(1) DEFAULT '0',
  `enabled_edu_disability_exp` tinyint(1) DEFAULT '0',
  `annual_income` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`member_id`),
  KEY `member_household_id` (`household_id`),
  CONSTRAINT `member_household_id` FOREIGN KEY (`household_id`) REFERENCES `iec_tbl_household` (`household_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=272930 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_tbl_part5_ai_long_jobs`
--

CREATE TABLE `iec_tbl_part5_ai_long_jobs` (
  `job_id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `job_desc` varchar(100) DEFAULT NULL,
  `wage_type` varchar(45) DEFAULT NULL,
  `hourly_wage` decimal(10,2) DEFAULT NULL,
  `hourly_per_week` int(11) DEFAULT NULL,
  `weeks_per_year` int(11) DEFAULT NULL,
  `annual_wage` decimal(10,2) DEFAULT NULL,
  `month_01_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_02_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_03_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_04_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_05_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_06_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_07_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_08_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_09_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_10_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_11_ot_bonus` decimal(10,2) DEFAULT NULL,
  `month_12_ot_bonus` decimal(10,2) DEFAULT NULL,
  `has_raise_cola_increase` tinyint(1) unsigned DEFAULT NULL,
  `raises_type` varchar(45) DEFAULT NULL COMMENT '(raise|cola)',
  `increase_type` varchar(45) DEFAULT NULL,
  `anticipated_raise` decimal(10,2) DEFAULT NULL,
  `raise_start_date` datetime DEFAULT NULL,
  `cola_pct` float DEFAULT NULL,
  `cola_start_week` datetime DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`job_id`),
  KEY `jobs_member_id` (`member_id`),
  CONSTRAINT `jobs_member_id` FOREIGN KEY (`member_id`) REFERENCES `iec_tbl_member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31240 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_tbl_part5_ai_long_otherincome`
--

CREATE TABLE `iec_tbl_part5_ai_long_otherincome` (
  `other_income_id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `anticipated_income_type_id` int(11) DEFAULT NULL,
  `frequency_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `description` varchar(250) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`other_income_id`),
  KEY `other_income_member_id` (`member_id`),
  KEY `other_income_anticipated_income_type_id` (`anticipated_income_type_id`),
  KEY `other_income_frequency_id` (`frequency_id`),
  CONSTRAINT `other_income_anticipated_income_type_id` FOREIGN KEY (`anticipated_income_type_id`) REFERENCES `iec_lku_anticipated_income_type` (`anticipated_income_type`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `other_income_frequency_id` FOREIGN KEY (`frequency_id`) REFERENCES `iec_lku_frequency` (`frequency_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `other_income_member_id` FOREIGN KEY (`member_id`) REFERENCES `iec_tbl_member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=36309 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_tbl_part5_ai_short`
--

CREATE TABLE `iec_tbl_part5_ai_short` (
  `part5_ai_short_id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `anticipated_income_type_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `description` varchar(250) DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`part5_ai_short_id`)
) ENGINE=InnoDB AUTO_INCREMENT=63789 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_tbl_part5_asset`
--

CREATE TABLE `iec_tbl_part5_asset` (
  `part5_asset_id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `asset_type_id` int(11) DEFAULT NULL,
  `asset_desc` varchar(250) DEFAULT NULL,
  `actual_income_from_asset` decimal(10,2) DEFAULT NULL,
  `current_value_from_asset` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`part5_asset_id`),
  KEY `part5_assets_member_id` (`member_id`),
  KEY `part5_assets_asset_type_id` (`asset_type_id`),
  CONSTRAINT `part5_assets_asset_type_id` FOREIGN KEY (`asset_type_id`) REFERENCES `iec_lku_asset_type` (`asset_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `part5_assets_member_id` FOREIGN KEY (`member_id`) REFERENCES `iec_tbl_member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=63761 DEFAULT CHARSET=utf8;

--
-- Table structure for table `iec_tbl_tbra`
--

CREATE TABLE `iec_tbl_tbra` (
  `tbra_id` int(11) NOT NULL AUTO_INCREMENT,
  `household_id` int(11) DEFAULT NULL,
  `tbra_activity` varchar(45) DEFAULT NULL,
  `approved_rent` decimal(10,2) DEFAULT NULL,
  `utility_allowance` decimal(10,2) DEFAULT NULL,
  `tbra_subsidy_method` varchar(45) DEFAULT NULL,
  `is_tenant_welfare_resident` tinyint(1) unsigned DEFAULT NULL,
  `welfare_rent` decimal(10,2) DEFAULT NULL,
  `rent_standard` decimal(10,2) unsigned DEFAULT NULL,
  `is_rent_standard` tinyint(1) unsigned DEFAULT NULL,
  `is_rent_reasonable` tinyint(1) unsigned DEFAULT NULL,
  `created_by` varchar(45) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `last_edited_by` varchar(45) DEFAULT NULL,
  `last_edited_date` datetime DEFAULT NULL,
  `deleted` tinyint(1) unsigned DEFAULT NULL,
  `status_id` int(11) DEFAULT NULL,
  `last_saved_section` varchar(64) DEFAULT NULL,
  `last_saved_item` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`tbra_id`),
  KEY `tbra_household_id` (`household_id`),
  KEY `tbra_status_id` (`status_id`),
  CONSTRAINT `tbra_household_id` FOREIGN KEY (`household_id`) REFERENCES `iec_tbl_household` (`household_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tbra_status_id` FOREIGN KEY (`status_id`) REFERENCES `iec_lku_status` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13849 DEFAULT CHARSET=utf8;

--
-- Table structure for table `lku_city_municipality`
--

CREATE TABLE `lku_city_municipality` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `county_name` varchar(100) NOT NULL,
  `state` char(2) NOT NULL,
  `type` varchar(30) DEFAULT NULL,
  `icfuid` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_lku_city_municipality_1` (`name`),
  KEY `idx_lku_city_municipality_2` (`state`),
  KEY `idx_lku_city_municipality_3` (`county_name`),
  KEY `idx_lku_city_municipality_4` (`icfuid`)
) ENGINE=InnoDB AUTO_INCREMENT=32810 DEFAULT CHARSET=utf8;

--
-- Table structure for table `lku_county`
--

CREATE TABLE `lku_county` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT 'county name',
  `stateid` char(2) NOT NULL,
  `fips` char(5) DEFAULT NULL,
  `type` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_lku_county_1` (`stateid`),
  CONSTRAINT `fk_lku_county_1` FOREIGN KEY (`stateid`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3247 DEFAULT CHARSET=utf8;

--
-- Table structure for table `lku_hud_region`
--

CREATE TABLE `lku_hud_region` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `code` varchar(45) DEFAULT NULL,
  `sortorder` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

--
-- Table structure for table `lms_lku_experience`
--

CREATE TABLE `lms_lku_experience` (
  `experience_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `experience_years` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`experience_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Table structure for table `lms_lku_programs`
--

CREATE TABLE `lms_lku_programs` (
  `program_category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `program_category` varchar(255) DEFAULT NULL,
  `short_name` varchar(45) DEFAULT NULL,
  `short_name_display` varchar(45) DEFAULT NULL,
  `lms_mapping_name` varchar(45) DEFAULT NULL,
  `parent_category_id` int(10) unsigned DEFAULT NULL,
  `type_id` int(10) unsigned DEFAULT NULL,
  `sort_order` int(10) DEFAULT NULL,
  PRIMARY KEY (`program_category_id`),
  KEY `fk_lms_lku_programs_cat1_idx` (`parent_category_id`),
  KEY `fk_lms_lku_programs_type1_idx` (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=utf8;

--
-- Table structure for table `lms_lku_type`
--

CREATE TABLE `lms_lku_type` (
  `type_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type_name` varchar(45) DEFAULT NULL,
  `sort_order` int(10) DEFAULT NULL,
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Table structure for table `lms_tbl_registration`
--

CREATE TABLE `lms_tbl_registration` (
  `user_id` char(35) NOT NULL,
  `program_category_id` int(10) unsigned NOT NULL,
  `experience_id` int(10) unsigned DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`,`program_category_id`),
  KEY `fk_lms_registration_lms_lku_experience1_idx` (`experience_id`),
  KEY `fk_lms_registration_lms_lku_programs1_idx` (`program_category_id`),
  CONSTRAINT `fk_lms_registration_lms_lku_experience1` FOREIGN KEY (`experience_id`) REFERENCES `lms_lku_experience` (`experience_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `lms_tbl_registration_to_be_sync`
--

CREATE TABLE `lms_tbl_registration_to_be_sync` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `external_id` char(36) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `user_id` char(35) DEFAULT NULL,
  `synched` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4084 DEFAULT CHARSET=utf8;

--
-- Table structure for table `lms_users`
--

CREATE TABLE `lms_users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `AGENCYSUBELEMENTCODE` varchar(255) DEFAULT NULL,
  `CITY` varchar(255) DEFAULT NULL,
  `COUNTRY` varchar(255) DEFAULT NULL,
  `COURSEWAREID` varchar(255) DEFAULT NULL,
  `DEFAULTGROUPNAME` varchar(255) DEFAULT NULL,
  `DEFAULTROLE` varchar(255) DEFAULT NULL,
  `EHRIEMPLOYEEID` varchar(255) DEFAULT NULL,
  `EMAIL` varchar(255) DEFAULT NULL,
  `EXTENSION` varchar(255) DEFAULT NULL,
  `EXTERNALITY` varchar(255) DEFAULT NULL,
  `FIRSTNAME` varchar(255) DEFAULT NULL,
  `LASTMODIFIED` varchar(255) DEFAULT NULL,
  `LASTNAME` varchar(255) DEFAULT NULL,
  `LOCATION` varchar(255) DEFAULT NULL,
  `MIDDLEINITIAL` varchar(255) DEFAULT NULL,
  `POSTALCODE` varchar(255) DEFAULT NULL,
  `STARTDATE` varchar(255) DEFAULT NULL,
  `STATE` varchar(255) DEFAULT NULL,
  `STREETADDRESS` varchar(255) DEFAULT NULL,
  `STREETADDRESS2` varchar(255) DEFAULT NULL,
  `TELEPHONE` varchar(255) DEFAULT NULL,
  `TIMEZONE` varchar(255) DEFAULT NULL,
  `USERNAME` varchar(255) DEFAULT NULL,
  `USERSTATUS` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_username` (`USERNAME`)
) ENGINE=InnoDB AUTO_INCREMENT=3739 DEFAULT CHARSET=utf8;

--
-- Table structure for table `ml_tbl_mailing_list_archive`
--

CREATE TABLE `ml_tbl_mailing_list_archive` (
  `id` char(10) NOT NULL,
  `subject` text,
  `archive_url` varchar(255) DEFAULT NULL,
  `send_time` datetime DEFAULT NULL,
  `group_year` int(11) DEFAULT NULL,
  `group_month` char(10) DEFAULT NULL,
  `group_label` char(15) DEFAULT NULL,
  `synched_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_asc_ae_subcategory`
--

CREATE TABLE `na_asc_ae_subcategory` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ae_id` int(10) unsigned NOT NULL,
  `subcategory_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ae_subcategory_UNIQUE` (`ae_id`,`subcategory_id`),
  KEY `fk_na_ae_subcategory_1` (`ae_id`),
  KEY `fk_na_ae_subcategory_2` (`subcategory_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1219 DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_asc_gap_na_skill_area`
--

CREATE TABLE `na_asc_gap_na_skill_area` (
  `na_id` int(10) unsigned NOT NULL,
  `skill_area_id` int(10) unsigned NOT NULL,
  `assessor_type_id` int(10) unsigned NOT NULL,
  `gap_grantee` tinyint(1) unsigned DEFAULT NULL,
  `gap_subrecipient_and_developer` tinyint(1) unsigned DEFAULT NULL,
  `comments` text CHARACTER SET latin1,
  PRIMARY KEY (`na_id`,`skill_area_id`,`assessor_type_id`),
  KEY `fk_na_asc_gap_na_skill_area_1` (`na_id`),
  KEY `fk_na_asc_gap_na_skill_area_2` (`skill_area_id`),
  KEY `fk_na_asc_gap_na_skill_area_3` (`assessor_type_id`),
  CONSTRAINT `fk_na_asc_gap_na_skill_area_1` FOREIGN KEY (`na_id`) REFERENCES `na_tbl_needs_assessment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_na_asc_gap_na_skill_area_3` FOREIGN KEY (`assessor_type_id`) REFERENCES `na_lku_assessor_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_asc_gap_na_subcategory`
--

CREATE TABLE `na_asc_gap_na_subcategory` (
  `na_id` int(10) unsigned NOT NULL,
  `subcategory_id` int(10) unsigned NOT NULL,
  `assessor_type_id` int(10) unsigned NOT NULL,
  `gap_grantee` tinyint(1) unsigned DEFAULT NULL,
  `gap_subrecipient_and_developer` tinyint(1) unsigned DEFAULT NULL,
  `comments` text CHARACTER SET latin1,
  PRIMARY KEY (`na_id`,`subcategory_id`,`assessor_type_id`),
  KEY `fk_na_gap_1` (`subcategory_id`),
  KEY `fk_na_gap_2` (`na_id`),
  KEY `fk_na_gap_3` (`assessor_type_id`),
  KEY `fk_na_gap_4` (`subcategory_id`),
  CONSTRAINT `fk_na_gap_cst_1` FOREIGN KEY (`subcategory_id`) REFERENCES `na_lku_subcategory` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_na_gap_cst_2` FOREIGN KEY (`na_id`) REFERENCES `na_tbl_needs_assessment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_na_gap_cst_3` FOREIGN KEY (`assessor_type_id`) REFERENCES `na_lku_assessor_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_asc_grantee_state`
--

CREATE TABLE `na_asc_grantee_state` (
  `granteeid` int(10) unsigned NOT NULL,
  `stateid` char(2) NOT NULL,
  PRIMARY KEY (`granteeid`,`stateid`),
  KEY `fk_asc_grantee_state_granteeid` (`granteeid`),
  KEY `fk_asc_grantee_state_stateid` (`stateid`),
  CONSTRAINT `fk_asc_grantee_state_stateid` FOREIGN KEY (`stateid`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_asc_na_status`
--

CREATE TABLE `na_asc_na_status` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `na_id` int(10) unsigned NOT NULL,
  `status_id` int(10) unsigned NOT NULL,
  `updated_by_id` char(35) CHARACTER SET latin1 NOT NULL,
  `comments` text CHARACTER SET latin1,
  `additional_comments` text CHARACTER SET latin1,
  `update_date` datetime NOT NULL,
  `recipients` text CHARACTER SET latin1,
  `additional_recipients` text CHARACTER SET latin1,
  `is_program_office` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_na_na_status_update_date` (`update_date`),
  KEY `fk_na_na_status_1` (`status_id`),
  CONSTRAINT `fk_na_na_status_1` FOREIGN KEY (`status_id`) REFERENCES `na_lku_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=218 DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_asc_na_subcategory`
--

CREATE TABLE `na_asc_na_subcategory` (
  `na_id` int(10) unsigned NOT NULL,
  `subcategory_id` int(10) unsigned NOT NULL,
  `other_subcategory_name` varchar(256) CHARACTER SET latin1 DEFAULT NULL,
  `innovative_practice` tinyint(1) unsigned DEFAULT NULL,
  `innovative_practice_initial_comments` text CHARACTER SET latin1,
  `innovative_practice_final_comments` text CHARACTER SET latin1,
  PRIMARY KEY (`na_id`,`subcategory_id`),
  KEY `fk_na_area_rating_1` (`na_id`),
  KEY `fk_na_area_rating_2` (`subcategory_id`),
  CONSTRAINT `fk_na_area_rating_1` FOREIGN KEY (`na_id`) REFERENCES `na_tbl_needs_assessment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_na_area_rating_2` FOREIGN KEY (`subcategory_id`) REFERENCES `na_lku_subcategory` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_asc_primary_team_member`
--

CREATE TABLE `na_asc_primary_team_member` (
  `na_id` int(10) unsigned NOT NULL,
  `firm_id` int(10) unsigned NOT NULL,
  `member_id` char(35) NOT NULL,
  `member_type` varchar(20) NOT NULL,
  PRIMARY KEY (`na_id`,`firm_id`,`member_id`),
  KEY `fk_na_asc_primary_team_member_1` (`na_id`),
  KEY `fk_na_asc_primary_team_member_2` (`firm_id`),
  KEY `fk_na_asc_primary_team_member_3` (`member_id`),
  CONSTRAINT `fk_na_asc_primary_team_member_1` FOREIGN KEY (`na_id`) REFERENCES `na_tbl_needs_assessment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_na_asc_primary_team_member_2` FOREIGN KEY (`firm_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_na_asc_primary_team_member_3` FOREIGN KEY (`member_id`) REFERENCES `tusers` (`UserID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_asc_rating_na_subcategory`
--

CREATE TABLE `na_asc_rating_na_subcategory` (
  `na_id` int(10) unsigned NOT NULL,
  `subcategory_id` int(10) unsigned NOT NULL,
  `assessor_type_id` int(10) unsigned NOT NULL,
  `rating_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`na_id`,`subcategory_id`,`assessor_type_id`),
  KEY `fk_na_subcategory_rating_1` (`subcategory_id`),
  KEY `fk_na_subcategory_rating_2` (`na_id`),
  KEY `fk_na_subcategory_rating_3` (`assessor_type_id`),
  KEY `fk_na_subcategory_rating_4` (`rating_id`),
  KEY `fk_na_asc_subcategory_rating_1` (`subcategory_id`),
  KEY `fk_na_asc_rating_na_subcategory_1` (`assessor_type_id`),
  CONSTRAINT `fk_na_asc_rating_na_subcategory_1` FOREIGN KEY (`assessor_type_id`) REFERENCES `na_lku_assessor_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_na_asc_subcategory_rating_1` FOREIGN KEY (`subcategory_id`) REFERENCES `na_lku_subcategory` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_na_subcategory_rating_2` FOREIGN KEY (`na_id`) REFERENCES `na_tbl_needs_assessment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_na_subcategory_rating_4` FOREIGN KEY (`rating_id`) REFERENCES `na_lku_rating` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_attachment`
--

CREATE TABLE `na_attachment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `na_status_id` int(10) unsigned NOT NULL,
  `name` varchar(256) CHARACTER SET latin1 NOT NULL,
  `path_name` varchar(256) CHARACTER SET latin1 NOT NULL,
  `date_uploaded` datetime NOT NULL,
  `mime_type` varchar(256) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_na_attachment_1` (`na_status_id`),
  CONSTRAINT `fk_na_attachment_1` FOREIGN KEY (`na_status_id`) REFERENCES `na_asc_na_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=171 DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_lku_assessor_type`
--

CREATE TABLE `na_lku_assessor_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET latin1 NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  `rating_level` varchar(16) CHARACTER SET latin1 DEFAULT NULL,
  `gap_level` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `ix_rating_type_order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_lku_category`
--

CREATE TABLE `na_lku_category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET latin1 NOT NULL,
  `description` text CHARACTER SET latin1 NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `ix_na_category_order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_lku_field_office`
--

CREATE TABLE `na_lku_field_office` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(64) CHARACTER SET latin1 NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `na_lku_rating_order_by` (`order_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_lku_rating`
--

CREATE TABLE `na_lku_rating` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` int(10) unsigned DEFAULT NULL,
  `description` varchar(32) CHARACTER SET latin1 NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `description_UNIQUE` (`description`),
  UNIQUE KEY `rating_value_UNIQUE` (`value`),
  KEY `ix_rating_order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_lku_skill_area`
--

CREATE TABLE `na_lku_skill_area` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
  `subcategory_id` int(10) unsigned NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uq_na_skill_area` (`name`,`subcategory_id`),
  KEY `fk_na_skill_area_1` (`subcategory_id`),
  KEY `ix_skill_area_order_by` (`order_by`),
  CONSTRAINT `fk_na_skill_area_1` FOREIGN KEY (`subcategory_id`) REFERENCES `na_lku_skill_area` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=572 DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_lku_status`
--

CREATE TABLE `na_lku_status` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET latin1 NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `ix_na_status_order_by` (`order_by`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_lku_subcategory`
--

CREATE TABLE `na_lku_subcategory` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET latin1 NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  `order_by` int(11) DEFAULT NULL,
  `is_other` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `fk_na_area_1` (`category_id`),
  KEY `ix_na_area_order_by` (`order_by`),
  CONSTRAINT `fk_na_area_1` FOREIGN KEY (`category_id`) REFERENCES `na_lku_category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_tbl_administrating_entity`
--

CREATE TABLE `na_tbl_administrating_entity` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET latin1 NOT NULL,
  `na_id` int(10) unsigned NOT NULL,
  `isprimary` tinyint(1) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_na_administrating_entities_1` (`na_id`),
  CONSTRAINT `fk_na_administrating_entities_1` FOREIGN KEY (`na_id`) REFERENCES `na_tbl_needs_assessment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_tbl_interview`
--

CREATE TABLE `na_tbl_interview` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
  `last_name` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
  `position` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  `role` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `na_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_na_tbl_interview_1` (`na_id`),
  CONSTRAINT `fk_na_tbl_interview_1` FOREIGN KEY (`na_id`) REFERENCES `na_tbl_needs_assessment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `na_tbl_needs_assessment`
--

CREATE TABLE `na_tbl_needs_assessment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `firm_id` int(10) unsigned NOT NULL,
  `state_id` char(2) NOT NULL,
  `field_office_id` int(10) unsigned DEFAULT NULL,
  `grantee_id` int(10) unsigned NOT NULL,
  `work_plan_number` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
  `executive_summary` text CHARACTER SET latin1,
  `priority_needs` text CHARACTER SET latin1,
  `grantee_overview` text CHARACTER SET latin1,
  `intergovernmental_relationships` text CHARACTER SET latin1,
  `intragovernmental_relationships` text CHARACTER SET latin1,
  `partnerships` text CHARACTER SET latin1,
  `market_observations` text CHARACTER SET latin1,
  `recommended_tasks_summary` text CHARACTER SET latin1,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_needs_assessment_1` (`state_id`),
  KEY `fk_na_tbl_needs_assessment_1` (`field_office_id`),
  KEY `fk_needs_assessment_2` (`grantee_id`),
  KEY `fk_needs_assessment_3` (`firm_id`),
  CONSTRAINT `fk_na_tbl_needs_assessment_1` FOREIGN KEY (`field_office_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_needs_assessment_1` FOREIGN KEY (`state_id`) REFERENCES `state` (`abbrev`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_needs_assessment_2` FOREIGN KEY (`grantee_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_needs_assessment_3` FOREIGN KEY (`firm_id`) REFERENCES `c_torganizations` (`orgid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=utf8 COMMENT='na = Needs Assessment';

--
-- Temporary table structure for view `na_vw_nadashboard`
--

/*!50001 DROP VIEW IF EXISTS `na_vw_nadashboard`*/;
/*!50001 CREATE TABLE `na_vw_nadashboard` (
  `naid` tinyint NOT NULL,
  `firm_id` tinyint NOT NULL,
  `state_id` tinyint NOT NULL,
  `state` tinyint NOT NULL,
  `grantee_id` tinyint NOT NULL,
  `lead_taprovider` tinyint NOT NULL,
  `grantee` tinyint NOT NULL,
  `create_date` tinyint NOT NULL,
  `status` tinyint NOT NULL,
  `statusid` tinyint NOT NULL,
  `update_date` tinyint NOT NULL,
  `ta_team_lead` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `na_vw_nadashboard_status`
--

/*!50001 DROP VIEW IF EXISTS `na_vw_nadashboard_status`*/;
/*!50001 CREATE TABLE `na_vw_nadashboard_status` (
  `na_id` tinyint NOT NULL,
  `status_id` tinyint NOT NULL,
  `statusName` tinyint NOT NULL,
  `id` tinyint NOT NULL,
  `comments` tinyint NOT NULL,
  `update_date` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `nsp_award`
--

CREATE TABLE `nsp_award` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `grant_no` varchar(255) DEFAULT NULL,
  `grantee_id` int(11) DEFAULT NULL,
  `obligated_amount` double(20,2) DEFAULT NULL,
  `expanded_amount` double(20,2) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `idx_nsp_award` (`grant_no`),
  KEY `idx.nsp_award2` (`grantee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=633 DEFAULT CHARSET=utf8;

--
-- Table structure for table `other_special_topics`
--

CREATE TABLE `other_special_topics` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `sort` int(10) DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `page_paragraphs`
--

CREATE TABLE `page_paragraphs` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `page_name` varchar(50) DEFAULT NULL,
  `paragraph_name` varchar(50) DEFAULT NULL,
  `paragraph_text` varchar(20000) DEFAULT NULL,
  `paragraph_image` varchar(255) DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `facet_id` int(9) DEFAULT NULL,
  `indexable` bit(1) DEFAULT NULL,
  `search_result_link` text,
  `search_doc_title` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `region`
--

CREATE TABLE `region` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_attribute`
--

CREATE TABLE `rpt_attribute` (
  `program` varchar(10) NOT NULL,
  `group` varchar(10) NOT NULL,
  `scope` varchar(15) NOT NULL,
  `attribute` varchar(25) NOT NULL,
  `txt` varchar(50) DEFAULT NULL,
  `position` int(11) DEFAULT NULL,
  `optional` bit(1) DEFAULT b'0',
  PRIMARY KEY (`program`,`group`,`scope`,`attribute`),
  KEY `fk_group_attribute` (`group`),
  KEY `fk_scope_attribute` (`scope`),
  CONSTRAINT `fk_group_attribute` FOREIGN KEY (`group`) REFERENCES `rpt_group` (`code`),
  CONSTRAINT `fk_program_attribute` FOREIGN KEY (`program`) REFERENCES `rpt_program` (`code`),
  CONSTRAINT `fk_scope_attribute` FOREIGN KEY (`scope`) REFERENCES `rpt_scope` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_coc`
--

CREATE TABLE `rpt_coc` (
  `code` varchar(15) NOT NULL,
  `label` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_environmental_review_record`
--

CREATE TABLE `rpt_environmental_review_record` (
  `errId` varchar(15) NOT NULL,
  `basePath` varchar(250) DEFAULT NULL,
  `filename` varchar(250) DEFAULT NULL,
  `projectName` varchar(60) DEFAULT NULL,
  `city` varchar(30) DEFAULT NULL,
  `state` varchar(2) DEFAULT NULL,
  `reviewLevel` varchar(5) DEFAULT NULL,
  `status` varchar(15) DEFAULT NULL,
  `err_status` varchar(2) DEFAULT NULL,
  `removalDate` datetime DEFAULT NULL,
  `uploadDate` datetime DEFAULT NULL,
  `message` varchar(2000) DEFAULT NULL,
  `unique_id` bigint(20) DEFAULT NULL,
  `publishDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`errId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_err_supporting_document`
--

CREATE TABLE `rpt_err_supporting_document` (
  `errId` varchar(15) NOT NULL,
  `basePath` varchar(250) NOT NULL,
  `filename` varchar(250) NOT NULL,
  `removalDate` datetime NOT NULL,
  `docName` varchar(20) NOT NULL DEFAULT '',
  `uploadDate` datetime DEFAULT NULL,
  `status` varchar(15) DEFAULT NULL,
  `message` varchar(2000) DEFAULT NULL,
  `unique_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`errId`,`docName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_filter`
--

CREATE TABLE `rpt_filter` (
  `program` varchar(10) NOT NULL,
  `group` varchar(10) NOT NULL,
  `filter` varchar(45) NOT NULL,
  `position` int(11) DEFAULT NULL,
  `default_text` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`program`,`group`,`filter`),
  KEY `fk_group_filter_idx` (`group`),
  KEY `fk_program_filter_idx` (`program`),
  CONSTRAINT `fk_group_filter` FOREIGN KEY (`group`) REFERENCES `rpt_group` (`code`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_program_filter` FOREIGN KEY (`program`) REFERENCES `rpt_program` (`code`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_filter_dependency_onchange`
--

CREATE TABLE `rpt_filter_dependency_onchange` (
  `program` varchar(10) NOT NULL,
  `group` varchar(10) NOT NULL,
  `dependency_filter` varchar(45) NOT NULL,
  `position` int(11) NOT NULL,
  `ifelse` varchar(30) DEFAULT NULL,
  `dependency_condition` varchar(300) DEFAULT NULL,
  `actions` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`program`,`group`,`dependency_filter`,`position`),
  KEY `fk_program_filter_dependency_onchange_idx` (`program`),
  KEY `fk_group_filter_dependency_onchange_idx` (`group`),
  CONSTRAINT `fk_group_filter_dependency_onchange` FOREIGN KEY (`group`) REFERENCES `rpt_group` (`code`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_program_filter_dependency_onchange` FOREIGN KEY (`program`) REFERENCES `rpt_program` (`code`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_filter_dependency_onload`
--

CREATE TABLE `rpt_filter_dependency_onload` (
  `program` varchar(10) NOT NULL,
  `group` varchar(10) NOT NULL,
  `filter` varchar(45) NOT NULL,
  `dependency_filter` varchar(45) NOT NULL,
  `position` int(11) DEFAULT NULL,
  `dependency_condition1` varchar(200) DEFAULT NULL,
  `dependency_condition2` varchar(200) DEFAULT NULL,
  `actions` varchar(310) DEFAULT NULL,
  PRIMARY KEY (`program`,`group`,`filter`,`dependency_filter`),
  KEY `fk_program_filter_dependency_onload_idx` (`program`),
  KEY `fk_group_filter_dependency_onload_idx` (`group`),
  CONSTRAINT `fk_group_filter_dependency_onload` FOREIGN KEY (`group`) REFERENCES `rpt_group` (`code`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_program_filter_dependency_onload` FOREIGN KEY (`program`) REFERENCES `rpt_program` (`code`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_filter_scope`
--

CREATE TABLE `rpt_filter_scope` (
  `program` varchar(10) NOT NULL,
  `group` varchar(10) NOT NULL,
  `filter_value` varchar(45) NOT NULL,
  `filter_label` varchar(100) DEFAULT NULL,
  `position` int(11) DEFAULT NULL,
  PRIMARY KEY (`program`,`group`,`filter_value`),
  KEY `fk_program_filters_scope_idx` (`program`),
  KEY `fk_group_filter_scope_idx` (`group`),
  CONSTRAINT `fk_group_filter_scope` FOREIGN KEY (`group`) REFERENCES `rpt_group` (`code`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_program_filter_scope` FOREIGN KEY (`program`) REFERENCES `rpt_program` (`code`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_fiscal_year`
--

CREATE TABLE `rpt_fiscal_year` (
  `code` varchar(15) NOT NULL,
  `label` varchar(45) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_gateway_error`
--

CREATE TABLE `rpt_gateway_error` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` varchar(250) DEFAULT NULL,
  `rootcause` varchar(500) DEFAULT NULL,
  `errorText` longtext,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16358 DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_grant_type`
--

CREATE TABLE `rpt_grant_type` (
  `code` varchar(15) NOT NULL,
  `label` varchar(45) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_grantee`
--

CREATE TABLE `rpt_grantee` (
  `code` varchar(15) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_group`
--

CREATE TABLE `rpt_group` (
  `code` varchar(10) NOT NULL,
  `label` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_hud_fo`
--

CREATE TABLE `rpt_hud_fo` (
  `code` varchar(10) NOT NULL,
  `label` varchar(45) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_hud_region`
--

CREATE TABLE `rpt_hud_region` (
  `code` char(3) NOT NULL,
  `label` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_inbound_log`
--

CREATE TABLE `rpt_inbound_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `method` varchar(25) NOT NULL,
  `filename` varchar(500) NOT NULL,
  `lastmodified` datetime NOT NULL,
  `uploaddate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=92645 DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_lku_conplan_grantee`
--

CREATE TABLE `rpt_lku_conplan_grantee` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `idis_id` bigint(20) NOT NULL,
  `name` varchar(500) NOT NULL,
  `dunns` varchar(20) DEFAULT NULL,
  `state_abbrev` char(2) NOT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_rpt_lku_conplan_grantee_1` (`idis_id`),
  KEY `idx_rpt_lku_conplan_grantee_2` (`state_abbrev`),
  CONSTRAINT `fk_rpt_lku_conplan_grantee_2` FOREIGN KEY (`state_abbrev`) REFERENCES `rpt_state` (`code`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9124 DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_lku_conplan_status`
--

CREATE TABLE `rpt_lku_conplan_status` (
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_lku_conplan_type`
--

CREATE TABLE `rpt_lku_conplan_type` (
  `code` varchar(10) NOT NULL,
  `long_name` varchar(500) NOT NULL,
  `short_name` varchar(500) NOT NULL,
  `display_order` int(10) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_lku_grantee_type`
--

CREATE TABLE `rpt_lku_grantee_type` (
  `abbeviation` varchar(10) NOT NULL,
  `name` varchar(500) NOT NULL,
  PRIMARY KEY (`abbeviation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_program`
--

CREATE TABLE `rpt_program` (
  `code` varchar(10) NOT NULL,
  `label` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_report`
--

CREATE TABLE `rpt_report` (
  `filename` varchar(250) NOT NULL,
  `basepath` varchar(250) NOT NULL DEFAULT '',
  `status` varchar(15) DEFAULT NULL,
  `message` varchar(2000) DEFAULT NULL,
  `uploadDate` datetime NOT NULL,
  `publishDate` datetime DEFAULT NULL,
  `program` varchar(10) DEFAULT NULL,
  `group` varchar(10) DEFAULT NULL,
  `scope` varchar(15) DEFAULT NULL,
  `COC` varchar(15) DEFAULT NULL,
  `grantType` varchar(15) DEFAULT NULL,
  `grantee` varchar(15) DEFAULT NULL,
  `hudFO` varchar(10) DEFAULT NULL,
  `hudRegion` char(3) DEFAULT NULL,
  `state` char(2) DEFAULT NULL,
  `periodEnd` date DEFAULT NULL,
  `toolType` varchar(50) DEFAULT NULL,
  `reportType` varchar(50) DEFAULT NULL,
  `sort` varchar(15) DEFAULT NULL,
  `fiscalYear` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`filename`),
  KEY `fk_type_report` (`program`,`group`,`scope`),
  KEY `rpt_report_coc_idx` (`COC`),
  KEY `rpt_report_grantee__idx` (`grantee`),
  KEY `idx_rpt_report_periodEnd` (`periodEnd`),
  KEY `idx_rpt_report_status` (`status`),
  KEY `idx_rpt_report_grantType` (`grantType`),
  KEY `idx_rpt_report_state` (`state`),
  KEY `idx_rpt_report_reportType` (`reportType`),
  KEY `idx_rpt_report_toolType` (`toolType`),
  KEY `idx_rpt_report_program_grantee_status` (`status`,`program`,`grantee`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`mkim`@`%`*/ /*!50003 TRIGGER trg_rpt_report_update AFTER UPDATE ON rpt_report
FOR EACH ROW

BEGIN

IF ( new.status = 'Published' ) THEN

	IF (
		0 = (SELECT COUNT(j.id)
				FROM gt_asc_grantee_jurisdiction j
				JOIN c_torganizations g on g.orgid = j.grantee_id
				JOIN gt_lku_program gp on gp.id = j.program_id 
				 WHERE g.grantee_code = new.grantee 
					AND gp.short_name = new.program
					AND j.`year` = year(new.periodEnd)
					AND j.active = 1 
					AND j.grantee_id is not null
			) 
	) THEN

		INSERT INTO gt_asc_grantee_jurisdiction (
				`grantee_id`,
				`program_id`,
				`year`,
				`nationwide`,
				`state`,
				`county_id`,
				`city_id`,
				`lastupdated`,
				`updatedby_name`,
				`active`)
		SELECT 		
			j.`grantee_id`,
			j.`program_id`,
			year(new.periodEnd),
			j.`nationwide`,
			j.`state`,
			j.`county_id`,
			j.`city_id`,
			now(),
			'Trigger: trg_rpt_report_update',
			j.`active`
		FROM gt_asc_grantee_jurisdiction j
		JOIN c_torganizations g on g.orgid = j.grantee_id
		JOIN gt_lku_program gp on gp.id = j.program_id 
		WHERE g.grantee_code = new.grantee 
				AND gp.short_name = new.program
				AND j.`year` = (year(new.periodEnd) - 1)
				AND j.active = 1 
				AND j.grantee_id is not null;

	ELSEIF (  		
		0 = (SELECT COUNT(j.id)
				FROM gt_asc_grantee_jurisdiction j
				 WHERE j.coc_no = SUBSTRING_INDEX(new.coc, '-', 2)
					AND j.`year` = year(new.periodEnd)
					AND j.active = 1 
					AND j.coc_no is not null	
			)
	) THEN

		INSERT INTO gt_asc_grantee_jurisdiction (
			`coc_no`,
			`year`,
			`nationwide`,
			`state`,
			`county_id`,
			`city_id`,
			`lastupdated`,
			`updatedby_name`,
			`active`)
		SELECT 		
			`coc_no`,
			year(new.periodEnd),
			`nationwide`,
			`state`,
			`county_id`,
			`city_id`,
			now(),
			'Trigger: trg_rpt_report_update',
			`active`
		FROM gt_asc_grantee_jurisdiction 
		WHERE coc_no = SUBSTRING_INDEX(new.coc, '-', 2) 
				AND year = (year(new.periodEnd) - 1)
				AND active = 1
				AND coc_no is not null; 
				
	END IF;
			
END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `rpt_report_type`
--

CREATE TABLE `rpt_report_type` (
  `program` varchar(10) NOT NULL,
  `group` varchar(10) NOT NULL,
  `code` varchar(15) NOT NULL,
  `label` varchar(45) NOT NULL,
  PRIMARY KEY (`code`),
  KEY `idx_rpt_report_type_program` (`program`),
  KEY `idx_rpt_report_type_group` (`group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_scope`
--

CREATE TABLE `rpt_scope` (
  `code` varchar(15) NOT NULL,
  `label` varchar(75) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_sort`
--

CREATE TABLE `rpt_sort` (
  `code` varchar(15) NOT NULL,
  `label` varchar(45) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_state`
--

CREATE TABLE `rpt_state` (
  `code` char(2) NOT NULL,
  `label` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_tbl_conplan`
--

CREATE TABLE `rpt_tbl_conplan` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `filename` varchar(250) NOT NULL,
  `program_year` int(10) DEFAULT NULL,
  `end_year` int(10) DEFAULT NULL,
  `review_date` datetime DEFAULT NULL,
  `report_type` varchar(10) DEFAULT NULL,
  `lead_grantee_idis_id` bigint(20) DEFAULT NULL,
  `owner_grantee_idis_id` bigint(20) DEFAULT NULL,
  `owner_grantee_type` varchar(10) DEFAULT NULL COMMENT 'The same Owner Grantee can have two of the exact same types of reports with different grantee types',
  `status` varchar(20) NOT NULL,
  `confirmed_In_S3` bit(1) DEFAULT NULL,
  `created_date` datetime NOT NULL COMMENT 'This is when the file first exists and a record is created',
  `updated_date` datetime NOT NULL COMMENT 'This is updated anytime there is a change to the record for any reason',
  `published_date` datetime DEFAULT NULL COMMENT 'This is updated anytime the file is published be it load from CACI or Unpublished then Published in the Admin can be NULL if Exception',
  `uploaded_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'This is updated anytime there is a change to the file from CACI so first time or any subseqent modifications',
  `compare_uploaded_date` varchar(30) NOT NULL COMMENT 'This is upload dates with S3 because CF converts to local',
  PRIMARY KEY (`id`),
  KEY `idx_rpt_tbl_conplan_1` (`filename`),
  KEY `idx_rpt_tbl_conplan_2` (`program_year`),
  KEY `idx_rpt_tbl_conplan_3` (`end_year`),
  KEY `idx_rpt_tbl_conplan_4` (`report_type`),
  KEY `idx_rpt_tbl_conplan_5` (`lead_grantee_idis_id`),
  KEY `idx_rpt_tbl_conplan_6` (`owner_grantee_idis_id`),
  KEY `idx_rpt_tbl_conplan_7` (`status`),
  KEY `idx_rpt_tbl_conplan_8` (`owner_grantee_type`),
  CONSTRAINT `fk_rpt_tbl_conplan_1` FOREIGN KEY (`report_type`) REFERENCES `rpt_lku_conplan_type` (`code`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_rpt_tbl_conplan_2` FOREIGN KEY (`owner_grantee_idis_id`) REFERENCES `rpt_lku_conplan_grantee` (`idis_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_rpt_tbl_conplan_3` FOREIGN KEY (`status`) REFERENCES `rpt_lku_conplan_status` (`name`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_rpt_tbl_conplan_4` FOREIGN KEY (`owner_grantee_type`) REFERENCES `rpt_lku_grantee_type` (`abbeviation`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9580 DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_tbl_conplan_load_error`
--

CREATE TABLE `rpt_tbl_conplan_load_error` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `conplan_id` int(10) NOT NULL,
  `message` varchar(2000) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_rpt_conplan_load_error_1` (`conplan_id`),
  CONSTRAINT `fk_rpt_tbl_conplan_load_error_1` FOREIGN KEY (`conplan_id`) REFERENCES `rpt_tbl_conplan` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_tool_type`
--

CREATE TABLE `rpt_tool_type` (
  `code` varchar(15) NOT NULL,
  `label` varchar(45) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rpt_type`
--

CREATE TABLE `rpt_type` (
  `program` varchar(10) NOT NULL,
  `group` varchar(10) NOT NULL,
  `scope` varchar(15) NOT NULL,
  `label` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`program`,`group`,`scope`),
  KEY `fk_program_type` (`program`),
  KEY `fk_group_type` (`group`),
  KEY `fk_scope_type` (`scope`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Temporary table structure for view `rpt_vw_categories`
--

/*!50001 DROP VIEW IF EXISTS `rpt_vw_categories`*/;
/*!50001 CREATE TABLE `rpt_vw_categories` (
  `category_short` tinyint NOT NULL,
  `category_long` tinyint NOT NULL,
  `category_display` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `rpt_vw_published_rpt_report`
--

/*!50001 DROP VIEW IF EXISTS `rpt_vw_published_rpt_report`*/;
/*!50001 CREATE TABLE `rpt_vw_published_rpt_report` (
  `pkid` tinyint NOT NULL,
  `filename` tinyint NOT NULL,
  `uploadDate` tinyint NOT NULL,
  `publishDate` tinyint NOT NULL,
  `program` tinyint NOT NULL,
  `status` tinyint NOT NULL,
  `urlPath` tinyint NOT NULL,
  `contentType` tinyint NOT NULL,
  `title` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `rpt_vw_published_rptreport_err_conplan`
--

/*!50001 DROP VIEW IF EXISTS `rpt_vw_published_rptreport_err_conplan`*/;
/*!50001 CREATE TABLE `rpt_vw_published_rptreport_err_conplan` (
  `pkid` tinyint NOT NULL,
  `filename` tinyint NOT NULL,
  `uploadDate` tinyint NOT NULL,
  `publishDate` tinyint NOT NULL,
  `urlPath` tinyint NOT NULL,
  `contentType` tinyint NOT NULL,
  `title` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `rpt_vw_rptreport_err_conplan_topics`
--

/*!50001 DROP VIEW IF EXISTS `rpt_vw_rptreport_err_conplan_topics`*/;
/*!50001 CREATE TABLE `rpt_vw_rptreport_err_conplan_topics` (
  `filename` tinyint NOT NULL,
  `topicAcronym` tinyint NOT NULL,
  `topicLongName` tinyint NOT NULL,
  `topic` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `rsc_asc_program_category`
--

CREATE TABLE `rsc_asc_program_category` (
  `program_id` int(10) unsigned NOT NULL,
  `program_category_id` int(10) unsigned NOT NULL,
  `sort_order` int(10) unsigned NOT NULL DEFAULT '0',
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`program_id`,`program_category_id`),
  KEY `fk_rsc_asc_program_category_1` (`program_category_id`),
  KEY `fk_rsc_asc_program_category_2` (`program_id`),
  CONSTRAINT `fk_rsc_asc_program_category_1` FOREIGN KEY (`program_category_id`) REFERENCES `rsc_lku_program_category` (`program_category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_rsc_asc_program_category_2` FOREIGN KEY (`program_id`) REFERENCES `rsc_tbl_program` (`program_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rsc_asc_program_ccr`
--

CREATE TABLE `rsc_asc_program_ccr` (
  `program_id` int(10) unsigned NOT NULL,
  `ccr_id` int(10) unsigned NOT NULL,
  `sort_order` int(10) NOT NULL DEFAULT '0',
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`program_id`,`ccr_id`),
  KEY `fk_rsc_asc_program_ccr_1` (`ccr_id`),
  KEY `fk_rsc_asc_program_ccr_2` (`program_id`),
  CONSTRAINT `fk_rsc_asc_program_ccr_1` FOREIGN KEY (`ccr_id`) REFERENCES `rsc_lku_ccr` (`ccr_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_rsc_asc_program_ccr_2` FOREIGN KEY (`program_id`) REFERENCES `rsc_tbl_program` (`program_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rsc_asc_program_reporting_system`
--

CREATE TABLE `rsc_asc_program_reporting_system` (
  `program_id` int(10) unsigned NOT NULL,
  `system_id` int(10) unsigned NOT NULL,
  `sort_order` int(10) NOT NULL DEFAULT '0',
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`program_id`,`system_id`),
  KEY `fk_rsc_asc_program_reporting_system_1` (`system_id`),
  KEY `fk_rsc_asc_program_reporting_system_2` (`program_id`),
  CONSTRAINT `fk_rsc_asc_program_reporting_system_1` FOREIGN KEY (`system_id`) REFERENCES `rsc_lku_reporting_system` (`system_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_rsc_asc_program_reporting_system_2` FOREIGN KEY (`program_id`) REFERENCES `rsc_tbl_program` (`program_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rsc_asc_program_topic`
--

CREATE TABLE `rsc_asc_program_topic` (
  `program_id` int(10) unsigned NOT NULL,
  `topic_id` int(10) unsigned NOT NULL,
  `sort_order` int(10) unsigned NOT NULL DEFAULT '0',
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`program_id`,`topic_id`),
  KEY `fk_rsc_asc_program_topic_1` (`topic_id`),
  KEY `fk_rsc_asc_program_topic_2` (`program_id`),
  CONSTRAINT `fk_rsc_asc_program_topic_1` FOREIGN KEY (`topic_id`) REFERENCES `rsc_lku_topic` (`topic_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_rsc_asc_program_topic_2` FOREIGN KEY (`program_id`) REFERENCES `rsc_tbl_program` (`program_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rsc_asc_resource_approver`
--

CREATE TABLE `rsc_asc_resource_approver` (
  `resource_id` int(10) unsigned NOT NULL,
  `approver_id` int(10) unsigned NOT NULL,
  `sort_order` int(10) unsigned NOT NULL DEFAULT '0',
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`resource_id`,`approver_id`),
  KEY `fk_rsc_asc_resource_approver_1` (`approver_id`),
  KEY `fk_rsc_asc_resource_approver_2` (`resource_id`),
  CONSTRAINT `fk_rsc_asc_resource_approver_1` FOREIGN KEY (`approver_id`) REFERENCES `rsc_lku_approver` (`approver_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_rsc_asc_resource_approver_2` FOREIGN KEY (`resource_id`) REFERENCES `rsc_tbl_resources` (`resource_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rsc_asc_resource_author`
--

CREATE TABLE `rsc_asc_resource_author` (
  `resource_id` int(10) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  `sort_order` int(10) unsigned NOT NULL DEFAULT '0',
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`resource_id`,`author_id`),
  KEY `fk_rsc_asc_resource_author_1` (`author_id`),
  KEY `fk_rsc_asc_resource_author_2` (`resource_id`),
  CONSTRAINT `fk_rsc_asc_resource_author_1` FOREIGN KEY (`author_id`) REFERENCES `rsc_lku_author` (`author_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_rsc_asc_resource_author_2` FOREIGN KEY (`resource_id`) REFERENCES `rsc_tbl_resources` (`resource_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rsc_asc_resource_ccr`
--

CREATE TABLE `rsc_asc_resource_ccr` (
  `resource_id` int(10) unsigned NOT NULL,
  `ccr_id` int(10) unsigned NOT NULL,
  `sort_order` int(10) unsigned NOT NULL DEFAULT '0',
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`resource_id`,`ccr_id`),
  KEY `fk_rsc_asc_resource_ccr_1` (`ccr_id`),
  KEY `fk_rsc_asc_resource_ccr_2` (`resource_id`),
  CONSTRAINT `fk_rsc_asc_resource_ccr_1` FOREIGN KEY (`ccr_id`) REFERENCES `rsc_lku_ccr` (`ccr_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_rsc_asc_resource_ccr_2` FOREIGN KEY (`resource_id`) REFERENCES `rsc_tbl_resources` (`resource_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `rsc_asc_resource_contact`
--

CREATE TABLE `rsc_asc_resource_contact` (
  `resource_id` int(10) unsigned NOT NULL,
  `contact_id` int(10) unsigned NOT NULL,
  `sort_order` int(10) unsigned NOT NULL DEFAULT '0',
  `date_created` datetime DEFAULT NULL,
  PRIMARY KEY (`resource_id`,`contact_id`),
  KEY `fk_rsc_asc_resource_contact_1` (`contact_id`),
  KEY `fk_rsc_asc_resource_contact_2` (`resource_id`),
  CONSTRAINT `fk_rsc_asc_resource_contact_1` FOREIGN KEY (`contact_id`) REFERENCES `rsc_lku_contact` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_rsc_asc_resource_contact_2` FOREIGN KEY (`resource_id`) REFERENCES `rsc_tbl_resources` (`resource_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
