-- Typ produktu/kategorie/+stránka
ALTER TABLE `product` CHANGE `isCategory` `isCategory` tinyint(1) NOT NULL DEFAULT '0' AFTER `productId`, ADD `type` tinyint(2) unsigned NOT NULL DEFAULT '0';
UPDATE product SET type = isCategory;

-- Příznak skladová položka
ALTER TABLE `product` ADD `stockable` tinyint(1) unsigned NOT NULL DEFAULT '0';

-- Upozornit před koncem
ALTER TABLE `package` ADD `notifyBefore` int unsigned NULL;

ALTER TABLE `product` ADD `color` varchar(20) NULL;

-- fitko, brumov

ALTER TABLE `product` CHANGE `customerOnly` `customerOnly` tinyint(1) NOT NULL DEFAULT '0' AFTER `enabled`;
ALTER TABLE `product` CHANGE `code` `code` varchar(50) COLLATE 'utf8_czech_ci' NOT NULL DEFAULT '' AFTER `deleted`;

--

CREATE TABLE `cachRegister` (
	`id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY PRIMARY KEY,
	`workshopUID` VARCHAR(100) NOT NULL,
	`cashRegisterUID` VARCHAR(100) NOT NULL,
	`companyId` INT(10) NOT NULL,
	`lastCounterValue` INT(10) NOT NULL
);
CREATE TABLE `company` (
	`id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY PRIMARY KEY,
	`name` VARCHAR(1000) NOT NULL,
	`vatUID` VARCHAR(20) NOT NULL,
	`keyFilePublic` VARCHAR(100) NOT NULL,
	`keyFilePrivate` VARCHAR(100) NOT NULL,
	`keyPassword` VARCHAR(100) NOT NULL
);
CREATE TABLE `receipt` (
	`id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY PRIMARY KEY,
	`dtCreate` DATETIME NOT NULL,
	`dtSend` DATETIME NOT NULL,
	`counterValue` INT(10) NOT NULL,
	`cachRegisterId` INT(10) NOT NULL,
	`dtReceipt` DATETIME NOT NULL,
	`totalPrice` DOUBLE(13,3) NOT NULL,
	`state` TEXT NOT NULL,
	`pkp` TEXT NOT NULL,
	`bkp` TEXT NOT NULL
);
CREATE TABLE `receiptTaxItem` (
	`id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY PRIMARY KEY,
	`taxRateId` INT(10) NOT NULL,
	`receiptId` INT(10) NOT NULL,
	`priceNT` DECIMAL(13,3) NOT NULL,
	`priceWT` DECIMAL(13,3) NOT NULL,
	`priceWTR` DECIMAL(13,3) NOT NULL,
	`priceTax` DECIMAL(13,3) NOT NULL
);
CREATE TABLE `taxRate` (
	`id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY PRIMARY KEY,
	`name` VARCHAR(100) NOT NULL,
	`rate` DECIMAL(13,3) NOT NULL,
	`field` VARCHAR(100) NOT NULL,
	`expired` TINYINT(1) NOT NULL CHECK(`expired` IN (1, 0))
);
ALTER TABLE `cachRegister` ADD FOREIGN KEY (`companyId`) REFERENCES `company` (`id`);
ALTER TABLE `receipt` ADD FOREIGN KEY (`cachRegisterId`) REFERENCES `cachRegister` (`id`);
ALTER TABLE `receiptTaxItem` ADD FOREIGN KEY (`taxRateId`) REFERENCES `taxRate` (`id`);
ALTER TABLE `receiptTaxItem` ADD FOREIGN KEY (`receiptId`) REFERENCES `receipt` (`id`);

ALTER TABLE `product` 
	ADD `taxRateId` int(10) NULL,
	ADD FOREIGN KEY (`taxRateId`) REFERENCES `taxRate` (`id`);


ALTER TABLE `receipt`
	CHANGE `dtSend` `dtSend` datetime NULL AFTER `dtCreate`,
	CHANGE `pkp` `pkp` text COLLATE 'utf8mb4_general_ci' NULL AFTER `state`,
	CHANGE `bkp` `bkp` text COLLATE 'utf8mb4_general_ci' NULL AFTER `pkp`;

ALTER TABLE `productSell` ADD `priceCreditNC` decimal(10,0) NULL AFTER `priceCredit`;
ALTER TABLE `prepaid` ADD `creditNCLeft` decimal(10,0) NOT NULL AFTER `creditLeft`;

ALTER TABLE `receipt`
	ADD `totalCredit` double(13,3) NOT NULL AFTER `totalPrice`,
	ADD `totalCreditNC` double(13,3) NOT NULL AFTER `totalCredit`;

ALTER TABLE `productSell`
	CHANGE `priceCredit` `priceCreditCA` decimal(10,0) NULL AFTER `priceDirectpaid`,
	ADD `priceCreditCL` decimal(10,0) NULL AFTER `priceCreditCA`,
	DROP `priceCreditNC`;

-- fitko ---

ALTER TABLE `customer`
	ADD `dtBirth` datetime NULL AFTER `rc`;

ALTER TABLE `customer`
    ADD `utBirth` int unsigned NULL AFTER `contactAddress`,
	ADD `rc` varchar(50) COLLATE 'utf8_czech_ci' NULL AFTER `utBirth`;

CREATE TABLE `config` (
                          `key` varchar(255) NOT NULL,
                          `content` text NOT NULL,
                          `contentType` enum('string','int','json') NOT NULL
);
ALTER TABLE `config` ADD PRIMARY KEY `key` (`key`);

-- 2021-08-26 -- Brumov,

CREATE TABLE `memberLevel` (
                               `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY PRIMARY KEY,
                               `name` VARCHAR(210) NOT NULL
);

ALTER TABLE `customer`
    ADD `memberLevelId` INT(10) NULL;
ALTER TABLE `customer` ADD FOREIGN KEY (`memberLevelId`) REFERENCES `memberLevel` (`id`);

INSERT INTO `memberLevel` (`name`) VALUES ('Nečlen');
INSERT INTO `memberLevel` (`name`) VALUES ('Člen');

INSERT INTO `product` (`id`, `name`, `description`, `price`, `enabled`, `customerOnly`, `packageId`, `productId`, `isCategory`, `position`, `deleted`, `code`, `askPrice`, `askCount`, `askCustomName`, `type`, `stockable`, `color`, `taxRateId`) VALUES
    (NULL,	'Doplnění / vrácení kreditu',	'Specialni produk, nemazat, neaktivovat',	0,	0,	0,	NULL,	NULL,	0,	0,	0,	'creditChange',	0,	0,	0,	0,	0,	NULL,	1);


