-- Cache to Buffer variable as much as possible

-- DROP DATABASE IF EXISTS shopping;
-- CREATE DATABASE shop;


CREATE TYPE message_type AS ENUM('text', 'image', 'voice', 'video');
CREATE TYPE role AS ENUM('user', 'admin' );
CREATE TYPE verification_type AS ENUM('first', 'old');
CREATE TYPE progress AS ENUM('created', 'abandoned', 'paid', 'wait-to-send', 'sending', 'received', 'commented', 'canceled', 'canceling'); -- \dT+ progress
-- TODO Create table for record the login and view product time

CREATE TABLE client (		-- user is a KEYWORD of PSQL
	id serial PRIMARY KEY,
	name varchar(40),
	state jsonb,	
	location varchar(30),
	openid char(28),
	info jsonb,
	time_create timestamp DEFAULT localtimestamp,
	time_recent timestamp
);

-- CREATE TABLE client_info (
-- 	uid serial REFERENCES client(id) ON DELETE CASCADE,
-- 	openid char(28),
-- 	info jsonb,
-- 	time timestamp DEFAULT localtimestamp
-- );

-- CREATE TABLE verification (
-- 	id serial PRIMARY KEY,
-- 	code char(6) NOT NULL,
-- 	phone char(11),
-- 	type varchar(20),
-- 	ip char(20),
-- 	location varchar(30),
-- 	time_create timestamp DEFAULT localtimestamp,
-- 	time_disable timestamp,
-- 	time_verify timestamp
-- );

-- CREATE TABLE device (
-- 	id serial PRIMARY KEY,
-- 	uid serial REFERENCES client(id) ON DELETE CASCADE,
-- 	agent varchar(200),
-- 	wechat_id varchar(64),
-- 	props jsonb,
-- 	time timestamp DEFAULT localtimestamp
-- );

-- ALTER table visit_history alter column ip type char(16);
CREATE TABLE visit_history (
	id serial PRIMARY KEY,
	uid int REFERENCES client(id) ON DELETE CASCADE,
	location varchar(30),
	ip char(16),
	time timestamp DEFAULT localtimestamp,
	time_leave timestamp
);
ALTER TABLE visit_history ADD COLUMN time_leave timestamp;

-- CREATE TABLE user_in_out (
-- 	id serial PRIMARY KEY,
-- 	uid int,		--For testing, not relative
-- 	time_in timestamp,
-- 	time_out timestamp
-- );
-- COMMENT ON TABLE user_in_out IS 'Log the connect/disconnet time of user';

CREATE TABLE admin (
	id serial PRIMARY KEY,
	name varchar(40),
	phone char(11),
	avatar varchar(120),
	password varchar(30) NOT NULL,
	info jsonb,
	time timestamp DEFAULT localtimestamp
);
CREATE TABLE admin_user_comment (
	id serial PRIMARY KEY,
	aid int REFERENCES admin(id),
	uid serial REFERENCES client(id) ON DELETE CASCADE,
	content text,
	time timestamp DEFAULT localtimestamp
);

CREATE TABLE keyword (			-- tags
	id serial PRIMARY KEY,
	name varchar(40) NOT NULL,
	creator int REFERENCES admin(id),
	info jsonb,
	time timestamp DEFAULT localtimestamp
);

CREATE TABLE request_keyword (		-- Record the request of Keyword API for data statistics  
	id serial PRIMARY KEY,
	uid int REFERENCES client(id) ON DELETE CASCADE,	-- Maybe a fresh client, not logged in
	kid int REFERENCES keyword(id),
	time timestamp DEFAULT localtimestamp
); 

CREATE TABLE product (
	id serial PRIMARY KEY,
	creator int REFERENCES admin(id),
	title varchar(60) NOT NULL,
	price money NOT NULL,
	unit varchar(10) DEFAULT '元/斤',
	content jsonb,
	info jsonb,
	time timestamp DEFAULT localtimestamp
);
COMMENT ON COLUMN product.content IS 'An array/list of text and images';  

