create database management;
use management;
CREATE TABLE demander (
  ID int NOT NULL auto_increment,
  name varchar(255) UNIQUE,
  address varchar(255),
  telephone varchar(255) DEFAULT NULL,
  PRIMARY KEY (ID)
);
CREATE TABLE materials (
  ID int NOT NULL auto_increment,
  name varchar(255) NOT NULL UNIQUE,
  type varchar(255) NOT NULL,
  PRIMARY KEY (ID)
);
CREATE TABLE order_form (
  demander_ID int NOT NULL,
  ID int NOT NULL auto_increment,
  type varchar(255) NOT NULL,
  emergency varchar(255) DEFAULT NULL,
  remark varchar(255) DEFAULT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (demander_ID) REFERENCES demander (ID) ON DELETE CASCADE
);
CREATE TABLE provider (
  ID int NOT NULL auto_increment,
  name varchar(255) UNIQUE,
  address varchar(255),
  telephone varchar(255) DEFAULT NULL,
  PRIMARY KEY (ID)
);
CREATE TABLE warehouse (
  ID int NOT NULL auto_increment,
  address varchar(255) NOT NULL,
  PRIMARY KEY (ID)
);
CREATE TABLE provide (
  provider_ID int NOT NULL,
  materials_ID int NOT NULL,
  time datetime NOT NULL,
  quantity decimal(8,0) DEFAULT NULL CHECK (quantity >= 0),
  verification BOOLEAN,
  PRIMARY KEY (provider_ID,materials_ID),
  KEY materials_ID (materials_ID),
  FOREIGN KEY (provider_ID) REFERENCES provider (ID) ON DELETE CASCADE,
  FOREIGN KEY (materials_ID) REFERENCES materials (ID) ON DELETE CASCADE
);
CREATE TABLE send (
  warehouse_ID int NOT NULL,
  materials_ID int NOT NULL,
  demander_ID int NOT NULL,
  time datetime DEFAULT NULL,
  quantity decimal(8,0) DEFAULT NULL CHECK (quantity >= 0),
  verification BOOLEAN,
  PRIMARY KEY (warehouse_ID,materials_ID,demander_ID),
  KEY materials_ID (materials_ID),
  KEY demander_ID (demander_ID),
  CONSTRAINT send_ibfk_1 FOREIGN KEY (warehouse_ID) REFERENCES warehouse (ID) ON DELETE CASCADE,
  CONSTRAINT send_ibfk_2 FOREIGN KEY (materials_ID) REFERENCES materials (ID) ON DELETE CASCADE,
  CONSTRAINT send_ibfk_3 FOREIGN KEY (demander_ID) REFERENCES demander (ID) ON DELETE CASCADE
);
CREATE TABLE stockpile (
  warehouse_ID int NOT NULL,
  materials_ID int NOT NULL,
  quantity decimal(8,0) DEFAULT NULL,
  PRIMARY KEY (warehouse_ID,materials_ID),
  KEY materials_ID (materials_ID),
  CONSTRAINT stockpile_ibfk_1 FOREIGN KEY (warehouse_ID) REFERENCES warehouse (ID) ON DELETE CASCADE,
  CONSTRAINT stockpile_ibfk_2 FOREIGN KEY (materials_ID) REFERENCES materials (ID) ON DELETE CASCADE,
  CONSTRAINT stockpile_chk_1 CHECK ((quantity >= 0))
);
CREATE TABLE users(
ID INT,
password varchar(255),
type varchar(255) check (type in ('demander','provider','admin')),
PRIMARY KEY (id,type)
);