# One-time updates;
# ALTER TABLE `mm_User` ADD `individualId` VARCHAR(12) NOT NULL , ADD INDEX (`individualId`) ;

UPDATE mm_User u SET u.individualId = '';

DROP TABLE IF EXISTS `updateIndividualId`;
CREATE TABLE `updateIndividualId`
    SELECT ii.individualId, u.id, u.added, u.changed, c.added added2, c.changed changed2, u.firstName, u.lastName,
      u.middleInitial, ua.address, u.email, c.name
    FROM mm_User u
      LEFT JOIN mm_UserAddress ua ON u.id = ua.userId AND ua.type = 'mailing'
      LEFT JOIN importIndividual ii ON u.firstName = ii.firstName AND u.lastName = ii.lastName AND
                                       u.middleInitial = ii.middleInitial AND ua.address = ii.mailingAddress1 AND
                                       IFNULL(u.email, '') = ii.emailAddress
      LEFT JOIN mm_Company c ON c.id = u.companyId
      LEFT JOIN (
                  SELECT companyId, companyName FROM importCompaniesAffiliate
                  UNION SELECT companyId, companyName FROM importCompaniesNonMember
                  UNION SELECT companyId, companyName FROM importCompaniesVendor
                  UNION SELECT companyId, companyName FROM importCompaniesAssociate
                  UNION SELECT companyId, companyName FROM importCompaniesRegular
                ) AS ic ON ii.companyId = ic.companyId AND ic.companyName = c.name
      LEFT JOIN mm_User u2 ON ii.individualId = u2.individualId
    WHERE u.individualId = '' AND u2.id IS NULL
    ORDER BY u.id;

UPDATE mm_User u JOIN updateIndividualId uii ON u.id = uii.id
SET u.individualId = uii.individualId;

## without middle Initial checking
DROP TABLE IF EXISTS `updateIndividualId`;
CREATE TABLE `updateIndividualId`
    SELECT ii.individualId, u.id, u.added, u.changed, c.added added2, c.changed changed2, u.firstName, u.lastName,
      u.middleInitial, ua.address, u.email, c.name
    FROM mm_User u
      LEFT JOIN mm_UserAddress ua ON u.id = ua.userId AND ua.type = 'mailing'
      LEFT JOIN importIndividual ii ON u.firstName = ii.firstName AND u.lastName = ii.lastName AND
                                       ua.address = ii.mailingAddress1 AND IFNULL(u.email, '') = ii.emailAddress
      LEFT JOIN mm_Company c ON c.id = u.companyId
      LEFT JOIN (
                  SELECT companyId, companyName FROM importCompaniesAffiliate
                  UNION SELECT companyId, companyName FROM importCompaniesNonMember
                  UNION SELECT companyId, companyName FROM importCompaniesVendor
                  UNION SELECT companyId, companyName FROM importCompaniesAssociate
                  UNION SELECT companyId, companyName FROM importCompaniesRegular
                ) AS ic ON ii.companyId = ic.companyId AND ic.companyName = c.name
      LEFT JOIN mm_User u2 ON ii.individualId = u2.individualId
    WHERE u.individualId = '' AND u2.id IS NULL
    ORDER BY u.id;

UPDATE mm_User u JOIN updateIndividualId uii ON u.id = uii.id
SET u.individualId = uii.individualId;

## without email checking
DROP TABLE IF EXISTS `updateIndividualId`;
CREATE TABLE `updateIndividualId`
    SELECT ii.individualId, u.id, u.added, u.changed, c.added added2, c.changed changed2, u.firstName, u.lastName,
      u.middleInitial, ua.address, u.email, c.name
    FROM mm_User u
      LEFT JOIN mm_UserAddress ua ON u.id = ua.userId AND ua.type = 'mailing'
      LEFT JOIN importIndividual ii ON u.firstName = ii.firstName AND u.lastName = ii.lastName AND
                                       ua.address = ii.mailingAddress1
      LEFT JOIN mm_Company c ON c.id = u.companyId
      LEFT JOIN (
                  SELECT companyId, companyName FROM importCompaniesAffiliate
                  UNION SELECT companyId, companyName FROM importCompaniesNonMember
                  UNION SELECT companyId, companyName FROM importCompaniesVendor
                  UNION SELECT companyId, companyName FROM importCompaniesAssociate
                  UNION SELECT companyId, companyName FROM importCompaniesRegular
                ) AS ic ON ii.companyId = ic.companyId AND ic.companyName = c.name
      LEFT JOIN mm_User u2 ON ii.individualId = u2.individualId
    WHERE u.individualId = '' AND u2.id IS NULL
    ORDER BY u.id;

