create schema if not exists webs;

create table if not exists webs.category (
  `id` int not null auto_increment,
  `name` varchar(45) not null,
	`parent` int null default null,
  primary key (`id`),
  constraint `category_parent_fk`
    foreign key (`parent`)
    references webs.category (`id`)
    on update cascade
);

create table if not exists webs.product (
  `id` int not null auto_increment,
  `name` varchar(255) not null,
  `price` decimal(5, 2) not null,
  `image` boolean not null default false,
  `description` mediumtext null default null,
	`category` int not null,
  primary key (`id`),
  constraint `product_category_fk`
    foreign key (`category`)
    references webs.category (`id`)
    on update cascade
);

create table if not exists webs.user (
  `id` int not null auto_increment,
  `name` varchar(45) not null,
	`hash` binary(64) not null,
	`privileges` int not null default 1,
  primary key (`id`)
);

create table if not exists webs.order (
  `id` int not null auto_increment,
  `status` int not null default 1,
  `user` int not null,
  primary key (`id`),
  constraint `order_user_fk`
    foreign key (`user`)
    references webs.user (`id`)
    on update cascade
);

create table if not exists webs.orderproduct (
  `id` int not null auto_increment,
  `product` int not null,
	`count` int not null default 1,
	`order` int not null,
  primary key (`id`),
  constraint `cart_product_fk`
    foreign key (`product`)
    references webs.product (`id`)
    on update cascade,
  constraint `cart_order_fk`
    foreign key (`order`)
    references webs.order (`id`)
    on update cascade
);

create table if not exists webs.promotion (
  `id` int not null auto_increment,
  `product` int not null,
  `count_buff` int not null default 1,
	`price_buff` decimal(4, 3) not null default 1.0,
  primary key (`id`),
  constraint `promotion_product_fk`
    foreign key (`product`)
    references webs.product (`id`)
    on update cascade
);