-- Start

-- EET

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 `prepaid` CHANGE `creditLeft` `creditCALeft` decimal(10,0) NOT NULL AFTER `utValidTo`;
ALTER TABLE `prepaid` ADD `creditCLLeft` decimal(10,0) NOT NULL DEFAULT '0' AFTER `creditCALeft`;

INSERT INTO `taxRate` (`id`, `name`, `rate`, `field`, `expired`) VALUES ('1', 'Výchozí', '0', '', '0');

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

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

ALTER TABLE `productSell`
	ADD `priceCreditCL` decimal(10,0) NULL AFTER `priceCreditCA`,
	ADD `priceCreditExistsCA` decimal(10,0) NULL AFTER `priceCreditCL`,
	ADD `priceCreditExistsCL` decimal(10,0) NULL AFTER `priceCreditExistsCA`,
	ADD `priceCachless` decimal(10,0) NULL AFTER `priceDirectpaid`;

-- (ne)potřebné tabulky

CREATE TABLE `company` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(1000) NOT NULL,
  `vatUID` varchar(20) NOT NULL,
  `certFilePublic` varchar(100) NOT NULL,
  `certFilePrivate` varchar(100) NOT NULL,
  `certPassword` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `cachRegister` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `workshopUID` varchar(100) NOT NULL,
  `cashRegisterUID` varchar(100) NOT NULL,
  `companyId` int(10) NOT NULL,
  `lastCounterValue` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `companyId` (`companyId`),
  CONSTRAINT `cachRegister_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `company` (`id`)
);

CREATE TABLE `receipt` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `dtCreate` datetime NOT NULL,
  `dtSend` datetime DEFAULT NULL,
  `counterValue` int(10) NOT NULL,
  `cachRegisterId` int(10) NOT NULL,
  `totalCache` double(13,3) NOT NULL,
  `totalCreditCA` double(13,3) NOT NULL,
  `totalCreditCL` double(13,3) NOT NULL,
  `state` enum('new','sent','sending','failed','empty') NOT NULL,
  `pkp` varchar(500) DEFAULT NULL,
  `bkp` varchar(50) DEFAULT NULL,
  `fik` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cachRegisterId` (`cachRegisterId`),
  CONSTRAINT `receipt_ibfk_1` FOREIGN KEY (`cachRegisterId`) REFERENCES `cachRegister` (`id`)
);

CREATE TABLE `receiptTaxItem` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `taxRateId` int(10) NOT NULL,
  `receiptId` int(10) NOT NULL,
  `priceNT` decimal(13,3) NOT NULL,
  `priceWT` decimal(13,3) NOT NULL,
  `priceTax` decimal(13,3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `taxRateId` (`taxRateId`),
  KEY `receiptId` (`receiptId`),
  CONSTRAINT `receiptTaxItem_ibfk_1` FOREIGN KEY (`taxRateId`) REFERENCES `taxRate` (`id`),
  CONSTRAINT `receiptTaxItem_ibfk_2` FOREIGN KEY (`receiptId`) REFERENCES `receipt` (`id`)
);

--

ALTER TABLE `customer` ADD `rc` varchar(50) COLLATE 'utf8_czech_ci' NULL;
ALTER TABLE `customer` ADD `dtBirth` datetime NULL AFTER `rc`;

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`);

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');

ALTER TABLE `productSell`
	ADD `receiptTaxItemId` int(10) NULL,
	ADD FOREIGN KEY (`receiptTaxItemId`) REFERENCES `receiptTaxItem` (`id`);

ALTER TABLE `taxRate` ADD `shortcut` varchar(10) NOT NULL DEFAULT '';
-- fitko ne
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);
-- /

-- Brumov
-- Fitko

ALTER TABLE `product` ADD `widget` text NULL;
-- Jen fitko
-- UPDATE `product` SET `id` = '133', `name` = 'Solárium', `description` = '', `price` = '0', `enabled` = '1', `customerOnly` = '0', `packageId` = NULL, `productId` = NULL, `isCategory` = '0', `position` = '0', `deleted` = '0', `code` = '', `askPrice` = '1', `askCount` = '0', `askCustomName` = '0', `type` = '0', `stockable` = '0', `color` = '#d8ff6b', `taxRateId` = '1', `widget` = '{\"widget\":\"SolarkoSell\"}'
-- WHERE `name` LIKE '%solar%' AND ((`id` = '133'));