UPDATE mm_User u JOIN updateIndividualId uii ON u.id = uii.id
SET u.individualId = uii.individualId;

## without mailing address checking
DROP TABLE IF EXISTS `updateIndividualId`;
CREATE TABLE `updateIndividualId`
    SELECT ii.individualId, u.id, u.added, u.changed, c.added added2, c.changed changed2, u.firstName, u.lastName,
      u.middleInitial, ua.address, u.email, c.name
    FROM mm_User u
      LEFT JOIN mm_UserAddress ua ON u.id = ua.userId AND ua.type = 'mailing'
      LEFT JOIN importIndividual ii ON u.firstName = ii.firstName AND u.lastName = ii.lastName AND
                                       IFNULL(u.email, '') = ii.emailAddress
      LEFT JOIN mm_Company c ON c.id = u.companyId
      LEFT JOIN (
                  SELECT companyId, companyName FROM importCompaniesAffiliate
                  UNION SELECT companyId, companyName FROM importCompaniesNonMember
                  UNION SELECT companyId, companyName FROM importCompaniesVendor
                  UNION SELECT companyId, companyName FROM importCompaniesAssociate
                  UNION SELECT companyId, companyName FROM importCompaniesRegular
                ) AS ic ON ii.companyId = ic.companyId AND ic.companyName = c.name
      LEFT JOIN mm_User u2 ON ii.individualId = u2.individualId
    WHERE u.individualId = '' AND u2.id IS NULL
    ORDER BY u.id;

UPDATE mm_User u JOIN updateIndividualId uii ON u.id = uii.id
SET u.individualId = uii.individualId;

## without both - email and  mailing address checking
DROP TABLE IF EXISTS `updateIndividualId`;
CREATE TABLE `updateIndividualId`
    SELECT ii.individualId, u.id, u.added, u.changed, c.added added2, c.changed changed2, u.firstName, u.lastName,
      u.middleInitial, ua.address, u.email, c.name
    FROM mm_User u
      LEFT JOIN mm_UserAddress ua ON u.id = ua.userId AND ua.type = 'mailing'
      LEFT JOIN importIndividual ii ON u.firstName = ii.firstName AND u.lastName = ii.lastName
      LEFT JOIN mm_Company c ON c.id = u.companyId
      LEFT JOIN (
                  SELECT companyId, companyName FROM importCompaniesAffiliate
                  UNION SELECT companyId, companyName FROM importCompaniesNonMember
                  UNION SELECT companyId, companyName FROM importCompaniesVendor
                  UNION SELECT companyId, companyName FROM importCompaniesAssociate
                  UNION SELECT companyId, companyName FROM importCompaniesRegular
                ) AS ic ON ii.companyId = ic.companyId AND ic.companyName = c.name
      LEFT JOIN mm_User u2 ON ii.individualId = u2.individualId
    WHERE u.individualId = '' AND u2.id IS NULL
    ORDER BY u.id;

UPDATE mm_User u JOIN updateIndividualId uii ON u.id = uii.id
SET u.individualId = uii.individualId;

