CREATE TABLE address(
	id INT PRIMARY KEY auto_increment COMMENT '收货地址id',
	user_id INT COMMENT '用户id',
	province VARCHAR(32) COMMENT '省',
	city VARCHAR(32) COMMENT '市',
	district VARCHAR(32) COMMENT '区',
	detail VARCHAR(32) COMMENT '详细地址',
	remark TINYINT COMMENT '备注(0家,1公司,2学校)',
	is_default TINYINT COMMENT '是否默认收货地址'
);

CREATE TABLE user (
	id INT PRIMARY KEY auto_increment COMMENT '用户id',
	username VARCHAR(64) not null UNIQUE COMMENT '用户名',
	password VARCHAR(64) not null COMMENT '密码',
	realname VARCHAR(32) COMMENT '真实姓名',
	sex char(2) COMMENT '性别',
	email VARCHAR(128) COMMENT '邮箱',
	mobile VARCHAR(16) COMMENT '手机号',
	id_code VARCHAR(18) COMMENT '身份证号'
);

CREATE TABLE praise (
	id INT PRIMARY KEY auto_increment COMMENT '点赞记录id',
	user_id INT not null COMMENT '用户id',
	spu_id INT not null COMMENT '商品spuid'
);

CREATE TABLE admin (
	id INT PRIMARY KEY auto_increment COMMENT '管理员id',
	username VARCHAR(64) not null UNIQUE COMMENT '管理员登录名',
	password VARCHAR(64) not null COMMENT '管理员密码'
);

CREATE TABLE `COMMENT`(
	id int PRIMARY KEY auto_increment COMMENT '评论记录id',
	user_id int not null COMMENT '用户id',
	spu_id int not null COMMENT '商品spuid',
	content VARCHAR(512) not null COMMENT '评论内容',
	star int(11) COMMENT '星星',
	level int(11) COMMENT '等级',
	parent_id int not null COMMENT '父评论id'
);

create table spu(
	id bigint PRIMARY KEY auto_increment COMMENT '商品spuid',
	serial_no VARCHAR(64) not null UNIQUE COMMENT '商品spu编号',
	name VARCHAR(128) not null COMMENT '商品spu名',
	description VARCHAR(256) COMMENT '商品spu描述',
	category_id int COMMENT '分类id',
	brand_id int COMMENT '品牌id'
);

create table brand(
	id BIGINT PRIMARY KEY auto_increment COMMENT '品牌id',
	name varchar(32) not null COMMENT '品牌名称' 
);

CREATE TABLE sku(
	id BIGINT PRIMARY KEY auto_increment COMMENT '商品sku',
	serial_no VARCHAR(64) not null UNIQUE COMMENT '商品sku编号',
	spu_id int COMMENT 'skuid',
	price int(11) COMMENT 'sku价格',
	stock int(11) COMMENT 'sku库存'
);

CREATE TABLE sku_property_option_relation(
	id int PRIMARY key auto_increment comment '商品sku和规格选项关系id',
	sku_id BIGINT COMMENT 'skuid',
	property_option_id int COMMENT '规格选项关系'
);

CREATE TABLE property_option(
	id int PRIMARY KEY auto_increment COMMENT '属性选项id',
	option_value VARCHAR(32) comment '属性选项值',
	property_id int comment '属性id'
);

CREATE TABLE property(
	id int PRIMARY key auto_increment COMMENT '属性id',
	name VARCHAR(32) COMMENT '属性名称',
	category_id int comment '分类id'
);

CREATE TABLE category(
	id BIGINT PRIMARY KEY auto_increment COMMENT '分类id',
	name varchar(32) not null UNIQUE COMMENT '分类名称'
);

CREATE TABLE category_brand_relation(
	id BIGINT PRIMARY KEY auto_increment COMMENT '关系id',
	category_id int COMMENT '分类id',
	brand_id int COMMENT '品牌id'
);

CREATE table specification(
	id int PRIMARY KEY auto_increment COMMENT '商品规格id',
	name VARCHAR(32) not null UNIQUE COMMENT '规格名称',
	category_id int COMMENT '分类id' 
);

CREATE TABLE specification_option(
	id INT PRIMARY KEY auto_increment COMMENT '规格选项id',
	option_value VARCHAR(32) not null UNIQUE COMMENT '规格选项值',
	specification_id int COMMENT '规格id'
);

CREATE table sku_specification_option_relation(
	id INT PRIMARY KEY auto_increment COMMENT '关系id',
	sku_id BIGINT COMMENT 'skuid',
	specification_option_id int COMMENT '规格选项关系'
);

alter table category modify id int unsigned auto_increment comment '主键id';
alter table brand modify id int unsigned auto_increment comment '主键id';
alter table category_brand_relation modify id int unsigned auto_increment comment '主键id';
alter table specification modify id int unsigned auto_increment comment '主键id';
alter table specification_option modify id int unsigned auto_increment comment '主键id';
alter table property modify id int unsigned auto_increment comment '主键id';
alter table property_option modify id int unsigned auto_increment comment '主键id';
alter table spu modify id int unsigned auto_increment comment '主键id';
alter table sku modify id int unsigned auto_increment comment '主键id';
alter table sku_property_option_relation modify id int unsigned auto_increment comment '主键id';
alter table sku_specification_option_relation modify id int unsigned auto_increment comment '主键id';
alter table user modify id int unsigned auto_increment comment '主键id';
alter table admin modify id int unsigned auto_increment comment '主键id';
alter table praise modify id int unsigned auto_increment comment '主键id';
alter table `comment` modify id int unsigned auto_increment comment '主键id';
alter table address modify id int unsigned auto_increment comment '主键id';

