DROP TABLE IF EXISTS `company`;
DROP TABLE IF EXISTS `issuercompany`;

CREATE TABLE IF NOT EXISTS `company` (
  `companyID` INT NOT NULL AUTO_INCREMENT,
  `companyName` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`companyID`));

CREATE TABLE IF NOT EXISTS `issuercompany` (
  `companyID` INT NOT NULL,
  `issuerID` INT NOT NULL,
  UNIQUE INDEX `issuerID_UNIQUE` (`issuerID` ASC));

INSERT INTO `company` (`companyName`) VALUES ('Let\'s Encrypt');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Let\'s Encrypt' WHERE `commonName` LIKE '%Let\'s Encrypt%';

INSERT INTO `company` (`companyName`) VALUES ('Comodo');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%COMODO%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%PositiveSSL%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%GlobalTrust%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%GlobeSSL%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%TrustAsia%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%TBS X509%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%InterContinental Hotels Group%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%USERTrust%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%SSL.com%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%McAfee%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%SecureCore%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%USERFirst%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%AAA Certificate%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%icewarp%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Crazy Domains%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%DOMENY%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%K Software%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%ReliaSite%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%BlackCert%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Western Digital%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%SignSec%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%InCommon%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Gandi%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Digi-Sign%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Terena%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%TrustSign%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%EssentialSSL%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'COMODO' WHERE `commonName` LIKE '%Trusted Secure Certificate Authority%';

INSERT INTO `company` (`companyName`) VALUES ('DigiCert');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'DigiCert' WHERE `commonName` LIKE '%DigiCert%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'DigiCert' WHERE `commonName` LIKE 'Cybertrust%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'DigiCert' WHERE `commonName` LIKE '%GTE Cybertrust%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'DigiCert' WHERE `commonName` LIKE '%Baltimore Cybertrust%';

INSERT INTO `company` (`companyName`) VALUES ('Entrust');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Entrust' WHERE `commonName` LIKE '%Entrust%';
-- http://www.businesswire.com/news/home/20160322005639/en/Entrust-Datacard-Trend-Micro-Partner-Provide-Comprehensive
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Entrust' WHERE `commonName` LIKE '%Trend Micro%';

INSERT INTO `company` (`companyName`) VALUES ('Symantec');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%Symantec%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%GeoTrust%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%AlphaSSL%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%RapidSSL%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%VeriSign%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Symantec' WHERE `commonName` LIKE '%Thawte%';

INSERT INTO `company` (`companyName`) VALUES ('GlobalSign');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'GlobalSign' WHERE `commonName` LIKE '%GlobalSign%';

INSERT INTO `company` (`companyName`) VALUES ('GoDaddy');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'GoDaddy' WHERE `commonName` LIKE '%GoDaddy%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'GoDaddy' WHERE `commonName` LIKE '%Go Daddy%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'GoDaddy' WHERE `commonName` LIKE '%Starfield%';

INSERT INTO `company` (`companyName`) VALUES ('StartSSL');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'StartSSL' WHERE `commonName` LIKE '%StartCom%';

INSERT INTO `company` (`companyName`) VALUES ('WoSign');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'WoSign' WHERE `commonName` LIKE '%WoSign%';
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'WoSign' WHERE `commonName` LIKE '%沃通免费%';

INSERT INTO `company` (`companyName`) VALUES ('Network Solutions');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Network Solutions' WHERE `commonName` LIKE '%Network Solutions%';

INSERT INTO `company` (`companyName`) VALUES ('QuoVadis');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'QuoVadis' WHERE `commonName` LIKE '%QuoVadis%';

INSERT INTO `company` (`companyName`) VALUES ('Microsoft');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Microsoft' WHERE `commonName` LIKE '%Microsoft%';

INSERT INTO `company` (`companyName`) VALUES ('Google');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Google' WHERE `commonName` LIKE '%Google%';

INSERT INTO `company` (`companyName`) VALUES ('TrustWave');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'TrustWave' WHERE `commonName` LIKE '%TrustWave%';

INSERT INTO `company` (`companyName`) VALUES ('Verizon');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Verizon' WHERE `commonName` LIKE '%Verizon%';

INSERT INTO `company` (`companyName`) VALUES ('Amazon');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'Amazon' WHERE `commonName` LIKE '%Amazon%';

INSERT INTO `company` (`companyName`) VALUES ('US DOD');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'US DOD' WHERE `commonName` LIKE '%DOD%';

INSERT INTO `company` (`companyName`) VALUES ('SECOM');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'SECOM' WHERE `commonName` LIKE '%SECOM%';

INSERT INTO `company` (`companyName`) VALUES ('TWCA');
INSERT INTO `issuercompany` (`issuerID`, `companyID`) SELECT issuerID, companyID FROM `issuer` JOIN `company` ON `companyName` = 'TWCA' WHERE `commonName` LIKE '%TWCA%';