civicrm-common 4.7.30+dfsg-1ubuntu1 / usr / share / civicrm / sql / civicrm.mysql

-- +--------------------------------------------------------------------+
-- | CiviCRM version 4.7                                                |
-- +--------------------------------------------------------------------+
-- | Copyright CiviCRM LLC (c) 2004-2017                                |
-- +--------------------------------------------------------------------+
-- | This file is a part of CiviCRM.                                    |
-- |                                                                    |
-- | CiviCRM is free software; you can copy, modify, and distribute it  |
-- | under the terms of the GNU Affero General Public License           |
-- | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
-- |                                                                    |
-- | CiviCRM is distributed in the hope that it will be useful, but     |
-- | WITHOUT ANY WARRANTY; without even the implied warranty of         |
-- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
-- | See the GNU Affero General Public License for more details.        |
-- |                                                                    |
-- | You should have received a copy of the GNU Affero General Public   |
-- | License and the CiviCRM Licensing Exception along                  |
-- | with this program; if not, contact CiviCRM LLC                     |
-- | at info[AT]civicrm[DOT]org. If you have questions about the        |
-- | GNU Affero General Public License or the licensing of CiviCRM,     |
-- | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
-- +--------------------------------------------------------------------+
--
-- Generated from schema.tpl
-- DO NOT EDIT.  Generated by CRM_Core_CodeGen
--


-- +--------------------------------------------------------------------+
-- | CiviCRM version 4.7                                                |
-- +--------------------------------------------------------------------+
-- | Copyright CiviCRM LLC (c) 2004-2017                                |
-- +--------------------------------------------------------------------+
-- | This file is a part of CiviCRM.                                    |
-- |                                                                    |
-- | CiviCRM is free software; you can copy, modify, and distribute it  |
-- | under the terms of the GNU Affero General Public License           |
-- | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
-- |                                                                    |
-- | CiviCRM is distributed in the hope that it will be useful, but     |
-- | WITHOUT ANY WARRANTY; without even the implied warranty of         |
-- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
-- | See the GNU Affero General Public License for more details.        |
-- |                                                                    |
-- | You should have received a copy of the GNU Affero General Public   |
-- | License and the CiviCRM Licensing Exception along                  |
-- | with this program; if not, contact CiviCRM LLC                     |
-- | at info[AT]civicrm[DOT]org. If you have questions about the        |
-- | GNU Affero General Public License or the licensing of CiviCRM,     |
-- | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
-- +--------------------------------------------------------------------+
--
-- Generated from drop.tpl
-- DO NOT EDIT.  Generated by CRM_Core_CodeGen
--
-- /*******************************************************
-- *
-- * Clean up the exisiting tables
-- *
-- *******************************************************/

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `civicrm_line_item`;
DROP TABLE IF EXISTS `civicrm_pledge_payment`;
DROP TABLE IF EXISTS `civicrm_events_in_carts`;
DROP TABLE IF EXISTS `civicrm_participant_payment`;
DROP TABLE IF EXISTS `civicrm_participant`;
DROP TABLE IF EXISTS `civicrm_event`;
DROP TABLE IF EXISTS `civicrm_membership_payment`;
DROP TABLE IF EXISTS `civicrm_entity_financial_trxn`;
DROP TABLE IF EXISTS `civicrm_contribution_soft`;
DROP TABLE IF EXISTS `civicrm_contribution_product`;
DROP TABLE IF EXISTS `civicrm_contribution`;
DROP TABLE IF EXISTS `civicrm_group_contact`;
DROP TABLE IF EXISTS `civicrm_loc_block`;
DROP TABLE IF EXISTS `civicrm_address`;
DROP TABLE IF EXISTS `civicrm_pcp_block`;
DROP TABLE IF EXISTS `civicrm_price_field_value`;
DROP TABLE IF EXISTS `civicrm_price_field`;
DROP TABLE IF EXISTS `civicrm_case_activity`;
DROP TABLE IF EXISTS `civicrm_activity_contact`;
DROP TABLE IF EXISTS `civicrm_activity`;
DROP TABLE IF EXISTS `civicrm_membership_log`;
DROP TABLE IF EXISTS `civicrm_membership`;
DROP TABLE IF EXISTS `civicrm_financial_trxn`;
DROP TABLE IF EXISTS `civicrm_contribution_recur`;
DROP TABLE IF EXISTS `civicrm_mailing_event_unsubscribe`;
DROP TABLE IF EXISTS `civicrm_mailing_event_trackable_url_open`;
DROP TABLE IF EXISTS `civicrm_mailing_event_reply`;
DROP TABLE IF EXISTS `civicrm_mailing_event_opened`;
DROP TABLE IF EXISTS `civicrm_mailing_event_forward`;
DROP TABLE IF EXISTS `civicrm_mailing_event_delivered`;
DROP TABLE IF EXISTS `civicrm_mailing_event_bounce`;
DROP TABLE IF EXISTS `civicrm_mailing_event_queue`;
DROP TABLE IF EXISTS `civicrm_mailing_spool`;
DROP TABLE IF EXISTS `civicrm_mailing_recipients`;
DROP TABLE IF EXISTS `civicrm_mailing_job`;
DROP TABLE IF EXISTS `civicrm_mailing_trackable_url`;
DROP TABLE IF EXISTS `civicrm_mailing_group`;
DROP TABLE IF EXISTS `civicrm_mailing`;
DROP TABLE IF EXISTS `civicrm_relationship`;
DROP TABLE IF EXISTS `civicrm_dashboard_contact`;
DROP TABLE IF EXISTS `civicrm_action_log`;
DROP TABLE IF EXISTS `civicrm_action_schedule`;
DROP TABLE IF EXISTS `civicrm_uf_join`;
DROP TABLE IF EXISTS `civicrm_uf_field`;
DROP TABLE IF EXISTS `civicrm_uf_group`;
DROP TABLE IF EXISTS `civicrm_entity_tag`;
DROP TABLE IF EXISTS `civicrm_discount`;
DROP TABLE IF EXISTS `civicrm_dashboard`;
DROP TABLE IF EXISTS `civicrm_county`;
DROP TABLE IF EXISTS `civicrm_price_set_entity`;
DROP TABLE IF EXISTS `civicrm_price_set`;
DROP TABLE IF EXISTS `civicrm_report_instance`;
DROP TABLE IF EXISTS `civicrm_pledge`;
DROP TABLE IF EXISTS `civicrm_case_contact`;
DROP TABLE IF EXISTS `civicrm_case`;
DROP TABLE IF EXISTS `civicrm_membership_block`;
DROP TABLE IF EXISTS `civicrm_membership_type`;
DROP TABLE IF EXISTS `civicrm_sms_provider`;
DROP TABLE IF EXISTS `civicrm_payment_token`;
DROP TABLE IF EXISTS `civicrm_payment_processor`;
DROP TABLE IF EXISTS `civicrm_contribution_widget`;
DROP TABLE IF EXISTS `civicrm_premiums_product`;
DROP TABLE IF EXISTS `civicrm_product`;
DROP TABLE IF EXISTS `civicrm_contribution_page`;
DROP TABLE IF EXISTS `civicrm_mailing_event_confirm`;
DROP TABLE IF EXISTS `civicrm_mailing_event_subscribe`;
DROP TABLE IF EXISTS `civicrm_group_organization`;
DROP TABLE IF EXISTS `civicrm_group_nesting`;
DROP TABLE IF EXISTS `civicrm_group_contact_cache`;
DROP TABLE IF EXISTS `civicrm_subscription_history`;
DROP TABLE IF EXISTS `civicrm_group`;
DROP TABLE IF EXISTS `civicrm_acl_cache`;
DROP TABLE IF EXISTS `civicrm_status_pref`;
DROP TABLE IF EXISTS `civicrm_word_replacement`;
DROP TABLE IF EXISTS `civicrm_print_label`;
DROP TABLE IF EXISTS `civicrm_setting`;
DROP TABLE IF EXISTS `civicrm_website`;
DROP TABLE IF EXISTS `civicrm_openid`;
DROP TABLE IF EXISTS `civicrm_timezone`;
DROP TABLE IF EXISTS `civicrm_uf_match`;
DROP TABLE IF EXISTS `civicrm_tag`;
DROP TABLE IF EXISTS `civicrm_state_province`;
DROP TABLE IF EXISTS `civicrm_phone`;
DROP TABLE IF EXISTS `civicrm_option_value`;
DROP TABLE IF EXISTS `civicrm_note`;
DROP TABLE IF EXISTS `civicrm_navigation`;
DROP TABLE IF EXISTS `civicrm_menu`;
DROP TABLE IF EXISTS `civicrm_mapping_field`;
DROP TABLE IF EXISTS `civicrm_mail_settings`;
DROP TABLE IF EXISTS `civicrm_log`;
DROP TABLE IF EXISTS `civicrm_job_log`;
DROP TABLE IF EXISTS `civicrm_job`;
DROP TABLE IF EXISTS `civicrm_im`;
DROP TABLE IF EXISTS `civicrm_entity_file`;
DROP TABLE IF EXISTS `civicrm_email`;
DROP TABLE IF EXISTS `civicrm_domain`;
DROP TABLE IF EXISTS `civicrm_custom_field`;
DROP TABLE IF EXISTS `civicrm_custom_group`;
DROP TABLE IF EXISTS `civicrm_country`;
DROP TABLE IF EXISTS `civicrm_cache`;
DROP TABLE IF EXISTS `civicrm_cxn`;
DROP TABLE IF EXISTS `civicrm_pcp`;
DROP TABLE IF EXISTS `civicrm_queue_item`;
DROP TABLE IF EXISTS `civicrm_pledge_block`;
DROP TABLE IF EXISTS `civicrm_tell_friend`;
DROP TABLE IF EXISTS `civicrm_grant`;
DROP TABLE IF EXISTS `civicrm_case_type`;
DROP TABLE IF EXISTS `civicrm_dedupe_exception`;
DROP TABLE IF EXISTS `civicrm_dedupe_rule`;
DROP TABLE IF EXISTS `civicrm_dedupe_rule_group`;
DROP TABLE IF EXISTS `civicrm_event_carts`;
DROP TABLE IF EXISTS `civicrm_participant_status_type`;
DROP TABLE IF EXISTS `civicrm_survey`;
DROP TABLE IF EXISTS `civicrm_campaign_group`;
DROP TABLE IF EXISTS `civicrm_campaign`;
DROP TABLE IF EXISTS `civicrm_membership_status`;
DROP TABLE IF EXISTS `civicrm_financial_item`;
DROP TABLE IF EXISTS `civicrm_entity_financial_account`;
DROP TABLE IF EXISTS `civicrm_financial_type`;
DROP TABLE IF EXISTS `civicrm_payment_processor_type`;
DROP TABLE IF EXISTS `civicrm_financial_account`;
DROP TABLE IF EXISTS `civicrm_currency`;
DROP TABLE IF EXISTS `civicrm_premiums`;
DROP TABLE IF EXISTS `civicrm_mailing_bounce_pattern`;
DROP TABLE IF EXISTS `civicrm_mailing_bounce_type`;
DROP TABLE IF EXISTS `civicrm_mailing_abtest`;
DROP TABLE IF EXISTS `civicrm_mailing_component`;
DROP TABLE IF EXISTS `civicrm_entity_batch`;
DROP TABLE IF EXISTS `civicrm_batch`;
DROP TABLE IF EXISTS `civicrm_contact_type`;
DROP TABLE IF EXISTS `civicrm_saved_search`;
DROP TABLE IF EXISTS `civicrm_relationship_type`;
DROP TABLE IF EXISTS `civicrm_acl_contact_cache`;
DROP TABLE IF EXISTS `civicrm_contact`;
DROP TABLE IF EXISTS `civicrm_acl_entity_role`;
DROP TABLE IF EXISTS `civicrm_acl`;
DROP TABLE IF EXISTS `civicrm_recurring_entity`;
DROP TABLE IF EXISTS `civicrm_action_mapping`;
DROP TABLE IF EXISTS `civicrm_prevnext_cache`;
DROP TABLE IF EXISTS `civicrm_persistent`;
DROP TABLE IF EXISTS `civicrm_component`;
DROP TABLE IF EXISTS `civicrm_worldregion`;
DROP TABLE IF EXISTS `civicrm_system_log`;
DROP TABLE IF EXISTS `civicrm_preferences_date`;
DROP TABLE IF EXISTS `civicrm_option_group`;
DROP TABLE IF EXISTS `civicrm_msg_template`;
DROP TABLE IF EXISTS `civicrm_mapping`;
DROP TABLE IF EXISTS `civicrm_managed`;
DROP TABLE IF EXISTS `civicrm_location_type`;
DROP TABLE IF EXISTS `civicrm_file`;
DROP TABLE IF EXISTS `civicrm_extension`;
DROP TABLE IF EXISTS `civicrm_address_format`;

SET FOREIGN_KEY_CHECKS=1;
-- /*******************************************************
-- *
-- * Create new tables
-- *
-- *******************************************************/

-- /*******************************************************
-- *
-- * civicrm_address_format
-- *
-- *******************************************************/
CREATE TABLE `civicrm_address_format` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Address Format Id',
     `format` text    COMMENT 'The format of an address' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_extension
