# SQL Manager 2007 for MySQL 4.2.0.2
# ---------------------------------------
# Host     : localhost
# Port     : 3306
# Database : game2k


SET FOREIGN_KEY_CHECKS=0;

CREATE DATABASE `game2k`
    CHARACTER SET 'utf8'
    COLLATE 'utf8_general_ci';

USE `game2k`;

#
# Structure for the `contacts` table : 
#

CREATE TABLE `contacts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `surname` varchar(64) NOT NULL DEFAULT '',
  `street` varchar(32) NOT NULL DEFAULT '',
  `city` varchar(32) NOT NULL DEFAULT '',
  `zip` varchar(128) NOT NULL DEFAULT '',
  `country` varchar(32) DEFAULT '',
  `phone` varchar(32) NOT NULL DEFAULT '',
  `email` varchar(64) NOT NULL DEFAULT '',
  `notice` text,
  PRIMARY KEY (`id`),
  KEY `surname` (`surname`(1)),
  KEY `zip` (`zip`(1)),
  KEY `name` (`name`(1)),
  KEY `city` (`city`(1)),
  KEY `street` (`street`(1))
) ENGINE=InnoDB AUTO_INCREMENT=7595 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=83 COMMENT='InnoDB free: 22528 kB';

#
# Structure for the `dobirky_contacts` table : 
#

CREATE TABLE `dobirky_contacts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `surname` varchar(64) NOT NULL DEFAULT '',
  `street` varchar(32) NOT NULL DEFAULT '',
  `city` varchar(64) NOT NULL DEFAULT '',
  `zip` varchar(128) NOT NULL DEFAULT '',
  `country` varchar(32) DEFAULT '',
  `phone` varchar(64) NOT NULL DEFAULT '',
  `email` varchar(64) NOT NULL DEFAULT '',
  `notice` text,
  `warning` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `surname` (`surname`(1)),
  KEY `zip` (`zip`(1)),
  KEY `name` (`name`(1)),
  KEY `city` (`city`(1)),
  KEY `street` (`street`(1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for the `dobirky` table : 
#

CREATE TABLE `dobirky` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice` varchar(16) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `price` int(11) unsigned DEFAULT '0',
  `contact` int(10) unsigned DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `text` text NOT NULL,
  `notice` text NOT NULL,
  `payment` int(11) NOT NULL DEFAULT '1',
  `pricepost` int(11) NOT NULL DEFAULT '0',
  `pc` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `date` (`date`),
  KEY `contact` (`contact`),
  CONSTRAINT `dobirky_ibfk_1` FOREIGN KEY (`contact`) REFERENCES `dobirky_contacts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#
# Structure for the `titles` table : 
#

CREATE TABLE `titles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `platform` int(10) unsigned DEFAULT NULL,
  `price` int(10) DEFAULT NULL,
  `price_date` date DEFAULT NULL,
  `cover_cd` text NOT NULL,
  `firms` text NOT NULL,
  `descript` text NOT NULL,
  `requests` text NOT NULL,
  `notice` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`(3)),
  KEY `platform` (`platform`)
) ENGINE=InnoDB AUTO_INCREMENT=7074 DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 7168 kB';

#
# Structure for the `products` table : 
#

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` int(10) unsigned DEFAULT NULL,
  `lang_p` int(11) unsigned DEFAULT '0',
  `lang_m` int(11) unsigned DEFAULT '0',
  `complete` int(11) DEFAULT '1',
  `status` int(11) DEFAULT '1',
  `buy_price` int(11) DEFAULT '0',
  `buy_date` date DEFAULT NULL,
  `buy_notice` text,
  `contact` int(10) unsigned DEFAULT NULL,
  `sell_price` int(11) DEFAULT NULL,
  `sell_date` date DEFAULT NULL,
  `cashout_date` date DEFAULT NULL,
  `sell_notice` text,
  `dobirka` tinyint(1) NOT NULL DEFAULT '0',
  `extra` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`),
  KEY `contact` (`contact`),
  KEY `buy_date` (`buy_date`),
  KEY `sell_date` (`sell_date`),
  KEY `dobirka` (`dobirka`),
  KEY `status` (`status`),
  KEY `extra` (`extra`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`title`) REFERENCES `titles` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `products_ibfk_2` FOREIGN KEY (`contact`) REFERENCES `contacts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=54414 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=100 COMMENT='InnoDB free: 10240 kB; (`title`) REFER `game2k/titles`(`id`)';

#
# Structure for the `dobirky_products` table : 
#

CREATE TABLE `dobirky_products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dobirka` int(10) unsigned DEFAULT NULL,
  `product` int(10) unsigned DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `notice` text,
  `platform` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`),
  KEY `product` (`product`),
  KEY `dobirka` (`dobirka`),
  CONSTRAINT `dobirky_products_fk` FOREIGN KEY (`dobirka`) REFERENCES `dobirky` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `dobirky_products_ibfk_1` FOREIGN KEY (`product`) REFERENCES `products` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8;

#
# Structure for the `orders` table : 
#

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `status` int(11) NOT NULL DEFAULT '1',
  `name` varchar(64) NOT NULL,
  `contact` varchar(64) NOT NULL,
  `zaloha` int(11) DEFAULT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`date`),
  KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#
# Structure for the `orders_products` table : 
#

CREATE TABLE `orders_products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order` int(10) unsigned DEFAULT NULL,
  `status` int(10) unsigned NOT NULL DEFAULT '1',
  `product` int(10) unsigned DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `notice` text NOT NULL,
  `platform` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `title` (`title`),
  KEY `product` (`product`),
  KEY `order` (`order`),
  CONSTRAINT `orders_products_ibfk_1` FOREIGN KEY (`order`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `orders_products_ibfk_2` FOREIGN KEY (`product`) REFERENCES `products` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#
# Structure for the `users` table : 
#

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `login` varchar(64) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `last` datetime DEFAULT NULL,
  `password` varchar(32) DEFAULT NULL,
  `rights` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#
# Definition for the `products_store` view : 
#

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`192.168.3.%` SQL SECURITY DEFINER VIEW `products_store` AS 
  select 
    `t`.`id` AS `id`,
    count(0) AS `count` 
  from 
    (`products` `p` join `titles` `t` on((`t`.`id` = `p`.`title`))) 
  where 
    isnull(`p`.`sell_date`) 
  group by 
    `t`.`id`;

#
# Definition for the `products_top` view : 
#

CREATE ALGORITHM=UNDEFINED DEFINER=`game2k`@`%` SQL SECURITY DEFINER VIEW `products_top` AS 
  select 
    `t`.`id` AS `id`,
    count(0) AS `count`,
    max(`p`.`sell_date`) AS `last_sell` 
  from 
    (`products` `p` join `titles` `t` on((`t`.`id` = `p`.`title`))) 
  where 
    (`p`.`sell_date` is not null) 
  group by 
    `t`.`id`;