CREATE TABLE `contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Contact ID',
  `contact_type` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Type of Contact.',
  `contact_sub_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'May be used to over-ride contact view and edit templates.',
  `do_not_email` tinyint(4) DEFAULT 0,
  `do_not_phone` tinyint(4) DEFAULT 0,
  `do_not_mail` tinyint(4) DEFAULT 0,
  `do_not_sms` tinyint(4) DEFAULT 0,
  `do_not_trade` tinyint(4) DEFAULT 0,
  `is_opt_out` tinyint(4) 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) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'May be used for SSN, EIN/TIN, Household ID (census) or other applicable unique legal/government ID.',
  `external_identifier` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sort_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Name used for sorting different contact types',
  `display_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Formatted name representing preferred format for display/print/other output.',
  `nick_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Nickname.',
  `legal_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Legal Name.',
  `image_URL` text COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'optional URL for preferred image (photo, logo, etc.) to display for this contact.',
  `preferred_communication_method` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'What is the preferred mode of communication.',
  `preferred_language` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Which language is preferred for communication. FK to languages in option_value.',
  `preferred_mail_format` varchar(8) COLLATE utf8_unicode_ci DEFAULT 'Both' COMMENT 'What is the preferred mode of sending an email.',
  `hash` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Key for validating requests related to this contact.',
  `api_key` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'API Key for validating requests related to this contact.',
  `source` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'where contact come from, e.g. import, donate module insert...',
  `first_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'First Name.',
  `middle_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Middle Name.',
  `last_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Last Name.',
  `prefix_id` int(10) unsigned DEFAULT NULL COMMENT 'Prefix or Title for name (Ms, Mr...). FK to prefix ID',
  `suffix_id` int(10) unsigned DEFAULT NULL COMMENT 'Suffix for name (Jr, Sr...). FK to suffix ID',
  `formal_title` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Formal (academic or similar) title in front of name. (Prof., Dr. etc.)',
  `communication_style_id` int(10) unsigned DEFAULT NULL COMMENT 'Communication style (e.g. formal vs. familiar) to use with this contact. FK to communication styles in option_value.',
  `email_greeting_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to option_value.id, that has to be valid registered Email Greeting.',
  `email_greeting_custom` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Custom Email Greeting.',
  `email_greeting_display` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Cache Email Greeting.',
  `postal_greeting_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to option_value.id, that has to be valid registered Postal Greeting.',
  `postal_greeting_custom` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Custom Postal greeting.',
  `postal_greeting_display` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Cache Postal greeting.',
  `addressee_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to option_value.id, that has to be valid registered Addressee.',
  `addressee_custom` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Custom Addressee.',
  `addressee_display` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Cache Addressee.',
  `job_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Job Title',
  `gender_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to gender ID',
  `birth_date` date DEFAULT NULL COMMENT 'Date of birth',
  `is_deceased` tinyint(4) NOT NULL DEFAULT 0,
  `deceased_date` date DEFAULT NULL COMMENT 'Date of deceased',
  `household_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Household Name.',
  `primary_contact_id` int(10) unsigned DEFAULT NULL COMMENT 'Optional FK to Primary Contact for this household.',
  `organization_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Organization Name.',
  `sic_code` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Standard Industry Classification Code.',
  `user_unique_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'the OpenID (or OpenID-style http://username.domain/) unique identifier for this contact mainly used for logging in to CiviCRM',
  `employer_id` int(10) unsigned DEFAULT NULL COMMENT 'OPTIONAL FK to contact record.',
  `is_deleted` tinyint(4) 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.',
  `hasGDPRConsent` tinyint(4) NOT NULL DEFAULT 0,
  `GDPRConsentDate` date DEFAULT '0000-00-00',
  `GDPRConsentSource` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UI_external_identifier` (`external_identifier`),
  KEY `index_contact_type` (`contact_type`),
  KEY `index_contact_sub_type` (`contact_sub_type`),
  KEY `index_sort_name` (`sort_name`),
  KEY `index_preferred_communication_method` (`preferred_communication_method`),
  KEY `index_hash` (`hash`),
  KEY `index_api_key` (`api_key`),
  KEY `index_first_name` (`first_name`),
  KEY `index_last_name` (`last_name`),
  KEY `UI_prefix` (`prefix_id`),
  KEY `UI_suffix` (`suffix_id`),
  KEY `UI_gender` (`gender_id`),
  KEY `index_household_name` (`household_name`),
  KEY `index_organization_name` (`organization_name`),
  KEY `index_is_deleted_sort_name` (`is_deleted`,`sort_name`,`id`),
  KEY `FK_contact_primary_contact_id` (`primary_contact_id`),
  KEY `FK_contact_employer_id` (`employer_id`),
  KEY `dedupe_index_birth_date` (`birth_date`),
  KEY `index_communication_style_id` (`communication_style_id`),
  KEY `dedupe_index_id` (`id`),
  KEY `hasGDPRConsent` (`hasGDPRConsent`),
  KEY `index_image_URL_128` (`image_URL`(128)),
  CONSTRAINT `FK_contact_employer_id` FOREIGN KEY (`employer_id`) REFERENCES `contact` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_contact_primary_contact_id` FOREIGN KEY (`primary_contact_id`) REFERENCES `contact` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=814224 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

CREATE TABLE `mailing_event_queue` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int(10) unsigned NOT NULL COMMENT 'FK to Job',
  `email_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Email',
  `contact_id` int(10) unsigned NOT NULL COMMENT 'FK to Contact',
  `hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Security hash',
  `phone_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to Phone',
  PRIMARY KEY (`id`),
  KEY `FK_mailing_event_queue_job_id` (`job_id`),
  KEY `FK_mailing_event_queue_email_id` (`email_id`),
  KEY `FK_mailing_event_queue_contact_id` (`contact_id`),
  KEY `FK_mailing_event_queue_phone_id` (`phone_id`),
  CONSTRAINT `FK_mailing_event_queue_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_mailing_event_queue_email_id` FOREIGN KEY (`email_id`) REFERENCES `email` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION,
  CONSTRAINT `FK_mailing_event_queue_job_id` FOREIGN KEY (`job_id`) REFERENCES `mailing_job` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_mailing_event_queue_phone_id` FOREIGN KEY (`phone_id`) REFERENCES `phone` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=10671871 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `group_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `group_id` int(10) unsigned NOT NULL COMMENT 'FK to group',
  `contact_id` int(10) unsigned NOT NULL COMMENT 'FK to contact',
  `status` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'status of contact relative to membership in group',
  `location_id` int(10) unsigned DEFAULT NULL COMMENT 'Optional location to associate with this membership',
  `email_id` int(10) unsigned DEFAULT NULL COMMENT 'Optional email to associate with this membership',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UI_contact_group` (`contact_id`,`group_id`),
  KEY `FK_group_contact_group_id` (`group_id`),
  KEY `FK_group_contact_location_id` (`location_id`),
  KEY `FK_group_contact_email_id` (`email_id`),
  CONSTRAINT `FK_group_contact_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_group_contact_email_id` FOREIGN KEY (`email_id`) REFERENCES `email` (`id`),
  CONSTRAINT `FK_group_contact_group_id` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_group_contact_location_id` FOREIGN KEY (`location_id`) REFERENCES `loc_block` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3896487 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
