--source include/have_innodb.inc
set foreign_key_checks=0;

--disable_query_log
DROP TABLE IF EXISTS `_product_instance_new`;
CREATE TABLE `_product_instance_new` (
  `PRODUCT_INSTANCE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `PRODUCT_SPECIFICATION_ID` int(11) NOT NULL,
  `CUSTOMER_PROFILE_ID` int(11) NOT NULL,
  `ORGANISATION_ID` int(11) NOT NULL,
  `STATUS` char(2) NOT NULL,
  `SEGMENT` varchar(20) NOT NULL,
  `CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
  `CREATED_BY_ORGANISATION_ID` int(11) DEFAULT NULL,
  `CREATED_DATETIME` datetime NOT NULL,
  `PROMOTION_CODE` varchar(100) NOT NULL,
  `LAST_MODIFIED` datetime NOT NULL,
  `FRIENDLY_NAME` varchar(200) NOT NULL,
  `LOGICAL_ID` int(11) DEFAULT NULL,
  `PHYSICAL_ID` varchar(20) DEFAULT NULL,
  `LAST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
  `FIRST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
  `LAST_RECONNECTION_DATETIME` datetime DEFAULT NULL,
  `LAST_IMEI` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`PRODUCT_INSTANCE_ID`),
  KEY `CUSTOMER_PROFILE_ID` (`CUSTOMER_PROFILE_ID`),
  KEY `FK_PRODUCT_INSTANCE_SPECIFICATION` (`PRODUCT_SPECIFICATION_ID`),
  KEY `product_instance_ibfk_3` (`ORGANISATION_ID`),
  KEY `product_instance_ibfk_4` (`CREATED_BY_CUSTOMER_PROFILE_ID`),
  KEY `LOGICAL_ID` (`LOGICAL_ID`),
  KEY `CREATED_BY_ORGANISATION_ID` (`CREATED_BY_ORGANISATION_ID`),
  CONSTRAINT `_product_instance_new_ibfk_1` FOREIGN KEY (`CREATED_BY_ORGANISATION_ID`) REFERENCES `organisation` (`ORGANISATION_ID`),
  CONSTRAINT `product_instance_ibfk_1` FOREIGN KEY (`PRODUCT_SPECIFICATION_ID`) REFERENCES `product_specification` (`PRODUCT_SPECIFICATION_ID`),
  CONSTRAINT `product_instance_ibfk_2` FOREIGN KEY (`CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile` (`CUSTOMER_PROFILE_ID`),
  CONSTRAINT `product_instance_ibfk_3` FOREIGN KEY (`ORGANISATION_ID`) REFERENCES `organisation` (`ORGANISATION_ID`),
  CONSTRAINT `product_instance_ibfk_4` FOREIGN KEY (`CREATED_BY_CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile` (`CUSTOMER_PROFILE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=308821 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `customer_profile`;
CREATE TABLE `customer_profile` (
  `CUSTOMER_PROFILE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `TITLE` varchar(20) DEFAULT NULL,
  `FIRST_NAME` varchar(100) NOT NULL,
  `MIDDLE_NAME` varchar(100) NOT NULL,
  `LAST_NAME` varchar(100) NOT NULL,
  `ID_NUMBER` varchar(50) NOT NULL,
  `CREATED_DATETIME` datetime NOT NULL,
  `DATE_OF_BIRTH` varchar(8) NOT NULL,
  `GENDER` varchar(1) NOT NULL,
  `LANGUAGE` varchar(50) NOT NULL,
  `EMAIL_ADDRESS` varchar(200) NOT NULL,
  `ALTERNATIVE_CONTACT_1` varchar(100) NOT NULL,
  `ALTERNATIVE_CONTACT_2` varchar(100) NOT NULL,
  `CLASSIFICATION` varchar(50) NOT NULL,
  `VERSION` int(11) NOT NULL,
  `STATUS` varchar(2) NOT NULL,
  `SSO_IDENTITY` varchar(100) NOT NULL,
  `SSO_DIGEST` varchar(100) NOT NULL,
  `SSO_AUTH_ATTEMPTS` tinyint(4) NOT NULL,
  `SSO_LOCK_EXPIRY` datetime DEFAULT NULL,
  `ID_NUMBER_TYPE` varchar(50) NOT NULL,
  `OPT_IN_LEVEL` int(11) NOT NULL,
  `CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
  `ACCOUNT_MANAGER_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
  `MOTHERS_MAIDEN_NAME` varchar(100) NOT NULL,
  `NATIONALITY` varchar(100) NOT NULL,
  `PASSPORT_EXPIRY_DATE` varchar(8) NOT NULL,
  `WAREHOUSE_ID` varchar(50) NOT NULL,
  `UPDATED_DATETIME` datetime DEFAULT NULL,
  `KYC_STATUS` varchar(1) DEFAULT NULL,
  `REFERRAL_CODE` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`CUSTOMER_PROFILE_ID`),
  UNIQUE KEY `SSO_IDENTITY` (`SSO_IDENTITY`),
  KEY `FIRST_NAME` (`FIRST_NAME`),
  KEY `LAST_NAME` (`LAST_NAME`),
  KEY `ID_NUMBER` (`ID_NUMBER`),
  KEY `EMAIL_ADDRESS` (`EMAIL_ADDRESS`)
) ENGINE=InnoDB AUTO_INCREMENT=41655 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `organisation`;
CREATE TABLE `organisation` (
  `ORGANISATION_ID` int(11) NOT NULL AUTO_INCREMENT,
  `ORGANISATION_NAME` varchar(200) NOT NULL,
  `ALTERNATIVE_CONTACT_1` varchar(100) NOT NULL,
  `ALTERNATIVE_CONTACT_2` varchar(100) NOT NULL,
  `EMAIL_ADDRESS` varchar(200) NOT NULL,
  `ORGANISATION_TYPE` varchar(50) NOT NULL,
  `TAX_NUMBER` varchar(50) NOT NULL,
  `COMPANY_NUMBER` varchar(50) NOT NULL,
  `SIZE` varchar(20) NOT NULL,
  `INDUSTRY` varchar(50) NOT NULL,
  `ACCOUNT_MANAGER_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
  `CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
  `STATUS` varchar(2) NOT NULL,
  `VERSION` int(11) NOT NULL,
  `CREATED_DATETIME` datetime NOT NULL,
  `CREDIT_ACCOUNT_NUMBER` varchar(20) NOT NULL,
  `MODIFICATION_ROLES` varchar(2000) NOT NULL,
  `CHANNEL_CODE` varchar(20) NOT NULL,
  PRIMARY KEY (`ORGANISATION_ID`),
  UNIQUE KEY `ORGANISATION_NAME` (`ORGANISATION_NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=7259 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `product_instance`;
CREATE TABLE `product_instance` (
  `PRODUCT_INSTANCE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `PRODUCT_SPECIFICATION_ID` int(11) NOT NULL,
  `CUSTOMER_PROFILE_ID` int(11) NOT NULL,
  `ORGANISATION_ID` int(11) NOT NULL,
  `STATUS` char(2) NOT NULL,
  `SEGMENT` varchar(20) NOT NULL,
  `CREATED_BY_CUSTOMER_PROFILE_ID` int(11) NOT NULL,
  `CREATED_DATETIME` datetime NOT NULL,
  `PROMOTION_CODE` varchar(100) NOT NULL,
  `LAST_MODIFIED` datetime NOT NULL,
  `FRIENDLY_NAME` varchar(200) NOT NULL,
  `LOGICAL_ID` int(11) DEFAULT NULL,
  `PHYSICAL_ID` varchar(20) DEFAULT NULL,
  `LAST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
  `FIRST_ACTIVITY_DATETIME` datetime DEFAULT NULL,
  `LAST_RECONNECTION_DATETIME` datetime DEFAULT NULL,
  `LAST_IMEI` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`PRODUCT_INSTANCE_ID`),
  KEY `CUSTOMER_PROFILE_ID` (`CUSTOMER_PROFILE_ID`),
  KEY `FK_PRODUCT_INSTANCE_SPECIFICATION` (`PRODUCT_SPECIFICATION_ID`),
  KEY `product_instance_ibfk_3` (`ORGANISATION_ID`),
  KEY `product_instance_ibfk_4` (`CREATED_BY_CUSTOMER_PROFILE_ID`),
  KEY `LOGICAL_ID` (`LOGICAL_ID`),
  CONSTRAINT `_product_instance_ibfk_1` FOREIGN KEY (`PRODUCT_SPECIFICATION_ID`) REFERENCES `product_specification` (`PRODUCT_SPECIFICATION_ID`),
  CONSTRAINT `_product_instance_ibfk_2` FOREIGN KEY (`CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile` (`CUSTOMER_PROFILE_ID`),
  CONSTRAINT `_product_instance_ibfk_3` FOREIGN KEY (`ORGANISATION_ID`) REFERENCES `organisation` (`ORGANISATION_ID`),
  CONSTRAINT `_product_instance_ibfk_4` FOREIGN KEY (`CREATED_BY_CUSTOMER_PROFILE_ID`) REFERENCES `customer_profile` (`CUSTOMER_PROFILE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=308821 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `product_specification`;
CREATE TABLE `product_specification` (
  `PRODUCT_SPECIFICATION_ID` int(11) NOT NULL,
  `PRODUCT_NAME` varchar(1000) NOT NULL,
  `PRODUCT_DESCRIPTION` varchar(2000) NOT NULL,
  `AVAILABLE_FROM` datetime NOT NULL,
  `AVAILABLE_TO` datetime NOT NULL,
  `PROVISION_ROLES` varchar(2000) NOT NULL,
  `SEGMENTS` varchar(2000) NOT NULL,
  `REPORTING_TYPE` varchar(20) NOT NULL,
  PRIMARY KEY (`PRODUCT_SPECIFICATION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--enable_query_log
set foreign_key_checks=1;

RENAME TABLE `product_instance` TO `XK4P3A_B0ZCUC_4WXPFR_MIDTE7_BOA174_IQJFXC_2AE81C_HP6FVD_1OLDJ0_K`;
show tables;
RENAME TABLE `_product_instance_new` TO `product_instance`;