SET FOREIGN_KEY_CHECKS = 0;

SET AUTOCOMMIT = 0;
START TRANSACTION;

ALTER TABLE `katao_order` DROP `invoice_date`;
ALTER TABLE `katao_order` DROP `invoice_due_date`;
ALTER TABLE `katao_order` DROP `invoice_reference`;
ALTER TABLE `katao_order` DROP `invoice_payment_mode`;
ALTER TABLE `katao_order` DROP `invoice_payment_details`;
ALTER TABLE `katao_order` DROP `invoice_taux0_bases_ht`;
ALTER TABLE `katao_order` DROP `invoice_taux0_amount`;
ALTER TABLE `katao_order` DROP `invoice_taux1_bases_ht`;
ALTER TABLE `katao_order` DROP `invoice_taux1_amount`;
ALTER TABLE `katao_order` DROP `invoice_taux2_bases_ht`;
ALTER TABLE `katao_order` DROP `invoice_taux2_amount`;
ALTER TABLE `katao_order` DROP `invoice_total_ht`;
ALTER TABLE `katao_order` DROP `invoice_total_tva`;
ALTER TABLE `katao_order` DROP `paid_at`;

ALTER TABLE `katao_order_product` DROP `quantity_invoiced`;
ALTER TABLE `katao_order_product` DROP `has_been_invoiced`;
ALTER TABLE `katao_order_product` DROP `invoice_unit_price`;
ALTER TABLE `katao_order_product` DROP `invoice_total_price`;

CREATE TABLE `katao_supplier_invoice`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`katao_supplier_id` INTEGER  NOT NULL,
	`katao_period_id` INTEGER  NOT NULL,
	`status` INTEGER default 1 NOT NULL,
	`supplier_name` VARCHAR(255)  NOT NULL,
	`supplier_email` VARCHAR(255)  NOT NULL,
	`supplier_address1` VARCHAR(255)  NOT NULL,
	`supplier_address2` VARCHAR(255),
	`supplier_zip` VARCHAR(255),
	`supplier_city` VARCHAR(255)  NOT NULL,
	`katao_name` VARCHAR(255)  NOT NULL,
	`katao_address` TEXT,
	`katao_siret_number` CHAR(14)  NOT NULL,
	`katao_rcs_number` VARCHAR(255)  NOT NULL,
	`katao_capital` INTEGER  NOT NULL,
	`invoice_date` DATETIME  NOT NULL,
	`due_date` DATETIME  NOT NULL,
	`payment_date` DATETIME,
	`reference` VARCHAR(255),
	`payment_mode` INTEGER default 1 NOT NULL,
	`payment_details` VARCHAR(255),
	`taux0_bases_ht` FLOAT default 0,
	`taux0_amount` FLOAT default 0,
	`taux1_bases_ht` FLOAT default 0,
	`taux1_amount` FLOAT default 0,
	`taux2_bases_ht` FLOAT default 0,
	`taux2_amount` FLOAT default 0,
	`total_ht` FLOAT default 0,
	`total_tva` FLOAT default 0,
	`comment` TEXT,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `katao_supplier_invoice_FI_1` (`katao_supplier_id`),
	CONSTRAINT `katao_supplier_invoice_FK_1`
		FOREIGN KEY (`katao_supplier_id`)
		REFERENCES `katao_supplier` (`id`)
		ON DELETE CASCADE,
	INDEX `katao_supplier_invoice_FI_2` (`katao_period_id`),
	CONSTRAINT `katao_supplier_invoice_FK_2`
		FOREIGN KEY (`katao_period_id`)
		REFERENCES `katao_period` (`id`)
		ON DELETE CASCADE
)Engine=InnoDB;

CREATE TABLE `katao_supplier_invoice_product`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`katao_supplier_invoice_id` INTEGER,
	`katao_product_id` INTEGER,
	`product_reference` VARCHAR(255),
	`product_name` VARCHAR(255)  NOT NULL,
	`product_price_euro` FLOAT  NOT NULL,
	`product_margin` FLOAT,
	`product_tva_rate` FLOAT default 0,
	`quantity` INTEGER default 1 NOT NULL,
	`product_price_total` FLOAT default 0,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `katao_supplier_invoice_product_FI_1` (`katao_supplier_invoice_id`),
	CONSTRAINT `katao_supplier_invoice_product_FK_1`
		FOREIGN KEY (`katao_supplier_invoice_id`)
		REFERENCES `katao_supplier_invoice` (`id`)
		ON DELETE CASCADE,
	INDEX `katao_supplier_invoice_product_FI_2` (`katao_product_id`),
	CONSTRAINT `katao_supplier_invoice_product_FK_2`
		FOREIGN KEY (`katao_product_id`)
		REFERENCES `katao_product` (`id`)
		ON DELETE CASCADE
)Engine=InnoDB;

CREATE TABLE `katao_supplier_invoice_order`
(
	`katao_supplier_invoice_id` INTEGER  NOT NULL,
	`katao_order_id` INTEGER  NOT NULL,
	PRIMARY KEY (`katao_supplier_invoice_id`,`katao_order_id`),
	CONSTRAINT `katao_supplier_invoice_order_FK_1`
		FOREIGN KEY (`katao_supplier_invoice_id`)
		REFERENCES `katao_supplier_invoice` (`id`)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	INDEX `katao_supplier_invoice_order_FI_2` (`katao_order_id`),
	CONSTRAINT `katao_supplier_invoice_order_FK_2`
		FOREIGN KEY (`katao_order_id`)
		REFERENCES `katao_order` (`id`)
		ON UPDATE CASCADE
		ON DELETE CASCADE
)Engine=InnoDB;

ALTER TABLE `katao_config` CHANGE `accounting_code_error_adjustment` `accounting_code_error_adjustment_positive` VARCHAR(12) NOT NULL;
ALTER TABLE `katao_config` ADD `accounting_code_error_adjustment_negative` VARCHAR(12) NOT NULL AFTER `accounting_code_error_adjustment_positive`;

UPDATE `katao_config` SET `accounting_code_error_adjustment_positive` = "758000";
UPDATE `katao_config` SET `accounting_code_error_adjustment_negative` = "658000";

SET FOREIGN_KEY_CHECKS = 1;

COMMIT;