-- ---
-- Globals
-- ---

-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- SET FOREIGN_KEY_CHECKS=0;

-- ---
-- Table 'products'
-- Tabela zawierajaca promocje
-- ---

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` INT(12) NOT NULL AUTO_INCREMENT COMMENT 'ID promocji',
  `name` VARCHAR(256) NOT NULL COMMENT 'Nazwa promocji',
  `img_url` VARCHAR(256) NOT NULL COMMENT 'Url obrazka promocji',
  `price` DECIMAL(9, 2) NOT NULL,
  `shop_id` INT(12) NOT NULL COMMENT 'ID sklepu',
  `category_id` INT(12) NOT NULL COMMENT 'ID kategorii',
  `starts_on` VARCHAR(128) NOT NULL COMMENT 'Data startu promocji, format YYYY.MM.DD',
  `ends_on` VARCHAR(128) NOT NULL DEFAULT 'NOT NULL' COMMENT 'Data końca promocji, format YYYY.MM.DD',
  `created_on` VARCHAR(128) NOT NULL COMMENT 'Data stworzenia promocji, format YYYY.MM.DD',
  `to_show` ENUM('Y', 'N') NOT NULL DEFAULT 'Y' COMMENT 'Czy promocja jest do wyświetlenia użytkownikom, Y - tak, N -',
  `created_by` INT(12) NOT NULL COMMENT 'Stworzone przez',
  PRIMARY KEY (`id`)
) COMMENT 'Tabela zawierajaca promocje';

-- ---
-- Table 'categories'
-- Tabela z kategoriami
-- ---

DROP TABLE IF EXISTS `categories`;

CREATE TABLE `categories` (
  `id` INT(12) NOT NULL AUTO_INCREMENT COMMENT 'ID kategorii',
  `name` VARCHAR(256) NOT NULL COMMENT 'Nazwa kategorii',
  PRIMARY KEY (`id`)
) COMMENT 'Tabela z kategoriami';

-- ---
-- Table 'shops'
-- Tabela sklepow
-- ---

DROP TABLE IF EXISTS `shops`;

CREATE TABLE `shops` (
  `id` INT(12) NOT NULL AUTO_INCREMENT COMMENT 'ID sklepu',
  `name` VARCHAR(256) NOT NULL COMMENT 'Nazwa sklepu',
  `img_url` VARCHAR(256) NOT NULL COMMENT 'Url obrazka sklepu',
  PRIMARY KEY (`id`)
) COMMENT 'Tabela sklepow';

-- ---
-- Table 'users'
-- Tabela adminow
-- ---

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL COMMENT 'Login',
  `password` VARCHAR(128) NOT NULL COMMENT 'Haslo',
  `img_url` VARCHAR(258) NOT NULL COMMENT 'Url obrazka profilowego',
  PRIMARY KEY (`id`)
) COMMENT 'Tabela adminow';

-- ---
-- Foreign Keys
-- ---

ALTER TABLE `products` ADD FOREIGN KEY (shop_id) REFERENCES `shops` (`id`);
ALTER TABLE `products` ADD FOREIGN KEY (category_id) REFERENCES `categories` (`id`);
ALTER TABLE `products` ADD FOREIGN KEY (created_by) REFERENCES `users` (`id`);

-- ---
-- Table Properties
-- ---

-- ALTER TABLE `products` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `categories` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `shops` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `users` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ---
-- Test Data
-- ---

-- INSERT INTO `products` (`id`,`name`,`img_url`,`price`,`shop_id`,`category_id`,`starts_on`,`ends_on`,`created_on`,`to_show`,`created_by`) VALUES
-- ('','','','','','','','','','','');
-- INSERT INTO `categories` (`id`,`name`) VALUES
-- ('','');
-- INSERT INTO `shops` (`id`,`name`,`img_url`) VALUES
-- ('','','');
-- INSERT INTO `users` (`id`,`name`,`password`,`img_url`) VALUES
-- ('','','','');