--liquibase formatted sql

--changeset lewis.stone:60bb3c1f-32e3-4bdd-a30d-f92beb87171a
CREATE TABLE `bill` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(500) NOT NULL,
  `is_deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`)
);
--rollback drop table bills;

--changeset lewis.stone:f2f91790-35bc-48a6-a50e-066b87db7428
CREATE TABLE `procurement` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `actual_total` decimal(15,2) NOT NULL,
  `bill_id` int(11) NOT NULL,
  `is_deleted` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`)
);
--rollback drop table procurement_items;

--changeset lewis.stone:ff8fd2d3-e76c-4969-aa4f-b8a5a66cd7aa
ALTER TABLE `procurement`
CHANGE COLUMN `name` `sw_number` VARCHAR(200) NULL DEFAULT NULL ,
CHANGE COLUMN `bill_id` `invoice_id` VARCHAR(200) NOT NULL ,
ADD UNIQUE INDEX `sw_number_UNIQUE` (`sw_number` ASC);

--changeset lewis.stone:ff8fd2d3-e76c-4969-aa4f-b8a5a66cd7ab
ALTER TABLE `procurement`
ADD COLUMN `deferred` BIT(1) NOT NULL DEFAULT 0 AFTER `is_deleted`;

--changeset lewis.stone:c4096ebd-9fbd-4c7a-a277-f82524254f99
drop table if exists bill

--changeset lewis.stone:381ab26c-9e5f-498b-aeb9-898a592e759c
CREATE TABLE `invoice_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL DEFAULT '(other)',
  `amount` DECIMAL(15,2) NOT NULL,
  `invoice_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_invoice_idx` (`invoice_id` ASC),
  CONSTRAINT `fk_items_invoice`
    FOREIGN KEY (`invoice_id`)
    REFERENCES `procurement` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `accounts_receivable` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` INT(10) UNSIGNED NOT NULL,
  `markup` DECIMAL(15,2) NOT NULL DEFAULT 0,
  `is_in_quickbooks` BIT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `invoice_idx` (`invoice_id` ASC),
  CONSTRAINT `fk_ar_invoice`
    FOREIGN KEY (`invoice_id`)
    REFERENCES `procurement` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `accounts_payable` (
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`invoice_id` INT(10) UNSIGNED NOT NULL,
	`is_in_quickbooks` BIT(1) NOT NULL DEFAULT 0,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `id_UNIQUE` (`id` ASC),
	INDEX `fk_ap_invoice_idx` (`invoice_id` ASC),
	CONSTRAINT `fk_ap_invoice`
		FOREIGN KEY (`invoice_id`)
		REFERENCES `procurement` (`id`)
		ON DELETE NO ACTION
		ON UPDATE NO ACTION);

--change-set lewis.stone:3fc4f76b-4fbc-4c2f-82ab-442456fb13d5
RENAME TABLE `procurement` TO `invoices`;
ALTER TABLE `invoices` CHANGE `invoice_id` `invoice_number` VARCHAR(200);
ALTER TABLE `invoices` CHANGE `deferred` `is_deferred` BIT(1);

--change-set lewis.stone:6adbba35-a92a-4600-bfd1-6e0299b0b3db
ALTER TABLE `accounts_receivable` DROP COLUMN `markup`;
ALTER TABLE `invoices` ADD COLUMN `actual_mark_up_total` DECIMAL(15,2) NOT NULL DEFAULT 0 AFTER `is_deferred`;
ALTER TABLE `invoices` CHANGE COLUMN `is_deferred` `is_deferred` BIT(1) NULL DEFAULT 0 ;




--changeset damon.overboe:alter-invoice-items1
ALTER TABLE `invoice_items`
	DROP COLUMN `amount`,
	ADD COLUMN `actual_ar` DECIMAL(15,2) NOT NULL,
	ADD COLUMN `actual_ap` DECIMAL(15,2) NOT NULL,
	ADD COLUMN `code` VARCHAR(100) NOT NULL
;
--rollback
ALTER TABLE `invoice_items`
	DROP COLUMN `actual_ar`,
	DROP COLUMN `actual_ap`,
	DROP COLUMN `code`,
	ADD COLUMN `amount`DECIMAL(15,2) NOT NULL

--changeset lewis.stone:add-invoice-items1-again
ALTER TABLE `invoice_items`
	DROP COLUMN `amount`,
	ADD COLUMN `actual_ar` DECIMAL(15,2) NOT NULL,
	ADD COLUMN `actual_ap` DECIMAL(15,2) NOT NULL,
	ADD COLUMN `code` VARCHAR(100) NOT NULL;

--changeset lewis.stone:add-pro-number-to-invoice-table
ALTER TABLE `invoices`
	ADD COLUMN `pro_number` VARCHAR(200) NULL AFTER `actual_mark_up_total`;

--changeset lewis.stone:updating-invoices-table-for-deferring
ALTER TABLE `invoices`
	CHANGE COLUMN `actual_mark_up_total` `actual_mark_up_total` DECIMAL(15,2) NULL DEFAULT '0.00' ;
ALTER TABLE `invoices`
	ADD COLUMN `expected_ap` DECIMAL(15,2) NULL DEFAULT '0.00' AFTER `pro_number`,
	ADD COLUMN `expected_ar` DECIMAL(15,2) NULL DEFAULT '0.00' AFTER `expected_ap`;

--changeset lewis.stone:changing-column-names-of-invoice-table
ALTER TABLE `invoices`
	CHANGE COLUMN `sw_number` `swNumber` VARCHAR(200) NULL DEFAULT NULL ,
	CHANGE COLUMN `actual_total` `actualAP` DECIMAL(15,2) NOT NULL ,
	CHANGE COLUMN `invoice_number` `invoiceNumber` VARCHAR(200) NULL DEFAULT NULL ,
	CHANGE COLUMN `is_deferred` `deferred` BIT(1) NULL DEFAULT b'0' ,
	CHANGE COLUMN `actual_mark_up_total` `arActual` DECIMAL(15,2) NULL DEFAULT '0.00' ,
	CHANGE COLUMN `pro_number` `proNumber` VARCHAR(200) NULL DEFAULT NULL ,
	CHANGE COLUMN `expected_ap` `expectedAP` DECIMAL(15,2) NULL DEFAULT '0.00' ,
	CHANGE COLUMN `expected_ar` `expectedAR` DECIMAL(15,2) NULL DEFAULT '0.00' ;

--changeset lewis.stone:adding-override-flag
ALTER TABLE `invoices`
	ADD COLUMN `override` BIT(1) NOT NULL DEFAULT 0 AFTER `expectedAR`;

--changeset jesse.skelton:changing-column-names-of-invoiceitems-table
ALTER TABLE `invoice_items`
	ADD COLUMN `expected_ap` DECIMAL(15,2) NULL DEFAULT '0.00' AFTER `code`,
	ADD COLUMN `expected_ar` DECIMAL(15,2) NULL DEFAULT '0.00' AFTER `expected_ap`;

--changeset jesse.skelton:adding-column-service-code
ALTER TABLE `invoices`
	ADD COLUMN `service_code` VARCHAR(100) NULL AFTER `expectedAR`

--changeset lewis.stone:adding-datetime-column-to-AR-AP-tables
ALTER TABLE `accounts_receivable`
	ADD COLUMN `last_accessed_datetime` DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00' AFTER `is_in_quickbooks`;
ALTER TABLE `accounts_payable`
	ADD COLUMN `last_accessed_datetime` DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00' AFTER `is_in_quickbooks`;

--changeset lewis.stone:update-table-accounts-receivable-to-accounts
RENAME TABLE `accounts_receivable` TO `accounts`;
ALTER TABLE `accounts`
	ADD COLUMN `type` VARCHAR(2) NOT NULL AFTER `last_accessed_datetime`;

--changeset lewis.stone:delete-table-accounts-payable
drop table if exists accounts_payable

--changeset jesse.skelton:alter-column-organization-id
ALTER TABLE `invoices`
	ADD COLUMN `organizationId` VARCHAR(100) NOT NULL DEFAULT 44 AFTER `override`

--changeset jesse.skelton:adding-column-organization-id
ALTER TABLE `invoices`
	CHANGE COLUMN `organizationId` `organizationId` VARCHAR(100) NOT NULL AFTER `override`

--changeset jesse.skelton:add-columns-for-summarize-items-set-defaults
ALTER TABLE `invoices`
	ADD COLUMN `totalContainers` VARCHAR(100) NOT NULL DEFAULT 44 AFTER `organizationId`;
ALTER TABLE `invoices`
	ADD COLUMN `totalWeight` VARCHAR(100) NOT NULL DEFAULT 515 AFTER `totalContainers`;
ALTER TABLE `invoices`
	ADD COLUMN `class` VARCHAR(100) NOT NULL DEFAULT 85 AFTER `totalWeight`;
ALTER TABLE `invoices`
	ADD COLUMN `nmfc` VARCHAR(100) NOT NULL DEFAULT '555555-5' AFTER `class`;
ALTER TABLE `invoices`
	ADD COLUMN `cubicFeet` VARCHAR(100) NOT NULL DEFAULT 55555 AFTER `nmfc`;

--changeset jesse.skelton:add-columns-for-summarize-items
ALTER TABLE `invoices`
	CHANGE COLUMN `totalContainers` `totalContainers` VARCHAR(100) NOT NULL AFTER `organizationId`;
ALTER TABLE `invoices`
	CHANGE COLUMN `totalWeight` `totalWeight` VARCHAR(100) NOT NULL AFTER `totalContainers`;
ALTER TABLE `invoices`
	CHANGE COLUMN `class` `class` VARCHAR(100) NOT NULL AFTER `totalWeight`;
ALTER TABLE `invoices`
	CHANGE COLUMN `nmfc` `nmfc` VARCHAR(100) NOT NULL AFTER `class`;
ALTER TABLE `invoices`
	CHANGE COLUMN `cubicFeet` `cubicFeet` VARCHAR(100) NOT NULL AFTER `nmfc`;

--changeset jesse.skelton:adding-is-processing-column-AR-AP-tables
ALTER TABLE `accounts`
	ADD COLUMN `is_processing` BIT(1) NOT NULL DEFAULT 0 AFTER `last_accessed_datetime`;

--changeset jesse.skelton:add-column-carrier-code
ALTER TABLE `invoices`
	ADD COLUMN `carrier_code` VARCHAR(100) NOT NULL DEFAULT 'FEDEX' AFTER `cubicFeet`;

--changeset lewis.stone:add-manifest-column
ALTER TABLE `invoices`
ADD COLUMN `manifestNumber` VARCHAR(100) NULL AFTER `carrier_code`;

--changeset jesse.skelton:add-qb-log-table
CREATE TABLE `qb_logs` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`account_id` VARCHAR(100) DEFAULT NULL,
	`blob` JSON NOT NULL,
	`status_code` VARCHAR(100) DEFAULT NULL,
	`timestamp` DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00',
	 PRIMARY KEY (`id`)
);

--changeset jesse.skelton:add-column-category-for-invoice-items
ALTER TABLE `invoice_items`
	ADD COLUMN `category` VARCHAR(100) NOT NULL AFTER `expected_ar`;

--changeset jesse.skelton:alter-ap-column-type-for-accounts
ALTER TABLE `accounts`
	CHANGE COLUMN `type` `type` VARCHAR(100) NOT NULL AFTER `is_processing`;

--changeset lewis.stone:adding-date-time-created-for-invoices
ALTER TABLE `invoices`
ADD COLUMN `dateTimeCreated` DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00' AFTER `manifestNumber`;
