--liquibase formatted sql

--changeset mark.gidman:c1ba6567-5a30-4684-a67f-af07b351ffd2
drop table user;
drop table account;
drop table api_token;
--rollback select 1=1;


--changeset mark.gidman:f6c7fe60-c07c-4925-b481-a88861364e03
CREATE TABLE organization (
	id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	name varchar(200) NOT NULL DEFAULT '',
	external_id varchar(200) DEFAULT NULL,
	created_on datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_on datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	created_by int(11) UNSIGNED NOT NULL,
	updated_by int(11) UNSIGNED NOT NULL,
	is_deleted tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
	PRIMARY KEY (id),
	INDEX ix_organization_external_id (external_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Allows us to group users and isolate business entities.';
--rollback drop table organization;


--changeset mark.gidman:4f0f37cf-89eb-455d-ad24-d3cd5e160616
CREATE TABLE user (
	id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	username varchar(255) NOT NULL DEFAULT '',
	password_hash char(60) NOT NULL,
	created_on datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_on datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	created_by int(11) UNSIGNED NOT NULL,
	updated_by int(11) UNSIGNED NOT NULL,
	is_deleted tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
	organization_id int(11) UNSIGNED NOT NULL,
	organization_role tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
	PRIMARY KEY (id),
	UNIQUE KEY uniq_username (username),
	KEY fk_user_organization_id (organization_id),
	CONSTRAINT fk_user_organization_id FOREIGN KEY (organization_id) REFERENCES organization (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Provides support for local authentication.';
--rollback drop table user;


--changeset mark.gidman:ad40b74a-844e-4f9f-9437-5a03b639ba56
CREATE TABLE api_token (
	id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	name varchar(200) NOT NULL DEFAULT '',
	token varchar(1200) NOT NULL DEFAULT '',
	created_on datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_on datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	created_by int(11) UNSIGNED NOT NULL,
	updated_by int(11) UNSIGNED NOT NULL,
	is_deleted tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
	organization_id int(11) UNSIGNED NOT NULL,
	organization_role tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
	PRIMARY KEY (id),
	KEY fk_api_token_organization_id (organization_id),
	CONSTRAINT fk_api_token_organization_id FOREIGN KEY (organization_id) REFERENCES organization (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Provides support for system authentication.';
--rollback drop table api_token;


--changeset mark.gidman:4f0f92cf-89eb-455d-ad24-d3cd5e160616

/* Set up system organization and user */
INSERT INTO organization(id, external_id, name, created_by, updated_by)
VALUES (1, null, 'system', 1, 1);

INSERT INTO user(id, username, password_hash, created_by, updated_by, organization_id, organization_role)
VALUES (1, 'system', '$2a$10$P6Ts6fNRLuETtunh9rX3GONnJoyl3Iaj2yLcwtqNB9wvKrhIr3/1a', 1, 1, 1, 255);

/* Set up the other accounts we know about right now */
INSERT INTO organization(id, external_id, name, created_by, updated_by)
VALUES (2, 'AW00021167', 'ARROWHEAD CONTAINERS', 1, 1),
	(3, 'AW00021149', 'BMP', 1, 1),
	(4, 'AW00021086', 'BOBCAT', 1, 1),
	(5, 'AW00021170', 'BOULDER TECH', 1, 1),
	(6, 'AW00021155', 'BRICTOWN TECH DBA RESULTS', 1, 1),
	(7, 'AW00021172', 'BURNS Equipment', 1, 1),
	(8, 'AW00021179', 'CERAMEX', 1, 1),
	(9, 'AW00021025', 'CNH COREVIEW', 1, 1),
	(10, 'AW00021125', 'CNHR', 1, 1),
	(11, 'AW00021102', 'CONCURRENT', 1, 1),
	(12, 'AW00021152', 'CONEQUIP', 1, 1),
	(13, 'AW00021071', 'COOK TRACTOR', 1, 1),
	(14, 'AW00021161', 'CORLEY\'S PARTS LLC', 1, 1),
	(15, 'AW00021108', 'CSI DESIGNS (CENTRAL STATES INDUSTRIAL)', 1, 1),
	(16, 'AW00021175', 'DOOSAN AMERICA', 1, 1),
	(17, 'AW00021160', 'GLITTER GEAR', 1, 1),
	(18, 'AW00021051', 'GLOBAL RECOVERY (GRC)', 1, 1),
	(19, 'AW00021045', 'H & R CONSTRUCTION', 1, 1),
	(20, 'AW00021146', 'HIGH TECH INDUSTRIES', 1, 1),
	(21, 'AW00021015', 'IDEA COMPANY', 1, 1),
	(22, 'AW00021150', 'IN MOTION', 1, 1),
	(23, 'AW00021064', 'INDUSTRIAL SURPLUS SUPPLY', 1, 1),
	(24, 'AW00021158', 'INTERMOUNTIAN MACHINERY', 1, 1),
	(25, 'AW00021090', 'ISUZU', 1, 1),
	(26, 'AW00021171', 'JAVA FARM SUPPLY', 1, 1),
	(27, 'AW00021156', 'MGI Transport', 1, 1),
	(28, 'AW00021129', 'N & S TRACTOR', 1, 1),
	(29, 'AW00021169', 'NAVISTAR LTL', 1, 1),
	(30, 'AW00021093', 'PINEAPPLE HOSPITALITY', 1, 1),
	(31, 'AW00021105', 'PROMOTER ADVERTISING', 1, 1),
	(32, 'AW00021101', 'RESULTS COMPUTER', 1, 1),
	(33, 'AW00021022', 'RIDEWELL SUSPENSIONS', 1, 1),
	(34, 'AW00021173', 'ROOK SYSTEMS GROUPS LLC', 1, 1),
	(35, 'AW00021074', 'SOUTHERN MISSOURI CONTAINER', 1, 1),
	(36, 'AW00021082', 'SRC AUTOMOTIVE', 1, 1),
	(37, 'AW00021085', 'SRC ELECTRICAL', 1, 1),
	(38, 'AW00021018', 'SRC HEAVY DUTY', 1, 1),
	(39, 'AW00021049', 'SRC LEXINGTON', 1, 1),
	(40, 'AW00021087', 'SRC POWER SYSTEMS', 1, 1),
	(41, 'AW00021091', 'WESTERN OVERSEAS', 1, 1),
	(42, 'AW00021121', 'YANMAR', 1, 1),
	(43, 'AW00021164', 'YANMAR AMERICA PARTS DIVISION', 1, 1);


INSERT INTO user(username, password_hash, organization_id, created_by, updated_by, organization_role)
VALUES ('testAW00021167', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 2, 1, 1, 120)
,('testAW00021149', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 3, 1, 1, 120)
,('testAW00021086', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 4, 1, 1, 120)
,('testAW00021170', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 5, 1, 1, 120)
,('testAW00021155', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 6, 1, 1, 120)
,('testAW00021172', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 7, 1, 1, 120)
,('testAW00021179', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 8, 1, 1, 120)
,('testAW00021025', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 9, 1, 1, 120)
,('testAW00021125', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 10, 1, 1, 120)
,('testAW00021102', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 11, 1, 1, 120)
,('testAW00021152', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 12, 1, 1, 120)
,('testAW00021071', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 13, 1, 1, 120)
,('testAW00021161', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 14, 1, 1, 120)
,('testAW00021108', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 15, 1, 1, 120)
,('testAW00021175', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 16, 1, 1, 120)
,('testAW00021160', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 17, 1, 1, 120)
,('testAW00021051', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 18, 1, 1, 120)
,('testAW00021045', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 19, 1, 1, 120)
,('testAW00021146', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 20, 1, 1, 120)
,('testAW00021015', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 21, 1, 1, 120)
,('testAW00021150', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 22, 1, 1, 120)
,('testAW00021064', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 23, 1, 1, 120)
,('testAW00021158', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 24, 1, 1, 120)
,('testAW00021090', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 25, 1, 1, 120)
,('testAW00021171', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 26, 1, 1, 120)
,('testAW00021156', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 27, 1, 1, 120)
,('testAW00021129', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 28, 1, 1, 120)
,('testAW00021169', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 29, 1, 1, 120)
,('testAW00021093', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 30, 1, 1, 120)
,('testAW00021105', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 31, 1, 1, 120)
,('testAW00021101', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 32, 1, 1, 120)
,('testAW00021022', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 33, 1, 1, 120)
,('testAW00021173', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 34, 1, 1, 120)
,('testAW00021074', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 35, 1, 1, 120)
,('testAW00021082', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 36, 1, 1, 120)
,('testAW00021085', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 37, 1, 1, 120)
,('testAW00021018', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 38, 1, 1, 120)
,('testAW00021049', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 39, 1, 1, 120)
,('testAW00021087', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 40, 1, 1, 120)
,('testAW00021091', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 41, 1, 1, 120)
,('testAW00021121', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 42, 1, 1, 120)
,('testAW00021164', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 43, 1, 1, 120);


--rollback delete from user where 1=1; delete from organization where 1=1;

--changeset damon.overboe:src-as-customer-44
	SET FOREIGN_KEY_CHECKS=0;

	INSERT INTO organization(id, external_id, name, created_by, updated_by)
	VALUES (44, 'AW00021001', 'SRC LOGISTICS', 1, 1);

	INSERT INTO user(username, password_hash, organization_id, created_by, updated_by, organization_role)
	VALUES ('testAW00021001', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 44, 1, 1, 120);

	SET FOREIGN_KEY_CHECKS=1;
-- rollback DELETE FROM user WHERE id=44; DELETE FROM organization WHERE id=44;

--changeset damon.overboe:add-customers-20170721
	SET FOREIGN_KEY_CHECKS=0;

	INSERT INTO `organization` (id,external_id,name,created_by,updated_by) VALUES
	(45, 'AW00021188', 'CLEAN DIESEL TECHNOLOGIES, INC.', 1, 1),
	(46, 'AW00021145', 'BOBCAT SURPLUS RETURNS', 1, 1),
	(47, 'AW00021144', 'BOBCAT CONTAINER RETURNS', 1, 1);

        INSERT INTO `user` (username, password_hash, organization_id, created_by, updated_by, organization_role) VALUES
	('testAW00021188', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 45, 1, 1, 120),
	('testAW00021145', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 46, 1, 1, 120),
	('testAW00021144', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 47, 1, 1, 120);

	SET FOREIGN_KEY_CHECKS=1;
--rollback DELETE FROM `organization` WHERE id > 44 AND id < 48; DELETE FROM `user` WHERE id > 44 AND id < 48;

--changeset damon.overboe:add-agco-customer-20170724
	SET FOREIGN_KEY_CHECKS=0;

	INSERT INTO `organization` (id,external_id,name,created_by,updated_by) VALUES
	(48, 'AW00021180', 'AGCO', 1, 1);

        INSERT INTO `user` (username, password_hash, organization_id, created_by, updated_by, organization_role) VALUES
	('testAW00021180', '$2a$10$K8yScgRurG5hQ3d41Fx91On.Bo1o5Gl12qybDXv53o1ARCcZqi4Zm', 48, 1, 1, 120);

	SET FOREIGN_KEY_CHECKS=1;
--rollback DELETE FROM `organization` WHERE id > 44 AND id < 48; DELETE FROM `user` WHERE id > 44 AND id < 48;

--changeset lewis.stone:add-qb-bindings-to-organization
ALTER TABLE `organization`
ADD COLUMN `expense_account` VARCHAR(40) NOT NULL DEFAULT '6001300 - Transportation COGS' AFTER `is_deleted`,
ADD COLUMN `ap_account` VARCHAR(40) NOT NULL DEFAULT '2121100 - AP TRADE' AFTER `expense_account`,
ADD COLUMN `income_account` VARCHAR(40) NOT NULL DEFAULT '5012000 - Transportation Sales' AFTER `ap_account`,
ADD COLUMN `ar_account` VARCHAR(40) NOT NULL DEFAULT '1139100 - AR TRADE' AFTER `income_account`;

--changeset lewis.stone:updating-catch-all-accounts-for-organizations
UPDATE `organization` SET `expense_account` = 'Transportation COGS';
UPDATE `organization` SET `ap_account` = 'AP - Trade';
UPDATE `organization` SET `income_account` = 'Transportation Other Item';
UPDATE `organization` SET `ar_account` = 'A/R - Trade';

--changeset lewis.stone:update-HRConstruction
UPDATE `organization` SET `expense_account`='Transportation COGS', `ap_account`='AP - Trade', `income_account`='Transportation H&R', `ar_account`='A/R - Trade' WHERE `id`='19';

--changeset lewis.stone:change-name-from-income-account-to-income-item-also-update-defaults
ALTER TABLE `organization`
CHANGE COLUMN `expense_account` `expense_account` VARCHAR(40) NOT NULL DEFAULT 'Transportation COGS' ,
CHANGE COLUMN `ap_account` `ap_account` VARCHAR(40) NOT NULL DEFAULT 'AP - Trade' ,
CHANGE COLUMN `income_account` `income_item` VARCHAR(40) NOT NULL DEFAULT 'Transportation Other Item' ,
CHANGE COLUMN `ar_account` `ar_account` VARCHAR(40) NOT NULL DEFAULT 'A/R - Trade' ;

--changeset lewis.stone:append-Parts-to-HR-Construction
UPDATE `organization` SET `name`='H & R CONSTRUCTION PARTS' WHERE `id`='19';
