SET CHARACTER_SET_CLIENT=utf8;
SET CHARACTER_SET_RESULTS=utf8;
SET COLLATION_DATABASE=utf8mb4_general_ci;
SET COLLATION_SERVER=utf8mb4_general_ci;
SET NAMES utf8;

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `ma_Naics`;

CREATE TABLE `ma_Naics` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `code` int(10) unsigned NOT NULL,
 `title` varchar(255) NOT NULL,
 `t` enum('n','y') DEFAULT 'n',
 `isCategory` enum('n','y') DEFAULT 'n',
 `added` datetime DEFAULT NULL,
 `changed` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA LOCAL INFILE 'NAICS_Structure(16163).csv' INTO TABLE `ma_Naics`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@dummy, `code`, @title, @isCategory)
SET
  `isCategory` = IF(@isCategory = 'y', 'y', 'n'),
  `title` = IF(BINARY RIGHT((@title:=TRIM(@title)), 1) = 'T', SUBSTRING(@title, 1, CHAR_LENGTH(@title) - 1), @title),
  `t` = IF(BINARY RIGHT(@title, 1) = 'T', 'y', 'n'),
  `added` = NOW(),
  `changed` = NOW()
;

DELETE FROM `ma_Naics` WHERE `code` = 0;

-- SELECT * FROM `ma_VendorService` ORDER BY RPAD(`code`, 6, '0');