legacy-core-tables-common-part.sql

CREATE TABLE IF NOT EXISTS `STUDIO_MANAGERS` (
  `uuid` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `USERNAME` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `PASSWORD` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `EMAIL` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `PHONE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `FIRST_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `LAST_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `STATUS` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `DATE_CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DATE_UPDATED` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  UNIQUE KEY `USERNAME` (`USERNAME`),
  UNIQUE KEY `EMAIL` (`EMAIL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `partner` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `partner_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `secret_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `manager_uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DATE_CREATED` timestamp NULL DEFAULT NULL,
  `DATE_UPDATED` timestamp NULL DEFAULT NULL,
  `active` bit(1) DEFAULT b'1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `partneruuid` (`uuid`),
  KEY `fk_4_mgr` (`manager_uuid`),
  CONSTRAINT `fk_4_mgr` FOREIGN KEY (`manager_uuid`) REFERENCES `STUDIO_MANAGERS` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `gpnxuser` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `partner_id` bigint(20) NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique-email` (`partner_id`,`email`),
  UNIQUE KEY `gpnxuser_Uuid` (`uuid`),
  KEY `idx_013_partner_id_uuid` (`partner_id`,`uuid`),
  KEY `idx_014_uuid` (`uuid`),
  CONSTRAINT `FKB2D9FEBE725C505E` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`),
  CONSTRAINT `fk_046_partner` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `key_value` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `upkey` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `security_level` int(11) NOT NULL,
  `_content` longtext COLLATE utf8_unicode_ci NOT NULL,
  `class` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `date_value` datetime DEFAULT NULL,
  `integer_value` bigint(20) DEFAULT NULL,
  `double_value` double DEFAULT NULL,
  `string_value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_004_uuid` (`uuid`),
  KEY `idx_005_string_value` (`upkey`,`string_value`),
  KEY `idx_006_integer_value` (`upkey`,`integer_value`),
  KEY `idx_007_double_value` (`upkey`,`double_value`),
  KEY `idx_008_date_value` (`upkey`,`date_value`),
  KEY `string_value_idx` (`string_value`),
  KEY `integer_value_idx` (`integer_value`),
  KEY `double_value_idx` (`double_value`),
  KEY `date_value_idx` (`date_value`),
  KEY `idx_key_value_user_upkey_string` (`user_id`,`upkey`,`string_value`),
  CONSTRAINT `FK_GPNXUSER` FOREIGN KEY (`user_id`) REFERENCES `gpnxuser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `STUDIO_CAMPAIGNS` (
  `uuid` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `database_uuid` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `active` bit(1) NOT NULL,
  `DATE_CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DATE_UPDATED` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY `fk_049_database` (`database_uuid`),
  CONSTRAINT `fk_049_database` FOREIGN KEY (`database_uuid`) REFERENCES `partner` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `referral` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `campaign_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `referrer_id` bigint(20) NOT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `campaign_uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique-campaign_id` (`campaign_id`,`user_id`),
  KEY `FKD4EE7B9D4A9694D5` (`referrer_id`),
  KEY `FKD4EE7B9D2D11F5A9` (`user_id`),
  KEY `fk_051_campaign` (`campaign_uuid`),
  CONSTRAINT `FKD4EE7B9D2D11F5A9` FOREIGN KEY (`user_id`) REFERENCES `gpnxuser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FKD4EE7B9D4A9694D5` FOREIGN KEY (`referrer_id`) REFERENCES `gpnxuser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_051_campaign` FOREIGN KEY (`campaign_uuid`) REFERENCES `STUDIO_CAMPAIGNS` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `STUDIO_GEOLOCATION_AREAS` (
  `uuid` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `radius` int(11) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `search_within_circle` bit(1) NOT NULL,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_updated` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `segment` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `partner_id` bigint(20) NOT NULL,
  `query` longtext COLLATE utf8_unicode_ci NOT NULL,
  `uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `rebuild_Frequency` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `active` bit(1) DEFAULT NULL,
  `geolocation_area_uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_last_rebuild` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `segmentuuid` (`uuid`),
  KEY `FK75A49F33725C505E` (`partner_id`),
  KEY `fk_055_geolocation` (`geolocation_area_uuid`),
  CONSTRAINT `FK75A49F33725C505E` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`),
  CONSTRAINT `fk_055_geolocation` FOREIGN KEY (`geolocation_area_uuid`) REFERENCES `STUDIO_GEOLOCATION_AREAS` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `segment_gpnxuser` (
  `segment_users_id` bigint(20) DEFAULT NULL,
  `gpnxuser_id` bigint(20) DEFAULT NULL,
  KEY `FKA2FEFA4A5C2C19D6` (`gpnxuser_id`),
  KEY `idx_012_seg_id_user_id` (`segment_users_id`,`gpnxuser_id`),
  CONSTRAINT `FKA2FEFA4A5C2C19D6` FOREIGN KEY (`gpnxuser_id`) REFERENCES `gpnxuser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_047_segment` FOREIGN KEY (`segment_users_id`) REFERENCES `segment` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `template` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `content` longtext COLLATE utf8_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8_unicode_ci,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `partner_id` bigint(20) NOT NULL,
  `uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DATE_CREATED` timestamp NULL DEFAULT NULL,
  `DATE_UPDATED` timestamp NULL DEFAULT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `metadata` longtext COLLATE utf8_unicode_ci,
  `temporary` bit(1) DEFAULT b'0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_009_uuid` (`uuid`),
  KEY `FKB13ACC7A725C505E` (`partner_id`),
  KEY `unique-partner_id` (`name`,`partner_id`),
  CONSTRAINT `FKB13ACC7A725C505E` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_shared_key` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `ip_address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `uuid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_shared_key` (`uuid`),
  KEY `FKE53F6C792D11F5A9` (`user_id`),
  CONSTRAINT `FKE53F6C792D11F5A9` FOREIGN KEY (`user_id`) REFERENCES `gpnxuser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `url` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `event` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `owner_id` bigint(20) NOT NULL,
  `partner_id` bigint(20) NOT NULL,
  `scopes` longtext COLLATE utf8_unicode_ci,
  `target` longtext COLLATE utf8_unicode_ci NOT NULL,
  `template_id` bigint(20) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `campaign_uuid` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK1C56F725C505E` (`partner_id`),
  KEY `FK1C56F72E66856` (`template_id`),
  KEY `unique-name` (`owner_id`,`partner_id`,`name`),
  KEY `fk_050_campaign` (`campaign_uuid`),
  CONSTRAINT `FK1C56F725C505E` FOREIGN KEY (`partner_id`) REFERENCES `partner` (`id`),
  CONSTRAINT `FK1C56F72E66856` FOREIGN KEY (`template_id`) REFERENCES `template` (`id`),
  CONSTRAINT `FK1C56F98F8A5C1` FOREIGN KEY (`owner_id`) REFERENCES `gpnxuser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_050_campaign` FOREIGN KEY (`campaign_uuid`) REFERENCES `STUDIO_CAMPAIGNS` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;