Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

290 KB
Raw Blame
--
-- Database: `openemr`
--
-- Keep v_database in sync with $v_database in version.php.
-- CI will fail if they don't match.
-- v_database: 541
--

--
-- Table structure for table `addresses`
--

DROP TABLE IF EXISTS `addresses`;
CREATE TABLE `addresses` (
  `id` int(11) NOT NULL default '0',
  `line1` varchar(255) default NULL,
  `line2` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `state` varchar(35) default NULL,
  `zip` varchar(10) default NULL,
  `plus_four` varchar(4) default NULL,
  `country` varchar(255) default NULL,
  `foreign_id` int(11) default NULL,
  `district` VARCHAR(255) DEFAULT NULL COMMENT 'The county or district of the address',
  PRIMARY KEY  (`id`),
  KEY `foreign_id` (`foreign_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `amc_misc_data`
--

DROP TABLE IF EXISTS `amc_misc_data`;
CREATE TABLE `amc_misc_data` (
  `amc_id` varchar(31) NOT NULL DEFAULT '' COMMENT 'Unique and maps to list_options list clinical_rules',
  `pid` bigint(20) default NULL,
  `map_category` varchar(255) NOT NULL default '' COMMENT 'Maps to an object category (such as prescriptions etc.)',
  `map_id` bigint(20) NOT NULL default '0' COMMENT 'Maps to an object id (such as prescription id etc.)',
  `date_created` datetime default NULL,
  `date_completed` datetime default NULL,
  `soc_provided` datetime default NULL,
  KEY  (`amc_id`,`pid`,`map_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `amendments`
--

DROP TABLE IF EXISTS `amendments`;
CREATE TABLE `amendments` (
  `amendment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Amendment ID',
  `amendment_date` date NOT NULL COMMENT 'Amendement request date',
  `amendment_by` varchar(50) NOT NULL COMMENT 'Amendment requested from',
  `amendment_status` varchar(50) NULL COMMENT 'Amendment status accepted/rejected/null',
  `pid` bigint(20) NOT NULL COMMENT 'Patient ID from patient_data',
  `amendment_desc` text COMMENT 'Amendment Details',
  `created_by` int(11) NOT NULL COMMENT 'references users.id for session owner',
  `modified_by` int(11) NULL COMMENT 'references users.id for session owner',
  `created_time` timestamp NULL COMMENT 'created time',
  `modified_time` timestamp NULL COMMENT 'modified time',
  PRIMARY KEY amendments_id(`amendment_id`),
  KEY amendment_pid(`pid`)
) ENGINE = InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `amendments_history`
--

DROP TABLE IF EXISTS `amendments_history`;
CREATE TABLE `amendments_history` (
  `amendment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Amendment ID',
  `amendment_note` text COMMENT 'Amendment requested from',
  `amendment_status` VARCHAR(50) NULL COMMENT 'Amendment Request Status',
  `created_by` int(11) NOT NULL COMMENT 'references users.id for session owner',
  `created_time` timestamp NULL COMMENT 'created time',
KEY amendment_history_id(`amendment_id`)
) ENGINE = InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `api_log`
--

DROP TABLE IF EXISTS `api_log`;
CREATE TABLE `api_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `log_id` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `patient_id` bigint(20) NOT NULL,
  `ip_address` varchar(255) NOT NULL,
  `method` varchar(20) NOT NULL,
  `request` varchar(255) NOT NULL,
  `request_url` text,
  `request_body` longtext,
  `response` longtext,
  `created_time` timestamp NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `api_token`
--

DROP TABLE IF EXISTS `api_token`;
CREATE TABLE `api_token` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(40) DEFAULT NULL,
  `token` varchar(128) DEFAULT NULL,
  `expiry` datetime DEFAULT NULL,
  `client_id` varchar(80) DEFAULT NULL,
  `scope` text COMMENT 'json encoded',
  `revoked` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=revoked,0=not revoked',
  `context` TEXT COMMENT 'context values that change/govern how access token are used',
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`)
) ENGINE = InnoDB;

