create table Role( id int primary key auto_increment, level ENUM('Administrator','WarehouseManger','User','NonUser',) );

create table Status( id int primary key auto_increment, state ENUM('Active','Inactive','Banned','Request') );

create table User ( id varchar(50) NOT NULL, name varchar(50) NOT NULL, birth date, password varchar(50) NOT NULL, email varchar(50) NOT NULL, telephone varchar(50), join_date date default current_timestamp(), role_id int not null default 4, status_id int not null default 4, primary key (id), foreign key (role_id) references Role (id),

foreign key (status_id) references Role (id) );

create table member( id varchar(50) not null, shop_name varchar(50) not null, co_reg_num varchar(50) not null, shop_address varchar(50) not null, business_type varchar(50), online_business_number varchar(50), fax varchar(50), primary key (id), foreign key (id) references User (id) );

select * from Role; INSERT INTO Role (level) VALUES ('Administrator'); INSERT INTO Role (level) VALUES ('WarehouseManger'); INSERT INTO Role (level) VALUES ('User'); INSERT INTO Role (level) VALUES ('NonUser');

create table Notice ( no int primary key auto_increment, user_id varchar(45) not null, type varchar(45) NOT NULL, title varchar(45) NOT NULL, content longtext NOT NULL, notice_date Date not null, foreign key (user_id) references user (id) ); select * from Notice;

create table Vehicle ( num varchar(45) primary key, type varchar(50) NOT NULL, dname varchar(50) NOT NULL,

dphone varchar(45) NOT NULL,

status smallint NOT NULL default 0, );

create table Warehouse ( id int primary key auto_increment, name varchar(45) NOT NULL, location varchar(45) NOT NULL, type varchar(45) NOT NULL );

create table Dispatch ( id int primary key auto_increment, vehicle_num varchar(45) NOT NULL, warehouse_id int NOT NULL, request_date date NOT NULL, dispatch_date date NOT NULL, foreign key (vehicle_num) references Vehicle (num), foreign key (warehouse_id) references Warehouse (id) );

create table Expense ( id int primary key auto_increment, warehouse_id int NOT NULL, type varchar(45) NOT NULL, cost int NOT NULL, expense_date date not null, foreign key (warehouse_id) references Warehouse (id) );


create table Sales ( id int primary key auto_increment, warehouse_id int NOT NULL, amount int NOT NULL, type varchar(45) NOT NULL, sales_date date not null, foreign key (warehouse_id) references Warehouse (id) );

create table Product ( id int primary key auto_increment, brand varchar(45) NOT NULL, name varchar(45) NOT NULL, type varchar(45) NOT NULL, price int NOT NULL, sale_price int NOT NULL, quantity int not null default 0 );

create table Stock ( id int primary key auto_increment, warehouse_id int default null, product_id int, quantity int NOT NULL, foreign key (warehouse_id) references Warehouse (id), foreign key (product_id) references Product (id) );

create table Stock_Count ( id int auto_increment, warehouse_id int, check_date datetime NOT NULL, primary key (id, warehouse_id), foreign key (warehouse_id) references Warehouse (id) );