--liquibase formatted sql

--changeset mark.gidman:c9c903ea-f59b-4321-bae6-98285f08d642
CREATE TABLE account (
	id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	name varchar(200) NOT NULL DEFAULT '',
	parent_id int(11) UNSIGNED DEFAULT NULL,
	rlms_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),
	KEY fk_account_parent_id (parent_id),
	INDEX ix_account_rlms_id (rlms_id),
	CONSTRAINT fk_account_parent_id FOREIGN KEY (parent_id) REFERENCES account (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Allows us to group users and isolate business entities.';
--rollback drop table account;


--changeset mark.gidman:0ec84df4-b0c4-4732-b998-d7a0eafc6bdf
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,
	account_id int(11) UNSIGNED NOT NULL,
	account_role tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
	PRIMARY KEY (id),
	UNIQUE KEY uniq_username (username),
	KEY fk_user_account_id (account_id),
	CONSTRAINT fk_user_account_id FOREIGN KEY (account_id) REFERENCES account (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Provides support for local authentication.';
--rollback drop table user;


--changeset mark.gidman:e4cd684d-1085-47dc-a811-0e4bc23af4cf

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

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

/* Set up SRC Corporate account and user */
INSERT INTO account(id, name, parent_id, created_by, updated_by)
VALUES (2, 'SRC Corporate', null, 1, 1);

INSERT INTO user(id, username, password_hash, created_by, updated_by, account_id, account_role)
VALUES (2, 'srcsuper', '$2a$10$DO4DkJzIAhGI3NBVp/J0OOIib/nn1E7m405SnCINM/zSBd9fLLu7O', 1, 1, 2, 255);

/* Set up SRC Logistics account and user */
INSERT INTO account(id, name, parent_id, created_by, updated_by)
VALUES (3, 'SRC Logistics', 2, 1, 1);

INSERT INTO user(id, username, password_hash, created_by, updated_by, account_id, account_role)
VALUES (3, 'srclogadmin', '$2a$10$D4wFLawWJrGFM6/2clqtLOIOfZR./qCRP/dZsE6m8e8uewc.haBhC', 1, 1, 3, 120);

INSERT INTO user(id, username, password_hash, created_by, updated_by, account_id, account_role)
VALUES (4, 'srcloguser', '$2a$10$7Tb3JMs0uamNpCsJ2qeEw.imSotOUkUOugdZXT32dHqWF5lzd3Uzy', 1, 1, 3, 60);


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