create schema if not exists webs;

create table if not exists webs.category (
  `ID` int not null auto_increment,
  `name` varchar(45) not null,
  primary key (`ID`)
);

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_path` varchar(255) not null default "img/placeholder.png",
	`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.customer (
  `ID` int not null auto_increment,
  `name` varchar(45) not null,
  primary key (`ID`)
);

create table if not exists webs.cart (
  `ID` int not null auto_increment,
  `product` int not null,
  `customer` int not null,
	`count` int not null default 1,
  primary key (`ID`),
  constraint `cart_product_fk`
    foreign key (`product`)
    references webs.product (`ID`)
    on update cascade,
  constraint `cart_customer_fk`
    foreign key (`customer`)
    references webs.customer (`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
);