# Create temporary tables for import and load data from *.csv files

DROP TABLE IF EXISTS `importIndividual`;
CREATE TABLE IF NOT EXISTS `importIndividual` (
  `individualId` varchar(10) DEFAULT NULL,
  `status` varchar(8) DEFAULT NULL,
  `company` varchar(72) DEFAULT NULL,
  `companyId` varchar(12) DEFAULT NULL,
  `firstName` varchar(23) DEFAULT NULL,
  `middleInitial` varchar(6) DEFAULT NULL,
  `lastName` varchar(25) DEFAULT NULL,
  `nickname` varchar(23) DEFAULT NULL,
  `emailAddress` varchar(44) DEFAULT NULL,
  `directPhone` varchar(14) DEFAULT NULL,
  `cellPhone` varchar(14) DEFAULT NULL,
  `jobTitle` varchar(106) DEFAULT NULL,
  `nameOfSupervisor` varchar(31) DEFAULT NULL,
  `password` varchar(5) DEFAULT NULL,
  `notes` varchar(51) DEFAULT NULL,
  `lists` varchar(358) DEFAULT NULL,
  `managementPosition` varchar(100) DEFAULT NULL,
  `managementPrivileges` varchar(1) DEFAULT NULL,
  `surveyContact` varchar(1) DEFAULT NULL,
  `mailingAddress1` varchar(63) DEFAULT NULL,
  `mailingAddress2` varchar(60) DEFAULT NULL,
  `mailingAddressCity` varchar(61) DEFAULT NULL,
  `mailingAddressState` varchar(20) DEFAULT NULL,
  `mailingAddressZip` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE '/home/mmua/import/individuals.csv'
  INTO TABLE importIndividual
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES TERMINATED BY '\n' STARTING BY ''
  IGNORE 1 LINES;

UPDATE importIndividual
SET company = TRIM(company),
  middleInitial = TRIM(middleInitial),
  emailAddress = TRIM(emailAddress),
  cellPhone = TRIM(REPLACE(REPLACE(REPLACE(cellPhone, '-', '.'), ') ', '.'), '(', '')),
  directPhone = TRIM(REPLACE(REPLACE(REPLACE(directPhone, '-', '.'), ') ', '.'), '(', '')),
  jobTitle = TRIM(jobTitle),
  password = TRIM(password),
  lists = TRIM(REPLACE(lists, ', ', ',')),
  managementPosition = TRIM(managementPosition),
  mailingAddress1 = TRIM(mailingAddress1),
  mailingAddress2 = TRIM(mailingAddress2),
  mailingAddressCity = TRIM(mailingAddressCity),
  mailingAddressState = TRIM(mailingAddressState),
  mailingAddressZip = TRIM(mailingAddressZip);

DROP TABLE IF EXISTS `importCompaniesRegular`;
CREATE TABLE IF NOT EXISTS `importCompaniesRegular` (
  `companyId` varchar(10) DEFAULT NULL,
  `status` varchar(8) DEFAULT NULL,
  `companyName` varchar(42) DEFAULT NULL,
  `physicalAddress1` varchar(60) DEFAULT NULL,
  `physicalAddress2` varchar(10) DEFAULT NULL,
  `physicalAddressCity` varchar(60) DEFAULT NULL,
  `physicalAddressState` varchar(20) DEFAULT NULL,
  `physicalAddressZip` varchar(10) DEFAULT NULL,
  `mailingAddress1` varchar(60) DEFAULT NULL,
  `mailingAddress2` varchar(10) DEFAULT NULL,
  `mailingAddressCity` varchar(60) DEFAULT NULL,
  `mailingAddressState` varchar(20) DEFAULT NULL,
  `mailingAddressZip` varchar(10) DEFAULT NULL,
  `county` varchar(30) DEFAULT NULL,
  `companyEmailAddress` varchar(40) DEFAULT NULL,
  `utilityPhone` varchar(17) DEFAULT NULL,
  `utilityFax` varchar(14) DEFAULT NULL,
  `cityOfficesPhone` varchar(17) DEFAULT NULL,
  `cityOfficesFax` varchar(14) DEFAULT NULL,
  `tollFreePhone` varchar(10) DEFAULT NULL,
  `website` varchar(44) DEFAULT NULL,
  `yearFounded` varchar(4) DEFAULT NULL,
  `governedBy` varchar(33) DEFAULT NULL,
  `meetingInfo` varchar(151) DEFAULT NULL,
  `utilityCategories` varchar(58) DEFAULT NULL,
  `taxExempt` varchar(1) DEFAULT NULL,
  `MNHouseLegislativeDistrict` varchar(48) DEFAULT NULL,
  `USCongressionalLegislativeDistrict` varchar(4) DEFAULT NULL,
  `description` varchar(10) DEFAULT NULL,
  `safetyGroups` varchar(33) DEFAULT NULL,
  `population` varchar(7) DEFAULT NULL,
  `statutoryAuthority` varchar(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE '/home/mmua/import/companiesRegular.csv'
  INTO TABLE importCompaniesRegular
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES TERMINATED BY '\n' STARTING BY ''
  IGNORE 1 LINES;

UPDATE importCompaniesRegular SET population = REPLACE(population, ',', ''),
  utilityCategories = REPLACE(utilityCategories, ' ', ''),
  safetyGroups = TRIM(safetyGroups),
  MNHouseLegislativeDistrict = REPLACE(REPLACE(MNHouseLegislativeDistrict, '&', ''), ' ', ''),
  USCongressionalLegislativeDistrict = REPLACE(USCongressionalLegislativeDistrict, ' ', ''),
  utilityPhone = REPLACE(REPLACE(REPLACE(utilityPhone, '-', '.'), ') ', '.'), '(', ''),
  utilityFax = REPLACE(REPLACE(REPLACE(utilityFax, '-', '.'), ') ', '.'), '(', ''),
  tollFreePhone = REPLACE(REPLACE(REPLACE(tollFreePhone, '-', '.'), ') ', '.'), '(', ''),
  cityOfficesPhone = REPLACE(REPLACE(REPLACE(cityOfficesPhone, '-', '.'), ') ', '.'), '(', ''),
  cityOfficesFax = REPLACE(REPLACE(REPLACE(cityOfficesFax, '-', '.'), ') ', '.'), '(', '');

UPDATE importCompaniesRegular SET population = 0 WHERE population = '';

ALTER TABLE `importCompaniesRegular` CHANGE `population` `population` INT NULL DEFAULT NULL;

ALTER TABLE `importCompaniesRegular` ADD PRIMARY KEY ( `companyId` );

DROP TABLE IF EXISTS `importCompaniesAffiliate`;
CREATE TABLE IF NOT EXISTS `importCompaniesAffiliate` (
  `companyId` varchar(10) DEFAULT NULL,
  `status` varchar(6) DEFAULT NULL,
  `companyName` varchar(41) DEFAULT NULL,
  `physicalAddress1` varchar(32) DEFAULT NULL,
  `physicalAddress2` varchar(10) DEFAULT NULL,
  `physicalAddressCity` varchar(17) DEFAULT NULL,
  `physicalAddressState` varchar(2) DEFAULT NULL,
  `physicalAddressZip` varchar(5) DEFAULT NULL,
  `mailingAddress1` varchar(32) DEFAULT NULL,
  `mailingAddress2` varchar(10) DEFAULT NULL,
  `mailingAddressCity` varchar(17) DEFAULT NULL,
  `mailingAddressState` varchar(2) DEFAULT NULL,
  `mailingAddressZip` varchar(10) DEFAULT NULL,
  `phone` varchar(14) DEFAULT NULL,
  `tollFreePhone` varchar(14) DEFAULT NULL,
  `fax` varchar(14) DEFAULT NULL,
  `website` varchar(17) DEFAULT NULL,
  `taxExempt` varchar(1) DEFAULT NULL,
  `description` varchar(945) DEFAULT NULL,
  `safetyGroups` varchar(17) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE '/home/mmua/import/companiesAffiliate.csv'
  INTO TABLE importCompaniesAffiliate
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES TERMINATED BY '\n' STARTING BY ''
  IGNORE 1 LINES;

UPDATE importCompaniesAffiliate SET phone = REPLACE(REPLACE(REPLACE(phone, '-', '.'), ') ', '.'), '(', ''),
  tollFreePhone = REPLACE(REPLACE(REPLACE(tollFreePhone, '-', '.'), ') ', '.'), '(', ''),
  fax = REPLACE(REPLACE(REPLACE(fax, '-', '.'), ') ', '.'), '(', '');

ALTER TABLE `importCompaniesAffiliate` ADD PRIMARY KEY ( `companyId` );

DROP TABLE IF EXISTS `importCompaniesAssociate`;
CREATE TABLE IF NOT EXISTS `importCompaniesAssociate` (
  `companyId` varchar(10) DEFAULT NULL,
  `status` varchar(15) DEFAULT NULL,
  `companyName` varchar(53) DEFAULT NULL,
  `physicalAddress1` varchar(39) DEFAULT NULL,
  `physicalAddress2` varchar(23) DEFAULT NULL,
  `physicalAddressCity` varchar(18) DEFAULT NULL,
  `physicalAddressState` varchar(2) DEFAULT NULL,
  `physicalAddressZip` varchar(10) DEFAULT NULL,
  `mailingAddress1` varchar(39) DEFAULT NULL,
  `mailingAddress2` varchar(21) DEFAULT NULL,
  `mailingAddressCity` varchar(18) DEFAULT NULL,
  `mailingAddressState` varchar(2) DEFAULT NULL,
  `mailingAddressZip` varchar(10) DEFAULT NULL,
  `companyEmailAddress` char(35) DEFAULT NULL,
  `phone` varchar(14) DEFAULT NULL,
  `tollFreePhone` varchar(20) DEFAULT NULL,
  `fax` varchar(14) DEFAULT NULL,
  `website` varchar(51) DEFAULT NULL,
  `taxExempt` varchar(1) DEFAULT NULL,
  `description` varchar(1120) DEFAULT NULL,
  `professionalCategory` varchar(37) DEFAULT NULL,
  `safetyGroups` varchar(27) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE '/home/mmua/import/companiesAssociate.csv'
  INTO TABLE importCompaniesAssociate
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES TERMINATED BY '\n' STARTING BY ''
  IGNORE 1 LINES;

UPDATE importCompaniesAssociate SET safetyGroups = TRIM(safetyGroups),
  phone = REPLACE(REPLACE(REPLACE(phone, '-', '.'), ') ', '.'), '(', ''),
  tollFreePhone = REPLACE(REPLACE(REPLACE(tollFreePhone, '-', '.'), ') ', '.'), '(', ''),
  fax = REPLACE(REPLACE(REPLACE(fax, '-', '.'), ') ', '.'), '(', '');

ALTER TABLE `importCompaniesAssociate` ADD PRIMARY KEY ( `companyId` );

DROP TABLE IF EXISTS `importCompaniesVendor`;
CREATE TABLE IF NOT EXISTS `importCompaniesVendor` (
  `companyId` varchar(10) DEFAULT NULL,
  `status` varchar(6) DEFAULT NULL,
  `companyName` varchar(45) DEFAULT NULL,
  `physicalAddress1` varchar(34) DEFAULT NULL,
  `physicalAddress2` varchar(10) DEFAULT NULL,
  `physicalAddressCity` varchar(18) DEFAULT NULL,
  `physicalAddressState` varchar(2) DEFAULT NULL,
  `physicalAddressZip` varchar(5) DEFAULT NULL,
  `mailingAddress1` varchar(34) DEFAULT NULL,
  `mailingAddress2` varchar(10) DEFAULT NULL,
  `mailingAddressCity` varchar(18) DEFAULT NULL,
  `mailingAddressState` varchar(2) DEFAULT NULL,
  `mailingAddressZip` varchar(5) DEFAULT NULL,
  `phone` varchar(14) DEFAULT NULL,
  `tollFreePhone` varchar(14) DEFAULT NULL,
  `fax` varchar(14) DEFAULT NULL,
  `website` varchar(32) DEFAULT NULL,
  `professionalCategory` varchar(27) DEFAULT NULL,
  `taxExempt` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE '/home/mmua/import/companiesVendor.csv'
  INTO TABLE importCompaniesVendor
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES TERMINATED BY '\n' STARTING BY ''
  IGNORE 1 LINES;

UPDATE importCompaniesVendor SET phone = REPLACE(REPLACE(REPLACE(phone, '-', '.'), ') ', '.'), '(', ''),
  tollFreePhone = REPLACE(REPLACE(REPLACE(tollFreePhone, '-', '.'), ') ', '.'), '(', ''),
  fax = REPLACE(REPLACE(REPLACE(fax, '-', '.'), ') ', '.'), '(', '');

ALTER TABLE `importCompaniesVendor` ADD PRIMARY KEY ( `companyId` );

DROP TABLE IF EXISTS `importCompaniesNonMember`;
CREATE TABLE IF NOT EXISTS `importCompaniesNonMember` (
  `companyId` varchar(10) DEFAULT NULL,
  `status` varchar(8) DEFAULT NULL,
  `companyName` varchar(53) DEFAULT NULL,
  `physicalAddress1` varchar(38) DEFAULT NULL,
  `physicalAddress2` varchar(41) DEFAULT NULL,
  `physicalAddressCity` varchar(17) DEFAULT NULL,
  `physicalAddressState` varchar(6) DEFAULT NULL,
  `physicalAddressZip` varchar(10) DEFAULT NULL,
  `mailingAddress1` varchar(41) DEFAULT NULL,
  `mailingAddress2` varchar(41) DEFAULT NULL,
  `mailingAddressCity` varchar(17) DEFAULT NULL,
  `mailingAddressState` varchar(6) DEFAULT NULL,
  `mailingAddressZip` varchar(10) DEFAULT NULL,
  `phone` varchar(14) DEFAULT NULL,
  `tollFreePhone` varchar(14) DEFAULT NULL,
  `fax` varchar(14) DEFAULT NULL,
  `website` varchar(31) DEFAULT NULL,
  `utilityCategory` varchar(10) DEFAULT NULL,
  `professionalCategory` char(16) DEFAULT NULL,
  `taxExempt` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE '/home/mmua/import/companiesNonmember.csv'
  INTO TABLE importCompaniesNonMember
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
  LINES TERMINATED BY '\n' STARTING BY ''
  IGNORE 1 LINES;

UPDATE importCompaniesNonMember SET phone = REPLACE(REPLACE(REPLACE(phone, '-', '.'), ') ', '.'), '(', ''),
  tollFreePhone = REPLACE(REPLACE(REPLACE(tollFreePhone, '-', '.'), ') ', '.'), '(', ''),
  fax = REPLACE(REPLACE(REPLACE(fax, '-', '.'), ') ', '.'), '(', '');

ALTER TABLE `importCompaniesNonMember` ADD PRIMARY KEY ( `companyId` );

# Drop foreign keys in order to clean (truncate) all tables for import
TRUNCATE TABLE `mm_CompanyAssociateCategory`;
ALTER TABLE `mm_CompanyAssociateCategory` DROP FOREIGN key `mm_CompanyAssociateCategory_ibfk_1`;

TRUNCATE TABLE `mm_CompanyDistrict`;
ALTER TABLE `mm_CompanyDistrict` DROP FOREIGN key `mm_CompanyDistrict_ibfk_1`;

TRUNCATE TABLE `mm_CompanyPermission`;
ALTER TABLE `mm_CompanyPermission` DROP FOREIGN key `mm_CompanyPermission_ibfk_1`;
ALTER TABLE `mm_CompanyPermission` DROP FOREIGN key `mm_CompanyPermission_ibfk_2`;

TRUNCATE TABLE `mm_CompanySafetyGroup`;
ALTER TABLE `mm_CompanySafetyGroup` DROP FOREIGN key `mm_CompanySafetyGroup_ibfk_1`;
ALTER TABLE `mm_CompanySafetyGroup` DROP FOREIGN key `mm_CompanySafetyGroup_ibfk_2`;

TRUNCATE TABLE `mm_CompanyUtilityCategory`;
ALTER TABLE `mm_CompanyUtilityCategory` DROP FOREIGN key `mm_CompanyUtilityCategory_ibfk_1`;

TRUNCATE TABLE `mm_CompanyUtilityStaff`;
ALTER TABLE `mm_CompanyUtilityStaff` DROP FOREIGN key `mm_CompanyUtilityStaff_ibfk_1`;
ALTER TABLE `mm_CompanyUtilityStaff` DROP FOREIGN key `mm_CompanyUtilityStaff_ibfk_3`;

ALTER TABLE `mm_User` DROP FOREIGN key `mm_User_ibfk_5`;
ALTER TABLE `mm_Company` DROP FOREIGN key `mm_Company_ibfk_1`;

TRUNCATE TABLE `mm_Company`;

TRUNCATE TABLE `mm_UserAddress`;
ALTER TABLE `mm_UserAddress` DROP FOREIGN key `fk_UserId`;

TRUNCATE TABLE `mm_UserList`;
ALTER TABLE `mm_UserList` DROP FOREIGN key `mm_UserList_ibfk_1`;

TRUNCATE TABLE `mm_Classified`;
ALTER TABLE `mm_Classified` DROP FOREIGN key `mm_Classified_ibfk_2`;

TRUNCATE TABLE `mm_EventAttendeeOption`;
ALTER TABLE `mm_EventAttendeeOption` DROP FOREIGN key `mm_EventAttendeeOption_ibfk_1`;

TRUNCATE TABLE `mm_EventAttendee`;
ALTER TABLE `mm_EventAttendee` DROP FOREIGN key `mm_EventAttendee_ibfk_2`;

TRUNCATE TABLE `mm_SponsorshipSubmission`;
ALTER TABLE `mm_SponsorshipSubmission` DROP FOREIGN key `mm_SponsorshipSubmission_ibfk_1`;

TRUNCATE TABLE `mm_TradeShowAttendee`;
ALTER TABLE `mm_TradeShowAttendee` DROP FOREIGN key `mm_TradeShowAttendee_ibfk_2`;

TRUNCATE TABLE `mm_User`;

TRUNCATE TABLE `mm_SafetyGroup`;

# Actual import - adding key columns
ALTER TABLE mm_Company ADD COLUMN companyId VARCHAR (10);
ALTER TABLE mm_Company ADD UNIQUE KEY ( `companyId` );
ALTER TABLE mm_User ADD COLUMN individualId VARCHAR (10);
ALTER TABLE mm_User ADD UNIQUE KEY ( `individualId` );

# Actual import - regular companies
INSERT INTO mm_Company (
  type, status, name, address, address2, city, state, zip, mailingAddress, mailingAddress2, mailingCity, mailingState,
    mailingZip, countyId, population, statutoryAuthorityId, email, utilityPhone, utilityFax,
    cityOfficesPhone, cityOfficesFax, tollFreePhone, websiteUrl, yearFounded, governedBy, meetingInfo,
    description, taxExempt, added, changed, companyId, poNumber
)
SELECT 'regular', cs.id, companyName, physicalAddress1, physicalAddress2, physicalAddressCity, physicalAddressState,
  physicalAddressZip, mailingAddress1, mailingAddress2, mailingAddressCity, mailingAddressState, mailingAddressZip,
  c.id, population, sa.id, companyEmailAddress, utilityPhone, utilityFax, cityOfficesPhone,
  cityOfficesFax, tollFreePhone, website, yearFounded, governedBy, meetingInfo, description,
  IF(taxExempt = 'Y', 'y', 'n'), NOW(),
  NOW(), companyId, ''
FROM importCompaniesRegular icr
  LEFT JOIN mm_CompanyStatus cs ON icr.status = cs.title
  LEFT JOIN mm_County c ON icr.county = c.title
  LEFT JOIN mm_StatutoryAuthority sa ON icr.statutoryAuthority = sa.title;

INSERT INTO mm_CompanyUtilityCategory
SELECT c.id, uc.id
  FROM mm_Company c
  JOIN importCompaniesRegular icr ON c.companyId = icr.companyId
  JOIN mm_UtilityCategory uc ON FIND_IN_SET(uc.title, icr.utilityCategories);

INSERT INTO mm_CompanyDistrict
SELECT c.id, ld.type, ld.district
  FROM mm_Company c
    JOIN importCompaniesRegular icr ON c.companyId = icr.companyId
    JOIN mm_LegislativeDistrict ld ON
        (ld.type = 'mnHouse' AND FIND_IN_SET(ld.district, icr.MNHouseLegislativeDistrict)) OR
        (ld.type = 'usCongressional' AND FIND_IN_SET(ld.district, icr.USCongressionalLegislativeDistrict));

INSERT INTO mm_SafetyGroup (title, added, changed)
SELECT DISTINCT safetyGroups, NOW(), NOW()
  FROM importCompaniesRegular
  WHERE safetyGroups != ''
UNION
SELECT DISTINCT safetyGroups, NOW(), NOW()
  FROM importCompaniesAssociate
  WHERE safetyGroups != ''
UNION
SELECT DISTINCT safetyGroups, NOW(), NOW()
  FROM importCompaniesAffiliate
  WHERE safetyGroups != '';

INSERT INTO mm_CompanySafetyGroup
  SELECT c.id, sg.id
  FROM mm_Company c
    JOIN importCompaniesRegular icr ON c.companyId = icr.companyId
    JOIN mm_SafetyGroup sg ON icr.safetyGroups = sg.title;

# Actual import - associate companies
INSERT INTO mm_Company (
  type, status, name, address, address2, city, state, zip, mailingAddress, mailingAddress2, mailingCity, mailingState,
  mailingZip, email, phone, tollFreePhone, fax, websiteUrl, description, taxExempt, added, changed, companyId, poNumber
)
  SELECT 'associate', cs.id, companyName, physicalAddress1, physicalAddress2, physicalAddressCity, physicalAddressState,
    physicalAddressZip, mailingAddress1, mailingAddress2, mailingAddressCity, mailingAddressState, mailingAddressZip,
    companyEmailAddress, phone, tollFreePhone, fax, website, description, IF(taxExempt = 'Y', 'y', 'n'), NOW(), NOW(),
    companyId, ''
  FROM importCompaniesAssociate ica
    LEFT JOIN mm_CompanyStatus cs ON ica.status = cs.title;

INSERT INTO mm_CompanySafetyGroup
  SELECT c.id, sg.id
  FROM mm_Company c
    JOIN importCompaniesAssociate ica ON c.companyId = ica.companyId
    JOIN mm_SafetyGroup sg ON ica.safetyGroups = sg.title;

CREATE TEMPORARY TABLE tAssociateCategory AS
SELECT DISTINCT professionalCategory
FROM importCompaniesAssociate
WHERE professionalCategory != '' AND professionalCategory NOT IN (SELECT title FROM mm_AssociateCategory);

INSERT INTO mm_AssociateCategory (title, added, changed)
    SELECT professionalCategory, NOW(), NOW()
FROM tAssociateCategory;

INSERT INTO mm_CompanyAssociateCategory
  SELECT c.id, ac.id
  FROM mm_Company c
    JOIN importCompaniesAssociate ica ON c.companyId = ica.companyId
    JOIN mm_AssociateCategory ac ON ica.professionalCategory = ac.title;

# Actual import - affiliate companies
INSERT INTO mm_Company (
  type, status, name, address, address2, city, state, zip, mailingAddress, mailingAddress2, mailingCity, mailingState,
  mailingZip, phone, tollFreePhone, fax, websiteUrl, description, taxExempt, added, changed, companyId, poNumber
)
  SELECT 'affiliate', cs.id, companyName, physicalAddress1, physicalAddress2, physicalAddressCity, physicalAddressState,
    physicalAddressZip, mailingAddress1, mailingAddress2, mailingAddressCity, mailingAddressState, mailingAddressZip,
    phone, tollFreePhone, fax, website, description, IF(taxExempt = 'Y', 'y', 'n'), NOW(), NOW(), companyId, ''
  FROM importCompaniesAffiliate ica
    LEFT JOIN mm_CompanyStatus cs ON ica.status = cs.title;

INSERT INTO mm_CompanySafetyGroup
  SELECT c.id, sg.id
  FROM mm_Company c
    JOIN importCompaniesAffiliate ica ON c.companyId = ica.companyId
    JOIN mm_SafetyGroup sg ON ica.safetyGroups = sg.title;

# Actual import - nonMember companies
INSERT INTO mm_Company (
  type, status, name, address, address2, city, state, zip, mailingAddress, mailingAddress2, mailingCity, mailingState,
  mailingZip, phone, tollFreePhone, fax, websiteUrl, taxExempt, added, changed, companyId, poNumber
)
  SELECT 'nonmember', cs.id, companyName, physicalAddress1, physicalAddress2, physicalAddressCity, physicalAddressState,
    physicalAddressZip, mailingAddress1, mailingAddress2, mailingAddressCity, mailingAddressState, mailingAddressZip,
    phone, tollFreePhone, fax, website, IF(taxExempt = 'Y', 'y', 'n'), NOW(), NOW(), companyId, ''
  FROM importCompaniesNonMember icnm
    LEFT JOIN mm_CompanyStatus cs ON icnm.status = cs.title;

CREATE TEMPORARY TABLE tNonMemberCategory AS
  SELECT DISTINCT professionalCategory
  FROM importCompaniesNonMember
  WHERE professionalCategory != '' AND professionalCategory NOT IN (SELECT title FROM mm_AssociateCategory);

INSERT INTO mm_AssociateCategory (title, added, changed)
  SELECT professionalCategory, NOW(), NOW()
  FROM tNonMemberCategory;

INSERT INTO mm_CompanyAssociateCategory
  SELECT c.id, ac.id
  FROM mm_Company c
    JOIN importCompaniesNonMember icnm ON c.companyId = icnm.companyId
    JOIN mm_AssociateCategory ac ON icnm.professionalCategory = ac.title;

# Actual import - vendor companies
INSERT INTO mm_Company (
  type, status, name, address, address2, city, state, zip, mailingAddress, mailingAddress2, mailingCity, mailingState,
  mailingZip, phone, tollFreePhone, fax, websiteUrl, taxExempt, added, changed, companyId, poNumber
)
  SELECT 'vendor', cs.id, companyName, physicalAddress1, physicalAddress2, physicalAddressCity, physicalAddressState,
    physicalAddressZip, mailingAddress1, mailingAddress2, mailingAddressCity, mailingAddressState, mailingAddressZip,
    phone, tollFreePhone, fax, website, IF(taxExempt = 'Y', 'y', 'n'), NOW(), NOW(), companyId, ''
  FROM importCompaniesVendor icv
    LEFT JOIN mm_CompanyStatus cs ON icv.status = cs.title;

CREATE TEMPORARY TABLE tVendorCategory AS
  SELECT DISTINCT professionalCategory
  FROM importCompaniesVendor
  WHERE professionalCategory != '' AND professionalCategory NOT IN (SELECT title FROM mm_AssociateCategory);

INSERT INTO mm_AssociateCategory (title, added, changed)
  SELECT professionalCategory, NOW(), NOW()
  FROM tVendorCategory;

INSERT INTO mm_CompanyAssociateCategory
  SELECT c.id, ac.id
  FROM mm_Company c
    JOIN importCompaniesVendor icv ON c.companyId = icv.companyId
    JOIN mm_AssociateCategory ac ON icv.professionalCategory = ac.title;

# Actual import - individuals
INSERT INTO mm_User(
  bought, firstName, middleInitial, lastName, nickname, alias, companyId, email, directPhone, cellPhone, jobTitle,
  supervisorName, notes, password, salt, status, sorter, added, changed, individualId
)
SELECT 'y', firstName, middleInitial, lastName, nickname, NULL, c.id, NULLIF(emailAddress, ''), directPhone, cellPhone,
  jobTitle, nameOfSupervisor, notes, password, SUBSTR(MD5(CONCAT(NOW(), RAND())), 1, 8), us.id, 0, NOW(), NOW(),
  individualId
FROM importIndividual ii
  LEFT JOIN mm_UserStatus us ON ii.status = us.title
  LEFT JOIN mm_Company c ON ii.companyId = c.companyId;

UPDATE mm_User
  SET sorter = id, password = MD5(CONCAT(salt, password));

INSERT INTO mm_UserAddress (userId, type, firstName, lastName, address, address2, city, state, zip, added, changed)
    SELECT u.id, 'mailing', ii.firstName, ii.lastName, mailingAddress1, mailingAddress2, mailingAddressCity,
      mailingAddressState, mailingAddressZip, NOW(), NOW()
FROM importIndividual ii
  JOIN mm_User u ON ii.individualId = u.individualId;

UPDATE mm_Company c
  JOIN importIndividual ii ON c.companyId = ii.companyId AND ii.surveyContact = 'Y'
  JOIN mm_User u ON ii.individualId = u.individualId
SET c.surveyContactId = u.id;

INSERT INTO mm_UserList
SELECT u.id, l.id
FROM mm_User u
  JOIN importIndividual ii ON u.individualId = ii.individualId
  JOIN mm_List l ON FIND_IN_SET(l.title, ii.lists);

INSERT INTO mm_CompanyPermission
SELECT c.id, u.id
FROM mm_User u
  JOIN importIndividual ii ON u.individualId = ii.individualId AND ii.managementPrivileges = 'Y'
  JOIN mm_Company c ON c.companyId = ii.companyId;

INSERT INTO mm_CompanyUtilityStaff (companyId, positionId, userId, sorter)
  SELECT c.id, pp.id, u.id, 0
  FROM mm_User u
    JOIN importIndividual ii ON u.individualId = ii.individualId
    JOIN mm_PersonnelPosition pp ON pp.title = ii.managementPosition
    JOIN mm_Company c ON c.companyId = ii.companyId;

UPDATE `mm_Company` SET websiteUrl = CONCAT('http://', websiteUrl);

# Restore foreign keys
ALTER TABLE `mm_CompanyAssociateCategory`
  ADD CONSTRAINT `mm_CompanyAssociateCategory_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `mm_Company` (`id`) ON DELETE CASCADE;
ALTER TABLE `mm_CompanyDistrict`
  ADD CONSTRAINT `mm_CompanyDistrict_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `mm_Company` (`id`) ON DELETE CASCADE;
ALTER TABLE `mm_CompanyPermission`
  ADD CONSTRAINT `mm_CompanyPermission_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `mm_Company` (`id`) ON DELETE CASCADE;
ALTER TABLE `mm_CompanySafetyGroup`
  ADD CONSTRAINT `mm_CompanySafetyGroup_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `mm_Company` (`id`) ON DELETE CASCADE;
ALTER TABLE `mm_CompanySafetyGroup`
  ADD CONSTRAINT `mm_CompanySafetyGroup_ibfk_2` FOREIGN KEY (`safetyGroupId`) REFERENCES `mm_SafetyGroup` (`id`);
ALTER TABLE `mm_CompanyUtilityCategory`
  ADD CONSTRAINT `mm_CompanyUtilityCategory_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `mm_Company` (`id`) ON DELETE CASCADE;
ALTER TABLE `mm_CompanyUtilityStaff`
  ADD CONSTRAINT `mm_CompanyUtilityStaff_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `mm_Company` (`id`) ON DELETE CASCADE;
ALTER TABLE `mm_User`
  ADD CONSTRAINT `mm_User_ibfk_5` FOREIGN KEY (`companyId`) REFERENCES `mm_Company` (`id`) ON DELETE SET NULL;

ALTER TABLE `mm_CompanyPermission`
  ADD CONSTRAINT `mm_CompanyPermission_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `mm_User` (`id`) ON DELETE CASCADE;
ALTER TABLE `mm_CompanyUtilityStaff`
  ADD CONSTRAINT `mm_CompanyUtilityStaff_ibfk_3` FOREIGN KEY (`userId`) REFERENCES `mm_User` (`id`) ON DELETE CASCADE;
ALTER TABLE `mm_UserAddress`
  ADD CONSTRAINT `fk_UserId` FOREIGN KEY (`userId`) REFERENCES `mm_User` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `mm_UserList`
  ADD CONSTRAINT `mm_UserList_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `mm_User` (`id`) ON DELETE CASCADE;
ALTER TABLE `mm_Classified`
  ADD CONSTRAINT `mm_Classified_ibfk_2` FOREIGN KEY (`submitterUserId`) REFERENCES `mm_User` (`id`) ON DELETE SET NULL;
ALTER TABLE `mm_Company`
  ADD CONSTRAINT `mm_Company_ibfk_1` FOREIGN KEY (`surveyContactId`) REFERENCES `mm_User` (`id`) ON DELETE SET NULL;
ALTER TABLE `mm_EventAttendee`
  ADD CONSTRAINT `mm_EventAttendee_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `mm_User` (`id`) ON DELETE SET NULL;
ALTER TABLE `mm_EventAttendeeOption`
  ADD CONSTRAINT `mm_EventAttendeeOption_ibfk_1` FOREIGN KEY (`attendeeId`) REFERENCES `mm_EventAttendee` (`id`) ON DELETE CASCADE ;
ALTER TABLE `mm_SponsorshipSubmission`
  ADD CONSTRAINT `mm_SponsorshipSubmission_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `mm_User` (`id`) ON DELETE SET NULL;
ALTER TABLE `mm_TradeShowAttendee`
  ADD CONSTRAINT `mm_TradeShowAttendee_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `mm_User` (`id`) ON DELETE SET NULL;

# Drop import columns
ALTER TABLE mm_Company DROP COLUMN companyId;
ALTER TABLE mm_User DROP COLUMN individualId ;