## nickname instead of firstname checking
DROP TABLE IF EXISTS `updateIndividualId`;
CREATE TABLE `updateIndividualId`
    SELECT ii.individualId, u.id, u.added, u.changed, c.added added2, c.changed changed2, u.firstName, u.lastName,
      u.middleInitial, ua.address, u.email, c.name
    FROM mm_User u
      LEFT JOIN mm_UserAddress ua ON u.id = ua.userId AND ua.type = 'mailing'
      LEFT JOIN importIndividual ii ON u.nickname = ii.nickname AND u.lastName = ii.lastName
      LEFT JOIN mm_Company c ON c.id = u.companyId
      LEFT JOIN (
                  SELECT companyId, companyName FROM importCompaniesAffiliate
                  UNION SELECT companyId, companyName FROM importCompaniesNonMember
                  UNION SELECT companyId, companyName FROM importCompaniesVendor
                  UNION SELECT companyId, companyName FROM importCompaniesAssociate
                  UNION SELECT companyId, companyName FROM importCompaniesRegular
                ) AS ic ON ii.companyId = ic.companyId AND ic.companyName = c.name
      LEFT JOIN mm_User u2 ON ii.individualId = u2.individualId
    WHERE u.individualId = '' AND u2.id IS NULL
    ORDER BY u.id;

UPDATE mm_User u JOIN updateIndividualId uii ON u.id = uii.id
SET u.individualId = uii.individualId;

## email-company only checking
DROP TABLE IF EXISTS `updateIndividualId`;
CREATE TABLE `updateIndividualId`
    SELECT ii.individualId, u.id, u.added, u.changed, c.added added2, c.changed changed2, u.firstName, u.lastName,
      u.middleInitial, ua.address, u.email, c.name
    FROM mm_User u
      LEFT JOIN mm_UserAddress ua ON u.id = ua.userId AND ua.type = 'mailing'
      LEFT JOIN importIndividual ii ON u.email = ii.emailAddress
      LEFT JOIN mm_Company c ON c.id = u.companyId
      LEFT JOIN (
                  SELECT companyId, companyName FROM importCompaniesAffiliate
                  UNION SELECT companyId, companyName FROM importCompaniesNonMember
                  UNION SELECT companyId, companyName FROM importCompaniesVendor
                  UNION SELECT companyId, companyName FROM importCompaniesAssociate
                  UNION SELECT companyId, companyName FROM importCompaniesRegular
                ) AS ic ON ii.companyId = ic.companyId AND ic.companyName = c.name
      LEFT JOIN mm_User u2 ON ii.individualId = u2.individualId
    WHERE u.individualId = '' AND u2.id IS NULL
    ORDER BY u.id;

UPDATE mm_User u JOIN updateIndividualId uii ON u.id = uii.id
SET u.individualId = uii.individualId;

## firstName/lastName -company only checking
DROP TABLE IF EXISTS `updateIndividualId`;
CREATE TABLE `updateIndividualId`
    SELECT ii.individualId, u.id, u.added, u.changed, c.added added2, c.changed changed2, u.firstName, u.lastName,
      u.middleInitial, ua.address, u.email, c.name
    FROM mm_User u
      LEFT JOIN mm_UserAddress ua ON u.id = ua.userId AND ua.type = 'mailing'
      LEFT JOIN importIndividual ii ON (u.firstName= ii.firstName) OR (u.lastName =  ii.lastName)
      INNER JOIN mm_Company c ON c.id = u.companyId
      INNER JOIN (
                  SELECT companyId, companyName FROM importCompaniesAffiliate
                  UNION SELECT companyId, companyName FROM importCompaniesNonMember
                  UNION SELECT companyId, companyName FROM importCompaniesVendor
                  UNION SELECT companyId, companyName FROM importCompaniesAssociate
                  UNION SELECT companyId, companyName FROM importCompaniesRegular
                ) AS ic ON ii.companyId = ic.companyId AND ic.companyName = c.name
      LEFT JOIN mm_User u2 ON ii.individualId = u2.individualId
    WHERE u.individualId = '' AND u2.id IS NULL
    ORDER BY u.id;

UPDATE mm_User u JOIN updateIndividualId uii ON u.id = uii.id
SET u.individualId = uii.individualId;

CREATE TEMPORARY TABLE userIndividualId AS
SELECT individualId, COUNT(id) _total
  FROM mm_User
    WHERE individualId != ''
  GROUP BY individualId
  HAVING _total > 1;

SELECT u.id, firstName, lastName, jobTitle, c.name, individualId
  FROM mm_User u
    LEFT JOIN mm_Company c ON u.companyId = c.id
  WHERE individualId IN (SELECT individualId FROM userIndividualId);