create database tvseries;

/** *******************************************************
 TABLES (section02-04中使用) 
 *************************************************** **/
create table tv_series(
 id serial primary key,
 name varchar(50) not null,
 season_count int not null,
 origin_release date not null,
 status smallint not null default 0,
 delete_reason varchar(100) null
);

create table tv_character(
 id serial primary key,
 tv_series_id int not null,
 name varchar(50) not null,
 photo varchar(100) null
);


/** *******************************************************
 section-05中用到的表和数据 
 *************************************************** **/
create table person(
 id int primary key,
 name varchar(50) not null,
 birthday timestamp default now()
);


/** *******************************************************
 section-06中用到的表及数据 
 *************************************************** **/
create table product(
	id char(5) not null primary key,
	name varchar(20) not null,
	price int not null,
	images varchar(100)[],
	specs JSON null
);

/** 枚举类型的ordertype **/
CREATE TYPE ORDERTYPE AS ENUM ('RETAIL', 'WHOLESALE');

create table order_main(
 id serial primary key,
 order_date timestamp not null,
 consignee varchar(20) not null,
 phone varchar(20) not null,
 province varchar(50) null,
 city varchar(50) null,
 district varchar(50) null,
 address varchar(50) null,
 order_type ORDERTYPE,
 status int default 0
);

create table order_detail(
 id serial primary key,
 order_id int not null,
 product_id varchar(10) not null,
 product_name varchar(100) not null,
 num int not null,
 price int not null
);

insert into product(id, name, price, specs, images) values('E0001', 'iPhone X手机模型', 10000, '{"颜色":"黑色", "材质":"塑料"}', '{"a1.jpg", "a2.jpg", "a3.jpg"}');
insert into product(id, name, price, specs, images) values('B0002', '双肩电脑包', 25000, '{"产地":"中国", "电脑夹层":"有"}', null);

insert into order_main(order_date, consignee, phone, province, city, district, address, order_type, status)
 values('2017/4/28 12:13:14', '郝尤乾', '13300001111', '上海', '上海', '徐汇', '漕宝路22222号', 'RETAIL', 0);
insert into order_detail(order_id, product_id, product_name, num, price)
 values(1, 'E0001', 'iPhone X手机模型', 1, 10000);
insert into order_detail(order_id, product_id, product_name, num, price)
 values(1, 'B0002', '双肩电脑包', 1, 25000);