-- *
-- *******************************************************/
CREATE TABLE `civicrm_extension` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Local Extension ID',
     `type` varchar(8) NOT NULL   ,
     `full_name` varchar(255) NOT NULL   COMMENT 'Fully qualified extension name',
     `name` varchar(255)    COMMENT 'Short name',
     `label` varchar(255)    COMMENT 'Short, printable name',
     `file` varchar(255)    COMMENT 'Primary PHP file',
     `schema_version` varchar(63)    COMMENT 'Revision code of the database schema; the format is module-defined',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this extension active?' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_extension_full_name`(
        full_name
  )
  ,     INDEX `UI_extension_name`(
        name
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_file
-- *
-- * Data store for uploaded (attached) files (pointer to file on disk OR blob). Maybe be joined to entities via custom_value.file_id or entity_file table.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_file` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique ID',
     `file_type_id` int unsigned    COMMENT 'Type of file (e.g. Transcript, Income Tax Return, etc). FK to civicrm_option_value.',
     `mime_type` varchar(255)    COMMENT 'mime type of the document',
     `uri` varchar(255)    COMMENT 'uri of the file on disk',
     `document` mediumblob    COMMENT 'contents of the document',
     `description` varchar(255)    COMMENT 'Additional descriptive text regarding this attachment (optional).',
     `upload_date` datetime    COMMENT 'Date and time that this attachment was uploaded or written to server.' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_location_type
-- *
-- *******************************************************/
CREATE TABLE `civicrm_location_type` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Location Type ID',
     `name` varchar(64)    COMMENT 'Location Type Name.',
     `display_name` varchar(64)    COMMENT 'Location Type Display Name.',
     `vcard_name` varchar(64)    COMMENT 'vCard Location Type Name.',
     `description` varchar(255)    COMMENT 'Location Type Description.',
     `is_reserved` tinyint    COMMENT 'Is this location type a predefined system location?',
     `is_active` tinyint    COMMENT 'Is this property active?',
     `is_default` tinyint    COMMENT 'Is this location type the default?' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_managed
-- *
-- * List of declaratively managed objects
-- *
-- *******************************************************/
CREATE TABLE `civicrm_managed` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Surrogate Key',
     `module` varchar(127) NOT NULL   COMMENT 'Name of the module which declared this object',
     `name` varchar(127)    COMMENT 'Symbolic name used by the module to identify the object',
     `entity_type` varchar(64) NOT NULL   COMMENT 'API entity type',
     `entity_id` int unsigned NOT NULL   COMMENT 'Foreign key to the referenced item.',
     `cleanup` varchar(32)    COMMENT 'Policy on when to cleanup entity (always, never, unused)' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_managed_module_name`(
        module
      , name
  )
  ,     INDEX `UI_managed_entity`(
        entity_type
      , entity_id
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mapping
-- *
-- * Store field mappings in import or export for reuse
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mapping` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Mapping ID',
     `name` varchar(64)    COMMENT 'Name of Mapping',
     `description` varchar(255)    COMMENT 'Description of Mapping.',
     `mapping_type_id` int unsigned    COMMENT 'Mapping Type' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_name`(
        name
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_msg_template
-- *
-- * Users will need a way to save and retrieve templates with tokens for use in recurring email communication tasks
-- *
-- *******************************************************/
CREATE TABLE `civicrm_msg_template` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Message Template ID',
     `msg_title` varchar(255)    COMMENT 'Descriptive title of message',
     `msg_subject` text    COMMENT 'Subject for email message.',
     `msg_text` longtext    COMMENT 'Text formatted message',
     `msg_html` longtext    COMMENT 'HTML formatted message',
     `is_active` tinyint   DEFAULT 1 ,
     `workflow_id` int unsigned    COMMENT 'a pseudo-FK to civicrm_option_value',
     `is_default` tinyint   DEFAULT 1 COMMENT 'is this the default message template for the workflow referenced by workflow_id?',
     `is_reserved` tinyint    COMMENT 'is this the reserved message template which we ship for the workflow referenced by workflow_id?',
     `is_sms` tinyint   DEFAULT 0 COMMENT 'Is this message template used for sms?',
     `pdf_format_id` int unsigned    COMMENT 'a pseudo-FK to civicrm_option_value containing PDF Page Format.' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_option_group
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_group` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Option Group ID',
     `name` varchar(64) NOT NULL   COMMENT 'Option group name. Used as selection key by class properties which lookup options in civicrm_option_value.',
     `title` varchar(255)    COMMENT 'Option Group title.',
     `description` varchar(255)    COMMENT 'Option group description.',
     `data_type` varchar(128)    COMMENT 'Option group description.',
     `is_reserved` tinyint   DEFAULT 1 COMMENT 'Is this a predefined system option group (i.e. it can not be deleted)?',
     `is_active` tinyint    COMMENT 'Is this option group active?',
     `is_locked` tinyint    COMMENT 'A lock to remove the ability to add new options via the UI.' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_preferences_date
-- *
-- * Define date preferences for the site
-- *
-- *******************************************************/
CREATE TABLE `civicrm_preferences_date` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `name` varchar(64) NOT NULL   COMMENT 'The meta name for this date (fixed in code)',
     `description` varchar(255)    COMMENT 'Description of this date type.',
     `start` int NOT NULL   COMMENT 'The start offset relative to current year',
     `end` int NOT NULL   COMMENT 'The end offset relative to current year, can be negative',
     `date_format` varchar(64)    COMMENT 'The date type',
     `time_format` varchar(64)    COMMENT 'time format' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_name`(
        name
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_system_log
-- *
-- *******************************************************/
CREATE TABLE `civicrm_system_log` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Primary key ID',
     `message` varchar(128) NOT NULL   COMMENT 'Standardized message',
     `context` longtext    COMMENT 'JSON encoded data',
     `level` varchar(9)   DEFAULT 'info' COMMENT 'error level per PSR3',
     `timestamp` timestamp   DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of when event occurred.',
     `contact_id` int unsigned    COMMENT 'Optional Contact ID that created the log. Not an FK as we keep this regardless',
     `hostname` varchar(128)    COMMENT 'Optional Name of logging host' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_worldregion
-- *
-- *******************************************************/
CREATE TABLE `civicrm_worldregion` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Country Id',
     `name` varchar(128)    COMMENT 'Region name to be associated with countries' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_component
-- *
-- *******************************************************/
CREATE TABLE `civicrm_component` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Component ID',
     `name` varchar(64) NOT NULL   COMMENT 'Name of the component.',
     `namespace` varchar(128)    COMMENT 'Path to components main directory in a form of a class\n      namespace.\n    ' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_persistent
-- *
-- * DB Template Customization strings
-- *
-- *******************************************************/
CREATE TABLE `civicrm_persistent` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Persistent Record Id',
     `context` varchar(255) NOT NULL   COMMENT 'Context for which name data pair is to be stored',
     `name` varchar(255) NOT NULL   COMMENT 'Name of Context',
     `data` longtext    COMMENT 'data associated with name',
     `is_config` tinyint NOT NULL  DEFAULT 0 COMMENT 'Config Settings' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_prevnext_cache
-- *
-- * Table to cache items for navigation on civicrm searched results.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_prevnext_cache` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `entity_table` varchar(64)    COMMENT 'physical tablename for entity being joined to discount, e.g. civicrm_event',
     `entity_id1` int unsigned NOT NULL   COMMENT 'FK to entity table specified in entity_table column.',
     `entity_id2` int unsigned NOT NULL   COMMENT 'FK to entity table specified in entity_table column.',
     `cacheKey` varchar(255)    COMMENT 'Unique path name for cache element of the searched item',
     `data` longtext    COMMENT 'cached snapshot of the serialized data',
     `is_selected` tinyint   DEFAULT 0  
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_all`(
        cacheKey
      , entity_id1
      , entity_id2
      , entity_table
      , is_selected
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_action_mapping
-- *
-- * Table to store mapping for the reminder.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_action_mapping` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `entity` varchar(64)    COMMENT 'Entity for which the reminder is created',
     `entity_value` varchar(64)    COMMENT 'Entity value',
     `entity_value_label` varchar(64)    COMMENT 'Entity value label',
     `entity_status` varchar(64)    COMMENT 'Entity status',
     `entity_status_label` varchar(64)    COMMENT 'Entity status label',
     `entity_date_start` varchar(64)    COMMENT 'Entity date',
     `entity_date_end` varchar(64)    COMMENT 'Entity date',
     `entity_recipient` varchar(64)    COMMENT 'Entity recipient' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_recurring_entity
-- *
-- *******************************************************/
CREATE TABLE `civicrm_recurring_entity` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `parent_id` int unsigned NOT NULL   COMMENT 'Recurring Entity Parent ID',
     `entity_id` int unsigned    COMMENT 'Recurring Entity Child ID',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Physical tablename for entity, e.g. civicrm_event',
     `mode` tinyint NOT NULL  DEFAULT 1 COMMENT '1-this entity, 2-this and the following entities, 3-all the entities' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_acl
-- *
-- * Access Control List
-- *
-- *******************************************************/
CREATE TABLE `civicrm_acl` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
     `name` varchar(64)    COMMENT 'ACL Name.',
     `deny` tinyint NOT NULL  DEFAULT 0 COMMENT 'Is this ACL entry Allow  (0) or Deny (1) ?',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Table of the object possessing this ACL entry (Contact, Group, or ACL Group)',
     `entity_id` int unsigned    COMMENT 'ID of the object possessing this ACL',
     `operation` varchar(8) NOT NULL   COMMENT 'What operation does this ACL entry control?',
     `object_table` varchar(64)    COMMENT 'The table of the object controlled by this ACL entry',
     `object_id` int unsigned    COMMENT 'The ID of the object controlled by this ACL entry',
     `acl_table` varchar(64)    COMMENT 'If this is a grant/revoke entry, what table are we granting?',
     `acl_id` int unsigned    COMMENT 'ID of the ACL or ACL group being granted/revoked',
     `is_active` tinyint    COMMENT 'Is this property active?' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_acl_id`(
        acl_id
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_acl_entity_role
-- *
-- * Join table for Contacts and Groups to ACL Roles
-- *
-- *******************************************************/
CREATE TABLE `civicrm_acl_entity_role` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
     `acl_role_id` int unsigned NOT NULL   COMMENT 'Foreign Key to ACL Role (which is an option value pair and hence an implicit FK)',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Table of the object joined to the ACL Role (Contact or Group)',
     `entity_id` int unsigned NOT NULL   COMMENT 'ID of the group/contact object being joined',
     `is_active` tinyint    COMMENT 'Is this property active?' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_role`(
        acl_role_id
  )
  ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_contact
-- *
-- * Contact objects are defined by a civicrm_contact record plus a related civicrm_contact_type record.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contact` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Contact ID',
     `contact_type` varchar(64)    COMMENT 'Type of Contact.',
     `contact_sub_type` varchar(255)    COMMENT 'May be used to over-ride contact view and edit templates.',
     `do_not_email` tinyint   DEFAULT 0 ,
     `do_not_phone` tinyint   DEFAULT 0 ,
     `do_not_mail` tinyint   DEFAULT 0 ,
     `do_not_sms` tinyint   DEFAULT 0 ,
     `do_not_trade` tinyint   DEFAULT 0 ,
     `is_opt_out` tinyint NOT NULL  DEFAULT 0 COMMENT 'Has the contact opted out from receiving all bulk email from the organization or site domain?',
     `legal_identifier` varchar(32)    COMMENT 'May be used for SSN, EIN/TIN, Household ID (census) or other applicable unique legal/government ID.\n    ',
     `external_identifier` varchar(64)    COMMENT 'Unique trusted external ID (generally from a legacy app/datasource). Particularly useful for deduping operations.',
     `sort_name` varchar(128)    COMMENT 'Name used for sorting different contact types',
     `display_name` varchar(128)    COMMENT 'Formatted name representing preferred format for display/print/other output.',
     `nick_name` varchar(128)    COMMENT 'Nickname.',
     `legal_name` varchar(128)    COMMENT 'Legal Name.',
     `image_URL` text    COMMENT 'optional URL for preferred image (photo, logo, etc.) to display for this contact.',
     `preferred_communication_method` varchar(255)    COMMENT 'What is the preferred mode of communication.',
     `preferred_language` varchar(5)    COMMENT 'Which language is preferred for communication. FK to languages in civicrm_option_value.',
     `preferred_mail_format` varchar(8)   DEFAULT "Both" COMMENT 'What is the preferred mode of sending an email.',
     `hash` varchar(32)    COMMENT 'Key for validating requests related to this contact.',
     `api_key` varchar(32)    COMMENT 'API Key for validating requests related to this contact.',
     `source` varchar(255)    COMMENT 'where contact come from, e.g. import, donate module insert...',
     `first_name` varchar(64)    COMMENT 'First Name.',
     `middle_name` varchar(64)    COMMENT 'Middle Name.',
     `last_name` varchar(64)    COMMENT 'Last Name.',
     `prefix_id` int unsigned    COMMENT 'Prefix or Title for name (Ms, Mr...). FK to prefix ID',
     `suffix_id` int unsigned    COMMENT 'Suffix for name (Jr, Sr...). FK to suffix ID',
     `formal_title` varchar(64)    COMMENT 'Formal (academic or similar) title in front of name. (Prof., Dr. etc.)',
     `communication_style_id` int unsigned    COMMENT 'Communication style (e.g. formal vs. familiar) to use with this contact. FK to communication styles in civicrm_option_value.',
     `email_greeting_id` int unsigned    COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Email Greeting.',
     `email_greeting_custom` varchar(128)    COMMENT 'Custom Email Greeting.',
     `email_greeting_display` varchar(255)    COMMENT 'Cache Email Greeting.',
     `postal_greeting_id` int unsigned    COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Postal Greeting.',
     `postal_greeting_custom` varchar(128)    COMMENT 'Custom Postal greeting.',
     `postal_greeting_display` varchar(255)    COMMENT 'Cache Postal greeting.',
     `addressee_id` int unsigned    COMMENT 'FK to civicrm_option_value.id, that has to be valid registered Addressee.',
     `addressee_custom` varchar(128)    COMMENT 'Custom Addressee.',
     `addressee_display` varchar(255)    COMMENT 'Cache Addressee.',
     `job_title` varchar(255)    COMMENT 'Job Title',
     `gender_id` int unsigned    COMMENT 'FK to gender ID',
     `birth_date` date    COMMENT 'Date of birth',
     `is_deceased` tinyint   DEFAULT 0 ,
     `deceased_date` date    COMMENT 'Date of deceased',
     `household_name` varchar(128)    COMMENT 'Household Name.',
     `primary_contact_id` int unsigned    COMMENT 'Optional FK to Primary Contact for this household.',
     `organization_name` varchar(128)    COMMENT 'Organization Name.',
     `sic_code` varchar(8)    COMMENT 'Standard Industry Classification Code.',
     `user_unique_id` varchar(255)    COMMENT 'the OpenID (or OpenID-style http://username.domain/) unique identifier for this contact mainly used for logging in to CiviCRM',
     `employer_id` int unsigned    COMMENT 'OPTIONAL FK to civicrm_contact record.',
     `is_deleted` tinyint NOT NULL  DEFAULT 0 ,
     `created_date` timestamp NULL  DEFAULT NULL COMMENT 'When was the contact was created.',
     `modified_date` timestamp NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the contact (or closely related entity) was created or modified or deleted.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_contact_type`(
        contact_type
  )
  ,     INDEX `index_contact_sub_type`(
        contact_sub_type
  )
  ,     UNIQUE INDEX `UI_external_identifier`(
        external_identifier
  )
  ,     INDEX `index_sort_name`(
        sort_name
  )
  ,     INDEX `index_preferred_communication_method`(
        preferred_communication_method
  )
  ,     INDEX `index_hash`(
        hash
  )
  ,     INDEX `index_api_key`(
        api_key
  )
  ,     INDEX `index_first_name`(
        first_name
  )
  ,     INDEX `index_last_name`(
        last_name
  )
  ,     INDEX `UI_prefix`(
        prefix_id
  )
  ,     INDEX `UI_suffix`(
        suffix_id
  )
  ,     INDEX `index_communication_style_id`(
        communication_style_id
  )
  ,     INDEX `UI_gender`(
        gender_id
  )
  ,     INDEX `index_is_deceased`(
        is_deceased
  )
  ,     INDEX `index_household_name`(
        household_name
  )
  ,     INDEX `index_organization_name`(
        organization_name
  )
  ,     INDEX `index_is_deleted_sort_name`(
        is_deleted
      , sort_name
      , id
  )
  
,          CONSTRAINT FK_civicrm_contact_primary_contact_id FOREIGN KEY (`primary_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contact_employer_id FOREIGN KEY (`employer_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_acl_contact_cache
-- *
-- * Join table cache for contacts that a user has permission on.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_acl_contact_cache` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
     `user_id` int unsigned    COMMENT 'FK to civicrm_contact (could be null for anon user)',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_contact',
     `operation` varchar(8) NOT NULL   COMMENT 'What operation does this user have permission on?' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_user_contact_operation`(
        user_id
      , contact_id
      , operation
  )
  
,          CONSTRAINT FK_civicrm_acl_contact_cache_user_id FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_acl_contact_cache_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_relationship_type
-- *
-- * Relationship types s/b structured with contact_a as the 'subject/child' contact and contact_b as the 'object/parent' contact (e.g. Individual A is Employee of Org B).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_relationship_type` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Primary key',
     `name_a_b` varchar(64)    COMMENT 'name for relationship of contact_a to contact_b.',
     `label_a_b` varchar(64)    COMMENT 'label for relationship of contact_a to contact_b.',
     `name_b_a` varchar(64)    COMMENT 'Optional name for relationship of contact_b to contact_a.',
     `label_b_a` varchar(64)    COMMENT 'Optional label for relationship of contact_b to contact_a.',
     `description` varchar(255)    COMMENT 'Optional verbose description of the relationship type.',
     `contact_type_a` varchar(12)    COMMENT 'If defined, contact_a in a relationship of this type must be a specific contact_type.',
     `contact_type_b` varchar(12)    COMMENT 'If defined, contact_b in a relationship of this type must be a specific contact_type.',
     `contact_sub_type_a` varchar(64)    COMMENT 'If defined, contact_sub_type_a in a relationship of this type must be a specific contact_sub_type.\n    ',
     `contact_sub_type_b` varchar(64)    COMMENT 'If defined, contact_sub_type_b in a relationship of this type must be a specific contact_sub_type.\n    ',
     `is_reserved` tinyint    COMMENT 'Is this relationship type a predefined system type (can not be changed or de-activated)?',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this relationship type currently active (i.e. can be used when creating or editing relationships)?\n    ' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name_a_b`(
        name_a_b
  )
  ,     UNIQUE INDEX `UI_name_b_a`(
        name_b_a
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_saved_search
-- *
-- * Users can save their complex SQL queries and use them later.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_saved_search` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Saved Search ID',
     `form_values` text    COMMENT 'Submitted form values for this search',
     `mapping_id` int unsigned    COMMENT 'Foreign key to civicrm_mapping used for saved search-builder searches.',
     `search_custom_id` int unsigned    COMMENT 'Foreign key to civicrm_option value table used for saved custom searches.',
     `where_clause` text    COMMENT 'the sql where clause if a saved search acl',
     `select_tables` text    COMMENT 'the tables to be included in a select data',
     `where_tables` text    COMMENT 'the tables to be included in the count statement' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_saved_search_mapping_id FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_contact_type
-- *
-- * Provide type information for contacts
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contact_type` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contact Type ID',
     `name` varchar(64)    COMMENT 'Internal name of Contact Type (or Subtype).',
     `label` varchar(64)    COMMENT 'localized Name of Contact Type.',
     `description` text    COMMENT 'localized Optional verbose description of the type.',
     `image_URL` varchar(255)    COMMENT 'URL of image if any.',
     `parent_id` int unsigned    COMMENT 'Optional FK to parent contact type.',
     `is_active` tinyint    COMMENT 'Is this entry active?',
     `is_reserved` tinyint    COMMENT 'Is this contact type a predefined system type' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `contact_type`(
        name
  )
  
,          CONSTRAINT FK_civicrm_contact_type_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_contact_type`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_batch
-- *
-- * Stores the details of a batch operation Used primarily when doing batch operations with an external system.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_batch` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Address ID',
     `name` varchar(64)    COMMENT 'Variable name/programmatic handle for this batch.',
     `title` varchar(255)    COMMENT 'Friendly Name.',
     `description` text    COMMENT 'Description of this batch set.',
     `created_id` int unsigned    COMMENT 'FK to Contact ID',
     `created_date` datetime    COMMENT 'When was this item created',
     `modified_id` int unsigned    COMMENT 'FK to Contact ID',
     `modified_date` datetime    COMMENT 'When was this item created',
     `saved_search_id` int unsigned    COMMENT 'FK to Saved Search ID',
     `status_id` int unsigned NOT NULL   COMMENT 'fk to Batch Status options in civicrm_option_values',
     `type_id` int unsigned    COMMENT 'fk to Batch Type options in civicrm_option_values',
     `mode_id` int unsigned    COMMENT 'fk to Batch mode options in civicrm_option_values',
     `total` decimal(20,2)    COMMENT 'Total amount for this batch.',
     `item_count` int unsigned    COMMENT 'Number of items in a batch.',
     `payment_instrument_id` int unsigned    COMMENT 'fk to Payment Instrument options in civicrm_option_values',
     `exported_date` datetime    ,
     `data` longtext    COMMENT 'cache entered data' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_batch_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_batch_modified_id FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_batch_saved_search_id FOREIGN KEY (`saved_search_id`) REFERENCES `civicrm_saved_search`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_entity_batch
-- *
-- * Batch entities (Contributions, Participants, Contacts) to a batch.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_batch` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
     `entity_table` varchar(64)    COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
     `entity_id` int unsigned NOT NULL   COMMENT 'FK to entity table specified in entity_table column.',
     `batch_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_batch' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  ,     UNIQUE INDEX `UI_batch_entity`(
        batch_id
      , entity_id
      , entity_table
  )
  
,          CONSTRAINT FK_civicrm_entity_batch_batch_id FOREIGN KEY (`batch_id`) REFERENCES `civicrm_batch`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_component
-- *
-- * Stores information about the mailing components (header/footer).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_component` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `name` varchar(64)    COMMENT 'The name of this component',
     `component_type` varchar(12)    COMMENT 'Type of Component.',
     `subject` varchar(255)    ,
     `body_html` text    COMMENT 'Body of the component in html format.',
     `body_text` text    COMMENT 'Body of the component in text format.',
     `is_default` tinyint   DEFAULT 0 COMMENT 'Is this the default component for this component_type?',
     `is_active` tinyint    COMMENT 'Is this property active?' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_abtest
-- *
-- * Stores information about abtesting
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_abtest` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `name` varchar(128)    COMMENT 'Name of the A/B test',
     `status` varchar(32)    COMMENT 'Status',
     `mailing_id_a` int unsigned    COMMENT 'The first experimental mailing (\"A\" condition)',
     `mailing_id_b` int unsigned    COMMENT 'The second experimental mailing (\"B\" condition)',
     `mailing_id_c` int unsigned    COMMENT 'The final, general mailing (derived from A or B)',
     `domain_id` int unsigned    COMMENT 'Which site is this mailing for',
     `testing_criteria` varchar(32)    ,
     `winner_criteria` varchar(32)    ,
     `specific_url` varchar(255)    COMMENT 'What specific url to track',
     `declare_winning_time` datetime    COMMENT 'In how much time to declare winner',
     `group_percentage` int unsigned    ,
     `created_id` int unsigned    COMMENT 'FK to Contact ID',
     `created_date` timestamp NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When was this item created' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_abtest_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_bounce_type
-- *
-- * Table to index the various bounce types and their properties
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_bounce_type` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `name` varchar(24) NOT NULL   COMMENT 'Type of bounce',
     `description` varchar(255)    COMMENT 'A description of this bounce type',
     `hold_threshold` int unsigned NOT NULL   COMMENT 'Number of bounces of this type required before the email address is put on bounce hold' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_bounce_pattern
-- *
-- * Pseudo-constant table of patterns for bounce classification
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_bounce_pattern` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `bounce_type_id` int unsigned NOT NULL   COMMENT 'Type of bounce',
     `pattern` varchar(255)    COMMENT 'A regexp to match a message to a bounce type' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_bounce_pattern_bounce_type_id FOREIGN KEY (`bounce_type_id`) REFERENCES `civicrm_mailing_bounce_type`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_premiums
-- *
-- * table - settings for the Premiums features for a given contribution page 
-- *
-- *******************************************************/
CREATE TABLE `civicrm_premiums` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `entity_table` varchar(64) NOT NULL   COMMENT 'Joins these premium settings to another object. Always civicrm_contribution_page for now.',
     `entity_id` int unsigned NOT NULL   ,
     `premiums_active` tinyint NOT NULL  DEFAULT 0 COMMENT 'Is the Premiums feature enabled for this page?',
     `premiums_intro_title` varchar(255)    COMMENT 'Title for Premiums section.',
     `premiums_intro_text` text    COMMENT 'Displayed in <div> at top of Premiums section of page. Text and HTML allowed.',
     `premiums_contact_email` varchar(100)    COMMENT 'This email address is included in receipts if it is populated and a premium has been selected.',
     `premiums_contact_phone` varchar(50)    COMMENT 'This phone number is included in receipts if it is populated and a premium has been selected.',
     `premiums_display_min_contribution` tinyint NOT NULL   COMMENT 'Boolean. Should we automatically display minimum contribution amount text after the premium descriptions.',
     `premiums_nothankyou_label` varchar(255)    COMMENT 'Label displayed for No Thank-you option in premiums block (e.g. No thank you)',
     `premiums_nothankyou_position` int unsigned   DEFAULT 1  
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_currency
-- *
-- *******************************************************/
CREATE TABLE `civicrm_currency` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Currency Id',
     `name` varchar(64)    COMMENT 'Currency Name',
     `symbol` varchar(8)    COMMENT 'Currency Symbol',
     `numeric_code` varchar(3)    COMMENT 'Numeric currency code',
     `full_name` varchar(64)    COMMENT 'Full currency name' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_financial_account
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_account` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'ID',
     `name` varchar(255) NOT NULL   COMMENT 'Financial Account Name.',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID that is responsible for the funds in this account',
     `financial_account_type_id` int unsigned NOT NULL  DEFAULT 3 COMMENT 'pseudo FK into civicrm_option_value.',
     `accounting_code` varchar(64)    COMMENT 'Optional value for mapping monies owed and received to accounting system codes.',
     `account_type_code` varchar(64)    COMMENT 'Optional value for mapping account types to accounting system account categories (QuickBooks Account Type Codes for example).',
     `description` varchar(255)    COMMENT 'Financial Type Description.',
     `parent_id` int unsigned    COMMENT 'Parent ID in account hierarchy',
     `is_header_account` tinyint   DEFAULT 0 COMMENT 'Is this a header account which does not allow transactions to be posted against it directly, but only to its sub-accounts?',
     `is_deductible` tinyint   DEFAULT 1 COMMENT 'Is this account tax-deductible?',
     `is_tax` tinyint   DEFAULT 0 COMMENT 'Is this account for taxes?',
     `tax_rate` decimal(10,8)    COMMENT 'The percentage of the total_amount that is due for this tax.',
     `is_reserved` tinyint    COMMENT 'Is this a predefined system object?',
     `is_active` tinyint    COMMENT 'Is this property active?',
     `is_default` tinyint    COMMENT 'Is this account the default one (or default tax one) for its financial_account_type?' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_financial_account_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_financial_account_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_financial_account`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_payment_processor_type
-- *
-- *******************************************************/
CREATE TABLE `civicrm_payment_processor_type` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Payment Processor Type ID',
     `name` varchar(64)    COMMENT 'Payment Processor Name.',
     `title` varchar(127)    COMMENT 'Payment Processor Name.',
     `description` varchar(255)    COMMENT 'Payment Processor Description.',
     `is_active` tinyint    COMMENT 'Is this processor active?',
     `is_default` tinyint    COMMENT 'Is this processor the default?',
     `user_name_label` varchar(255)    ,
     `password_label` varchar(255)    ,
     `signature_label` varchar(255)    ,
     `subject_label` varchar(255)    ,
     `class_name` varchar(255)    ,
     `url_site_default` varchar(255)    ,
     `url_api_default` varchar(255)    ,
     `url_recur_default` varchar(255)    ,
     `url_button_default` varchar(255)    ,
     `url_site_test_default` varchar(255)    ,
     `url_api_test_default` varchar(255)    ,
     `url_recur_test_default` varchar(255)    ,
     `url_button_test_default` varchar(255)    ,
     `billing_mode` int unsigned NOT NULL   COMMENT 'Billing Mode (deprecated)',
     `is_recur` tinyint    COMMENT 'Can process recurring contributions',
     `payment_type` int unsigned   DEFAULT 1 COMMENT 'Payment Type: Credit or Debit (deprecated)',
     `payment_instrument_id` int unsigned   DEFAULT 1 COMMENT 'Payment Instrument ID' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_financial_type
-- *
-- * Formerly civicrm_contribution_type merged into this table in 4.3
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_type` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'ID of original financial_type so you can search this table by the financial_type.id and then select the relevant version based on the timestamp',
     `name` varchar(64) NOT NULL   COMMENT 'Financial Type Name.',
     `description` varchar(255)    COMMENT 'Financial Type Description.',
     `is_deductible` tinyint   DEFAULT 1 COMMENT 'Is this financial type tax-deductible? If true, contributions of this type may be fully OR partially deductible - non-deductible amount is stored in the Contribution record.',
     `is_reserved` tinyint    COMMENT 'Is this a predefined system object?',
     `is_active` tinyint    COMMENT 'Is this property active?' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_id`(
        id
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_entity_financial_account
-- *
-- * Map between an entity and a financial account, where there is a specific relationship between the financial account and the entity, e.g. Income Account for or AR Account for
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_financial_account` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'ID',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Links to an entity_table like civicrm_financial_type',
     `entity_id` int unsigned NOT NULL   COMMENT 'Links to an id in the entity_table, such as vid in civicrm_financial_type',
     `account_relationship` int unsigned NOT NULL   COMMENT 'FK to a new civicrm_option_value (account_relationship)',
     `financial_account_id` int unsigned NOT NULL   COMMENT 'FK to the financial_account_id' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `index_entity_id_entity_table_account_relationship`(
        entity_id
      , entity_table
      , account_relationship
  )
  
,          CONSTRAINT FK_civicrm_entity_financial_account_financial_account_id FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account`(`id`) ON DELETE RESTRICT  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_financial_item
-- *
-- * Financial data for civicrm_line_item, etc.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_item` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `created_date` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time the item was created',
     `transaction_date` datetime NOT NULL   COMMENT 'Date and time of the source transaction',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact ID of contact the item is from',
     `description` varchar(255)    COMMENT 'Human readable description of this item, to ease display without lookup of source item.',
     `amount` decimal(20,2) NOT NULL  DEFAULT 0 COMMENT 'Total amount of this item',
     `currency` varchar(3)    COMMENT 'Currency for the amount',
     `financial_account_id` int unsigned    COMMENT 'FK to civicrm_financial_account',
     `status_id` int unsigned    COMMENT 'Payment status: test, paid, part_paid, unpaid (if empty assume unpaid)',
     `entity_table` varchar(64)    COMMENT 'The table providing the source of this item such as civicrm_line_item',
     `entity_id` int unsigned    COMMENT 'The specific source item that is responsible for the creation of this financial_item' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `IX_created_date`(
        created_date
  )
  ,     INDEX `IX_transaction_date`(
        transaction_date
  )
  ,     INDEX `index_entity_id_entity_table`(
        entity_id
      , entity_table
  )
  
,          CONSTRAINT FK_civicrm_financial_item_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_financial_item_financial_account_id FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_membership_status
-- *
-- * Membership Status stores admin configurable rules for assigning status to memberships.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership_status` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Membership Id',
     `name` varchar(128)    COMMENT 'Name for Membership Status',
     `label` varchar(128)    COMMENT 'Label for Membership Status',
     `start_event` varchar(12)    COMMENT 'Event when this status starts.',
     `start_event_adjust_unit` varchar(8)    COMMENT 'Unit used for adjusting from start_event.',
     `start_event_adjust_interval` int    COMMENT 'Status range begins this many units from start_event.',
     `end_event` varchar(12)    COMMENT 'Event after which this status ends.',
     `end_event_adjust_unit` varchar(8)    COMMENT 'Unit used for adjusting from the ending event.',
     `end_event_adjust_interval` int    COMMENT 'Status range ends this many units from end_event.',
     `is_current_member` tinyint    COMMENT 'Does this status aggregate to current members (e.g. New, Renewed, Grace might all be TRUE... while Unrenewed, Lapsed, Inactive would be FALSE).',
     `is_admin` tinyint    COMMENT 'Is this status for admin/manual assignment only.',
     `weight` int    ,
     `is_default` tinyint    COMMENT 'Assign this status to a membership record if no other status match is found.',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this membership_status enabled.',
     `is_reserved` tinyint   DEFAULT 0 COMMENT 'Is this membership_status reserved.' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_campaign
-- *
-- * Campaign Details.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_campaign` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Campaign ID.',
     `name` varchar(255) NOT NULL   COMMENT 'Name of the Campaign.',
     `title` varchar(255)    COMMENT 'Title of the Campaign.',
     `description` text    COMMENT 'Full description of Campaign.',
     `start_date` datetime    COMMENT 'Date and time that Campaign starts.',
     `end_date` datetime    COMMENT 'Date and time that Campaign ends.',
     `campaign_type_id` int unsigned   DEFAULT NULL COMMENT 'Campaign Type ID.Implicit FK to civicrm_option_value where option_group = campaign_type',
     `status_id` int unsigned   DEFAULT NULL COMMENT 'Campaign status ID.Implicit FK to civicrm_option_value where option_group = campaign_status',
     `external_identifier` varchar(32)    COMMENT 'Unique trusted external ID (generally from a legacy app/datasource). Particularly useful for deduping operations.',
     `parent_id` int unsigned   DEFAULT NULL COMMENT 'Optional parent id for this Campaign.',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this Campaign enabled or disabled/cancelled?',
     `created_id` int unsigned    COMMENT 'FK to civicrm_contact, who created this Campaign.',
     `created_date` datetime    COMMENT 'Date and time that Campaign was created.',
     `last_modified_id` int unsigned    COMMENT 'FK to civicrm_contact, who recently edited this Campaign.',
     `last_modified_date` datetime    COMMENT 'Date and time that Campaign was edited last time.',
     `goal_general` text    COMMENT 'General goals for Campaign.',
     `goal_revenue` decimal(20,2)    COMMENT 'The target revenue for this campaign.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_campaign_type_id`(
        campaign_type_id
  )
  ,     INDEX `UI_campaign_status_id`(
        status_id
  )
  ,     UNIQUE INDEX `UI_external_identifier`(
        external_identifier
  )
  
,          CONSTRAINT FK_civicrm_campaign_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_campaign_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_campaign_last_modified_id FOREIGN KEY (`last_modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_campaign_group
-- *
-- * Campaign Group Details.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_campaign_group` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Campaign Group id.',
     `campaign_id` int unsigned NOT NULL   COMMENT 'Foreign key to the activity Campaign.',
     `group_type` varchar(8)   DEFAULT NULL COMMENT 'Type of Group.',
     `entity_table` varchar(64)   DEFAULT NULL COMMENT 'Name of table where item being referenced is stored.',
     `entity_id` int unsigned   DEFAULT NULL COMMENT 'Entity id of referenced table.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_campaign_group_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_survey
-- *
-- * Campaign Survey Details.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_survey` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Survey id.',
     `title` varchar(255) NOT NULL   COMMENT 'Title of the Survey.',
     `campaign_id` int unsigned   DEFAULT NULL COMMENT 'Foreign key to the Campaign.',
     `activity_type_id` int unsigned   DEFAULT NULL COMMENT 'Implicit FK to civicrm_option_value where option_group = activity_type',
     `recontact_interval` text    COMMENT 'Recontact intervals for each status.',
     `instructions` text    COMMENT 'Script instructions for volunteers to use for the survey.',
     `release_frequency` int unsigned   DEFAULT NULL COMMENT 'Number of days for recurrence of release.',
     `max_number_of_contacts` int unsigned   DEFAULT NULL COMMENT 'Maximum number of contacts to allow for survey.',
     `default_number_of_contacts` int unsigned   DEFAULT NULL COMMENT 'Default number of contacts to allow for survey.',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this survey enabled or disabled/cancelled?',
     `is_default` tinyint   DEFAULT 0 COMMENT 'Is this default survey?',
     `created_id` int unsigned    COMMENT 'FK to civicrm_contact, who created this Survey.',
     `created_date` datetime    COMMENT 'Date and time that Survey was created.',
     `last_modified_id` int unsigned    COMMENT 'FK to civicrm_contact, who recently edited this Survey.',
     `last_modified_date` datetime    COMMENT 'Date and time that Survey was edited last time.',
     `result_id` int unsigned   DEFAULT NULL COMMENT 'Used to store option group id.',
     `bypass_confirm` tinyint   DEFAULT 0 COMMENT 'Bypass the email verification.',
     `thankyou_title` varchar(255)    COMMENT 'Title for Thank-you page (header title tag, and display at the top of the page).',
     `thankyou_text` text    COMMENT 'text and html allowed. displayed above result on success page',
     `is_share` tinyint   DEFAULT 1 COMMENT 'Can people share the petition through social media?' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_activity_type_id`(
        activity_type_id
  )
  
,          CONSTRAINT FK_civicrm_survey_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_survey_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_survey_last_modified_id FOREIGN KEY (`last_modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_participant_status_type
-- *
-- * various types of CiviEvent participant statuses
-- *
-- *******************************************************/
CREATE TABLE `civicrm_participant_status_type` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'unique participant status type id',
     `name` varchar(64)    COMMENT 'non-localized name of the status type',
     `label` varchar(255)    COMMENT 'localized label for display of this status type',
     `class` varchar(8)    COMMENT 'the general group of status type this one belongs to',
     `is_reserved` tinyint    COMMENT 'whether this is a status type required by the system',
     `is_active` tinyint   DEFAULT 1 COMMENT 'whether this status type is active',
     `is_counted` tinyint    COMMENT 'whether this status type is counted against event size limit',
     `weight` int unsigned NOT NULL   COMMENT 'controls sort order',
     `visibility_id` int unsigned    COMMENT 'whether the status type is visible to the public, an implicit foreign key to option_value.value related to the `visibility` option_group' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_event_carts
-- *
-- *******************************************************/
CREATE TABLE `civicrm_event_carts` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Cart Id',
     `user_id` int unsigned    COMMENT 'FK to civicrm_contact who created this cart',
     `completed` tinyint   DEFAULT 0  
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_event_carts_user_id FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_dedupe_rule_group
-- *
-- * Dedupe rule groups
-- *
-- *******************************************************/
CREATE TABLE `civicrm_dedupe_rule_group` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique dedupe rule group id',
     `contact_type` varchar(12)    COMMENT 'The type of contacts this group applies to',
     `threshold` int NOT NULL   COMMENT 'The weight threshold the sum of the rule weights has to cross to consider two contacts the same',
     `used` varchar(12) NOT NULL   COMMENT 'Whether the rule should be used for cases where usage is Unsupervised, Supervised OR General(programatically)',
     `name` varchar(64)    COMMENT 'Name of the rule group',
     `title` varchar(255)    COMMENT 'Label of the rule group',
     `is_reserved` tinyint    COMMENT 'Is this a reserved rule - a rule group that has been optimized and cannot be changed by the admin' 
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_dedupe_rule
-- *
-- * Dedupe rules for use by rule groups
-- *
-- *******************************************************/
CREATE TABLE `civicrm_dedupe_rule` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique dedupe rule id',
     `dedupe_rule_group_id` int unsigned NOT NULL   COMMENT 'The id of the rule group this rule belongs to',
     `rule_table` varchar(64) NOT NULL   COMMENT 'The name of the table this rule is about',
     `rule_field` varchar(64) NOT NULL   COMMENT 'The name of the field of the table referenced in rule_table',
     `rule_length` int unsigned    COMMENT 'The length of the matching substring',
     `rule_weight` int NOT NULL   COMMENT 'The weight of the rule' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_dedupe_rule_dedupe_rule_group_id FOREIGN KEY (`dedupe_rule_group_id`) REFERENCES `civicrm_dedupe_rule_group`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_dedupe_exception
-- *
-- * Dedupe exceptions
-- *
-- *******************************************************/
CREATE TABLE `civicrm_dedupe_exception` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique dedupe exception id',
     `contact_id1` int unsigned    COMMENT 'FK to Contact ID',
     `contact_id2` int unsigned    COMMENT 'FK to Contact ID' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_contact_id1_contact_id2`(
        contact_id1
      , contact_id2
  )
  
,          CONSTRAINT FK_civicrm_dedupe_exception_contact_id1 FOREIGN KEY (`contact_id1`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_dedupe_exception_contact_id2 FOREIGN KEY (`contact_id2`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_case_type
-- *
-- * Case type definition
-- *
-- *******************************************************/
CREATE TABLE `civicrm_case_type` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Autoincremented type id',
     `name` varchar(64) NOT NULL   COMMENT 'Machine name for Case Type',
     `title` varchar(64) NOT NULL   COMMENT 'Natural language name for Case Type',
     `description` varchar(255)    COMMENT 'Description of the Case Type',
     `is_active` tinyint    COMMENT 'Is this entry active?',
     `is_reserved` tinyint    COMMENT 'Is this case type a predefined system type?',
     `weight` int NOT NULL  DEFAULT 1 COMMENT 'Ordering of the case types',
     `definition` blob    COMMENT 'xml definition of case type' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `case_type_name`(
        name
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_grant
-- *
-- * This table stores information about grants given to a contact.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_grant` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Grant id',
     `contact_id` int unsigned NOT NULL   COMMENT 'Contact ID of contact record given grant belongs to.',
     `application_received_date` date    COMMENT 'Date on which grant application was received by donor.',
     `decision_date` date    COMMENT 'Date on which grant decision was made.',
     `money_transfer_date` date    COMMENT 'Date on which grant money transfer was made.',
     `grant_due_date` date    COMMENT 'Date on which grant report is due.',
     `grant_report_received` tinyint    COMMENT 'Yes/No field stating whether grant report was received by donor.',
     `grant_type_id` int unsigned NOT NULL   COMMENT 'Type of grant. Implicit FK to civicrm_option_value in grant_type option_group.',
     `amount_total` decimal(20,2) NOT NULL   COMMENT 'Requested grant amount, in default currency.',
     `amount_requested` decimal(20,2)    COMMENT 'Requested grant amount, in original currency (optional).',
     `amount_granted` decimal(20,2)    COMMENT 'Granted amount, in default currency.',
     `currency` varchar(3) NOT NULL   COMMENT '3 character string, value from config setting or input via user.',
     `rationale` text    COMMENT 'Grant rationale.',
     `status_id` int unsigned NOT NULL   COMMENT 'Id of Grant status.',
     `financial_type_id` int unsigned   DEFAULT NULL COMMENT 'FK to Financial Type.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_grant_type_id`(
        grant_type_id
  )
  ,     INDEX `index_status_id`(
        status_id
  )
  
,          CONSTRAINT FK_civicrm_grant_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_grant_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_tell_friend
-- *
-- *******************************************************/
CREATE TABLE `civicrm_tell_friend` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Friend ID',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Name of table where item being referenced is stored.',
     `entity_id` int unsigned NOT NULL   COMMENT 'Foreign key to the referenced item.',
     `title` varchar(255)    ,
     `intro` text    COMMENT 'Introductory message to contributor or participant displayed on the Tell a Friend form.',
     `suggested_message` text    COMMENT 'Suggested message to friends, provided as default on the Tell A Friend form.',
     `general_link` varchar(255)    COMMENT 'URL for general info about the organization - included in the email sent to friends.',
     `thankyou_title` varchar(255)    COMMENT 'Text for Tell a Friend thank you page header and HTML title.',
     `thankyou_text` text    COMMENT 'Thank you message displayed on success page.',
     `is_active` tinyint     
,
        PRIMARY KEY (`id`)
 
 
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_pledge_block
-- *
-- *******************************************************/
CREATE TABLE `civicrm_pledge_block` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Pledge ID',
     `entity_table` varchar(64)    COMMENT 'physical tablename for entity being joined to pledge, e.g. civicrm_contact',
     `entity_id` int unsigned NOT NULL   COMMENT 'FK to entity table specified in entity_table column.',
     `pledge_frequency_unit` varchar(128)    COMMENT 'Delimited list of supported frequency units',
     `is_pledge_interval` tinyint   DEFAULT 0 COMMENT 'Is frequency interval exposed on the contribution form.',
     `max_reminders` int unsigned   DEFAULT 1 COMMENT 'The maximum number of payment reminders to send for any given payment.',
     `initial_reminder_day` int unsigned   DEFAULT 5 COMMENT 'Send initial reminder this many days prior to the payment due date.',
     `additional_reminder_day` int unsigned   DEFAULT 5 COMMENT 'Send additional reminder this many days after last one sent, up to maximum number of reminders.',
     `pledge_start_date` varchar(64)    COMMENT 'The date the first scheduled pledge occurs.',
     `is_pledge_start_date_visible` tinyint NOT NULL  DEFAULT 0 COMMENT 'If true - recurring start date is shown.',
     `is_pledge_start_date_editable` tinyint NOT NULL  DEFAULT 0 COMMENT 'If true - recurring start date is editable.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_queue_item
-- *
-- * Stores a list of queue items
-- *
-- *******************************************************/
CREATE TABLE `civicrm_queue_item` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `queue_name` varchar(64) NOT NULL   COMMENT 'Name of the queue which includes this item',
     `weight` int NOT NULL   ,
     `submit_time` datetime NOT NULL   COMMENT 'date on which this item was submitted to the queue',
     `release_time` datetime    COMMENT 'date on which this job becomes available; null if ASAP',
     `data` text    COMMENT 'Serialized queue data' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_queueids`(
        queue_name
      , weight
      , id
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_pcp
-- *
-- *******************************************************/
CREATE TABLE `civicrm_pcp` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Personal Campaign Page ID',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact ID',
     `status_id` int unsigned NOT NULL   ,
     `title` varchar(255)   DEFAULT NULL ,
     `intro_text` text   DEFAULT NULL ,
     `page_text` text   DEFAULT NULL ,
     `donate_link_text` varchar(255)   DEFAULT NULL ,
     `page_id` int unsigned NOT NULL   COMMENT 'The Contribution or Event Page which triggered this pcp',
     `page_type` varchar(64)   DEFAULT 'contribute' COMMENT 'The type of PCP this is: contribute or event',
     `pcp_block_id` int unsigned NOT NULL   COMMENT 'The pcp block that this pcp page was created from',
     `is_thermometer` int unsigned   DEFAULT 0 ,
     `is_honor_roll` int unsigned   DEFAULT 0 ,
     `goal_amount` decimal(20,2)    COMMENT 'Goal amount of this Personal Campaign Page.',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `is_active` tinyint   DEFAULT 0 COMMENT 'Is Personal Campaign Page enabled/active?',
     `is_notify` tinyint   DEFAULT 0 COMMENT 'Notify owner via email when someone donates to page?' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_pcp_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_cxn
-- *
-- *******************************************************/
CREATE TABLE `civicrm_cxn` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Connection ID',
     `app_guid` varchar(128)    COMMENT 'Application GUID',
     `app_meta` text    COMMENT 'Application Metadata (JSON)',
     `cxn_guid` varchar(128)    COMMENT 'Connection GUID',
     `secret` text    COMMENT 'Shared secret',
     `perm` text    COMMENT 'Permissions approved for the service (JSON)',
     `options` text    COMMENT 'Options for the service (JSON)',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is connection currently enabled?',
     `created_date` timestamp NULL  DEFAULT NULL COMMENT 'When was the connection was created.',
     `modified_date` timestamp NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When the connection was created or modified.',
     `fetched_date` timestamp NULL  DEFAULT NULL COMMENT 'The last time the application metadata was fetched.' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_appid`(
        app_guid
  )
  ,     UNIQUE INDEX `UI_keypair_cxnid`(
        cxn_guid
  )
  
 
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_cache
-- *
-- * Table to cache items for civicrm components.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_cache` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `group_name` varchar(32) NOT NULL   COMMENT 'group name for cache element, useful in cleaning cache elements',
     `path` varchar(255)    COMMENT 'Unique path name for cache element',
     `data` longtext    COMMENT 'data associated with this path',
     `component_id` int unsigned    COMMENT 'Component that this menu item belongs to',
     `created_date` timestamp   DEFAULT CURRENT_TIMESTAMP COMMENT 'When was the cache item created',
     `expired_date` timestamp NULL  DEFAULT NULL COMMENT 'When should the cache item expire' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_group_path_date`(
        group_name
      , path
      , created_date
  )
  
,          CONSTRAINT FK_civicrm_cache_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_country
-- *
-- *******************************************************/
CREATE TABLE `civicrm_country` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Country Id',
     `name` varchar(64)    COMMENT 'Country Name',
     `iso_code` char(2)    COMMENT 'ISO Code',
     `country_code` varchar(4)    COMMENT 'National prefix to be used when dialing TO this country.',
     `address_format_id` int unsigned    COMMENT 'Foreign key to civicrm_address_format.id.',
     `idd_prefix` varchar(4)    COMMENT 'International direct dialing prefix from within the country TO another country',
     `ndd_prefix` varchar(4)    COMMENT 'Access prefix to call within a country to a different area',
     `region_id` int unsigned NOT NULL   COMMENT 'Foreign key to civicrm_worldregion.id.',
     `is_province_abbreviated` tinyint   DEFAULT 0 COMMENT 'Should state/province be displayed as abbreviation for contacts from this country?' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name_iso_code`(
        name
      , iso_code
  )
  
,          CONSTRAINT FK_civicrm_country_address_format_id FOREIGN KEY (`address_format_id`) REFERENCES `civicrm_address_format`(`id`) ,          CONSTRAINT FK_civicrm_country_region_id FOREIGN KEY (`region_id`) REFERENCES `civicrm_worldregion`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_custom_group
-- *
-- * All extended (custom) properties are associated with a group. These are logical sets of related data.
  
-- *
-- *******************************************************/
CREATE TABLE `civicrm_custom_group` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Custom Group ID',
     `name` varchar(64)    COMMENT 'Variable name/programmatic handle for this group.',
     `title` varchar(64) NOT NULL   COMMENT 'Friendly Name.',
     `extends` varchar(255)   DEFAULT 'Contact' COMMENT 'Type of object this group extends (can add other options later e.g. contact_address, etc.).',
     `extends_entity_column_id` int unsigned   DEFAULT NULL COMMENT 'FK to civicrm_option_value.id (for option group custom_data_type.)',
     `extends_entity_column_value` varchar(255)    COMMENT 'linking custom group for dynamic object',
     `style` varchar(15)    COMMENT 'Visual relationship between this form and its parent.',
     `collapse_display` int unsigned   DEFAULT 0 COMMENT 'Will this group be in collapsed or expanded mode on initial display ?',
     `help_pre` text    COMMENT 'Description and/or help text to display before fields in form.',
     `help_post` text    COMMENT 'Description and/or help text to display after fields in form.',
     `weight` int NOT NULL  DEFAULT 1 COMMENT 'Controls display order when multiple extended property groups are setup for the same class.',
     `is_active` tinyint    COMMENT 'Is this property active?',
     `table_name` varchar(255)    COMMENT 'Name of the table that holds the values for this group.',
     `is_multiple` tinyint    COMMENT 'Does this group hold multiple values?',
     `min_multiple` int unsigned    COMMENT 'minimum number of multiple records (typically 0?)',
     `max_multiple` int unsigned    COMMENT 'maximum number of multiple records, if 0 - no max',
     `collapse_adv_display` int unsigned   DEFAULT 0 COMMENT 'Will this group be in collapsed or expanded mode on advanced search display ?',
     `created_id` int unsigned    COMMENT 'FK to civicrm_contact, who created this custom group',
     `created_date` datetime    COMMENT 'Date and time this custom group was created.',
     `is_reserved` tinyint   DEFAULT 0 COMMENT 'Is this a reserved Custom Group?',
     `is_public` tinyint   DEFAULT 1 COMMENT 'Is this property public?' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_title_extends`(
        title
      , extends
  )
  ,     UNIQUE INDEX `UI_name_extends`(
        name
      , extends
  )
  
,          CONSTRAINT FK_civicrm_custom_group_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_custom_field
-- *
-- * Stores info about an extended (custom) property (data and form field info).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_custom_field` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Custom Field ID',
     `custom_group_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_custom_group.',
     `name` varchar(64)    COMMENT 'Variable name/programmatic handle for this group.',
     `label` varchar(255) NOT NULL   COMMENT 'Text for form field label (also friendly name for administering this custom property).',
     `data_type` varchar(16) NOT NULL   COMMENT 'Controls location of data storage in extended_data table.',
     `html_type` varchar(32) NOT NULL   COMMENT 'HTML types plus several built-in extended types.',
     `default_value` varchar(255)    COMMENT 'Use form_options.is_default for field_types which use options.',
     `is_required` tinyint    COMMENT 'Is a value required for this property.',
     `is_searchable` tinyint    COMMENT 'Is this property searchable.',
     `is_search_range` tinyint   DEFAULT 0 COMMENT 'Is this property range searchable.',
     `weight` int NOT NULL  DEFAULT 1 COMMENT 'Controls field display order within an extended property group.',
     `help_pre` text    COMMENT 'Description and/or help text to display before this field.',
     `help_post` text    COMMENT 'Description and/or help text to display after this field.',
     `mask` varchar(64)    COMMENT 'Optional format instructions for specific field types, like date types.',
     `attributes` varchar(255)    COMMENT 'Store collection of type-appropriate attributes, e.g. textarea  needs rows/cols attributes',
     `javascript` varchar(255)    COMMENT 'Optional scripting attributes for field.',
     `is_active` tinyint    COMMENT 'Is this property active?',
     `is_view` tinyint    COMMENT 'Is this property set by PHP Code? A code field is viewable but not editable',
     `options_per_line` int unsigned    COMMENT 'number of options per line for checkbox and radio',
     `text_length` int unsigned    COMMENT 'field length if alphanumeric',
     `start_date_years` int    COMMENT 'Date may be up to start_date_years years prior to the current date.',
     `end_date_years` int    COMMENT 'Date may be up to end_date_years years after the current date.',
     `date_format` varchar(64)    COMMENT 'date format for custom date',
     `time_format` int unsigned    COMMENT 'time format for custom date',
     `note_columns` int unsigned    COMMENT ' Number of columns in Note Field ',
     `note_rows` int unsigned    COMMENT ' Number of rows in Note Field ',
     `column_name` varchar(255)    COMMENT 'Name of the column that holds the values for this field.',
     `option_group_id` int unsigned    COMMENT 'For elements with options, the option group id that is used',
     `filter` varchar(255)    COMMENT 'Stores Contact Get API params contact reference custom fields. May be used for other filters in the future.',
     `in_selector` tinyint   DEFAULT 0 COMMENT 'Should the multi-record custom field values be displayed in tab table listing' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_label_custom_group_id`(
        label
      , custom_group_id
  )
  ,     UNIQUE INDEX `UI_name_custom_group_id`(
        name
      , custom_group_id
  )
  
,          CONSTRAINT FK_civicrm_custom_field_custom_group_id FOREIGN KEY (`custom_group_id`) REFERENCES `civicrm_custom_group`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_domain
-- *
-- * Top-level hierarchy to support multi-org/domain installations. Define domains for multi-org installs, else all contacts belong to one domain.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_domain` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Domain ID',
     `name` varchar(64)    COMMENT 'Name of Domain / Organization',
     `description` varchar(255)    COMMENT 'Description of Domain.',
     `config_backend` text    COMMENT 'Backend configuration.',
     `version` varchar(32)    COMMENT 'The civicrm version this instance is running',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID. This is specifically not an FK to avoid circular constraints',
     `locales` text    COMMENT 'list of locales supported by the current db state (NULL for single-lang install)',
     `locale_custom_strings` text    COMMENT 'Locale specific string overrides' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_domain_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_email
-- *
-- * Email information for a specific location.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_email` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Email ID',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID',
     `location_type_id` int unsigned    COMMENT 'Which Location does this email belong to.',
     `email` varchar(254)    COMMENT 'Email address',
     `is_primary` tinyint   DEFAULT 0 COMMENT 'Is this the primary?',
     `is_billing` tinyint   DEFAULT 0 COMMENT 'Is this the billing?',
     `on_hold` tinyint NOT NULL  DEFAULT 0 COMMENT 'Is this address on bounce hold?',
     `is_bulkmail` tinyint NOT NULL  DEFAULT 0 COMMENT 'Is this address for bulk mail ?',
     `hold_date` datetime    COMMENT 'When the address went on bounce hold',
     `reset_date` datetime    COMMENT 'When the address bounce status was last reset',
     `signature_text` text   DEFAULT NULL COMMENT 'Text formatted signature for the email.',
     `signature_html` text   DEFAULT NULL COMMENT 'HTML formatted signature for the email.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_location_type`(
        location_type_id
  )
  ,     INDEX `UI_email`(
        email
  )
  ,     INDEX `index_is_primary`(
        is_primary
  )
  ,     INDEX `index_is_billing`(
        is_billing
  )
  
,          CONSTRAINT FK_civicrm_email_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_entity_file
-- *
-- * Attaches (joins) uploaded files (images, documents, etc.) to entities (Contacts, Groups, Actions).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_file` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
     `entity_table` varchar(64)    COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
     `entity_id` int unsigned NOT NULL   COMMENT 'FK to entity table specified in entity_table column.',
     `file_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_file' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  ,     INDEX `index_entity_file_id`(
        entity_table
      , entity_id
      , file_id
  )
  
,          CONSTRAINT FK_civicrm_entity_file_file_id FOREIGN KEY (`file_id`) REFERENCES `civicrm_file`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_im
-- *
-- * IM information for a specific location.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_im` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique IM ID',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID',
     `location_type_id` int unsigned    COMMENT 'Which Location does this email belong to.',
     `name` varchar(64)    COMMENT 'IM screen name',
     `provider_id` int unsigned    COMMENT 'Which IM Provider does this screen name belong to.',
     `is_primary` tinyint   DEFAULT 0 COMMENT 'Is this the primary IM for this contact and location.',
     `is_billing` tinyint   DEFAULT 0 COMMENT 'Is this the billing?' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_location_type`(
        location_type_id
  )
  ,     INDEX `UI_provider_id`(
        provider_id
  )
  ,     INDEX `index_is_primary`(
        is_primary
  )
  ,     INDEX `index_is_billing`(
        is_billing
  )
  
,          CONSTRAINT FK_civicrm_im_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_job
-- *
-- * Scheduled job.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_job` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Job Id',
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this scheduled job for',
     `run_frequency` varchar(8)   DEFAULT "Daily" COMMENT 'Scheduled job run frequency.',
     `last_run` timestamp NULL  DEFAULT NULL COMMENT 'When was this cron entry last run',
     `scheduled_run_date` timestamp NULL  DEFAULT NULL COMMENT 'When is this cron entry scheduled to run',
     `name` varchar(255)    COMMENT 'Title of the job',
     `description` varchar(255)    COMMENT 'Description of the job',
     `api_entity` varchar(255)    COMMENT 'Entity of the job api call',
     `api_action` varchar(255)    COMMENT 'Action of the job api call',
     `parameters` text    COMMENT 'List of parameters to the command.',
     `is_active` tinyint    COMMENT 'Is this job active?' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_job_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_job_log
-- *
-- * Scheduled jobs log.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_job_log` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Job log entry Id',
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this scheduled job for',
     `run_time` timestamp    COMMENT 'Log entry date',
     `job_id` int unsigned    COMMENT 'Pointer to job id - not a FK though, just for logging purposes',
     `name` varchar(255)    COMMENT 'Title of the job',
     `command` varchar(255)    COMMENT 'Full path to file containing job script',
     `description` varchar(255)    COMMENT 'Title line of log entry',
     `data` text    COMMENT 'Potential extended data for specific job run (e.g. tracebacks).' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_job_log_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_log
-- *
-- * Log can be linked to any object in the application.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_log` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Log ID',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Name of table where item being referenced is stored.',
     `entity_id` int unsigned NOT NULL   COMMENT 'Foreign key to the referenced item.',
     `data` text    COMMENT 'Updates does to this object if any.',
     `modified_id` int unsigned    COMMENT 'FK to Contact ID of person under whose credentials this data modification was made.',
     `modified_date` datetime    COMMENT 'When was the referenced entity created or modified or deleted.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  
,          CONSTRAINT FK_civicrm_log_modified_id FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mail_settings
-- *
-- * Various email accounts for use by CiviMail (and its processor)
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mail_settings` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this match entry for',
     `name` varchar(255)    COMMENT 'name of this group of settings',
     `is_default` tinyint    COMMENT 'whether this is the default set of settings for this domain',
     `domain` varchar(255)    COMMENT 'email address domain (the part after @)',
     `localpart` varchar(255)    COMMENT 'optional local part (like civimail+ for addresses like civimail+s.1.2@example.com)',
     `return_path` varchar(255)    COMMENT 'contents of the Return-Path header',
     `protocol` varchar(255)    COMMENT 'name of the protocol to use for polling (like IMAP, POP3 or Maildir)',
     `server` varchar(255)    COMMENT 'server to use when polling',
     `port` int unsigned    COMMENT 'port to use when polling',
     `username` varchar(255)    COMMENT 'username to use when polling',
     `password` varchar(255)    COMMENT 'password to use when polling',
     `is_ssl` tinyint    COMMENT 'whether to use SSL or not',
     `source` varchar(255)    COMMENT 'folder to poll from when using IMAP, path to poll from when using Maildir, etc.',
     `activity_status` varchar(255)    COMMENT 'Name of status to use when creating email to activity.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mail_settings_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mapping_field
-- *
-- * Individual field mappings for Mapping
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mapping_field` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Mapping Field ID',
     `mapping_id` int unsigned NOT NULL   COMMENT 'Mapping to which this field belongs',
     `name` varchar(255)    COMMENT 'Mapping field key',
     `contact_type` varchar(64)    COMMENT 'Contact Type in mapping',
     `column_number` int unsigned NOT NULL   COMMENT 'Column number for mapping set',
     `location_type_id` int unsigned    COMMENT 'Location type of this mapping, if required',
     `phone_type_id` int unsigned    COMMENT 'Which type of phone does this number belongs.',
     `im_provider_id` int unsigned    COMMENT 'Which type of IM Provider does this name belong.',
     `website_type_id` int unsigned    COMMENT 'Which type of website does this site belong',
     `relationship_type_id` int unsigned    COMMENT 'Relationship type, if required',
     `relationship_direction` varchar(6)    ,
     `grouping` int unsigned   DEFAULT 1 COMMENT 'Used to group mapping_field records into related sets (e.g. for criteria sets in search builder\n      mappings).\n    ',
     `operator` varchar(16)    COMMENT 'SQL WHERE operator for search-builder mapping fields (search criteria).',
     `value` varchar(255)    COMMENT 'SQL WHERE value for search-builder mapping fields.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mapping_field_mapping_id FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping`(`id`) ,          CONSTRAINT FK_civicrm_mapping_field_location_type_id FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type`(`id`) ,          CONSTRAINT FK_civicrm_mapping_field_relationship_type_id FOREIGN KEY (`relationship_type_id`) REFERENCES `civicrm_relationship_type`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_menu
-- *
-- * Table to store menu items for all civicrm components.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_menu` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this menu item for',
     `path` varchar(255)    COMMENT 'Path Name',
     `path_arguments` text    COMMENT 'Arguments to pass to the url',
     `title` varchar(255)    ,
     `access_callback` varchar(255)    COMMENT 'Function to call to check access permissions',
     `access_arguments` text    COMMENT 'Arguments to pass to access callback',
     `page_callback` varchar(255)    COMMENT 'function to call for this url',
     `page_arguments` text    COMMENT 'Arguments to pass to page callback',
     `breadcrumb` text    COMMENT 'Breadcrumb for the path.',
     `return_url` varchar(255)    COMMENT 'Url where a page should redirected to, if next url not known.',
     `return_url_args` varchar(255)    COMMENT 'Arguments to pass to return_url',
     `component_id` int unsigned    COMMENT 'Component that this menu item belongs to',
     `is_active` tinyint    COMMENT 'Is this menu item active?',
     `is_public` tinyint    COMMENT 'Is this menu accessible to the public?',
     `is_exposed` tinyint    COMMENT 'Is this menu exposed to the navigation system?',
     `is_ssl` tinyint    COMMENT 'Should this menu be exposed via SSL if enabled?',
     `weight` int NOT NULL  DEFAULT 1 COMMENT 'Ordering of the menu items in various blocks.',
     `type` int NOT NULL  DEFAULT 1 COMMENT 'Drupal menu type.',
     `page_type` int NOT NULL  DEFAULT 1 COMMENT 'CiviCRM menu type.',
     `skipBreadcrumb` tinyint    COMMENT 'skip this url being exposed to breadcrumb',
     `module_data` text    COMMENT 'All other menu metadata not stored in other fields' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_path_domain_id`(
        path
      , domain_id
  )
  
,          CONSTRAINT FK_civicrm_menu_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ,          CONSTRAINT FK_civicrm_menu_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_navigation
-- *
-- * Table to store navigation.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_navigation` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this navigation item for',
     `label` varchar(255)    COMMENT 'Navigation Title',
     `name` varchar(255)    COMMENT 'Internal Name',
     `url` varchar(255)    COMMENT 'url in case of custom navigation link',
     `icon` varchar(255) NULL  DEFAULT NULL COMMENT 'CSS class name for an icon',
     `permission` varchar(255)    COMMENT 'Permission for menu item',
     `permission_operator` varchar(3)    COMMENT 'Permission Operator',
     `parent_id` int unsigned    COMMENT 'Parent navigation item, used for grouping',
     `is_active` tinyint    COMMENT 'Is this navigation item active?',
     `has_separator` tinyint    COMMENT 'If separator needs to be added after this menu item',
     `weight` int    COMMENT 'Ordering of the navigation items in various blocks.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_navigation_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ,          CONSTRAINT FK_civicrm_navigation_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_navigation`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_note
-- *
-- * Notes can be linked to any object in the application.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_note` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Note ID',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Name of table where item being referenced is stored.',
     `entity_id` int unsigned NOT NULL   COMMENT 'Foreign key to the referenced item.',
     `note` text    COMMENT 'Note and/or Comment.',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID creator',
     `modified_date` date    COMMENT 'When was this note last modified/edited',
     `subject` varchar(255)    COMMENT 'subject of note description',
     `privacy` varchar(255)    COMMENT 'Foreign Key to Note Privacy Level (which is an option value pair and hence an implicit FK)' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  
,          CONSTRAINT FK_civicrm_note_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_option_value
-- *
-- *******************************************************/
CREATE TABLE `civicrm_option_value` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Option ID',
     `option_group_id` int unsigned NOT NULL   COMMENT 'Group which this option belongs to.',
     `label` varchar(512) NOT NULL   COMMENT 'Option string as displayed to users - e.g. the label in an HTML OPTION tag.',
     `value` varchar(512) NOT NULL   COMMENT 'The actual value stored (as a foreign key) in the data record. Functions which need lookup option_value.title should use civicrm_option_value.option_group_id plus civicrm_option_value.value as the key.',
     `name` varchar(255)    COMMENT 'Stores a fixed (non-translated) name for this option value. Lookup functions should use the name as the key for the option value row.',
     `grouping` varchar(255)    COMMENT 'Use to sort and/or set display properties for sub-set(s) of options within an option group. EXAMPLE: Use for college_interest field, to differentiate partners from non-partners.',
     `filter` int unsigned    COMMENT 'Bitwise logic can be used to create subsets of options within an option_group for different uses.',
     `is_default` tinyint   DEFAULT 0 COMMENT 'Is this the default option for the group?',
     `weight` int unsigned NOT NULL   COMMENT 'Controls display sort order.',
     `description` text    COMMENT 'Optional description.',
     `is_optgroup` tinyint   DEFAULT 0 COMMENT 'Is this row simply a display header? Expected usage is to render these as OPTGROUP tags within a SELECT field list of options?',
     `is_reserved` tinyint   DEFAULT 0 COMMENT 'Is this a predefined system object?',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this option active?',
     `component_id` int unsigned    COMMENT 'Component that this option value belongs/caters to.',
     `domain_id` int unsigned    COMMENT 'Which Domain is this option value for',
     `visibility_id` int unsigned   DEFAULT NULL ,
     `icon` varchar(255)   DEFAULT NULL COMMENT 'crm-i icon class',
     `color` varchar(255)   DEFAULT NULL COMMENT 'Hex color value e.g. #ffffff' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_option_group_id_value`(
        value(128)
      , option_group_id
  )
  ,     INDEX `index_option_group_id_name`(
        name(128)
      , option_group_id
  )
  
,          CONSTRAINT FK_civicrm_option_value_option_group_id FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_option_value_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`) ,          CONSTRAINT FK_civicrm_option_value_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_phone
-- *
-- * Phone information for a specific location.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_phone` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Phone ID',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID',
     `location_type_id` int unsigned    COMMENT 'Which Location does this phone belong to.',
     `is_primary` tinyint   DEFAULT 0 COMMENT 'Is this the primary phone for this contact and location.',
     `is_billing` tinyint   DEFAULT 0 COMMENT 'Is this the billing?',
     `mobile_provider_id` int unsigned    COMMENT 'Which Mobile Provider does this phone belong to.',
     `phone` varchar(32)    COMMENT 'Complete phone number.',
     `phone_ext` varchar(16)    COMMENT 'Optional extension for a phone number.',
     `phone_numeric` varchar(32)    COMMENT 'Phone number stripped of all whitespace, letters, and punctuation.',
     `phone_type_id` int unsigned    COMMENT 'Which type of phone does this number belongs.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_location_type`(
        location_type_id
  )
  ,     INDEX `index_is_primary`(
        is_primary
  )
  ,     INDEX `index_is_billing`(
        is_billing
  )
  ,     INDEX `UI_mobile_provider_id`(
        mobile_provider_id
  )
  ,     INDEX `index_phone_numeric`(
        phone_numeric
  )
  
,          CONSTRAINT FK_civicrm_phone_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_state_province
-- *
-- *******************************************************/
CREATE TABLE `civicrm_state_province` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'State/Province ID',
     `name` varchar(64)    COMMENT 'Name of State/Province',
     `abbreviation` varchar(4)    COMMENT '2-4 Character Abbreviation of State/Province',
     `country_id` int unsigned NOT NULL   COMMENT 'ID of Country that State/Province belong' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name_country_id`(
        name
      , country_id
  )
  
,          CONSTRAINT FK_civicrm_state_province_country_id FOREIGN KEY (`country_id`) REFERENCES `civicrm_country`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_tag
-- *
-- * Provides support for flat or hierarchical classification of various types of entities (contacts, groups, actions...).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_tag` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Tag ID',
     `name` varchar(64) NOT NULL   COMMENT 'Name of Tag.',
     `description` varchar(255)    COMMENT 'Optional verbose description of the tag.',
     `parent_id` int unsigned   DEFAULT NULL COMMENT 'Optional parent id for this tag.',
     `is_selectable` tinyint   DEFAULT 1 COMMENT 'Is this tag selectable / displayed',
     `is_reserved` tinyint   DEFAULT 0 ,
     `is_tagset` tinyint   DEFAULT 0 ,
     `used_for` varchar(64)   DEFAULT NULL ,
     `created_id` int unsigned    COMMENT 'FK to civicrm_contact, who created this tag',
     `color` varchar(255)   DEFAULT NULL COMMENT 'Hex color value e.g. #ffffff',
     `created_date` datetime    COMMENT 'Date and time that tag was created.' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_tag_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_tag`(`id`) ,          CONSTRAINT FK_civicrm_tag_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_uf_match
-- *
-- * The mapping from an user framework (UF) object to a CRM object.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_uf_match` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'System generated ID.',
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this match entry for',
     `uf_id` int unsigned NOT NULL   COMMENT 'UF ID',
     `uf_name` varchar(128)    COMMENT 'UF Name',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID',
     `language` varchar(5)    COMMENT 'UI language preferred by the given user/contact' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `I_civicrm_uf_match_uf_id`(
        uf_id
  )
  ,     UNIQUE INDEX `UI_uf_name_domain_id`(
        uf_name
      , domain_id
  )
  ,     UNIQUE INDEX `UI_contact_domain_id`(
        contact_id
      , domain_id
  )
  
,          CONSTRAINT FK_civicrm_uf_match_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ,          CONSTRAINT FK_civicrm_uf_match_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_timezone
-- *
-- *******************************************************/
CREATE TABLE `civicrm_timezone` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Timezone Id',
     `name` varchar(64)    COMMENT 'Timezone full name',
     `abbreviation` char(3)    COMMENT 'ISO Code for timezone abbreviation',
     `gmt` varchar(64)    COMMENT 'GMT name of the timezone',
     `offset` int    ,
     `country_id` int unsigned NOT NULL   COMMENT 'Country Id' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_timezone_country_id FOREIGN KEY (`country_id`) REFERENCES `civicrm_country`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_openid
-- *
-- * OpenID information for a specific location.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_openid` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique OpenID ID',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID',
     `location_type_id` int unsigned    COMMENT 'Which Location does this email belong to.',
     `openid` varchar(255)    COMMENT 'the OpenID (or OpenID-style http://username.domain/) unique identifier for this contact mainly used for logging in to CiviCRM',
     `allowed_to_login` tinyint NOT NULL  DEFAULT 0 COMMENT 'Whether or not this user is allowed to login',
     `is_primary` tinyint   DEFAULT 0 COMMENT 'Is this the primary email for this contact and location.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_location_type`(
        location_type_id
  )
  ,     UNIQUE INDEX `UI_openid`(
        openid
  )
  
,          CONSTRAINT FK_civicrm_openid_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_website
-- *
-- * Website information for a specific location.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_website` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Website ID',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID',
     `url` varchar(128)    COMMENT 'Website',
     `website_type_id` int unsigned    COMMENT 'Which Website type does this website belong to.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_website_type_id`(
        website_type_id
  )
  
,          CONSTRAINT FK_civicrm_website_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_setting
-- *
-- * Table to store civicrm settings for civicrm core and components.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_setting` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `name` varchar(255)    COMMENT 'Unique name for setting',
     `value` text    COMMENT 'data associated with this group / name combo',
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this menu item for',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID if the setting is localized to a contact',
     `is_domain` tinyint    COMMENT 'Is this setting a contact specific or site wide setting?',
     `component_id` int unsigned    COMMENT 'Component that this menu item belongs to',
     `created_date` datetime    COMMENT 'When was the setting created',
     `created_id` int unsigned    COMMENT 'FK to civicrm_contact, who created this setting' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `index_domain_contact_name`(
        domain_id
      , contact_id
      , name
  )
  
,          CONSTRAINT FK_civicrm_setting_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_setting_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_setting_component_id FOREIGN KEY (`component_id`) REFERENCES `civicrm_component`(`id`) ,          CONSTRAINT FK_civicrm_setting_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_print_label
-- *
-- * Table to store the labels created by user.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_print_label` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `title` varchar(255)    COMMENT 'User title for for this label layout',
     `name` varchar(255)    COMMENT 'variable name/programmatic handle for this field.',
     `description` text    COMMENT 'Description of this label layout',
     `label_format_name` varchar(255)    COMMENT 'This refers to name column of civicrm_option_value row in name_badge option group',
     `label_type_id` int unsigned    COMMENT 'Implicit FK to civicrm_option_value row in NEW label_type option group',
     `data` longtext    COMMENT 'contains json encode configurations options',
     `is_default` tinyint   DEFAULT 1 COMMENT 'Is this default?',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this option active?',
     `is_reserved` tinyint   DEFAULT 1 COMMENT 'Is this reserved label?',
     `created_id` int unsigned    COMMENT 'FK to civicrm_contact, who created this label layout' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_print_label_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_word_replacement
-- *
-- * Top-level hierarchy to support word replacement.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_word_replacement` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Word replacement ID',
     `find_word` varchar(255) COLLATE utf8_bin    COMMENT 'Word which need to be replaced',
     `replace_word` varchar(255) COLLATE utf8_bin    COMMENT 'Word which will replace the word in find',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this entry active?',
     `match_type` varchar(16)   DEFAULT "wildcardMatch" ,
     `domain_id` int unsigned    COMMENT 'FK to Domain ID. This is for Domain specific word replacement' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_domain_find`(
        domain_id
      , find_word
  )
  
,          CONSTRAINT FK_civicrm_word_replacement_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_status_pref
-- *
-- * Preferences controlling status checks called in system.check.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_status_pref` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Status Preference ID',
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this Status Preference for',
     `name` varchar(255) NOT NULL   COMMENT 'Name of the status check this preference references.',
     `hush_until` date   DEFAULT NULL COMMENT 'expires ignore_severity.  NULL never hushes.',
     `ignore_severity` int unsigned   DEFAULT 1 COMMENT 'Hush messages up to and including this severity.',
     `prefs` varchar(255)    COMMENT 'These settings are per-check, and can\'t be compared across checks.',
     `check_info` varchar(255)    COMMENT 'These values are per-check, and can\'t be compared across checks.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_status_pref_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_status_pref_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_acl_cache
-- *
-- * Cache for acls and contacts
-- *
-- *******************************************************/
CREATE TABLE `civicrm_acl_cache` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
     `contact_id` int unsigned    COMMENT 'Foreign Key to Contact',
     `acl_id` int unsigned NOT NULL   COMMENT 'Foreign Key to ACL',
     `modified_date` timestamp NULL   COMMENT 'When was this cache entry last modified' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_acl_id`(
        acl_id
  )
  
,          CONSTRAINT FK_civicrm_acl_cache_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_acl_cache_acl_id FOREIGN KEY (`acl_id`) REFERENCES `civicrm_acl`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_group
-- *
-- * Provide grouping of related contacts
-- *
-- *******************************************************/
CREATE TABLE `civicrm_group` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Group ID',
     `name` varchar(64)    COMMENT 'Internal name of Group.',
     `title` varchar(64)    COMMENT 'Name of Group.',
     `description` text    COMMENT 'Optional verbose description of the group.',
     `source` varchar(64)    COMMENT 'Module or process which created this group.',
     `saved_search_id` int unsigned    COMMENT 'FK to saved search table.',
     `is_active` tinyint    COMMENT 'Is this entry active?',
     `visibility` varchar(24)   DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.',
     `where_clause` text    COMMENT 'the sql where clause if a saved search acl',
     `select_tables` text    COMMENT 'the tables to be included in a select data',
     `where_tables` text    COMMENT 'the tables to be included in the count statement',
     `group_type` varchar(128)    COMMENT 'FK to group type',
     `cache_date` timestamp NULL   COMMENT 'Date when we created the cache for a smart group',
     `refresh_date` timestamp NULL   COMMENT 'Date and time when we need to refresh the cache next.',
     `parents` text    COMMENT 'IDs of the parent(s)',
     `children` text    COMMENT 'IDs of the child(ren)',
     `is_hidden` tinyint   DEFAULT 0 COMMENT 'Is this group hidden?',
     `is_reserved` tinyint   DEFAULT 0 ,
     `created_id` int unsigned    COMMENT 'FK to contact table.',
     `modified_id` int unsigned    COMMENT 'FK to contact table.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_group_type`(
        group_type
  )
  ,     UNIQUE INDEX `UI_title`(
        title
  )
  ,     UNIQUE INDEX `UI_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_group_saved_search_id FOREIGN KEY (`saved_search_id`) REFERENCES `civicrm_saved_search`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_group_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_group_modified_id FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_subscription_history
-- *
-- * History information of subscribe/unsubscribe actions
-- *
-- *******************************************************/
CREATE TABLE `civicrm_subscription_history` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Internal Id',
     `contact_id` int unsigned NOT NULL   COMMENT 'Contact Id',
     `group_id` int unsigned    COMMENT 'Group Id',
     `date` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'Date of the (un)subscription',
     `method` varchar(8)    COMMENT 'How the (un)subscription was triggered',
     `status` varchar(8)    COMMENT 'The state of the contact within the group',
     `tracking` varchar(255)    COMMENT 'IP address or other tracking info' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_subscription_history_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_subscription_history_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_group_contact_cache
-- *
-- * Join table cache for 'static' groups.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_group_contact_cache` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
     `group_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_group',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_contact' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_contact_group`(
        contact_id
      , group_id
  )
  
,          CONSTRAINT FK_civicrm_group_contact_cache_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_group_contact_cache_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_group_nesting
-- *
-- * Provide parent-child relationships for groups
-- *
-- *******************************************************/
CREATE TABLE `civicrm_group_nesting` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Relationship ID',
     `child_group_id` int unsigned NOT NULL   COMMENT 'ID of the child group',
     `parent_group_id` int unsigned NOT NULL   COMMENT 'ID of the parent group' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_group_nesting_child_group_id FOREIGN KEY (`child_group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_group_nesting_parent_group_id FOREIGN KEY (`parent_group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_group_organization
-- *
-- * Integrate Organization information into Groups
-- *
-- *******************************************************/
CREATE TABLE `civicrm_group_organization` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Relationship ID',
     `group_id` int unsigned NOT NULL   COMMENT 'ID of the group',
     `organization_id` int unsigned NOT NULL   COMMENT 'ID of the Organization Contact' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_group_organization`(
        group_id
      , organization_id
  )
  
,          CONSTRAINT FK_civicrm_group_organization_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_group_organization_organization_id FOREIGN KEY (`organization_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_event_subscribe
-- *
-- * Tracks when a (new) contact subscribes to a group by email
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_subscribe` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `group_id` int unsigned NOT NULL   COMMENT 'FK to Group',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact',
     `hash` varchar(255) NOT NULL   COMMENT 'Security hash',
     `time_stamp` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When this subscription event occurred.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_event_subscribe_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_mailing_event_subscribe_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_event_confirm
-- *
-- * Tracks when a subscription event is confirmed by email
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_confirm` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `event_subscribe_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_mailing_event_subscribe',
     `time_stamp` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When this confirmation event occurred.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_event_confirm_event_subscribe_id FOREIGN KEY (`event_subscribe_id`) REFERENCES `civicrm_mailing_event_subscribe`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_contribution_page
-- *
-- * A Contribution object store meta information about a single customized contribution page
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_page` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution Id',
     `title` varchar(255)    COMMENT 'Contribution Page title. For top of page display',
     `intro_text` text    COMMENT 'Text and html allowed. Displayed below title.',
     `financial_type_id` int unsigned    COMMENT 'default financial type assigned to contributions submitted via this page, e.g. Contribution, Campaign Contribution',
     `payment_processor` varchar(128)    COMMENT 'Payment Processors configured for this contribution Page',
     `is_credit_card_only` tinyint   DEFAULT 0 COMMENT 'if true - processing logic must reject transaction at confirmation stage if pay method != credit card',
     `is_monetary` tinyint   DEFAULT 1 COMMENT 'if true - allows real-time monetary transactions otherwise non-monetary transactions',
     `is_recur` tinyint   DEFAULT 0 COMMENT 'if true - allows recurring contributions, valid only for PayPal_Standard',
     `is_confirm_enabled` tinyint   DEFAULT 1 COMMENT 'if false, the confirm page in contribution pages gets skipped',
     `recur_frequency_unit` varchar(128)    COMMENT 'Supported recurring frequency units.',
     `is_recur_interval` tinyint   DEFAULT 0 COMMENT 'if true - supports recurring intervals',
     `is_recur_installments` tinyint   DEFAULT 0 COMMENT 'if true - asks user for recurring installments',
     `adjust_recur_start_date` tinyint   DEFAULT 0 COMMENT 'if true - user is able to adjust payment start date',
     `is_pay_later` tinyint   DEFAULT 0 COMMENT 'if true - allows the user to send payment directly to the org later',
     `pay_later_text` text    COMMENT 'The text displayed to the user in the main form',
     `pay_later_receipt` text    COMMENT 'The receipt sent to the user instead of the normal receipt text',
     `is_partial_payment` tinyint   DEFAULT 0 COMMENT 'is partial payment enabled for this online contribution page',
     `initial_amount_label` varchar(255)    COMMENT 'Initial amount label for partial payment',
     `initial_amount_help_text` text    COMMENT 'Initial amount help text for partial payment',
     `min_initial_amount` decimal(20,2)    COMMENT 'Minimum initial amount for partial payment',
     `is_allow_other_amount` tinyint   DEFAULT 0 COMMENT 'if true, page will include an input text field where user can enter their own amount',
     `default_amount_id` int unsigned    COMMENT 'FK to civicrm_option_value.',
     `min_amount` decimal(20,2)    COMMENT 'if other amounts allowed, user can configure minimum allowed.',
     `max_amount` decimal(20,2)    COMMENT 'if other amounts allowed, user can configure maximum allowed.',
     `goal_amount` decimal(20,2)    COMMENT 'The target goal for this page, allows people to build a goal meter',
     `thankyou_title` varchar(255)    COMMENT 'Title for Thank-you page (header title tag, and display at the top of the page).',
     `thankyou_text` text    COMMENT 'text and html allowed. displayed above result on success page',
     `thankyou_footer` text    COMMENT 'Text and html allowed. displayed at the bottom of the success page. Common usage is to include link(s) to other pages such as tell-a-friend, etc.',
     `is_email_receipt` tinyint   DEFAULT 0 COMMENT 'if true, receipt is automatically emailed to contact on success',
     `receipt_from_name` varchar(255)    COMMENT 'FROM email name used for receipts generated by contributions to this contribution page.',
     `receipt_from_email` varchar(255)    COMMENT 'FROM email address used for receipts generated by contributions to this contribution page.',
     `cc_receipt` varchar(255)    COMMENT 'comma-separated list of email addresses to cc each time a receipt is sent',
     `bcc_receipt` varchar(255)    COMMENT 'comma-separated list of email addresses to bcc each time a receipt is sent',
     `receipt_text` text    COMMENT 'text to include above standard receipt info on receipt email. emails are text-only, so do not allow html for now',
     `is_active` tinyint    COMMENT 'Is this property active?',
     `footer_text` text    COMMENT 'Text and html allowed. Displayed at the bottom of the first page of the contribution wizard.',
     `amount_block_is_active` tinyint   DEFAULT 1 COMMENT 'Is this property active?',
     `start_date` datetime    COMMENT 'Date and time that this page starts.',
     `end_date` datetime    COMMENT 'Date and time that this page ends. May be NULL if no defined end date/time',
     `created_id` int unsigned    COMMENT 'FK to civicrm_contact, who created this contribution page',
     `created_date` datetime    COMMENT 'Date and time that contribution page was created.',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `campaign_id` int unsigned    COMMENT 'The campaign for which we are collecting contributions with this page.',
     `is_share` tinyint   DEFAULT 1 COMMENT 'Can people share the contribution page through social media?',
     `is_billing_required` tinyint   DEFAULT 0 COMMENT 'if true - billing block is required for online contribution page' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_contribution_page_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ,          CONSTRAINT FK_civicrm_contribution_page_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_page_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_product
-- *
-- * able - stores "product info" for premiums and can be used for non-incentive products 
-- *
-- *******************************************************/
CREATE TABLE `civicrm_product` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `name` varchar(255) NOT NULL   COMMENT 'Required product/premium name',
     `description` text    COMMENT 'Optional description of the product/premium.',
     `sku` varchar(50)    COMMENT 'Optional product sku or code.',
     `options` text    COMMENT 'Store comma-delimited list of color, size, etc. options for the product.',
     `image` varchar(255)    COMMENT 'Full or relative URL to uploaded image - fullsize.',
     `thumbnail` varchar(255)    COMMENT 'Full or relative URL to image thumbnail.',
     `price` decimal(20,2)    COMMENT 'Sell price or market value for premiums. For tax-deductible contributions, this will be stored as non_deductible_amount in the contribution record.',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `financial_type_id` int unsigned   DEFAULT NULL COMMENT 'FK to Financial Type.',
     `min_contribution` decimal(20,2)    COMMENT 'Minimum contribution required to be eligible to select this premium.',
     `cost` decimal(20,2)    COMMENT 'Actual cost of this product. Useful to determine net return from sale or using this as an incentive.',
     `is_active` tinyint NOT NULL   COMMENT 'Disabling premium removes it from the premiums_premium join table below.',
     `period_type` varchar(8)   DEFAULT 'rolling' COMMENT 'Rolling means we set start/end based on current day, fixed means we set start/end for current year or month\n      (e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006) ',
     `fixed_period_start_day` int   DEFAULT 0101 COMMENT 'Month and day (MMDD) that fixed period type subscription or membership starts.',
     `duration_unit` varchar(8)   DEFAULT 'year' ,
     `duration_interval` int    COMMENT 'Number of units for total duration of subscription, service, membership (e.g. 12 Months).',
     `frequency_unit` varchar(8)   DEFAULT 'month' COMMENT 'Frequency unit and interval allow option to store actual delivery frequency for a subscription or service.',
     `frequency_interval` int    COMMENT 'Number of units for delivery frequency of subscription, service, membership (e.g. every 3 Months).' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_product_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_premiums_product
-- *
-- * joins premiums (settings) to individual product/premium items - determines which products are available for a given contribution page
-- *
-- *******************************************************/
CREATE TABLE `civicrm_premiums_product` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution ID',
     `premiums_id` int unsigned NOT NULL   COMMENT 'Foreign key to premiums settings record.',
     `product_id` int unsigned NOT NULL   COMMENT 'Foreign key to each product object.',
     `weight` int unsigned NOT NULL   ,
     `financial_type_id` int unsigned   DEFAULT NULL COMMENT 'FK to Financial Type.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_premiums_product_premiums_id FOREIGN KEY (`premiums_id`) REFERENCES `civicrm_premiums`(`id`) ,          CONSTRAINT FK_civicrm_premiums_product_product_id FOREIGN KEY (`product_id`) REFERENCES `civicrm_product`(`id`) ,          CONSTRAINT FK_civicrm_premiums_product_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_contribution_widget
-- *
-- * A Widget object to store meta information about a single customized contribution widget
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_widget` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution Id',
     `contribution_page_id` int unsigned    COMMENT 'The Contribution Page which triggered this contribution',
     `is_active` tinyint    COMMENT 'Is this property active?',
     `title` varchar(255)    COMMENT 'Widget title.',
     `url_logo` varchar(255)    COMMENT 'URL to Widget logo',
     `button_title` varchar(255)    COMMENT 'Button title.',
     `about` text    COMMENT 'About description.',
     `url_homepage` varchar(255)    COMMENT 'URL to Homepage.',
     `color_title` varchar(10)    ,
     `color_button` varchar(10)    ,
     `color_bar` varchar(10)    ,
     `color_main_text` varchar(10)    ,
     `color_main` varchar(10)    ,
     `color_main_bg` varchar(10)    ,
     `color_bg` varchar(10)    ,
     `color_about_link` varchar(10)    ,
     `color_homepage_link` varchar(10)     
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_contribution_widget_contribution_page_id FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_payment_processor
-- *
-- *******************************************************/
CREATE TABLE `civicrm_payment_processor` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Payment Processor ID',
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this match entry for',
     `name` varchar(64)    COMMENT 'Payment Processor Name.',
     `description` varchar(255)    COMMENT 'Payment Processor Description.',
     `payment_processor_type_id` int unsigned    ,
     `is_active` tinyint    COMMENT 'Is this processor active?',
     `is_default` tinyint    COMMENT 'Is this processor the default?',
     `is_test` tinyint    COMMENT 'Is this processor for a test site?',
     `user_name` varchar(255)    ,
     `password` varchar(255)    ,
     `signature` text    ,
     `url_site` varchar(255)    ,
     `url_api` varchar(255)    ,
     `url_recur` varchar(255)    ,
     `url_button` varchar(255)    ,
     `subject` varchar(255)    ,
     `class_name` varchar(255)    ,
     `billing_mode` int unsigned NOT NULL   COMMENT 'Billing Mode (deprecated)',
     `is_recur` tinyint    COMMENT 'Can process recurring contributions',
     `payment_type` int unsigned   DEFAULT 1 COMMENT 'Payment Type: Credit or Debit (deprecated)',
     `payment_instrument_id` int unsigned   DEFAULT 1 COMMENT 'Payment Instrument ID',
     `accepted_credit_cards` text   DEFAULT NULL COMMENT 'array of accepted credit card types' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name_test_domain_id`(
        name
      , is_test
      , domain_id
  )
  
,          CONSTRAINT FK_civicrm_payment_processor_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ,          CONSTRAINT FK_civicrm_payment_processor_payment_processor_type_id FOREIGN KEY (`payment_processor_type_id`) REFERENCES `civicrm_payment_processor_type`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_payment_token
-- *
-- * Payment Token
-- *
-- *******************************************************/
CREATE TABLE `civicrm_payment_token` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Payment Token ID',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact ID for the owner of the token',
     `payment_processor_id` int unsigned NOT NULL   ,
     `token` varchar(255) NOT NULL   COMMENT 'Externally provided token string',
     `created_date` timestamp   DEFAULT CURRENT_TIMESTAMP COMMENT 'Date created',
     `created_id` int unsigned    COMMENT 'Contact ID of token creator',
     `expiry_date` datetime    COMMENT 'Date this token expires',
     `email` varchar(255)    COMMENT 'Email at the time of token creation. Useful for fraud forensics',
     `billing_first_name` varchar(255)    COMMENT 'Billing first name at the time of token creation. Useful for fraud forensics',
     `billing_middle_name` varchar(255)    COMMENT 'Billing middle name at the time of token creation. Useful for fraud forensics',
     `billing_last_name` varchar(255)    COMMENT 'Billing last name at the time of token creation. Useful for fraud forensics',
     `masked_account_number` varchar(255)    COMMENT 'Holds the part of the card number or account details that may be retained or displayed',
     `ip_address` varchar(255)    COMMENT 'IP used when creating the token. Useful for fraud forensics' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_payment_token_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_payment_token_payment_processor_id FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor`(`id`) ON DELETE RESTRICT,          CONSTRAINT FK_civicrm_payment_token_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_sms_provider
-- *
-- * Table to add different sms providers 
-- *
-- *******************************************************/
CREATE TABLE `civicrm_sms_provider` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'SMS Provider ID',
     `name` varchar(64)    COMMENT 'Provider internal name points to option_value of option_group sms_provider_name',
     `title` varchar(64)    COMMENT 'Provider name visible to user',
     `username` varchar(255)    ,
     `password` varchar(255)    ,
     `api_type` int unsigned NOT NULL   COMMENT 'points to value in civicrm_option_value for group sms_api_type',
     `api_url` varchar(128)    ,
     `api_params` text    COMMENT 'the api params in xml, http or smtp format',
     `is_default` tinyint   DEFAULT 0 ,
     `is_active` tinyint   DEFAULT 0 ,
     `domain_id` int unsigned    COMMENT 'Which Domain is this sms provider for' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_sms_provider_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_membership_type
-- *
-- * Sites can configure multiple types of memberships. They encode the owner organization, fee, and the rules needed to set start and end (expire) dates when a member signs up for that type.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership_type` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Membership Id',
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this match entry for',
     `name` varchar(128)    COMMENT 'Name of Membership Type',
     `description` varchar(255)    COMMENT 'Description of Membership Type',
     `member_of_contact_id` int unsigned NOT NULL   COMMENT 'Owner organization for this membership type. FK to Contact ID',
     `financial_type_id` int unsigned NOT NULL   COMMENT 'If membership is paid by a contribution - what financial type should be used. FK to civicrm_financial_type.id',
     `minimum_fee` decimal(18,9)   DEFAULT 0 COMMENT 'Minimum fee for this membership (0 for free/complimentary memberships).',
     `duration_unit` varchar(8)    COMMENT 'Unit in which membership period is expressed.',
     `duration_interval` int    COMMENT 'Number of duration units in membership period (e.g. 1 year, 12 months).',
     `period_type` varchar(8)    COMMENT 'Rolling membership period starts on signup date. Fixed membership periods start on fixed_period_start_day.',
     `fixed_period_start_day` int    COMMENT 'For fixed period memberships, month and day (mmdd) on which subscription/membership will start. Period start is back-dated unless after rollover day.',
     `fixed_period_rollover_day` int    COMMENT 'For fixed period memberships, signups after this day (mmdd) rollover to next period.',
     `relationship_type_id` varchar(64)    COMMENT 'FK to Relationship Type ID',
     `relationship_direction` varchar(128)    ,
     `max_related` int    COMMENT 'Maximum number of related memberships.',
     `visibility` varchar(64)    ,
     `weight` int    ,
     `receipt_text_signup` varchar(255)    COMMENT 'Receipt Text for membership signup',
     `receipt_text_renewal` varchar(255)    COMMENT 'Receipt Text for membership renewal',
     `auto_renew` tinyint   DEFAULT 0 COMMENT '0 = No auto-renew option; 1 = Give option, but not required; 2 = Auto-renew required;',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this membership_type enabled' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_relationship_type_id`(
        relationship_type_id
  )
  
,          CONSTRAINT FK_civicrm_membership_type_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ,          CONSTRAINT FK_civicrm_membership_type_member_of_contact_id FOREIGN KEY (`member_of_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE RESTRICT,          CONSTRAINT FK_civicrm_membership_type_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_membership_block
-- *
-- * A Membership Block stores admin configurable status options and rules
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership_block` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Membership Id',
     `entity_table` varchar(64)    COMMENT 'Name for Membership Status',
     `entity_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_contribution_page.id',
     `membership_types` varchar(1024)    COMMENT 'Membership types to be exposed by this block',
     `membership_type_default` int unsigned    COMMENT 'Optional foreign key to membership_type',
     `display_min_fee` tinyint   DEFAULT 1 COMMENT 'Display minimum membership fee',
     `is_separate_payment` tinyint   DEFAULT 1 COMMENT 'Should membership transactions be processed separately',
     `new_title` varchar(255)    COMMENT 'Title to display at top of block',
     `new_text` text    COMMENT 'Text to display below title',
     `renewal_title` varchar(255)    COMMENT 'Title for renewal',
     `renewal_text` text    COMMENT 'Text to display for member renewal',
     `is_required` tinyint   DEFAULT 0 COMMENT 'Is membership sign up optional',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this membership_block enabled' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_membership_block_entity_id FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contribution_page`(`id`) ,          CONSTRAINT FK_civicrm_membership_block_membership_type_default FOREIGN KEY (`membership_type_default`) REFERENCES `civicrm_membership_type`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_case
-- *
-- * This table stores information about cases grouping activities.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_case` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Case ID',
     `case_type_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_case_type.id',
     `subject` varchar(128)    COMMENT 'Short name of the case.',
     `start_date` date    COMMENT 'Date on which given case starts.',
     `end_date` date    COMMENT 'Date on which given case ends.',
     `details` text    COMMENT 'Details about the meeting (agenda, notes, etc).',
     `status_id` int unsigned NOT NULL   COMMENT 'Id of case status.',
     `is_deleted` tinyint   DEFAULT 0 ,
     `created_date` timestamp NULL  DEFAULT NULL COMMENT 'When was the case was created.',
     `modified_date` timestamp NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the case (or closely related entity) was created or modified or deleted.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_case_type_id`(
        case_type_id
  )
  ,     INDEX `index_is_deleted`(
        is_deleted
  )
  
,          CONSTRAINT FK_civicrm_case_case_type_id FOREIGN KEY (`case_type_id`) REFERENCES `civicrm_case_type`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_case_contact
-- *
-- * Joining table for case-contact associations.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_case_contact` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique case-contact association id',
     `case_id` int unsigned NOT NULL   COMMENT 'Case ID of case-contact association.',
     `contact_id` int unsigned NOT NULL   COMMENT 'Contact ID of contact record given case belongs to.' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_case_contact_id`(
        case_id
      , contact_id
  )
  
,          CONSTRAINT FK_civicrm_case_contact_case_id FOREIGN KEY (`case_id`) REFERENCES `civicrm_case`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_case_contact_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_pledge
-- *
-- *******************************************************/
CREATE TABLE `civicrm_pledge` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Pledge ID',
     `contact_id` int unsigned NOT NULL   COMMENT 'Foreign key to civicrm_contact.id .',
     `financial_type_id` int unsigned    COMMENT 'FK to Financial Type',
     `contribution_page_id` int unsigned    COMMENT 'The Contribution Page which triggered this contribution',
     `amount` decimal(20,2) NOT NULL   COMMENT 'Total pledged amount.',
     `original_installment_amount` decimal(20,2) NOT NULL   COMMENT 'Original amount for each of the installments.',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `frequency_unit` varchar(8)   DEFAULT 'month' COMMENT 'Time units for recurrence of pledge payments.',
     `frequency_interval` int unsigned NOT NULL  DEFAULT 1 COMMENT 'Number of time units for recurrence of pledge payments.',
     `frequency_day` int unsigned NOT NULL  DEFAULT 3 COMMENT 'Day in the period when the pledge payment is due e.g. 1st of month, 15th etc. Use this to set the scheduled dates for pledge payments.',
     `installments` int unsigned   DEFAULT 1 COMMENT 'Total number of payments to be made.',
     `start_date` datetime NOT NULL   COMMENT 'The date the first scheduled pledge occurs.',
     `create_date` datetime NOT NULL   COMMENT 'When this pledge record was created.',
     `acknowledge_date` datetime    COMMENT 'When a pledge acknowledgement message was sent to the contributor.',
     `modified_date` datetime    COMMENT 'Last updated date for this pledge record.',
     `cancel_date` datetime    COMMENT 'Date this pledge was cancelled by contributor.',
     `end_date` datetime    COMMENT 'Date this pledge finished successfully (total pledge payments equal to or greater than pledged amount).',
     `max_reminders` int unsigned   DEFAULT 1 COMMENT 'The maximum number of payment reminders to send for any given payment.',
     `initial_reminder_day` int unsigned   DEFAULT 5 COMMENT 'Send initial reminder this many days prior to the payment due date.',
     `additional_reminder_day` int unsigned   DEFAULT 5 COMMENT 'Send additional reminder this many days after last one sent, up to maximum number of reminders.',
     `status_id` int unsigned    COMMENT 'Implicit foreign key to civicrm_option_values in the pledge_status option group.',
     `is_test` tinyint   DEFAULT 0 ,
     `campaign_id` int unsigned    COMMENT 'The campaign for which this pledge has been initiated.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_status`(
        status_id
  )
  
,          CONSTRAINT FK_civicrm_pledge_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_pledge_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ,          CONSTRAINT FK_civicrm_pledge_contribution_page_id FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_pledge_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_report_instance
-- *
-- * Users can save their report instance and put in a cron tab etc.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_report_instance` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Report Instance ID',
     `domain_id` int unsigned NOT NULL   COMMENT 'Which Domain is this instance for',
     `title` varchar(255)    COMMENT 'Report Instance Title.',
     `report_id` varchar(512) NOT NULL   COMMENT 'FK to civicrm_option_value for the report template',
     `name` varchar(255)    COMMENT 'when combined with report_id/template uniquely identifies the instance',
     `args` varchar(255)    COMMENT 'arguments that are passed in the url when invoking the instance',
     `description` varchar(255)    COMMENT 'Report Instance description.',
     `permission` varchar(255)    COMMENT 'permission required to be able to run this instance',
     `grouprole` varchar(1024)    COMMENT 'role required to be able to run this instance',
     `form_values` text    COMMENT 'Submitted form values for this report',
     `is_active` tinyint    COMMENT 'Is this entry active?',
     `created_id` int unsigned    COMMENT 'FK to contact table.',
     `owner_id` int unsigned    COMMENT 'FK to contact table.',
     `email_subject` varchar(255)    COMMENT 'Subject of email',
     `email_to` text    COMMENT 'comma-separated list of email addresses to send the report to',
     `email_cc` text    COMMENT 'comma-separated list of email addresses to send the report to',
     `header` text    COMMENT 'comma-separated list of email addresses to send the report to',
     `footer` text    COMMENT 'comma-separated list of email addresses to send the report to',
     `navigation_id` int unsigned    COMMENT 'FK to navigation ID',
     `drilldown_id` int unsigned    COMMENT 'FK to instance ID drilldown to',
     `is_reserved` tinyint   DEFAULT 0  
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_report_instance_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ,          CONSTRAINT FK_civicrm_report_instance_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_report_instance_owner_id FOREIGN KEY (`owner_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_report_instance_navigation_id FOREIGN KEY (`navigation_id`) REFERENCES `civicrm_navigation`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_report_instance_drilldown_id FOREIGN KEY (`drilldown_id`) REFERENCES `civicrm_report_instance`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_price_set
-- *
-- *******************************************************/
CREATE TABLE `civicrm_price_set` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Price Set',
     `domain_id` int unsigned    COMMENT 'Which Domain is this price-set for',
     `name` varchar(255) NOT NULL   COMMENT 'Variable name/programmatic handle for this set of price fields.',
     `title` varchar(255) NOT NULL   COMMENT 'Displayed title for the Price Set.',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this price set active',
     `help_pre` text    COMMENT 'Description and/or help text to display before fields in form.',
     `help_post` text    COMMENT 'Description and/or help text to display after fields in form.',
     `javascript` varchar(64)    COMMENT 'Optional Javascript script function(s) included on the form with this price_set. Can be used for conditional',
     `extends` varchar(255) NOT NULL   COMMENT 'What components are using this price set?',
     `financial_type_id` int unsigned   DEFAULT NULL COMMENT 'FK to Financial Type(for membership price sets only).',
     `is_quick_config` tinyint   DEFAULT 0 COMMENT 'Is set if edited on Contribution or Event Page rather than through Manage Price Sets',
     `is_reserved` tinyint   DEFAULT 0 COMMENT 'Is this a predefined system price set  (i.e. it can not be deleted, edited)?',
     `min_amount` int unsigned   DEFAULT 0 COMMENT 'Minimum Amount required for this set.' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_price_set_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ,          CONSTRAINT FK_civicrm_price_set_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_price_set_entity
-- *
-- *******************************************************/
CREATE TABLE `civicrm_price_set_entity` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Price Set Entity',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Table which uses this price set',
     `entity_id` int unsigned NOT NULL   COMMENT 'Item in table',
     `price_set_id` int unsigned NOT NULL   COMMENT 'price set being used' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_entity`(
        entity_table
      , entity_id
  )
  
,          CONSTRAINT FK_civicrm_price_set_entity_price_set_id FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_county
-- *
-- *******************************************************/
CREATE TABLE `civicrm_county` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'County ID',
     `name` varchar(64)    COMMENT 'Name of County',
     `abbreviation` varchar(4)    COMMENT '2-4 Character Abbreviation of County',
     `state_province_id` int unsigned NOT NULL   COMMENT 'ID of State/Province that County belongs' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name_state_id`(
        name
      , state_province_id
  )
  
,          CONSTRAINT FK_civicrm_county_state_province_id FOREIGN KEY (`state_province_id`) REFERENCES `civicrm_state_province`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_dashboard
-- *
-- * Table to store dashboard.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_dashboard` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `domain_id` int unsigned NOT NULL   COMMENT 'Domain for dashboard',
     `name` varchar(64)    COMMENT 'Internal name of dashlet.',
     `label` varchar(255)    COMMENT 'dashlet title',
     `url` varchar(255)    COMMENT 'url in case of external dashlet',
     `permission` varchar(255)    COMMENT 'Permission for the dashlet',
     `permission_operator` varchar(3)    COMMENT 'Permission Operator',
     `fullscreen_url` varchar(255)    COMMENT 'fullscreen url for dashlet',
     `is_active` tinyint   DEFAULT 0 COMMENT 'Is this dashlet active?',
     `is_reserved` tinyint   DEFAULT 0 COMMENT 'Is this dashlet reserved?',
     `cache_minutes` int unsigned NOT NULL  DEFAULT 60 COMMENT 'Number of minutes to cache dashlet content in browser localStorage.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_dashboard_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_discount
-- *
-- * Stores discounts for events on the basis of date
-- *
-- *******************************************************/
CREATE TABLE `civicrm_discount` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
     `entity_table` varchar(64)    COMMENT 'physical tablename for entity being joined to discount, e.g. civicrm_event',
     `entity_id` int unsigned NOT NULL   COMMENT 'FK to entity table specified in entity_table column.',
     `price_set_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_price_set',
     `start_date` date    COMMENT 'Date when discount starts.',
     `end_date` date    COMMENT 'Date when discount ends.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  ,     INDEX `index_entity_option_id`(
        entity_table
      , entity_id
      , price_set_id
  )
  
,          CONSTRAINT FK_civicrm_discount_price_set_id FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_entity_tag
-- *
-- * Tag entities (Contacts, Groups, Actions) to categories.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_tag` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
     `entity_table` varchar(64)    COMMENT 'physical tablename for entity being joined to file, e.g. civicrm_contact',
     `entity_id` int unsigned NOT NULL   COMMENT 'FK to entity table specified in entity_table column.',
     `tag_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_tag' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_entity_id_entity_table_tag_id`(
        entity_id
      , entity_table
      , tag_id
  )
  
,          CONSTRAINT FK_civicrm_entity_tag_tag_id FOREIGN KEY (`tag_id`) REFERENCES `civicrm_tag`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_uf_group
-- *
-- * User framework groups. Each group represents a form which encompasses a set of fields defined in civicrm_uf_fields table. Initially will be used for CiviCRM Profile form(s). Subsequently we anticipate using this to define other public facing forms (e.g. online donation solicitation forms, mailing list preferences, etc.).
-- *
-- *******************************************************/
CREATE TABLE `civicrm_uf_group` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this form currently active? If false, hide all related fields for all sharing contexts.',
     `group_type` varchar(255)    COMMENT 'This column will store a comma separated list of the type(s) of profile fields.',
     `title` varchar(64) NOT NULL   COMMENT 'Form title.',
     `description` text    COMMENT 'Optional verbose description of the profile.',
     `help_pre` text    COMMENT 'Description and/or help text to display before fields in form.',
     `help_post` text    COMMENT 'Description and/or help text to display after fields in form.',
     `limit_listings_group_id` int unsigned    COMMENT 'Group id, foreign key from civicrm_group',
     `post_URL` varchar(255)    COMMENT 'Redirect to URL.',
     `add_to_group_id` int unsigned    COMMENT 'foreign key to civicrm_group_id',
     `add_captcha` tinyint   DEFAULT 0 COMMENT 'Should a CAPTCHA widget be included this Profile form.',
     `is_map` tinyint   DEFAULT 0 COMMENT 'Do we want to map results from this profile.',
     `is_edit_link` tinyint   DEFAULT 0 COMMENT 'Should edit link display in profile selector',
     `is_uf_link` tinyint   DEFAULT 0 COMMENT 'Should we display a link to the website profile in profile selector',
     `is_update_dupe` tinyint   DEFAULT 0 COMMENT 'Should we update the contact record if we find a duplicate',
     `cancel_URL` varchar(255)    COMMENT 'Redirect to URL when Cancle button clik .',
     `is_cms_user` tinyint   DEFAULT 0 COMMENT 'Should we create a cms user for this profile ',
     `notify` text    ,
     `is_reserved` tinyint    COMMENT 'Is this group reserved for use by some other CiviCRM functionality?',
     `name` varchar(64)    COMMENT 'Name of the UF group for directly addressing it in the codebase',
     `created_id` int unsigned    COMMENT 'FK to civicrm_contact, who created this UF group',
     `created_date` datetime    COMMENT 'Date and time this UF group was created.',
     `is_proximity_search` tinyint   DEFAULT 0 COMMENT 'Should we include proximity search feature in this profile search form?',
     `cancel_button_text` varchar(64)   DEFAULT NULL COMMENT 'Custom Text to display on the Cancel button when used in create or edit mode',
     `submit_button_text` varchar(64)   DEFAULT NULL COMMENT 'Custom Text to display on the submit button on profile edit/create screens' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_uf_group_limit_listings_group_id FOREIGN KEY (`limit_listings_group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_uf_group_add_to_group_id FOREIGN KEY (`add_to_group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_uf_group_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_uf_field
-- *
-- * User Framework fields and their properties.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_uf_field` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
     `uf_group_id` int unsigned NOT NULL   COMMENT 'Which form does this field belong to.',
     `field_name` varchar(64) NOT NULL   COMMENT 'Name for CiviCRM field which is being exposed for sharing.',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this field currently shareable? If false, hide the field for all sharing contexts.',
     `is_view` tinyint   DEFAULT 0 COMMENT 'the field is view only and not editable in user forms.',
     `is_required` tinyint   DEFAULT 0 COMMENT 'Is this field required when included in a user or registration form?',
     `weight` int NOT NULL  DEFAULT 1 COMMENT 'Controls field display order when user framework fields are displayed in registration and account editing forms.',
     `help_post` text    COMMENT 'Description and/or help text to display after this field.',
     `help_pre` text    COMMENT 'Description and/or help text to display before this field.',
     `visibility` varchar(32)   DEFAULT 'User and User Admin Only' COMMENT 'In what context(s) is this field visible.',
     `in_selector` tinyint   DEFAULT 0 COMMENT 'Is this field included as a column in the selector table?',
     `is_searchable` tinyint   DEFAULT 0 COMMENT 'Is this field included search form of profile?',
     `location_type_id` int unsigned    COMMENT 'Location type of this mapping, if required',
     `phone_type_id` int unsigned    COMMENT 'Phone Type Id, if required',
     `website_type_id` int unsigned    COMMENT 'Website Type Id, if required',
     `label` varchar(255) NOT NULL   COMMENT 'To save label for fields.',
     `field_type` varchar(255)    COMMENT 'This field saves field type (ie individual,household.. field etc).',
     `is_reserved` tinyint    COMMENT 'Is this field reserved for use by some other CiviCRM functionality?',
     `is_multi_summary` tinyint   DEFAULT 0 COMMENT 'Include in multi-record listing?' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `IX_website_type_id`(
        website_type_id
  )
  
,          CONSTRAINT FK_civicrm_uf_field_uf_group_id FOREIGN KEY (`uf_group_id`) REFERENCES `civicrm_uf_group`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_uf_field_location_type_id FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_uf_join
-- *
-- * User framework join table. This links various internal civicrm object with a profile. Initial use cases are the donation object and the user module
-- *
-- *******************************************************/
CREATE TABLE `civicrm_uf_join` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique table ID',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this join currently active?',
     `module` varchar(64) NOT NULL   COMMENT 'Module which owns this uf_join instance, e.g. User Registration, CiviDonate, etc.',
     `entity_table` varchar(64)    COMMENT 'Name of table where item being referenced is stored. Modules which only need a single collection of uf_join instances may choose not to populate entity_table and entity_id.',
     `entity_id` int unsigned    COMMENT 'Foreign key to the referenced item.',
     `weight` int NOT NULL  DEFAULT 1 COMMENT 'Controls display order when multiple user framework groups are setup for concurrent display.',
     `uf_group_id` int unsigned NOT NULL   COMMENT 'Which form does this field belong to.',
     `module_data` longtext    COMMENT 'Json serialized array of data used by the ufjoin.module' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  
,          CONSTRAINT FK_civicrm_uf_join_uf_group_id FOREIGN KEY (`uf_group_id`) REFERENCES `civicrm_uf_group`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_action_schedule
-- *
-- * Table to store the reminders.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_action_schedule` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `name` varchar(64)    COMMENT 'Name of the action(reminder)',
     `title` varchar(64)    COMMENT 'Title of the action(reminder)',
     `recipient` varchar(64)    COMMENT 'Recipient',
     `limit_to` tinyint    COMMENT 'Is this the recipient criteria limited to OR in addition to?',
     `entity_value` varchar(255)    COMMENT 'Entity value',
     `entity_status` varchar(64)    COMMENT 'Entity status',
     `start_action_offset` int unsigned    COMMENT 'Reminder Interval.',
     `start_action_unit` varchar(8)    COMMENT 'Time units for reminder.',
     `start_action_condition` varchar(64)    COMMENT 'Reminder Action',
     `start_action_date` varchar(64)    COMMENT 'Entity date',
     `is_repeat` tinyint   DEFAULT 0 ,
     `repetition_frequency_unit` varchar(8)    COMMENT 'Time units for repetition of reminder.',
     `repetition_frequency_interval` int unsigned    COMMENT 'Time interval for repeating the reminder.',
     `end_frequency_unit` varchar(8)    COMMENT 'Time units till repetition of reminder.',
     `end_frequency_interval` int unsigned    COMMENT 'Time interval till repeating the reminder.',
     `end_action` varchar(32)    COMMENT 'Reminder Action till repeating the reminder.',
     `end_date` varchar(64)    COMMENT 'Entity end date',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this option active?',
     `recipient_manual` varchar(128)    COMMENT 'Contact IDs to which reminder should be sent.',
     `recipient_listing` varchar(128)    COMMENT 'listing based on recipient field.',
     `body_text` longtext    COMMENT 'Body of the mailing in text format.',
     `body_html` longtext    COMMENT 'Body of the mailing in html format.',
     `sms_body_text` longtext    COMMENT 'Content of the SMS text.',
     `subject` varchar(128)    COMMENT 'Subject of mailing',
     `record_activity` tinyint   DEFAULT NULL COMMENT 'Record Activity for this reminder?',
     `mapping_id` varchar(64)    COMMENT 'Name/ID of the mapping to use on this table',
     `group_id` int unsigned    COMMENT 'FK to Group',
     `msg_template_id` int unsigned    COMMENT 'FK to the message template.',
     `sms_template_id` int unsigned    COMMENT 'FK to the message template.',
     `absolute_date` date    COMMENT 'Date on which the reminder be sent.',
     `from_name` varchar(255)    COMMENT 'Name in \"from\" field',
     `from_email` varchar(255)    COMMENT 'Email address in \"from\" field',
     `mode` varchar(128)   DEFAULT "Email" COMMENT 'Send the message as email or sms or both.',
     `sms_provider_id` int unsigned    ,
     `used_for` varchar(64)    COMMENT 'Used for repeating entity',
     `filter_contact_language` varchar(128)    COMMENT 'Used for multilingual installation',
     `communication_language` varchar(8)    COMMENT 'Used for multilingual installation' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_action_schedule_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_action_schedule_msg_template_id FOREIGN KEY (`msg_template_id`) REFERENCES `civicrm_msg_template`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_action_schedule_sms_template_id FOREIGN KEY (`sms_template_id`) REFERENCES `civicrm_msg_template`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_action_schedule_sms_provider_id FOREIGN KEY (`sms_provider_id`) REFERENCES `civicrm_sms_provider`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_action_log
-- *
-- * Table to store log for the reminder.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_action_log` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `contact_id` int unsigned    COMMENT 'FK to Contact ID',
     `entity_id` int unsigned NOT NULL   COMMENT 'FK to id of the entity that the action was performed on. Pseudo - FK.',
     `entity_table` varchar(255)    COMMENT 'name of the entity table for the above id, e.g. civicrm_activity, civicrm_participant',
     `action_schedule_id` int unsigned NOT NULL   COMMENT 'FK to the action schedule that this action originated from.',
     `action_date_time` datetime    COMMENT 'date time that the action was performed on.',
     `is_error` tinyint   DEFAULT 0 COMMENT 'Was there any error sending the reminder?',
     `message` text    COMMENT 'Description / text in case there was an error encountered.',
     `repetition_number` int unsigned    COMMENT 'Keeps track of the sequence number of this repetition.',
     `reference_date` date   DEFAULT NULL COMMENT 'Stores the date from the entity which triggered this reminder action (e.g. membership.end_date for most membership renewal reminders)' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_action_log_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_action_log_action_schedule_id FOREIGN KEY (`action_schedule_id`) REFERENCES `civicrm_action_schedule`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_dashboard_contact
-- *
-- * Table to store dashboard for each contact.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_dashboard_contact` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `dashboard_id` int unsigned NOT NULL   COMMENT 'Dashboard ID',
     `contact_id` int unsigned NOT NULL   COMMENT 'Contact ID',
     `column_no` tinyint   DEFAULT 0 COMMENT 'column no for this widget',
     `is_active` tinyint   DEFAULT 0 COMMENT 'Is this widget active?',
     `weight` int   DEFAULT 0 COMMENT 'Ordering of the widgets.' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `index_dashboard_id_contact_id`(
        dashboard_id
      , contact_id
  )
  
,          CONSTRAINT FK_civicrm_dashboard_contact_dashboard_id FOREIGN KEY (`dashboard_id`) REFERENCES `civicrm_dashboard`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_dashboard_contact_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_relationship
-- *
-- * Relationship between any 2 types of contacts.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_relationship` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Relationship ID',
     `contact_id_a` int unsigned NOT NULL   COMMENT 'id of the first contact',
     `contact_id_b` int unsigned NOT NULL   COMMENT 'id of the second contact',
     `relationship_type_id` int unsigned NOT NULL   COMMENT 'id of the relationship',
     `start_date` date    COMMENT 'date when the relationship started',
     `end_date` date    COMMENT 'date when the relationship ended',
     `is_active` tinyint   DEFAULT 1 COMMENT 'is the relationship active ?',
     `description` varchar(255)    COMMENT 'Optional verbose description for the relationship.',
     `is_permission_a_b` tinyint   DEFAULT 0 COMMENT 'is contact a has permission to view / edit contact and\n      related data for contact b ?\n    ',
     `is_permission_b_a` tinyint   DEFAULT 0 COMMENT 'is contact b has permission to view / edit contact and\n      related data for contact a ?\n    ',
     `case_id` int unsigned   DEFAULT NULL COMMENT 'FK to civicrm_case' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_relationship_contact_id_a FOREIGN KEY (`contact_id_a`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_relationship_contact_id_b FOREIGN KEY (`contact_id_b`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_relationship_relationship_type_id FOREIGN KEY (`relationship_type_id`) REFERENCES `civicrm_relationship_type`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_relationship_case_id FOREIGN KEY (`case_id`) REFERENCES `civicrm_case`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing
-- *
-- * Stores information about a mailing.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `domain_id` int unsigned    COMMENT 'Which site is this mailing for',
     `header_id` int unsigned    COMMENT 'FK to the header component.',
     `footer_id` int unsigned    COMMENT 'FK to the footer component.',
     `reply_id` int unsigned    COMMENT 'FK to the auto-responder component.',
     `unsubscribe_id` int unsigned    COMMENT 'FK to the unsubscribe component.',
     `resubscribe_id` int unsigned    ,
     `optout_id` int unsigned    COMMENT 'FK to the opt-out component.',
     `name` varchar(128)    COMMENT 'Mailing Name.',
     `mailing_type` varchar(32)    COMMENT 'differentiate between standalone mailings, A/B tests, and A/B final-winner',
     `from_name` varchar(128)    COMMENT 'From Header of mailing',
     `from_email` varchar(128)    COMMENT 'From Email of mailing',
     `replyto_email` varchar(128)    COMMENT 'Reply-To Email of mailing',
     `template_type` varchar(64) NOT NULL  DEFAULT 'traditional' COMMENT 'The language/processing system used for email templates.',
     `template_options` longtext    COMMENT 'Advanced options used by the email templating system. (JSON encoded)',
     `subject` varchar(128)    COMMENT 'Subject of mailing',
     `body_text` longtext    COMMENT 'Body of the mailing in text format.',
     `body_html` longtext    COMMENT 'Body of the mailing in html format.',
     `url_tracking` tinyint    COMMENT 'Should we track URL click-throughs for this mailing?',
     `forward_replies` tinyint    COMMENT 'Should we forward replies back to the author?',
     `auto_responder` tinyint    COMMENT 'Should we enable the auto-responder?',
     `open_tracking` tinyint    COMMENT 'Should we track when recipients open/read this mailing?',
     `is_completed` tinyint    COMMENT 'Has at least one job associated with this mailing finished?',
     `msg_template_id` int unsigned    COMMENT 'FK to the message template.',
     `override_verp` tinyint   DEFAULT 0 COMMENT 'Should we overrite VERP address in Reply-To',
     `created_id` int unsigned    COMMENT 'FK to Contact ID who first created this mailing',
     `created_date` timestamp NULL  DEFAULT NULL COMMENT 'Date and time this mailing was created.',
     `modified_date` timestamp NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When the mailing (or closely related entity) was created or modified or deleted.',
     `scheduled_id` int unsigned    COMMENT 'FK to Contact ID who scheduled this mailing',
     `scheduled_date` timestamp NULL  DEFAULT NULL COMMENT 'Date and time this mailing was scheduled.',
     `approver_id` int unsigned    COMMENT 'FK to Contact ID who approved this mailing',
     `approval_date` timestamp NULL  DEFAULT NULL COMMENT 'Date and time this mailing was approved.',
     `approval_status_id` int unsigned    COMMENT 'The status of this mailing. Values: none, approved, rejected',
     `approval_note` longtext    COMMENT 'Note behind the decision.',
     `is_archived` tinyint   DEFAULT 0 COMMENT 'Is this mailing archived?',
     `visibility` varchar(40)   DEFAULT 'Public Pages' COMMENT 'In what context(s) is the mailing contents visible (online viewing)',
     `campaign_id` int unsigned    COMMENT 'The campaign for which this mailing has been initiated.',
     `dedupe_email` tinyint   DEFAULT 0 COMMENT 'Remove duplicate emails?',
     `sms_provider_id` int unsigned    ,
     `hash` varchar(16)    COMMENT 'Key for validating requests related to this mailing.',
     `location_type_id` int unsigned    COMMENT 'With email_selection_method, determines which email address to use',
     `email_selection_method` varchar(20)   DEFAULT 'automatic' COMMENT 'With location_type_id, determine how to choose the email address to use.',
     `language` varchar(5)    COMMENT 'Language of the content of the mailing. Useful for tokens.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_hash`(
        hash
  )
  
,          CONSTRAINT FK_civicrm_mailing_domain_id FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_header_id FOREIGN KEY (`header_id`) REFERENCES `civicrm_mailing_component`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_footer_id FOREIGN KEY (`footer_id`) REFERENCES `civicrm_mailing_component`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_reply_id FOREIGN KEY (`reply_id`) REFERENCES `civicrm_mailing_component`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_unsubscribe_id FOREIGN KEY (`unsubscribe_id`) REFERENCES `civicrm_mailing_component`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_optout_id FOREIGN KEY (`optout_id`) REFERENCES `civicrm_mailing_component`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_msg_template_id FOREIGN KEY (`msg_template_id`) REFERENCES `civicrm_msg_template`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_scheduled_id FOREIGN KEY (`scheduled_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_approver_id FOREIGN KEY (`approver_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_sms_provider_id FOREIGN KEY (`sms_provider_id`) REFERENCES `civicrm_sms_provider`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_location_type_id FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_group
-- *
-- * Stores information about the groups that participate in this mailing..
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_group` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `mailing_id` int unsigned NOT NULL   COMMENT 'The ID of a previous mailing to include/exclude recipients.',
     `group_type` varchar(8)    COMMENT 'Are the members of the group included or excluded?.',
     `entity_table` varchar(64) NOT NULL   COMMENT 'Name of table where item being referenced is stored.',
     `entity_id` int unsigned NOT NULL   COMMENT 'Foreign key to the referenced item.',
     `search_id` int    COMMENT 'The filtering search. custom search id or -1 for civicrm api search',
     `search_args` text    COMMENT 'The arguments to be sent to the search function' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_group_mailing_id FOREIGN KEY (`mailing_id`) REFERENCES `civicrm_mailing`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_trackable_url
-- *
-- * Stores URLs for which we should track click-throughs from mailings
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_trackable_url` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `url` text NOT NULL   COMMENT 'The URL to be tracked.',
     `mailing_id` int unsigned NOT NULL   COMMENT 'FK to the mailing' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_trackable_url_mailing_id FOREIGN KEY (`mailing_id`) REFERENCES `civicrm_mailing`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_job
-- *
-- * Stores information about the job that executes this mailing
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_job` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `mailing_id` int unsigned NOT NULL   COMMENT 'The ID of the mailing this Job will send.',
     `scheduled_date` timestamp NULL  DEFAULT NULL COMMENT 'date on which this job was scheduled.',
     `start_date` timestamp NULL  DEFAULT NULL COMMENT 'date on which this job was started.',
     `end_date` timestamp NULL  DEFAULT NULL COMMENT 'date on which this job ended.',
     `status` varchar(12)    COMMENT 'The state of this job',
     `is_test` tinyint   DEFAULT 0 COMMENT 'Is this job for a test mail?',
     `job_type` varchar(255)    COMMENT 'Type of mailling job: null | child ',
     `parent_id` int unsigned   DEFAULT NULL COMMENT 'Parent job id',
     `job_offset` int   DEFAULT 0 COMMENT 'Offset of the child job',
     `job_limit` int   DEFAULT 0 COMMENT 'Queue size limit for each child job' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_job_mailing_id FOREIGN KEY (`mailing_id`) REFERENCES `civicrm_mailing`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_mailing_job_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_mailing_job`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_recipients
-- *
-- * Stores information about the recipients of a mailing.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_recipients` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `mailing_id` int unsigned NOT NULL   COMMENT 'The ID of the mailing this Job will send.',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact',
     `email_id` int unsigned   DEFAULT NULL COMMENT 'FK to Email',
     `phone_id` int unsigned   DEFAULT NULL COMMENT 'FK to Phone' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_recipients_mailing_id FOREIGN KEY (`mailing_id`) REFERENCES `civicrm_mailing`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_mailing_recipients_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_mailing_recipients_email_id FOREIGN KEY (`email_id`) REFERENCES `civicrm_email`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_recipients_phone_id FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_spool
-- *
-- * Stores the outbond mails
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_spool` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `job_id` int unsigned NOT NULL   COMMENT 'The ID of the Job .',
     `recipient_email` text    COMMENT 'The email of the receipients this mail is to be sent.',
     `headers` text    COMMENT 'The header information of this mailing .',
     `body` text    COMMENT 'The body of this mailing.',
     `added_at` timestamp NULL  DEFAULT NULL COMMENT 'date on which this job was added.',
     `removed_at` timestamp NULL  DEFAULT NULL COMMENT 'date on which this job was removed.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_spool_job_id FOREIGN KEY (`job_id`) REFERENCES `civicrm_mailing_job`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_event_queue
-- *
-- * A collection of all intended recipients of a job
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_queue` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `job_id` int unsigned NOT NULL   COMMENT 'FK to Job',
     `email_id` int unsigned   DEFAULT NULL COMMENT 'FK to Email',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact',
     `hash` varchar(255) NOT NULL   COMMENT 'Security hash',
     `phone_id` int unsigned   DEFAULT NULL COMMENT 'FK to Phone' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_hash`(
        hash
  )
  
,          CONSTRAINT FK_civicrm_mailing_event_queue_job_id FOREIGN KEY (`job_id`) REFERENCES `civicrm_mailing_job`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_mailing_event_queue_email_id FOREIGN KEY (`email_id`) REFERENCES `civicrm_email`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_mailing_event_queue_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_mailing_event_queue_phone_id FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_event_bounce
-- *
-- * Tracks when and why an email bounced.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_bounce` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `event_queue_id` int unsigned NOT NULL   COMMENT 'FK to EventQueue',
     `bounce_type_id` int unsigned    COMMENT 'What type of bounce was it?',
     `bounce_reason` varchar(255)    COMMENT 'The reason the email bounced.',
     `time_stamp` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When this bounce event occurred.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_event_bounce_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_event_delivered
-- *
-- * Tracks when a queued email is actually delivered to the MTA
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_delivered` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `event_queue_id` int unsigned NOT NULL   COMMENT 'FK to EventQueue',
     `time_stamp` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When this delivery event occurred.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_event_delivered_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_event_forward
-- *
-- * Tracks when a contact forwards a mailing to a (new) contact
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_forward` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `event_queue_id` int unsigned NOT NULL   COMMENT 'FK to EventQueue',
     `dest_queue_id` int unsigned    COMMENT 'FK to EventQueue for destination',
     `time_stamp` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When this forward event occurred.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_event_forward_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_mailing_event_forward_dest_queue_id FOREIGN KEY (`dest_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_event_opened
-- *
-- * Tracks when a delivered email is opened by the recipient
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_opened` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `event_queue_id` int unsigned NOT NULL   COMMENT 'FK to EventQueue',
     `time_stamp` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When this open event occurred.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_event_opened_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_event_reply
-- *
-- * Tracks when a contact replies to a mailing
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_reply` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `event_queue_id` int unsigned NOT NULL   COMMENT 'FK to EventQueue',
     `time_stamp` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When this reply event occurred.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_event_reply_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_event_trackable_url_open
-- *
-- * Tracks when a TrackableURL is clicked by a recipient.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_trackable_url_open` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `event_queue_id` int unsigned NOT NULL   COMMENT 'FK to EventQueue',
     `trackable_url_id` int unsigned NOT NULL   COMMENT 'FK to TrackableURL',
     `time_stamp` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When this trackable URL open occurred.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_event_trackable_url_open_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_mailing_event_trackable_url_open_trackable_url_id FOREIGN KEY (`trackable_url_id`) REFERENCES `civicrm_mailing_trackable_url`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_mailing_event_unsubscribe
-- *
-- * Tracks when a recipient unsubscribes from a group/domain
-- *
-- *******************************************************/
CREATE TABLE `civicrm_mailing_event_unsubscribe` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `event_queue_id` int unsigned NOT NULL   COMMENT 'FK to EventQueue',
     `org_unsubscribe` tinyint NOT NULL   COMMENT 'Unsubscribe at org- or group-level',
     `time_stamp` timestamp NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT 'When this delivery event occurred.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_mailing_event_unsubscribe_event_queue_id FOREIGN KEY (`event_queue_id`) REFERENCES `civicrm_mailing_event_queue`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_contribution_recur
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_recur` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution Recur ID',
     `contact_id` int unsigned NOT NULL   COMMENT 'Foreign key to civicrm_contact.id .',
     `amount` decimal(20,2) NOT NULL   COMMENT 'Amount to be contributed or charged each recurrence.',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `frequency_unit` varchar(8)   DEFAULT 'month' COMMENT 'Time units for recurrence of payment.',
     `frequency_interval` int unsigned NOT NULL   COMMENT 'Number of time units for recurrence of payment.',
     `installments` int unsigned    COMMENT 'Total number of payments to be made. Set this to 0 if this is an open-ended commitment i.e. no set end date.',
     `start_date` datetime NOT NULL   COMMENT 'The date the first scheduled recurring contribution occurs.',
     `create_date` datetime NOT NULL   COMMENT 'When this recurring contribution record was created.',
     `modified_date` datetime    COMMENT 'Last updated date for this record. mostly the last time a payment was received',
     `cancel_date` datetime    COMMENT 'Date this recurring contribution was cancelled by contributor- if we can get access to it',
     `end_date` datetime    COMMENT 'Date this recurring contribution finished successfully',
     `processor_id` varchar(255)    COMMENT 'Possibly needed to store a unique identifier for this recurring payment order - if this is available from the processor??',
     `payment_token_id` int unsigned    COMMENT 'Optionally used to store a link to a payment token used for this recurring contribution.',
     `trxn_id` varchar(255)    COMMENT 'unique transaction id. may be processor id, bank id + trans id, or account number + check number... depending on payment_method',
     `invoice_id` varchar(255)    COMMENT 'unique invoice id, system generated or passed in',
     `contribution_status_id` int unsigned   DEFAULT 1 ,
     `is_test` tinyint   DEFAULT 0 ,
     `cycle_day` int unsigned NOT NULL  DEFAULT 1 COMMENT 'Day in the period when the payment should be charged e.g. 1st of month, 15th etc.',
     `next_sched_contribution_date` datetime    COMMENT 'Next scheduled date',
     `failure_count` int unsigned   DEFAULT 0 COMMENT 'Number of failed charge attempts since last success. Business rule could be set to deactivate on more than x failures.',
     `failure_retry_date` datetime    COMMENT 'Date to retry failed attempt',
     `auto_renew` tinyint NOT NULL  DEFAULT 0 COMMENT 'Some systems allow contributor to set a number of installments - but then auto-renew the subscription or commitment if they do not cancel.',
     `payment_processor_id` int unsigned    COMMENT 'Foreign key to civicrm_payment_processor.id',
     `financial_type_id` int unsigned    COMMENT 'FK to Financial Type',
     `payment_instrument_id` int unsigned    COMMENT 'FK to Payment Instrument',
     `campaign_id` int unsigned    COMMENT 'The campaign for which this contribution has been triggered.',
     `is_email_receipt` tinyint   DEFAULT 1 COMMENT 'if true, receipt is automatically emailed to contact on each successful payment' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_contrib_trxn_id`(
        trxn_id
  )
  ,     UNIQUE INDEX `UI_contrib_invoice_id`(
        invoice_id
  )
  ,     INDEX `index_contribution_status`(
        contribution_status_id
  )
  ,     INDEX `UI_contribution_recur_payment_instrument_id`(
        payment_instrument_id
  )
  
,          CONSTRAINT FK_civicrm_contribution_recur_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_contribution_recur_payment_token_id FOREIGN KEY (`payment_token_id`) REFERENCES `civicrm_payment_token`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_recur_payment_processor_id FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_recur_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_recur_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_financial_trxn
-- *
-- *******************************************************/
CREATE TABLE `civicrm_financial_trxn` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `from_financial_account_id` int unsigned    COMMENT 'FK to financial_account table.',
     `to_financial_account_id` int unsigned    COMMENT 'FK to financial_financial_account table.',
     `trxn_date` datetime   DEFAULT NULL COMMENT 'date transaction occurred',
     `total_amount` decimal(20,2) NOT NULL   COMMENT 'amount of transaction',
     `fee_amount` decimal(20,2)    COMMENT 'actual processor fee if known - may be 0.',
     `net_amount` decimal(20,2)    COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `is_payment` tinyint   DEFAULT 0 COMMENT 'Is this entry either a payment or a reversal of a payment?',
     `trxn_id` varchar(255)    COMMENT 'Transaction id supplied by external processor. This may not be unique.',
     `trxn_result_code` varchar(255)    COMMENT 'processor result code',
     `status_id` int unsigned    COMMENT 'pseudo FK to civicrm_option_value of contribution_status_id option_group',
     `payment_processor_id` int unsigned    COMMENT 'Payment Processor for this financial transaction',
     `payment_instrument_id` int unsigned    COMMENT 'FK to payment_instrument option group values',
     `card_type_id` int unsigned    COMMENT 'FK to accept_creditcard option group values',
     `check_number` varchar(255)    COMMENT 'Check number',
     `pan_truncation` varchar(4)    COMMENT 'Last 4 digits of credit card' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_ftrxn_trxn_id`(
        trxn_id
  )
  ,     INDEX `UI_ftrxn_payment_instrument_id`(
        payment_instrument_id
  )
  ,     INDEX `UI_ftrxn_check_number`(
        check_number
  )
  
,          CONSTRAINT FK_civicrm_financial_trxn_from_financial_account_id FOREIGN KEY (`from_financial_account_id`) REFERENCES `civicrm_financial_account`(`id`) ,          CONSTRAINT FK_civicrm_financial_trxn_to_financial_account_id FOREIGN KEY (`to_financial_account_id`) REFERENCES `civicrm_financial_account`(`id`) ,          CONSTRAINT FK_civicrm_financial_trxn_payment_processor_id FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_membership
-- *
-- * Contact Membership records.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Membership Id',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact ID',
     `membership_type_id` int unsigned NOT NULL   COMMENT 'FK to Membership Type',
     `join_date` date    COMMENT 'Beginning of initial membership period (member since...).',
     `start_date` date    COMMENT 'Beginning of current uninterrupted membership period.',
     `end_date` date    COMMENT 'Current membership period expire date.',
     `source` varchar(128)    ,
     `status_id` int unsigned NOT NULL   COMMENT 'FK to Membership Status',
     `is_override` tinyint    COMMENT 'Admin users may set a manual status which overrides the calculated status. When this flag is true, automated status update scripts should NOT modify status for the record.',
     `owner_membership_id` int unsigned    COMMENT 'Optional FK to Parent Membership.',
     `max_related` int    COMMENT 'Maximum number of related memberships (membership_type override).',
     `is_test` tinyint   DEFAULT 0 ,
     `is_pay_later` tinyint   DEFAULT 0 ,
     `contribution_recur_id` int unsigned    COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each membership in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.',
     `campaign_id` int unsigned    COMMENT 'The campaign for which this membership is attached.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_owner_membership_id`(
        owner_membership_id
  )
  
,          CONSTRAINT FK_civicrm_membership_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_membership_membership_type_id FOREIGN KEY (`membership_type_id`) REFERENCES `civicrm_membership_type`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_membership_status_id FOREIGN KEY (`status_id`) REFERENCES `civicrm_membership_status`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_membership_owner_membership_id FOREIGN KEY (`owner_membership_id`) REFERENCES `civicrm_membership`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_membership_contribution_recur_id FOREIGN KEY (`contribution_recur_id`) REFERENCES `civicrm_contribution_recur`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_membership_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_membership_log
-- *
-- * Logs actions which affect a Membership record (signup, status override, renewal, etc.)
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership_log` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `membership_id` int unsigned NOT NULL   COMMENT 'FK to Membership table',
     `status_id` int unsigned NOT NULL   COMMENT 'New status assigned to membership by this action. FK to Membership Status',
     `start_date` date    COMMENT 'New membership period start date',
     `end_date` date    COMMENT 'New membership period expiration date.',
     `modified_id` int unsigned    COMMENT 'FK to Contact ID of person under whose credentials this data modification was made.',
     `modified_date` date    COMMENT 'Date this membership modification action was logged.',
     `membership_type_id` int unsigned    COMMENT 'FK to Membership Type.',
     `max_related` int    COMMENT 'Maximum number of related memberships.' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_membership_log_membership_id FOREIGN KEY (`membership_id`) REFERENCES `civicrm_membership`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_membership_log_status_id FOREIGN KEY (`status_id`) REFERENCES `civicrm_membership_status`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_membership_log_modified_id FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_membership_log_membership_type_id FOREIGN KEY (`membership_type_id`) REFERENCES `civicrm_membership_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_activity
-- *
-- * Other Activity details stored here include contact, location, details.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_activity` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique  Other Activity ID',
     `source_record_id` int unsigned    COMMENT 'Artificial FK to original transaction (e.g. contribution) IF it is not an Activity. Table can be figured out through activity_type_id, and further through component registry.',
     `activity_type_id` int unsigned NOT NULL  DEFAULT 1 COMMENT 'FK to civicrm_option_value.id, that has to be valid, registered activity type.',
     `subject` varchar(255)    COMMENT 'The subject/purpose/short description of the activity.',
     `activity_date_time` datetime    COMMENT 'Date and time this activity is scheduled to occur. Formerly named scheduled_date_time.',
     `duration` int unsigned    COMMENT 'Planned or actual duration of activity expressed in minutes. Conglomerate of former duration_hours and duration_minutes.',
     `location` varchar(255)    COMMENT 'Location of the activity (optional, open text).',
     `phone_id` int unsigned    COMMENT 'Phone ID of the number called (optional - used if an existing phone number is selected).',
     `phone_number` varchar(64)    COMMENT 'Phone number in case the number does not exist in the civicrm_phone table.',
     `details` longtext    COMMENT 'Details about the activity (agenda, notes, etc).',
     `status_id` int unsigned    COMMENT 'ID of the status this activity is currently in. Foreign key to civicrm_option_value.',
     `priority_id` int unsigned    COMMENT 'ID of the priority given to this activity. Foreign key to civicrm_option_value.',
     `parent_id` int unsigned    COMMENT 'Parent meeting ID (if this is a follow-up item). This is not currently implemented',
     `is_test` tinyint   DEFAULT 0 ,
     `medium_id` int unsigned   DEFAULT NULL COMMENT 'Activity Medium, Implicit FK to civicrm_option_value where option_group = encounter_medium.',
     `is_auto` tinyint   DEFAULT 0 ,
     `relationship_id` int unsigned   DEFAULT NULL COMMENT 'FK to Relationship ID',
     `is_current_revision` tinyint   DEFAULT 1 ,
     `original_id` int unsigned    COMMENT 'Activity ID of the first activity record in versioning chain.',
     `result` varchar(255)    COMMENT 'Currently being used to store result id for survey activity, FK to option value.',
     `is_deleted` tinyint   DEFAULT 0 ,
     `campaign_id` int unsigned    COMMENT 'The campaign for which this activity has been triggered.',
     `engagement_level` int unsigned    COMMENT 'Assign a specific level of engagement to this activity. Used for tracking constituents in ladder of engagement.',
     `weight` int    ,
     `is_star` tinyint   DEFAULT 0 COMMENT 'Activity marked as favorite.',
     `created_date` timestamp NULL  DEFAULT NULL COMMENT 'When was the activity was created.',
     `modified_date` timestamp NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the activity (or closely related entity) was created or modified or deleted.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_source_record_id`(
        source_record_id
  )
  ,     INDEX `UI_activity_type_id`(
        activity_type_id
  )
  ,     INDEX `index_activity_date_time`(
        activity_date_time
  )
  ,     INDEX `index_status_id`(
        status_id
  )
  ,     INDEX `index_medium_id`(
        medium_id
  )
  ,     INDEX `index_is_current_revision`(
        is_current_revision
  )
  ,     INDEX `index_is_deleted`(
        is_deleted
  )
  
,          CONSTRAINT FK_civicrm_activity_phone_id FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_activity_parent_id FOREIGN KEY (`parent_id`) REFERENCES `civicrm_activity`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_activity_relationship_id FOREIGN KEY (`relationship_id`) REFERENCES `civicrm_relationship`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_activity_original_id FOREIGN KEY (`original_id`) REFERENCES `civicrm_activity`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_activity_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_activity_contact
-- *
-- * Activity Contact
-- *
-- *******************************************************/
CREATE TABLE `civicrm_activity_contact` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Activity contact id',
     `activity_id` int unsigned NOT NULL   COMMENT 'Foreign key to the activity for this record.',
     `contact_id` int unsigned NOT NULL   COMMENT 'Foreign key to the contact for this record.',
     `record_type_id` int unsigned    COMMENT 'Nature of this contact\'s role in the activity: 1 assignee, 2 creator, 3 focus or target.' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_activity_contact`(
        contact_id
      , activity_id
      , record_type_id
  )
  ,     INDEX `index_record_type`(
        activity_id
      , record_type_id
  )
  
,          CONSTRAINT FK_civicrm_activity_contact_activity_id FOREIGN KEY (`activity_id`) REFERENCES `civicrm_activity`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_activity_contact_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_case_activity
-- *
-- * Joining table for case-activity associations.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_case_activity` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique case-activity association id',
     `case_id` int unsigned NOT NULL   COMMENT 'Case ID of case-activity association.',
     `activity_id` int unsigned NOT NULL   COMMENT 'Activity ID of case-activity association.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_case_activity_id`(
        case_id
      , activity_id
  )
  
,          CONSTRAINT FK_civicrm_case_activity_case_id FOREIGN KEY (`case_id`) REFERENCES `civicrm_case`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_case_activity_activity_id FOREIGN KEY (`activity_id`) REFERENCES `civicrm_activity`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_price_field
-- *
-- *******************************************************/
CREATE TABLE `civicrm_price_field` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Price Field',
     `price_set_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_price_set',
     `name` varchar(255) NOT NULL   COMMENT 'Variable name/programmatic handle for this field.',
     `label` varchar(255) NOT NULL   COMMENT 'Text for form field label (also friendly name for administering this field).',
     `html_type` varchar(12) NOT NULL   ,
     `is_enter_qty` tinyint   DEFAULT 0 COMMENT 'Enter a quantity for this field?',
     `help_pre` text    COMMENT 'Description and/or help text to display before this field.',
     `help_post` text    COMMENT 'Description and/or help text to display after this field.',
     `weight` int   DEFAULT 1 COMMENT 'Order in which the fields should appear',
     `is_display_amounts` tinyint   DEFAULT 1 COMMENT 'Should the price be displayed next to the label for each option?',
     `options_per_line` int unsigned   DEFAULT 1 COMMENT 'number of options per line for checkbox and radio',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this price field active',
     `is_required` tinyint   DEFAULT 1 COMMENT 'Is this price field required (value must be > 1)',
     `active_on` datetime   DEFAULT NULL COMMENT 'If non-zero, do not show this field before the date specified',
     `expire_on` datetime   DEFAULT NULL COMMENT 'If non-zero, do not show this field after the date specified',
     `javascript` varchar(255)    COMMENT 'Optional scripting attributes for field',
     `visibility_id` int unsigned   DEFAULT 1 COMMENT 'Implicit FK to civicrm_option_group with name = \'visibility\'' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_name`(
        name
  )
  
,          CONSTRAINT FK_civicrm_price_field_price_set_id FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_price_field_value
-- *
-- *******************************************************/
CREATE TABLE `civicrm_price_field_value` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Price Field Value',
     `price_field_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_price_field',
     `name` varchar(255)    COMMENT 'Price field option name',
     `label` varchar(255)    COMMENT 'Price field option label',
     `description` text   DEFAULT NULL COMMENT '>Price field option description.',
     `help_pre` text   DEFAULT NULL COMMENT 'Price field option pre help text.',
     `help_post` text   DEFAULT NULL COMMENT 'Price field option post field help.',
     `amount` decimal(18,9) NOT NULL   COMMENT 'Price field option amount',
     `count` int unsigned   DEFAULT NULL COMMENT 'Number of participants per field option',
     `max_value` int unsigned   DEFAULT NULL COMMENT 'Max number of participants per field options',
     `weight` int   DEFAULT 1 COMMENT 'Order in which the field options should appear',
     `membership_type_id` int unsigned   DEFAULT NULL COMMENT 'FK to Membership Type',
     `membership_num_terms` int unsigned   DEFAULT NULL COMMENT 'Number of terms for this membership',
     `is_default` tinyint   DEFAULT 0 COMMENT 'Is this default price field option',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is this price field value active',
     `financial_type_id` int unsigned   DEFAULT NULL COMMENT 'FK to Financial Type.',
     `non_deductible_amount` decimal(20,2) NOT NULL  DEFAULT 0.0 COMMENT 'Portion of total amount which is NOT tax deductible.',
     `visibility_id` int unsigned   DEFAULT 1 COMMENT 'Implicit FK to civicrm_option_group with name = \'visibility\'' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_price_field_value_price_field_id FOREIGN KEY (`price_field_id`) REFERENCES `civicrm_price_field`(`id`) ,          CONSTRAINT FK_civicrm_price_field_value_membership_type_id FOREIGN KEY (`membership_type_id`) REFERENCES `civicrm_membership_type`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_price_field_value_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_pcp_block
-- *
-- * A Personal Campaign Page Block stores admin configurable status options and rules
-- *
-- *******************************************************/
CREATE TABLE `civicrm_pcp_block` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'PCP block Id',
     `entity_table` varchar(64)    ,
     `entity_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_contribution_page.id OR civicrm_event.id',
     `target_entity_type` varchar(255) NOT NULL  DEFAULT 'contribute' COMMENT 'The type of entity that this pcp targets',
     `target_entity_id` int unsigned NOT NULL   COMMENT 'The entity that this pcp targets',
     `supporter_profile_id` int unsigned   DEFAULT NULL COMMENT 'FK to civicrm_uf_group.id. Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
     `owner_notify_id` int unsigned   DEFAULT 0 COMMENT 'FK to civicrm_option_group with name = PCP owner notifications',
     `is_approval_needed` tinyint   DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
     `is_tellfriend_enabled` tinyint   DEFAULT NULL COMMENT 'Does Personal Campaign Page allow using tell a friend?',
     `tellfriend_limit` int unsigned   DEFAULT NULL COMMENT 'Maximum recipient fields allowed in tell a friend',
     `link_text` varchar(255)   DEFAULT NULL COMMENT 'Link text for PCP.',
     `is_active` tinyint   DEFAULT 1 COMMENT 'Is Personal Campaign Page Block enabled/active?',
     `notify_email` varchar(255)   DEFAULT NULL COMMENT 'If set, notification is automatically emailed to this email-address on create/update Personal Campaign Page' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_pcp_block_supporter_profile_id FOREIGN KEY (`supporter_profile_id`) REFERENCES `civicrm_uf_group`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_address
-- *
-- * Stores the physical street / mailing address. This format should be capable of storing ALL international addresses.
-- *
-- *******************************************************/
CREATE TABLE `civicrm_address` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique Address ID',
     `contact_id` int unsigned    COMMENT 'FK to Contact ID',
     `location_type_id` int unsigned    COMMENT 'Which Location does this address belong to.',
     `is_primary` tinyint   DEFAULT 0 COMMENT 'Is this the primary address.',
     `is_billing` tinyint   DEFAULT 0 COMMENT 'Is this the billing address.',
     `street_address` varchar(96)    COMMENT 'Concatenation of all routable street address components (prefix, street number, street name, suffix, unit\n      number OR P.O. Box). Apps should be able to determine physical location with this data (for mapping, mail\n      delivery, etc.).\n    ',
     `street_number` int    COMMENT 'Numeric portion of address number on the street, e.g. For 112A Main St, the street_number = 112.',
     `street_number_suffix` varchar(8)    COMMENT 'Non-numeric portion of address number on the street, e.g. For 112A Main St, the street_number_suffix = A\n    ',
     `street_number_predirectional` varchar(8)    COMMENT 'Directional prefix, e.g. SE Main St, SE is the prefix.',
     `street_name` varchar(64)    COMMENT 'Actual street name, excluding St, Dr, Rd, Ave, e.g. For 112 Main St, the street_name = Main.',
     `street_type` varchar(8)    COMMENT 'St, Rd, Dr, etc.',
     `street_number_postdirectional` varchar(8)    COMMENT 'Directional prefix, e.g. Main St S, S is the suffix.',
     `street_unit` varchar(16)    COMMENT 'Secondary unit designator, e.g. Apt 3 or Unit # 14, or Bldg 1200',
     `supplemental_address_1` varchar(96)    COMMENT 'Supplemental Address Information, Line 1',
     `supplemental_address_2` varchar(96)    COMMENT 'Supplemental Address Information, Line 2',
     `supplemental_address_3` varchar(96)    COMMENT 'Supplemental Address Information, Line 3',
     `city` varchar(64)    COMMENT 'City, Town or Village Name.',
     `county_id` int unsigned    COMMENT 'Which County does this address belong to.',
     `state_province_id` int unsigned    COMMENT 'Which State_Province does this address belong to.',
     `postal_code_suffix` varchar(12)    COMMENT 'Store the suffix, like the +4 part in the USPS system.',
     `postal_code` varchar(64)    COMMENT 'Store both US (zip5) AND international postal codes. App is responsible for country/region appropriate validation.',
     `usps_adc` varchar(32)    COMMENT 'USPS Bulk mailing code.',
     `country_id` int unsigned    COMMENT 'Which Country does this address belong to.',
     `geo_code_1` double    COMMENT 'Latitude',
     `geo_code_2` double    COMMENT 'Longitude',
     `manual_geo_code` tinyint   DEFAULT 0 COMMENT 'Is this a manually entered geo code',
     `timezone` varchar(8)    COMMENT 'Timezone expressed as a UTC offset - e.g. United States CST would be written as \"UTC-6\".',
     `name` varchar(255)    ,
     `master_id` int unsigned    COMMENT 'FK to Address ID' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_location_type`(
        location_type_id
  )
  ,     INDEX `index_is_primary`(
        is_primary
  )
  ,     INDEX `index_is_billing`(
        is_billing
  )
  ,     INDEX `index_street_name`(
        street_name
  )
  ,     INDEX `index_city`(
        city
  )
  
,          CONSTRAINT FK_civicrm_address_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_address_county_id FOREIGN KEY (`county_id`) REFERENCES `civicrm_county`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_address_state_province_id FOREIGN KEY (`state_province_id`) REFERENCES `civicrm_state_province`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_address_country_id FOREIGN KEY (`country_id`) REFERENCES `civicrm_country`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_address_master_id FOREIGN KEY (`master_id`) REFERENCES `civicrm_address`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_loc_block
-- *
-- * Define location specific properties
-- *
-- *******************************************************/
CREATE TABLE `civicrm_loc_block` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Unique ID',
     `address_id` int unsigned    ,
     `email_id` int unsigned    ,
     `phone_id` int unsigned    ,
     `im_id` int unsigned    ,
     `address_2_id` int unsigned    ,
     `email_2_id` int unsigned    ,
     `phone_2_id` int unsigned    ,
     `im_2_id` int unsigned     
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_loc_block_address_id FOREIGN KEY (`address_id`) REFERENCES `civicrm_address`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_loc_block_email_id FOREIGN KEY (`email_id`) REFERENCES `civicrm_email`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_loc_block_phone_id FOREIGN KEY (`phone_id`) REFERENCES `civicrm_phone`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_loc_block_im_id FOREIGN KEY (`im_id`) REFERENCES `civicrm_im`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_loc_block_address_2_id FOREIGN KEY (`address_2_id`) REFERENCES `civicrm_address`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_loc_block_email_2_id FOREIGN KEY (`email_2_id`) REFERENCES `civicrm_email`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_loc_block_phone_2_id FOREIGN KEY (`phone_2_id`) REFERENCES `civicrm_phone`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_loc_block_im_2_id FOREIGN KEY (`im_2_id`) REFERENCES `civicrm_im`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_group_contact
-- *
-- * Join table sets membership for 'static' groups. Also used to store 'opt-out' entries for 'query' type groups (status = 'OUT') 
-- *
-- *******************************************************/
CREATE TABLE `civicrm_group_contact` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'primary key',
     `group_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_group',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to civicrm_contact',
     `status` varchar(8)    COMMENT 'status of contact relative to membership in group',
     `location_id` int unsigned    COMMENT 'Optional location to associate with this membership',
     `email_id` int unsigned    COMMENT 'Optional email to associate with this membership' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_contact_group`(
        contact_id
      , group_id
  )
  
,          CONSTRAINT FK_civicrm_group_contact_group_id FOREIGN KEY (`group_id`) REFERENCES `civicrm_group`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_group_contact_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_group_contact_location_id FOREIGN KEY (`location_id`) REFERENCES `civicrm_loc_block`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_group_contact_email_id FOREIGN KEY (`email_id`) REFERENCES `civicrm_email`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_contribution
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Contribution ID',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact ID',
     `financial_type_id` int unsigned    COMMENT 'FK to Financial Type for (total_amount - non_deductible_amount).',
     `contribution_page_id` int unsigned    COMMENT 'The Contribution Page which triggered this contribution',
     `payment_instrument_id` int unsigned    COMMENT 'FK to Payment Instrument',
     `receive_date` datetime    COMMENT 'Date contribution was received - not necessarily the creation date of the record',
     `non_deductible_amount` decimal(20,2)   DEFAULT 0 COMMENT 'Portion of total amount which is NOT tax deductible. Equal to total_amount for non-deductible financial types.',
     `total_amount` decimal(20,2) NOT NULL   COMMENT 'Total amount of this contribution. Use market value for non-monetary gifts.',
     `fee_amount` decimal(20,2)    COMMENT 'actual processor fee if known - may be 0.',
     `net_amount` decimal(20,2)    COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.',
     `trxn_id` varchar(255)    COMMENT 'unique transaction id. may be processor id, bank id + trans id, or account number + check number... depending on payment_method',
     `invoice_id` varchar(255)    COMMENT 'unique invoice id, system generated or passed in',
     `invoice_number` varchar(255)    COMMENT 'Human readable invoice number',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `cancel_date` datetime    COMMENT 'when was gift cancelled',
     `cancel_reason` text    ,
     `receipt_date` datetime    COMMENT 'when (if) receipt was sent. populated automatically for online donations w/ automatic receipting',
     `thankyou_date` datetime    COMMENT 'when (if) was donor thanked',
     `source` varchar(255)    COMMENT 'Origin of this Contribution.',
     `amount_level` text    ,
     `contribution_recur_id` int unsigned    COMMENT 'Conditional foreign key to civicrm_contribution_recur id. Each contribution made in connection with a recurring contribution carries a foreign key to the recurring contribution record. This assumes we can track these processor initiated events.',
     `is_test` tinyint   DEFAULT 0 ,
     `is_pay_later` tinyint   DEFAULT 0 ,
     `contribution_status_id` int unsigned   DEFAULT 1 ,
     `address_id` int unsigned    COMMENT 'Conditional foreign key to civicrm_address.id. We insert an address record for each contribution when we have associated billing name and address data.',
     `check_number` varchar(255)    ,
     `campaign_id` int unsigned    COMMENT 'The campaign for which this contribution has been triggered.',
     `creditnote_id` varchar(255)    COMMENT 'unique credit note id, system generated or passed in',
     `tax_amount` decimal(20,2)    COMMENT 'Total tax amount of this contribution.',
     `revenue_recognition_date` datetime    COMMENT 'Stores the date when revenue should be recognized.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_contrib_payment_instrument_id`(
        payment_instrument_id
  )
  ,     INDEX `index_total_amount_receive_date`(
        total_amount
      , receive_date
  )
  ,     INDEX `index_source`(
        source
  )
  ,     UNIQUE INDEX `UI_contrib_trxn_id`(
        trxn_id
  )
  ,     UNIQUE INDEX `UI_contrib_invoice_id`(
        invoice_id
  )
  ,     INDEX `index_contribution_status`(
        contribution_status_id
  )
  ,     INDEX `received_date`(
        receive_date
  )
  ,     INDEX `check_number`(
        check_number
  )
  ,     INDEX `index_creditnote_id`(
        creditnote_id
  )
  
,          CONSTRAINT FK_civicrm_contribution_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_contribution_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ,          CONSTRAINT FK_civicrm_contribution_contribution_page_id FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_contribution_recur_id FOREIGN KEY (`contribution_recur_id`) REFERENCES `civicrm_contribution_recur`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_address_id FOREIGN KEY (`address_id`) REFERENCES `civicrm_address`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_contribution_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_contribution_product
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_product` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `product_id` int unsigned NOT NULL   ,
     `contribution_id` int unsigned NOT NULL   ,
     `product_option` varchar(255)    COMMENT 'Option value selected if applicable - e.g. color, size etc.',
     `quantity` int    ,
     `fulfilled_date` date    COMMENT 'Optional. Can be used to record the date this product was fulfilled or shipped.',
     `start_date` date    COMMENT 'Actual start date for a time-delimited premium (subscription, service or membership)',
     `end_date` date    COMMENT 'Actual end date for a time-delimited premium (subscription, service or membership)',
     `comment` text    ,
     `financial_type_id` int unsigned   DEFAULT NULL COMMENT 'FK to Financial Type(for membership price sets only).' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_contribution_product_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_contribution_product_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_contribution_soft
-- *
-- *******************************************************/
CREATE TABLE `civicrm_contribution_soft` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Soft Contribution ID',
     `contribution_id` int unsigned NOT NULL   COMMENT 'FK to contribution table.',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact ID',
     `amount` decimal(20,2) NOT NULL   COMMENT 'Amount of this soft contribution.',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `pcp_id` int unsigned   DEFAULT NULL COMMENT 'FK to civicrm_pcp.id',
     `pcp_display_in_roll` tinyint   DEFAULT 0 ,
     `pcp_roll_nickname` varchar(255)   DEFAULT NULL ,
     `pcp_personal_note` varchar(255)   DEFAULT NULL ,
     `soft_credit_type_id` int unsigned   DEFAULT NULL COMMENT 'Soft Credit Type ID.Implicit FK to civicrm_option_value where option_group = soft_credit_type.' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_id`(
        pcp_id
  )
  
,          CONSTRAINT FK_civicrm_contribution_soft_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_contribution_soft_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_contribution_soft_pcp_id FOREIGN KEY (`pcp_id`) REFERENCES `civicrm_pcp`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_entity_financial_trxn
-- *
-- *******************************************************/
CREATE TABLE `civicrm_entity_financial_trxn` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'ID',
     `entity_table` varchar(64) NOT NULL   COMMENT 'May contain civicrm_financial_item, civicrm_contribution, civicrm_financial_trxn, civicrm_grant, etc',
     `entity_id` int unsigned NOT NULL   ,
     `financial_trxn_id` int unsigned    ,
     `amount` decimal(20,2) NOT NULL   COMMENT 'allocated amount of transaction to this entity' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `UI_entity_financial_trxn_entity_table`(
        entity_table
  )
  ,     INDEX `UI_entity_financial_trxn_entity_id`(
        entity_id
  )
  
,          CONSTRAINT FK_civicrm_entity_financial_trxn_financial_trxn_id FOREIGN KEY (`financial_trxn_id`) REFERENCES `civicrm_financial_trxn`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_membership_payment
-- *
-- * Membership Payment
-- *
-- *******************************************************/
CREATE TABLE `civicrm_membership_payment` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `membership_id` int unsigned NOT NULL   COMMENT 'FK to Membership table',
     `contribution_id` int unsigned    COMMENT 'FK to contribution table.' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_contribution_membership`(
        contribution_id
      , membership_id
  )
  
,          CONSTRAINT FK_civicrm_membership_payment_membership_id FOREIGN KEY (`membership_id`) REFERENCES `civicrm_membership`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_membership_payment_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_event
-- *
-- *******************************************************/
CREATE TABLE `civicrm_event` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Event',
     `title` varchar(255)    COMMENT 'Event Title (e.g. Fall Fundraiser Dinner)',
     `summary` text    COMMENT 'Brief summary of event. Text and html allowed. Displayed on Event Registration form and can be used on other CMS pages which need an event summary.',
     `description` text    COMMENT 'Full description of event. Text and html allowed. Displayed on built-in Event Information screens.',
     `event_type_id` int unsigned   DEFAULT 0 COMMENT 'Event Type ID.Implicit FK to civicrm_option_value where option_group = event_type.',
     `participant_listing_id` int unsigned   DEFAULT 0 COMMENT 'Should we expose the participant list? Implicit FK to civicrm_option_value where option_group = participant_listing.',
     `is_public` tinyint   DEFAULT 1 COMMENT 'Public events will be included in the iCal feeds. Access to private event information may be limited using ACLs.',
     `start_date` datetime    COMMENT 'Date and time that event starts.',
     `end_date` datetime    COMMENT 'Date and time that event ends. May be NULL if no defined end date/time',
     `is_online_registration` tinyint   DEFAULT 0 COMMENT 'If true, include registration link on Event Info page.',
     `registration_link_text` varchar(255)    COMMENT 'Text for link to Event Registration form which is displayed on Event Information screen when is_online_registration is true.',
     `registration_start_date` datetime    COMMENT 'Date and time that online registration starts.',
     `registration_end_date` datetime    COMMENT 'Date and time that online registration ends.',
     `max_participants` int unsigned   DEFAULT NULL COMMENT 'Maximum number of registered participants to allow. After max is reached, a custom Event Full message is displayed. If NULL, allow unlimited number of participants.',
     `event_full_text` text    COMMENT 'Message to display on Event Information page and INSTEAD OF Event Registration form if maximum participants are signed up. Can include email address/info about getting on a waiting list, etc. Text and html allowed.',
     `is_monetary` tinyint   DEFAULT 0 COMMENT 'If true, one or more fee amounts must be set and a Payment Processor must be configured for Online Event Registration.',
     `financial_type_id` int unsigned   DEFAULT NULL COMMENT 'Financial type assigned to paid event registrations for this event. Required if is_monetary is true.',
     `payment_processor` varchar(128)    COMMENT 'Payment Processors configured for this Event (if is_monetary is true)',
     `is_map` tinyint   DEFAULT 0 COMMENT 'Include a map block on the Event Information page when geocode info is available and a mapping provider has been specified?',
     `is_active` tinyint   DEFAULT 0 COMMENT 'Is this Event enabled or disabled/cancelled?',
     `fee_label` varchar(255)    ,
     `is_show_location` tinyint   DEFAULT 1 COMMENT 'If true, show event location.',
     `loc_block_id` int unsigned    COMMENT 'FK to Location Block ID',
     `default_role_id` int unsigned   DEFAULT 1 COMMENT 'Participant role ID. Implicit FK to civicrm_option_value where option_group = participant_role.',
     `intro_text` text    COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.',
     `footer_text` text    COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.',
     `confirm_title` varchar(255)   DEFAULT NULL COMMENT 'Title for Confirmation page.',
     `confirm_text` text    COMMENT 'Introductory message for Event Registration page. Text and html allowed. Displayed at the top of Event Registration form.',
     `confirm_footer_text` text    COMMENT 'Footer message for Event Registration page. Text and html allowed. Displayed at the bottom of Event Registration form.',
     `is_email_confirm` tinyint   DEFAULT 0 COMMENT 'If true, confirmation is automatically emailed to contact on successful registration.',
     `confirm_email_text` text    COMMENT 'text to include above standard event info on confirmation email. emails are text-only, so do not allow html for now',
     `confirm_from_name` varchar(255)    COMMENT 'FROM email name used for confirmation emails.',
     `confirm_from_email` varchar(255)    COMMENT 'FROM email address used for confirmation emails.',
     `cc_confirm` varchar(255)    COMMENT 'comma-separated list of email addresses to cc each time a confirmation is sent',
     `bcc_confirm` varchar(255)    COMMENT 'comma-separated list of email addresses to bcc each time a confirmation is sent',
     `default_fee_id` int unsigned    COMMENT 'FK to civicrm_option_value.',
     `default_discount_fee_id` int unsigned    COMMENT 'FK to civicrm_option_value.',
     `thankyou_title` varchar(255)   DEFAULT NULL COMMENT 'Title for ThankYou page.',
     `thankyou_text` text    COMMENT 'ThankYou Text.',
     `thankyou_footer_text` text    COMMENT 'Footer message.',
     `is_pay_later` tinyint   DEFAULT 0 COMMENT 'if true - allows the user to send payment directly to the org later',
     `pay_later_text` text    COMMENT 'The text displayed to the user in the main form',
     `pay_later_receipt` text    COMMENT 'The receipt sent to the user instead of the normal receipt text',
     `is_partial_payment` tinyint   DEFAULT 0 COMMENT 'is partial payment enabled for this event',
     `initial_amount_label` varchar(255)    COMMENT 'Initial amount label for partial payment',
     `initial_amount_help_text` text    COMMENT 'Initial amount help text for partial payment',
     `min_initial_amount` decimal(20,2)    COMMENT 'Minimum initial amount for partial payment',
     `is_multiple_registrations` tinyint   DEFAULT 0 COMMENT 'if true - allows the user to register multiple participants for event',
     `max_additional_participants` int unsigned   DEFAULT 0 COMMENT 'Maximum number of additional participants that can be registered on a single booking',
     `allow_same_participant_emails` tinyint   DEFAULT 0 COMMENT 'if true - allows the user to register multiple registrations from same email address.',
     `has_waitlist` tinyint    COMMENT 'Whether the event has waitlist support.',
     `requires_approval` tinyint    COMMENT 'Whether participants require approval before they can finish registering.',
     `expiration_time` int unsigned    COMMENT 'Expire pending but unconfirmed registrations after this many hours.',
     `allow_selfcancelxfer` tinyint   DEFAULT 0 COMMENT 'Allow self service cancellation or transfer for event?',
     `selfcancelxfer_time` int unsigned   DEFAULT 0 COMMENT 'Number of hours prior to event start date to allow self-service cancellation or transfer.',
     `waitlist_text` text    COMMENT 'Text to display when the event is full, but participants can signup for a waitlist.',
     `approval_req_text` text    COMMENT 'Text to display when the approval is required to complete registration for an event.',
     `is_template` tinyint NOT NULL  DEFAULT 0 COMMENT 'whether the event has template',
     `template_title` varchar(255)    COMMENT 'Event Template Title',
     `created_id` int unsigned    COMMENT 'FK to civicrm_contact, who created this event',
     `created_date` datetime    COMMENT 'Date and time that event was created.',
     `currency` varchar(3)    COMMENT '3 character string, value from config setting or input via user.',
     `campaign_id` int unsigned    COMMENT 'The campaign for which this event has been created.',
     `is_share` tinyint   DEFAULT 1 COMMENT 'Can people share the event through social media?',
     `is_confirm_enabled` tinyint   DEFAULT 1 COMMENT 'If false, the event booking confirmation screen gets skipped',
     `parent_event_id` int unsigned   DEFAULT NULL COMMENT 'Implicit FK to civicrm_event: parent event',
     `slot_label_id` int unsigned   DEFAULT NULL COMMENT 'Subevent slot label. Implicit FK to civicrm_option_value where option_group = conference_slot.',
     `dedupe_rule_group_id` int unsigned   DEFAULT NULL COMMENT 'Rule to use when matching registrations for this event',
     `is_billing_required` tinyint   DEFAULT 0 COMMENT 'if true than billing block is required this event' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_event_type_id`(
        event_type_id
  )
  ,     INDEX `index_participant_listing_id`(
        participant_listing_id
  )
  ,     INDEX `index_parent_event_id`(
        parent_event_id
  )
  
,          CONSTRAINT FK_civicrm_event_loc_block_id FOREIGN KEY (`loc_block_id`) REFERENCES `civicrm_loc_block`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_event_created_id FOREIGN KEY (`created_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_event_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_event_dedupe_rule_group_id FOREIGN KEY (`dedupe_rule_group_id`) REFERENCES `civicrm_dedupe_rule_group`(`id`)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_participant
-- *
-- *******************************************************/
CREATE TABLE `civicrm_participant` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Participant Id',
     `contact_id` int unsigned NOT NULL   COMMENT 'FK to Contact ID',
     `event_id` int unsigned NOT NULL   COMMENT 'FK to Event ID',
     `status_id` int unsigned NOT NULL  DEFAULT 1 COMMENT 'Participant status ID. FK to civicrm_participant_status_type. Default of 1 should map to status = Registered.',
     `role_id` varchar(128)   DEFAULT NULL COMMENT 'Participant role ID. Implicit FK to civicrm_option_value where option_group = participant_role.',
     `register_date` datetime    COMMENT 'When did contact register for event?',
     `source` varchar(128)    COMMENT 'Source of this event registration.',
     `fee_level` text    COMMENT 'Populate with the label (text) associated with a fee level for paid events with multiple levels. Note that\n      we store the label value and not the key\n    ',
     `is_test` tinyint   DEFAULT 0 ,
     `is_pay_later` tinyint   DEFAULT 0 ,
     `fee_amount` decimal(20,2)    COMMENT 'actual processor fee if known - may be 0.',
     `registered_by_id` int unsigned   DEFAULT NULL COMMENT 'FK to Participant ID',
     `discount_id` int unsigned   DEFAULT NULL COMMENT 'FK to Discount ID',
     `fee_currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value derived from config setting.',
     `campaign_id` int unsigned    COMMENT 'The campaign for which this participant has been registered.',
     `discount_amount` int unsigned    COMMENT 'Discount Amount',
     `cart_id` int unsigned    COMMENT 'FK to civicrm_event_carts',
     `must_wait` int    COMMENT 'On Waiting List',
     `transferred_to_contact_id` int unsigned   DEFAULT NULL COMMENT 'FK to Contact ID' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_status_id`(
        status_id
  )
  ,     INDEX `index_role_id`(
        role_id
  )
  
,          CONSTRAINT FK_civicrm_participant_contact_id FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_participant_event_id FOREIGN KEY (`event_id`) REFERENCES `civicrm_event`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_participant_status_id FOREIGN KEY (`status_id`) REFERENCES `civicrm_participant_status_type`(`id`) ,          CONSTRAINT FK_civicrm_participant_registered_by_id FOREIGN KEY (`registered_by_id`) REFERENCES `civicrm_participant`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_participant_discount_id FOREIGN KEY (`discount_id`) REFERENCES `civicrm_discount`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_participant_campaign_id FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_participant_cart_id FOREIGN KEY (`cart_id`) REFERENCES `civicrm_event_carts`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_participant_transferred_to_contact_id FOREIGN KEY (`transferred_to_contact_id`) REFERENCES `civicrm_contact`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_participant_payment
-- *
-- *******************************************************/
CREATE TABLE `civicrm_participant_payment` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Participant Payment Id',
     `participant_id` int unsigned NOT NULL   COMMENT 'Participant Id (FK)',
     `contribution_id` int unsigned NOT NULL   COMMENT 'FK to contribution table.' 
,
        PRIMARY KEY (`id`)
 
    ,     UNIQUE INDEX `UI_contribution_participant`(
        contribution_id
      , participant_id
  )
  
,          CONSTRAINT FK_civicrm_participant_payment_participant_id FOREIGN KEY (`participant_id`) REFERENCES `civicrm_participant`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_participant_payment_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_events_in_carts
-- *
-- *******************************************************/
CREATE TABLE `civicrm_events_in_carts` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Event In Cart Id',
     `event_id` int unsigned    COMMENT 'FK to Event ID',
     `event_cart_id` int unsigned    COMMENT 'FK to Event Cart ID' 
,
        PRIMARY KEY (`id`)
 
 
,          CONSTRAINT FK_civicrm_events_in_carts_event_id FOREIGN KEY (`event_id`) REFERENCES `civicrm_event`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_events_in_carts_event_cart_id FOREIGN KEY (`event_cart_id`) REFERENCES `civicrm_event_carts`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_pledge_payment
-- *
-- * Pledge Payment
-- *
-- *******************************************************/
CREATE TABLE `civicrm_pledge_payment` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  ,
     `pledge_id` int unsigned NOT NULL   COMMENT 'FK to Pledge table',
     `contribution_id` int unsigned    COMMENT 'FK to contribution table.',
     `scheduled_amount` decimal(20,2) NOT NULL   COMMENT 'Pledged amount for this payment (the actual contribution amount might be different).',
     `actual_amount` decimal(20,2)    COMMENT 'Actual amount that is paid as the Pledged installment amount.',
     `currency` varchar(3)   DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
     `scheduled_date` datetime NOT NULL   COMMENT 'The date the pledge payment is supposed to happen.',
     `reminder_date` datetime    COMMENT 'The date that the most recent payment reminder was sent.',
     `reminder_count` int unsigned   DEFAULT 0 COMMENT 'The number of payment reminders sent.',
     `status_id` int unsigned     
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_contribution_pledge`(
        contribution_id
      , pledge_id
  )
  ,     INDEX `index_status`(
        status_id
  )
  
,          CONSTRAINT FK_civicrm_pledge_payment_pledge_id FOREIGN KEY (`pledge_id`) REFERENCES `civicrm_pledge`(`id`) ON DELETE CASCADE,          CONSTRAINT FK_civicrm_pledge_payment_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE CASCADE  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;

-- /*******************************************************
-- *
-- * civicrm_line_item
-- *
-- *******************************************************/
CREATE TABLE `civicrm_line_item` (


     `id` int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'Line Item',
     `entity_table` varchar(64) NOT NULL   COMMENT 'table which has the transaction',
     `entity_id` int unsigned NOT NULL   COMMENT 'entry in table',
     `contribution_id` int unsigned    COMMENT 'FK to civicrm_contribution',
     `price_field_id` int unsigned    COMMENT 'FK to civicrm_price_field',
     `label` varchar(255)   DEFAULT NULL COMMENT 'descriptive label for item - from price_field_value.label',
     `qty` decimal(20,2) NOT NULL   COMMENT 'How many items ordered',
     `unit_price` decimal(20,2) NOT NULL   COMMENT 'price of each item',
     `line_total` decimal(20,2) NOT NULL   COMMENT 'qty * unit_price',
     `participant_count` int unsigned   DEFAULT NULL COMMENT 'Participant count for field',
     `price_field_value_id` int unsigned   DEFAULT NULL COMMENT 'FK to civicrm_price_field_value',
     `financial_type_id` int unsigned   DEFAULT NULL COMMENT 'FK to Financial Type.',
     `non_deductible_amount` decimal(20,2) NOT NULL  DEFAULT 0.0 COMMENT 'Portion of total amount which is NOT tax deductible.',
     `tax_amount` decimal(20,2)    COMMENT 'tax of each item' 
,
        PRIMARY KEY (`id`)
 
    ,     INDEX `index_entity`(
        entity_table
      , entity_id
  )
  ,     UNIQUE INDEX `UI_line_item_value`(
        entity_table
      , entity_id
      , contribution_id
      , price_field_value_id
      , price_field_id
  )
  
,          CONSTRAINT FK_civicrm_line_item_contribution_id FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_line_item_price_field_id FOREIGN KEY (`price_field_id`) REFERENCES `civicrm_price_field`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_line_item_price_field_value_id FOREIGN KEY (`price_field_value_id`) REFERENCES `civicrm_price_field_value`(`id`) ON DELETE SET NULL,          CONSTRAINT FK_civicrm_line_item_financial_type_id FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type`(`id`) ON DELETE SET NULL  
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci  ;