-- 1). 品牌和分类是m-n关系, 关系通过第三张关系表实现,所以分类与关系表是1-n,品牌和关系表也是1-n
alter table category_brand_relation modify category_id int unsigned;
alter table category_brand_relation add constraint fk_relation_category_id foreign key(category_id) references category(id);
alter table category_brand_relation modify brand_id int unsigned;
alter table category_brand_relation add constraint fk_relation_brand_id foreign key(brand_id) references brand(id);
-- 2). 商品规格与规格选项是1-n关系
alter table specification_option modify specification_id int unsigned;
alter table specification_option add constraint fk_specification_id foreign key(specification_id) references specification(id);
-- 3). 商品属性与属性选项是1-n关系
alter table property_option modify property_id int unsigned;
alter table property_option add constraint fk_property_id foreign key(property_id) references property(id);
-- 3). 分类与规格是1-n关系
alter table specification modify category_id int unsigned;
alter table specification add constraint fk_specification_category_id foreign key(category_id) references category(id);
-- 4). 分类与属性是1-n关系
alter table property modify category_id int unsigned;
alter table property add constraint fk_property_category_id foreign key(category_id) references category(id);
-- 5). 分类与SPU是1-n关系
alter table spu modify category_id int unsigned;
alter table spu add constraint fk_spu_category_id foreign key(category_id) references category(id);
-- 6). 品牌与SPU是1-n关系
alter table spu modify brand_id int unsigned;
alter table spu add constraint fk_spu_brand_id foreign key(brand_id) references brand(id);
-- 7). SPU与SKU是1-n关系
alter table sku modify spu_id int unsigned;
alter table sku add constraint fk_sku_spu_id foreign key(spu_id) references spu(id);
-- 8). SKU与规格选项是m-n关系, 关系在第三张表sku_specification_option_relation中体现
alter table sku_specification_option_relation modify specification_option_id int unsigned;
alter table sku_specification_option_relation add constraint fk_specification_option_id foreign key(specification_option_id) references specification_option(id);
alter table sku_specification_option_relation modify sku_id int unsigned;
alter table sku_specification_option_relation add constraint fk_specification_option_sku_id foreign key(sku_id) references sku(id);
-- 9). SKU与属性选项是m-n关系, 关系在第三张表sku_property_option_relation中体现
alter table sku_property_option_relation modify property_option_id int unsigned;
alter table sku_property_option_relation add constraint fk_property_option_id foreign key(property_option_id) references property_option(id);
alter table sku_property_option_relation modify sku_id int unsigned;
alter table sku_property_option_relation add constraint fk_property_option_sku_id foreign key(sku_id) references sku(id);
-- 10). 用户和点赞是1-n关系
alter table praise modify user_id int unsigned;
alter table praise add constraint fk_praise_user_id foreign key(user_id) references user(id);
-- 11). SPU和点赞是1-n关系
alter table praise modify spu_id int unsigned;
alter table praise add constraint fk_praise_spu_id foreign key(spu_id) references spu(id);
-- 12). 用户和评论是1-n关系
alter table `comment` modify user_id int unsigned;
alter table `comment` add constraint fk_comment_user_id foreign key(user_id) references user(id);
-- 13). SPU和评论是1-n关系
alter table `comment` modify spu_id int unsigned;
alter table `comment` add constraint fk_comment_spu_id foreign key(spu_id) references spu(id);
-- 14). 评论和评论回复是1-n关系(自连接)
alter table `comment` modify parent_id int unsigned;
alter table `comment` add constraint fk_praise_parent_id foreign key(parent_id) references `comment`(id);
-- 15). 用户和收货地址是1-n关系
alter table address modify user_id int unsigned;
alter table address add constraint fk_address_user_id foreign key(user_id) references user(id);


alter table address add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table address add column gmt_modified datetime default null;
alter table brand add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table brand add column gmt_modified datetime default null;
alter table category add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table category add column gmt_modified datetime default null;
alter table category_brand_relation add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table category_brand_relation add column gmt_modified datetime default null;
alter table `comment` add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table `comment` add column gmt_modified datetime default null;
alter table praise add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table praise add column gmt_modified datetime default null;
alter table property add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table property add column gmt_modified datetime default null;
alter table property_option add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table property_option add column gmt_modified datetime default null;
alter table sku add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table sku add column gmt_modified datetime default null;
alter table sku_property_option_relation add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table sku_property_option_relation add column gmt_modified datetime default null;
alter table sku_specification_option_relation add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table sku_specification_option_relation add column gmt_modified datetime default null;
alter table specification add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table specification add column gmt_modified datetime default null;
alter table specification_option add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table specification_option add column gmt_modified datetime default null;
alter table spu add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table spu add column gmt_modified datetime default null;
alter table user add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table user add column gmt_modified datetime default null;
alter table admin add column gmt_create datetime default CURRENT_TIMESTAMP;
alter table admin add column gmt_modified datetime default null;


alter table address add column is_delete tinyint default 0;
alter table brand add column is_delete tinyint default 0;
alter table category add column is_delete tinyint default 0;
alter table category_brand_relation add column is_delete tinyint default 0;
alter table `comment` add column is_delete tinyint default 0;
alter table praise add column is_delete tinyint default 0;
alter table property add column is_delete tinyint default 0;
alter table property_option add column is_delete tinyint default 0;
alter table sku add column is_delete tinyint default 0;
alter table sku_property_option_relation add column is_delete tinyint default 0;
alter table sku_specification_option_relation add column is_delete tinyint default 0;
alter table specification add column is_delete tinyint default 0;
alter table specification_option add column is_delete tinyint default 0;
alter table spu add column is_delete tinyint default 0;
alter table user add column is_delete tinyint default 0;
alter table admin add column is_delete tinyint default 0;