--
-- Table structure for table `api_refresh_token`
--
DROP TABLE IF EXISTS `api_refresh_token`;
CREATE TABLE `api_refresh_token` (
 `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
 `user_id` VARCHAR(40) DEFAULT NULL,
 `client_id` VARCHAR(80) DEFAULT NULL,
 `token` VARCHAR(128) NOT NULL,
 `expiry` DATETIME DEFAULT NULL,
 `revoked` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=revoked,0=not revoked',
 PRIMARY KEY (`id`),
 UNIQUE KEY (`token`),
 INDEX `api_refresh_token_usr_client_idx` (`client_id`, `user_id`)
) ENGINE = InnoDB COMMENT = 'Holds information about api refresh tokens.';
-- --------------------------------------------------------

--
-- Table structure for table `audit_master`
--

DROP TABLE IF EXISTS `audit_master`;
CREATE TABLE `audit_master` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `pid` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL COMMENT 'The Id of the user who approves or denies',
  `approval_status` tinyint(4) NOT NULL COMMENT '1-Pending,2-Approved,3-Denied,4-Appointment directly updated to calendar table,5-Cancelled appointment',
  `comments` text,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_time` datetime NOT NULL,
  `ip_address` varchar(100) NOT NULL,
  `type` tinyint(4) NOT NULL COMMENT '1-new patient,2-existing patient,3-change is only in the document,4-Patient upload,5-random key,10-Appointment',
  `is_qrda_document` BOOLEAN NULL DEFAULT FALSE,
  `is_unstructured_document` BOOLEAN NULL DEFAULT FALSE,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `audit_details`
--

DROP TABLE IF EXISTS `audit_details`;
CREATE TABLE `audit_details` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `table_name` VARCHAR(100) NOT NULL COMMENT 'openemr table name',
  `field_name` VARCHAR(100) NOT NULL COMMENT 'openemr table''s field name',
  `field_value` LONGTEXT COMMENT 'openemr table''s field value',
  `audit_master_id` BIGINT(20) NOT NULL COMMENT 'Id of the audit_master table',
  `entry_identification` VARCHAR(255) NOT NULL DEFAULT '1' COMMENT 'Used when multiple entry occurs from the same table.1 means no multiple entry',
  PRIMARY KEY (`id`),
  KEY `audit_master_id` (`audit_master_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `background_services`
--

DROP TABLE IF EXISTS `background_services`;
CREATE TABLE `background_services` (
  `name` varchar(31) NOT NULL,
  `title` varchar(127) NOT NULL COMMENT 'name for reports',
  `active` tinyint(1) NOT NULL default '0',
  `running` tinyint(1) NOT NULL default '-1' COMMENT 'True indicates managed service is busy. Skip this interval',
  `next_run` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `execute_interval` int(11) NOT NULL default '0' COMMENT 'minimum number of minutes between function calls,0=manual mode',
  `function` varchar(127) NOT NULL COMMENT 'name of background service function',
  `require_once` varchar(255) default NULL COMMENT 'include file (if necessary)',
  `sort_order` int(11) NOT NULL default '100' COMMENT 'lower numbers will be run first',
  `lock_expires_at` datetime DEFAULT NULL COMMENT 'Lease expiration. Compared with NOW() on acquire, so the stored value uses whatever session timezone is in effect (OpenEMR syncs it to gbl_time_zone). Set on acquire, cleared on release. Expired leases are automatically stolen by the next worker.',
  PRIMARY KEY  (`name`)
) ENGINE=InnoDB;



--
-- Inserting data for table `background_services`
--

-- --------------------------------------------------------

--
-- Table structure for table `batchcom`
--

DROP TABLE IF EXISTS `batchcom`;
CREATE TABLE `batchcom` (
  `id` bigint(20) NOT NULL auto_increment,
  `patient_id` bigint(20) NOT NULL default '0',
  `sent_by` bigint(20) NOT NULL default '0',
  `msg_type` varchar(60) default NULL,
  `msg_subject` varchar(255) default NULL,
  `msg_text` mediumtext,
  `msg_date_sent` datetime NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `billing`
--

DROP TABLE IF EXISTS `billing`;
CREATE TABLE `billing` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime default NULL,
  `code_type` varchar(15) default NULL,
  `code` varchar(20) default NULL,
  `pid` bigint(20) default NULL,
  `provider_id` int(11) default NULL,
  `user` int(11) default NULL,
  `groupname` varchar(255) default NULL,
  `authorized` tinyint(1) default NULL,
  `encounter` int(11) default NULL,
  `code_text` longtext,
  `billed` tinyint(1) default NULL,
  `activity` tinyint(1) default NULL,
  `payer_id` int(11) default NULL,
  `bill_process` tinyint(2) NOT NULL default '0',
  `bill_date` datetime default NULL,
  `process_date` datetime default NULL,
  `process_file` varchar(255) default NULL,
  `modifier` varchar(12) default NULL,
  `units` int(11) default NULL,
  `fee` decimal(12,2) default NULL,
  `justify` varchar(255) default NULL,
  `target` varchar(30) default NULL,
  `x12_partner_id` int(11) default NULL,
  `ndc_info` varchar(255) default NULL,
  `notecodes` varchar(25) NOT NULL default '',
  `external_id` VARCHAR(20) DEFAULT NULL,
  `pricelevel` varchar(31) default '',
  `revenue_code` varchar(6) NOT NULL default '' COMMENT 'Item revenue code',
  `chargecat` varchar(31) default '' COMMENT 'Charge category or customer',
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(255) default NULL,
  `value` varchar(255) default NULL,
  `parent` int(11) NOT NULL default '0',
  `lft` int(11) NOT NULL default '0',
  `rght` int(11) NOT NULL default '0',
  `aco_spec` varchar(63) NOT NULL DEFAULT 'patients|docs',
  `codes` varchar(255) NOT NULL DEFAULT '' COMMENT 'Category codes for documents stored in this category',
  PRIMARY KEY  (`id`),
  KEY `parent` (`parent`),
  KEY `lft` (`lft`,`rght`)
) ENGINE=InnoDB;

--
-- Inserting data for table `categories`
--

-- --------------------------------------------------------

--
-- Table structure for table `categories_seq`
--

DROP TABLE IF EXISTS `categories_seq`;
CREATE TABLE `categories_seq` (
  `id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

--
-- Inserting data for table `categories_seq`
--

-- --------------------------------------------------------

--
-- Table structure for table `categories_to_documents`
--

DROP TABLE IF EXISTS `categories_to_documents`;
CREATE TABLE `categories_to_documents` (
  `category_id` int(11) NOT NULL default '0',
  `document_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`category_id`,`document_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `claims`
--

DROP TABLE IF EXISTS `claims`;
CREATE TABLE `claims` (
  `patient_id` bigint(20) NOT NULL,
  `encounter_id` int(11) NOT NULL,
  `version` int(10) unsigned NOT NULL COMMENT 'Claim version, incremented in code',
  `payer_id` int(11) NOT NULL default '0',
  `status` tinyint(2) NOT NULL default '0',
  `payer_type` tinyint(4) NOT NULL default '0',
  `bill_process` tinyint(2) NOT NULL default '0',
  `bill_time` datetime default NULL,
  `process_time` datetime default NULL,
  `process_file` varchar(255) default NULL,
  `target` varchar(30) default NULL,
  `x12_partner_id` int(11) NOT NULL default '0',
  `submitted_claim` text COMMENT 'This claims form claim data',
  PRIMARY KEY  (`patient_id`,`encounter_id`,`version`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `clinical_plans`
--

DROP TABLE IF EXISTS `clinical_plans`;
CREATE TABLE `clinical_plans` (
  `id` varchar(31) NOT NULL DEFAULT '' COMMENT 'Unique and maps to list_options list clinical_plans',
  `pid` bigint(20) NOT NULL DEFAULT '0' COMMENT '0 is default for all patients, while > 0 is id from patient_data table',
  `normal_flag` tinyint(1) COMMENT 'Normal Activation Flag',
  `cqm_flag` tinyint(1) COMMENT 'Clinical Quality Measure flag (unable to customize per patient)',
  `cqm_2011_flag` tinyint(1) COMMENT '2011 Clinical Quality Measure flag (unable to customize per patient)',
  `cqm_2014_flag` tinyint(1) COMMENT '2014 Clinical Quality Measure flag (unable to customize per patient)',
  `cqm_measure_group` varchar(10) NOT NULL default '' COMMENT 'Clinical Quality Measure Group Identifier',
  PRIMARY KEY  (`id`,`pid`)
) ENGINE=InnoDB;

--
-- Inserting data for Clinical Quality Measure (CQM) plans
--
--   Inserting data for Measure Group A: Diabetes Mellitus
--

--
--   Inserting data for Measure Group C: Chronic Kidney Disease (CKD)
--

--
--   Inserting data for Measure Group D: Preventative Care
--

--
--   Inserting data for Measure Group E: Perioperative Care
--

--
--   Inserting data for Measure Group F: Rheumatoid Arthritis
--

--
--    Inserting data for Measure Group G: Back Pain
--

--
--   Inserting data for Measure Group H: Coronary Artery Bypass Graft (CABG)
--

--
-- Inserting data for Standard clinical plans
--
--   Inserting data for Diabetes Mellitus
--

--
--   Inserting data for Prevention Plan
--

-- --------------------------------------------------------

--
-- Table structure for table `clinical_plans_rules`
--

DROP TABLE IF EXISTS `clinical_plans_rules`;
CREATE TABLE `clinical_plans_rules` (
  `plan_id` varchar(31) NOT NULL DEFAULT '' COMMENT 'Unique and maps to list_options list clinical_plans',
  `rule_id` varchar(31) NOT NULL DEFAULT '' COMMENT 'Unique and maps to list_options list clinical_rules',
  PRIMARY KEY  (`plan_id`,`rule_id`)
) ENGINE=InnoDB;

--
-- Inserting data for Clinical Quality Measure (CQM) plans to rules mappings
--
--   Inserting data for Measure Group A: Diabetes Mellitus
--
--     Inserting data for NQF 0059 (PQRI 1)   Diabetes: HbA1c Poor Control
--

--
--     Inserting data for NQF 0064 (PQRI 2)   Diabetes: LDL Management & Control
--

--
--     Inserting data for NQF 0061 (PQRI 3)   Diabetes: Blood Pressure Management
--

--
--     Inserting data for NQF 0055 (PQRI 117) Diabetes: Eye Exam
--

--
--     Inserting data for NQF 0056 (PQRI 163) Diabetes: Foot Exam
--

--
-- Inserting data for Measure Group D: Preventative Care
--
--   Inserting data for NQF 0041 (PQRI 110) Influenza Immunization for Patients >= 50 Years Old
--

--
--   Inserting data for NQF 0043 (PQRI 111) Pneumonia Vaccination Status for Older Adults
--

--
--   Inserting data for NQF 0421 (PQRI 128) Adult Weight Screening and Follow-Up
--

--
-- Inserting data for Standard clinical plans to rules mappings
--
--   Inserting data for Diabetes Mellitus
--
--     Inserting data for Hemoglobin A1C

--
--     Inserting data for Urine Microalbumin
--

--
--     Inserting data for Eye Exam

--
--     Inserting data for Foot Exam
--

--
--   Inserting data for Preventative Care
--
--     Inserting data for Hypertension: Blood Pressure Measurement
--

--
--     Inserting data for Tobacco Use Assessment
--

--
--     Inserting data for Tobacco Cessation Intervention
--

--
--     Inserting data for Adult Weight Screening and Follow-Up
--

--
--     Inserting data for Weight Assessment and Counseling for Children and Adolescents
--

--
--    Inserting data for Influenza Immunization for Patients >= 50 Years Old
--

--
--    Inserting data for Pneumonia Vaccination Status for Older Adults
--

--
--    Inserting data for Cancer Screening: Mammogram
--

--
--    Inserting data for Cancer Screening: Pap Smear
--

--
--    Inserting data for Cancer Screening: Colon Cancer Screening
--

--
--    Inserting data for Cancer Screening: Prostate Cancer Screening
--

-- --------------------------------------------------------

--
-- Table structure for table `clinical_rules`
--

DROP TABLE IF EXISTS `clinical_rules`;
CREATE TABLE `clinical_rules` (
  `id` varchar(31) NOT NULL DEFAULT '' COMMENT 'Unique and maps to list_options list clinical_rules',
  `pid` bigint(20) NOT NULL DEFAULT '0' COMMENT '0 is default for all patients, while > 0 is id from patient_data table',
  `active_alert_flag` tinyint(1) COMMENT 'Active Alert Widget Module flag - note not yet utilized',
  `passive_alert_flag` tinyint(1) COMMENT 'Passive Alert Widget Module flag',
  `cqm_flag` tinyint(1) COMMENT 'Clinical Quality Measure flag (unable to customize per patient)',
  `cqm_2011_flag` tinyint(1) COMMENT '2011 Clinical Quality Measure flag (unable to customize per patient)',
  `cqm_2014_flag` tinyint(1) COMMENT '2014 Clinical Quality Measure flag (unable to customize per patient)',
  `cqm_nqf_code` varchar(10) NOT NULL default '' COMMENT 'Clinical Quality Measure NQF identifier',
  `cqm_pqri_code` varchar(10) NOT NULL default '' COMMENT 'Clinical Quality Measure PQRI identifier',
  `amc_flag` tinyint(1) COMMENT 'Automated Measure Calculation flag (unable to customize per patient)',
  `amc_2011_flag` tinyint(1) COMMENT '2011 Automated Measure Calculation flag for (unable to customize per patient)',
  `amc_2014_flag` tinyint(1) COMMENT '2014 Automated Measure Calculation flag for (unable to customize per patient)',
  `amc_2015_flag` TINYINT(1) NULL DEFAULT NULL COMMENT '2015 Automated Measure Calculation flag for (unable to customize per patient)',
  `amc_code` varchar(10) NOT NULL default '' COMMENT 'Automated Measure Calculation identifier (MU rule)',
  `amc_code_2014` varchar(30) NOT NULL default '' COMMENT 'Automated Measure Calculation 2014 identifier (MU rule)',
  `amc_code_2015` VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'Automated Measure Calculation 2014 identifier (MU rule)',
  `amc_2014_stage1_flag` tinyint(1) COMMENT '2014 Stage 1 - Automated Measure Calculation flag for (unable to customize per patient)',
  `amc_2014_stage2_flag` tinyint(1) COMMENT '2014 Stage 2 - Automated Measure Calculation flag for (unable to customize per patient)',
  `patient_reminder_flag` tinyint(1) COMMENT 'Clinical Reminder Module flag',
  `bibliographic_citation` VARCHAR(255) NOT NULL DEFAULT '',
  `developer` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Clinical Rule Developer',
  `funding_source` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Clinical Rule Funding Source',
  `release_version` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Clinical Rule Release Version',
  `web_reference` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Clinical Rule Web Reference',
  `linked_referential_cds` VARCHAR(50) NOT NULL DEFAULT '',
  `access_control` VARCHAR(255) NOT NULL DEFAULT 'patients:med' COMMENT 'ACO link for access control',
  `patient_dob_usage` TEXT COMMENT 'Description of how patient DOB is used by this rule',
  `patient_ethnicity_usage` TEXT COMMENT 'Description of how patient ethnicity is used by this rule',
  `patient_health_status_usage` TEXT COMMENT 'Description of how patient health status assessments are used by this rule',
  `patient_gender_identity_usage` TEXT COMMENT 'Description of how patient gender identity information is used by this rule',
  `patient_language_usage` TEXT COMMENT 'Description of how patient language information is used by this rule',
  `patient_race_usage` TEXT COMMENT 'Description of how patient race information is used by this rule',
  `patient_sex_usage` TEXT COMMENT 'Description of how patient birth sex information is used by this rule',
  `patient_sexual_orientation_usage` TEXT COMMENT 'Description of how patient sexual orientation is used by this rule',
  `patient_sodh_usage` TEXT COMMENT 'Description of how patient social determinants of health are used by this rule',
  PRIMARY KEY  (`id`,`pid`)
) ENGINE=InnoDB;

--
-- Inserting data for Automated Measure Calculation (AMC) rules
--
--   Inserting data for MU 170.302(c) Maintain an up-to-date problem list of current and active diagnoses (2014-MU-AMC:170.314(g)(1)/(2)–4)
--

--
--   Inserting data for MU 170.302(d) Maintain active medication list
--

--
--   Inserting data for MU 170.302(e) Maintain active medication allergy list
--

--
--   Inserting data for MU 170.302(f) Record and chart changes in vital signs
--

--
--   Inserting data for MU 170.302(g) Record smoking status for patients 13 years old or older
--

--
--   Inserting data for MU 170.302(h) Incorporate clinical lab-test results into certified EHR technology as structured data
--

--
--   Inserting data for MU 170.302(j) The EP, eligible hospital or CAH who receives a patient from another
--     setting of care or provider of care or believes an encounter is relevant
--     should perform medication reconciliation
--

--
--   Inserting data for MU 170.302(m) Use certified EHR technology to identify patient-specific education resources
--     and provide those resources to the patient if appropriate
--

--
--   Inserting data for MU 170.304(a) Use CPOE for medication orders directly entered by any licensed healthcare
--     professional who can enter orders into the medical record per state, local
--     and professional guidelines
--

--
--   Inserting data for MU 170.304(b) Generate and transmit permissible prescriptions electronically (eRx)
--

--
--   Inserting data for MU 170.304(c) Record demographics
--

--
--   Inserting data for MU 170.304(d) Send reminders to patients per patient preference for preventive/follow up care
--

--
--   Inserting data for MU 170.304(f) Provide patients with an electronic copy of their health information
--               (including diagnostic test results, problem list, medication lists,
--               medication allergies), upon request
--

--
--   Inserting data for MU 170.304(g) Provide patients with timely electronic access to their health information
--              (including lab results, problem list, medication lists, medication allergies)
--              within four business days of the information being available to the EP
--

--
--   Inserting data for MU 170.304(h) Provide clinical summaries for patients for each office visit
--

--
--   Inserting data for MU 170.304(i) The EP, eligible hospital or CAH who transitions their patient to
--               another setting of care or provider of care or refers their patient to
--               another provider of care should provide summary of care record for
--               each transition of care or referral
--

--
-- Inserting data for Clinical Quality Measure (CQM) rules
--
--   Inserting data for NQF 0013 Hypertension: Blood Pressure Measurement
--

--
--   Inserting data for NQF 0028a Tobacco Use Assessment
--

--
--   Inserting data for NQF 0028b Tobacco Cessation Intervention
--

--
--   Inserting data for NQF 0421 (PQRI 128) Adult Weight Screening and Follow-Up
--

--
--   Inserting data for NQF 0024 Weight Assessment and Counseling for Children and Adolescents
--

--
--   Inserting data for NQF 0041 (PQRI 110) Influenza Immunization for Patients >= 50 Years Old
--

--
--   Inserting data for NQF 0038 Childhood immunization Status
--


--
--   Inserting data for NQF 0043 (PQRI 111) Pneumonia Vaccination Status for Older Adults
--

--
--   Inserting data for NQF 0055 (PQRI 117) Diabetes: Eye Exam
--

--
--   Inserting data for NQF 0056 (PQRI 163) Diabetes: Foot Exam
--

--
--   Inserting data for NQF 0059 (PQRI 1) Diabetes: HbA1c Poor Control
--

--
--   Inserting data for NQF 0061 (PQRI 3) Diabetes: Blood Pressure Management
--

--
--   Inserting data for NQF 0064 (PQRI 2) Diabetes: LDL Management & Control
--

--
--   Inserting data for NQF 0002 Rule Children Pharyngitis
--

--
--   Inserting data for NQF 0101 Rule Fall Screening
--

--
--   Inserting data for NQF 0384 Rule Pain Intensity
--

--
--   Inserting data for NQF 0038 Rule Child Immunization Status
--

--
--   Inserting data for NQF 0028 Rule Tobacco Use
--

--
-- Inserting data for Standard clinical rules
--
--   Inserting data for Hypertension: Blood Pressure Measurement
--

--
--   Inserting data for Tobacco Use Assessment
--

--
--   Inserting data for Tobacco Cessation Intervention
--

--
--   Inserting data for Adult Weight Screening and Follow-Up
--

--
--   Inserting data for Weight Assessment and Counseling for Children and Adolescents
--

--
--   Inserting data for Influenza Immunization for Patients >= 50 Years Old
--

--
--   Inserting data for Pneumonia Vaccination Status for Older Adults
--

--
--   Inserting data for Diabetes: Hemoglobin A1C
--

--
--   Inserting data for Diabetes: Urine Microalbumin
--

--
--   Inserting data for Diabetes: Eye Exam
--

--
--   Inserting data for Diabetes: Foot Exam
--

--
--   Inserting data for Cancer Screening: Mammogram
--

--
--   Inserting data for Cancer Screening: Pap Smear
--

--
--   Inserting data for Cancer Screening: Colon Cancer Screening
--

--
--   Inserting data for Cancer Screening: Prostate Cancer Screening
--

--
-- Inserting data for Rules to specifically demonstrate passing of NIST criteria
--
--   Inserting data for Coumadin Management - INR Monitoring
--

--
-- Inserting data for Rule to specifically demonstrate MU2 for CDR engine
--

--
-- Inserting data for MU2 AMC rules
--

-- 2015 AMC Rules
-- --------------------------------------------------------

--
-- Table structure for table `clinical_rules_log
--

DROP TABLE IF EXISTS `clinical_rules_log`;
CREATE TABLE `clinical_rules_log` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime DEFAULT NULL,
  `pid` bigint(20) NOT NULL DEFAULT '0',
  `uid` bigint(20) NOT NULL DEFAULT '0',
  `category` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'An example category is clinical_reminder_widget',
  `value` TEXT,
  `new_value` TEXT,
  `facility_id` INT(11) DEFAULT '0' COMMENT 'facility where the rule was executed, 0 if unknown',
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  KEY `uid` (`uid`),
  KEY `category` (`category`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `codes`
--

DROP TABLE IF EXISTS `codes`;
CREATE TABLE `codes` (
  `id` int(11) NOT NULL auto_increment,
  `code_text` text,
  `code_text_short` text,
  `code` varchar(25) NOT NULL default '',
  `code_type` smallint(6) default NULL,
  `modifier` varchar(12) NOT NULL default '',
  `units` int(11) default NULL,
  `fee` decimal(12,2) default NULL,
  `superbill` varchar(31) NOT NULL default '',
  `related_code` varchar(255) NOT NULL default '',
  `taxrates` varchar(255) NOT NULL default '',
  `cyp_factor` float NOT NULL DEFAULT 0 COMMENT 'quantity representing a years supply',
  `active` TINYINT(1) DEFAULT 1 COMMENT '0 = inactive, 1 = active',
  `reportable` TINYINT(1) DEFAULT 0 COMMENT '0 = non-reportable, 1 = reportable',
  `financial_reporting` TINYINT(1) DEFAULT 0 COMMENT '0 = negative, 1 = considered important code in financial reporting',
  `revenue_code` varchar(6) NOT NULL default '' COMMENT 'Item revenue code',
  PRIMARY KEY  (`id`),
  KEY `code` (`code`),
  KEY `code_type` (`code_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------
--
-- Table structure for table `contact`
--
DROP TABLE IF EXISTS `contact`;
CREATE TABLE `contact` (
   `id` BIGINT(20) NOT NULL auto_increment,
   `foreign_table_name` VARCHAR(255) NOT NULL DEFAULT '',
   `foreign_id` BIGINT(20) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   KEY (`foreign_id`)
) ENGINE = InnoDB;

-- --------------------------------------------------------
--
-- Table structure for table `contact_address`
--
DROP TABLE IF EXISTS `contact_address`;
    CREATE TABLE `contact_address` (
    `id` BIGINT(20) NOT NULL auto_increment,
    `contact_id` BIGINT(20) NOT NULL,
    `address_id` BIGINT(20) NOT NULL,
    `priority` INT(11) NULL,
    `type` VARCHAR(255) NULL COMMENT 'FK to list_options.option_id for list_id address-types',
    `use` VARCHAR(255) NULL COMMENT 'FK to list_options.option_id for list_id address-uses',
    `notes` TINYTEXT,
    `status` CHAR(1) NULL COMMENT 'A=active,I=inactive',
    `is_primary` CHAR(1) NULL COMMENT 'Y=yes,N=no',
    `period_start` DATETIME NULL COMMENT 'Date the address became active',
    `period_end` DATETIME NULL COMMENT 'Date the address became deactivated',
    `inactivated_reason` VARCHAR(45) NULL DEFAULT NULL COMMENT '[Values: Moved, Mail Returned, etc]',
    `created_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id',
    `updated_date` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id',
    PRIMARY KEY (`id`),
    KEY (`contact_id`),
    KEY (`address_id`),
    KEY contact_address_idx (`contact_id`,`address_id`)
) ENGINE = InnoDB ;


DROP TABLE IF EXISTS `contact_telecom`;
CREATE TABLE `contact_telecom` (
    `id` BIGINT(20) NOT NULL auto_increment,
    `contact_id` BIGINT(20) NOT NULL,
    `rank` INT(11) NULL COMMENT 'Specify preferred order of use (1 = highest)',
    `system` VARCHAR(255) NULL
    	COMMENT 'FK to list_options.option_id for list_id telecom_systems [phone, fax, email, pager, url, sms, other]',
    `use` VARCHAR(255) NULL
    	COMMENT 'FK to list_options.option_id for list_id telecom_uses [home, work, temp, old, mobile]',
    `value` varchar(255) default NULL,
    `status` CHAR(1) NULL COMMENT 'A=active,I=inactive',
    `is_primary` CHAR(1) NULL COMMENT 'Y=yes,N=no',
    `notes` TINYTEXT,
    `period_start` DATETIME NULL COMMENT 'Date the telecom became active',
    `period_end` DATETIME NULL COMMENT 'Date the telecom became deactivated',
    `inactivated_reason` VARCHAR(45) DEFAULT NULL COMMENT '[Values: ???, etc]',
    `created_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id',
    `updated_date` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id',
   PRIMARY KEY (`id`),
    KEY (`contact_id`)
) ENGINE = InnoDB ;

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `uuid` BINARY(16) DEFAULT NULL,
    `title` VARCHAR(31) DEFAULT NULL COMMENT 'Mr., Mrs., Dr., etc.',
    `first_name` VARCHAR(63) DEFAULT NULL,
    `middle_name` VARCHAR(63) DEFAULT NULL,
    `last_name` VARCHAR(63) DEFAULT NULL,
    `preferred_name` VARCHAR(63) DEFAULT NULL COMMENT 'Name person prefers to be called',
    `gender` VARCHAR(31) DEFAULT NULL,
    `birth_date` DATE DEFAULT NULL,
    `death_date` DATE DEFAULT NULL,
    `marital_status` VARCHAR(31) DEFAULT NULL,
    `race` VARCHAR(63) DEFAULT NULL,
    `ethnicity` VARCHAR(63) DEFAULT NULL,
    `preferred_language` VARCHAR(63) DEFAULT NULL COMMENT 'ISO 639-1 code',
    `communication` VARCHAR(254) DEFAULT NULL COMMENT 'Communication preferences/needs',
    `ssn` VARCHAR(31) DEFAULT NULL COMMENT 'Should be encrypted in application',
    `active` TINYINT(1) DEFAULT 1 COMMENT '1=active, 0=inactive',
    `inactive_reason` VARCHAR(255) DEFAULT NULL,
    `inactive_date` DATETIME DEFAULT NULL,
    `notes` TEXT,
    `created_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id',
    `updated_date` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uuid` (`uuid`),
    KEY `idx_person_name` (`last_name`, `first_name`),
    KEY `idx_person_dob` (`birth_date`),
    KEY `idx_person_search` (`last_name`, `first_name`, `birth_date`),
    KEY `idx_person_active` (`active`)
) ENGINE=InnoDB COMMENT='Core person demographics - contact info in contact_telecom';

DROP TABLE IF EXISTS `contact_relation`;
CREATE TABLE `contact_relation` (
    `id`  BIGINT(20) NOT NULL auto_increment,
    `contact_id`  BIGINT(20) NOT NULL,
    `target_table`  VARCHAR(255) NOT NULL DEFAULT '',
    `target_id`  BIGINT(20) NOT NULL,
    `active` BOOLEAN DEFAULT TRUE,
    `role` VARCHAR(63)  DEFAULT NULL,
    `relationship` VARCHAR(63)  DEFAULT NULL,
    `contact_priority` INT DEFAULT 1 COMMENT '1=highest priority',
    `is_primary_contact` BOOLEAN DEFAULT FALSE,
    `is_emergency_contact` BOOLEAN DEFAULT FALSE,
    `can_make_medical_decisions` BOOLEAN DEFAULT FALSE,
    `can_receive_medical_info` BOOLEAN DEFAULT FALSE,
    `start_date` DATETIME DEFAULT NULL,
    `end_date` DATETIME DEFAULT NULL,
    `notes` TEXT,
    `created_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id',
    `updated_date` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updated_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id',
   PRIMARY KEY (`id`),
   KEY (`contact_id`),
   INDEX idx_contact_target_table (target_table, target_id)
) ENGINE = InnoDB;

DROP TABLE IF EXISTS `person_patient_link`;
CREATE TABLE `person_patient_link` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `person_id` BIGINT(20) NOT NULL COMMENT 'FK to person.id',
    `patient_id` BIGINT(20) NOT NULL COMMENT 'FK to patient_data.id',
    `linked_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the link was created',
    `linked_by` BIGINT(20) DEFAULT NULL COMMENT 'FK to users.id - who created the link',
    `link_method` VARCHAR(50) DEFAULT 'manual' COMMENT 'How link was created: manual, auto_detected, migrated, import',
    `notes` TEXT COMMENT 'Optional notes about why/how they were linked',
    `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Whether link is active (allows soft delete)',
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_active_link` (`person_id`, `patient_id`, `active`),
    KEY `idx_ppl_person` (`person_id`),
    KEY `idx_ppl_patient` (`patient_id`),
    KEY `idx_ppl_active` (`active`),
    KEY `idx_ppl_linked_date` (`linked_date`),
    KEY `idx_ppl_method` (`link_method`)
) ENGINE=InnoDB COMMENT='Links person records to patient_data records when person becomes patient';
-- --------------------------------------------------------

--
-- Table structure for table `syndromic_surveillance`
--

DROP TABLE IF EXISTS `syndromic_surveillance`;
CREATE TABLE `syndromic_surveillance` (
  `id` bigint(20) NOT NULL auto_increment,
  `lists_id` bigint(20) NOT NULL,
  `submission_date` datetime NOT NULL,
  `filename` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY (`lists_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `dated_reminders`
--

DROP TABLE IF EXISTS `dated_reminders`;
CREATE TABLE `dated_reminders` (
  `dr_id` int(11) NOT NULL AUTO_INCREMENT,
  `dr_from_ID` int(11) NOT NULL,
  `dr_message_text` varchar(160) NOT NULL,
  `dr_message_sent_date` datetime NOT NULL,
  `dr_message_due_date` date NOT NULL,
  `pid` bigint(20) NOT NULL,
  `message_priority` tinyint(1) NOT NULL,
  `message_processed` tinyint(1) NOT NULL DEFAULT '0',
  `processed_date` timestamp NULL DEFAULT NULL,
  `dr_processed_by` int(11) NOT NULL,
  PRIMARY KEY (`dr_id`),
  KEY `dr_from_ID` (`dr_from_ID`,`dr_message_due_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `dated_reminders_link`
--

DROP TABLE IF EXISTS `dated_reminders_link`;
CREATE TABLE `dated_reminders_link` (
  `dr_link_id` int(11) NOT NULL AUTO_INCREMENT,
  `dr_id` int(11) NOT NULL,
  `to_id` int(11) NOT NULL,
  PRIMARY KEY (`dr_link_id`),
  KEY `to_id` (`to_id`),
  KEY `dr_id` (`dr_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `direct_message_log`
--

DROP TABLE IF EXISTS `direct_message_log`;
CREATE TABLE `direct_message_log` (
  `id` bigint(20) NOT NULL auto_increment,
  `msg_type` char(1) NOT NULL COMMENT 'S=sent,R=received',
  `msg_id` varchar(127) NOT NULL,
  `sender` varchar(255) NOT NULL,
  `recipient` varchar(255) NOT NULL,
  `create_ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `status` char(1) NOT NULL COMMENT 'Q=queued,D=dispatched,R=received,F=failed',
  `status_info` varchar(511) default NULL,
  `status_ts` timestamp NULL default NULL,
  `patient_id` bigint(20) default NULL,
  `user_id` bigint(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `msg_id` (`msg_id`),
  KEY `patient_id` (`patient_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `documents`
--

DROP TABLE IF EXISTS `documents`;
CREATE TABLE `documents` (
  `id` int(11) NOT NULL default '0',
  `uuid` binary(16) DEFAULT NULL,
  `type` enum('file_url','blob','web_url') default NULL,
  `size` int(11) default NULL,
  `date` datetime default NULL,
  `date_expires` datetime default NULL,
  `url` varchar(255) default NULL,
  `thumb_url` varchar(255) default NULL,
  `mimetype` varchar(255) default NULL,
  `pages` int(11) default NULL,
  `owner` int(11) default NULL,
  `revision` timestamp NOT NULL,
  `foreign_id` bigint(20) default NULL,
  `docdate` date default NULL,
  `hash` varchar(255) DEFAULT NULL,
  `list_id` bigint(20) NOT NULL default '0',
  `name` varchar(255) DEFAULT NULL,
  `drive_uuid` binary(16) DEFAULT NULL,
  `couch_docid` VARCHAR(100) DEFAULT NULL,
  `couch_revid` VARCHAR(100) DEFAULT NULL,
  `storagemethod` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0->Harddisk,1->CouchDB',
  `path_depth` TINYINT DEFAULT '1' COMMENT 'Depth of path to use in url to find document. Not applicable for CouchDB.',
  `imported` TINYINT DEFAULT 0 NULL COMMENT 'Parsing status for CCR/CCD/CCDA importing',
  `encounter_id` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Encounter id if tagged',
  `encounter_check` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'If encounter is created while tagging',
  `audit_master_approval_status` TINYINT NOT NULL DEFAULT 1 COMMENT 'approval_status from audit_master table',
  `audit_master_id` int(11) default NULL,
  `documentationOf` varchar(255) DEFAULT NULL,
  `encrypted` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0->No,1->Yes',
  `document_data` MEDIUMTEXT,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `foreign_reference_id` bigint(20) default NULL,
  `foreign_reference_table` VARCHAR(40) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `drive_uuid` (`drive_uuid`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `revision` (`revision`),
  KEY `foreign_id` (`foreign_id`),
  KEY `foreign_reference` (`foreign_reference_id`, `foreign_reference_table`),
  KEY `owner` (`owner`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `documents_legal_detail`
--

DROP TABLE IF EXISTS `documents_legal_detail`;
CREATE TABLE `documents_legal_detail` (
  `dld_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dld_pid` int(10) unsigned DEFAULT NULL,
  `dld_facility` int(10) unsigned DEFAULT NULL,
  `dld_provider` int(10) unsigned DEFAULT NULL,
  `dld_encounter` int(10) unsigned DEFAULT NULL,
  `dld_master_docid` int(10) unsigned NOT NULL,
  `dld_signed` smallint(5) unsigned NOT NULL COMMENT '0-Not Signed or Cannot Sign(Layout),1-Signed,2-Ready to sign,3-Denied(Pat Regi),4-Patient Upload,10-Save(Layout)',
  `dld_signed_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dld_filepath` varchar(75) DEFAULT NULL,
  `dld_filename` varchar(45) NOT NULL,
  `dld_signing_person` varchar(50) NOT NULL,
  `dld_sign_level` int(11) NOT NULL COMMENT 'Sign flow level',
  `dld_content` varchar(50) NOT NULL COMMENT 'Layout sign position',
  `dld_file_for_pdf_generation` blob NOT NULL COMMENT 'The filled details in the fdf file is stored here.Patient Registration Screen',
  `dld_denial_reason` longtext,
  `dld_moved` tinyint(4) NOT NULL DEFAULT '0',
  `dld_patient_comments` text COMMENT 'Patient comments stored here',
  PRIMARY KEY (`dld_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `documents_legal_master`
--

DROP TABLE IF EXISTS `documents_legal_master`;
CREATE TABLE `documents_legal_master` (
  `dlm_category` int(10) unsigned DEFAULT NULL,
  `dlm_subcategory` int(10) unsigned DEFAULT NULL,
  `dlm_document_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dlm_document_name` varchar(75) NOT NULL,
  `dlm_filepath` varchar(75) NOT NULL,
  `dlm_facility` int(10) unsigned DEFAULT NULL,
  `dlm_provider` int(10) unsigned DEFAULT NULL,
  `dlm_sign_height` double NOT NULL,
  `dlm_sign_width` double NOT NULL,
  `dlm_filename` varchar(45) NOT NULL,
  `dlm_effective_date` datetime NOT NULL,
  `dlm_version` int(10) unsigned NOT NULL,
  `content` varchar(255) NOT NULL,
  `dlm_savedsign` varchar(255) DEFAULT NULL COMMENT '0-Yes 1-No',
  `dlm_review` varchar(255) DEFAULT NULL COMMENT '0-Yes 1-No',
  `dlm_upload_type` tinyint(4) DEFAULT '0' COMMENT '0-Provider Uploaded,1-Patient Uploaded',
  PRIMARY KEY (`dlm_document_id`)
) ENGINE=InnoDB COMMENT='List of Master Docs to be signed' AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `documents_legal_categories`
--

DROP TABLE IF EXISTS `documents_legal_categories`;
CREATE TABLE `documents_legal_categories` (
  `dlc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dlc_category_type` int(10) unsigned NOT NULL COMMENT '1 category 2 subcategory',
  `dlc_category_name` varchar(45) NOT NULL,
  `dlc_category_parent` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`dlc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7;

--
-- Inserting data for table `documents_legal_categories`
--

--
-- Table structure for table `drug_inventory`
--

DROP TABLE IF EXISTS `drug_inventory`;
CREATE TABLE `drug_inventory` (
  `inventory_id` int(11) NOT NULL auto_increment,
  `drug_id` int(11) NOT NULL,
  `lot_number` varchar(20) default NULL,
  `expiration` date default NULL,
  `manufacturer` varchar(255) default NULL,
  `on_hand` int(11) NOT NULL default '0',
  `warehouse_id` varchar(31) NOT NULL DEFAULT '',
  `vendor_id` bigint(20) NOT NULL DEFAULT 0,
  `last_notify` date NULL,
  `destroy_date` date default NULL,
  `destroy_method` varchar(255) default NULL,
  `destroy_witness` varchar(255) default NULL,
  `destroy_notes` varchar(255) default NULL,
  PRIMARY KEY  (`inventory_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `drug_sales`
--

DROP TABLE IF EXISTS `drug_sales`;
CREATE TABLE `drug_sales` (
  `uuid` binary(16) DEFAULT NULL COMMENT 'UUID for this drug sales record, for data exchange purposes',
  `sale_id` int(11) NOT NULL auto_increment,
  `drug_id` int(11) NOT NULL,
  `inventory_id` int(11) NOT NULL,
  `prescription_id` int(11) NOT NULL default '0',
  `pid` bigint(20) NOT NULL default '0',
  `encounter` int(11) NOT NULL default '0',
  `user` varchar(255) default NULL,
  `sale_date` date NOT NULL,
  `quantity` int(11) NOT NULL default '0',
  `fee` decimal(12,2) NOT NULL default '0.00',
  `billed` tinyint(1) NOT NULL default '0' COMMENT 'indicates if the sale is posted to accounting',
  `xfer_inventory_id` int(11) NOT NULL DEFAULT 0,
  `distributor_id` bigint(20) NOT NULL DEFAULT 0 COMMENT 'references users.id',
  `notes` varchar(255) NOT NULL DEFAULT '',
  `bill_date` datetime default NULL,
  `pricelevel` varchar(31) default '',
  `selector` varchar(255) default '' comment 'references drug_templates.selector',
  `trans_type` tinyint NOT NULL DEFAULT 1 COMMENT '1=sale, 2=purchase, 3=return, 4=transfer, 5=adjustment',
  `chargecat` varchar(31) default '',
  `pharmacy_supply_type` VARCHAR(50) DEFAULT NULL COMMENT 'fk to list_options.option_id where list_id=pharmacy_supply_type to indicate type of dispensing first order, refil, emergency, partial order, etc',
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` BIGINT(20) DEFAULT NULL COMMENT 'fk to users.id for user that last updated this entry',
  `created_by` BIGINT(20) DEFAULT NULL COMMENT 'fk to users.id for user that created this entry',
  PRIMARY KEY  (`sale_id`),
  UNIQUE INDEX `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `drug_templates`
--

DROP TABLE IF EXISTS `drug_templates`;
CREATE TABLE `drug_templates` (
  `drug_id` int(11) NOT NULL,
  `selector` varchar(255) NOT NULL default '',
  `dosage` varchar(10) default NULL,
  `period` int(11) NOT NULL default '0',
  `quantity` int(11) NOT NULL default '0',
  `refills` int(11) NOT NULL default '0',
  `taxrates` varchar(255) default NULL,
  `pkgqty` float NOT NULL DEFAULT 1.0 COMMENT 'Number of product items per template item',
  PRIMARY KEY  (`drug_id`,`selector`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `drugs`
--

DROP TABLE IF EXISTS `drugs`;
CREATE TABLE `drugs` (
  `drug_id` int(11) NOT NULL auto_increment,
  `uuid` binary(16) DEFAULT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `ndc_number` varchar(20) NOT NULL DEFAULT '',
  `on_order` int(11) NOT NULL default '0',
  `reorder_point` float NOT NULL DEFAULT 0.0,
  `max_level` float NOT NULL DEFAULT 0.0,
  `last_notify` date NULL,
  `reactions` text,
  `form` varchar(31) NOT NULL default '0',
  `size` varchar(25) NOT NULL default '',
  `unit` varchar(31) NOT NULL default '0',
  `route` varchar(31) NOT NULL default '0',
  `substitute` int(11) NOT NULL default '0',
  `related_code` varchar(255) NOT NULL DEFAULT '' COMMENT 'may reference a related codes.code',
  `cyp_factor` float NOT NULL DEFAULT 0 COMMENT 'quantity representing a years supply',
  `active` TINYINT(1) DEFAULT 1 COMMENT '0 = inactive, 1 = active',
  `allow_combining` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = allow filling an order from multiple lots',
  `allow_multiple`  tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = allow multiple lots at one warehouse',
  `drug_code` varchar(25) NULL,
  `consumable` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = will not show on the fee sheet',
  `dispensable` tinyint(1) NOT NULL DEFAULT 1 COMMENT '0 = pharmacy elsewhere, 1 = dispensed here',
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`drug_id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `edi_sequences`
--

DROP TABLE IF EXISTS `edi_sequences`;
CREATE TABLE `edi_sequences` (
  `id` int(9) unsigned NOT NULL default '0'
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `eligibility_verification`
--

DROP TABLE IF EXISTS `eligibility_verification`;
CREATE TABLE `eligibility_verification` (
  `verification_id` bigint(20) NOT NULL auto_increment,
  `response_id` varchar(32) default NULL,
  `insurance_id` bigint(20) default NULL,
  `eligibility_check_date` datetime default NULL,
  `copay` int(11) default NULL,
  `deductible` int(11) default NULL,
  `deductiblemet` enum('Y','N') default 'Y',
  `create_date` date default NULL,
  PRIMARY KEY  (`verification_id`),
  KEY `insurance_id` (`insurance_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `email_queue`
--
DROP TABLE IF EXISTS `email_queue`;
CREATE TABLE `email_queue` (
  `id` bigint NOT NULL auto_increment,
  `sender` varchar(255) DEFAULT '',
  `recipient` varchar(255) DEFAULT '',
  `subject` varchar(255) DEFAULT '',
  `body` text,
  `datetime_queued` datetime default NULL,
  `sent` tinyint DEFAULT 0,
  `datetime_sent` datetime default NULL,
  `error` tinyint DEFAULT 0,
  `error_message` text,
  `datetime_error` datetime default NULL,
  `template_name` VARCHAR(255) DEFAULT NULL COMMENT 'The folder prefix and base filename (w/o extension) of the twig template file to use for this email',
PRIMARY KEY (`id`),
KEY `sent` (`sent`)
) ENGINE=InnoDb AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `employer_data`
--

DROP TABLE IF EXISTS `employer_data`;
CREATE TABLE `employer_data` (
  `id` bigint(20) NOT NULL auto_increment,
  `uuid` binary(16) DEFAULT NULL COMMENT 'UUID for this employer record, for data exchange purposes',
  `name` varchar(255) default NULL,
  `street` varchar(255) default NULL,
  `street_line_2` TINYTEXT,
  `postal_code` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `state` varchar(255) default NULL,
  `country` varchar(255) default NULL,
  `date` datetime default NULL,
  `pid` bigint(20) NOT NULL default '0',
  `start_date` datetime DEFAULT NULL COMMENT 'Employment start date for patient',
  `end_date` datetime DEFAULT NULL COMMENT 'Employment end date for patient',
  `occupation` longtext COMMENT 'Employment Occupation fk to list_options.option_id where list_id=OccupationODH',
  `industry` text COMMENT 'Employment Industry fk to list_options.option_id where list_id=IndustryODH',
  `created_by` int DEFAULT NULL COMMENT 'fk to users.id for the user that entered in the employer data',
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`),
  UNIQUE KEY `uuid_unique` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `enc_category_map`
--
--   Mapping of rule encounter categories to category ids
--   from the event category in openemr_postcalendar_categories
--

DROP TABLE IF EXISTS `enc_category_map`;
CREATE TABLE `enc_category_map` (
  `rule_enc_id` varchar(31) NOT NULL DEFAULT '' COMMENT 'encounter id from rule_enc_types list in list_options',
  `main_cat_id` int(11) NOT NULL DEFAULT 0 COMMENT 'category id from event category in openemr_postcalendar_categories',
  KEY  (`rule_enc_id`,`main_cat_id`)
) ENGINE=InnoDB;

--
-- Inserting data for table `enc_category_map`
--

-- --------------------------------------------------------

--
-- Table structure for table `erx_ttl_touch`
--   Store records last update per patient data process
--

DROP TABLE IF EXISTS `erx_ttl_touch`;
CREATE  TABLE `erx_ttl_touch` (
  `patient_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'Patient record Id' ,
  `process` ENUM('allergies','medications') NOT NULL COMMENT 'NewCrop eRx SOAP process' ,
  `updated` DATETIME NOT NULL COMMENT 'Date and time of last process update for patient' ,
  PRIMARY KEY (`patient_id`, `process`)
) ENGINE = InnoDB COMMENT = 'Store records last update per patient data process';

-- --------------------------------------------------------

--
-- Table structure for table `erx_rx_log`
--

DROP TABLE IF EXISTS `erx_rx_log`;
CREATE TABLE `erx_rx_log` (
 `id` int(20) NOT NULL AUTO_INCREMENT,
 `prescription_id` int(6) NOT NULL,
 `date` varchar(25) NOT NULL,
 `time` varchar(15) NOT NULL,
 `code` int(6) NOT NULL,
 `status` text,
 `message_id` varchar(100) DEFAULT NULL,
 `read` int(1) DEFAULT NULL,
 PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `erx_narcotics`
--

DROP TABLE IF EXISTS `erx_narcotics`;
CREATE TABLE `erx_narcotics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `drug` varchar(255) NOT NULL,
  `dea_number` varchar(5) NOT NULL,
  `csa_sch` varchar(2) NOT NULL,
  `narc` varchar(2) NOT NULL,
  `other_names` varchar(255) NOT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `standardized_tables_track`
--

DROP TABLE IF EXISTS `standardized_tables_track`;
CREATE TABLE `standardized_tables_track` (
  `id` int(11) NOT NULL auto_increment,
  `imported_date` datetime default NULL,
  `name` varchar(255) NOT NULL default '' COMMENT 'name of standardized tables such as RXNORM',
  `revision_version` varchar(255) NOT NULL default '' COMMENT 'revision of standardized tables that were imported',
  `revision_date` datetime default NULL COMMENT 'revision of standardized tables that were imported',
  `file_checksum` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `facility`
--

DROP TABLE IF EXISTS `facility`;
CREATE TABLE `facility` (
  `id` int(11) NOT NULL auto_increment,
  `uuid` binary(16) DEFAULT NULL,
  `name` varchar(255) default NULL,
  `phone` varchar(30) default NULL,
  `fax` varchar(30) default NULL,
  `street` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `state` varchar(50) default NULL,
  `postal_code` varchar(11) default NULL,
  `country_code` varchar(30) NOT NULL default '',
  `federal_ein` varchar(15) default NULL,
  `website` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `service_location` tinyint(1) NOT NULL default '1',
  `billing_location` tinyint(1) NOT NULL default '1',
  `accepts_assignment` tinyint(1) NOT NULL default '1',
  `pos_code` tinyint(4) default NULL,
  `x12_sender_id` varchar(25) default NULL,
  `attn` varchar(65) default NULL,
  `domain_identifier` varchar(60) default NULL,
  `facility_npi` varchar(15) default NULL,
  `facility_taxonomy` varchar(15) default NULL,
  `tax_id_type` VARCHAR(31) NOT NULL DEFAULT '',
  `color` VARCHAR(7) NOT NULL DEFAULT '',
  `primary_business_entity` INT(10) NOT NULL DEFAULT '1' COMMENT '0-Not Set as business entity 1-Set as business entity',
  `facility_code` VARCHAR(31) default NULL,
  `extra_validation` tinyint(1) NOT NULL DEFAULT '1',
  `mail_street` varchar(30) default NULL,
  `mail_street2` varchar(30) default NULL,
  `mail_city` varchar(50) default NULL,
  `mail_state` varchar(3) default NULL,
  `mail_zip` varchar(10) default NULL,
  `oid` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'HIEs CCDA and FHIR an OID is required/wanted',
  `iban` varchar(50) default NULL,
  `info` TEXT,
  `weno_id` VARCHAR(10) DEFAULT NULL,
  `inactive` tinyint(1) NOT NULL DEFAULT '0',
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `organization_type` VARCHAR(50) NOT NULL DEFAULT 'prov' COMMENT 'Organization type as defined by HL7 Value Set: OrganizationType',
  UNIQUE KEY `uuid` (`uuid`),
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4;

--
-- Inserting data for table `facility`
--

-- --------------------------------------------------------

--
-- Table structure for table `facility_user_ids`
--

DROP TABLE IF EXISTS `facility_user_ids`;
CREATE TABLE  `facility_user_ids` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) DEFAULT NULL,
  `facility_id` bigint(20) DEFAULT NULL,
  `uuid` binary(16) DEFAULT NULL,
  `field_id`    varchar(31)  NOT NULL COMMENT 'references layout_options.field_id',
  `field_value` TEXT,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`,`facility_id`,`field_id`),
  KEY `uuid` (`uuid`)
) ENGINE=InnoDB  AUTO_INCREMENT=1;

-- ---------------------------------------------------------

--
-- Table structure for table `fee_schedule`
--

DROP TABLE IF EXISTS `fee_schedule`;
CREATE TABLE `fee_schedule` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `insurance_company_id` INT(11) NOT NULL DEFAULT 0,
    `plan` VARCHAR(20) DEFAULT '',
    `code` VARCHAR(10) DEFAULT '',
    `modifier` VARCHAR(2) DEFAULT '',
    `type` VARCHAR(20) DEFAULT '',
    `fee` decimal(12,2) DEFAULT NULL,
    `effective_date` date DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ins_plan_code_mod_type_date` (`insurance_company_id`, `plan`, `code`, `modifier`, `type`, `effective_date`)
) ENGINE=InnoDb AUTO_INCREMENT=1;

-- ---------------------------------------------------------

--
-- Table structure for table `fee_sheet_options`
--

DROP TABLE IF EXISTS `fee_sheet_options`;
CREATE TABLE `fee_sheet_options` (
  `fs_category` varchar(63) default NULL,
  `fs_option` varchar(63) default NULL,
  `fs_codes` varchar(255) default NULL
) ENGINE=InnoDB;

--
-- Inserting data for table `fee_sheet_options`
--

-- --------------------------------------------------------

--
-- Table structure for table `form_clinical_notes`
--

DROP TABLE IF EXISTS `form_clinical_notes`;
CREATE TABLE `form_clinical_notes` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `form_id` bigint(20) NOT NULL,
    `uuid` binary(16) DEFAULT NULL,
    `date` DATE DEFAULT NULL,
    `pid` bigint(20) DEFAULT NULL,
    `encounter` varchar(255) DEFAULT NULL,
    `user` varchar(255) DEFAULT NULL,
    `groupname` varchar(255) DEFAULT NULL,
    `authorized` tinyint(4) DEFAULT NULL,
    `activity` tinyint(4) DEFAULT NULL,
    `code` varchar(255) DEFAULT NULL,
    `codetext` text,
    `description` text,
    `external_id` VARCHAR(30) DEFAULT NULL,
    `clinical_notes_type` varchar(100) DEFAULT NULL,
    `clinical_notes_category` varchar(100) DEFAULT NULL,
    `note_related_to` text,
    `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_dictation`
--

DROP TABLE IF EXISTS `form_dictation`;
CREATE TABLE `form_dictation` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime default NULL,
  `pid` bigint(20) default NULL,
  `user` varchar(255) default NULL,
  `groupname` varchar(255) default NULL,
  `authorized` tinyint(4) default NULL,
  `activity` tinyint(4) default NULL,
  `dictation` longtext,
  `additional_notes` longtext,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `form_encounter`
--

DROP TABLE IF EXISTS `form_encounter`;
CREATE TABLE `form_encounter` (
  `id` bigint(20) NOT NULL auto_increment,
  `uuid` binary(16) DEFAULT NULL,
  `date` datetime default NULL,
  `reason` longtext,
  `facility` longtext,
  `facility_id` int(11) NOT NULL default '0',
  `pid` bigint(20) default NULL,
  `encounter` bigint(20) default NULL,
  `onset_date` datetime default NULL,
  `sensitivity` varchar(30) default NULL,
  `billing_note` text,
  `pc_catid` int(11) NOT NULL default '5' COMMENT 'event category from openemr_postcalendar_categories',
  `last_level_billed` int  NOT NULL DEFAULT 0 COMMENT '0=none, 1=ins1, 2=ins2, etc',
  `last_level_closed` int  NOT NULL DEFAULT 0 COMMENT '0=none, 1=ins1, 2=ins2, etc',
  `last_stmt_date`    date DEFAULT NULL,
  `stmt_count`        int  NOT NULL DEFAULT 0,
  `provider_id` INT(11) DEFAULT '0' COMMENT 'default and main provider for this visit',
  `supervisor_id` INT(11) DEFAULT '0' COMMENT 'supervising provider, if any, for this visit',
  `invoice_refno` varchar(31) NOT NULL DEFAULT '',
  `referral_source` varchar(31) NOT NULL DEFAULT '',
  `billing_facility` INT(11) NOT NULL DEFAULT 0,
  `external_id` VARCHAR(20) DEFAULT NULL,
  `pos_code` tinyint(4) default NULL,
  `parent_encounter_id` BIGINT(20) NULL DEFAULT NULL,
  `class_code` VARCHAR(10) NOT NULL DEFAULT "AMB",
  `shift` varchar(31) NOT NULL DEFAULT '',
  `voucher_number` varchar(255) NOT NULL DEFAULT '' COMMENT 'also called referral number',
  `discharge_disposition` varchar(100) NULL DEFAULT NULL,
  `encounter_type_code` VARCHAR(31) NULL DEFAULT NULL COMMENT 'not all types are categories',
  `encounter_type_description` TEXT,
  `referring_provider_id` INT(11) DEFAULT '0' COMMENT 'referring provider, if any, for this visit',
  `date_end` DATETIME DEFAULT NULL,
  `in_collection` tinyint(1) default NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ordering_provider_id` INT(11) DEFAULT '0' COMMENT 'referring provider, if any, for this visit',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `pid_encounter` (`pid`, `encounter`),
  KEY `encounter_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `form_misc_billing_options`
--

DROP TABLE IF EXISTS `form_misc_billing_options`;
CREATE TABLE `form_misc_billing_options` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime default NULL,
  `pid` bigint(20) default NULL,
  `user` varchar(255) default NULL,
  `groupname` varchar(255) default NULL,
  `authorized` tinyint(4) default NULL,
  `activity` tinyint(4) default NULL,
  `employment_related` tinyint(1) default NULL,
  `auto_accident` tinyint(1) default NULL,
  `accident_state` varchar(2) default NULL,
  `other_accident` tinyint(1) default NULL,
  `medicaid_referral_code` varchar(2)   default NULL,
  `epsdt_flag` tinyint(1) default NULL,
  `provider_qualifier_code` varchar(2) default NULL,
  `provider_id` int(11) default NULL,
  `outside_lab` tinyint(1) default NULL,
  `lab_amount` decimal(5,2) default NULL,
  `is_unable_to_work` tinyint(1) default NULL,
  `onset_date` date default NULL,
  `date_initial_treatment` date default NULL,
  `off_work_from` date default NULL,
  `off_work_to` date default NULL,
  `is_hospitalized` tinyint(1) default NULL,
  `hospitalization_date_from` date default NULL,
  `hospitalization_date_to` date default NULL,
  `medicaid_resubmission_code` varchar(10) default NULL,
  `medicaid_original_reference` varchar(15) default NULL,
  `prior_auth_number` varchar(20) default NULL,
  `comments` varchar(255) default NULL,
  `replacement_claim` tinyint(1) default 0,
  `icn_resubmission_number` varchar(35) default NULL,
  `box_14_date_qual` char(3) default NULL,
  `box_15_date_qual` char(3) default NULL,
  `encounter` bigint(20) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `encounter` (`encounter`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `form_reviewofs`
--

DROP TABLE IF EXISTS `form_reviewofs`;
CREATE TABLE `form_reviewofs` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime default NULL,
  `pid` bigint(20) default NULL,
  `user` varchar(255) default NULL,
  `groupname` varchar(255) default NULL,
  `authorized` tinyint(4) default NULL,
  `activity` tinyint(4) default NULL,
  `fever` varchar(5) default NULL,
  `chills` varchar(5) default NULL,
  `night_sweats` varchar(5) default NULL,
  `weight_loss` varchar(5) default NULL,
  `poor_appetite` varchar(5) default NULL,
  `insomnia` varchar(5) default NULL,
  `fatigued` varchar(5) default NULL,
  `depressed` varchar(5) default NULL,
  `hyperactive` varchar(5) default NULL,
  `exposure_to_foreign_countries` varchar(5) default NULL,
  `cataracts` varchar(5) default NULL,
  `cataract_surgery` varchar(5) default NULL,
  `glaucoma` varchar(5) default NULL,
  `double_vision` varchar(5) default NULL,
  `blurred_vision` varchar(5) default NULL,
  `poor_hearing` varchar(5) default NULL,
  `headaches` varchar(5) default NULL,
  `ringing_in_ears` varchar(5) default NULL,
  `bloody_nose` varchar(5) default NULL,
  `sinusitis` varchar(5) default NULL,
  `sinus_surgery` varchar(5) default NULL,
  `dry_mouth` varchar(5) default NULL,
  `strep_throat` varchar(5) default NULL,
  `tonsillectomy` varchar(5) default NULL,
  `swollen_lymph_nodes` varchar(5) default NULL,
  `throat_cancer` varchar(5) default NULL,
  `throat_cancer_surgery` varchar(5) default NULL,
  `heart_attack` varchar(5) default NULL,
  `irregular_heart_beat` varchar(5) default NULL,
  `chest_pains` varchar(5) default NULL,
  `shortness_of_breath` varchar(5) default NULL,
  `high_blood_pressure` varchar(5) default NULL,
  `heart_failure` varchar(5) default NULL,
  `poor_circulation` varchar(5) default NULL,
  `vascular_surgery` varchar(5) default NULL,
  `cardiac_catheterization` varchar(5) default NULL,
  `coronary_artery_bypass` varchar(5) default NULL,
  `heart_transplant` varchar(5) default NULL,
  `stress_test` varchar(5) default NULL,
  `emphysema` varchar(5) default NULL,
  `chronic_bronchitis` varchar(5) default NULL,
  `interstitial_lung_disease` varchar(5) default NULL,
  `shortness_of_breath_2` varchar(5) default NULL,
  `lung_cancer` varchar(5) default NULL,
  `lung_cancer_surgery` varchar(5) default NULL,
  `pheumothorax` varchar(5) default NULL,
  `stomach_pains` varchar(5) default NULL,
  `peptic_ulcer_disease` varchar(5) default NULL,
  `gastritis` varchar(5) default NULL,
  `endoscopy` varchar(5) default NULL,
  `polyps` varchar(5) default NULL,
  `colonoscopy` varchar(5) default NULL,
  `colon_cancer` varchar(5) default NULL,
  `colon_cancer_surgery` varchar(5) default NULL,
  `ulcerative_colitis` varchar(5) default NULL,
  `crohns_disease` varchar(5) default NULL,
  `appendectomy` varchar(5) default NULL,
  `divirticulitis` varchar(5) default NULL,
  `divirticulitis_surgery` varchar(5) default NULL,
  `gall_stones` varchar(5) default NULL,
  `cholecystectomy` varchar(5) default NULL,
  `hepatitis` varchar(5) default NULL,
  `cirrhosis_of_the_liver` varchar(5) default NULL,
  `splenectomy` varchar(5) default NULL,
  `kidney_failure` varchar(5) default NULL,
  `kidney_stones` varchar(5) default NULL,
  `kidney_cancer` varchar(5) default NULL,
  `kidney_infections` varchar(5) default NULL,
  `bladder_infections` varchar(5) default NULL,
  `bladder_cancer` varchar(5) default NULL,
  `prostate_problems` varchar(5) default NULL,
  `prostate_cancer` varchar(5) default NULL,
  `kidney_transplant` varchar(5) default NULL,
  `sexually_transmitted_disease` varchar(5) default NULL,
  `burning_with_urination` varchar(5) default NULL,
  `discharge_from_urethra` varchar(5) default NULL,
  `rashes` varchar(5) default NULL,
  `infections` varchar(5) default NULL,
  `ulcerations` varchar(5) default NULL,
  `pemphigus` varchar(5) default NULL,
  `herpes` varchar(5) default NULL,
  `osetoarthritis` varchar(5) default NULL,
  `rheumotoid_arthritis` varchar(5) default NULL,
  `lupus` varchar(5) default NULL,
  `ankylosing_sondlilitis` varchar(5) default NULL,
  `swollen_joints` varchar(5) default NULL,
  `stiff_joints` varchar(5) default NULL,
  `broken_bones` varchar(5) default NULL,
  `neck_problems` varchar(5) default NULL,
  `back_problems` varchar(5) default NULL,
  `back_surgery` varchar(5) default NULL,
  `scoliosis` varchar(5) default NULL,
  `herniated_disc` varchar(5) default NULL,
  `shoulder_problems` varchar(5) default NULL,
  `elbow_problems` varchar(5) default NULL,
  `wrist_problems` varchar(5) default NULL,
  `hand_problems` varchar(5) default NULL,
  `hip_problems` varchar(5) default NULL,
  `knee_problems` varchar(5) default NULL,
  `ankle_problems` varchar(5) default NULL,
  `foot_problems` varchar(5) default NULL,
  `insulin_dependent_diabetes` varchar(5) default NULL,
  `noninsulin_dependent_diabetes` varchar(5) default NULL,
  `hypothyroidism` varchar(5) default NULL,
  `hyperthyroidism` varchar(5) default NULL,
  `cushing_syndrom` varchar(5) default NULL,
  `addison_syndrom` varchar(5) default NULL,
  `additional_notes` longtext,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `form_ros`
--

DROP TABLE IF EXISTS `form_ros`;
CREATE TABLE `form_ros` (
  `id` int(11) NOT NULL auto_increment,
  `pid` bigint(20) NOT NULL,
  `activity` int(11) NOT NULL default '1',
  `date` datetime default NULL,
  `weight_change` varchar(3) default NULL,
  `weakness` varchar(3) default NULL,
  `fatigue` varchar(3) default NULL,
  `anorexia` varchar(3) default NULL,
  `fever` varchar(3) default NULL,
  `chills` varchar(3) default NULL,
  `night_sweats` varchar(3) default NULL,
  `insomnia` varchar(3) default NULL,
  `irritability` varchar(3) default NULL,
  `heat_or_cold` varchar(3) default NULL,
  `intolerance` varchar(3) default NULL,
  `change_in_vision` varchar(3) default NULL,
  `glaucoma_history` varchar(3) default NULL,
  `eye_pain` varchar(3) default NULL,
  `irritation` varchar(3) default NULL,
  `redness` varchar(3) default NULL,
  `excessive_tearing` varchar(3) default NULL,
  `double_vision` varchar(3) default NULL,
  `blind_spots` varchar(3) default NULL,
  `photophobia` varchar(3) default NULL,
  `hearing_loss` varchar(3) default NULL,
  `discharge` varchar(3) default NULL,
  `pain` varchar(3) default NULL,
  `vertigo` varchar(3) default NULL,
  `tinnitus` varchar(3) default NULL,
  `frequent_colds` varchar(3) default NULL,
  `sore_throat` varchar(3) default NULL,
  `sinus_problems` varchar(3) default NULL,
  `post_nasal_drip` varchar(3) default NULL,
  `nosebleed` varchar(3) default NULL,
  `snoring` varchar(3) default NULL,
  `apnea` varchar(3) default NULL,
  `breast_mass` varchar(3) default NULL,
  `breast_discharge` varchar(3) default NULL,
  `biopsy` varchar(3) default NULL,
  `abnormal_mammogram` varchar(3) default NULL,
  `cough` varchar(3) default NULL,
  `sputum` varchar(3) default NULL,
  `shortness_of_breath` varchar(3) default NULL,
  `wheezing` varchar(3) default NULL,
  `hemoptsyis` varchar(3) default NULL,
  `asthma` varchar(3) default NULL,
  `copd` varchar(3) default NULL,
  `chest_pain` varchar(3) default NULL,
  `palpitation` varchar(3) default NULL,
  `syncope` varchar(3) default NULL,
  `pnd` varchar(3) default NULL,
  `doe` varchar(3) default NULL,
  `orthopnea` varchar(3) default NULL,
  `peripheal` varchar(3) default NULL,
  `edema` varchar(3) default NULL,
  `legpain_cramping` varchar(3) default NULL,
  `history_murmur` varchar(3) default NULL,
  `arrythmia` varchar(3) default NULL,
  `heart_problem` varchar(3) default NULL,
  `dysphagia` varchar(3) default NULL,
  `heartburn` varchar(3) default NULL,
  `bloating` varchar(3) default NULL,
  `belching` varchar(3) default NULL,
  `flatulence` varchar(3) default NULL,
  `nausea` varchar(3) default NULL,
  `vomiting` varchar(3) default NULL,
  `hematemesis` varchar(3) default NULL,
  `gastro_pain` varchar(3) default NULL,
  `food_intolerance` varchar(3) default NULL,
  `hepatitis` varchar(3) default NULL,
  `jaundice` varchar(3) default NULL,
  `hematochezia` varchar(3) default NULL,
  `changed_bowel` varchar(3) default NULL,
  `diarrhea` varchar(3) default NULL,
  `constipation` varchar(3) default NULL,
  `polyuria` varchar(3) default NULL,
  `polydypsia` varchar(3) default NULL,
  `dysuria` varchar(3) default NULL,
  `hematuria` varchar(3) default NULL,
  `frequency` varchar(3) default NULL,
  `urgency` varchar(3) default NULL,
  `incontinence` varchar(3) default NULL,
  `renal_stones` varchar(3) default NULL,
  `utis` varchar(3) default NULL,
  `hesitancy` varchar(3) default NULL,
  `dribbling` varchar(3) default NULL,
  `stream` varchar(3) default NULL,
  `nocturia` varchar(3) default NULL,
  `erections` varchar(3) default NULL,
  `ejaculations` varchar(3) default NULL,
  `g` varchar(3) default NULL,
  `p` varchar(3) default NULL,
  `ap` varchar(3) default NULL,
  `lc` varchar(3) default NULL,
  `mearche` varchar(3) default NULL,
  `menopause` varchar(3) default NULL,
  `lmp` varchar(3) default NULL,
  `f_frequency` varchar(3) default NULL,
  `f_flow` varchar(3) default NULL,
  `f_symptoms` varchar(3) default NULL,
  `abnormal_hair_growth` varchar(3) default NULL,
  `f_hirsutism` varchar(3) default NULL,
  `joint_pain` varchar(3) default NULL,
  `swelling` varchar(3) default NULL,
  `m_redness` varchar(3) default NULL,
  `m_warm` varchar(3) default NULL,
  `m_stiffness` varchar(3) default NULL,
  `muscle` varchar(3) default NULL,
  `m_aches` varchar(3) default NULL,
  `fms` varchar(3) default NULL,
  `arthritis` varchar(3) default NULL,
  `loc` varchar(3) default NULL,
  `seizures` varchar(3) default NULL,
  `stroke` varchar(3) default NULL,
  `tia` varchar(3) default NULL,
  `n_numbness` varchar(3) default NULL,
  `n_weakness` varchar(3) default NULL,
  `paralysis` varchar(3) default NULL,
  `intellectual_decline` varchar(3) default NULL,
  `memory_problems` varchar(3) default NULL,
  `dementia` varchar(3) default NULL,
  `n_headache` varchar(3) default NULL,
  `s_cancer` varchar(3) default NULL,
  `psoriasis` varchar(3) default NULL,
  `s_acne` varchar(3) default NULL,
  `s_other` varchar(3) default NULL,
  `s_disease` varchar(3) default NULL,
  `p_diagnosis` varchar(3) default NULL,
  `p_medication` varchar(3) default NULL,
  `depression` varchar(3) default NULL,
  `anxiety` varchar(3) default NULL,
  `social_difficulties` varchar(3) default NULL,
  `thyroid_problems` varchar(3) default NULL,
  `diabetes` varchar(3) default NULL,
  `abnormal_blood` varchar(3) default NULL,
  `anemia` varchar(3) default NULL,
  `fh_blood_problems` varchar(3) default NULL,
  `bleeding_problems` varchar(3) default NULL,
  `allergies` varchar(3) default NULL,
  `frequent_illness` varchar(3) default NULL,
  `hiv` varchar(3) default NULL,
  `hai_status` varchar(3) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `form_soap`
--

DROP TABLE IF EXISTS `form_soap`;
CREATE TABLE `form_soap` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime default NULL,
  `pid` bigint(20) default '0',
  `user` varchar(255) default NULL,
  `groupname` varchar(255) default NULL,
  `authorized` tinyint(4) default '0',
  `activity` tinyint(4) default '0',
  `subjective` text,
  `objective` text,
  `assessment` text,
  `plan` text,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `form_vitals`
--

DROP TABLE IF EXISTS `form_vitals`;
CREATE TABLE `form_vitals` (
  `id` bigint(20) NOT NULL auto_increment,
  `uuid` BINARY(16) DEFAULT NULL,
  `date` datetime default NULL,
  `pid` bigint(20) default '0',
  `user` varchar(255) default NULL,
  `groupname` varchar(255) default NULL,
  `authorized` tinyint(4) default '0',
  `activity` tinyint(4) default '0',
  `bps` varchar(40) default NULL,
  `bpd` varchar(40) default NULL,
  `weight` DECIMAL(12,6) default '0.00',
  `height` DECIMAL(12,6) default '0.00',
  `temperature` DECIMAL(12,6) default '0.00',
  `temp_method` varchar(255) default NULL,
  `pulse` DECIMAL(12,6) default '0.00',
  `respiration` DECIMAL(12,6) default '0.00',
  `note` varchar(255) default NULL,
  `BMI` DECIMAL(12,6) default '0.0',
  `BMI_status` varchar(255) default NULL,
  `waist_circ` DECIMAL(12,6) default '0.00',
  `head_circ` DECIMAL(12,6) default '0.00',
  `oxygen_saturation` DECIMAL(6,2) default '0.00',
  `oxygen_flow_rate` DECIMAL(12,6) default '0.00',
  `external_id` VARCHAR(20) DEFAULT NULL,
  `ped_weight_height` DECIMAL(6,2) default '0.00',
  `ped_bmi` DECIMAL(6,2) default '0.00',
  `ped_head_circ` DECIMAL(6,2) default '0.00',
  `inhaled_oxygen_concentration` DECIMAL(6,2) DEFAULT '0.00',
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `forms`
--

DROP TABLE IF EXISTS `forms`;
CREATE TABLE `forms` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime default NULL,
  `encounter` bigint(20) default NULL,
  `form_name` longtext,
  `form_id` bigint(20) default NULL,
  `pid` bigint(20) default NULL,
  `user` varchar(255) default NULL,
  `groupname` varchar(255) default NULL,
  `authorized` tinyint(4) default NULL,
  `deleted` tinyint(4) DEFAULT '0' NOT NULL COMMENT 'flag indicates form has been deleted',
  `formdir` longtext,
  `therapy_group_id` INT(11) DEFAULT NULL,
  `issue_id` bigint(20) NOT NULL default 0 COMMENT 'references lists.id to identify a case',
  `provider_id` bigint(20) NOT NULL default 0 COMMENT 'references users.id to identify a provider',
  PRIMARY KEY  (`id`),
  KEY `pid_encounter` (`pid`, `encounter`),
  KEY `form_id` (`form_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_acl`
--

DROP TABLE IF EXISTS `gacl_acl`;
CREATE TABLE `gacl_acl` (
  `id` int(11) NOT NULL DEFAULT 0,
  `section_value` varchar(150) NOT NULL DEFAULT 'system',
  `allow` int(11) NOT NULL DEFAULT 0,
  `enabled` int(11) NOT NULL DEFAULT 0,
  `return_value` text,
  `note` text,
  `updated_date` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `gacl_enabled_acl` (`enabled`),
  KEY `gacl_section_value_acl` (`section_value`),
  KEY `gacl_updated_date_acl` (`updated_date`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_acl_sections`
--

DROP TABLE IF EXISTS `gacl_acl_sections`;
CREATE TABLE `gacl_acl_sections` (
  `id` int(11) NOT NULL DEFAULT 0,
  `value` varchar(150) NOT NULL,
  `order_value` int(11) NOT NULL DEFAULT 0,
  `name` varchar(230) NOT NULL,
  `hidden` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gacl_value_acl_sections` (`value`),
  KEY `gacl_hidden_acl_sections` (`hidden`)
) ENGINE=InnoDB;

--
-- Dumping data for table `gacl_acl_sections`
--

-- --------------------------------------------------------

--
-- Table structure for table `gacl_acl_seq`
--

DROP TABLE IF EXISTS `gacl_acl_seq`;
CREATE TABLE `gacl_acl_seq` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB;

--
-- Inserting data for table `gacl_acl_seq`
--

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aco`
--

DROP TABLE IF EXISTS `gacl_aco`;
CREATE TABLE `gacl_aco` (
  `id` int(11) NOT NULL DEFAULT 0,
  `section_value` varchar(150) NOT NULL DEFAULT '0',
  `value` varchar(150) NOT NULL,
  `order_value` int(11) NOT NULL DEFAULT 0,
  `name` varchar(255) NOT NULL,
  `hidden` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gacl_section_value_value_aco` (`section_value`,`value`),
  KEY `gacl_hidden_aco` (`hidden`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aco_map`
--

DROP TABLE IF EXISTS `gacl_aco_map`;
CREATE TABLE `gacl_aco_map` (
  `acl_id` int(11) NOT NULL DEFAULT 0,
  `section_value` varchar(150) NOT NULL DEFAULT '0',
  `value` varchar(150) NOT NULL,
  PRIMARY KEY (`acl_id`,`section_value`,`value`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aco_sections`
--

DROP TABLE IF EXISTS `gacl_aco_sections`;
CREATE TABLE `gacl_aco_sections` (
  `id` int(11) NOT NULL DEFAULT 0,
  `value` varchar(150) NOT NULL,
  `order_value` int(11) NOT NULL DEFAULT 0,
  `name` varchar(230) NOT NULL,
  `hidden` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gacl_value_aco_sections` (`value`),
  KEY `gacl_hidden_aco_sections` (`hidden`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aco_sections_seq`
--

DROP TABLE IF EXISTS `gacl_aco_sections_seq`;
CREATE TABLE `gacl_aco_sections_seq` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB;

--
-- Inserting data for table `gacl_aco_sections_seq`
--

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aco_seq`
--

DROP TABLE IF EXISTS `gacl_aco_seq`;
CREATE TABLE `gacl_aco_seq` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB;


--
-- Inserting data for table `gacl_aco_seq`
--

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aro`
--

DROP TABLE IF EXISTS `gacl_aro`;
CREATE TABLE `gacl_aro` (
  `id` int(11) NOT NULL DEFAULT 0,
  `section_value` varchar(150) NOT NULL DEFAULT '0',
  `value` varchar(150) NOT NULL,
  `order_value` int(11) NOT NULL DEFAULT 0,
  `name` varchar(255) NOT NULL,
  `hidden` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gacl_section_value_value_aro` (`section_value`,`value`),
  KEY `gacl_hidden_aro` (`hidden`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aro_groups`
--

DROP TABLE IF EXISTS `gacl_aro_groups`;
CREATE TABLE `gacl_aro_groups` (
  `id` int(11) NOT NULL DEFAULT 0,
  `parent_id` int(11) NOT NULL DEFAULT 0,
  `lft` int(11) NOT NULL DEFAULT 0,
  `rgt` int(11) NOT NULL DEFAULT 0,
  `name` varchar(255) NOT NULL,
  `value` varchar(150) NOT NULL,
  PRIMARY KEY (`id`,`value`),
  UNIQUE KEY `gacl_value_aro_groups` (`value`),
  KEY `gacl_parent_id_aro_groups` (`parent_id`),
  KEY `gacl_lft_rgt_aro_groups` (`lft`,`rgt`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aro_groups_id_seq`
--

DROP TABLE IF EXISTS `gacl_aro_groups_id_seq`;
CREATE TABLE `gacl_aro_groups_id_seq` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB;

--
-- Inserting data for table `gacl_aro_groups_id_seq`
--

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aro_groups_map`
--

DROP TABLE IF EXISTS `gacl_aro_groups_map`;
CREATE TABLE `gacl_aro_groups_map` (
  `acl_id` int(11) NOT NULL DEFAULT 0,
  `group_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`acl_id`,`group_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aro_map`
--

DROP TABLE IF EXISTS `gacl_aro_map`;
CREATE TABLE `gacl_aro_map` (
  `acl_id` int(11) NOT NULL DEFAULT 0,
  `section_value` varchar(150) NOT NULL DEFAULT '0',
  `value` varchar(150) NOT NULL,
  PRIMARY KEY (`acl_id`,`section_value`,`value`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aro_sections`
--

DROP TABLE IF EXISTS `gacl_aro_sections`;
CREATE TABLE `gacl_aro_sections` (
  `id` int(11) NOT NULL DEFAULT 0,
  `value` varchar(150) NOT NULL,
  `order_value` int(11) NOT NULL DEFAULT 0,
  `name` varchar(230) NOT NULL,
  `hidden` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gacl_value_aro_sections` (`value`),
  KEY `gacl_hidden_aro_sections` (`hidden`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aro_sections_seq`
--

DROP TABLE IF EXISTS `gacl_aro_sections_seq`;
CREATE TABLE `gacl_aro_sections_seq` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB;

--
-- Inserting data for table `gacl_aro_sections_seq`
--

-- --------------------------------------------------------

--
-- Table structure for table `gacl_aro_seq`
--

DROP TABLE IF EXISTS `gacl_aro_seq`;
CREATE TABLE `gacl_aro_seq` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB;

--
-- Inserting data for table `gacl_aro_seq`
--

-- --------------------------------------------------------

--
-- Table structure for table `gacl_axo`
--

DROP TABLE IF EXISTS `gacl_axo`;
CREATE TABLE `gacl_axo` (
  `id` int(11) NOT NULL DEFAULT 0,
  `section_value` varchar(150) NOT NULL DEFAULT '0',
  `value` varchar(150) NOT NULL,
  `order_value` int(11) NOT NULL DEFAULT 0,
  `name` varchar(255) NOT NULL,
  `hidden` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gacl_section_value_value_axo` (`section_value`,`value`),
  KEY `gacl_hidden_axo` (`hidden`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_axo_groups`
--

DROP TABLE IF EXISTS `gacl_axo_groups`;
CREATE TABLE `gacl_axo_groups` (
  `id` int(11) NOT NULL DEFAULT 0,
  `parent_id` int(11) NOT NULL DEFAULT 0,
  `lft` int(11) NOT NULL DEFAULT 0,
  `rgt` int(11) NOT NULL DEFAULT 0,
  `name` varchar(255) NOT NULL,
  `value` varchar(150) NOT NULL,
  PRIMARY KEY (`id`,`value`),
  UNIQUE KEY `gacl_value_axo_groups` (`value`),
  KEY `gacl_parent_id_axo_groups` (`parent_id`),
  KEY `gacl_lft_rgt_axo_groups` (`lft`,`rgt`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_axo_groups_map`
--

DROP TABLE IF EXISTS `gacl_axo_groups_map`;
CREATE TABLE `gacl_axo_groups_map` (
  `acl_id` int(11) NOT NULL DEFAULT 0,
  `group_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`acl_id`,`group_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_axo_map`
--

DROP TABLE IF EXISTS `gacl_axo_map`;
CREATE TABLE `gacl_axo_map` (
  `acl_id` int(11) NOT NULL DEFAULT 0,
  `section_value` varchar(150) NOT NULL DEFAULT '0',
  `value` varchar(150) NOT NULL,
  PRIMARY KEY (`acl_id`,`section_value`,`value`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_axo_sections`
--

DROP TABLE IF EXISTS `gacl_axo_sections`;
CREATE TABLE `gacl_axo_sections` (
  `id` int(11) NOT NULL DEFAULT 0,
  `value` varchar(150) NOT NULL,
  `order_value` int(11) NOT NULL DEFAULT 0,
  `name` varchar(230) NOT NULL,
  `hidden` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gacl_value_axo_sections` (`value`),
  KEY `gacl_hidden_axo_sections` (`hidden`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_groups_aro_map`
--

DROP TABLE IF EXISTS `gacl_groups_aro_map`;
CREATE TABLE `gacl_groups_aro_map` (
  `group_id` int(11) NOT NULL DEFAULT 0,
  `aro_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`group_id`,`aro_id`),
  KEY `gacl_aro_id` (`aro_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_groups_axo_map`
--

DROP TABLE IF EXISTS `gacl_groups_axo_map`;
CREATE TABLE `gacl_groups_axo_map` (
  `group_id` int(11) NOT NULL DEFAULT 0,
  `axo_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`group_id`,`axo_id`),
  KEY `gacl_axo_id` (`axo_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `gacl_phpgacl`
--

DROP TABLE IF EXISTS `gacl_phpgacl`;
CREATE TABLE `gacl_phpgacl` (
  `name` varchar(230) NOT NULL,
  `value` varchar(150) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB;


--
-- Dumping data for table `gacl_phpgacl`
--

-- --------------------------------------------------------

--
-- Table structure for table `groups`
--

DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` longtext,
  `user` longtext,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `history_data`
--

DROP TABLE IF EXISTS `history_data`;
CREATE TABLE `history_data` (
  `id` bigint(20) NOT NULL auto_increment,
  `uuid` binary(16) DEFAULT NULL,
  `coffee` longtext,
  `tobacco` longtext,
  `alcohol` longtext,
  `sleep_patterns` longtext,
  `exercise_patterns` longtext,
  `seatbelt_use` longtext,
  `counseling` longtext,
  `hazardous_activities` longtext,
  `recreational_drugs` longtext,
  `last_breast_exam` varchar(255) default NULL,
  `last_mammogram` varchar(255) default NULL,
  `last_gynocological_exam` varchar(255) default NULL,
  `last_rectal_exam` varchar(255) default NULL,
  `last_prostate_exam` varchar(255) default NULL,
  `last_physical_exam` varchar(255) default NULL,
  `last_sigmoidoscopy_colonoscopy` varchar(255) default NULL,
  `last_ecg` varchar(255) default NULL,
  `last_cardiac_echo` varchar(255) default NULL,
  `last_retinal` varchar(255) default NULL,
  `last_fluvax` varchar(255) default NULL,
  `last_pneuvax` varchar(255) default NULL,
  `last_ldl` varchar(255) default NULL,
  `last_hemoglobin` varchar(255) default NULL,
  `last_psa` varchar(255) default NULL,
  `last_exam_results` varchar(255) default NULL,
  `history_mother` longtext,
  `dc_mother` text,
  `history_father` longtext,
  `dc_father`  text,
  `history_siblings` longtext,
  `dc_siblings` text,
  `history_offspring` longtext,
  `dc_offspring` text,
  `history_spouse` longtext,
  `dc_spouse` text,
  `relatives_cancer` longtext,
  `relatives_tuberculosis` longtext,
  `relatives_diabetes` longtext,
  `relatives_high_blood_pressure` longtext,
  `relatives_heart_problems` longtext,
  `relatives_stroke` longtext,
  `relatives_epilepsy` longtext,
  `relatives_mental_illness` longtext,
  `relatives_suicide` longtext,
  `cataract_surgery` datetime default NULL,
  `tonsillectomy` datetime default NULL,
  `cholecystestomy` datetime default NULL,
  `heart_surgery` datetime default NULL,
  `hysterectomy` datetime default NULL,
  `hernia_repair` datetime default NULL,
  `hip_replacement` datetime default NULL,
  `knee_replacement` datetime default NULL,
  `appendectomy` datetime default NULL,
  `date` datetime default NULL,
  `pid` bigint(20) NOT NULL default '0',
  `name_1` varchar(255) default NULL,
  `value_1` varchar(255) default NULL,
  `name_2` varchar(255) default NULL,
  `value_2` varchar(255) default NULL,
  `additional_history` text,
  `exams` text,
  `usertext11` TEXT,
  `usertext12` varchar(255) NOT NULL DEFAULT '',
  `usertext13` varchar(255) NOT NULL DEFAULT '',
  `usertext14` varchar(255) NOT NULL DEFAULT '',
  `usertext15` varchar(255) NOT NULL DEFAULT '',
  `usertext16` varchar(255) NOT NULL DEFAULT '',
  `usertext17` varchar(255) NOT NULL DEFAULT '',
  `usertext18` varchar(255) NOT NULL DEFAULT '',
  `usertext19` varchar(255) NOT NULL DEFAULT '',
  `usertext20` varchar(255) NOT NULL DEFAULT '',
  `usertext21` varchar(255) NOT NULL DEFAULT '',
  `usertext22` varchar(255) NOT NULL DEFAULT '',
  `usertext23` varchar(255) NOT NULL DEFAULT '',
  `usertext24` varchar(255) NOT NULL DEFAULT '',
  `usertext25` varchar(255) NOT NULL DEFAULT '',
  `usertext26` varchar(255) NOT NULL DEFAULT '',
  `usertext27` varchar(255) NOT NULL DEFAULT '',
  `usertext28` varchar(255) NOT NULL DEFAULT '',
  `usertext29` varchar(255) NOT NULL DEFAULT '',
  `usertext30` varchar(255) NOT NULL DEFAULT '',
  `userdate11` date DEFAULT NULL,
  `userdate12` date DEFAULT NULL,
  `userdate13` date DEFAULT NULL,
  `userdate14` date DEFAULT NULL,
  `userdate15` date DEFAULT NULL,
  `userarea11` text,
  `userarea12` text,
  `created_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id the user that first created this record',
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `icd9_dx_code`
--

DROP TABLE IF EXISTS `icd9_dx_code`;
CREATE TABLE `icd9_dx_code` (
  `dx_id` SERIAL,
  `dx_code`             varchar(5),
  `formatted_dx_code`   varchar(6),
  `short_desc`          varchar(60),
  `long_desc`           varchar(300),
  `active` tinyint default 0,
  `revision` int default 0,
  KEY `dx_code` (`dx_code`),
  KEY `formatted_dx_code` (`formatted_dx_code`),
  KEY `active` (`active`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd9_sg_code`
--

DROP TABLE IF EXISTS `icd9_sg_code`;
CREATE TABLE `icd9_sg_code` (
  `sg_id` SERIAL,
  `sg_code`             varchar(5),
  `formatted_sg_code`   varchar(6),
  `short_desc`          varchar(60),
  `long_desc`           varchar(300),
  `active` tinyint default 0,
  `revision` int default 0,
  KEY `sg_code` (`sg_code`),
  KEY `formatted_sg_code` (`formatted_sg_code`),
  KEY `active` (`active`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd9_dx_long_code`
--

DROP TABLE IF EXISTS `icd9_dx_long_code`;
CREATE TABLE `icd9_dx_long_code` (
  `dx_id` SERIAL,
  `dx_code`             varchar(5),
  `long_desc`           varchar(300),
  `active` tinyint default 0,
  `revision` int default 0
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd9_sg_long_code`
--

DROP TABLE IF EXISTS `icd9_sg_long_code`;
CREATE TABLE `icd9_sg_long_code` (
  `sq_id` SERIAL,
  `sg_code`             varchar(5),
  `long_desc`           varchar(300),
  `active` tinyint default 0,
  `revision` int default 0
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd10_dx_order_code`
--

DROP TABLE IF EXISTS `icd10_dx_order_code`;
CREATE TABLE `icd10_dx_order_code` (
  `dx_id`               SERIAL,
  `dx_code`             varchar(7),
  `formatted_dx_code`   varchar(10),
  `valid_for_coding`    char,
  `short_desc`          varchar(60),
  `long_desc`           text,
  `active` tinyint default 0,
  `revision` int default 0,
  KEY `formatted_dx_code` (`formatted_dx_code`),
  KEY `active` (`active`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd10_pcs_order_code`
--

DROP TABLE IF EXISTS `icd10_pcs_order_code`;
CREATE TABLE `icd10_pcs_order_code` (
  `pcs_id`              SERIAL,
  `pcs_code`            varchar(7),
  `valid_for_coding`    char,
  `short_desc`          varchar(60),
  `long_desc`           text,
  `active` tinyint default 0,
  `revision` int default 0,
  KEY `pcs_code` (`pcs_code`),
  KEY `active` (`active`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd10_gem_pcs_9_10`
--

DROP TABLE IF EXISTS `icd10_gem_pcs_9_10`;
CREATE TABLE `icd10_gem_pcs_9_10` (
  `map_id` SERIAL,
  `pcs_icd9_source` varchar(5) default NULL,
  `pcs_icd10_target` varchar(7) default NULL,
  `flags` varchar(5) default NULL,
  `active` tinyint default 0,
  `revision` int default 0
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd10_gem_pcs_10_9`
--

DROP TABLE IF EXISTS `icd10_gem_pcs_10_9`;
CREATE TABLE `icd10_gem_pcs_10_9` (
  `map_id` SERIAL,
  `pcs_icd10_source` varchar(7) default NULL,
  `pcs_icd9_target` varchar(5) default NULL,
  `flags` varchar(5) default NULL,
  `active` tinyint default 0,
  `revision` int default 0
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd10_gem_dx_9_10`
--

DROP TABLE IF EXISTS `icd10_gem_dx_9_10`;
CREATE TABLE `icd10_gem_dx_9_10` (
  `map_id` SERIAL,
  `dx_icd9_source` varchar(5) default NULL,
  `dx_icd10_target` varchar(7) default NULL,
  `flags` varchar(5) default NULL,
  `active` tinyint default 0,
  `revision` int default 0
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd10_gem_dx_10_9`
--

DROP TABLE IF EXISTS `icd10_gem_dx_10_9`;
CREATE TABLE `icd10_gem_dx_10_9` (
  `map_id` SERIAL,
  `dx_icd10_source` varchar(7) default NULL,
  `dx_icd9_target` varchar(5) default NULL,
  `flags` varchar(5) default NULL,
  `active` tinyint default 0,
  `revision` int default 0
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd10_reimbr_dx_9_10`
--

DROP TABLE IF EXISTS `icd10_reimbr_dx_9_10`;
CREATE TABLE `icd10_reimbr_dx_9_10` (
  `map_id` SERIAL,
  `code`        varchar(8),
  `code_cnt`    tinyint,
  `ICD9_01`     varchar(5),
  `ICD9_02`     varchar(5),
  `ICD9_03`     varchar(5),
  `ICD9_04`     varchar(5),
  `ICD9_05`     varchar(5),
  `ICD9_06`     varchar(5),
  `active` tinyint default 0,
  `revision` int default 0
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `icd10_reimbr_pcs_9_10`
--

DROP TABLE IF EXISTS `icd10_reimbr_pcs_9_10`;
CREATE TABLE `icd10_reimbr_pcs_9_10` (
  `map_id`      SERIAL,
  `code`        varchar(8),
  `code_cnt`    tinyint,
  `ICD9_01`     varchar(5),
  `ICD9_02`     varchar(5),
  `ICD9_03`     varchar(5),
  `ICD9_04`     varchar(5),
  `ICD9_05`     varchar(5),
  `ICD9_06`     varchar(5),
  `active` tinyint default 0,
  `revision` int default 0
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `immunizations`
--

DROP TABLE IF EXISTS `immunizations`;
CREATE TABLE `immunizations` (
  `id` bigint(20) NOT NULL auto_increment,
  `uuid` binary(16) DEFAULT NULL,
  `patient_id` bigint(20) default NULL,
  `administered_date` datetime default NULL,
  `immunization_id` int(11) default NULL,
  `cvx_code` varchar(64) default NULL,
  `manufacturer` varchar(100) default NULL,
  `lot_number` varchar(50) default NULL,
  `administered_by_id` bigint(20) default NULL,
  `administered_by` VARCHAR( 255 ) default NULL COMMENT 'Alternative to administered_by_id',
  `education_date` date default NULL,
  `vis_date` date default NULL COMMENT 'Date of VIS Statement',
  `note` text,
  `create_date` datetime default NULL,
  `update_date` timestamp NOT NULL,
  `created_by` bigint(20) default NULL,
  `updated_by` bigint(20) default NULL,
  `amount_administered` float DEFAULT NULL,
  `amount_administered_unit` varchar(50) DEFAULT NULL,
  `expiration_date` date DEFAULT NULL,
  `route` varchar(100) DEFAULT NULL,
  `administration_site` varchar(100) DEFAULT NULL,
  `added_erroneously` tinyint(1) NOT NULL DEFAULT '0',
  `external_id` VARCHAR(20) DEFAULT NULL,
  `completion_status` VARCHAR(50) DEFAULT NULL,
  `information_source` VARCHAR(31) DEFAULT NULL,
  `refusal_reason` VARCHAR(31) DEFAULT NULL,
  `ordering_provider` INT(11) DEFAULT NULL,
  `reason_code` varchar(31) DEFAULT NULL COMMENT 'Medical code explaining reason of the vital observation value in form codesystem:codetype;...;',
  `reason_description` text COMMENT 'Human readable text description of the reason_code column',
  `encounter_id` BIGINT(20) DEFAULT NULL COMMENT 'fk to form_encounter.encounter to link immunization to encounter record',
  PRIMARY KEY  (`id`),
  KEY `patient_id` (`patient_id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `insurance_companies`
--

DROP TABLE IF EXISTS `insurance_companies`;
CREATE TABLE `insurance_companies` (
  `id` int(11) NOT NULL default '0',
  `uuid` binary(16)   DEFAULT NULL,
  `name` varchar(255) default NULL,
  `attn` varchar(255) default NULL,
  `cms_id` varchar(15) default NULL,
  `ins_type_code` int(11) default NULL,
  `x12_receiver_id` varchar(25) default NULL,
  `x12_default_partner_id` int(11) default NULL,
  `alt_cms_id` varchar(15) default NULL,
  `inactive` tinyint(1) NOT NULL DEFAULT '0',
  `eligibility_id` VARCHAR(32) default NULL,
  `x12_default_eligibility_id` INT(11) default NULL,
  `cqm_sop` int DEFAULT NULL COMMENT 'HL7 Source of Payment for eCQMs',
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `insurance_data`
--

DROP TABLE IF EXISTS `insurance_data`;
CREATE TABLE `insurance_data` (
  `id` bigint(20) NOT NULL auto_increment,
  `uuid` binary(16)   DEFAULT NULL,
  `type` enum('primary','secondary','tertiary') default NULL,
  `provider` varchar(255) default NULL,
  `plan_name` varchar(255) default NULL,
  `policy_number` varchar(255) default NULL,
  `group_number` varchar(255) default NULL,
  `subscriber_lname` varchar(255) default NULL,
  `subscriber_mname` varchar(255) default NULL,
  `subscriber_fname` varchar(255) default NULL,
  `subscriber_relationship` varchar(255) default NULL,
  `subscriber_ss` varchar(255) default NULL,
  `subscriber_DOB` date default NULL,
  `subscriber_street` varchar(255) default NULL,
  `subscriber_postal_code` varchar(255) default NULL,
  `subscriber_city` varchar(255) default NULL,
  `subscriber_state` varchar(255) default NULL,
  `subscriber_country` varchar(255) default NULL,
  `subscriber_phone` varchar(255) default NULL,
  `subscriber_employer` varchar(255) default NULL,
  `subscriber_employer_street` varchar(255) default NULL,
  `subscriber_employer_postal_code` varchar(255) default NULL,
  `subscriber_employer_state` varchar(255) default NULL,
  `subscriber_employer_country` varchar(255) default NULL,
  `subscriber_employer_city` varchar(255) default NULL,
  `copay` varchar(255) default NULL,
  `date` date NULL,
  `pid` bigint(20) NOT NULL default '0',
  `subscriber_sex` varchar(25) default NULL,
  `accept_assignment` varchar(5) NOT NULL DEFAULT 'TRUE',
  `policy_type` varchar(25) NOT NULL default '',
  `subscriber_street_line_2` TINYTEXT,
  `subscriber_employer_street_line_2` TINYTEXT,
  `date_end` date NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `pid_type_date` (`pid`,`type`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `insurance_numbers`
--

DROP TABLE IF EXISTS `insurance_numbers`;
CREATE TABLE `insurance_numbers` (
  `id` int(11) NOT NULL default '0',
  `provider_id` int(11) NOT NULL default '0',
  `insurance_company_id` int(11) default NULL,
  `provider_number` varchar(20) default NULL,
  `rendering_provider_number` varchar(20) default NULL,
  `group_number` varchar(20) default NULL,
  `provider_number_type` varchar(4) default NULL,
  `rendering_provider_number_type` varchar(4) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `insurance_type_codes`
--

DROP TABLE IF EXISTS `insurance_type_codes`;
CREATE TABLE `insurance_type_codes` (
  `id` int(2) NOT NULL,
  `type` varchar(60) NOT NULL,
  `claim_type` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

--
-- Inserting data for table `insurance_type_codes`
--

-- --------------------------------------------------------

--
-- Table structure for table `ip_tracking`
--
DROP TABLE IF EXISTS `ip_tracking`;
CREATE TABLE `ip_tracking` (
    `id` bigint NOT NULL auto_increment,
    `ip_string` varchar(255) DEFAULT '',
    `total_ip_login_fail_counter` bigint DEFAULT 0,
    `ip_login_fail_counter` bigint DEFAULT 0,
    `ip_last_login_fail` datetime DEFAULT NULL,
    `ip_auto_block_emailed` tinyint DEFAULT 0,
    `ip_force_block` tinyint DEFAULT 0,
    `ip_no_prevent_timing_attack` tinyint DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ip_string` (`ip_string`)
) ENGINE=InnoDb AUTO_INCREMENT=1;


-- --------------------------------------------------------

--
-- Table structure for table `issue_encounter`
--

DROP TABLE IF EXISTS `issue_encounter`;
CREATE TABLE `issue_encounter` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uuid` binary(16) DEFAULT NULL COMMENT 'UUID for this issue encounter record, for data exchange purposes',
  `pid` bigint(20) NOT NULL,
  `list_id` int(11) NOT NULL,
  `encounter` int(11) NOT NULL,
  `resolved` tinyint(1) NOT NULL,
  `created_by` bigint(20) DEFAULT NULL COMMENT 'fk to users.id for the user that entered in the issue encounter data',
  `updated_by` bigint(20) DEFAULT NULL COMMENT 'fk to users.id for the user that last updated the issue encounter data',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp when this issue encounter record was created',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'timestamp when this issue encounter record was last updated',
  UNIQUE KEY `uniq_issue_key`(`pid`,`list_id`,`encounter`),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid_unique` (`uuid`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `issue_types`
--

DROP TABLE IF EXISTS `issue_types`;
CREATE TABLE `issue_types` (
    `active` tinyint(1) NOT NULL DEFAULT '1',
    `category` varchar(75) NOT NULL DEFAULT '',
    `type` varchar(75) NOT NULL DEFAULT '',
    `plural` varchar(75) NOT NULL DEFAULT '',
    `singular` varchar(75) NOT NULL DEFAULT '',
    `abbreviation` varchar(75) NOT NULL DEFAULT '',
    `style` smallint(6) NOT NULL DEFAULT '0',
    `force_show` smallint(6) NOT NULL DEFAULT '0',
    `ordering` int(11) NOT NULL DEFAULT '0',
    `aco_spec` varchar(63) NOT NULL default 'patients|med',
    PRIMARY KEY (`category`,`type`)
) ENGINE=InnoDB;

--
-- Inserting data for table `issue_types`
--

-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `form_history_sdoh_health_concerns` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `sdoh_history_id` bigint(20) UNSIGNED NOT NULL COMMENT 'FK to form_history_sdoh.id',
    `health_concern_id` bigint(20) NOT NULL COMMENT 'FK to lists.id where type=health_concern or medical_problem',
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` bigint(20) DEFAULT NULL COMMENT 'FK to users.id',
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_sdoh_concern` (`sdoh_history_id`, `health_concern_id`),
    KEY `idx_sdoh_history` (`sdoh_history_id`),
    KEY `idx_health_concern` (`health_concern_id`)
) ENGINE=InnoDB COMMENT='Links SDOH assessments to health concern conditions';

--
-- Table structure for table `keys`
--

DROP TABLE IF EXISTS `keys`;
CREATE TABLE `keys` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL DEFAULT '',
  `value` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`name`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `lang_constants`
--

DROP TABLE IF EXISTS `lang_constants`;
CREATE TABLE `lang_constants` (
  `cons_id` int(11) NOT NULL auto_increment,
  `constant_name` mediumtext BINARY,
  UNIQUE KEY `cons_id` (`cons_id`),
  KEY `constant_name` (`constant_name`(100))
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `lang_definitions`
--

DROP TABLE IF EXISTS `lang_definitions`;
CREATE TABLE `lang_definitions` (
  `def_id` int(11) NOT NULL auto_increment,
  `cons_id` int(11) NOT NULL default '0',
  `lang_id` int(11) NOT NULL default '0',
  `definition` mediumtext,
  UNIQUE KEY `def_id` (`def_id`),
  KEY `cons_id` (`cons_id`),
  KEY `lang_cons` (`lang_id`, `cons_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `lang_languages`
--

DROP TABLE IF EXISTS `lang_languages`;
CREATE TABLE `lang_languages` (
  `lang_id` int(11) NOT NULL auto_increment,
  `lang_code` char(2) NOT NULL default '',
  `lang_description` varchar(100) default NULL,
  `lang_is_rtl` TINYINT DEFAULT 0 COMMENT 'Set this to 1 for RTL languages Arabic, Farsi, Hebrew, Urdu etc.',
  UNIQUE KEY `lang_id` (`lang_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2;

--
-- Inserting data for table `lang_languages`
--

-- --------------------------------------------------------

--
-- Table structure for table `lang_custom`
--

DROP TABLE IF EXISTS `lang_custom`;
CREATE TABLE `lang_custom` (
  `lang_description` varchar(100) NOT NULL default '',
  `lang_code` char(2) NOT NULL default '',
  `constant_name` mediumtext,
  `definition` mediumtext
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `layout_group_properties`
--

DROP TABLE IF EXISTS `layout_group_properties`;
CREATE TABLE `layout_group_properties` (
  grp_form_id     varchar(31)    not null,
  grp_group_id    varchar(31)    not null default '' comment 'empty when representing the whole form',
  grp_title       varchar(63)    not null default '' comment 'descriptive name of the form or group',
  grp_subtitle    varchar(63)    not null default '' comment 'for display under the title',
  grp_mapping     varchar(31)    not null default '' comment 'the form category',
  grp_seq         int(11)        not null default 0  comment 'optional order within mapping',
  grp_activity    tinyint(1)     not null default 1,
  grp_repeats     int(11)        not null default 0,
  grp_columns     int(11)        not null default 0,
  grp_size        int(11)        not null default 0,
  grp_issue_type  varchar(75)    not null default '',
  grp_aco_spec    varchar(63)    not null default '',
  grp_save_close  tinyint(1)     not null default 0,
  grp_init_open   tinyint(1)     not null default 0,
  grp_referrals   tinyint(1)     not null default 0,
  grp_unchecked   tinyint(1)     not null default 0,
  grp_services    varchar(4095)  not null default '',
  grp_products    varchar(4095)  not null default '',
  grp_diags       varchar(4095)  not null default '',
  grp_last_update timestamp      NULL,
  PRIMARY KEY (grp_form_id, grp_group_id)
) ENGINE=InnoDB;

--
-- Inserting data for table `layout_group_properties`
--

-- --------------------------------------------------------

--
-- Table structure for table `layout_options`
--

DROP TABLE IF EXISTS `layout_options`;
CREATE TABLE `layout_options` (
  `form_id` varchar(31) NOT NULL default '',
  `field_id` varchar(31) NOT NULL default '',
  `group_id` varchar(31) NOT NULL default '',
  `title` text,
  `seq` int(11) NOT NULL default '0',
  `data_type` tinyint(3) NOT NULL default '0',
  `uor` tinyint(1) NOT NULL default '1',
  `fld_length` int(11) NOT NULL default '15',
  `max_length` int(11) NOT NULL default '0',
  `list_id` varchar(100) NOT NULL default '',
  `titlecols` tinyint(3) NOT NULL default '1',
  `datacols` tinyint(3) NOT NULL default '1',
  `default_value` varchar(255) NOT NULL default '',
  `edit_options` varchar(36) NOT NULL default '',
  `description` text,
  `fld_rows` int(11) NOT NULL default '0',
  `list_backup_id` varchar(100) NOT NULL default '',
  `source` char(1) NOT NULL default 'F' COMMENT 'F=Form, D=Demographics, H=History, E=Encounter',
  `conditions` text COMMENT 'serialized array of skip conditions',
  `validation` varchar(100) default NULL,
  `codes` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (`form_id`,`field_id`,`seq`)
) ENGINE=InnoDB;

--
-- Inserting data for table `layout_options`
--

--
-- choices
--
-- Stats
--
-- ------------------------------------
-- --------------------------------------------------------

--
-- Table structure for table `list_options`
--

DROP TABLE IF EXISTS `list_options`;
CREATE TABLE `list_options` (
  `list_id` varchar(100) NOT NULL default '',
  `option_id` varchar(100) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `seq` int(11) NOT NULL default '0',
  `is_default` tinyint(1) NOT NULL default '0',
  `option_value` float NOT NULL default '0',
  `mapping` varchar(31) NOT NULL DEFAULT '',
  `notes` TEXT,
  `codes` varchar(255) NOT NULL DEFAULT '',
  `toggle_setting_1` tinyint(1) NOT NULL default '0',
  `toggle_setting_2` tinyint(1) NOT NULL default '0',
  `activity` TINYINT DEFAULT 1 NOT NULL,
  `subtype` varchar(31) NOT NULL DEFAULT '',
  `edit_options` tinyint(1) NOT NULL DEFAULT '1',
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`list_id`,`option_id`)
) ENGINE=InnoDB;

--
-- Inserting data for table `list_options`
--

-- there isn't really an NCI code for affected area as it depends on what area it is, so this code is unmapeable
--
--  Clinical Plan Titles
--

--
-- Clinical Rule Titles
--

--
-- order types
--

--
-- Clinical Rule Target Methods
--

-- Clinical Rule Target Intervals

-- Clinical Rule Comparisons
-- Clinical Rule Filter Methods
-- Clinical Rule Age Intervals
-- Encounter Types (needed for mapping encounters for CQM rules)
-- Clinical Rule Action Categories
-- Clinical Rule Action Items
-- Clinical Rule Reminder Intervals
-- Clinical Rule Reminder Methods
-- Clinical Rule Reminder Due Options
-- Clinical Rule Reminder Inactivate Options
-- eRx User Roles
-- MSP remit codes
-- Medical Problem Issue List
-- Ophthalmology: Medical Problem Issue List
-- Medication Issue List
-- Allergy Issue List
-- Surgery Issue List
-- Dental Issue List
-- General Issue List
-- Issue Types List
-- Issue Subtypes List
-- Insurance Types List
-- Amendment Statuses
-- Amendment request from
-- Patient Flow Board Rooms
-- Religious Affiliation
-- Relationship
-- Severity
-- Physician Type

-- Industry

-- Occupation

-- Industry ODH

-- Occupation ODH

-- Reaction

-- County

-- Immunization Manufacturers

-- Immunization Completion Status

-- Immunization Registry Status

-- Publicity Code

-- Immunization Refusal Reason

-- Immunization Information Source

-- Next of kin Relationship
-- Immunization Administered Site
-- Immunization Observation Criteria
-- Immunization Vaccine Eligibility Results
--  LBF Validation

--  Form Keys

-- provider_qualifier_code

-- Files type white list

-- Sample Apps (Disabled)

-- Sort Directions

-- ActEncounterCode [FHIR Encounter.class]
-- us-core-provider-role [FHIR PractitionerRole.role]
-- us-core-provider-specialty [FHIR PractitionerRole.specialty]
-- AllergyIntolerance Verification Status Codes [FHIR AllergyIntolerance.verification]
-- Condition Verification Status Codes [FHIR Condition.verification]
-- Vitals Interpretation Values
-- Discharge Disposition (for encounters and eventually appointments)
-- External Patient Education
-- Observation Types
-- --------------------------------------------------------

-- Observation Statii
-- Add list options of observation-status codes
-- --------------------------------------------------------

-- CCDA Sections for sort orders
-- Address Use Types
-- Address Types
-- -------------------------------------------------------------------------------------------------------------------------------------------------------
-- Social History SDOHValuesets

-- -----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Vital Signs Answers (single set; no duplicate parent)
-- Tribal Affiliations
-- Disability Status parent exists above; now the answers
-- SDOH Problems (single set)
-- SDOH Interventions (fixed duplicate option_id for 467681000124101)
-- --------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Intentional missing create list. Appends
-- Yes/No/Unknown List
-- Administrative Sex list used for patient_data.sex_identified field.
-- This list seems to constantly update with USCDI versions and new administrations so expect the values here to change frequently
-- note USCDI V3 has a ton more options here, but USCDI V4 reverts to M/F/nonbinary/asked-decline with expansion allowed so adding in unknown to map values from patient_data.sex column
-- Insert pronoun list
-- Add v3-ActPharmacySupplyType for tracking the supply type of drug dispensing
-- this is an example value set which means the value set can be nearly anything we want here so we can expand in the future if needed
-- Related Person relationships
-- Spouse/Partner
-- Parents
-- Children
-- Siblings
-- Grandparents
-- Great Grandparents
-- Grandchildren
-- Extended Family
 -- In-Laws
-- Other Relationships
-- Self
-- Related Person Roles
-- Telecom System types
-- Telecome Uses
-- Person Patient Link Method
--
-- Table structure for table `lists`
--

DROP TABLE IF EXISTS `lists`;
CREATE TABLE `lists` (
  `id` bigint(20) NOT NULL auto_increment,
  `uuid` binary(16) DEFAULT NULL,
  `date` datetime default NULL,
  `type` varchar(255) default NULL,
  `subtype` varchar(31) NOT NULL DEFAULT '',
  `title` varchar(255) default NULL,
  `udi` varchar(255) default NULL,
  `udi_data` text,
  `begdate` datetime default NULL,
  `enddate` datetime default NULL,
  `returndate` date default NULL,
  `occurrence` int(11) default '0' COMMENT "Reference to list_options option_id='occurrence'",
  `classification` int(11) default '0',
  `referredby` varchar(255) default NULL,
  `extrainfo` varchar(255) default NULL,
  `diagnosis` varchar(255) default NULL,
  `activity` tinyint(4) default NULL,
  `comments` longtext,
  `pid` bigint(20) default NULL,
  `user` varchar(255) default NULL,
  `groupname` varchar(255) default NULL,
  `outcome` int(11) NOT NULL default '0',
  `destination` varchar(255) default NULL,
  `reinjury_id` bigint(20)  NOT NULL DEFAULT 0,
  `injury_part` varchar(31) NOT NULL DEFAULT '',
  `injury_type` varchar(31) NOT NULL DEFAULT '',
  `injury_grade` varchar(31) NOT NULL DEFAULT '',
  `reaction` varchar(255) NOT NULL DEFAULT '',
  `verification` VARCHAR(36) NOT NULL DEFAULT '' COMMENT 'Reference to list_options option_id = allergyintolerance-verification',
  `external_allergyid` INT(11) DEFAULT NULL,
  `erx_source` ENUM('0','1') DEFAULT '0' NOT NULL  COMMENT '0-OpenEMR 1-External',
  `erx_uploaded` ENUM('0','1') DEFAULT '0' NOT NULL  COMMENT '0-Pending NewCrop upload 1-Uploaded TO NewCrop',
  `modifydate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `severity_al` VARCHAR( 50 ) DEFAULT NULL,
  `external_id` VARCHAR(20) DEFAULT NULL,
  `list_option_id` VARCHAR(100) DEFAULT NULL COMMENT 'Reference to list_options table',
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`),
  KEY `type` (`type`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;


-- --------------------------------------------------------
DROP TABLE IF EXISTS `lists_medication`;
CREATE TABLE `lists_medication` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT
    , `list_id` BIGINT(20) NULL COMMENT 'FK Reference to lists.id'
    , `drug_dosage_instructions` LONGTEXT COMMENT 'Free text dosage instructions for taking the drug'
    , `usage_category` VARCHAR(100) NULL COMMENT 'option_id in list_options.list_id=medication-usage-category'
    , `usage_category_title` VARCHAR(255) NOT NULL COMMENT 'title in list_options.list_id=medication-usage-category'
    , `request_intent` VARCHAR(100) NULL COMMENT 'option_id in list_options.list_id=medication-request-intent'
    , `request_intent_title` VARCHAR(255) NOT NULL COMMENT 'title in list_options.list_id=medication-request-intent'
    , `medication_adherence_information_source` VARCHAR(50) DEFAULT NULL COMMENT 'fk to list_options.option_id where list_id=medication_adherence_information_source to indicate who provided the medication adherence information'
    , `medication_adherence` VARCHAR(50) DEFAULT NULL COMMENT 'fk to list_options.option_id where list_id=medication_adherence to indicate if patient is complying with medication regimen'
    , `medication_adherence_date_asserted` DATETIME DEFAULT NULL COMMENT 'Date when the medication adherence information was asserted'
    , `prescription_id` BIGINT(20) DEFAULT NULL COMMENT 'fk to prescriptions.prescription_id to link medication to prescription record'
    , `is_primary_record` TINYINT(1) DEFAULT '1' COMMENT 'Indicates if this medication is a primary record(1) or a reported record(0)'
    , `reporting_source_record_id` BIGINT(20) DEFAULT NULL COMMENT 'If this is a reported record, this is the fk to the users.id column for the address book user that the medication was reported by'
    , PRIMARY KEY (`id`)
    , INDEX `lists_med_usage_category_idx`(`usage_category`)
    , INDEX `lists_med_request_intent_idx`(`request_intent`)
    , INDEX `lists_medication_list_idx` (`list_id`)
) ENGINE = InnoDB COMMENT = 'Holds additional data about patient medications.';

-- --------------------------------------------------------

--
-- Table structure for table `lists_touch`
--

DROP TABLE IF EXISTS `lists_touch`;
CREATE TABLE `lists_touch` (
  `pid` bigint(20) NOT NULL default '0',
  `type` varchar(255) NOT NULL default '',
  `date` datetime default NULL,
  PRIMARY KEY  (`pid`,`type`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `log`
--

DROP TABLE IF EXISTS `log`;
CREATE TABLE `log` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime default NULL,
  `event` varchar(255) default NULL,
  `category` varchar(255) default NULL,
  `user` varchar(255) default NULL,
  `groupname` varchar(255) default NULL,
  `comments` longtext,
  `user_notes` longtext,
  `patient_id` bigint(20) default NULL,
  `success` tinyint(1) default 1,
  `checksum` longtext,
  `crt_user` varchar(255) default NULL,
  `log_from` VARCHAR(20) DEFAULT 'open-emr',
  `menu_item_id` INT(11) DEFAULT NULL,
  `ccda_doc_id` INT(11) DEFAULT NULL COMMENT 'CCDA document id from ccda',
  PRIMARY KEY  (`id`),
  KEY `patient_id` (`patient_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;


-- --------------------------------------------------------

--
-- Table structure for table `modules`
--

DROP TABLE IF EXISTS `modules`;
CREATE TABLE `modules` (
  `mod_id` INT(11) NOT NULL AUTO_INCREMENT,
  `mod_name` VARCHAR(64) NOT NULL DEFAULT '0',
  `mod_directory` VARCHAR(64) NOT NULL DEFAULT '',
  `mod_parent` VARCHAR(64) NOT NULL DEFAULT '',
  `mod_type` VARCHAR(64) NOT NULL DEFAULT '',
  `mod_active` INT(1) UNSIGNED NOT NULL DEFAULT '0',
  `mod_ui_name` VARCHAR(64) NOT NULL DEFAULT '',
  `mod_relative_link` VARCHAR(64) NOT NULL DEFAULT '',
  `mod_ui_order` TINYINT(3) NOT NULL DEFAULT '0',
  `mod_ui_active` INT(1) UNSIGNED NOT NULL DEFAULT '0',
  `mod_description` VARCHAR(255) NOT NULL DEFAULT '',
  `mod_nick_name` VARCHAR(25) NOT NULL DEFAULT '',
  `mod_enc_menu` VARCHAR(10) NOT NULL DEFAULT 'no',
  `permissions_item_table` CHAR(100) DEFAULT NULL,
  `directory` VARCHAR(255) NOT NULL,
  `date` DATETIME NOT NULL,
  `sql_run` TINYINT(4) DEFAULT '0',
  `type` TINYINT(4) DEFAULT '0',
  `sql_version` VARCHAR(150) NOT NULL,
  `acl_version` VARCHAR(150) NOT NULL,
  PRIMARY KEY (`mod_id`,`mod_directory`)
) ENGINE=InnoDB;

--
-- Inserting data for table `modules`
--

-- --------------------------------------------------------

--
-- Table structure for table `module_acl_group_settings`
--

DROP TABLE IF EXISTS `module_acl_group_settings`;
CREATE TABLE `module_acl_group_settings` (
  `module_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `section_id` int(11) NOT NULL,
  `allowed` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`module_id`,`group_id`,`section_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `module_acl_sections`
--

DROP TABLE IF EXISTS `module_acl_sections`;
CREATE TABLE `module_acl_sections` (
  `section_id` int(11) DEFAULT NULL,
  `section_name` varchar(255) DEFAULT NULL,
  `parent_section` int(11) DEFAULT NULL,
  `section_identifier` varchar(50) DEFAULT NULL,
  `module_id` int(11) DEFAULT NULL
) ENGINE=InnoDB;

--
-- Inserting data for table `module_acl_sections`
--

-- --------------------------------------------------------

--
-- Table structure for table `module_acl_user_settings`
--

DROP TABLE IF EXISTS `module_acl_user_settings`;
CREATE TABLE `module_acl_user_settings` (
  `module_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `section_id` int(11) NOT NULL,
  `allowed` int(1) DEFAULT NULL,
  PRIMARY KEY (`module_id`,`user_id`,`section_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `module_configuration`
--

DROP TABLE IF EXISTS `module_configuration`;
CREATE TABLE `module_configuration` (
  `module_config_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `module_id` int(10) unsigned NOT NULL,
  `field_name` varchar(45) NOT NULL,
  `field_value` varchar(255) NOT NULL,
  `created_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id the user that first created this record',
  `date_added` DATETIME DEFAULT NULL COMMENT 'Datetime the record was initially created',
  `updated_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id the user that last modified this record',
  `date_modified` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Datetime the record was last modified',
  `date_created` DATETIME DEFAULT NULL COMMENT 'Datetime the record was created',
  PRIMARY KEY (`module_config_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `modules_hooks_settings`
--

DROP TABLE IF EXISTS `modules_hooks_settings`;
CREATE TABLE `modules_hooks_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mod_id` int(11) DEFAULT NULL,
  `enabled_hooks` varchar(255) DEFAULT NULL,
  `attached_to` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `modules_settings`
--

DROP TABLE IF EXISTS `modules_settings`;
CREATE TABLE `modules_settings` (
  `mod_id` INT(11) DEFAULT NULL,
  `fld_type` SMALLINT(6) DEFAULT NULL COMMENT '1=>ACL,2=>preferences,3=>hooks',
  `obj_name` VARCHAR(255) DEFAULT NULL,
  `menu_name` VARCHAR(255) DEFAULT NULL,
  `path` VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `notes`
--

DROP TABLE IF EXISTS `notes`;
CREATE TABLE `notes` (
  `id` int(11) NOT NULL default '0',
  `foreign_id` int(11) NOT NULL default '0',
  `note` varchar(255) default NULL,
  `owner` int(11) default NULL,
  `date` datetime default NULL,
  `revision` timestamp NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `foreign_id` (`owner`),
  KEY `foreign_id_2` (`foreign_id`),
  KEY `date` (`date`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `onotes`
--

DROP TABLE IF EXISTS `onotes`;
CREATE TABLE `onotes` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime default NULL,
  `body` longtext,
  `user` varchar(255) default NULL,
  `groupname` varchar(255) default NULL,
  `activity` tinyint(4) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `onsite_documents`
--

DROP TABLE IF EXISTS `onsite_documents`;
CREATE TABLE `onsite_documents` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `pid` bigint(20) UNSIGNED DEFAULT NULL,
  `facility` int(10) UNSIGNED DEFAULT NULL,
  `provider` int(10) UNSIGNED DEFAULT NULL,
  `encounter` int(10) UNSIGNED DEFAULT NULL,
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `doc_type` varchar(255) NOT NULL,
  `patient_signed_status` smallint(5) UNSIGNED NOT NULL,
  `patient_signed_time` datetime NULL,
  `authorize_signed_time` datetime DEFAULT NULL,
  `accept_signed_status` smallint(5) NOT NULL,
  `authorizing_signator` varchar(50) NOT NULL,
  `review_date` datetime NULL,
  `denial_reason` varchar(255) NOT NULL,
  `authorized_signature` text,
  `patient_signature` text,
  `full_document` mediumblob,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `template_data` longtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;


-- --------------------------------------------------------

--
-- Table structure for table `onsite_mail`
--

DROP TABLE IF EXISTS `onsite_mail`;
CREATE TABLE `onsite_mail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT NULL,
  `owner` varchar(128) DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `groupname` varchar(255) DEFAULT NULL,
  `activity` tinyint(4) DEFAULT NULL,
  `authorized` tinyint(4) DEFAULT NULL,
  `header` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `body` longtext,
  `recipient_id` varchar(128) DEFAULT NULL,
  `recipient_name` varchar(255) DEFAULT NULL,
  `sender_id` varchar(128) DEFAULT NULL,
  `sender_name` varchar(255) DEFAULT NULL,
  `assigned_to` varchar(255) DEFAULT NULL,
  `deleted` tinyint(4) DEFAULT '0' COMMENT 'flag indicates note is deleted',
  `delete_date` datetime DEFAULT NULL,
  `mtype` varchar(128) DEFAULT NULL,
  `message_status` varchar(20) NOT NULL DEFAULT 'New',
  `mail_chain` int(11) DEFAULT NULL,
  `reply_mail_chain` int(11) DEFAULT NULL,
  `is_msg_encrypted` tinyint(2) DEFAULT '0' COMMENT 'Whether messsage encrypted 0-Not encrypted, 1-Encrypted',
  PRIMARY KEY (`id`),
  KEY `pid` (`owner`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `onsite_messages`
--

DROP TABLE IF EXISTS `onsite_messages`;
CREATE TABLE `onsite_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `message` longtext,
  `ip` varchar(15) NOT NULL,
  `date` datetime NOT NULL,
  `sender_id` VARCHAR(64) NULL COMMENT 'who sent id',
  `recip_id` varchar(255) NOT NULL COMMENT 'who to id array',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='Portal messages' AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `onsite_online`
--

DROP TABLE IF EXISTS `onsite_online`;
CREATE TABLE `onsite_online` (
  `hash` varchar(32) NOT NULL,
  `ip` varchar(15) NOT NULL,
  `last_update` datetime NOT NULL,
  `username` varchar(64) NOT NULL,
  `userid` int(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`hash`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `onsite_portal_activity`
--

DROP TABLE IF EXISTS `onsite_portal_activity`;
CREATE TABLE `onsite_portal_activity` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT NULL,
  `patient_id` bigint(20) DEFAULT NULL,
  `activity` varchar(255) DEFAULT NULL,
  `require_audit` tinyint(1) DEFAULT '1',
  `pending_action` varchar(255) DEFAULT NULL,
  `action_taken` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `narrative` longtext,
  `table_action` longtext,
  `table_args` longtext,
  `action_user` int(11) DEFAULT NULL,
  `action_taken_time` datetime DEFAULT NULL,
  `checksum` longtext,
  PRIMARY KEY (`id`),
  KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `onsite_signatures`
--

DROP TABLE IF EXISTS `onsite_signatures`;
CREATE TABLE `onsite_signatures` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `status` varchar(128) NOT NULL DEFAULT 'waiting',
  `type` varchar(128) NOT NULL,
  `created` int(11) NOT NULL,
  `lastmod` datetime NOT NULL,
  `pid` bigint(20) DEFAULT NULL,
  `encounter` int(11) DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `activity` tinyint(4) NOT NULL DEFAULT '0',
  `authorized` tinyint(4) DEFAULT NULL,
  `signator` varchar(255) NOT NULL,
  `sig_image` text,
  `signature` text,
  `sig_hash` varchar(255) NOT NULL,
  `ip` varchar(46) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pid` (`pid`,`user`),
  KEY `encounter` (`encounter`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `openemr_module_vars`
--

DROP TABLE IF EXISTS `openemr_module_vars`;
CREATE TABLE `openemr_module_vars` (
  `pn_id` int(11) unsigned NOT NULL auto_increment,
  `pn_modname` varchar(64) default NULL,
  `pn_name` varchar(64) default NULL,
  `pn_value` longtext,
  PRIMARY KEY  (`pn_id`),
  KEY `pn_modname` (`pn_modname`),
  KEY `pn_name` (`pn_name`)
) ENGINE=InnoDB AUTO_INCREMENT=235;

--
-- Inserting data for table `openemr_module_vars`
--

-- --------------------------------------------------------

--
-- Table structure for table `openemr_modules`
--

DROP TABLE IF EXISTS `openemr_modules`;
CREATE TABLE `openemr_modules` (
  `pn_id` int(11) unsigned NOT NULL auto_increment,
  `pn_name` varchar(64) default NULL,
  `pn_type` int(6) NOT NULL default '0',
  `pn_displayname` varchar(64) default NULL,
  `pn_description` varchar(255) default NULL,
  `pn_regid` int(11) unsigned NOT NULL default '0',
  `pn_directory` varchar(64) default NULL,
  `pn_version` varchar(10) default NULL,
  `pn_admin_capable` tinyint(1) NOT NULL default '0',
  `pn_user_capable` tinyint(1) NOT NULL default '0',
  `pn_state` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`pn_id`)
) ENGINE=InnoDB AUTO_INCREMENT=47;

--
-- Inserting data for table `openemr_modules`
--

-- --------------------------------------------------------

--
-- Table structure for table `openemr_postcalendar_categories`
--

DROP TABLE IF EXISTS `openemr_postcalendar_categories`;
CREATE TABLE `openemr_postcalendar_categories` (
  `pc_catid` int(11) unsigned NOT NULL auto_increment,
  `pc_constant_id` VARCHAR (255) default NULL,
  `pc_catname` varchar(100) default NULL,
  `pc_catcolor` varchar(50) default NULL,
  `pc_catdesc` text,
  `pc_recurrtype` int(1) NOT NULL default '0',
  `pc_enddate` date default NULL,
  `pc_recurrspec` text,
  `pc_recurrfreq` int(3) NOT NULL default '0',
  `pc_duration` bigint(20) NOT NULL default '0',
  `pc_end_date_flag` tinyint(1) NOT NULL default '0',
  `pc_end_date_type` int(2) default NULL,
  `pc_end_date_freq` int(11) NOT NULL default '0',
  `pc_end_all_day` tinyint(1) NOT NULL default '0',
  `pc_dailylimit` int(2) NOT NULL default '0',
  `pc_cattype` INT( 11 ) NOT NULL COMMENT 'Used in grouping categories',
  `pc_active` tinyint(1) NOT NULL default 1,
  `pc_seq` int(11) NOT NULL default '0',
  `aco_spec` VARCHAR(63) NOT NULL default 'encounters|notes',
  `pc_last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`pc_catid`),
  UNIQUE KEY (`pc_constant_id`),
  KEY `basic_cat` (`pc_catname`,`pc_catcolor`)
) ENGINE=InnoDB AUTO_INCREMENT=11;

--
-- Inserting data for table `openemr_postcalendar_categories`
--


-- --------------------------------------------------------

--
-- Table structure for table `openemr_postcalendar_events`
--

DROP TABLE IF EXISTS `openemr_postcalendar_events`;
CREATE TABLE `openemr_postcalendar_events` (
  `pc_eid` int(11) unsigned NOT NULL auto_increment,
  `pc_catid` int(11) NOT NULL default '0',
  `pc_multiple` int(10) unsigned NOT NULL,
  `pc_aid` varchar(30) default NULL,
  `pc_pid` varchar(11) default NULL,
  `pc_gid` int(11) default 0,
  `pc_title` varchar(150) default NULL,
  `pc_time` datetime default NULL,
  `pc_hometext` text,
  `pc_comments` int(11) default '0',
  `pc_counter` mediumint(8) unsigned default '0',
  `pc_topic` int(3) NOT NULL default '1',
  `pc_informant` varchar(20) default NULL,
  `pc_eventDate` date NOT NULL,
  `pc_endDate` date DEFAULT NULL,
  `pc_duration` bigint(20) NOT NULL default '0',
  `pc_recurrtype` int(1) NOT NULL default '0',
  `pc_recurrspec` text,
  `pc_recurrfreq` int(3) NOT NULL default '0',
  `pc_startTime` time default NULL,
  `pc_endTime` time default NULL,
  `pc_alldayevent` int(1) NOT NULL default '0',
  `pc_location` text,
  `pc_conttel` varchar(50) default NULL,
  `pc_contname` varchar(50) default NULL,
  `pc_contemail` varchar(255) default NULL,
  `pc_website` varchar(255) default NULL,
  `pc_fee` varchar(50) default NULL,
  `pc_eventstatus` int(11) NOT NULL default '0',
  `pc_sharing` int(11) NOT NULL default '0',
  `pc_language` varchar(30) default NULL,
  `pc_apptstatus` varchar(15) NOT NULL default '-',
  `pc_prefcatid` int(11) NOT NULL default '0',
  `pc_facility` int(11) NOT NULL default '0' COMMENT 'facility id for this event',
  `pc_sendalertsms` VARCHAR(3) NOT NULL DEFAULT 'NO',
  `pc_sendalertemail` VARCHAR( 3 ) NOT NULL DEFAULT 'NO',
  `pc_billing_location` SMALLINT (6) NOT NULL DEFAULT '0',
  `pc_room` varchar(20) NOT NULL DEFAULT '',
  `uuid` binary(16) DEFAULT NULL,
  PRIMARY KEY  (`pc_eid`),
  KEY `basic_event` (`pc_catid`,`pc_aid`,`pc_eventDate`,`pc_endDate`,`pc_eventstatus`,`pc_sharing`,`pc_topic`),
  KEY `pc_eventDate` (`pc_eventDate`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=7;

-- --------------------------------------------------------

--
-- Table structure for table `patient_access_onsite`
--

DROP TABLE IF EXISTS `patient_access_onsite`;
CREATE TABLE `patient_access_onsite`(
  `id` INT NOT NULL AUTO_INCREMENT,
  `pid` bigint(20),
  `portal_username` VARCHAR(100),
  `portal_pwd` VARCHAR(255),
  `portal_pwd_status` TINYINT DEFAULT '1' COMMENT '0=>Password Created Through Demographics by The provider or staff. Patient Should Change it at first time it.1=>Pwd updated or created by patient itself',
  `portal_login_username` VARCHAR(100) DEFAULT NULL COMMENT 'User entered username',
  `portal_onetime`  VARCHAR(255) DEFAULT NULL,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pid` (`pid`)
)ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `patient_data`
--

DROP TABLE IF EXISTS `patient_data`;
CREATE TABLE `patient_data` (
  `id` bigint(20) NOT NULL auto_increment,
  `uuid` binary(16) DEFAULT NULL,
  `title` varchar(255) NOT NULL default '',
  `language` varchar(255) NOT NULL default '',
  `financial` varchar(255) NOT NULL default '',
  `fname` varchar(255) NOT NULL default '',
  `lname` varchar(255) NOT NULL default '',
  `mname` varchar(255) NOT NULL default '',
  `DOB` date default NULL,
  `street` varchar(255) NOT NULL default '',
  `postal_code` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `state` varchar(255) NOT NULL default '',
  `country_code` varchar(255) NOT NULL default '',
  `drivers_license` varchar(255) NOT NULL default '',
  `ss` varchar(255) NOT NULL default '',
  `occupation` longtext,
  `phone_home` varchar(255) NOT NULL default '',
  `phone_biz` varchar(255) NOT NULL default '',
  `phone_contact` varchar(255) NOT NULL default '',
  `phone_cell` varchar(255) NOT NULL default '',
  `pharmacy_id` int(11) NOT NULL default '0',
  `status` varchar(255) NOT NULL default '',
  `contact_relationship` varchar(255) NOT NULL default '',
  `date` datetime default NULL,
  `sex` varchar(255) NOT NULL default '' COMMENT 'Sex at birth',
  `referrer` varchar(255) NOT NULL default '',
  `referrerID` varchar(255) NOT NULL default '',
  `providerID` int(11) default NULL,
  `ref_providerID` int(11) default NULL,
  `email` varchar(255) NOT NULL default '',
  `email_direct` varchar(255) NOT NULL default '',
  `ethnoracial` varchar(255) NOT NULL default '',
  `race` varchar(255) NOT NULL default '',
  `ethnicity` varchar(255) NOT NULL default '',
  `religion` varchar(40) NOT NULL default '',
  `interpreter` varchar(255) NOT NULL default '' COMMENT 'original field used for determining if patient needs an interpreter, now used for additional notes about need for interpreter',
  `interpreter_needed` TEXT COMMENT 'fk to list_options.option_id where list_id=yes_no_unknown used to determine if patient needs an interpreter',
  `migrantseasonal` varchar(255) NOT NULL default '',
  `family_size` varchar(255) NOT NULL default '',
  `monthly_income` varchar(255) NOT NULL default '',
  `billing_note` text,
  `homeless` varchar(255) NOT NULL default '',
  `financial_review` datetime default NULL,
  `pubpid` varchar(255) NOT NULL default '',
  `pid` bigint(20) NOT NULL default '0',
  `genericname1` varchar(255) NOT NULL default '',
  `genericval1` varchar(255) NOT NULL default '',
  `genericname2` varchar(255) NOT NULL default '',
  `genericval2` varchar(255) NOT NULL default '',
  `hipaa_mail` varchar(3) NOT NULL default '',
  `hipaa_voice` varchar(3) NOT NULL default '',
  `hipaa_notice` varchar(3) NOT NULL default '',
  `hipaa_message` varchar(20) NOT NULL default '',
  `hipaa_allowsms` VARCHAR(3) NOT NULL DEFAULT 'NO',
  `hipaa_allowemail` VARCHAR(3) NOT NULL DEFAULT 'NO',
  `squad` varchar(32) NOT NULL default '',
  `fitness` int(11) NOT NULL default '0',
  `referral_source` varchar(30) NOT NULL default '',
  `usertext1` varchar(255) NOT NULL DEFAULT '',
  `usertext2` varchar(255) NOT NULL DEFAULT '',
  `usertext3` varchar(255) NOT NULL DEFAULT '',
  `usertext4` varchar(255) NOT NULL DEFAULT '',
  `usertext5` varchar(255) NOT NULL DEFAULT '',
  `usertext6` varchar(255) NOT NULL DEFAULT '',
  `usertext7` varchar(255) NOT NULL DEFAULT '',
  `usertext8` varchar(255) NOT NULL DEFAULT '',
  `userlist1` varchar(255) NOT NULL DEFAULT '',
  `userlist2` varchar(255) NOT NULL DEFAULT '',
  `userlist3` varchar(255) NOT NULL DEFAULT '',
  `userlist4` varchar(255) NOT NULL DEFAULT '',
  `userlist5` varchar(255) NOT NULL DEFAULT '',
  `userlist6` varchar(255) NOT NULL DEFAULT '',
  `userlist7` varchar(255) NOT NULL DEFAULT '',
  `pricelevel` varchar(255) NOT NULL default 'standard',
  `regdate`     DATETIME DEFAULT NULL COMMENT 'Registration Date',
  `contrastart` date DEFAULT NULL COMMENT 'Date contraceptives initially used',
  `completed_ad` VARCHAR(3) NOT NULL DEFAULT 'NO',
  `ad_reviewed` DATETIME DEFAULT NULL COMMENT 'Date and time the advance care directive was reviewed and validated by the authenticator user.',
  `advance_directive_user_authenticator` BIGINT(20) COMMENT 'fk to users.id of the user who authenticates that the advance care directive is valid.',
  `vfc` varchar(255) NOT NULL DEFAULT '',
  `mothersname` varchar(255) NOT NULL DEFAULT '',
  `guardiansname` TEXT,
  `allow_imm_reg_use` varchar(255) NOT NULL DEFAULT '',
  `allow_imm_info_share` varchar(255) NOT NULL DEFAULT '',
  `allow_health_info_ex` varchar(255) NOT NULL DEFAULT '',
  `allow_patient_portal` varchar(31) NOT NULL DEFAULT '',
  `deceased_date` datetime default NULL,
  `deceased_reason` varchar(255) NOT NULL default '',
  `soap_import_status` TINYINT(4) DEFAULT NULL COMMENT '1-Prescription Press 2-Prescription Import 3-Allergy Press 4-Allergy Import',
  `cmsportal_login` varchar(60) NOT NULL default '',
  `care_team_provider` TEXT,
  `care_team_facility` TEXT,
  `care_team_status` TEXT,
  `county` varchar(40) NOT NULL default '',
  `industry` TEXT,
  `imm_reg_status` TEXT,
  `imm_reg_stat_effdate` TEXT,
  `publicity_code` TEXT,
  `publ_code_eff_date` TEXT,
  `protect_indicator` TEXT,
  `prot_indi_effdate` TEXT,
  `guardianrelationship` TEXT,
  `guardiansex` TEXT,
  `guardianaddress` TEXT,
  `guardiancity` TEXT,
  `guardianstate` TEXT,
  `guardianpostalcode` TEXT,
  `guardiancountry` TEXT,
  `guardianphone` TEXT,
  `guardianworkphone` TEXT,
  `guardianemail` TEXT,
  `sexual_orientation` TEXT,
  `gender_identity` TEXT,
  `birth_fname` TEXT,
  `birth_lname` TEXT,
  `birth_mname` TEXT,
  `dupscore` INT NOT NULL default -9,
  `name_history` TINYTEXT,
  `suffix` TINYTEXT,
  `street_line_2` TINYTEXT,
  `patient_groups` TEXT,
  `prevent_portal_apps` TEXT,
  `provider_since_date` TINYTEXT,
  `created_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id the user that first created this record',
  `updated_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id the user that last modified this record',
  `preferred_name` TINYTEXT,
  `nationality_country` TINYTEXT,
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tribal_affiliations` TEXT,
  `sex_identified` TEXT COMMENT 'Patient reported current sex',
  `pronoun` TEXT,
  UNIQUE KEY `pid` (`pid`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `idx_patient_name` (`lname`, `fname`),
  KEY `idx_patient_dob` (`DOB`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
-- --------------------------------------------------------

--
-- Table structure for table `patient_history` that is a dependent table on `patient_data`
DROP TABLE IF EXISTS `patient_history`;
CREATE TABLE `patient_history` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT
    , `uuid` BINARY(16) NULL
    , `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    , `care_team_provider` TEXT
    , `care_team_facility` TEXT
    , `pid` BIGINT(20) NOT NULL
    , `history_type_key` varchar(36) DEFAULT NULL
    , `previous_name_prefix` TEXT
    , `previous_name_first` TEXT
    , `previous_name_middle` TEXT
    , `previous_name_last` TEXT
    , `previous_name_suffix` TEXT
    , `previous_name_enddate` date DEFAULT NULL
    ,`created_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id the user that first created this record'
    , PRIMARY KEY (`id`)
    , UNIQUE `uuid` (`uuid`)
    , KEY `pid_idx` (`pid`)
) ENGINE = InnoDB;
--
-- Table structure for table `patient_portal_menu`
--

DROP TABLE IF EXISTS `patient_portal_menu`;
CREATE TABLE `patient_portal_menu` (
  `patient_portal_menu_id` INT(11) NOT NULL AUTO_INCREMENT,
  `patient_portal_menu_group_id` INT(11) DEFAULT NULL,
  `menu_name` VARCHAR(40) DEFAULT NULL,
  `menu_order` SMALLINT(4) DEFAULT NULL,
  `menu_status` TINYINT(2) DEFAULT '1',
  PRIMARY KEY (`patient_portal_menu_id`)
) ENGINE=INNODB AUTO_INCREMENT=14;

-- --------------------------------------------------------

--
-- Table structure for table `patient_reminders`
--

DROP TABLE IF EXISTS `patient_reminders`;
CREATE TABLE `patient_reminders` (
  `id` bigint(20) NOT NULL auto_increment,
  `active` tinyint(1) NOT NULL default 1 COMMENT '1 if active and 0 if not active',
  `date_inactivated` datetime DEFAULT NULL,
  `reason_inactivated` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to list_options list rule_reminder_inactive_opt',
  `due_status` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to list_options list rule_reminder_due_opt',
  `pid` bigint(20) NOT NULL COMMENT 'id from patient_data table',
  `category` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to the category item in the rule_action_item table',
  `item` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to the item column in the rule_action_item table',
  `date_created` datetime DEFAULT NULL,
  `date_sent` datetime DEFAULT NULL,
  `voice_status` tinyint(1) NOT NULL default 0 COMMENT '0 if not sent and 1 if sent',
  `sms_status` tinyint(1) NOT NULL default 0 COMMENT '0 if not sent and 1 if sent',
  `email_status` tinyint(1) NOT NULL default 0 COMMENT '0 if not sent and 1 if sent',
  `mail_status` tinyint(1) NOT NULL default 0 COMMENT '0 if not sent and 1 if sent',
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  KEY (`category`,`item`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `patient_tracker`
--

DROP TABLE IF EXISTS `patient_tracker`;
CREATE TABLE `patient_tracker` (
  `id`                     bigint(20)   NOT NULL auto_increment,
  `date`                   datetime     DEFAULT NULL,
  `apptdate`               date         DEFAULT NULL,
  `appttime`               time         DEFAULT NULL,
  `eid`                    bigint(20)   NOT NULL default '0',
  `pid`                    bigint(20)   NOT NULL default '0',
  `original_user`          varchar(255) NOT NULL default '' COMMENT 'This is the user that created the original record',
  `encounter`              bigint(20)   NOT NULL default '0',
  `lastseq`                varchar(4)   NOT NULL default '' COMMENT 'The element file should contain this number of elements',
  `random_drug_test`       TINYINT(1)   DEFAULT NULL COMMENT 'NULL if not randomized. If randomized, 0 is no, 1 is yes',
  `drug_screen_completed`  TINYINT(1)   NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY (`eid`),
  KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

--
-- Table structure for table `patient_tracker_element`
--

DROP TABLE IF EXISTS `patient_tracker_element`;
CREATE TABLE `patient_tracker_element` (
  `pt_tracker_id`      bigint(20)   NOT NULL default '0' COMMENT 'maps to id column in patient_tracker table',
  `start_datetime`     datetime     DEFAULT NULL,
  `room`               varchar(20)  NOT NULL default '',
  `status`             varchar(31)  NOT NULL default '',
  `seq`                varchar(4)   NOT NULL default '' COMMENT 'This is a numerical sequence for this pt_tracker_id events',
  `user`               varchar(255) NOT NULL default '' COMMENT 'This is the user that created this element',
  KEY  (`pt_tracker_id`,`seq`)
) ENGINE=InnoDB;

--
-- Table structure for table `payments`
--

DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` bigint(20) NOT NULL auto_increment,
  `pid` bigint(20) NOT NULL default '0',
  `dtime` datetime NOT NULL,
  `encounter` bigint(20) NOT NULL default '0',
  `user` varchar(255) default NULL,
  `method` varchar(255) default NULL,
  `source` varchar(255) default NULL,
  `amount1` decimal(12,2) NOT NULL default '0.00',
  `amount2` decimal(12,2) NOT NULL default '0.00',
  `posted1` decimal(12,2) NOT NULL default '0.00',
  `posted2` decimal(12,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `payment_gateway_details`
--

DROP TABLE IF EXISTS `payment_gateway_details`;
CREATE TABLE `payment_gateway_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `service_name` varchar(100) DEFAULT NULL,
  `login_id` varchar(255) DEFAULT NULL,
  `transaction_key` varchar(255) DEFAULT NULL,
  `md5` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `pharmacies`
--

DROP TABLE IF EXISTS `pharmacies`;
CREATE TABLE `pharmacies` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(255) default NULL,
  `transmit_method` int(11) NOT NULL default '1',
  `email` varchar(255) default NULL,
  `ncpdp` int(12) DEFAULT NULL,
  `npi` int(12) DEFAULT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `phone_numbers`
--

DROP TABLE IF EXISTS `phone_numbers`;
CREATE TABLE `phone_numbers` (
  `id` int(11) NOT NULL default '0',
  `country_code` varchar(5) default NULL,
  `area_code` char(3) default NULL,
  `prefix` char(3) default NULL,
  `number` varchar(4) default NULL,
  `type` int(11) default NULL,
  `foreign_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `foreign_id` (`foreign_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `pnotes`
--

DROP TABLE IF EXISTS `pnotes`;
CREATE TABLE `pnotes` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime default NULL,
  `body` longtext,
  `pid` bigint(20) default NULL,
  `user` varchar(255) default NULL,
  `groupname` varchar(255) default NULL,
  `activity` tinyint(4) default NULL,
  `authorized` tinyint(4) default NULL,
  `title` varchar(255) default NULL,
  `assigned_to` varchar(255) default NULL,
  `deleted` tinyint(4) default 0 COMMENT 'flag indicates note is deleted',
  `message_status` VARCHAR(20) NOT NULL DEFAULT 'New',
  `portal_relation` VARCHAR(100) NULL,
  `is_msg_encrypted` TINYINT(2) DEFAULT '0' COMMENT 'Whether messsage encrypted 0-Not encrypted, 1-Encrypted',
  `update_by` bigint(20) default NULL,
  `update_date` DATETIME DEFAULT NULL,
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `prescriptions`
--

DROP TABLE IF EXISTS `prescriptions`;
CREATE TABLE `prescriptions` (
  `id` int(11) NOT NULL auto_increment,
  `uuid` binary(16) DEFAULT NULL,
  `patient_id` bigint(20) default NULL,
  `filled_by_id` int(11) default NULL,
  `pharmacy_id` int(11) default NULL,
  `date_added` DATETIME DEFAULT NULL COMMENT 'Datetime the prescriptions was initially created',
  `date_modified` DATETIME DEFAULT NULL COMMENT 'Datetime the prescriptions was last modified',
  `provider_id` int(11) default NULL,
  `encounter` int(11) default NULL,
  `start_date` date default NULL,
  `drug` varchar(150) default NULL,
  `drug_id` int(11) NOT NULL default '0',
  `rxnorm_drugcode` varchar(25) DEFAULT NULL,
  `form` int(3) default NULL,
  `dosage` varchar(100) default NULL,
  `quantity` varchar(31) default NULL,
  `size` varchar(25) DEFAULT NULL,
  `unit` int(11) default NULL,
  `route` varchar(100) default NULL COMMENT 'Max size 100 characters is same max as immunizations',
  `interval` int(11) default NULL,
  `substitute` int(11) default NULL,
  `refills` int(11) default NULL,
  `per_refill` int(11) default NULL,
  `filled_date` date default NULL,
  `medication` int(11) default NULL,
  `note` text,
  `active` int(11) NOT NULL default '1',
  `datetime` DATETIME DEFAULT NULL,
  `user` VARCHAR(50) DEFAULT NULL,
  `site` VARCHAR(50) DEFAULT NULL,
  `prescriptionguid` VARCHAR(50) DEFAULT NULL,
  `erx_source` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0-OpenEMR 1-External',
  `erx_uploaded` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0-Pending NewCrop upload 1-Uploaded to NewCrop',
  `drug_info_erx` TEXT,
  `external_id` VARCHAR(20) DEFAULT NULL,
  `end_date` date default NULL,
  `indication` text,
  `prn` VARCHAR(30) DEFAULT NULL,
  `ntx` INT(2) DEFAULT NULL,
  `rtx` INT(2) DEFAULT NULL,
  `txDate` DATE NOT NULL,
  `usage_category` VARCHAR(100) NULL COMMENT 'option_id in list_options.list_id=medication-usage-category',
  `usage_category_title` VARCHAR(255) NOT NULL COMMENT 'title in list_options.list_id=medication-usage-category',
  `request_intent` VARCHAR(100) NULL COMMENT 'option_id in list_options.list_id=medication-request-intent',
  `request_intent_title` VARCHAR(255) NOT NULL COMMENT 'title in list_options.list_id=medication-request-intent',
  `drug_dosage_instructions` longtext COMMENT 'Medication dosage instructions',
  `diagnosis` TEXT COMMENT 'Diagnosis or reason for the prescription',
  `created_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id the user that first created this record',
  `updated_by` BIGINT(20) DEFAULT NULL COMMENT 'users.id the user that last modified this record',
  PRIMARY KEY  (`id`),
  KEY `patient_id` (`patient_id`),
  UNIQUE INDEX `uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `prices`
--

DROP TABLE IF EXISTS `prices`;
CREATE TABLE `prices` (
  `pr_id` varchar(11) NOT NULL default '',
  `pr_selector` varchar(255) NOT NULL default '' COMMENT 'template selector for drugs, empty for codes',
  `pr_level` varchar(31) NOT NULL default '',
  `pr_price` decimal(12,2) NOT NULL default '0.00' COMMENT 'price in local currency',
  PRIMARY KEY  (`pr_id`,`pr_selector`,`pr_level`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `pro_assessments`
--

DROP TABLE IF EXISTS `pro_assessments`;
CREATE TABLE `pro_assessments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_oid` varchar(255) NOT NULL COMMENT 'unique id for specific instrument, pulled from assessment center API',
  `form_name` varchar (255) NOT NULL COMMENT 'pulled from assessment center API',
  `user_id` int(11) NOT NULL COMMENT 'ID for user that orders the form',
  `deadline` datetime NOT NULL COMMENT 'deadline to complete the form, will be used when sending notification and reminders',
  `patient_id` int(11) NOT NULL COMMENT 'ID for patient to order the form for',
  `assessment_oid` varchar(255) NOT NULL COMMENT 'unique id for this specific assessment, pulled from assessment center API',
  `status` varchar(255) NOT NULL COMMENT 'ordered or completed',
  `score` double NOT NULL COMMENT 'T-Score for the assessment',
  `error` double NOT NULL COMMENT 'Standard error for the score',
  `created_at` datetime NOT NULL COMMENT 'timestamp recording the creation time of this assessment',
  `updated_at` datetime NOT NULL COMMENT 'this field indicates the completion time when the status is completed',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `registry`
--

DROP TABLE IF EXISTS `registry`;
CREATE TABLE `registry` (
  `name` varchar(255) default NULL,
  `state` tinyint(4) default NULL,
  `directory` varchar(255) default NULL,
  `id` bigint(20) NOT NULL auto_increment,
  `sql_run` tinyint(4) default NULL,
  `unpackaged` tinyint(4) default NULL,
  `date` datetime default NULL,
  `priority` int(11) default '0',
  `category` varchar(255) default NULL,
  `nickname` varchar(255) default NULL,
  `patient_encounter` TINYINT NOT NULL DEFAULT '1',
  `therapy_group_encounter` TINYINT NOT NULL DEFAULT '0',
  `aco_spec` varchar(63) NOT NULL default 'encounters|notes',
  `form_foreign_id` BIGINT(21) NULL DEFAULT NULL COMMENT 'An id to a form repository. Primarily questionnaire_repository.',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25;

--
-- Inserting data for table `registry`
--

-- --------------------------------------------------------

--
-- Table structure for table `report_itemized`
-- (goal is optimize insert performance, so only one key)
--

DROP TABLE IF EXISTS `report_itemized`;
CREATE TABLE `report_itemized` (
  `report_id` bigint(20) NOT NULL,
  `itemized_test_id` smallint(6) NOT NULL,
  `numerator_label` varchar(25) NOT NULL DEFAULT '' COMMENT 'Only used in special cases',
  `pass` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 is fail, 1 is pass, 2 is excluded',
  `pid` bigint(20) NOT NULL,
  `rule_id` VARCHAR(31) DEFAULT NULL  COMMENT 'fk to clinical_rules.rule_id',
  `item_details` TEXT COMMENT 'JSON with specific sub item results for a clinical rule',
  KEY (`report_id`,`itemized_test_id`,`numerator_label`,`pass`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `report_results`
--

DROP TABLE IF EXISTS `report_results`;
CREATE TABLE `report_results` (
  `report_id` bigint(20) NOT NULL,
  `field_id` varchar(31) NOT NULL default '',
  `field_value` text,
  PRIMARY KEY (`report_id`,`field_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `rule_action`
--

DROP TABLE IF EXISTS `rule_action`;
CREATE TABLE `rule_action` (
  `id` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to the id column in the clinical_rules table',
  `group_id` bigint(20) NOT NULL DEFAULT 1 COMMENT 'Contains group id to identify collection of targets in a rule',
  `category` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to the category item in the rule_action_item table',
  `item` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to the item column in the rule_action_item table',
  KEY  (`id`)
) ENGINE=InnoDB;

--
-- Standard clinical rule actions
--

-- --------------------------------------------------------

--
-- Table structure for table `rule_action_item`
--

DROP TABLE IF EXISTS `rule_action_item`;
CREATE TABLE `rule_action_item` (
  `category` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to list_options list rule_action_category',
  `item` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to list_options list rule_action',
  `clin_rem_link` varchar(255) NOT NULL DEFAULT '' COMMENT 'Custom html link in clinical reminder widget',
  `reminder_message` TEXT COMMENT 'Custom message in patient reminder',
  `custom_flag` tinyint(1) NOT NULL default 0 COMMENT '1 indexed to rule_patient_data, 0 indexed within main schema',
  PRIMARY KEY  (`category`,`item`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `rule_filter`
--

DROP TABLE IF EXISTS `rule_filter`;
CREATE TABLE `rule_filter` (
  `id` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to the id column in the clinical_rules table',
  `include_flag` tinyint(1) NOT NULL default 0 COMMENT '0 is exclude and 1 is include',
  `required_flag` tinyint(1) NOT NULL default 0 COMMENT '0 is optional and 1 is required',
  `method` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to list_options list rule_filters',
  `method_detail` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to list_options lists rule__intervals',
  `value` varchar(255) NOT NULL DEFAULT '',
  KEY  (`id`)
) ENGINE=InnoDB;

--
-- Standard clinical rule filters
--
-- Hypertension: Blood Pressure Measurement
--

-- Tobacco Use Assessment
-- no filters
-- Tobacco Cessation Intervention

-- Adult Weight Screening and Follow-Up

-- Weight Assessment and Counseling for Children and Adolescents

-- Influenza Immunization for Patients >= 50 Years Old

-- Pneumonia Vaccination Status for Older Adults

-- Diabetes: Hemoglobin A1C

-- Diabetes: Urine Microalbumin

-- Diabetes: Eye Exam

-- Diabetes: Foot Exam

-- Cancer Screening: Mammogram

-- Cancer Screening: Pap Smear

-- Cancer Screening: Colon Cancer Screening

-- Cancer Screening: Prostate Cancer Screening

--
-- Rule filters to specifically demonstrate passing of NIST criteria
--
-- Coumadin Management - INR Monitoring
--

-- Penicillin Allergy Assessment

-- --------------------------------------------------------

--
-- Table structure for table `rule_patient_data`
--

DROP TABLE IF EXISTS `rule_patient_data`;
CREATE TABLE `rule_patient_data` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime DEFAULT NULL,
  `pid` bigint(20) NOT NULL,
  `category` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to the category item in the rule_action_item table',
  `item` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to the item column in the rule_action_item table',
  `complete` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to list_options list yesno',
  `result` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (`id`),
  KEY (`pid`),
  KEY (`category`,`item`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `rule_reminder`
--

DROP TABLE IF EXISTS `rule_reminder`;
CREATE TABLE `rule_reminder` (
  `id` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to the id column in the clinical_rules table',
  `method` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to list_options list rule_reminder_methods',
  `method_detail` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to list_options list rule_reminder_intervals',
  `value` varchar(255) NOT NULL DEFAULT '',
  KEY  (`id`)
) ENGINE=InnoDB;

-- Hypertension: Blood Pressure Measurement
-- Tobacco Use Assessment
-- Tobacco Cessation Intervention
-- Adult Weight Screening and Follow-Up
-- Weight Assessment and Counseling for Children and Adolescents
-- Influenza Immunization for Patients >= 50 Years Old
-- Pneumonia Vaccination Status for Older Adults
-- Diabetes: Hemoglobin A1C
-- Diabetes: Urine Microalbumin
-- Diabetes: Eye Exam
-- Diabetes: Foot Exam
-- Cancer Screening: Mammogram
-- Cancer Screening: Pap Smear
-- Cancer Screening: Colon Cancer Screening
-- Cancer Screening: Prostate Cancer Screening
-- Coumadin Management - INR Monitoring
-- Data Entry - Social Security Number
-- Penicillin Allergy Assessment
-- Blood Pressure Measurement
-- INR Measurement
-- --------------------------------------------------------

--
-- Table structure for table `rule_target`
--

DROP TABLE IF EXISTS `rule_target`;
CREATE TABLE `rule_target` (
  `id` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to the id column in the clinical_rules table',
  `group_id` bigint(20) NOT NULL DEFAULT 1 COMMENT 'Contains group id to identify collection of targets in a rule',
  `include_flag` tinyint(1) NOT NULL default 0 COMMENT '0 is exclude and 1 is include',
  `required_flag` tinyint(1) NOT NULL default 0 COMMENT '0 is required and 1 is optional',
  `method` varchar(31) NOT NULL DEFAULT '' COMMENT 'Maps to list_options list rule_targets',
  `value` varchar(255) NOT NULL DEFAULT '' COMMENT 'Data is dependent on the method',
  `interval` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Only used in interval entries',
  KEY  (`id`)
) ENGINE=InnoDB;

--
-- Standard clinical rule targets
--
-- Hypertension: Blood Pressure Measurement
-- Tobacco Use Assessment
-- Tobacco Cessation Intervention
-- Adult Weight Screening and Follow-Up
-- Weight Assessment and Counseling for Children and Adolescents
-- Influenza Immunization for Patients >= 50 Years Old
-- Pneumonia Vaccination Status for Older Adults
-- Diabetes: Hemoglobin A1C
-- Diabetes: Urine Microalbumin
-- Diabetes: Eye Exam
-- Diabetes: Foot Exam
-- Cancer Screening: Mammogram
-- Cancer Screening: Pap Smear
-- Cancer Screening: Colon Cancer Screening
-- Cancer Screening: Prostate Cancer Screening
--
-- Rule targets to specifically demonstrate passing of NIST criteria
--
-- Coumadin Management - INR Monitoring
-- Data entry - Social security number.
-- Penicillin allergy assessment.
-- Blood Pressure Measurement
-- INR Measurement
-- --------------------------------------------------------

--
-- Table structure for table `sequences`
--

DROP TABLE IF EXISTS `sequences`;
CREATE TABLE `sequences` (
  `id` int(11) unsigned NOT NULL default '0'
) ENGINE=InnoDB;

--
-- Inserting data for table `sequences`
--

-- --------------------------------------------------------

--
-- Table structure for table `session_tracker`
--

DROP TABLE IF EXISTS `session_tracker`;
CREATE TABLE `session_tracker` (
  `uuid` binary(16) NOT NULL DEFAULT '',
  `created` timestamp NULL,
  `last_updated` timestamp NULL,
  `number_scripts` bigint DEFAULT 1,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `payment_processing_audit`
--

DROP TABLE IF EXISTS `payment_processing_audit`;
CREATE TABLE `payment_processing_audit` (
  `uuid` binary(16) NOT NULL DEFAULT '',
  `service` varchar(50) DEFAULT NULL,
  `pid` bigint NOT NULL,
  `success` tinyint DEFAULT 0,
  `action_name` varchar(50) DEFAULT NULL,
  `amount` varchar(20) DEFAULT NULL,
  `ticket` varchar(100) DEFAULT NULL,
  `transaction_id` varchar(100) DEFAULT NULL,
  `audit_data` text,
  `date` datetime DEFAULT NULL,
  `map_uuid` binary(16) DEFAULT NULL,
  `map_transaction_id` varchar(100) DEFAULT NULL,
  `reverted` tinyint DEFAULT 0,
  `revert_action_name` varchar(50) DEFAULT NULL,
  `revert_transaction_id` varchar(100) DEFAULT NULL,
  `revert_audit_data` text,
  `revert_date` datetime DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY (`pid`),
  KEY (`success`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `supported_external_dataloads`
--

DROP TABLE IF EXISTS `supported_external_dataloads`;
CREATE TABLE `supported_external_dataloads` (
  `load_id` SERIAL,
  `load_type` varchar(24) NOT NULL DEFAULT '',
  `load_source` varchar(24) NOT NULL DEFAULT 'CMS',
  `load_release_date` date NOT NULL,
  `load_filename` varchar(256) NOT NULL DEFAULT '',
  `load_checksum` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB;

--
-- Inserting data for table `supported_external_dataloads`
--

-- --------------------------------------------------------

--
-- Table structure for table `transactions`
--

DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
  `id`                      bigint(20)   NOT NULL auto_increment,
  `date`                    datetime     default NULL,
  `title`                   varchar(255) NOT NULL DEFAULT '',
  `pid`                     bigint(20)   default NULL,
  `user`                    varchar(255) NOT NULL DEFAULT '',
  `groupname`               varchar(255) NOT NULL DEFAULT '',
  `authorized`              tinyint(4)   default NULL,
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL auto_increment,
  `uuid` binary(16) DEFAULT NULL,
  `username` varchar(255) default NULL,
  `password` longtext,
  `authorized` tinyint(4) default NULL,
  `info` longtext,
  `source` tinyint(4) default NULL,
  `fname` varchar(255) default NULL,
  `mname` varchar(255) default NULL,
  `lname` varchar(255) default NULL,
  `suffix` varchar(255) default NULL,
  `federaltaxid` varchar(255) default NULL,
  `federaldrugid` varchar(255) default NULL,
  `upin` varchar(255) default NULL,
  `facility` varchar(255) default NULL,
  `facility_id` int(11) NOT NULL default '0',
  `see_auth` int(11) NOT NULL default '1',
  `active` tinyint(1) NOT NULL default '1',
  `npi` varchar(15) default NULL,
  `title` varchar(30) default NULL,
  `specialty` varchar(255) default NULL,
  `billname` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `email_direct` varchar(255) NOT NULL default '',
  `google_signin_email` VARCHAR(255) UNIQUE DEFAULT NULL,
  `url` varchar(255) default NULL,
  `assistant` varchar(255) default NULL,
  `organization` varchar(255) default NULL,
  `valedictory` varchar(255) default NULL,
  `street` varchar(60) default NULL,
  `streetb` varchar(60) default NULL,
  `city` varchar(30) default NULL,
  `state` varchar(30) default NULL,
  `zip` varchar(20) default NULL,
  `country_code` varchar(255) COMMENT 'ISO 3166-1 alpha-2 country code for address but can take entire country name for now',
  `street2` varchar(60) default NULL,
  `streetb2` varchar(60) default NULL,
  `city2` varchar(30) default NULL,
  `state2` varchar(30) default NULL,
  `zip2` varchar(20) default NULL,
  `country_code2` varchar(255) COMMENT 'ISO 3166-1 alpha-2 country code for address but can take entire country name for now',
  `phone` varchar(30) default NULL,
  `fax` varchar(30) default NULL,
  `phonew1` varchar(30) default NULL,
  `phonew2` varchar(30) default NULL,
  `phonecell` varchar(30) default NULL,
  `notes` text,
  `cal_ui` tinyint(4) NOT NULL default '1',
  `taxonomy` varchar(30) NOT NULL DEFAULT '207Q00000X',
  `calendar` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = appears in calendar',
  `abook_type` varchar(31) NOT NULL DEFAULT '',
  `default_warehouse` varchar(31) NOT NULL DEFAULT '',
  `irnpool` varchar(31) NOT NULL DEFAULT '',
  `state_license_number` VARCHAR(25) DEFAULT NULL,
  `weno_prov_id` VARCHAR(15) DEFAULT NULL,
  `newcrop_user_role` VARCHAR(30) DEFAULT NULL,
  `cpoe` tinyint(1) NULL DEFAULT NULL,
  `physician_type` VARCHAR(50) DEFAULT NULL,
  `main_menu_role` VARCHAR(50) NOT NULL DEFAULT 'standard',
  `patient_menu_role` VARCHAR(50) NOT NULL DEFAULT 'standard',
  `portal_user` tinyint(1) NOT NULL DEFAULT '0',
  `supervisor_id` int(11) NOT NULL DEFAULT '0',
  `billing_facility` TEXT,
  `billing_facility_id` INT(11) NOT NULL DEFAULT '0',
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `abook_type` (`abook_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

--
-- Inserting data for table `users`
--
-- NOTE THIS IS DONE AFTER INSTALLATION WHERE THE sql/official_additional_users.sql script is called durig setup
--  (so these inserts can be found in the sql/official_additional_users.sql script)
--

-- --------------------------------------------------------

--
-- Table structure for table `user_secure`
--

DROP TABLE IF EXISTS `users_secure`;
CREATE TABLE `users_secure` (
  `id` bigint(20) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255),
  `last_update_password` datetime DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `password_history1` varchar(255),
  `password_history2` varchar(255),
  `password_history3` varchar(255),
  `password_history4` varchar(255),
  `last_challenge_response` datetime DEFAULT NULL,
  `login_work_area` text,
  `total_login_fail_counter` bigint DEFAULT 0,
  `login_fail_counter` INT(11) DEFAULT '0',
  `last_login_fail` datetime DEFAULT NULL,
  `auto_block_emailed` tinyint DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `USERNAME_ID` (`id`,`username`)
) ENGINE=InnoDb;

-- --------------------------------------------------------

--
-- Table structure for table `user_settings`
--

DROP TABLE IF EXISTS `user_settings`;
CREATE TABLE `user_settings` (
  `setting_user`  bigint(20)   NOT NULL DEFAULT 0,
  `setting_label` varchar(100)  NOT NULL,
  `setting_value` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`setting_user`, `setting_label`)
) ENGINE=InnoDB;

--
-- Inserting data for table `user_settings`
--

-- --------------------------------------------------------

--
-- Table structure for table `uuid_mapping`
--

DROP TABLE IF EXISTS `uuid_mapping`;
CREATE TABLE `uuid_mapping` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uuid` binary(16) NOT NULL DEFAULT '',
  `resource` varchar(255) NOT NULL DEFAULT '',
  `resource_path` VARCHAR(255) DEFAULT NULL,
  `table` varchar(255) NOT NULL DEFAULT '',
  `target_uuid` binary(16) NOT NULL DEFAULT '',
  `created` timestamp NULL,
  PRIMARY KEY (`id`),
  KEY `uuid` (`uuid`),
  KEY `resource` (`resource`),
  KEY `table` (`table`),
  KEY `target_uuid` (`target_uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `uuid_registry`
--

DROP TABLE IF EXISTS `uuid_registry`;
CREATE TABLE `uuid_registry` (
  `uuid` binary(16) NOT NULL DEFAULT '',
  `table_name` varchar(255) NOT NULL DEFAULT '',
  `table_id` varchar(255) NOT NULL DEFAULT '',
  `table_vertical` varchar(255) NOT NULL DEFAULT '',
  `couchdb` varchar(255) NOT NULL DEFAULT '',
  `document_drive` tinyint(4) NOT NULL DEFAULT '0',
  `mapped` tinyint(4) NOT NULL DEFAULT '0',
  `created` timestamp NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `validate_email`
--

DROP TABLE IF EXISTS `verify_email`;
CREATE TABLE `verify_email` (
  `id` bigint NOT NULL auto_increment,
  `pid_holder` bigint DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `language` varchar(100) DEFAULT NULL,
  `fname` varchar(255) DEFAULT NULL,
  `mname` varchar(255) DEFAULT NULL,
  `lname` varchar(255) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `token_onetime`  VARCHAR(255) DEFAULT NULL,
  `active` tinyint NOT NULL default 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`email`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `voids`
--

DROP TABLE IF EXISTS `voids`;
CREATE TABLE `voids` (
  `void_id`                bigint(20)    NOT NULL AUTO_INCREMENT,
  `patient_id`             bigint(20)    NOT NULL            COMMENT 'references patient_data.pid',
  `encounter_id`           bigint(20)    NOT NULL DEFAULT 0  COMMENT 'references form_encounter.encounter',
  `what_voided`            varchar(31)   NOT NULL            COMMENT 'checkout,receipt and maybe other options later',
  `date_original`          datetime      DEFAULT NULL        COMMENT 'time of original action that is now voided',
  `date_voided`            datetime      NOT NULL            COMMENT 'time of void action',
  `user_id`                bigint(20)    NOT NULL            COMMENT 'references users.id',
  `amount1`                decimal(12,2) NOT NULL DEFAULT 0  COMMENT 'for checkout,receipt total voided adjustments',
  `amount2`                decimal(12,2) NOT NULL DEFAULT 0  COMMENT 'for checkout,receipt total voided payments',
  `other_info`             text                              COMMENT 'for checkout,receipt the old invoice refno',
  `reason`                 VARCHAR(31)   default '',
  `notes`                  VARCHAR(255)  default '',
  PRIMARY KEY (`void_id`),
  KEY datevoided (date_voided),
  KEY pidenc (patient_id, encounter_id)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `x12_partners`
--

DROP TABLE IF EXISTS `x12_partners`;
CREATE TABLE `x12_partners` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(255) default NULL,
  `id_number` varchar(255) default NULL,
  `x12_sender_id` varchar(255) default NULL,
  `x12_receiver_id` varchar(255) default NULL,
  `processing_format` enum('standard','medi-cal','cms','proxymed','oa_eligibility','availity_eligibility') default NULL,
  `x12_isa01` VARCHAR( 2 ) NOT NULL DEFAULT '00' COMMENT 'User logon Required Indicator',
  `x12_isa02` VARCHAR( 10 ) NOT NULL DEFAULT '          ' COMMENT 'User Logon',
  `x12_isa03` VARCHAR( 2 ) NOT NULL DEFAULT '00' COMMENT 'User password required Indicator',
  `x12_isa04` VARCHAR( 10 ) NOT NULL DEFAULT '          ' COMMENT 'User Password',
  `x12_isa05` char(2)     NOT NULL DEFAULT 'ZZ',
  `x12_isa07` char(2)     NOT NULL DEFAULT 'ZZ',
  `x12_isa14` char(1)     NOT NULL DEFAULT '0',
  `x12_isa15` char(1)     NOT NULL DEFAULT 'P',
  `x12_gs02`  varchar(15) NOT NULL DEFAULT '',
  `x12_per06` varchar(80) NOT NULL DEFAULT '',
  `x12_dtp03` char(1)     NOT NULL DEFAULT 'A',
  `x12_gs03` varchar(15) DEFAULT NULL,
  `x12_submitter_id` smallint(6) DEFAULT NULL,
  `x12_submitter_name` varchar(255) DEFAULT NULL,
  `x12_sftp_login` varchar(255) DEFAULT NULL,
  `x12_sftp_pass` varchar(255) DEFAULT NULL,
  `x12_sftp_host` varchar(255) DEFAULT NULL,
  `x12_sftp_port` varchar(255) DEFAULT NULL,
  `x12_sftp_local_dir` varchar(255) DEFAULT NULL,
  `x12_sftp_remote_dir` varchar(255) DEFAULT NULL,
  `x12_token_endpoint` tinytext,
  `x12_eligibility_endpoint` tinytext,
  `x12_claim_status_endpoint` tinytext,
  `x12_attachment_endpoint` tinytext,
  `x12_client_id` tinytext,
  `x12_client_secret` tinytext,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `automatic_notification`
--

DROP TABLE IF EXISTS `automatic_notification`;
CREATE TABLE `automatic_notification` (
  `notification_id` int(5) NOT NULL auto_increment,
  `sms_gateway_type` varchar(255) NOT NULL,
  `provider_name` varchar(100) NOT NULL,
  `message` text,
  `email_sender` varchar(100) NOT NULL,
  `email_subject` varchar(100) NOT NULL,
  `type` enum('SMS','Email') NOT NULL default 'SMS',
  PRIMARY KEY  (`notification_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3;

--
-- Inserting data for table `automatic_notification`
--

-- --------------------------------------------------------

--
-- Table structure for table `notification_log`
--

DROP TABLE IF EXISTS `notification_log`;
CREATE TABLE `notification_log` (
  `iLogId` int(11) NOT NULL auto_increment,
  `pid` bigint(20) NOT NULL,
  `pc_eid` int(11) unsigned NULL,
  `sms_gateway_type` varchar(50) NOT NULL,
  `smsgateway_info` varchar(255) NOT NULL,
  `message` text,
  `email_sender` varchar(255) NOT NULL,
  `email_subject` varchar(255) NOT NULL,
  `type` enum('SMS','Email') NOT NULL,
  `patient_info` text,
  `pc_eventDate` date NOT NULL,
  `pc_endDate` date NOT NULL,
  `pc_startTime` time NOT NULL,
  `pc_endTime` time NOT NULL,
  `dSentDateTime` datetime NOT NULL,
  PRIMARY KEY  (`iLogId`)
) ENGINE=InnoDB AUTO_INCREMENT=5;

-- --------------------------------------------------------

--
-- Table structure for table `notification_settings`
--

DROP TABLE IF EXISTS `notification_settings`;
CREATE TABLE `notification_settings` (
  `SettingsId` int(3) NOT NULL auto_increment,
  `Send_SMS_Before_Hours` int(3) NOT NULL,
  `Send_Email_Before_Hours` int(3) NOT NULL,
  `SMS_gateway_username` varchar(100) NOT NULL,
  `SMS_gateway_password` varchar(100) NOT NULL,
  `SMS_gateway_apikey` varchar(100) NOT NULL,
  `type` varchar(50) NOT NULL,
  PRIMARY KEY  (`SettingsId`)
) ENGINE=InnoDB AUTO_INCREMENT=2;

--
-- Inserting data for table `notification_settings`
--

-- -------------------------------------------------------------------

--
-- Table structure for table `chart_tracker`
--

DROP TABLE IF EXISTS `chart_tracker`;
CREATE TABLE chart_tracker (
  ct_pid            int(11)       NOT NULL,
  ct_when           datetime      NOT NULL,
  ct_userid         bigint(20)    NOT NULL DEFAULT 0,
  ct_location       varchar(31)   NOT NULL DEFAULT '',
  PRIMARY KEY (ct_pid, ct_when)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `ar_session`
--

DROP TABLE IF EXISTS `ar_session`;
CREATE TABLE ar_session (
  session_id     int unsigned  NOT NULL AUTO_INCREMENT,
  payer_id       int(11)       NOT NULL            COMMENT '0=pt else references insurance_companies.id',
  user_id        int(11)       NOT NULL            COMMENT 'references users.id for session owner',
  closed         tinyint(1)    NOT NULL DEFAULT 0  COMMENT '0=no, 1=yes',
  reference      varchar(255)  NOT NULL DEFAULT '' COMMENT 'check or EOB number',
  check_date     date          DEFAULT NULL,
  deposit_date   date          DEFAULT NULL,
  pay_total      decimal(12,2) NOT NULL DEFAULT 0,
  created_time timestamp NOT NULL default CURRENT_TIMESTAMP,
  modified_time datetime NOT NULL,
  global_amount decimal( 12, 2 ) NOT NULL ,
  payment_type varchar( 50 ) NOT NULL ,
  description text,
  adjustment_code varchar( 50 ) NOT NULL ,
  post_to_date date NOT NULL ,
  patient_id bigint(20) NOT NULL,
  payment_method varchar( 25 ) NOT NULL,
  PRIMARY KEY (session_id),
  KEY user_closed (user_id, closed),
  KEY deposit_date (deposit_date)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `ar_activity`
--

DROP TABLE IF EXISTS `ar_activity`;
CREATE TABLE ar_activity (
  pid            int(11)       NOT NULL,
  encounter      int(11)       NOT NULL,
  sequence_no    int unsigned  NOT NULL            COMMENT 'Ar_activity sequence_no, incremented in code',
  `code_type`    varchar(12)   NOT NULL DEFAULT '',
  code           varchar(20)   NOT NULL            COMMENT 'empty means claim level',
  modifier       varchar(12)   NOT NULL DEFAULT '',
  payer_type     int           NOT NULL            COMMENT '0=pt, 1=ins1, 2=ins2, etc',
  post_time      datetime      NOT NULL,
  post_user      int(11)       NOT NULL            COMMENT 'references users.id',
  session_id     int unsigned  NOT NULL            COMMENT 'references ar_session.session_id',
  memo           varchar(255)  NOT NULL DEFAULT '' COMMENT 'adjustment reasons go here',
  pay_amount     decimal(12,2) NOT NULL DEFAULT 0  COMMENT 'either pay or adj will always be 0',
  adj_amount     decimal(12,2) NOT NULL DEFAULT 0,
  modified_time datetime NOT NULL,
  follow_up char(1) NOT NULL,
  follow_up_note text,
  account_code varchar(15) NOT NULL,
  reason_code varchar(255) DEFAULT NULL COMMENT 'Use as needed to show the primary payer adjustment reason code',
  deleted        datetime DEFAULT NULL COMMENT 'NULL if active, otherwise when voided',
  post_date      date DEFAULT NULL COMMENT 'Posting date if specified at payment time',
  payer_claim_number varchar(30) DEFAULT NULL,
  PRIMARY KEY (pid, encounter, sequence_no),
  KEY session_id (session_id)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `users_facility`
--

DROP TABLE IF EXISTS `users_facility`;
CREATE TABLE `users_facility` (
  `tablename` varchar(64) NOT NULL,
  `table_id` int(11) NOT NULL,
  `facility_id` int(11) NOT NULL,
  `warehouse_id` varchar(31) NOT NULL DEFAULT '',
  PRIMARY KEY (`tablename`,`table_id`,`facility_id`,`warehouse_id`)
) ENGINE=InnoDB COMMENT='joins users or patient_data to facility table';

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `lbf_data`
--

DROP TABLE IF EXISTS `lbf_data`;
CREATE TABLE `lbf_data` (
  `form_id`     int(11)      NOT NULL AUTO_INCREMENT COMMENT 'references forms.form_id',
  `field_id`    varchar(31)  NOT NULL COMMENT 'references layout_options.field_id',
  `field_value` LONGTEXT,
  PRIMARY KEY (`form_id`,`field_id`)
) ENGINE=InnoDB COMMENT='contains all data from layout-based forms';

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `lbt_data`
--

DROP TABLE IF EXISTS `lbt_data`;
CREATE TABLE `lbt_data` (
  `form_id`     bigint(20)   NOT NULL COMMENT 'references transactions.id',
  `field_id`    varchar(31)  NOT NULL COMMENT 'references layout_options.field_id',
  `field_value` TEXT,
  PRIMARY KEY (`form_id`,`field_id`)
) ENGINE=InnoDB COMMENT='contains all data from layout-based transactions';

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `gprelations`
--

DROP TABLE IF EXISTS `gprelations`;
CREATE TABLE gprelations (
  type1 int(2)     NOT NULL,
  id1   bigint(20) NOT NULL,
  type2 int(2)     NOT NULL,
  id2   bigint(20) NOT NULL,
  PRIMARY KEY (type1,id1,type2,id2),
  KEY key2  (type2,id2)
) ENGINE=InnoDB COMMENT='general purpose relations';

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `procedure_providers`
--

DROP TABLE IF EXISTS `procedure_providers`;
CREATE TABLE `procedure_providers` (
  `ppid`         bigint(20)   NOT NULL auto_increment,
  `uuid`         binary(16)   DEFAULT NULL,
  `name`         varchar(255) NOT NULL DEFAULT '',
  `npi`          varchar(15)  NOT NULL DEFAULT '',
  `send_app_id`  varchar(255) NOT NULL DEFAULT ''  COMMENT 'Sending application ID (MSH-3.1)',
  `send_fac_id`  varchar(255) NOT NULL DEFAULT ''  COMMENT 'Sending facility ID (MSH-4.1)',
  `recv_app_id`  varchar(255) NOT NULL DEFAULT ''  COMMENT 'Receiving application ID (MSH-5.1)',
  `recv_fac_id`  varchar(255) NOT NULL DEFAULT ''  COMMENT 'Receiving facility ID (MSH-6.1)',
  `DorP`         char(1)      NOT NULL DEFAULT 'D' COMMENT 'Debugging or Production (MSH-11)',
  `direction`    char(1)      NOT NULL DEFAULT 'B' COMMENT 'Bidirectional or Results-only',
  `protocol`     varchar(15)  NOT NULL DEFAULT 'DL',
  `remote_host`  varchar(255) NOT NULL DEFAULT '',
  `login`        varchar(255) NOT NULL DEFAULT '',
  `password`     varchar(255) NOT NULL DEFAULT '',
  `orders_path`  varchar(255) NOT NULL DEFAULT '',
  `results_path` varchar(255) NOT NULL DEFAULT '',
  `notes`        text,
  `lab_director` bigint(20)   NOT NULL DEFAULT '0',
  `active`       tinyint(1)   NOT NULL DEFAULT '1',
  `type`         varchar(31)  DEFAULT NULL,
  `date_created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ppid`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `procedure_type`
--

DROP TABLE IF EXISTS `procedure_type`;
CREATE TABLE `procedure_type` (
  `procedure_type_id`   bigint(20)   NOT NULL AUTO_INCREMENT,
  `parent`              bigint(20)   NOT NULL DEFAULT 0  COMMENT 'references procedure_type.procedure_type_id',
  `name`                varchar(63)  NOT NULL DEFAULT '' COMMENT 'name for this category, procedure or result type',
  `lab_id`              bigint(20)   NOT NULL DEFAULT 0  COMMENT 'references procedure_providers.ppid, 0 means default to parent',
  `procedure_code`      varchar(64)  NOT NULL DEFAULT '' COMMENT 'code identifying this procedure',
  `procedure_type`      varchar(31)  NOT NULL DEFAULT '' COMMENT 'see list proc_type',
  `body_site`           varchar(31)  NOT NULL DEFAULT '' COMMENT 'where to do injection, e.g. arm, buttock',
  `specimen`            varchar(31)  NOT NULL DEFAULT '' COMMENT 'blood, urine, saliva, etc.',
  `route_admin`         varchar(31)  NOT NULL DEFAULT '' COMMENT 'oral, injection',
  `laterality`          varchar(31)  NOT NULL DEFAULT '' COMMENT 'left, right, ...',
  `description`         varchar(255) NOT NULL DEFAULT '' COMMENT 'descriptive text for procedure_code',
  `standard_code`       varchar(255) NOT NULL DEFAULT '' COMMENT 'industry standard code type and code (e.g. CPT4:12345)',
  `related_code`        varchar(255) NOT NULL DEFAULT '' COMMENT 'suggested code(s) for followup services if result is abnormal',
  `units`               varchar(31)  NOT NULL DEFAULT '' COMMENT 'default for procedure_result.units',
  `range`               varchar(255) NOT NULL DEFAULT '' COMMENT 'default for procedure_result.range',
  `seq`                 int(11)      NOT NULL default 0  COMMENT 'sequence number for ordering',
  `activity`            tinyint(1)   NOT NULL default 1  COMMENT '1=active, 0=inactive',
  `notes`               varchar(255) NOT NULL default '' COMMENT 'additional notes to enhance description',
  `transport`           varchar(31)  DEFAULT NULL,
  `procedure_type_name` varchar(64)  NULL,
  PRIMARY KEY (`procedure_type_id`),
  KEY parent (parent),
  KEY `ptype_procedure_code` (`procedure_code`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `procedure_questions`
--

DROP TABLE IF EXISTS `procedure_questions`;
CREATE TABLE `procedure_questions` (
  `lab_id`              bigint(20)   NOT NULL DEFAULT 0   COMMENT 'references procedure_providers.ppid to identify the lab',
  `procedure_code`      varchar(31)  NOT NULL DEFAULT ''  COMMENT 'references procedure_type.procedure_code to identify this order type',
  `question_code`       varchar(31)  NOT NULL DEFAULT ''  COMMENT 'code identifying this question',
  `seq`                 int(11)      NOT NULL default 0   COMMENT 'sequence number for ordering',
  `question_text`       varchar(255) NOT NULL DEFAULT ''  COMMENT 'descriptive text for question_code',
  `required`            tinyint(1)   NOT NULL DEFAULT 0   COMMENT '1 = required, 0 = not',
  `maxsize`             int          NOT NULL DEFAULT 0   COMMENT 'maximum length if text input field',
  `fldtype`             char(1)      NOT NULL DEFAULT 'T' COMMENT 'Text, Number, Select, Multiselect, Date, Gestational-age',
  `options`             text                              COMMENT 'choices for fldtype S and T',
  `tips`                varchar(255) NOT NULL DEFAULT ''  COMMENT 'Additional instructions for answering the question',
  `activity`            tinyint(1)   NOT NULL DEFAULT 1   COMMENT '1 = active, 0 = inactive',
  PRIMARY KEY (`lab_id`, `procedure_code`, `question_code`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `procedure_order`
--

DROP TABLE IF EXISTS `procedure_order`;
CREATE TABLE `procedure_order` (
  `procedure_order_id`     bigint(20)       NOT NULL AUTO_INCREMENT,
  `uuid`                   binary(16)       DEFAULT NULL,
  `provider_id`            bigint(20)       NOT NULL DEFAULT 0  COMMENT 'references users.id, the ordering provider',
  `patient_id`             bigint(20)       NOT NULL            COMMENT 'references patient_data.pid',
  `encounter_id`           bigint(20)       NOT NULL DEFAULT 0  COMMENT 'references form_encounter.encounter',
  `date_collected`         datetime         DEFAULT NULL        COMMENT 'time specimen collected',
  `date_ordered`           datetime         DEFAULT NULL,
  `order_priority`         varchar(31)      NOT NULL DEFAULT '',
  `order_status`           varchar(31)      NOT NULL DEFAULT '' COMMENT 'pending,routed,complete,canceled',
  `patient_instructions`   text,
  `activity`               tinyint(1)       NOT NULL DEFAULT 1  COMMENT '0 if deleted',
  `control_id`             varchar(255)     NOT NULL DEFAULT '' COMMENT 'This is the CONTROL ID that is sent back from lab',
  `lab_id`                 bigint(20)       NOT NULL DEFAULT 0  COMMENT 'references procedure_providers.ppid',
  `specimen_type`          varchar(31)      NOT NULL DEFAULT '' COMMENT 'from the Specimen_Type list',
  `specimen_location`      varchar(31)      NOT NULL DEFAULT '' COMMENT 'from the Specimen_Location list',
  `specimen_volume`        varchar(30)      NOT NULL DEFAULT '' COMMENT 'from a text input field',
  `date_transmitted`       datetime         DEFAULT NULL        COMMENT 'time of order transmission, null if unsent',
  `clinical_hx`            varchar(255)     NOT NULL DEFAULT '' COMMENT 'clinical history text that may be relevant to the order',
  `external_id`            varchar(20)      DEFAULT NULL,
  `history_order`          enum('0','1')    DEFAULT '0'         COMMENT 'references order is added for history purpose only.',
  `order_diagnosis`        varchar(255)     DEFAULT ''          COMMENT 'primary order diagnosis',
  `billing_type`           varchar(4)       DEFAULT NULL,
  `specimen_fasting`       varchar(31)      DEFAULT NULL,
  `order_psc`              tinyint(4)       DEFAULT NULL,
  `order_abn`              varchar(31)      NOT NULL DEFAULT 'not_required',
  `collector_id`           bigint(11)       NOT NULL DEFAULT 0,
  `account`                varchar(60)      DEFAULT NULL,
  `account_facility`       int(11)          DEFAULT NULL,
  `provider_number`        varchar(30)      DEFAULT NULL,
  `procedure_order_type`   varchar(32)      NOT NULL DEFAULT 'laboratory_test',
  `scheduled_date` datetime DEFAULT NULL COMMENT 'Scheduled date for service (FHIR occurrence[x])',
  `scheduled_start` datetime DEFAULT NULL COMMENT 'Scheduled start time (FHIR occurrencePeriod.start)',
  `scheduled_end` datetime DEFAULT NULL COMMENT 'Scheduled end time (FHIR occurrencePeriod.end)',
  `performer_type` varchar(50) DEFAULT NULL COMMENT 'Type of performer: laboratory, radiology, pathology (SNOMED CT)',
  `order_intent` varchar(31) NOT NULL DEFAULT 'order' COMMENT 'FHIR intent: order, plan, directive, proposal',
  `location_id` int(11) DEFAULT NULL COMMENT 'References facility.id for service location (FHIR locationReference)',
  PRIMARY KEY (`procedure_order_id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `datepid` (`date_ordered`,`patient_id`),
  KEY `patient_id` (`patient_id`),
  KEY `idx_specimen_type` (`specimen_type`),
  KEY `idx_scheduled_date` (`scheduled_date`),
  KEY `idx_order_intent` (`order_intent`),
  KEY `idx_location_id` (`location_id`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `procedure_order_code`
--

DROP TABLE IF EXISTS `procedure_order_code`;
CREATE TABLE `procedure_order_code` (
  `procedure_order_id`      bigint(20)  NOT NULL                COMMENT 'references procedure_order.procedure_order_id',
  `procedure_order_seq`     int(11)     NOT NULL COMMENT 'Supports multiple tests per order. Procedure_order_seq, incremented in code',
  `procedure_code`          varchar(64) NOT NULL DEFAULT ''     COMMENT 'like procedure_type.procedure_code',
  `procedure_name`          varchar(255) NOT NULL DEFAULT ''    COMMENT 'descriptive name of the procedure code',
  `procedure_source`        char(1)     NOT NULL DEFAULT '1'    COMMENT '1=original order, 2=added after order sent',
  `diagnoses`               text                                COMMENT 'diagnoses and maybe other coding (e.g. ICD9:111.11)',
  `do_not_send`             tinyint(1)  NOT NULL DEFAULT '0'    COMMENT '0 = normal, 1 = do not transmit to lab',
  `procedure_order_title`   varchar( 255 ) NULL DEFAULT NULL,
  `procedure_type`          varchar(31) DEFAULT NULL,
  `transport`               varchar(31) DEFAULT NULL,
  `date_end` datetime DEFAULT NULL,
  `reason_code` varchar(31) DEFAULT NULL,
  `reason_description` text,
  `reason_date_low` datetime DEFAULT NULL,
  `reason_date_high` datetime DEFAULT NULL,
  `reason_status` varchar(31) DEFAULT NULL,
  PRIMARY KEY (`procedure_order_id`, `procedure_order_seq`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `procedure_answers`
--

DROP TABLE IF EXISTS `procedure_answers`;
CREATE TABLE `procedure_answers` (
  `procedure_order_id`  bigint(20)   NOT NULL DEFAULT 0  COMMENT 'references procedure_order.procedure_order_id',
  `procedure_order_seq` int(11)      NOT NULL DEFAULT 0  COMMENT 'references procedure_order_code.procedure_order_seq',
  `question_code`       varchar(31)  NOT NULL DEFAULT '' COMMENT 'references procedure_questions.question_code',
  `answer_seq`          int(11)      NOT NULL COMMENT 'supports multiple-choice questions. answer_seq, incremented in code',
  `answer`              varchar(255) NOT NULL DEFAULT '' COMMENT 'answer data',
  `procedure_code`      varchar(31)  DEFAULT NULL,
  PRIMARY KEY (`procedure_order_id`, `procedure_order_seq`, `question_code`, `answer_seq`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `procedure_report`
--

DROP TABLE IF EXISTS `procedure_report`;
CREATE TABLE `procedure_report` (
  `procedure_report_id` bigint(20)     NOT NULL AUTO_INCREMENT,
  `uuid`                binary(16)     DEFAULT NULL,
  `procedure_order_id`  bigint(20)     DEFAULT NULL   COMMENT 'references procedure_order.procedure_order_id',
  `procedure_order_seq` int(11)        NOT NULL DEFAULT 1  COMMENT 'references procedure_order_code.procedure_order_seq',
  `date_collected`      datetime       DEFAULT NULL,
  `date_collected_tz`   varchar(5)     DEFAULT ''          COMMENT '+-hhmm offset from UTC',
  `date_report`         datetime       DEFAULT NULL,
  `date_report_tz`      varchar(5)     DEFAULT ''          COMMENT '+-hhmm offset from UTC',
  `source`              bigint(20)     NOT NULL DEFAULT 0  COMMENT 'references users.id, who entered this data',
  `specimen_num`        varchar(63)    NOT NULL DEFAULT '',
  `report_status`       varchar(31)    NOT NULL DEFAULT '' COMMENT 'received,complete,error',
  `review_status`       varchar(31)    NOT NULL DEFAULT 'received' COMMENT 'pending review status: received,reviewed',
  `report_notes`        text           COMMENT 'notes from the lab',
  PRIMARY KEY (`procedure_report_id`),
  KEY procedure_order_id (procedure_order_id),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `procedure_result`
--

DROP TABLE IF EXISTS `procedure_result`;
CREATE TABLE `procedure_result` (
  `procedure_result_id` bigint(20)   NOT NULL AUTO_INCREMENT,
  `uuid`                binary(16)   DEFAULT NULL,
  `procedure_report_id` bigint(20)   NOT NULL            COMMENT 'references procedure_report.procedure_report_id',
  `result_data_type`    char(1)      NOT NULL DEFAULT 'S' COMMENT 'N=Numeric, S=String, F=Formatted, E=External, L=Long text as first line of comments',
  `result_code`         varchar(31)  NOT NULL DEFAULT '' COMMENT 'LOINC code, might match a procedure_type.procedure_code',
  `result_text`         varchar(255) NOT NULL DEFAULT '' COMMENT 'Description of result_code',
  `date`                datetime     DEFAULT NULL        COMMENT 'lab-provided date specific to this result',
  `facility`            varchar(255) NOT NULL DEFAULT '' COMMENT 'lab-provided testing facility ID',
  `units`               varchar(31)  NOT NULL DEFAULT '',
  `result`              varchar(255) NOT NULL DEFAULT '',
  `range`               varchar(255) NOT NULL DEFAULT '',
  `abnormal`            varchar(31)  NOT NULL DEFAULT '' COMMENT 'no,yes,high,low',
  `comments`            text                             COMMENT 'comments from the lab',
  `document_id`         bigint(20)   NOT NULL DEFAULT 0  COMMENT 'references documents.id if this result is a document',
  `result_status`       varchar(31)  NOT NULL DEFAULT '' COMMENT 'preliminary, cannot be done, final, corrected, incomplete...etc.',
  `date_end`            datetime     DEFAULT NULL        COMMENT 'lab-provided end date specific to this result',
  PRIMARY KEY (`procedure_result_id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY procedure_report_id (procedure_report_id)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `procedure_specimen`
--

DROP TABLE IF EXISTS `procedure_specimen`;
CREATE TABLE `procedure_specimen` (
  `procedure_specimen_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'record id',
  `uuid` binary(16) DEFAULT NULL COMMENT 'FHIR Specimen id',
  `procedure_order_id` BIGINT(20) NOT NULL COMMENT 'links to procedure_order.procedure_order_id',
  `procedure_order_seq` INT(11) NOT NULL COMMENT 'links to procedure_order_code.procedure_order_seq (per test line)',
  `specimen_identifier` VARCHAR(128) DEFAULT NULL COMMENT 'tube/barcode/internal id',
  `accession_identifier` VARCHAR(128) DEFAULT NULL COMMENT 'lab accession number',
  `specimen_type_code` VARCHAR(64) DEFAULT NULL COMMENT 'prefer SNOMED CT code',
  `specimen_type` VARCHAR(255) DEFAULT NULL COMMENT 'display/text',
  `collection_method_code` VARCHAR(64) DEFAULT NULL,
  `collection_method` VARCHAR(255) DEFAULT NULL,
  `specimen_location_code` VARCHAR(64) DEFAULT NULL,
  `specimen_location` VARCHAR(255) DEFAULT NULL,
  `collected_date` DATETIME DEFAULT NULL COMMENT 'single instant',
  `collection_date_low` DATETIME DEFAULT NULL COMMENT 'period start',
  `collection_date_high` DATETIME DEFAULT NULL COMMENT 'period end',
  `volume_value` DECIMAL(10,3) DEFAULT NULL,
  `volume_unit` VARCHAR(32) DEFAULT 'mL',
  `condition_code` VARCHAR(32) DEFAULT NULL COMMENT 'HL7 v2 0493 (e.g., ACT, HEM)',
  `specimen_condition` VARCHAR(64) DEFAULT NULL,
  `comments` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` BIGINT(20) DEFAULT NULL,
  `updated_by` BIGINT(20) DEFAULT NULL,
   `deleted` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`procedure_specimen_id`),
  UNIQUE KEY `uuid_unique` (`uuid`),
  KEY `idx_order_line` (`procedure_order_id`,`procedure_order_seq`),
  KEY `idx_identifier` (`specimen_identifier`),
  KEY `idx_accession` (`accession_identifier`)
) ENGINE=InnoDB;

-- ------------------------------------------------------------------------

--
-- Table structure for table `procedure_order_relationships`
--

DROP TABLE IF EXISTS `procedure_order_relationships`;
CREATE TABLE `procedure_order_relationships` (
 `id` INT AUTO_INCREMENT PRIMARY KEY,
 `procedure_order_id` BIGINT(20) NOT NULL COMMENT 'Links to procedure_order.procedure_order_id',
 `resource_type` VARCHAR(50) NOT NULL COMMENT 'FHIR resource type (Observation, Condition, etc.)',
 `resource_uuid` BINARY(16) NOT NULL COMMENT 'UUID of the related resource',
 `relationship` VARCHAR(50) DEFAULT NULL COMMENT 'Type of relationship',
 `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 `created_by` BIGINT(20) DEFAULT NULL COMMENT 'User who created this link',
 INDEX `idx_order_id` (`procedure_order_id`),
 INDEX `idx_resource` (`resource_type`, `resource_uuid`),
 INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB COMMENT='Links ServiceRequests to supporting clinical information';

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `globals`
--

DROP TABLE IF EXISTS `globals`;
CREATE TABLE `globals` (
  `gl_name`             varchar(63)    NOT NULL,
  `gl_index`            int(11)        NOT NULL DEFAULT 0,
  `gl_value`            varchar(255)   NOT NULL DEFAULT '',
  PRIMARY KEY (`gl_name`, `gl_index`)
) ENGINE=InnoDB;

-- -----------------------------------------------------------------------------------

--
-- Table structure for table `code_types`
--

DROP TABLE IF EXISTS `code_types`;
CREATE TABLE code_types (
  ct_key  varchar(15) NOT NULL           COMMENT 'short alphanumeric name',
  ct_id   int(11)     UNIQUE NOT NULL    COMMENT 'numeric identifier',
  ct_seq  int(11)     NOT NULL DEFAULT 0 COMMENT 'sort order',
  ct_mod  int(11)     NOT NULL DEFAULT 0 COMMENT 'length of modifier field',
  ct_just varchar(15) NOT NULL DEFAULT ''COMMENT 'ct_key of justify type, if any',
  ct_mask varchar(9)  NOT NULL DEFAULT ''COMMENT 'formatting mask for code values',
  ct_fee  tinyint(1)  NOT NULL default 0 COMMENT '1 if fees are used',
  ct_rel  tinyint(1)  NOT NULL default 0 COMMENT '1 if can relate to other code types',
  ct_nofs tinyint(1)  NOT NULL default 0 COMMENT '1 if to be hidden in the fee sheet',
  ct_diag tinyint(1)  NOT NULL default 0 COMMENT '1 if this is a diagnosis type',
  ct_active tinyint(1) NOT NULL default 1 COMMENT '1 if this is active',
  ct_label varchar(31) NOT NULL default '' COMMENT 'label of this code type',
  ct_external tinyint(1) NOT NULL default 0 COMMENT '0 if stored codes in codes tables, 1 or greater if codes stored in external tables',
  ct_claim tinyint(1) NOT NULL default 0 COMMENT '1 if this is used in claims',
  ct_proc tinyint(1) NOT NULL default 0 COMMENT '1 if this is a procedure type',
  ct_term tinyint(1) NOT NULL default 0 COMMENT '1 if this is a clinical term',
  ct_problem tinyint(1) NOT NULL default 0 COMMENT '1 if this code type is used as a medical problem',
  ct_drug tinyint(1) NOT NULL default 0 COMMENT '1 if this code type is used as a medication',
  PRIMARY KEY (ct_key)
) ENGINE=InnoDB;

-- Race List
-- Ethnicity List
-- void reasons list

-- payment methods list

-- shift list

-- Charge categories list (Customers), used by IPPF checkout

-- list_options for `form_eye`

-- we leave in the old one in case people are still referring to it but we deactivate it
-- Insert Medication Usage Intent
-- Insert Medication Request Intent
-- Insert 2021 eCQM Reporting Measures
-- Insert encounter_type list
-- --------------------------------------------------------

--
-- Table structure for table `extended_log`
--

DROP TABLE IF EXISTS `extended_log`;
CREATE TABLE `extended_log` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime default NULL,
  `event` varchar(255) default NULL,
  `user` varchar(255) default NULL,
  `recipient` varchar(255) default NULL,
  `description` longtext,
  `patient_id` bigint(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `patient_id` (`patient_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `version`
--

DROP TABLE IF EXISTS `version`;
CREATE TABLE version (
  v_major    int(11)     NOT NULL DEFAULT 0,
  v_minor    int(11)     NOT NULL DEFAULT 0,
  v_patch    int(11)     NOT NULL DEFAULT 0,
  v_realpatch int(11)    NOT NULL DEFAULT 0,
  v_tag      varchar(31) NOT NULL DEFAULT '',
  v_database int(11)     NOT NULL DEFAULT 0,
  v_acl      int(11)     NOT NULL DEFAULT 0
) ENGINE=InnoDB;

--
-- Inserting data for table `version`
--

-- --------------------------------------------------------

--
-- Table structure for table `customlists`
--

DROP TABLE IF EXISTS `customlists`;
CREATE TABLE `customlists` (
  `cl_list_slno` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cl_list_id` int(10) unsigned NOT NULL COMMENT 'ID OF THE lIST FOR NEW TAKE SELECT MAX(cl_list_id)+1',
  `cl_list_item_id` int(10) unsigned DEFAULT NULL COMMENT 'ID OF THE lIST FOR NEW TAKE SELECT MAX(cl_list_item_id)+1',
  `cl_list_type` int(10) unsigned NOT NULL COMMENT '0=>List Name 1=>list items 2=>Context 3=>Template 4=>Sentence 5=> SavedTemplate 6=>CustomButton',
  `cl_list_item_short` varchar(10) DEFAULT NULL,
  `cl_list_item_long` text,
  `cl_list_item_level` int(11) DEFAULT NULL COMMENT 'Flow level for List Designation',
  `cl_order` int(11) DEFAULT NULL,
  `cl_deleted` tinyint(1) DEFAULT '0',
  `cl_creator` int(11) DEFAULT NULL,
  PRIMARY KEY (`cl_list_slno`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

--
-- Inserting data for table `customlists`
--

-- --------------------------------------------------------

--
-- Table structure for table `template_users`
--

DROP TABLE IF EXISTS `template_users`;
CREATE TABLE `template_users` (
  `tu_id` int(11) NOT NULL AUTO_INCREMENT,
  `tu_user_id` int(11) DEFAULT NULL,
  `tu_facility_id` int(11) DEFAULT NULL,
  `tu_template_id` int(11) DEFAULT NULL,
  `tu_template_order` int(11) DEFAULT NULL,
  PRIMARY KEY (`tu_id`),
  UNIQUE KEY `templateuser` (`tu_user_id`,`tu_template_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `product_warehouse`
--

DROP TABLE IF EXISTS `product_warehouse`;
CREATE TABLE `product_warehouse` (
  `pw_drug_id`   int(11) NOT NULL,
  `pw_warehouse` varchar(31) NOT NULL,
  `pw_min_level` float       DEFAULT 0,
  `pw_max_level` float       DEFAULT 0,
  PRIMARY KEY  (`pw_drug_id`,`pw_warehouse`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `misc_address_book`
--

DROP TABLE IF EXISTS `misc_address_book`;
CREATE TABLE `misc_address_book` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `fname` varchar(255) DEFAULT NULL,
  `mname` varchar(255) DEFAULT NULL,
  `lname` varchar(255) DEFAULT NULL,
  `street` varchar(60) DEFAULT NULL,
  `city` varchar(30) DEFAULT NULL,
  `state` varchar(30) DEFAULT NULL,
  `zip` varchar(20) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `esign_signatures`
--

DROP TABLE IF EXISTS `esign_signatures`;
CREATE TABLE `esign_signatures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tid` int(11) NOT NULL COMMENT 'Table row ID for signature',
  `table` varchar(255) NOT NULL COMMENT 'table name for the signature',
  `uid` int(11) NOT NULL COMMENT 'user id for the signing user',
  `datetime` datetime NOT NULL COMMENT 'datetime of the signature action',
  `is_lock` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sig, lock or amendment',
  `amendment` text COMMENT 'amendment text, if any',
  `hash` varchar(255) NOT NULL COMMENT 'hash of signed data',
  `signature_hash` varchar(255) NOT NULL COMMENT 'hash of signature itself',
  PRIMARY KEY (`id`),
  KEY `tid` (`tid`),
  KEY `table` (`table`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `log_comment_encrypt`
--

DROP TABLE IF EXISTS `log_comment_encrypt`;
CREATE TABLE `log_comment_encrypt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log_id` int(11) NOT NULL,
  `encrypt` enum('Yes','No') NOT NULL DEFAULT 'No',
  `checksum` longtext,
  `checksum_api` longtext,
  `version` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 for mycrypt and 1 for openssl',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `shared_attributes`
--

DROP TABLE IF EXISTS `shared_attributes`;
CREATE TABLE `shared_attributes` (
  `pid`          bigint(20)   NOT NULL,
  `encounter`    bigint(20)   NOT NULL COMMENT '0 if patient attribute, else encounter attribute',
  `field_id`     varchar(31)  NOT NULL COMMENT 'references layout_options.field_id',
  `last_update`  datetime     NOT NULL COMMENT 'time of last update',
  `user_id`      bigint(20)   NOT NULL COMMENT 'user who last updated',
  `field_value`  TEXT,
  PRIMARY KEY (`pid`, `encounter`, `field_id`)
);

-- --------------------------------------------------------

--
-- Table structure for table `ccda_components`
--

DROP TABLE IF EXISTS `ccda_components`;
CREATE TABLE ccda_components (
  ccda_components_id int(11) NOT NULL AUTO_INCREMENT,
  ccda_components_field varchar(100) DEFAULT NULL,
  ccda_components_name varchar(100) DEFAULT NULL,
  ccda_type int(11) NOT NULL COMMENT '0=>sections,1=>components',
  PRIMARY KEY (ccda_components_id)
) ENGINE=InnoDB AUTO_INCREMENT=23;

--
-- Inserting data for table `ccda_components`
--

-- --------------------------------------------------------

--
-- Table structure for table `ccda_sections`
--

DROP TABLE IF EXISTS `ccda_sections`;
CREATE TABLE ccda_sections (
  ccda_sections_id int(11) NOT NULL AUTO_INCREMENT,
  ccda_components_id int(11) DEFAULT NULL,
  ccda_sections_field varchar(100) DEFAULT NULL,
  ccda_sections_name varchar(100) DEFAULT NULL,
  ccda_sections_req_mapping tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (ccda_sections_id)
) ENGINE=InnoDB AUTO_INCREMENT=46;

--
-- Inserting data for table `ccda_sections`
--

-- --------------------------------------------------------

--
-- Table structure for table `ccda_field_mapping`
--

DROP TABLE IF EXISTS `ccda_field_mapping`;
CREATE TABLE ccda_field_mapping (
  id int(11) NOT NULL AUTO_INCREMENT,
  table_id int(11) DEFAULT NULL,
  ccda_field varchar(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `ccda`
--

DROP TABLE IF EXISTS `ccda`;
CREATE TABLE `ccda` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `uuid` binary(16) DEFAULT NULL,
  `pid` BIGINT(20) DEFAULT NULL,
  `encounter` BIGINT(20) DEFAULT NULL,
  `ccda_data` LONGTEXT,
  `time` VARCHAR(50) DEFAULT NULL,
  `status` SMALLINT(6) DEFAULT NULL,
  `updated_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` VARCHAR(50) null,
  `couch_docid` VARCHAR(100) NULL,
  `couch_revid` VARCHAR(100) NULL,
  `hash` varchar(255) DEFAULT NULL,
  `view` tinyint(4) NOT NULL DEFAULT '0',
  `transfer` tinyint(4) NOT NULL DEFAULT '0',
  `emr_transfer` tinyint(4) NOT NULL DEFAULT '0',
  `encrypted` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0->No,1->Yes',
  `transaction_id` BIGINT(20) COMMENT 'fk to transaction referral record',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `unique_key` (`pid`,`encounter`,`time`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `ccda_table_mapping`
--

DROP TABLE IF EXISTS `ccda_table_mapping`;
CREATE TABLE ccda_table_mapping (
  id int(11) NOT NULL AUTO_INCREMENT,
  ccda_component varchar(100) DEFAULT NULL,
  ccda_component_section varchar(100) DEFAULT NULL,
  form_dir varchar(100) DEFAULT NULL,
  form_type smallint(6) DEFAULT NULL,
  form_table varchar(100) DEFAULT NULL,
  user_id int(11) DEFAULT NULL,
  deleted tinyint(4) NOT NULL DEFAULT '0',
  timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `external_procedures`
--

DROP TABLE IF EXISTS `external_procedures`;
CREATE TABLE `external_procedures` (
  `ep_id` int(11) NOT NULL AUTO_INCREMENT,
  `ep_date` date DEFAULT NULL,
  `ep_code_type` varchar(20) DEFAULT NULL,
  `ep_code` varchar(9) DEFAULT NULL,
  `ep_pid` int(11) DEFAULT NULL,
  `ep_encounter` int(11) DEFAULT NULL,
  `ep_code_text` longtext,
  `ep_facility_id` varchar(255) DEFAULT NULL,
  `ep_external_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ep_id`),
  KEY `ep_pid` (`ep_pid`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `external_encounters`
--

DROP TABLE IF EXISTS `external_encounters`;
CREATE TABLE `external_encounters` (
  `ee_id` int(11) NOT NULL AUTO_INCREMENT,
  `ee_date` date DEFAULT NULL,
  `ee_pid` int(11) DEFAULT NULL,
  `ee_provider_id` varchar(255) DEFAULT NULL,
  `ee_facility_id` varchar(255) DEFAULT NULL,
  `ee_encounter_diagnosis` varchar(255) DEFAULT NULL,
  `ee_external_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ee_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_care_plan`
--

DROP TABLE IF EXISTS `form_care_plan`;
CREATE TABLE `form_care_plan` (
  `id` bigint(20) NOT NULL,
  `date` datetime DEFAULT NULL,
  `pid` bigint(20) DEFAULT NULL,
  `encounter` varchar(255) DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `groupname` varchar(255) DEFAULT NULL,
  `authorized` tinyint(4) DEFAULT NULL,
  `activity` tinyint(4) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  `codetext` text,
  `description` text,
  `external_id` varchar(30) DEFAULT NULL,
  `care_plan_type` varchar(30) DEFAULT NULL,
  `note_related_to` text,
  `date_end` datetime DEFAULT NULL,
  `reason_code` varchar(31) DEFAULT NULL,
  `reason_description` text,
  `reason_date_low` datetime DEFAULT NULL COMMENT 'The date the reason was recorded',
  `reason_date_high` datetime DEFAULT NULL COMMENT 'The date the explanation reason for the care plan entry value ends',
  `reason_status` varchar(31) DEFAULT NULL,
  `plan_status` varchar(32) DEFAULT NULL COMMENT 'Care Plan status (e.g., draft, active, completed, etc)',
  `proposed_date` DATETIME NULL COMMENT 'Target or Achieve-by date for the goal',
  KEY `idx_status_date` (`plan_status`,`date`,`date_end`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_functional_cognitive_status`
--

DROP TABLE IF EXISTS `form_functional_cognitive_status`;
CREATE TABLE `form_functional_cognitive_status` (
  `id` bigint(20) NOT NULL,
  `date` date DEFAULT NULL,
  `pid` bigint(20) DEFAULT NULL,
  `encounter` varchar(255) DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `groupname` varchar(255) DEFAULT NULL,
  `authorized` tinyint(4) DEFAULT NULL,
  `activity` tinyint(4) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  `codetext` text,
  `description` text,
  `external_id` varchar(30) DEFAULT NULL
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_observation`
--

DROP TABLE IF EXISTS `form_observation`;
CREATE TABLE `form_observation` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `uuid` binary(16) DEFAULT NULL COMMENT 'UUID for the observation, used as unique logical identifier',
   `form_id` bigint(20) NOT NULL COMMENT 'FK to forms.form_id',
  `date` DATETIME DEFAULT NULL,
  `pid` bigint(20) DEFAULT NULL,
  `encounter` varchar(255) DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `groupname` varchar(255) DEFAULT NULL,
  `authorized` tinyint(4) DEFAULT NULL,
  `activity` tinyint(4) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  `observation` varchar(255) DEFAULT NULL,
  `ob_value` varchar(255),
  `ob_unit` varchar(255),
  `description` varchar(255),
  `code_type` varchar(255),
  `table_code` varchar(255),
  `ob_code` VARCHAR(64) DEFAULT NULL,
  `ob_type` VARCHAR(64) DEFAULT NULL,
  `ob_status` varchar(32) DEFAULT NULL,
  `result_status` varchar(32) DEFAULT NULL,
  `ob_reason_status` varchar(32) DEFAULT NULL,
  `ob_reason_code` varchar(64) DEFAULT NULL,
  `ob_reason_text` text,
  `ob_documentationof_table` varchar(255) DEFAULT NULL,
  `ob_documentationof_table_id` bigint(21) DEFAULT NULL,
   `date_end` DATETIME DEFAULT NULL,
   `parent_observation_id` bigint(20) DEFAULT NULL COMMENT 'FK to parent observation for sub-observations',
   `category` varchar(64) DEFAULT NULL COMMENT 'FK to list_options.option_id for observation category (SDOH, Functional, Cognitive, Physical, etc)',
   `questionnaire_response_id` bigint(21) DEFAULT NULL COMMENT 'FK to questionnaire_response table',
   `ob_value_code_description` VARCHAR(255) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `idx_form_id` (`form_id`),
   KEY `idx_parent_observation` (`parent_observation_id`),
   KEY `idx_category` (`category`),
   KEY `idx_questionnaire_response` (`questionnaire_response_id`),
   KEY `idx_pid_encounter` (`pid`, `encounter`),
   KEY `idx_date` (`date`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_clinical_instructions`
--

DROP TABLE IF EXISTS `form_clinical_instructions`;
CREATE TABLE `form_clinical_instructions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` bigint(20) DEFAULT NULL,
  `encounter` varchar(255) DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `instruction` text,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `activity` TINYINT DEFAULT 1 NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table 'valueset'
--

DROP TABLE IF EXISTS `valueset`;
CREATE TABLE `valueset` (
  `nqf_code` varchar(255) NOT NULL DEFAULT '',
  `code` varchar(255) NOT NULL DEFAULT '',
  `code_system` varchar(255) NOT NULL DEFAULT '',
  `code_type` varchar(255) DEFAULT NULL,
  `valueset` varchar(255) NOT NULL DEFAULT '',
  `description` varchar(255) DEFAULT NULL,
  `valueset_name` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`nqf_code`,`code`,`valueset`)
) ENGINE=InnoDB;

-- -------------------------------------------------------

--
-- Table structure for table `immunization_observation`
--

DROP TABLE IF EXISTS `immunization_observation`;
CREATE TABLE `immunization_observation` (
  `imo_id` int(11) NOT NULL AUTO_INCREMENT,
  `imo_im_id` int(11) NOT NULL,
  `imo_pid` int(11) DEFAULT NULL,
  `imo_criteria` varchar(255) DEFAULT NULL,
  `imo_criteria_value` varchar(255) DEFAULT NULL,
  `imo_user` int(11) DEFAULT NULL,
  `imo_code` varchar(255) DEFAULT NULL,
  `imo_codetext` varchar(255) DEFAULT NULL,
  `imo_codetype` varchar(255) DEFAULT NULL,
  `imo_vis_date_published` date DEFAULT NULL,
  `imo_vis_date_presented` date DEFAULT NULL,
  `imo_date_observation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`imo_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table 'calendar external'
--

DROP TABLE IF EXISTS `calendar_external`;
CREATE TABLE calendar_external (
  `id` INT NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL,
  `description` VARCHAR(45) NOT NULL,
  `source` VARCHAR(45) NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_mag_dispense`
--

DROP TABLE IF EXISTS `form_eye_mag_dispense`;
CREATE TABLE `form_eye_mag_dispense` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `encounter` bigint(20) NULL,
  `pid` bigint(20) DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `groupname` varchar(255) DEFAULT NULL,
  `authorized` tinyint(4) DEFAULT NULL,
  `activity` tinyint(4) DEFAULT NULL,
  `REFDATE` DATETIME NULL DEFAULT NULL,
  `REFTYPE` varchar(10) DEFAULT NULL,
  `RXTYPE` varchar(20)DEFAULT NULL,
  `ODSPH` varchar(10) DEFAULT NULL,
  `ODCYL` varchar(10) DEFAULT NULL,
  `ODAXIS` varchar(10) DEFAULT NULL,
  `OSSPH` varchar(10) DEFAULT NULL,
  `OSCYL` varchar(10) DEFAULT NULL,
  `OSAXIS` varchar(10) DEFAULT NULL,
  `ODMIDADD` varchar(10) DEFAULT NULL,
  `OSMIDADD` varchar(10) DEFAULT NULL,
  `ODADD` varchar(10) DEFAULT NULL,
  `OSADD` varchar(10) DEFAULT NULL,
  `ODHPD` varchar(20) DEFAULT NULL,
  `ODHBASE` varchar(20) DEFAULT NULL,
  `ODVPD` varchar(20) DEFAULT NULL,
  `ODVBASE` varchar(20) DEFAULT NULL,
  `ODSLABOFF` varchar(20) DEFAULT NULL,
  `ODVERTEXDIST` varchar(20) DEFAULT NULL,
  `OSHPD` varchar(20) DEFAULT NULL,
  `OSHBASE` varchar(20) DEFAULT NULL,
  `OSVPD` varchar(20) DEFAULT NULL,
  `OSVBASE` varchar(20) DEFAULT NULL,
  `OSSLABOFF` varchar(20) DEFAULT NULL,
  `OSVERTEXDIST` varchar(20) DEFAULT NULL,
  `ODMPDD` varchar(20) DEFAULT NULL,
  `ODMPDN` varchar(20) DEFAULT NULL,
  `OSMPDD` varchar(20) DEFAULT NULL,
  `OSMPDN` varchar(20) DEFAULT NULL,
  `BPDD` varchar(20) DEFAULT NULL,
  `BPDN` varchar(20) DEFAULT NULL,
  `LENS_MATERIAL` varchar(20) DEFAULT NULL,
  `LENS_TREATMENTS` varchar(100) DEFAULT NULL,
  `CTLMANUFACTUREROD` varchar(25) DEFAULT NULL,
  `CTLMANUFACTUREROS` varchar(25) DEFAULT NULL,
  `CTLSUPPLIEROD` varchar(25) DEFAULT NULL,
  `CTLSUPPLIEROS` varchar(25) DEFAULT NULL,
  `CTLBRANDOD` varchar(50) DEFAULT NULL,
  `CTLBRANDOS` varchar(50) DEFAULT NULL,
  `CTLODQUANTITY` varchar(255) DEFAULT NULL,
  `CTLOSQUANTITY` varchar(255) DEFAULT NULL,
  `ODDIAM` varchar(50) DEFAULT NULL,
  `ODBC` varchar(50) DEFAULT NULL,
  `OSDIAM` varchar(50) DEFAULT NULL,
  `OSBC` varchar(50) DEFAULT NULL,
  `RXCOMMENTS` text,
  `COMMENTS` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pid` (`pid`,`encounter`,`id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_mag_prefs`
--

DROP TABLE IF EXISTS `form_eye_mag_prefs`;
CREATE TABLE `form_eye_mag_prefs` (
  `PEZONE` varchar(25) DEFAULT NULL,
  `LOCATION` varchar(25) DEFAULT NULL,
  `LOCATION_text` varchar(25) NOT NULL,
  `id` bigint(20) DEFAULT NULL,
  `selection` varchar(255) DEFAULT NULL,
  `ZONE_ORDER` int(11) DEFAULT NULL,
  `GOVALUE` varchar(10) DEFAULT '0',
  `ordering` smallint(6) DEFAULT NULL,
  `FILL_ACTION` varchar(10) NOT NULL DEFAULT 'ADD',
  `GORIGHT` varchar(50) NOT NULL,
  `GOLEFT` varchar(50) NOT NULL,
  `UNSPEC` varchar(50) NOT NULL,
  UNIQUE KEY `id` (`id`,`PEZONE`,`LOCATION`,`selection`)
) ENGINE=InnoDB;

--
-- Inserting data for table `form_eye_mag_prefs`
--

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_mag_orders`
--

DROP TABLE IF EXISTS `form_eye_mag_orders`;
CREATE TABLE `form_eye_mag_orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `form_id` int(20) NOT NULL,
  `pid` bigint(20) NOT NULL,
  `ORDER_DETAILS` varchar(255) NOT NULL,
  `ORDER_STATUS` varchar(50) DEFAULT NULL,
  `ORDER_PRIORITY` varchar(50) DEFAULT NULL,
  `ORDER_DATE_PLACED` date NOT NULL,
  `ORDER_PLACED_BYWHOM` varchar(50) DEFAULT NULL,
  `ORDER_DATE_COMPLETED` date DEFAULT NULL,
  `ORDER_COMPLETED_BYWHOM` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `VISIT_ID` (`pid`,`ORDER_DETAILS`,`ORDER_DATE_PLACED`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_mag_impplan`
--

DROP TABLE IF EXISTS `form_eye_mag_impplan`;
CREATE TABLE `form_eye_mag_impplan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` bigint(20) NOT NULL,
  `pid` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `codetype` varchar(50) DEFAULT NULL,
  `codedesc` varchar(255) DEFAULT NULL,
  `codetext` varchar(255) DEFAULT NULL,
  `plan` varchar(3000) DEFAULT NULL,
  `PMSFH_link` varchar(50) DEFAULT NULL,
  `IMPPLAN_order` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `second_index` (`form_id`,`pid`,`title`,`plan`(20))
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_mag_wearing`
--

DROP TABLE IF EXISTS `form_eye_mag_wearing`;
CREATE TABLE `form_eye_mag_wearing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ENCOUNTER` int(11) NOT NULL,
  `FORM_ID` smallint(6) NOT NULL,
  `PID` bigint(20) NOT NULL,
  `RX_NUMBER` int(11) NOT NULL,
  `ODSPH` varchar(10) DEFAULT NULL,
  `ODCYL` varchar(10) DEFAULT NULL,
  `ODAXIS` varchar(10) DEFAULT NULL,
  `OSSPH` varchar(10) DEFAULT NULL,
  `OSCYL` varchar(10) DEFAULT NULL,
  `OSAXIS` varchar(10) DEFAULT NULL,
  `ODMIDADD` varchar(10) DEFAULT NULL,
  `OSMIDADD` varchar(10) DEFAULT NULL,
  `ODADD` varchar(10) DEFAULT NULL,
  `OSADD` varchar(10) DEFAULT NULL,
  `ODVA` varchar(10) DEFAULT NULL,
  `OSVA` varchar(10) DEFAULT NULL,
  `ODNEARVA` varchar(10) DEFAULT NULL,
  `OSNEARVA` varchar(10) DEFAULT NULL,
  `ODHPD` varchar(20) DEFAULT NULL,
  `ODHBASE` varchar(20) DEFAULT NULL,
  `ODVPD` varchar(20) DEFAULT NULL,
  `ODVBASE` varchar(20) DEFAULT NULL,
  `ODSLABOFF` varchar(20) DEFAULT NULL,
  `ODVERTEXDIST` varchar(20) DEFAULT NULL,
  `OSHPD` varchar(20) DEFAULT NULL,
  `OSHBASE` varchar(20) DEFAULT NULL,
  `OSVPD` varchar(20) DEFAULT NULL,
  `OSVBASE` varchar(20) DEFAULT NULL,
  `OSSLABOFF` varchar(20) DEFAULT NULL,
  `OSVERTEXDIST` varchar(20) DEFAULT NULL,
  `ODMPDD` varchar(20) DEFAULT NULL,
  `ODMPDN` varchar(20) DEFAULT NULL,
  `OSMPDD` varchar(20) DEFAULT NULL,
  `OSMPDN` varchar(20) DEFAULT NULL,
  `BPDD` varchar(20) DEFAULT NULL,
  `BPDN` varchar(20) DEFAULT NULL,
  `LENS_MATERIAL` varchar(20) DEFAULT NULL,
  `LENS_TREATMENTS` varchar(100) DEFAULT NULL,
  `RX_TYPE` varchar(25) DEFAULT NULL,
  `COMMENTS` text,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `FORM_ID` (`FORM_ID`,`ENCOUNTER`,`PID`,`RX_NUMBER`)
) ENGINE=InnoDB;

--
-- Table structure for table `form_taskman`
--

DROP TABLE IF EXISTS `form_taskman`;
CREATE TABLE `form_taskman` (
    `ID` bigint(20) NOT NULL AUTO_INCREMENT,
    `REQ_DATE` datetime NOT NULL,
    `FROM_ID` bigint(20) NOT NULL,
    `TO_ID` bigint(20) NOT NULL,
    `PATIENT_ID` bigint(20) NOT NULL, `DOC_TYPE` varchar(20) DEFAULT NULL,
    `DOC_ID` bigint(20) DEFAULT NULL,
    `ENC_ID` bigint(20) DEFAULT NULL,
    `METHOD` varchar(20) NOT NULL, `COMPLETED` varchar(1) DEFAULT NULL COMMENT '1 = completed',
    `COMPLETED_DATE` datetime DEFAULT NULL,
    `COMMENT` varchar(50) DEFAULT NULL,
    `USERFIELD_1` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`ID`)
) ENGINE=INNODB;

-- -----------------------------------------------------
--
-- Table structure for table 'product_registration'
--

DROP TABLE IF EXISTS `product_registration`;
CREATE TABLE `product_registration` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NULL,
    `opt_out` TINYINT(1) NULL,
    `auth_by_id` INT(11) NULL,
    `telemetry_disabled` TINYINT(1) NULL COMMENT '1 opted out, disabled. NULL ask. 0 use option scopes',
    `last_ask_date` DATETIME NULL,
    `last_ask_version`TINYTEXT,
    `options` TEXT COMMENT 'JSON array of scope options',
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------

--
-- Table structure for table 'codes_history'
--

DROP TABLE IF EXISTS `codes_history`;
CREATE TABLE `codes_history` (
  `log_id` bigint(20) NOT NULL auto_increment,
  `date` datetime,
  `code` varchar(25),
  `modifier` varchar(12),
  `active` tinyint(1),
  `diagnosis_reporting` tinyint(1),
  `financial_reporting` tinyint(1),
  `category` varchar(255),
  `code_type_name` varchar(255),
  `code_text` text,
  `code_text_short` text,
  `prices` text,
  `action_type` varchar(25),
  `update_by` varchar(255),
   PRIMARY KEY (`log_id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------

--
-- Table structure for `therapy_groups`
--

DROP TABLE IF EXISTS `therapy_groups`;
CREATE TABLE `therapy_groups` (
  `group_id` int(11) NOT NULL auto_increment,
  `group_name` varchar(255) NOT NULL ,
  `group_start_date` date NOT NULL ,
  `group_end_date` date,
  `group_type` tinyint NOT NULL,
  `group_participation` tinyint NOT NULL,
  `group_status` int(11) NOT NULL,
  `group_notes` text,
  `group_guest_counselors` varchar(255),
  PRIMARY KEY  (`group_id`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for `therapy_groups_participants`
--

DROP TABLE IF EXISTS `therapy_groups_participants`;
CREATE TABLE `therapy_groups_participants` (
  `group_id` int(11) NOT NULL,
  `pid` bigint(20) NOT NULL,
  `group_patient_status` int(11) NOT NULL,
  `group_patient_start` date NOT NULL ,
  `group_patient_end` date,
  `group_patient_comment` text,
  PRIMARY KEY (`group_id`,`pid`)
) ENGINE=InnoDB;

-- -- ---------------------------------------------------------

--
-- Table structure for `therapy_groups_participant_attendance`
--

DROP TABLE IF EXISTS `therapy_groups_participant_attendance`;
CREATE TABLE `therapy_groups_participant_attendance` (
  `form_id` int(11) NOT NULL ,
  `pid` bigint(20) NOT NULL,
  `meeting_patient_comment` text ,
  `meeting_patient_status` varchar(15),
  PRIMARY KEY (`form_id`,`pid`)
) ENGINE=InnoDB;

-- -- ---------------------------------------------------------

--
-- Table structure for `therapy_groups_counselors`
--

DROP TABLE IF EXISTS `therapy_groups_counselors`;
CREATE TABLE `therapy_groups_counselors`(
    `group_id` int(11) NOT NULL,
    `user_id` int(11) NOT NULL,
    PRIMARY KEY (`group_id`,`user_id`)
) ENGINE=InnoDB;

-- -- ---------------------------------------------------------

--
-- Table structure for `form_groups_encounter`
--

DROP TABLE IF EXISTS `form_groups_encounter`;
CREATE TABLE `form_groups_encounter` (
  `id` bigint(20) NOT NULL auto_increment,
  `date` datetime default NULL,
  `reason` longtext,
  `facility` longtext,
  `facility_id` int(11) NOT NULL default '0',
  `group_id` bigint(20) default NULL,
  `encounter` bigint(20) default NULL,
  `onset_date` datetime default NULL,
  `sensitivity` varchar(30) default NULL,
  `billing_note` text,
  `pc_catid` int(11) NOT NULL default '5' COMMENT 'event category from openemr_postcalendar_categories',
  `last_level_billed` int  NOT NULL DEFAULT 0 COMMENT '0=none, 1=ins1, 2=ins2, etc',
  `last_level_closed` int  NOT NULL DEFAULT 0 COMMENT '0=none, 1=ins1, 2=ins2, etc',
  `last_stmt_date`    date DEFAULT NULL,
  `stmt_count`        int  NOT NULL DEFAULT 0,
  `provider_id` INT(11) DEFAULT '0' COMMENT 'default and main provider for this visit',
  `supervisor_id` INT(11) DEFAULT '0' COMMENT 'supervising provider, if any, for this visit',
  `invoice_refno` varchar(31) NOT NULL DEFAULT '',
  `referral_source` varchar(31) NOT NULL DEFAULT '',
  `billing_facility` INT(11) NOT NULL DEFAULT 0,
  `external_id` VARCHAR(20) DEFAULT NULL,
  `pos_code` tinyint(4) default NULL,
  `counselors` VARCHAR (255),
  `appt_id` INT(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `pid_encounter` (`group_id`, `encounter`),
  KEY `encounter_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- -- ---------------------------------------------------------

--
-- Table structure for `form_group_attendance`
--

DROP TABLE IF EXISTS `form_group_attendance`;
CREATE TABLE `form_group_attendance` (
  `id`	bigint(20) auto_increment,
  `date`	date,
  `group_id`	int(11),
  `user`	varchar(255),
  `groupname`	varchar(255),
  `authorized`	tinyint(4),
  `encounter_id`	int(11),
  activity	tinyint(4),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- -- ---------------------------------------------------------

--
-- Table structure for `patient_birthday_alert`
--

DROP TABLE IF EXISTS `patient_birthday_alert`;
CREATE TABLE `patient_birthday_alert` (
  `pid` bigint(20) NOT NULL DEFAULT 0,
  `user_id` bigint(20) NOT NULL DEFAULT 0,
  `turned_off_on` date NOT NULL,
  PRIMARY KEY  (`pid`,`user_id`)
) ENGINE=InnoDB;

--
-- Table structure for table `medex_icons`
--
DROP TABLE IF EXISTS `medex_icons`;
CREATE TABLE `medex_icons` (
  `i_UID` int(11) NOT NULL AUTO_INCREMENT,
  `msg_type` varchar(50) NOT NULL,
  `msg_status` varchar(10) NOT NULL,
  `i_description` varchar(255),
  `i_html` text,
  `i_blob` longtext,
  PRIMARY KEY (`i_UID`)
) ENGINE=InnoDB;

--
-- Dumping data for table `medex_icons`
--


-- --------------------------------------------------------

--
-- Table structure for table `medex_outgoing`
DROP TABLE IF EXISTS `medex_outgoing`;
CREATE TABLE `medex_outgoing` (
  `msg_uid` int(11) NOT NULL AUTO_INCREMENT,
  `msg_pid` int(11) NOT NULL,
  `msg_pc_eid` varchar(11) NOT NULL,
  `campaign_uid` int(11) NOT NULL DEFAULT '0',
  `msg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `msg_type` varchar(50) NOT NULL,
  `msg_reply` varchar(50) DEFAULT NULL,
  `msg_extra_text` text,
  `medex_uid` int(11),
  PRIMARY KEY (`msg_uid`),
  UNIQUE KEY `msg_eid` (`msg_uid`,`msg_pc_eid`,`medex_uid`)
) ENGINE=InnoDB;

--
-- Dumping data for table `medex_outgoing`
--


-- --------------------------------------------------------

--
-- Table structure for table `medex_prefs`
--
DROP TABLE IF EXISTS `medex_prefs`;
CREATE TABLE `medex_prefs` (
  `MedEx_id` int(11) DEFAULT '0',
  `ME_username` varchar(100) DEFAULT NULL,
  `ME_api_key` text,
  `ME_facilities` varchar(50) DEFAULT NULL,
  `ME_providers` varchar(100) DEFAULT NULL,
  `ME_hipaa_default_override` varchar(3) DEFAULT NULL,
  `PHONE_country_code` int(4) NOT NULL DEFAULT '1',
  `MSGS_default_yes` varchar(3) DEFAULT NULL,
  `POSTCARDS_local` varchar(3) DEFAULT NULL,
  `POSTCARDS_remote` varchar(3) DEFAULT NULL,
  `LABELS_local` varchar(3) DEFAULT NULL,
  `LABELS_choice` varchar(50) DEFAULT NULL,
  `combine_time` tinyint(4) DEFAULT NULL,
  `postcard_top` varchar(255) DEFAULT NULL,
  `status` text,
  `MedEx_lastupdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `ME_username` (`ME_username`)
) ENGINE=InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `medex_recalls`
--
DROP TABLE IF EXISTS `medex_recalls`;
CREATE TABLE `medex_recalls` (
  `r_ID` int(11) NOT NULL AUTO_INCREMENT,
  `r_PRACTID` int(11) NOT NULL,
  `r_pid` int(11) NOT NULL COMMENT 'PatientID from pat_data',
  `r_eventDate` date NOT NULL COMMENT 'Date of Appt or Recall',
  `r_facility` int(11) NOT NULL,
  `r_provider` int(11) NOT NULL,
  `r_reason` varchar(255) DEFAULT NULL,
  `r_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`r_ID`),
  UNIQUE KEY `r_PRACTID` (`r_PRACTID`,`r_pid`)
) ENGINE=InnoDB;


-- --------------------------------------------------------

--
-- Table structure for table `form_eye_base`
--
DROP TABLE IF EXISTS `form_eye_base`;
CREATE TABLE `form_eye_base` (
  `id`         bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Links to forms.form_id',
  `date`       datetime DEFAULT NULL,
  `pid`        bigint(20)   DEFAULT NULL,
  `user`       varchar(255) DEFAULT NULL,
  `groupname`  varchar(255) DEFAULT NULL,
  `authorized` tinyint(4)   DEFAULT NULL,
  `activity`   tinyint(4)   DEFAULT NULL,
  PRIMARY KEY `form_link` (`id`)
) ENGINE = InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_hpi`
--

DROP TABLE IF EXISTS `form_eye_hpi`;
CREATE TABLE `form_eye_hpi` (
  `id`          bigint(20) NOT NULL COMMENT 'Links to forms.form_id',
  `pid`         bigint(20)   DEFAULT NULL,
  `CC1`         varchar(255) DEFAULT NULL,
  `HPI1`        text,
  `QUALITY1`    varchar(255) DEFAULT NULL,
  `TIMING1`     varchar(255) DEFAULT NULL,
  `DURATION1`   varchar(255) DEFAULT NULL,
  `CONTEXT1`    varchar(255) DEFAULT NULL,
  `SEVERITY1`   varchar(255) DEFAULT NULL,
  `MODIFY1`     varchar(255) DEFAULT NULL,
  `ASSOCIATED1` varchar(255) DEFAULT NULL,
  `LOCATION1`   varchar(255) DEFAULT NULL,
  `CHRONIC1`    varchar(255) DEFAULT NULL,
  `CHRONIC2`    varchar(255) DEFAULT NULL,
  `CHRONIC3`    varchar(255) DEFAULT NULL,
  `CC2`         text,
  `HPI2`        text,
  `QUALITY2`    text,
  `TIMING2`     text,
  `DURATION2`   text,
  `CONTEXT2`    text,
  `SEVERITY2`   text,
  `MODIFY2`     text,
  `ASSOCIATED2` text,
  `LOCATION2`   text,
  `CC3`         text,
  `HPI3`        text,
  `QUALITY3`    text,
  `TIMING3`     text,
  `DURATION3`   text,
  `CONTEXT3`    text,
  `SEVERITY3`   text,
  `MODIFY3`     text,
  `ASSOCIATED3` text,
  `LOCATION3`   text,
  PRIMARY KEY `hpi_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
)  ENGINE = InnoDB;


-- --------------------------------------------------------

--
-- Table structure for table `form_eye_ros`
--
DROP TABLE IF EXISTS `form_eye_ros`;
CREATE TABLE `form_eye_ros` (
  `id`           bigint(20) NOT NULL COMMENT 'Links to forms.form_id',
  `pid`          bigint(20)   DEFAULT NULL,
  `ROSGENERAL`   text,
  `ROSHEENT`     text,
  `ROSCV`        text,
  `ROSPULM`      text,
  `ROSGI`        text,
  `ROSGU`        text,
  `ROSDERM`      text,
  `ROSNEURO`     text,
  `ROSPSYCH`     text,
  `ROSMUSCULO`   text,
  `ROSIMMUNO`    text,
  `ROSENDOCRINE` text,
  `ROSCOMMENTS`  text,
  PRIMARY KEY `ros_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
  )
  ENGINE = InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_vitals`
--

DROP TABLE IF EXISTS `form_eye_vitals`;
CREATE TABLE `form_eye_vitals` (
  `id`          bigint(20)  NOT NULL COMMENT 'Links to forms.form_id',
  `pid`         bigint(20)   DEFAULT NULL,
  `alert`       char(3)     DEFAULT 'yes',
  `oriented`    char(3)     DEFAULT 'TPP',
  `confused`    char(3)     DEFAULT 'nml',
  `ODIOPAP`     varchar(10) DEFAULT NULL,
  `OSIOPAP`     varchar(10) DEFAULT NULL,
  `ODIOPTPN`    varchar(10) DEFAULT NULL,
  `OSIOPTPN`    varchar(10) DEFAULT NULL,
  `ODIOPFTN`    varchar(10) DEFAULT NULL,
  `OSIOPFTN`    varchar(10) DEFAULT NULL,
  `IOPTIME`     time        NOT NULL,
  `ODIOPPOST`   varchar(10) NOT NULL,
  `OSIOPPOST`   varchar(10) NOT NULL,
  `IOPPOSTTIME` time        DEFAULT NULL,
  `ODIOPTARGET` varchar(10) NOT NULL,
  `OSIOPTARGET` varchar(10) NOT NULL,
  `AMSLEROD`    smallint(1) DEFAULT NULL,
  `AMSLEROS`    smallint(1) DEFAULT NULL,
  `ODVF1`       tinyint(1)  DEFAULT NULL,
  `ODVF2`       tinyint(1)  DEFAULT NULL,
  `ODVF3`       tinyint(1)  DEFAULT NULL,
  `ODVF4`       tinyint(1)  DEFAULT NULL,
  `OSVF1`       tinyint(1)  DEFAULT NULL,
  `OSVF2`       tinyint(1)  DEFAULT NULL,
  `OSVF3`       tinyint(1)  DEFAULT NULL,
  `OSVF4`       tinyint(1)  DEFAULT NULL,
  PRIMARY KEY `vitals_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
  )
  ENGINE = InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_acuity`
--

DROP TABLE IF EXISTS `form_eye_acuity`;
CREATE TABLE `form_eye_acuity` (
  `id`            bigint(20)  NOT NULL COMMENT 'Links to forms.form_id',
  `pid`           bigint(20)   DEFAULT NULL,
  `SCODVA`        varchar(25)  DEFAULT NULL,
  `SCOSVA`        varchar(25)  DEFAULT NULL,
  `PHODVA`        varchar(25)  DEFAULT NULL,
  `PHOSVA`        varchar(25)  DEFAULT NULL,
  `CTLODVA`       varchar(25)  DEFAULT NULL,
  `CTLOSVA`       varchar(25)  DEFAULT NULL,
  `MRODVA`        varchar(25)  DEFAULT NULL,
  `MROSVA`        varchar(25)  DEFAULT NULL,
  `SCNEARODVA`    varchar(25)  DEFAULT NULL,
  `SCNEAROSVA`    varchar(25)  DEFAULT NULL,
  `MRNEARODVA`    varchar(25)  DEFAULT NULL,
  `MRNEAROSVA`    varchar(25)  DEFAULT NULL,
  `GLAREODVA`     varchar(25)  DEFAULT NULL,
  `GLAREOSVA`     varchar(25)  DEFAULT NULL,
  `GLARECOMMENTS` varchar(255) DEFAULT NULL,
  `ARODVA`        varchar(25)  DEFAULT NULL,
  `AROSVA`        varchar(25)  DEFAULT NULL,
  `CRODVA`        varchar(25)  DEFAULT NULL,
  `CROSVA`        varchar(25)  DEFAULT NULL,
  `CTLODVA1`      varchar(25)  DEFAULT NULL,
  `CTLOSVA1`      varchar(25)  DEFAULT NULL,
  `PAMODVA`       varchar(25)  DEFAULT NULL,
  `PAMOSVA`       varchar(25)  DEFAULT NULL,
  `LIODVA`        varchar(25)  NOT NULL,
  `LIOSVA`        varchar(25)  NOT NULL,
  `WODVANEAR`     varchar(25)  DEFAULT NULL,
  `OSVANEARCC`    varchar(25)  DEFAULT NULL,
  `BINOCVA`       varchar(25)  DEFAULT NULL,
  PRIMARY KEY `acuity_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
  )
  ENGINE = InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_refraction`
--
DROP TABLE IF EXISTS `form_eye_refraction`;
CREATE TABLE `form_eye_refraction` (
  `id`                bigint(20) NOT NULL COMMENT 'Links to forms.form_id',
  `pid`               bigint(20)   DEFAULT NULL,
  `MRODSPH`           varchar(25)  DEFAULT NULL,
  `MRODCYL`           varchar(25)  DEFAULT NULL,
  `MRODAXIS`          varchar(25)  DEFAULT NULL,
  `MRODPRISM`         varchar(25)  DEFAULT NULL,
  `MRODBASE`          varchar(25)  DEFAULT NULL,
  `MRODADD`           varchar(25)  DEFAULT NULL,
  `MROSSPH`           varchar(25)  DEFAULT NULL,
  `MROSCYL`           varchar(25)  DEFAULT NULL,
  `MROSAXIS`          varchar(25)  DEFAULT NULL,
  `MROSPRISM`         varchar(50)  DEFAULT NULL,
  `MROSBASE`          varchar(50)  DEFAULT NULL,
  `MROSADD`           varchar(25)  DEFAULT NULL,
  `MRODNEARSPHERE`    varchar(25)  DEFAULT NULL,
  `MRODNEARCYL`       varchar(25)  DEFAULT NULL,
  `MRODNEARAXIS`      varchar(25)  DEFAULT NULL,
  `MRODPRISMNEAR`     varchar(50)  DEFAULT NULL,
  `MRODBASENEAR`      varchar(25)  DEFAULT NULL,
  `MROSNEARSHPERE`    varchar(25)  DEFAULT NULL,
  `MROSNEARCYL`       varchar(25)  DEFAULT NULL,
  `MROSNEARAXIS`      varchar(125) DEFAULT NULL,
  `MROSPRISMNEAR`     varchar(50)  DEFAULT NULL,
  `MROSBASENEAR`      varchar(25)  DEFAULT NULL,
  `CRODSPH`           varchar(25)  DEFAULT NULL,
  `CRODCYL`           varchar(25)  DEFAULT NULL,
  `CRODAXIS`          varchar(25)  DEFAULT NULL,
  `CROSSPH`           varchar(25)  DEFAULT NULL,
  `CROSCYL`           varchar(25)  DEFAULT NULL,
  `CROSAXIS`          varchar(25)  DEFAULT NULL,
  `CRCOMMENTS`        varchar(255) DEFAULT NULL,
  `BALANCED`          char(2)    NOT NULL,
  `ARODSPH`           varchar(25)  DEFAULT NULL,
  `ARODCYL`           varchar(25)  DEFAULT NULL,
  `ARODAXIS`          varchar(25)  DEFAULT NULL,
  `AROSSPH`           varchar(25)  DEFAULT NULL,
  `AROSCYL`           varchar(25)  DEFAULT NULL,
  `AROSAXIS`          varchar(25)  DEFAULT NULL,
  `ARODADD`           varchar(25)  DEFAULT NULL,
  `AROSADD`           varchar(25)  DEFAULT NULL,
  `ARNEARODVA`        varchar(25)  DEFAULT NULL,
  `ARNEAROSVA`        varchar(25)  DEFAULT NULL,
  `ARODPRISM`         varchar(50)  DEFAULT NULL,
  `AROSPRISM`         varchar(50)  DEFAULT NULL,
  `CTLODSPH`          varchar(25)  DEFAULT NULL,
  `CTLODCYL`          varchar(25)  DEFAULT NULL,
  `CTLODAXIS`         varchar(25)  DEFAULT NULL,
  `CTLODBC`           varchar(25)  DEFAULT NULL,
  `CTLODDIAM`         varchar(25)  DEFAULT NULL,
  `CTLOSSPH`          varchar(25)  DEFAULT NULL,
  `CTLOSCYL`          varchar(25)  DEFAULT NULL,
  `CTLOSAXIS`         varchar(25)  DEFAULT NULL,
  `CTLOSBC`           varchar(25)  DEFAULT NULL,
  `CTLOSDIAM`         varchar(25)  DEFAULT NULL,
  `CTL_COMMENTS`      text,
  `CTLMANUFACTUREROD` varchar(50)  DEFAULT NULL,
  `CTLSUPPLIEROD`     varchar(50)  DEFAULT NULL,
  `CTLBRANDOD`        varchar(50)  DEFAULT NULL,
  `CTLMANUFACTUREROS` varchar(50)  DEFAULT NULL,
  `CTLSUPPLIEROS`     varchar(50)  DEFAULT NULL,
  `CTLBRANDOS`        varchar(50)  DEFAULT NULL,
  `CTLODADD`          varchar(25)  DEFAULT NULL,
  `CTLOSADD`          varchar(25)  DEFAULT NULL,
  `NVOCHECKED`        varchar(25)  DEFAULT NULL,
  `ADDCHECKED`        varchar(25)  DEFAULT NULL,
  PRIMARY KEY `refraction_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
  )
  ENGINE = InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_biometrics`
--

DROP TABLE IF EXISTS `form_eye_biometrics`;
CREATE TABLE `form_eye_biometrics` (
  `id`            bigint (20) NOT NULL COMMENT 'Links to forms.form_id',
  `pid`           bigint(20)   DEFAULT NULL,
  `ODK1`          varchar (10) DEFAULT NULL,
  `ODK2`          varchar (10) DEFAULT NULL,
  `ODK2AXIS`      varchar (10) DEFAULT NULL,
  `OSK1`          varchar (10) DEFAULT NULL,
  `OSK2`          varchar (10) DEFAULT NULL,
  `OSK2AXIS`      varchar (10) DEFAULT NULL,
  `ODAXIALLENGTH` varchar (20) DEFAULT NULL,
  `OSAXIALLENGTH` varchar (20) DEFAULT NULL,
  `ODPDMeasured`  varchar (20) DEFAULT NULL,
  `OSPDMeasured`  varchar (20) DEFAULT NULL,
  `ODACD`         varchar (20) DEFAULT NULL,
  `OSACD`         varchar (20) DEFAULT NULL,
  `ODW2W`         varchar (20) DEFAULT NULL,
  `OSW2W`         varchar (20) DEFAULT NULL,
  `ODLT`          varchar (20) DEFAULT NULL,
  `OSLT`          varchar (20) DEFAULT NULL,
  PRIMARY KEY `biometrics_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
)
  ENGINE = InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_external`
--

DROP TABLE IF EXISTS `form_eye_external`;
CREATE TABLE `form_eye_external` (
  `id`           bigint(20)  NOT NULL COMMENT 'Links to forms.form_id',
  `pid`          bigint(20)  DEFAULT NULL,
  `RUL`          text,
  `LUL`          text,
  `RLL`          text,
  `LLL`          text,
  `RBROW`        text,
  `LBROW`        text,
  `RMCT`         text,
  `LMCT`         text,
  `RADNEXA`      text,
  `LADNEXA`      text,
  `RMRD`         varchar(25) DEFAULT NULL,
  `LMRD`         varchar(25) DEFAULT NULL,
  `RLF`          varchar(25) DEFAULT NULL,
  `LLF`          varchar(25) DEFAULT NULL,
  `RVFISSURE`    varchar(25) DEFAULT NULL,
  `LVFISSURE`    varchar(25) DEFAULT NULL,
  `ODHERTEL`     varchar(25) DEFAULT NULL,
  `OSHERTEL`     varchar(25) DEFAULT NULL,
  `HERTELBASE`   varchar(25) DEFAULT NULL,
  `RCAROTID`     text,
  `LCAROTID`     text,
  `RTEMPART`     text,
  `LTEMPART`     text,
  `RCNV`         text,
  `LCNV`         text,
  `RCNVII`       text,
  `LCNVII`       text,
  `EXT_COMMENTS` text,
  PRIMARY KEY `external_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
)
  ENGINE = InnoDB;

-- --------------------------------------------------------

--
-- Table structure for table `form_eye_antseg`
--

DROP TABLE IF EXISTS `form_eye_antseg`;
CREATE TABLE `form_eye_antseg` (
  `id`                   bigint(20) NOT NULL COMMENT 'Links to forms.form_id',
  `pid`                  bigint(20)   DEFAULT NULL,
  `ODSCHIRMER1`          varchar(25) DEFAULT NULL,
  `OSSCHIRMER1`          varchar(25) DEFAULT NULL,
  `ODSCHIRMER2`          varchar(25) DEFAULT NULL,
  `OSSCHIRMER2`          varchar(25) DEFAULT NULL,
  `ODTBUT`               varchar(25) DEFAULT NULL,
  `OSTBUT`               varchar(25) DEFAULT NULL,
  `OSCONJ`               text,
  `ODCONJ`               text,
  `ODCORNEA`             text,
  `OSCORNEA`             text,
  `ODAC`                 text,
  `OSAC`                 text,
  `ODLENS`               text,
  `OSLENS`               text,
  `ODIRIS`               text,
  `OSIRIS`               text,
  `PUPIL_NORMAL`         varchar(2)  DEFAULT '1',
  `ODPUPILSIZE1`         varchar(25) DEFAULT NULL,
  `ODPUPILSIZE2`         varchar(25) DEFAULT NULL,
  `ODPUPILREACTIVITY`    char(25)    DEFAULT NULL,
  `ODAPD`                varchar(25) DEFAULT NULL,
  `OSPUPILSIZE1`         varchar(25) DEFAULT NULL,
  `OSPUPILSIZE2`         varchar(25) DEFAULT NULL,
  `OSPUPILREACTIVITY`    char(25)    DEFAULT NULL,
  `OSAPD`                varchar(25) DEFAULT NULL,
  `DIMODPUPILSIZE1`      varchar(25) DEFAULT NULL,
  `DIMODPUPILSIZE2`      varchar(25) DEFAULT NULL,
  `DIMODPUPILREACTIVITY` varchar(25) DEFAULT NULL,
  `DIMOSPUPILSIZE1`      varchar(25) DEFAULT NULL,
  `DIMOSPUPILSIZE2`      varchar(25) DEFAULT NULL,
  `DIMOSPUPILREACTIVITY` varchar(25) DEFAULT NULL,
  `PUPIL_COMMENTS`       text,
  `ODKTHICKNESS`         varchar(25) DEFAULT NULL,
  `OSKTHICKNESS`         varchar(25) DEFAULT NULL,
  `ODGONIO`              varchar(25) DEFAULT NULL,
  `OSGONIO`              varchar(25) DEFAULT NULL,
  `ANTSEG_COMMENTS`      text,
  PRIMARY KEY `antseg_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
 )
  ENGINE = InnoDB;


-- --------------------------------------------------------

--
-- Table structure for table `form_eye_postseg`
--

DROP TABLE IF EXISTS `form_eye_postseg`;
CREATE TABLE `form_eye_postseg` (
  `id`              bigint(20)  NOT NULL COMMENT 'Links to forms.form_id',
  `pid`             bigint(20)   DEFAULT NULL,
  `ODDISC`          text,
  `OSDISC`          text,
  `ODCUP`           text,
  `OSCUP`           text,
  `ODMACULA`        text,
  `OSMACULA`        text,
  `ODVESSELS`       text,
  `OSVESSELS`       text,
  `ODVITREOUS`      text,
  `OSVITREOUS`      text,
  `ODPERIPH`        text,
  `OSPERIPH`        text,
  `ODCMT`           text,
  `OSCMT`           text,
  `RETINA_COMMENTS` text,
  `DIL_RISKS`       char(2)     NOT NULL DEFAULT 'on',
  `DIL_MEDS`        mediumtext,
  `WETTYPE`         varchar(10) NOT NULL,
  `ATROPINE`        varchar(25) NOT NULL,
  `CYCLOMYDRIL`     varchar(25) NOT NULL,
  `TROPICAMIDE`     varchar(25) NOT NULL,
  `CYCLOGYL`        varchar(25) NOT NULL,
  `NEO25`           varchar(25) NOT NULL,
  PRIMARY KEY `postseg_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
 )
  ENGINE = InnoDB;


-- --------------------------------------------------------

--
-- Table structure for table `form_eye_neuro`
--

DROP TABLE IF EXISTS `form_eye_neuro`;
CREATE TABLE `form_eye_neuro` (
  `id`         bigint (20) NOT NULL COMMENT 'Links to forms.form_id',
  `pid`        bigint(20)   DEFAULT NULL,
  `ACT`        char (3) NOT NULL DEFAULT 'on',
  `ACT5CCDIST` text,
  `ACT1CCDIST` text,
  `ACT2CCDIST` text,
  `ACT3CCDIST` text,
  `ACT4CCDIST` text,
  `ACT6CCDIST` text,
  `ACT7CCDIST` text,
  `ACT8CCDIST` text,
  `ACT9CCDIST` text,
  `ACT10CCDIST` text,
  `ACT11CCDIST` text,
  `ACT1SCDIST` text,
  `ACT2SCDIST` text,
  `ACT3SCDIST` text,
  `ACT4SCDIST` text,
  `ACT5SCDIST` text,
  `ACT6SCDIST` text,
  `ACT7SCDIST` text,
  `ACT8SCDIST` text,
  `ACT9SCDIST` text,
  `ACT10SCDIST` text,
  `ACT11SCDIST` text,
  `ACT1SCNEAR` text,
  `ACT2SCNEAR` text,
  `ACT3SCNEAR` text,
  `ACT4SCNEAR` text,
  `ACT5CCNEAR` text,
  `ACT6CCNEAR` text,
  `ACT7CCNEAR` text,
  `ACT8CCNEAR` text,
  `ACT9CCNEAR` text,
  `ACT10CCNEAR` text,
  `ACT11CCNEAR` text,
  `ACT5SCNEAR` text,
  `ACT6SCNEAR` text,
  `ACT7SCNEAR` text,
  `ACT8SCNEAR` text,
  `ACT9SCNEAR` text,
  `ACT10SCNEAR` text,
  `ACT11SCNEAR` text,
  `ACT1CCNEAR` text,
  `ACT2CCNEAR` text,
  `ACT3CCNEAR` text,
  `ACT4CCNEAR`text,
  `MOTILITYNORMAL` char (3) NOT NULL DEFAULT 'on',
  `MOTILITY_RS` char (1) DEFAULT '0',
  `MOTILITY_RI` char (1) DEFAULT '0',
  `MOTILITY_RR` char (1) DEFAULT '0',
  `MOTILITY_RL` char (1) DEFAULT '0',
  `MOTILITY_LS` char (1) DEFAULT '0',
  `MOTILITY_LI` char (1) DEFAULT '0',
  `MOTILITY_LR` char (1) DEFAULT '0',
  `MOTILITY_LL` char (1) DEFAULT '0',
  `MOTILITY_RRSO` int (1) DEFAULT NULL,
  `MOTILITY_RLSO` int (1) DEFAULT NULL,
  `MOTILITY_RRIO` int (1) DEFAULT NULL,
  `MOTILITY_RLIO` int (1) DEFAULT NULL,
  `MOTILITY_LRSO` int (1) DEFAULT NULL,
  `MOTILITY_LLSO` int (1) DEFAULT NULL,
  `MOTILITY_LRIO` int (1) DEFAULT NULL,
  `MOTILITY_LLIO` int (1) DEFAULT NULL,
  `NEURO_COMMENTS` text,
  `STEREOPSIS` varchar (25) DEFAULT NULL,
  `ODNPA` text,
  `OSNPA` text,
  `VERTFUSAMPS` text,
  `DIVERGENCEAMPS` text,
  `NPC` varchar (10) DEFAULT NULL,
  `DACCDIST` varchar (20) DEFAULT NULL,
  `DACCNEAR` varchar (20) DEFAULT NULL,
  `CACCDIST` varchar (20) DEFAULT NULL,
  `CACCNEAR` varchar (20) DEFAULT NULL,
  `ODCOLOR` text,
  `OSCOLOR` text,
  `ODCOINS` text,
  `OSCOINS` text,
  `ODREDDESAT` varchar (20) DEFAULT NULL,
  `OSREDDESAT` varchar (20) DEFAULT NULL,
  PRIMARY KEY `neuro_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
 )
  ENGINE = InnoDB;


-- --------------------------------------------------------

--
-- Table structure for table `form_eye_locking`
--

DROP TABLE IF EXISTS `form_eye_locking`;
CREATE TABLE `form_eye_locking` (
  `id`         bigint(20) NOT NULL COMMENT 'Links to forms.form_id',
  `pid`        bigint(20)          DEFAULT NULL,
  `IMP`        text,
  `PLAN`       text,
  `Resource`   varchar(50)         DEFAULT NULL,
  `Technician` varchar(50)         DEFAULT NULL,
  `LOCKED`     varchar(3)          DEFAULT NULL,
  `LOCKEDDATE` timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP,
  `LOCKEDBY`   varchar(50)         DEFAULT NULL,
  PRIMARY KEY `locking_link` (`id`),
  UNIQUE KEY `id_pid` (`id`,`pid`)
  )
  ENGINE = InnoDB;

DROP TABLE IF EXISTS `login_mfa_registrations`;
CREATE TABLE `login_mfa_registrations` (
  `user_id`         bigint(20)     NOT NULL,
  `name`            varchar(30)    NOT NULL,
  `last_challenge`  datetime       DEFAULT NULL,
  `method`          varchar(31)    NOT NULL COMMENT 'Q&A, U2F, TOTP etc.',
  `var1`            varchar(4096)  NOT NULL DEFAULT '' COMMENT 'Question, U2F registration etc.',
  `var2`            varchar(256)   NOT NULL DEFAULT '' COMMENT 'Answer etc.',
  PRIMARY KEY (`user_id`, `name`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `benefit_eligibility`;
CREATE TABLE `benefit_eligibility` (
    `response_id` bigint(20) NOT NULL,
    `verification_id` bigint(20) NOT NULL,
    `type` varchar(4) DEFAULT NULL,
    `benefit_type` varchar(255) DEFAULT NULL,
    `start_date` date DEFAULT NULL,
    `end_date` date DEFAULT NULL,
    `coverage_level` varchar(255) DEFAULT NULL,
    `coverage_type` varchar(512) DEFAULT NULL,
    `plan_type` varchar(255) DEFAULT NULL,
    `plan_description` varchar(255) DEFAULT NULL,
    `coverage_period` varchar(255) DEFAULT NULL,
    `amount` decimal(5,2) DEFAULT NULL,
    `percent` decimal(3,2) DEFAULT NULL,
    `network_ind` varchar(2) DEFAULT NULL,
    `message` varchar(512) DEFAULT NULL,
    `response_status` enum('A','D') DEFAULT 'A',
    `response_create_date` date DEFAULT NULL,
    `response_modify_date` date DEFAULT NULL
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `oauth_clients`;
CREATE TABLE `oauth_clients` (
`client_id` varchar(80) NOT NULL,
`client_role` varchar(20) DEFAULT NULL,
`client_name` varchar(80) NOT NULL,
`client_secret` text,
`registration_token` varchar(80) DEFAULT NULL,
`registration_uri_path` varchar(40) DEFAULT NULL,
`register_date` datetime DEFAULT NULL,
`revoke_date` datetime DEFAULT NULL,
`contacts` text,
`redirect_uri` text,
`grant_types` varchar(80) DEFAULT NULL,
`scope` text,
`user_id` varchar(40) DEFAULT NULL,
`site_id` varchar(64) DEFAULT NULL,
`is_confidential` tinyint(1) NOT NULL DEFAULT '1',
`logout_redirect_uris` text,
`jwks_uri` text,
`jwks` text,
`initiate_login_uri` text,
`endorsements` text,
`policy_uri` text,
`tos_uri` text,
`is_enabled` tinyint(1) NOT NULL DEFAULT '0',
`skip_ehr_launch_authorization_flow` tinyint(1) NOT NULL DEFAULT '0',
`dsi_type` TINYINT UNSIGNED NOT NULL DEFAULT '1' COMMENT '0=none, 1=evidence-based,2=predictive',
PRIMARY KEY (`client_id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `oauth_trusted_user`;
CREATE TABLE `oauth_trusted_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` varchar(80) DEFAULT NULL,
`client_id` varchar(80) DEFAULT NULL,
`scope` text,
`persist_login` tinyint(1) DEFAULT '0',
`time` timestamp NULL DEFAULT NULL,
`code` text,
`session_cache` text,
`grant_type` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `accounts_id` (`user_id`),
KEY `clients_id` (`client_id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `x12_remote_tracker`;
CREATE TABLE `x12_remote_tracker` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`x12_partner_id` int(11) NOT NULL,
`x12_filename` varchar(255) NOT NULL,
`status` varchar(255) NOT NULL,
`claims` text,
`messages` text,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `export_job`;
CREATE TABLE `export_job` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uuid` binary(16) DEFAULT NULL,
  `user_id` varchar(40) NOT NULL,
  `client_id` varchar(80) NOT NULL,
  `status` varchar(40) NOT NULL,
  `start_time` datetime DEFAULT NULL,
  `resource_include_time` datetime DEFAULT NULL,
  `output_format` varchar(128) NOT NULL,
  `request_uri` varchar(128) NOT NULL,
  `resources` text,
  `output` text,
  `errors` text,
  `access_token_id` text,
  UNIQUE (`uuid`),
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB COMMENT='fhir export jobs';

DROP TABLE IF EXISTS `form_vital_details`;
CREATE TABLE `form_vital_details` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`form_id` bigint(20) NOT NULL COMMENT 'FK to vital_forms.id',
`vitals_column` varchar(64) NOT NULL COMMENT 'Column name from form_vitals',
`interpretation_list_id` varchar(100) DEFAULT NULL COMMENT 'FK to list_options.list_id for observation_interpretation',
`interpretation_option_id` varchar(100) DEFAULT NULL COMMENT 'FK to list_options.option_id for observation_interpretation',
`interpretation_codes` varchar(255) DEFAULT NULL COMMENT 'Archived original codes value from list_options observation_interpretation',
`interpretation_title` varchar(255) DEFAULT NULL COMMENT 'Archived original title value from list_options observation_interpretation',
`reason_code` VARCHAR(31) NULL DEFAULT NULL COMMENT 'Medical code explaining reason of the vital observation value in form codesystem:codetype;...;',
`reason_description` TEXT COMMENT 'Human readable text description of the reason_code column',
`reason_status` VARCHAR(31) NULL DEFAULT NULL COMMENT 'The status of the reason ie completed, in progress, etc',
PRIMARY KEY (`id`),
KEY `fk_form_id` (`form_id`),
KEY `fk_list_options_id` (`interpretation_list_id`, `interpretation_option_id`)
) ENGINE=InnoDB COMMENT='Detailed information of each vital_forms observation column';

DROP TABLE IF EXISTS `form_vitals_calculation`;
CREATE TABLE `form_vitals_calculation` (
   `id` int NOT NULL AUTO_INCREMENT,
   `uuid` binary(16) DEFAULT NULL,
   `encounter` bigint(20) DEFAULT NULL COMMENT 'fk to form_encounter.id',
   `pid` bigint(20) NOT NULL COMMENT 'fk to patient_data.pid',
   `date_start` datetime DEFAULT NULL,
   `date_end` datetime DEFAULT NULL,
   `created_at` datetime DEFAULT NULL,
   `updated_at` datetime DEFAULT NULL,
   `created_by` bigint(20) DEFAULT NULL,
   `updated_by` bigint(20) DEFAULT NULL,
   `calculation_id` varchar(64) DEFAULT NULL COMMENT 'application identifier representing calculation e.g., bp-MeanLast5, bp-Mean3Day, bp-MeanEncounter',
   PRIMARY KEY (`id`),
   UNIQUE KEY `unq_uuid` (`uuid`),
   KEY `idx_pid` (`pid`),
   KEY `idx_encounter` (`encounter`),
   KEY `idx_calculation_id` (`calculation_id`)
) ENGINE=InnoDB COMMENT = 'Main calculation records - one per logical calculation (e.g., average BP)';

DROP TABLE IF EXISTS `form_vitals_calculation_components`;
CREATE TABLE `form_vitals_calculation_components` (
    `id` int NOT NULL AUTO_INCREMENT,
    `fvc_uuid` binary(16) NOT NULL COMMENT 'fk to form_vitals_calculation.uuid',
    `vitals_column` varchar(64) NOT NULL COMMENT 'Component type: bps, bpd, pulse, etc.',
    `value` DECIMAL(12,6) DEFAULT NULL COMMENT 'Calculated numeric component value',
    `value_string` varchar(255) DEFAULT NULL COMMENT 'Calculated non-numeric component value',
    `value_unit` varchar(16) DEFAULT NULL COMMENT 'Unit for this component value',
    `component_order` int NOT NULL DEFAULT 0 COMMENT 'Display order for components',
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_fvc_component` (`fvc_uuid`, `vitals_column`),
    KEY `idx_vitals_column` (`vitals_column`),
    KEY `idx_component_order` (`fvc_uuid`, `component_order`)
) ENGINE=InnoDB COMMENT = 'Component values for calculations (e.g., systolic=120, diastolic=80)';

DROP TABLE IF EXISTS `form_vitals_calculation_form_vitals`;
CREATE TABLE `form_vitals_calculation_form_vitals` (
   `fvc_uuid` binary(16) NOT NULL COMMENT 'fk to form_vitals_calculation.uuid',
   `vitals_id` bigint(20) NOT NULL COMMENT 'fk to form_vitals.id',
   PRIMARY KEY (`fvc_uuid`, `vitals_id`)
) ENGINE=InnoDB COMMENT = 'Join table between form_vitals_calculation and form_vitals table representing the derivative observation relationship between the calculation and the source records';

DROP TABLE IF EXISTS `jwt_grant_history`;
CREATE TABLE `jwt_grant_history` (
`id` INT NOT NULL AUTO_INCREMENT
 , `jti` VARCHAR(100) NOT NULL COMMENT 'Unique JWT id'
 , `client_id` VARCHAR(80) NOT NULL COMMENT 'FK oauth2_clients.client_id'
 , `jti_exp` TIMESTAMP NULL DEFAULT NULL COMMENT 'jwt exp claim when the jwt expires'
 , `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'datetime the grant authorization was requested'
 , PRIMARY KEY (`id`)
 , KEY `jti` (`jti`)
) ENGINE = InnoDB COMMENT = 'Holds JWT authorization grant ids to prevent replay attacks';

DROP TABLE IF EXISTS `document_templates`;
CREATE TABLE `document_templates` (
  `id` bigint(21) UNSIGNED NOT NULL AUTO_INCREMENT,
  `pid` bigint(20) DEFAULT NULL,
  `provider` int(11) UNSIGNED DEFAULT NULL,
  `encounter` int(11) UNSIGNED DEFAULT NULL,
  `modified_date` datetime NOT NULL DEFAULT current_timestamp(),
  `profile` varchar(63) NOT NULL,
  `category` varchar(63) NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `template_name` varchar(255) DEFAULT NULL,
  `status` varchar(31) DEFAULT NULL,
  `send_date` datetime NOT NULL DEFAULT current_timestamp(),
  `end_date` datetime DEFAULT NULL,
  `size` int(11) NOT NULL DEFAULT 0,
  `template_content` mediumblob DEFAULT NULL,
  `mime` varchar(31) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `location` (`pid`,`profile`,`category`,`template_name`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `document_template_profiles`;
CREATE TABLE `document_template_profiles` (
  `id` bigint(21) UNSIGNED NOT NULL AUTO_INCREMENT,
  `template_id` bigint(21) UNSIGNED NOT NULL,
  `profile` varchar(64) NOT NULL,
  `template_name` varchar(255) NOT NULL,
  `category` varchar(64) NOT NULL,
  `provider` int(11) UNSIGNED DEFAULT NULL,
  `modified_date` datetime NOT NULL DEFAULT current_timestamp(),
  `member_of` varchar(64) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 0,
  `recurring` tinyint(1) NOT NULL DEFAULT 1,
  `event_trigger` varchar(31) NOT NULL,
  `period` int(4) NOT NULL,
  `notify_trigger` varchar(31) NOT NULL,
  `notify_period` int(4) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `location` (`profile`,`template_id`,`member_of`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `valueset_oid`;
CREATE TABLE `valueset_oid` (
  `nqf_code` varchar(255) NOT NULL DEFAULT '',
  `code` varchar(255) NOT NULL DEFAULT '',
  `code_system` varchar(255) NOT NULL DEFAULT '',
  `code_type` varchar(255) DEFAULT NULL,
  `valueset` varchar(255) NOT NULL DEFAULT '',
  `description` varchar(255) DEFAULT NULL,
  `valueset_name` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`nqf_code`,`code`,`valueset`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `questionnaire_repository`;
CREATE TABLE `questionnaire_repository` (
    `id` bigint(21) UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid` binary(16) DEFAULT NULL,
    `questionnaire_id` varchar(255) DEFAULT NULL,
    `provider` int(11) UNSIGNED DEFAULT NULL,
    `version` int(11) NOT NULL DEFAULT 1,
    `created_date` datetime DEFAULT current_timestamp(),
    `modified_date` datetime DEFAULT current_timestamp(),
    `name` varchar(255) DEFAULT NULL,
    `type` varchar(63) NOT NULL DEFAULT 'Questionnaire',
    `profile` varchar(255) DEFAULT NULL,
    `active` tinyint(1) NOT NULL DEFAULT 1,
    `status` varchar(31) DEFAULT NULL,
    `source_url` text,
    `code` varchar(255) DEFAULT NULL,
    `code_display` text,
    `questionnaire` longtext,
    `lform` longtext,
    `category` VARCHAR(64) DEFAULT NULL COMMENT 'Used for grouping and organizing ',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uuid` (`uuid`),
    KEY `search` (`name`,`questionnaire_id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `questionnaire_response`;
CREATE TABLE `questionnaire_response` (
  `id` bigint(21) NOT NULL AUTO_INCREMENT,
  `uuid` binary(16) DEFAULT NULL,
  `response_id` varchar(255) DEFAULT NULL COMMENT 'A globally unique id for answer set. String version of UUID',
  `questionnaire_foreign_id` bigint(21) DEFAULT NULL COMMENT 'questionnaire_repository id for subject questionnaire',
  `questionnaire_id` varchar(255) DEFAULT NULL COMMENT 'Id for questionnaire content. String version of UUID',
  `questionnaire_name` varchar(255) DEFAULT NULL,
  `patient_id` int(11) DEFAULT NULL,
  `encounter` int(11) DEFAULT NULL COMMENT 'May or may not be associated with an encounter',
  `audit_user_id` int(11) DEFAULT NULL,
  `creator_user_id` int(11) DEFAULT NULL COMMENT 'user id if answers are provider',
  `create_time` datetime DEFAULT current_timestamp(),
  `last_updated` datetime DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT 1,
  `status` varchar(63) DEFAULT NULL COMMENT 'form current status. completed,active,incomplete',
  `questionnaire` longtext COMMENT 'the subject questionnaire json',
  `questionnaire_response` longtext COMMENT 'questionnaire response json',
  `form_response` longtext COMMENT 'lform answers array json',
  `form_score` int(11) DEFAULT NULL COMMENT 'Arithmetic scoring of questionnaires',
  `tscore` double DEFAULT NULL COMMENT 'T-Score',
  `error` double DEFAULT NULL COMMENT 'Standard error for the T-Score',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `response_index` (`response_id`, `patient_id`, `questionnaire_id`, `questionnaire_name`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `form_questionnaire_assessments`;
CREATE TABLE `form_questionnaire_assessments` (
  `id` bigint(21) NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT current_timestamp(),
  `response_id` TEXT COMMENT 'The foreign id to the questionnaire_response repository',
  `pid` bigint(21) NOT NULL DEFAULT 0,
  `user` varchar(255) DEFAULT NULL,
  `groupname` varchar(255) DEFAULT NULL,
  `authorized` tinyint(4) NOT NULL DEFAULT 0,
  `activity` tinyint(4) NOT NULL DEFAULT 1,
  `copyright` text,
  `form_name` varchar(255) DEFAULT NULL,
  `response_meta` text COMMENT 'json meta data for the response resource',
  `questionnaire_id` TEXT COMMENT 'The foreign id to the questionnaire_repository',
  `questionnaire` longtext,
  `questionnaire_response` longtext,
  `lform` longtext,
  `lform_response` longtext,
  `category` VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `onetime_auth`;
CREATE TABLE `onetime_auth` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `pid` bigint(20) DEFAULT NULL,
    `create_user_id` bigint(20) DEFAULT NULL,
    `context` varchar(64) DEFAULT NULL,
    `access_count` int(11) NOT NULL DEFAULT 0,
    `remote_ip` varchar(32) DEFAULT NULL,
    `onetime_pin` varchar(10) DEFAULT NULL COMMENT 'Max 10 numeric. Default 6',
    `onetime_token` tinytext,
    `redirect_url` tinytext,
    `expires` int(11) DEFAULT NULL,
    `date_created` datetime DEFAULT current_timestamp(),
    `last_accessed` datetime DEFAULT NULL,
    `scope` tinytext COMMENT 'context scope for this token',
    `profile` tinytext COMMENT 'profile of scope for this token',
    `onetime_actions` text COMMENT 'JSON array of actions that can be performed with this token',
    PRIMARY KEY (`id`),
    KEY `pid` (`pid`,`onetime_token`(32))
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `patient_settings`;
CREATE TABLE `patient_settings` (
     `setting_patient`  bigint(20)   NOT NULL DEFAULT 0,
     `setting_label` varchar(100)  NOT NULL,
     `setting_value` varchar(255) NOT NULL DEFAULT '',
     PRIMARY KEY (`setting_patient`, `setting_label`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `recent_patients`;
CREATE TABLE `recent_patients` (
    `user_id` varchar(40) NOT NULL,
    `patients` TEXT,
    PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `dsi_source_attributes`;
CREATE TABLE `dsi_source_attributes` (
 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `client_id` VARCHAR(80) NOT NULL,
 `list_id` VARCHAR(100) NOT NULL,
 `option_id` VARCHAR(100) NOT NULL,
 `clinical_rule_id` VARCHAR(31) DEFAULT NULL,
 `source_value` TEXT,
 `created_by` BIGINT(20) DEFAULT NULL,
 `last_updated_by` BIGINT(20) DEFAULT NULL,
 `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 `last_updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE (`list_id`, `option_id`, `client_id`)
) ENGINE=InnoDB COMMENT = 'Holds information about decision support intervention system source attributes';

-- Populate list with ONC default values
-- Populate list with ONC default values
-- -----------------------------------------------------
--
-- Table structure for table 'track_events'
--

DROP TABLE IF EXISTS `track_events`;
CREATE TABLE `track_events` (
    `id`                  INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_type`     TEXT,
    `event_label`    VARCHAR(255) DEFAULT NULL,
    `event_url`       TEXT,
    `event_target`  TEXT,
    `first_event`     DATETIME NULL,
    `last_event`     DATETIME NULL,
    `label_count`    INT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_event_label_target` (`event_label`, `event_url`(255), `event_target`(255))
) ENGINE = InnoDB COMMENT = 'Telemetry Event Data';

-- -----------------------------------------------------
--
-- Table structure for table `care_teams`
--

DROP TABLE IF EXISTS `care_teams`;
CREATE TABLE `care_teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` binary(16) DEFAULT NULL,
  `pid` int(11) NOT NULL COMMENT 'fk to patient_data.pid',
  `status` varchar(100) DEFAULT 'active' COMMENT 'fk to list_options.option_id where list_id=Care_Team_Status',
  `team_name` varchar(255) DEFAULT NULL,
  `note` text,
  `date_created` datetime DEFAULT current_timestamp(),
  `date_updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_by` BIGINT(20) COMMENT 'fk to users.id for user who created this record',
  `updated_by` BIGINT(20) COMMENT 'fk to users.id for user who last updated this record',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `care_team_member`;
CREATE TABLE `care_team_member` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `care_team_id` int(11) NOT NULL,
    `user_id` BIGINT(20) COMMENT 'fk to users.id represents a provider or staff member',
    `contact_id` BIGINT(20) COMMENT 'fk to contact.id which represents a contact person not in users or facility table',
    `role` varchar(50) NOT NULL COMMENT 'fk to list_options.option_id WHERE list_id=care_team_roles',
    `facility_id` BIGINT(20) COMMENT 'fk to facility.id represents an organization or location',
    `provider_since` date NULL,
    `status` varchar(100) DEFAULT 'active' COMMENT 'fk to list_options.option_id where list_id=Care_Team_Status',
    `date_created` datetime DEFAULT current_timestamp(),
    `date_updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    `created_by` BIGINT(20) COMMENT 'fk to users.id and is the user that added this team member',
    `updated_by` BIGINT(20) COMMENT 'fk to users.id and is the user that last updated this team member',
    `note` text,
    PRIMARY KEY (`id`),
    UNIQUE KEY `care_team_member_unique` (`care_team_id`, `user_id`, `facility_id`, `contact_id`)
) ENGINE=InnoDB COMMENT='Stores members of a care team for a patient';
-- ----------------------------------------------------------------
--
-- Care Team Roles (based on HL7/USCDI v3)
--

-- ---------------------------------------------------------------------------------------------------------------------------------
--
-- 2023 Performance Period Measures

-- ---------------------------------------------------------------------------------------------------------------------------------
--
-- 2024 Performance Period Measures

-- ---------------------------------------------------------------------------------------------------------------------------------
--
-- 2025 Performance Period Measures

-- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
-- Periods List

-- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Social History SDOH

DROP TABLE IF EXISTS `form_history_sdoh`;
CREATE TABLE `form_history_sdoh`
(
    `id`                              bigint(21) UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid`                            binary(16)                   DEFAULT NULL,
    `pid`                             int(10) UNSIGNED    NOT NULL,
    `encounter`                       int(10) UNSIGNED             DEFAULT NULL,
    `created_at`                      datetime            NOT NULL DEFAULT current_timestamp(),
    `updated_at`                      datetime            NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    `created_by`                      int(10) UNSIGNED             DEFAULT NULL COMMENT 'fk to users.id user that created this record',
    `updated_by`                      int(10) UNSIGNED             DEFAULT NULL COMMENT 'fk to users.id user that last modified this record',
    `assessment_date`                 date                         DEFAULT NULL,
    `screening_tool`                  varchar(255)                 DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_instruments represents the assessment tool used to administer this assessment',
    `assessor`                        varchar(255)                 DEFAULT NULL COMMENT 'fk to users.username the user that administered the assessment',
    `food_insecurity`                 varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_food_insecurity_risk',
    `food_insecurity_notes`           text,
    `housing_instability`             varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_housing_worry',
    `housing_instability_notes`       text,
    `transportation_insecurity`       varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_transportation_barrier',
    `transportation_insecurity_notes` text,
    `utilities_insecurity`            varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_utilities_shutoff',
    `utilities_insecurity_notes`      text,
    `interpersonal_safety`            varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_financial_strain',
    `interpersonal_safety_notes`      text,
    `financial_strain`                varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_financial_strain',
    `financial_strain_notes`          text,
    `social_isolation`                varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_social_isolation_freq',
    `social_isolation_notes`          text,
    `childcare_needs`                 varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_childcare_needs',
    `childcare_needs_notes`           text,
    `digital_access`                  varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_digital_access',
    `digital_access_notes`            text,
    `employment_status`               varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_food_insecurity_risk',
    `education_level`                 varchar(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_education_level',
    `caregiver_status`                varchar(20)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_food_insecurity_risk',
    `veteran_status`                  varchar(20)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=sdoh_food_insecurity_risk',
    `pregnancy_status`                varchar(20)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=pregnancy_status',
    `pregnancy_edd`                   date                         DEFAULT NULL COMMENT 'Estimated due date for pregnancy',
    `pregnancy_intent`                VARCHAR(32)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=pregnancy_intent Pregnancy Intent Over Next Year (codes from PregnancyIntent list)',
    `postpartum_status`               varchar(20)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=postpartum_status',
    `postpartum_end`                  date                         DEFAULT NULL COMMENT 'PostPartum end date',
    `goals`                           text,
    `interventions`                   text,
    `instrument_score`                INT                          DEFAULT NULL,
    `positive_domain_count`           INT                          DEFAULT NULL,
    `declined_flag`                   TINYINT(1)                   DEFAULT NULL,
    `disability_status`               VARCHAR(50)                  DEFAULT NULL COMMENT 'fk to list_options.option_id WHERE list_id=disability_status',
    `disability_status_notes`         TEXT,
    `disability_scale`                TEXT,
    `hunger_q1`                       VARCHAR(50)                  DEFAULT NULL COMMENT 'LOINC 88122-7 response' COMMENT 'fk to list_options.option_id WHERE list_id=vital_signs_answers',
    `hunger_q2`                       VARCHAR(50)                  DEFAULT NULL COMMENT 'LOINC 88123-5 response'COMMENT 'fk to list_options.option_id WHERE list_id=vital_signs_answers',
    `hunger_score`                    INT                          DEFAULT NULL COMMENT 'Calculated HVS score',
    PRIMARY KEY (`id`),
    KEY `uuid_idx` (`uuid`),
    KEY `pid_idx` (`pid`),
    KEY `assessment_idx` (`assessment_date`),
    KEY `encounter_idx` (`encounter`)
) ENGINE = InnoDB;

--
-- Table structure for linking clinical notes to documents
--
DROP TABLE IF EXISTS `clinical_notes_documents`;
CREATE TABLE  `clinical_notes_documents` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `clinical_note_id` bigint(20) NOT NULL COMMENT 'Foreign key to form_clinical_notes.id',
  `document_id` bigint(20) NOT NULL COMMENT 'Foreign key to documents.id',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the link was created',
  `created_by` varchar(255) DEFAULT NULL COMMENT 'Username who created the link',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_note_document` (`clinical_note_id`, `document_id`),
  KEY `idx_clinical_note_id` (`clinical_note_id`),
  KEY `idx_document_id` (`document_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB COMMENT='Links clinical notes to patient documents';

--
-- Table structure for linking clinical notes to procedure results
--
DROP TABLE IF EXISTS `clinical_notes_procedure_results`;
CREATE TABLE `clinical_notes_procedure_results` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `clinical_note_id` bigint(20) NOT NULL COMMENT 'Foreign key to form_clinical_notes.id',
  `procedure_result_id` bigint(20) NOT NULL COMMENT 'Foreign key to procedure_result.procedure_result_id',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the link was created',
  `created_by` varchar(255) DEFAULT NULL COMMENT 'Username who created the link',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_note_result` (`clinical_note_id`, `procedure_result_id`),
  KEY `idx_clinical_note_id` (`clinical_note_id`),
  KEY `idx_procedure_result_id` (`procedure_result_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB COMMENT='Links clinical notes to procedure results/lab values';

-- Patient Preferences Database Schema
-- Uses OpenEMR's list_options table for LOINC codes
-- Table for storing patient treatment intervention preferences
DROP TABLE IF EXISTS `patient_treatment_intervention_preferences`;
CREATE TABLE `patient_treatment_intervention_preferences` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `uuid` binary(16) DEFAULT NULL,
    `patient_id` int(11) NOT NULL COMMENT 'fk to patient_data.pid',
    `observation_code` varchar(50) NOT NULL COMMENT 'LOINC code',
    `observation_code_text` varchar(255) DEFAULT NULL,
    `value_type` enum('coded','text','boolean') DEFAULT 'coded',
    `value_code` varchar(50) DEFAULT NULL COMMENT 'fk to preference_value_sets.answer_code',
    `value_code_system` varchar(255) DEFAULT NULL COMMENT 'fk to preference_value_sets.answer_system',
    `value_display` varchar(255) DEFAULT NULL COMMENT 'fk to preference_value_sets.answer_display',
    `value_text` text,
    `value_boolean` tinyint(1) DEFAULT NULL,
    `effective_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `status` varchar(20) DEFAULT 'final' COMMENT 'valid options are final,amended,preliminary',
    `note` text,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_uuid` (`uuid`),
    KEY `patient_id` (`patient_id`),
    KEY `observation_code` (`observation_code`),
    KEY `status` (`status`)
    ) ENGINE=InnoDB;

    -- Table for storing patient care experience preferences
DROP TABLE IF EXISTS `patient_care_experience_preferences`;
CREATE TABLE `patient_care_experience_preferences` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `uuid` binary(16) DEFAULT NULL,
    `patient_id` int(11) NOT NULL,
    `observation_code` varchar(50) NOT NULL COMMENT 'LOINC code',
    `observation_code_text` varchar(255) DEFAULT NULL,
    `value_type` enum('coded','text','boolean') DEFAULT 'coded',
    `value_code` varchar(50) DEFAULT NULL COMMENT 'fk to preference_value_sets.answer_code',
    `value_code_system` varchar(255) DEFAULT NULL COMMENT 'fk to preference_value_sets.answer_system',
    `value_display` varchar(255) DEFAULT NULL COMMENT 'fk to preference_value_sets.answer_display',
    `value_text` text,
    `value_boolean` tinyint(1) DEFAULT NULL,
    `effective_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `status` varchar(20) DEFAULT 'final' COMMENT 'valid options are final,amended,preliminary',
    `note` text,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_uuid` (`uuid`),
    KEY `patient_id` (`patient_id`),
    KEY `observation_code` (`observation_code`),
    KEY `status` (`status`)
) ENGINE=InnoDB;

    -- ------------------------------------- Parent lists under `lists`--------------------------------------------------------------------
    INSERT INTO `list_options` (`list_id`,`option_id`,`title`,`seq`)
    VALUES  ('lists','treatment_intervention_preferences','Treatment Intervention Preferences',1);
    INSERT INTO `list_options` (`list_id`,`option_id`,`title`,`seq`,`notes`,`codes`,`activity`) VALUES
    ('treatment_intervention_preferences','81329-5','Thoughts on resuscitation (CPR)',10,'tip_resuscitation_answers','LOINC:81329-5',1),
    ('treatment_intervention_preferences','81330-3','Thoughts on intubation',20,'tip_intubation_answers','LOINC:81330-3',1),
    ('treatment_intervention_preferences','81331-1','Thoughts on tube feeding',30,'tip_tubefeeding_answers','LOINC:81331-1',1),
    ('treatment_intervention_preferences','81332-9','Thoughts on IV fluid and support',40,'tip_ivfluids_answers','LOINC:81332-9',1),
    ('treatment_intervention_preferences','81333-7','Thoughts on antibiotics',50,'tip_antibiotics_answers','LOINC:81333-7',1),
    ('treatment_intervention_preferences','75773-2','Goals, preferences, and priorities for medical treatment [Reported]',5,'tip_general_answers','LOINC:75773-2',1),
    ('treatment_intervention_preferences','81336-0','Patient''s thoughts on cardiopulmonary bypass',60,'tip_bypass_answers','LOINC:81336-0',1),
    ('treatment_intervention_preferences','81337-8','Patient''s thoughts on mechanical ventilation',70,'tip_ventilation_answers','LOINC:81337-8',1),
    ('treatment_intervention_preferences','81376-6','Upon death organ donation consent',80,'tip_organ_donation_answers','LOINC:81376-6',1),
    ('treatment_intervention_preferences','81378-2','Patient Healthcare goals',90,'tip_healthcare_goals_text','LOINC:81378-2',1);

    INSERT INTO `list_options` (`list_id`,`option_id`,`title`,`seq`)
    VALUES ('lists','care_experience_preferences','Care Experience Preferences',1);
    INSERT INTO `list_options` (`list_id`,`option_id`,`title`,`seq`,`notes`,`codes`,`activity`) VALUES
    ('care_experience_preferences','95541-9','Care experience preference',10,'cep_general_answers','LOINC:95541-9',1),
    ('care_experience_preferences','81364-2','Religious or cultural beliefs (reported)',20,'cep_religious_answers','LOINC:81364-2',1),
    ('care_experience_preferences','81365-9','Religious/cultural affiliation contact to notify (reported)',30,'cep_religious_contact_answers','LOINC:81365-9',1),
    ('care_experience_preferences','103980-9','Preferred pharmacy',40,'cep_pharmacy_answers','LOINC:103980-9',1),
    ('care_experience_preferences','81338-6','Patient goals, preferences & priorities for care experience',90,'cep_overall_narrative','LOINC:81338-6',1),
    ('care_experience_preferences','81342-8','Care experience preference under certain health conditions',50,'cep_conditional_answers','LOINC:81342-8',1),
    ('care_experience_preferences','81343-6','Care experience preference at end of life',60,'cep_endoflife_answers','LOINC:81343-6',1),
    ('care_experience_preferences','81362-6','Preferred location for healthcare',70,'cep_location_answers','LOINC:81362-6',1),
    ('care_experience_preferences','81363-4','Preferred healthcare professional',80,'cep_professional_answers','LOINC:81363-4',1);
 -- Value sets table for coded answers
DROP TABLE IF EXISTS `preference_value_sets`;
CREATE TABLE `preference_value_sets` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `loinc_code` varchar(50) NOT NULL,
    `answer_code` varchar(100) NOT NULL,
    `answer_system` varchar(255) NOT NULL,
    `answer_display` varchar(255) NOT NULL,
    `answer_definition` text,
    `sort_order` int(11) DEFAULT 0,
    `active` tinyint(1) DEFAULT 1,
    PRIMARY KEY (`id`),
    KEY `loinc_code` (`loinc_code`)
    ) ENGINE=InnoDB COMMENT='Answer lists for preference codes';

    INSERT INTO `preference_value_sets`
    (`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('81329-5','LA33470-8','http://loinc.org','Yes CPR',1,1),
    ('81329-5','LA33471-6','http://loinc.org','No CPR (Do Not Attempt Resuscitation)',2,1),
    ('81329-5','UNK','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Unknown',99,1),
    ('81329-5','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other (see free text)',100,1),
    ('81330-3','373066001','http://snomed.info/sct','Yes',1,1),
    ('81330-3','373067005','http://snomed.info/sct','No',2,1),
    ('81330-3','UNK','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Unknown',99,1),
    ('81330-3','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other (see free text)',100,1),
    ('81331-1','373066001','http://snomed.info/sct','Yes',1,1),
    ('81331-1','373067005','http://snomed.info/sct','No',2,1),
    ('81331-1','UNK','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Unknown',99,1),
    ('81331-1','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other (see free text)',100,1),
    ('81332-9','373066001','http://snomed.info/sct','Yes',1,1),
    ('81332-9','373067005','http://snomed.info/sct','No',2,1),
    ('81332-9','UNK','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Unknown',99,1),
    ('81332-9','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other (see free text)',100,1),
    ('81333-7','373066001','http://snomed.info/sct','Yes',1,1),
    ('81333-7','373067005','http://snomed.info/sct','No',2,1),
    ('81333-7','UNK','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Unknown',99,1),
    ('81333-7','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other (see free text)',100,1),
    ('81364-2','160542002','http://snomed.info/sct','Muslim',1,1),
    ('81364-2','160540005','http://snomed.info/sct','Jewish',2,1),
    ('81364-2','160539006','http://snomed.info/sct','Christian',3,1),
    ('81364-2','160538003','http://snomed.info/sct','Hindu',4,1),
    ('81364-2','160543007','http://snomed.info/sct','Buddhist',5,1),
    ('81364-2','276119007','http://snomed.info/sct','No religion',6,1),
    ('81364-2','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other',99,1),
    ('81365-9','373066001','http://snomed.info/sct','Yes',1,1),
    ('81365-9','373067005','http://snomed.info/sct','No',2,1),
    ('81365-9','UNK','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Unknown',99,1),
    ('81365-9','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other (see free text)',100,1),
    ('103980-9','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other (see free text)',100,1),
    ('95541-9','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other (see free text)',100,1),
    ('81338-6','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other (see free text)',100,1);
    INSERT INTO `preference_value_sets`
    (`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('75773-2','385643006','http://snomed.info/sct','Prefers full resuscitation',1,1),
    ('75773-2','385644000','http://snomed.info/sct','Prefers limited resuscitation',2,1),
    ('75773-2','304253006','http://snomed.info/sct','Does not want resuscitation',3,1),
    ('75773-2','395092004','http://snomed.info/sct','Prefers aggressive treatment',4,1),
    ('75773-2','395093009','http://snomed.info/sct','Prefers comfort measures only',5,1),
    ('75773-2','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other',99,1);
    -- Cardiopulmonary Bypass (81336-0)
    INSERT INTO `preference_value_sets`
    (`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('81336-0','373066001','http://snomed.info/sct','Yes',1,1),
    ('81336-0','373067005','http://snomed.info/sct','No',2,1),
    ('81336-0','261665006','http://snomed.info/sct','Unknown',98,1),
    ('81336-0','UNK','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Unknown',99,1);
    -- Mechanical Ventilation (81337-8)
    INSERT INTO `preference_value_sets`
    (`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('81337-8','LA33470-8','http://loinc.org','Yes ventilation',1,1),
    ('81337-8','LA33471-6','http://loinc.org','No ventilation',2,1),
    ('81337-8','LA32996-3','http://loinc.org','Trial period of ventilation',3,1),
    ('81337-8','UNK','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Unknown',99,1);
    -- Organ Donation (81376-6)
    INSERT INTO `preference_value_sets`
    (`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('81376-6','LA33-6','http://loinc.org','Yes',1,1),
    ('81376-6','LA32-8','http://loinc.org','No',2,1),
    ('81376-6','LA32948-4','http://loinc.org','Yes, but only certain organs/tissues',3,1),
    ('81376-6','LA4489-6','http://loinc.org','Unknown',99,1);
    -- Care Under Certain Health Conditions (81342-8)
    INSERT INTO `preference_value_sets`
    (`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('81342-8','LA33474-0','http://loinc.org','If mentally incapacitated',1,1),
    ('81342-8','LA33475-7','http://loinc.org','If terminally ill',2,1),
    ('81342-8','LA33476-5','http://loinc.org','If permanently unconscious',3,1),
    ('81342-8','LA33477-3','http://loinc.org','If severe chronic illness',4,1),
    ('81342-8','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other condition',99,1);
    -- Care at End of Life (81343-6)
    INSERT INTO `preference_value_sets`
    (`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('81343-6','395092004','http://snomed.info/sct','Prefers aggressive treatment',1,1),
    ('81343-6','395093009','http://snomed.info/sct','Prefers comfort measures only',2,1),
    ('81343-6','385644000','http://snomed.info/sct','Limited intervention',3,1),
    ('81343-6','225270000','http://snomed.info/sct','Hospice care',4,1),
    ('81343-6','385656005','http://snomed.info/sct','Home death preferred',5,1),
    ('81343-6','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other',99,1);
    -- Preferred Location (81362-6)
    INSERT INTO `preference_value_sets`
    (`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('81362-6','264362003','http://snomed.info/sct','Home',1,1),
    ('81362-6','22232009','http://snomed.info/sct','Hospital',2,1),
    ('81362-6','284546000','http://snomed.info/sct','Hospice',3,1),
    ('81362-6','42665001','http://snomed.info/sct','Nursing home',4,1),
    ('81362-6','413456002','http://snomed.info/sct','Adult day care center',5,1),
    ('81362-6','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other location',99,1);
    -- Preferred Healthcare Professional (81363-4)
    INSERT INTO `preference_value_sets`
    (`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('81363-4','309343006','http://snomed.info/sct','Physician',1,1),
    ('81363-4','106292003','http://snomed.info/sct','Professional nurse',2,1),
    ('81363-4','224571005','http://snomed.info/sct','Nurse practitioner',3,1),
    ('81363-4','449161006','http://snomed.info/sct','Physician assistant',4,1),
    ('81363-4','768730001','http://snomed.info/sct','Home health aide',5,1),
    ('81363-4','OTH','http://terminology.hl7.org/CodeSystem/v3-NullFlavor','Other provider',99,1);
    -- Add more religious/cultural options
    INSERT INTO `preference_value_sets`
    (`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('81364-2','309884000','http://snomed.info/sct','Atheist',7,1),
    ('81364-2','160234004','http://snomed.info/sct','Agnostic',8,1),
    ('81364-2','428821008','http://snomed.info/sct','Latter Day Saints',9,1),
    ('81364-2','80587008','http://snomed.info/sct','Jehovah''s Witness',10,1),
    ('81364-2','309687009','http://snomed.info/sct','Baptist',11,1),
    ('81364-2','160540005','http://snomed.info/sct','Sikh',12,1),
    ('81364-2','LA14063-6','http://loinc.org','Prefer not to answer',98,1);
    -- General Preferences
    INSERT INTO preference_value_sets(`loinc_code`,`answer_code`,`answer_system`,`answer_display`,`sort_order`,`active`) VALUES
    ('95541-9', 314433002, 'http://snomed.info/sct', 'Preference for health professional (finding)', 1, 1);

-- Doctrine Migrations tracking
-- Their tooling will create this automatically, but having it here simplifies
-- schema comparisons.
DROP TABLE IF EXISTS `migrations`;
CREATE TABLE `migrations` (
    `version` varchar(191) NOT NULL,
    `executed_at` datetime DEFAULT NULL,
    `execution_duration_ms` int DEFAULT NULL,
    PRIMARY KEY (`version`)
) ENGINE=InnoDB;