CREATE TABLE public.product_tags
(
    pid integer NOT NULL,
    tid integer NOT NULL,
    creator integer,
    "time" timestamp without time zone DEFAULT now(),
    id integer NOT NULL DEFAULT nextval('product_tags_id_seq'::regclass),
    CONSTRAINT product_tags_pkey PRIMARY KEY (id),
    CONSTRAINT product_tag_uniq UNIQUE (tid, pid),
    CONSTRAINT fkey_aid FOREIGN KEY (creator)
        REFERENCES public.admin (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fkey_pid FOREIGN KEY (pid)
        REFERENCES public.product (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fkey_tid FOREIGN KEY (tid)
        REFERENCES public.tag (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

COMMENT ON TABLE product_tags IS 'For searching relative products';  

CREATE TABLE public.product_edit
(
    id integer NOT NULL,
    aid integer NOT NULL,
    pid integer NOT NULL,
    title character varying(60) COLLATE pg_catalog."default",
    price money,
    unit character varying(10) COLLATE pg_catalog."default",
    content jsonb,
    addition jsonb,
    comment text COLLATE pg_catalog."default",
    edit_comment text COLLATE pg_catalog."default",
    "time" timestamp without time zone DEFAULT now(),
    CONSTRAINT product_edit_pkey PRIMARY KEY (id),
    CONSTRAINT product_edit_aid_fkey FOREIGN KEY (aid)
        REFERENCES public.admin (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT product_update_pid_fkey FOREIGN KEY (pid)
        REFERENCES public.product (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

COMMENT ON COLUMN public.product_edit.edit_comment
    IS 'Write by admin, what changed? '; 

CREATE TABLE buy (
	id serial PRIMARY KEY,
	uid int REFERENCES client(id) ON DELETE CASCADE,
	pid int REFERENCES product(id),
	address text,
	contact text,
	remark text,	-- User to admin
	star smallint,	-- 1~5
	current progress DEFAULT 'created',
	time timestamp DEFAULT localtimestamp
); 


CREATE TABLE order_state (
	id serial PRIMARY KEY,
	oid int REFERENCES buy(id),
	action progress NOT NULL,
	current boolean DEFAULT true,
	time timestamp DEFAULT localtimestamp
); 

CREATE TABLE transport (
	id serial PRIMARY KEY,
	oid int REFERENCES buy(id),
	aid int REFERENCES admin(id),
	description text,
	phone char(11),
	time_receive timestamp,
	time timestamp DEFAULT localtimestamp
); 

CREATE TABLE cancel (
	id serial PRIMARY KEY,
	oid int REFERENCES buy(id) NOT NULL,
	aid int REFERENCES admin(id),
	reason text,
	admin_remark text,
	accept boolean,
	time_accept timestamp,
	time_refuse timestamp,
	time timestamp DEFAULT localtimestamp
); 

CREATE TABLE transaction (
	id serial PRIMARY KEY,
	uid int REFERENCES client(id) ON DELETE CASCADE,
	pid int REFERENCES product(id),
	oid int REFERENCES buy(id),
	wx_pid int, -- WeChat https://pay.weixin.qq.com/wiki/doc/api/wxa/wxa_api.php?chapter=3_1
	amount money,
	time_finish timestamp,
	time_refund timestamp,
	time timestamp DEFAULT localtimestamp
);

CREATE TABLE favor (
	id serial PRIMARY KEY,
	uid int REFERENCES client(id) ON DELETE CASCADE,
	pid int REFERENCES product(id),
	time timestamp DEFAULT localtimestamp,
	UNIQUE(uid, pid)
); 
CREATE TABLE view_product (
	id serial PRIMARY KEY,
	uid int REFERENCES client(id) ON DELETE CASCADE,	-- Maybe a fresh client, not logged in
	pid int REFERENCES product(id),
	location varchar(30),
	ip char(20),
	time timestamp DEFAULT localtimestamp
); 

COMMENT ON COLUMN buy.current IS 'An array/list of text and images';  

CREATE TABLE comment (
	id serial PRIMARY KEY,
	uid int REFERENCES client(id) ON DELETE CASCADE,
	pid int REFERENCES product(id),
	content text DEFAULT NULL,
	time timestamp DEFAULT localtimestamp
);
COMMENT ON TABLE comment IS 'Create it after user ';  

COMMENT ON COLUMN comment.content IS 'An array/list of text and images';  

CREATE TABLE comment_image (
	id serial PRIMARY KEY,
	mid int REFERENCES comment(id),
	uid int REFERENCES client(id) ON DELETE CASCADE,
	src text NOT NULL,
	time timestamp DEFAULT localtimestamp
);
CREATE TABLE product_image (
	id serial PRIMARY KEY,
	pid int REFERENCES product(id),
	aid int REFERENCES admin(id),
	src text NOT NULL,
	time timestamp DEFAULT localtimestamp
);


CREATE TABLE chat (
	id serial PRIMARY KEY,
	uid int REFERENCES client(id) NOT NULL,
	aid int REFERENCES admin(id),
	pid int REFERENCES product(id),	
	sender role NOT NULL,
	content text NOT NULL,
	type message_type DEFAULT 'text',
	time timestamp DEFAULT localtimestamp,
	near_last boolean		-- Decide necessary to display the time
);
COMMENT ON COLUMN public.chat.near_last
    IS 'Decide necessary to display the time?';

COMMENT ON TABLE chat 
	IS 'There must be a uid no matter it is sender or receiver, aid will only valuable when `sender = admin`, pid is the product the chat creates from ';