数据库课程实践

---

• 天津大学 2024 数据库课程实践
• Lab Member：海棠未雨，梨花先雪，流萤染夏
• 最终成绩 91 分捏 😋

---

报告如下：

答辩PPT如下：

源码如下：

--先删关系表
--交易系统关系表
DROP TABLE IF EXISTS sell;
DROP TABLE IF EXISTS hold_ ;
DROP TABLE IF EXISTS order_ ;
--用户系统关系表
DROP TABLE IF EXISTS consumer_get_coupon ;
DROP TABLE IF EXISTS consumer_recieve_redpacket ;
DROP TABLE IF EXISTS consumer_register_address ;
DROP TABLE IF EXISTS friendship ;
DROP TABLE IF EXISTS fan ;
DROP TABLE IF EXISTS user_focus_user ;
DROP TABLE IF EXISTS manage_subaccount ;
DROP TABLE IF EXISTS shopping_cart ;
DROP TABLE IF EXISTS collect ;
DROP TABLE IF EXISTS watching_history ;
--聊天系统关系表
DROP TABLE IF EXISTS friend_and_friend ;
DROP TABLE IF EXISTS seller_and_agent ;
DROP TABLE IF EXISTS consumer_and_agent ;
DROP TABLE IF EXISTS consumer_and_seller ;
DROP TABLE IF EXISTS member_and_member ;
DROP TABLE IF EXISTS group_members ;
DROP TABLE IF EXISTS friend_request ;
DROP TABLE IF EXISTS group_request ;
DROP TABLE IF EXISTS reply ;
DROP TABLE IF EXISTS consumer_give_evaluation ;
DROP TABLE IF EXISTS consumer_ask_question ;
DROP TABLE IF EXISTS consumer_reply_question ;
DROP TABLE IF EXISTS consumer_focus_question ;
DROP TABLE IF EXISTS consumer_have_archive ;

--再删实体表
--交易系统实体表
DROP TABLE IF EXISTS mark ;
DROP TABLE IF EXISTS logistics ;
DROP TABLE IF EXISTS food ;
DROP TABLE IF EXISTS cloth ;
DROP TABLE IF EXISTS ele_appliment ;
DROP TABLE IF EXISTS ele_product ;
DROP TABLE IF EXISTS makeup ;
DROP TABLE IF EXISTS sport ;
DROP TABLE IF EXISTS toy ;
DROP TABLE IF EXISTS daily_necessarity ;
DROP TABLE IF EXISTS discount ;
DROP TABLE IF EXISTS store ;
DROP TABLE IF EXISTS commodity ;
DROP TABLE IF EXISTS historical_price ;
--用户系统实体表
DROP TABLE IF EXISTS red_packet ;
DROP TABLE IF EXISTS taobao_history ;
DROP TABLE IF EXISTS address ;
DROP TABLE IF EXISTS archive ;
DROP TABLE IF EXISTS coupon ;
DROP TABLE IF EXISTS users ;
DROP TABLE IF EXISTS consumer ;
DROP TABLE IF EXISTS seller ;
--聊天系统实体表
DROP TABLE IF EXISTS official_agent ;
DROP TABLE IF EXISTS groups ;
DROP TABLE IF EXISTS message ;
DROP TABLE IF EXISTS conversation_message ;
DROP TABLE IF EXISTS group_message ;
DROP TABLE IF EXISTS request ;
DROP TABLE IF EXISTS evaluation ;
DROP TABLE IF EXISTS comments ;
DROP TABLE IF EXISTS question ;



--创建店铺表
CREATE TABLE store(
   store_id VARCHAR(20) PRIMARY KEY,
   store_name VARCHAR(30) NOT NULL,
   tag VARCHAR(20),
   profile_photo  VARCHAR(500),
   seller_id VARCHAR(20) NOT NULL,
   address VARCHAR(50),
   sales DOUBLE PRECISION,
   vis_num INT,
   ord_num INT,
   tr_rate INT,
   candidate_num INT,
   Lv INT,
   credit INT,
   ct_time TIMESTAMP,
   sub_account INT
);

--创建商品表
CREATE TABLE commodity(
    com_id VARCHAR(20) PRIMARY KEY,
    com_name VARCHAR(30) NOT NULL,
    category VARCHAR(20),
    price DOUBLE PRECISION,
	dis_price DOUBLE PRECISION,
    num INT,
    description VARCHAR(500),
    detail VARCHAR(2000),
    after_sale VARCHAR(100),
    up_time TIMESTAMP,
    com_image VARCHAR(500)
);

--创建活动表
CREATE TABLE discount(
    dis_id VARCHAR(20) PRIMARY KEY,
    st_time TIMESTAMP,
    end_time TIMESTAMP,
    allowance DOUBLE PRECISION
);

--创建商品历史价格表（弱实体）
CREATE TABLE historical_price(
	com_id VARCHAR(20) PRIMARY KEY,
	time TIMESTAMP,
	price DOUBLE PRECISION
);

--创建店铺评分（弱实体）
CREATE TABLE mark(
    store_id VARCHAR(20) PRIMARY KEY,
    quality DOUBLE PRECISION,
    service DOUBLE PRECISION,
    distribution DOUBLE PRECISION,
    com_score DOUBLE PRECISION
);

--创建商品物流（弱实体）
CREATE TABLE logistics(
    com_id VARCHAR(20) PRIMARY KEY,
    deadline TIMESTAMP,
    price DOUBLE PRECISION,
    comp_name VARCHAR(100),
    send_addr VARCHAR(50),
    receive_addr VARCHAR(50)
);

--商品子类表
CREATE TABLE food(
    com_id VARCHAR(20) PRIMARY KEY,
    com_name VARCHAR(30) NOT NULL,
    category VARCHAR(20),
    price DOUBLE PRECISION,
	dis_price DOUBLE PRECISION,
    num INT,
    description VARCHAR(500),
    detail VARCHAR(2000),
    after_sale VARCHAR(100),
    up_time TIMESTAMP,
    com_image VARCHAR(500),
    shelf_life INT,
    weight VARCHAR(20),
    taste VARCHAR(20)
);
CREATE table cloth(
    com_id VARCHAR(20) PRIMARY KEY,
    com_name VARCHAR(30) NOT NULL,
    category VARCHAR(20),
    price DOUBLE PRECISION,
	dis_price DOUBLE PRECISION,
    num INT,
    description VARCHAR(500),
    detail VARCHAR(2000),
    after_sale VARCHAR(100),
    up_time TIMESTAMP,
    com_image VARCHAR(500),
    type VARCHAR(20),
    size VARCHAR(20)
);


CREATE TABLE ele_appliment(
    com_id VARCHAR(20) PRIMARY KEY,
    com_name VARCHAR(30) NOT NULL,
    category VARCHAR(20),
    price DOUBLE PRECISION,
	dis_price DOUBLE PRECISION,
    num INT,
    description VARCHAR(500),
    detail VARCHAR(2000),
    after_sale VARCHAR(100),
    up_time TIMESTAMP,
    com_image VARCHAR(500),
    paymentway VARCHAR(20),
    type VARCHAR(20),
    memory VARCHAR(20),
    safegaurd VARCHAR(20),
    net_type VARCHAR(20)
);
CREATE TABLE ele_product(
    com_id VARCHAR(20) PRIMARY KEY,
    com_name VARCHAR(30) NOT NULL,
    category VARCHAR(20),
    price DOUBLE PRECISION,
	dis_price DOUBLE PRECISION,
    num INT,
    description VARCHAR(500),
    detail VARCHAR(2000),
    after_sale VARCHAR(100),
    up_time TIMESTAMP,
    com_image VARCHAR(500),
    paymentway VARCHAR(20),
    type VARCHAR(20),
    safegaurd VARCHAR(20)
);

CREATE TABLE makeup(
    com_id VARCHAR(20) PRIMARY KEY,
    com_name VARCHAR(30) NOT NULL,
    category VARCHAR(20),
    price DOUBLE PRECISION,
	dis_price DOUBLE PRECISION,
    num INT,
    description VARCHAR(500),
    detail VARCHAR(2000),
    after_sale VARCHAR(100),
    up_time TIMESTAMP,
    com_image VARCHAR(500),
    type VARCHAR(20),
    weight VARCHAR(20)
);

CREATE TABLE sport(
    com_id VARCHAR(20) PRIMARY KEY,
    com_name VARCHAR(30) NOT NULL,
    category VARCHAR(20),
    price DOUBLE PRECISION,
	dis_price DOUBLE PRECISION,
    num INT,
    description VARCHAR(500),
    detail VARCHAR(2000),
    after_sale VARCHAR(100),
    up_time TIMESTAMP,
    com_image VARCHAR(500),
    type VARCHAR(20),
    size VARCHAR(20)
);

CREATE TABLE toy(
    com_id VARCHAR(20) PRIMARY KEY,
    com_name VARCHAR(30) NOT NULL,
    category VARCHAR(20),
    price DOUBLE PRECISION,
	dis_price DOUBLE PRECISION,
    num INT,
    description VARCHAR(500),
    detail VARCHAR(2000),
    after_sale VARCHAR(100),
    up_time TIMESTAMP,
    com_image VARCHAR(500),
    type VARCHAR(20),
    suit_age INT
);

CREATE TABLE daily_necessarity(
    com_id VARCHAR(20) PRIMARY KEY,
    com_name VARCHAR(30) NOT NULL,
    category VARCHAR(20),
    price DOUBLE PRECISION,
	dis_price DOUBLE PRECISION,
    num INT,
    description VARCHAR(500),
    detail VARCHAR(2000),
    after_sale VARCHAR(100),
    up_time TIMESTAMP,
    com_image VARCHAR(500),
    type VARCHAR(20),
    weight VARCHAR(20)
);

CREATE TABLE other(
    com_id VARCHAR(20) PRIMARY KEY,
    com_name VARCHAR(30) NOT NULL,
    category VARCHAR(20),
    price DOUBLE PRECISION,
	dis_price DOUBLE PRECISION,
    num INT,
    detail VARCHAR(2000),
    after_sale VARCHAR(100),
    up_time TIMESTAMP,
    com_image VARCHAR(500),
    description VARCHAR(200)
);


--交易系统的关系

--店铺和商品的关系

CREATE TABLE sell(
    store_id VARCHAR(20),
    com_id VARCHAR(20),
    st_time TIMESTAMP,
    PRIMARY KEY (store_id,com_id),
    FOREIGN KEY (store_id) REFERENCES store (store_id),
    FOREIGN KEY (com_id) REFERENCES commodity (com_id)
);

--商品和活动的关系

CREATE TABLE hold_(
    com_id VARCHAR(20),
    dis_id VARCHAR(20),
    PRIMARY KEY (com_id,dis_id),
    FOREIGN KEY (com_id) REFERENCES commodity (com_id),
    FOREIGN KEY (dis_id) REFERENCES discount (dis_id)
);

--创建订单表

CREATE TABLE order_(
    order_id VARCHAR(20) PRIMARY KEY,
    com_id VARCHAR(20),
    user_id VARCHAR(20),
    real_pay DOUBLE PRECISION,
    ct_time TIMESTAMP,
    pay_time TIMESTAMP,
    state VARCHAR(20),
    pickup_code VARCHAR(20),
    description VARCHAR(20),
    com_num INT
);

-- 用户系统
-- 创建用户表
CREATE TABLE users (
    user_id VARCHAR(20) PRIMARY KEY,
    user_name VARCHAR(30) NULL DEFAULT NULL,
    password_ VARCHAR(20) NOT NULL CHECK (LENGTH(password_) >= 8),
    gender CHAR(1) NOT NULL CHECK (gender IN ('M','F')),
    telephone CHAR(11) NOT NULL,
	type_ VARCHAR(10) NOT NULL,CHECK (type_ IN ('consumer','seller'))	
);
-- 创建消费者表
CREATE TABLE consumer (
    user_id VARCHAR(20) NOT NULL PRIMARY KEY, 
    user_name VARCHAR(30) NULL DEFAULT NULL,
    password_ VARCHAR(20) NOT NULL CHECK (LENGTH(password_) >= 8),
    gender CHAR(1) NOT NULL CHECK (gender IN ('M','F')),
    telephone CHAR(11) NOT NULL,
    membership BOOLEAN NOT NULL,
    credit INT NOT NULL CHECK (credit >= 0),
    headshot VARCHAR(500) NULL DEFAULT NULL, -- 存储用户头像的地址
    fan_number INT NOT NULL CHECK (fan_number >= 0),
    focus_number INT NOT NULL CHECK (focus_number >= 0),
    likes INT NOT NULL CHECK (likes >= 0),
    change DOUBLE PRECISION NOT NULL CHECK (change >= 0), -- 零钱
    shopping_coin INT NOT NULL CHECK (shopping_coin >= 0),
    alipay VARCHAR(20) NULL DEFAULT NULL,
	type_ VARCHAR(10) NOT NULL,CHECK (type_ = 'consumer'),
	shopping_cart VARCHAR(3000) NULL DEFAULT NULL
);
-- 创建卖家表
CREATE TABLE seller (
    user_id VARCHAR(20) NOT NULL PRIMARY KEY, 
    user_name VARCHAR(30) NULL DEFAULT NULL,
    password_ VARCHAR(20) NOT NULL CHECK (LENGTH(password_) >= 8),
    gender CHAR(1) NOT NULL CHECK (gender IN ('M','F')),
    telephone CHAR(11) NOT NULL,
	type_ VARCHAR(10) NOT NULL,CHECK (type_ = 'seller'),
    service_authority BOOLEAN NOT NULL,
    operator_authority BOOLEAN NOT NULL
);

-- 创建红包表
CREATE TABLE red_packet (
    red_packet_id SERIAL NOT NULL PRIMARY KEY,
    name_ VARCHAR(30) NOT NULL,
    value_ DOUBLE PRECISION NOT NULL,
    condition_ VARCHAR(20) NOT NULL,
    end_date DATE NOT NULL
);

-- 创建优惠券表
CREATE TABLE coupon (
    coupon_id SERIAL NOT NULL PRIMARY KEY,
    store_id VARCHAR(20) NOT NULL, 
    value_ DOUBLE PRECISION NOT NULL,
    condition_ VARCHAR(20) NOT NULL,
    end_date DATE NOT NULL,
	FOREIGN KEY (store_id) REFERENCES store (store_id)
);

-- 创建淘宝历程表
CREATE TABLE taobao_history (
    user_id VARCHAR(20) NOT NULL,
    register_date DATE NOT NULL,
    total_days INT NOT NULL CHECK (total_days >= 0),
    save_money DOUBLE PRECISION NOT NULL CHECK (save_money >= 0),
    month_spend_money DOUBLE PRECISION NOT NULL CHECK (month_spend_money >= 0),
	PRIMARY KEY (user_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id)
);

-- 创建收货地址表
CREATE TABLE address (
    address_id SERIAL NOT NULL PRIMARY KEY,
    name_ VARCHAR(30) NOT NULL,
    telephone CHAR(11) NOT NULL,
    address VARCHAR(50) NOT NULL
);

-- 创建档案表
CREATE TABLE archive (
    archive_id SERIAL NOT NULL PRIMARY KEY,
    height DOUBLE PRECISION NOT NULL CHECK (height > 0),
    weight DOUBLE PRECISION NOT NULL CHECK (weight > 0),
    shoe_size DOUBLE PRECISION NOT NULL CHECK (shoe_size >= 16 and shoe_size <= 45)
);

-- 创建好友关系表
CREATE TABLE friendship (
    user1_id VARCHAR(20) NOT NULL,
    user2_id VARCHAR(20) NOT NULL,
    accept_date DATE NOT NULL,
    PRIMARY KEY (user1_id, user2_id),
	FOREIGN KEY (user1_id) REFERENCES consumer (user_id),
	FOREIGN KEY (user2_id) REFERENCES consumer (user_id)
);

-- 创建收货地址总表
CREATE TABLE consumer_register_address (
    user_id VARCHAR(20) NOT NULL,
    address_id SERIAL NOT NULL,
	common_address BOOLEAN NOT NULL,
	PRIMARY KEY (user_id, address_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id),
    FOREIGN KEY (address_id) REFERENCES address (address_id)
);

-- 创建粉丝列表总表
CREATE TABLE fan (
    user_id VARCHAR(20) NOT NULL,
    fan_id VARCHAR(20) NOT NULL,
	PRIMARY KEY (user_id, fan_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id),
    FOREIGN KEY (fan_id) REFERENCES consumer (user_id)
);

-- 创建关注列表总表
CREATE TABLE user_focus_user (
    user_id VARCHAR(20) NOT NULL,
    focus_id VARCHAR(20) NOT NULL,
	PRIMARY KEY (user_id, focus_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id),
    FOREIGN KEY (focus_id) REFERENCES consumer (user_id)
);

-- 创建用户红包关系表
CREATE TABLE consumer_recieve_redpacket (
    user_id VARCHAR(20) NOT NULL,
    red_packet_id SERIAL NOT NULL,
	PRIMARY KEY (user_id, red_packet_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id),
    FOREIGN KEY (red_packet_id) REFERENCES red_packet (red_packet_id)
);

-- 创建用户优惠券关系表
CREATE TABLE consumer_get_coupon (
    user_id VARCHAR(20) NOT NULL,
    coupon_id SERIAL NOT NULL,
	PRIMARY KEY (user_id, coupon_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id),
    FOREIGN KEY (coupon_id) REFERENCES coupon (coupon_id)
);

-- 创建主账号和子账号关系表
CREATE TABLE manage_subaccount (
    main_user_id VARCHAR(20) NOT NULL,
    sub_user_id VARCHAR(20) NOT NULL,
	PRIMARY KEY (main_user_id,sub_user_id),
    FOREIGN KEY (main_user_id) REFERENCES seller (user_id),
    FOREIGN KEY (sub_user_id) REFERENCES seller (user_id)
);

--用户档案索引表
CREATE TABLE consumer_have_archive (
    user_id VARCHAR(20) NOT NULL,
    archive_id SERIAL NOT NULL,
	PRIMARY KEY (user_id,archive_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id),
    FOREIGN KEY (archive_id) REFERENCES archive (archive_id)
);

--消费者收藏表
CREATE TABLE collect (
    user_id VARCHAR(20) NOT NULL,
    com_id VARCHAR(20) NOT NULL,
    collect_time DATE NOT NULL,
    PRIMARY KEY (user_id,com_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id),
    FOREIGN KEY (com_id) REFERENCES commodity (com_id)
);

--消费者浏览记录表
CREATE TABLE watching_history (
    user_id VARCHAR(20) NOT NULL,
    com_id VARCHAR(20) NOT NULL,
    watch_time DATE NOT NULL,
    PRIMARY KEY (user_id,com_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id),
    FOREIGN KEY (com_id) REFERENCES commodity (com_id)
);

-- 聊天反馈系统
-- 创建官方客服总表
CREATE TABLE official_agent(
	official_agent_id VARCHAR(20) PRIMARY KEY,
	telephone VARCHAR(20) NOT NULL,
	start_date TIMESTAMP NOT NULL,
	end_date TIMESTAMP NOT NULL
);

-- 创建群组总表
CREATE TABLE groups(
	group_id VARCHAR(20) PRIMARY KEY,
	group_name VARCHAR(30) NOT NULL,
	created_date TIMESTAMP DEFAULT NULL,
	description VARCHAR(100) DEFAULT NULL,
	member_num INT CHECK(member_num >= 0)
);

-- 创建消息总表
CREATE TABLE message(
	message_id VARCHAR(20) PRIMARY KEY,
	message_type VARCHAR(10) CHECK (message_type IN ('文本','语音','图片','视频')),
	message_content VARCHAR(500) NOT NULL,
	message_date TIMESTAMP NOT NULL
);

-- 创建对话消息总表
CREATE TABLE conversation_message(
	message_id VARCHAR(20) PRIMARY KEY,
	message_type VARCHAR(10) CHECK (message_type IN ('文本','语音','图片','视频')),
	message_content VARCHAR(500) NOT NULL,
	message_date TIMESTAMP NOT NULL,
	isread BOOLEAN  NOT NULL
);

-- 创建群组消息总表
CREATE TABLE group_message(
	message_id VARCHAR(20) PRIMARY KEY,
	message_type VARCHAR(10) CHECK (message_type IN ('文本','语音','图片','视频')),
	message_content VARCHAR(500) NOT NULL,
	message_date TIMESTAMP NOT NULL
);

-- 创建请求总表
CREATE TABLE request(
	request_id VARCHAR(20) PRIMARY KEY,
	request_status VARCHAR(10) CHECK (request_status IN ('未处理','已接受','已拒绝')),
	request_description VARCHAR(100) DEFAULT NULL,
	request_date TIMESTAMP NOT NULL
);

-- 创建用户评价总表
CREATE TABLE evaluation(
	evaluation_id VARCHAR(20) PRIMARY KEY,
	evaluation_content_type VARCHAR(10) CHECK (evaluation_content_type IN ('文本','图片','视频')),
	evaluation_content VARCHAR(500) NOT NULL,
	isanonymous BOOLEAN NOT NULL,
	evaluation_type CHAR(1) CHECK (evaluation_type IN ('好','差')),
	evaluated_date TIMESTAMP NOT NULL,
	isfollow BOOLEAN NOT NULL,
	likes INT CHECK (likes >= 0),
	comment_num INT CHECK (comment_num >=0),
	comprehensive_evaluation INT check(comprehensive_evaluation IN (0,1,2,3,4,5)),
	description_match INT check(description_match IN (0,1,2,3,4,5)),
	logistics_service INT check(logistics_service IN (0,1,2,3,4,5)),
	service_attitude INT check(service_attitude IN (0,1,2,3,4,5))
);

-- 创建用户评论总表(弱实体)
CREATE TABLE comments(
	evaluation_id VARCHAR(20),
	user_id VARCHAR(30),
	comment_date TIMESTAMP,
	comment_type VARCHAR(10) CHECK (comment_type IN ('文本','图片','视频')),
	comment_content VARCHAR(500) NOT NULL,
	likes INT,
	PRIMARY KEY(evaluation_id, user_id, comment_date),
	FOREIGN KEY (evaluation_id) REFERENCES evaluation (evaluation_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id)
);
-- 创建问题总表
CREATE TABLE question(
	question_id VARCHAR(20) PRIMARY KEY,
	question_content VARCHAR(500) NOT NULL,
	question_date TIMESTAMP DEFAULT NULL,
	reply_num INT CHECK(reply_num >= 0)
);
-- 创建好友聊天消息索引表
CREATE TABLE friend_and_friend(
	sender_id VARCHAR(20),
	receiver_id VARCHAR(20),
	message_id VARCHAR(20),
	PRIMARY KEY(sender_id, receiver_id, message_id),
	FOREIGN KEY (sender_id) REFERENCES consumer (user_id),
    FOREIGN KEY (receiver_id) REFERENCES consumer (user_id),
	FOREIGN KEY (message_id) REFERENCES conversation_message (message_id)
);
-- 创建卖家和客服消息索引表
CREATE TABLE seller_and_agent(
	seller_id VARCHAR(20),
	official_agent_id VARCHAR(20),
	message_id VARCHAR(20),
	sender BOOLEAN NOT NULL, --0表示卖家发的，1表示客服发的
	PRIMARY KEY(seller_id, official_agent_id, message_id),
	FOREIGN KEY (seller_id) REFERENCES seller (user_id),
    FOREIGN KEY (official_agent_id) REFERENCES official_agent (official_agent_id),
	FOREIGN KEY (message_id) REFERENCES conversation_message (message_id)
);


-- 创建消费者和客服消息索引表
CREATE TABLE consumer_and_agent(
	consumer_id VARCHAR(20),
	official_agent_id VARCHAR(20),
	message_id VARCHAR(20),
	sender BOOLEAN NOT NULL, --0表示消费者发的，1表示客服发的
	PRIMARY KEY(consumer_id, official_agent_id, message_id),
	FOREIGN KEY (consumer_id) REFERENCES consumer (user_id),
    FOREIGN KEY (official_agent_id) REFERENCES official_agent (official_agent_id),
	FOREIGN KEY (message_id) REFERENCES conversation_message (message_id)
);

-- 创建消费者和卖家消息索引表
CREATE TABLE consumer_and_seller(
	consumer_id VARCHAR(20),
	seller_id VARCHAR(20),
	message_id VARCHAR(20),
	sender BOOLEAN NOT NULL, --0表示消费者发的，1表示卖家发的
	PRIMARY KEY(consumer_id, seller_id, message_id),
	FOREIGN KEY (consumer_id) REFERENCES consumer (user_id),
    FOREIGN KEY (seller_id) REFERENCES seller (user_id),
	FOREIGN KEY (message_id) REFERENCES conversation_message (message_id)
);

-- 创建群组消息索引表
CREATE TABLE member_and_member(
	group_id VARCHAR(20),
	sender_id VARCHAR(20),
	message_id VARCHAR(20),
	PRIMARY KEY(group_id, sender_id, message_id),
	FOREIGN KEY (group_id) REFERENCES groups (group_id),
    FOREIGN KEY (sender_id) REFERENCES consumer (user_id),
	FOREIGN KEY (message_id) REFERENCES group_message (message_id)
);

-- 创建群组成员表
CREATE TABLE group_members(
	group_id VARCHAR(20),
	user_id VARCHAR(20),
	role VARCHAR(10) CHECK (role IN ('群主','管理员','普通成员')),
	PRIMARY KEY(group_id, user_id),
	FOREIGN KEY (group_id) REFERENCES groups (group_id),
    FOREIGN KEY (user_id) REFERENCES consumer (user_id)
);
-- 创建好友请求表
CREATE TABLE friend_request(
	request_id VARCHAR(20),
	sender_id VARCHAR(20),
	receiver_id VARCHAR(20),
	PRIMARY KEY(request_id, sender_id, receiver_id),
	FOREIGN KEY (request_id) REFERENCES request (request_id),
    FOREIGN KEY (sender_id) REFERENCES consumer (user_id),
	FOREIGN KEY (receiver_id) REFERENCES consumer (user_id)
);
-- 创建入群请求表
CREATE TABLE group_request(
	request_id VARCHAR(20),
	group_id VARCHAR(20),
	user_id VARCHAR(20),
	PRIMARY KEY(request_id, group_id, user_id),
	FOREIGN KEY (request_id) REFERENCES request (request_id),
    FOREIGN KEY (group_id) REFERENCES groups (group_id),
	FOREIGN KEY (user_id) REFERENCES consumer (user_id)
);
-- 创建卖家回评表
CREATE TABLE reply(
	evaluation_id VARCHAR(20),
	user_id VARCHAR(20),
	reply_date DATE,
	reply_type VARCHAR(10) CHECK (reply_type IN ('文本','图片','视频')),
	reply_content VARCHAR(500) NOT NULL,
	likes INT CHECK(likes >= 0),
	PRIMARY KEY(evaluation_id, user_id, reply_date),
	FOREIGN KEY (evaluation_id) REFERENCES evaluation (evaluation_id),
	FOREIGN KEY (user_id) REFERENCES seller (user_id)
);

-- 创建消费者评价关系表
CREATE TABLE consumer_give_evaluation(
	evaluation_id VARCHAR(20),
	user_id VARCHAR(20),
	com_id VARCHAR(20),
	PRIMARY KEY(evaluation_id, user_id, com_id),
	FOREIGN KEY (evaluation_id) REFERENCES evaluation (evaluation_id),
	FOREIGN KEY (user_id) REFERENCES consumer (user_id),
	FOREIGN KEY (com_id) REFERENCES commodity (com_id)
);
-- 创建消费者提问关系表
CREATE TABLE consumer_ask_question(
	question_id VARCHAR(20),
	user_id VARCHAR(20),
	com_id VARCHAR(20),
	PRIMARY KEY(question_id, user_id, com_id),
	FOREIGN KEY (question_id) REFERENCES question (question_id),
	FOREIGN KEY (user_id) REFERENCES consumer (user_id),
	FOREIGN KEY (com_id) REFERENCES commodity (com_id)
);
-- 创建消费者回答问题表
CREATE TABLE consumer_reply_question(
	question_id VARCHAR(20),
	user_id VARCHAR(20),
	reply_date TIMESTAMP,
	reply_content VARCHAR(500) NOT NULL,
	PRIMARY KEY(question_id, user_id, reply_date),
	FOREIGN KEY (question_id) REFERENCES question (question_id),
	FOREIGN KEY (user_id) REFERENCES consumer (user_id)
);
-- 创建消费者关注问题表
CREATE TABLE consumer_focus_question(
	question_id VARCHAR(20),
	user_id VARCHAR(20),
	focus_date TIMESTAMP DEFAULT NULL,
	PRIMARY KEY(question_id, user_id),
	FOREIGN KEY (question_id) REFERENCES question (question_id),
	FOREIGN KEY (user_id) REFERENCES consumer (user_id)
);

--视图
--商品各种类别的视图
CREATE VIEW com_food AS SELECT commodity.com_name, commodity.price, commodity.dis_price, commodity.description, commodity.detail, commodity.com_image, shelf_life, weight, taste
						FROM commodity join food on commodity.com_id = food.com_id
						where commodity.category = 'food';
DROP VIEW com_food;
CREATE VIEW com_cloth AS SELECT commodity.com_name, commodity.price, commodity.dis_price, commodity.description, commodity.detail, commodity.com_image, type, size
						FROM commodity join cloth on commodity.com_id = cloth.com_id
						where commodity.category = 'cloth';
DROP VIEW com_cloth;
CREATE VIEW com_ele_appliment AS SELECT commodity.com_name, commodity.price, commodity.dis_price, commodity.description, commodity.detail, commodity.com_image, paymentway, type, memory, safegaurd, net_type
						FROM commodity join ele_appliment on commodity.com_id = ele_appliment.com_id
						where commodity.category = 'ele_appliment';
DROP VIEW com_ele_appliment;
CREATE VIEW com_ele_product AS SELECT commodity.com_name, commodity.price, commodity.dis_price, commodity.description, commodity.detail, commodity.com_image, paymentway, type, safegaurd
						FROM commodity join ele_product on commodity.com_id = ele_product.com_id
						where commodity.category = 'ele_product';
DROP VIEW com_ele_product;
CREATE VIEW com_makeup AS SELECT commodity.com_name, commodity.price, commodity.dis_price, commodity.description, commodity.detail, commodity.com_image, type, weight
						FROM commodity join makeup on commodity.com_id = makeup.com_id
						where commodity.category = 'makeup';
DROP VIEW com_makeup;
CREATE VIEW com_sport AS SELECT commodity.com_name, commodity.price, commodity.dis_price, commodity.description, commodity.detail, commodity.com_image, type, size
						FROM commodity join sport on commodity.com_id = sport.com_id
						where commodity.category = 'sport';
DROP VIEW com_sport;
CREATE VIEW com_toy AS SELECT commodity.com_name, commodity.price, commodity.dis_price, commodity.description, commodity.detail, commodity.com_image, type, suit_age
						FROM commodity join toy on commodity.com_id = toy.com_id
						where commodity.category = 'toy';
DROP VIEW com_toy;
CREATE VIEW com_daily_necessarity AS SELECT commodity.com_name, commodity.price, commodity.dis_price, commodity.description, commodity.detail, commodity.com_image, type, weight
						FROM commodity join daily_necessarity on commodity.com_id = daily_necessarity.com_id
						where commodity.category = 'daily_necessarity';
DROP VIEW com_daily_necessarity;
--店铺所拥有的商品创建视图（改store_id）
CREATE VIEW com_of_store AS SELECT com_name, category, num, price, dis_price, description, detail, com_image, after_sale, up_time
						FROM commodity join sell on commodity.com_id = sell.com_id
						where store_id = '012345678';
DROP VIEW com_of_store;
--每个消费者的浏览记录创建视图（改user_id）
CREATE VIEW personal_browsing_history AS SELECT com_name, price, dis_price, description, detail, com_image, watch_time
						FROM commodity join watching_history on commodity.com_id = watching_history.com_id
						where user_id = '000111111';
DROP VIEW personal_browsing_history;
--每个商品过去30天的历史价格创建视图
CREATE VIEW historical_commodity_price AS SELECT com_name, price, time
						FROM commodity join historical_price on commodity.com_id = historical_price.com_id
						where com_id = '54645645645';
DROP VIEW historical_commodity_price;
--每个消费者的收藏记录创建视图（改user_id）
CREATE VIEW personal_collect_history AS SELECT com_name, price, dis_price, description, detail, com_image, collect_time
						FROM commodity join collect on commodity.com_id = collect.com_id
						where user_id = '000111111';
DROP VIEW personal_collect_history;
--每个消费者的订单创建视图（改user_id）
CREATE VIEW personal_order AS SELECT com_name, real_pay, pay_time, order_.description, detail, com_image, com_num, pickup_code
						FROM order_ join commodity on commodity.com_id = order_.com_id
						where user_id = '000111111';
DROP VIEW personal_order;
--为好友的聊天记录创建视图(user_id)
CREATE VIEW chat_history AS SELECT message_content, message_date
						FROM conversation_message join friend_and_friend on conversation_message.message_id = friend_and_friend.message_id
						where (sender_id = 'tb12345' AND receiver_id = 'tb12342') OR  (sender_id = 'tb12342' AND receiver_id = 'tb12345')
						order by message_date;
DROP VIEW chat_history;


SELECT * FROM com_food;
SELECT * FROM com_cloth;
SELECT * FROM com_ele_appliment;
SELECT * FROM com_ele_product;
SELECT * FROM com_makeup;
SELECT * FROM com_sport;
SELECT * FROM com_toy;
SELECT * FROM com_daily_necessarity;
SELECT * FROM chat_history;

--订单表物流表触发器

CREATE OR REPLACE FUNCTION order_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO logistics (com_id)
    VALUES (NEW.com_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_order_trigger
AFTER INSERT ON order_
FOR EACH ROW
EXECUTE FUNCTION order_insert_trigger();

--删除触发器
DROP TRIGGER insert_order_trigger ON order_

--商品表发生变化历史价格表变化触发器
CREATE OR REPLACE FUNCTION history_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN 
    INSERT INTO historical_price (com_id,time,price)
    VALUES (NEW.com_id,NOW(),NEW.price);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_history_trigger
AFTER UPDATE ON commodity
FOR EACH ROW
EXECUTE FUNCTION history_insert_trigger();

--删除触发器
DROP TRIGGER insert_history_trigger ON commodity

--商品大表与子表触发器
CREATE OR REPLACE FUNCTION insert_comsubtable_function()
  RETURNS TRIGGER AS $$
BEGIN
  -- 判断商品类别
  IF NEW.category = 'food' THEN
    -- 更新food
    INSERT INTO food(com_id,com_name,category,price,dis_price,num,description,detail,after_sale,up_time,com_image)
    VALUES (NEW.com_id,NEW.com_name,NEW.category,NEW.price,NEW.dis_price,NEW.num,NEW.description,NEW.detail,NEW.after_sale,NEW.up_time,NEW.com_image);
  ELSIF NEW.category = 'cloth' THEN
    -- 更新cloth
    INSERT INTO cloth(com_id,com_name,category,price,dis_price,num,description,detail,after_sale,up_time,com_image)
    VALUES (NEW.com_id,NEW.com_name,NEW.category,NEW.price,NEW.dis_price,NEW.num,NEW.description,NEW.detail,NEW.after_sale,NEW.up_time,NEW.com_image);
  ELSIF NEW.category = 'ele_appliment' THEN
    -- 更新ele_appliment
    INSERT INTO ele_appliment(com_id,com_name,category,price,dis_price,num,description,detail,after_sale,up_time,com_image)
    VALUES (NEW.com_id,NEW.com_name,NEW.category,NEW.price,NEW.dis_price,NEW.num,NEW.description,NEW.detail,NEW.after_sale,NEW.up_time,NEW.com_image);
  ELSIF NEW.category = 'ele_product' THEN
    -- 更新ele_product
    INSERT INTO ele_product(com_id,com_name,category,price,dis_price,num,description,detail,after_sale,up_time,com_image)
    VALUES (NEW.com_id,NEW.com_name,NEW.category,NEW.price,NEW.dis_price,NEW.num,NEW.description,NEW.detail,NEW.after_sale,NEW.up_time,NEW.com_image);
  ELSIF NEW.category = 'makeup' THEN
    -- 更新makeup
    INSERT INTO makeup(com_id,com_name,category,price,dis_price,num,description,detail,after_sale,up_time,com_image)
    VALUES (NEW.com_id,NEW.com_name,NEW.category,NEW.price,NEW.dis_price,NEW.num,NEW.description,NEW.detail,NEW.after_sale,NEW.up_time,NEW.com_image);
  ELSIF NEW.category = 'sport' THEN
    -- 更新sport
    INSERT INTO sport(com_id,com_name,category,price,dis_price,num,description,detail,after_sale,up_time,com_image)
    VALUES (NEW.com_id,NEW.com_name,NEW.category,NEW.price,NEW.dis_price,NEW.num,NEW.description,NEW.detail,NEW.after_sale,NEW.up_time,NEW.com_image);
  ELSIF NEW.category = 'toy' THEN
    -- 更新toy
    INSERT INTO toy(com_id,com_name,category,price,dis_price,num,description,detail,after_sale,up_time,com_image)
    VALUES (NEW.com_id,NEW.com_name,NEW.category,NEW.price,NEW.dis_price,NEW.num,NEW.description,NEW.detail,NEW.after_sale,NEW.up_time,NEW.com_image);
  ELSIF NEW.category = 'daily_necessarity' THEN
    -- 更新daily_necessarity
    INSERT INTO daily_necessarity(com_id,com_name,category,price,dis_price,num,description,detail,after_sale,up_time,com_image)
    VALUES (NEW.com_id,NEW.com_name,NEW.category,NEW.price,NEW.dis_price,NEW.num,NEW.description,NEW.detail,NEW.after_sale,NEW.up_time,NEW.com_image);
  ELSE 
    -- 更新other
    INSERT INTO other(com_id,com_name,category,price,dis_price,num,description,detail,after_sale,up_time,com_image)
    VALUES (NEW.com_id,NEW.com_name,NEW.category,NEW.price,NEW.dis_price,NEW.num,NEW.description,NEW.detail,NEW.after_sale,NEW.up_time,NEW.com_image);
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_comsubtable_trigger
AFTER INSERT ON commodity
FOR EACH ROW
EXECUTE FUNCTION insert_comsubtable_function();
								  
DROP TRIGGER insert_comsubtable_trigger ON commodity


CREATE OR REPLACE FUNCTION update_comsubtable_function()
  RETURNS TRIGGER AS $$
BEGIN
  -- 判断商品类别
  IF OLD.category = 'food' THEN
    -- 更新food
    UPDATE food
    SET com_name=NEW.com_name,category=NEW.category,price=NEW.price,dis_price=NEW.dis_price,num=NEW.num,
    description=NEW.description,detail=NEW.detail,after_sale=NEW.after_sale,up_time=NEW.up_time,com_image=NEW.com_image
    WHERE com_id=NEW.com_id;
  ELSIF OLD.category = 'cloth' THEN
    -- 更新cloth
    UPDATE cloth
    SET com_name=NEW.com_name,category=NEW.category,price=NEW.price,dis_price=NEW.dis_price,num=NEW.num,
    description=NEW.description,detail=NEW.detail,after_sale=NEW.after_sale,up_time=NEW.up_time,com_image=NEW.com_image
    WHERE com_id=NEW.com_id;
  ELSIF OLD.category = 'ele_appliment' THEN
    -- 更新ele_appliment
    UPDATE ele_appliment
    SET com_name=NEW.com_name,category=NEW.category,price=NEW.price,dis_price=NEW.dis_price,num=NEW.num,
    description=NEW.description,detail=NEW.detail,after_sale=NEW.after_sale,up_time=NEW.up_time,com_image=NEW.com_image
    WHERE com_id=NEW.com_id;
  ELSIF OLD.category = 'ele_product' THEN
    -- 更新ele_product
    UPDATE ele_product
    SET com_name=NEW.com_name,category=NEW.category,price=NEW.price,dis_price=NEW.dis_price,num=NEW.num,
    description=NEW.description,detail=NEW.detail,after_sale=NEW.after_sale,up_time=NEW.up_time,com_image=NEW.com_image
    WHERE com_id=NEW.com_id;
  ELSIF OLD.category = 'makeup' THEN
    -- 更新makeup
    UPDATE makeup
    SET com_name=NEW.com_name,category=NEW.category,price=NEW.price,dis_price=NEW.dis_price,num=NEW.num,
    description=NEW.description,detail=NEW.detail,after_sale=NEW.after_sale,up_time=NEW.up_time,com_image=NEW.com_image
    WHERE com_id=NEW.com_id;
  ELSIF OLD.category = 'sport' THEN
    -- 更新sport
    UPDATE sport
    SET com_name=NEW.com_name,category=NEW.category,price=NEW.price,dis_price=NEW.dis_price,num=NEW.num,
    description=NEW.description,detail=NEW.detail,after_sale=NEW.after_sale,up_time=NEW.up_time,com_image=NEW.com_image
    WHERE com_id=NEW.com_id;
  ELSIF OLD.category = 'toy' THEN
    -- 更新toy
    UPDATE toy
    SET com_name=NEW.com_name,category=NEW.category,price=NEW.price,dis_price=NEW.dis_price,num=NEW.num,
    description=NEW.description,detail=NEW.detail,after_sale=NEW.after_sale,up_time=NEW.up_time,com_image=NEW.com_image
    WHERE com_id=NEW.com_id;
  ELSIF OLD.category = 'daily_necessarity' THEN
    -- 更新daily_necessarity
    UPDATE daily_necessarity
    SET com_name=NEW.com_name,category=NEW.category,price=NEW.price,dis_price=NEW.dis_price,num=NEW.num,
    description=NEW.description,detail=NEW.detail,after_sale=NEW.after_sale,up_time=NEW.up_time,com_image=NEW.com_image
    WHERE com_id=NEW.com_id;
  ELSE 
    -- 更新other
    UPDATE other
    SET com_name=NEW.com_name,category=NEW.category,price=NEW.price,dis_price=NEW.dis_price,num=NEW.num,
    description=NEW.description,detail=NEW.detail,after_sale=NEW.after_sale,up_time=NEW.up_time,com_image=NEW.com_image
    WHERE com_id=NEW.com_id;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_comsubtable_trigger
AFTER UPDATE ON commodity
FOR EACH ROW
EXECUTE FUNCTION update_comsubtable_function();
								  
DROP TRIGGER update_comsubtable_trigger ON commodity


CREATE OR REPLACE FUNCTION delete_comsubtable_function()
  RETURNS TRIGGER AS $$
BEGIN
  -- 判断商品类别
  IF OLD.category = 'food' THEN
    -- 更新food
    DELETE FROM food
    WHERE com_id=OLD.com_id;
  ELSIF OLD.category = 'cloth' THEN
    -- 更新cloth
    DELETE FROM cloth
    WHERE com_id=OLD.com_id;
  ELSIF OLD.category = 'ele_appliment' THEN
    -- 更新ele_appliment
    DELETE FROM ele_appliment
    WHERE com_id=OLD.com_id;
  ELSIF OLD.category = 'ele_product' THEN
    -- 更新ele_product
    DELETE FROM ele_product
    WHERE com_id=OLD.com_id;
  ELSIF OLD.category = 'makeup' THEN
    -- 更新makeup
    DELETE FROM makeup
    WHERE com_id=OLD.com_id;
  ELSIF OLD.category = 'sport' THEN
    -- 更新sport
    DELETE FROM sport
    WHERE com_id=OLD.com_id;
  ELSIF OLD.category = 'toy' THEN
    -- 更新toy
    DELETE FROM toy
    WHERE com_id=OLD.com_id;
  ELSIF OLD.category = 'daily_necessarity' THEN
    -- 更新daily_necessarity
    DELETE FROM daily_necessarity
    WHERE com_id=OLD.com_id;
  ELSE 
    -- 更新other
    DELETE FROM other
    WHERE com_id=OLD.com_id;
  END IF;

  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_comsubtable_trigger
AFTER DELETE ON commodity
FOR EACH ROW
EXECUTE FUNCTION delete_comsubtable_function();
								  
DROP TRIGGER delete_comsubtable_trigger ON commodity


-- 函数
-- 计算商品价格
DROP FUNCTION calculate_price( IN product_id VARCHAR(20),IN user_id VARCHAR(20),INOUT total_payment DOUBLE PRECISION);
CREATE OR REPLACE FUNCTION calculate_price(
    IN product_id VARCHAR(20),
    IN user_id2 VARCHAR(20),
    INOUT total_payment DOUBLE PRECISION
) AS $$
DECLARE
    product_price DOUBLE PRECISION;
	discount_price DOUBLE PRECISION;
    red_packet_discount DOUBLE PRECISION;
    red_packet_id2 INT;
BEGIN
    SELECT price INTO product_price FROM commodity WHERE com_id = product_id;
	SELECT dis_price INTO discount_price FROM commodity WHERE com_id = product_id;
    SELECT red_packet_id INTO red_packet_id2 FROM consumer_recieve_redpacket WHERE user_id = user_id2;
    SELECT value_ INTO red_packet_discount FROM red_packet WHERE red_packet_id = red_packet_id2;
	RAISE INFO 'origin price: %', product_price;
	RAISE INFO 'discount_price: %', discount_price;
	RAISE INFO 'red_packet_discount: %', red_packet_discount;
    total_payment := product_price - red_packet_discount - discount_price;
    -- 更新红包表
    DELETE FROM consumer_recieve_redpacket WHERE user_id = user_id;
    DELETE FROM red_packet WHERE red_packet_id = red_packet_id;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;



-- 直接购买商品
DROP FUNCTION buy_commodity_directly(IN product_id VARCHAR(20),IN user_id VARCHAR(20),IN order_id VARCHAR(20),IN buy_num INT);
CREATE OR REPLACE FUNCTION buy_commodity_directly(
    IN product_id VARCHAR(20),
    IN user_id VARCHAR(20),
	IN order_id VARCHAR(20),
	IN buy_num INT
) RETURNS VOID AS $$
DECLARE
    total_payment DOUBLE PRECISION;
BEGIN
    UPDATE commodity SET num = num - buy_num WHERE com_id = product_id;
	SELECT calculate_price(product_id,user_id,total_payment) INTO total_payment;
	INSERT INTO order_ VALUES (order_id,product_id,user_id,total_payment,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'WAITDELIVER') ;
	RAISE INFO 'Calculated Total Payment: %', total_payment;
	RAISE INFO 'Insert order';
	RAISE INFO 'update Commodity num';
END;
$$ LANGUAGE plpgsql;

INSERT INTO red_packet
VALUES(1,'无门槛红包',2,'无门槛','2023-12-25');

INSERT INTO consumer_recieve_redpacket
VALUES ('tb12345',1);
DELETE FROM order_ WHERE order_id = 'com12347tb12345';
DELETE FROM logistics WHERE com_id = 'com12347';
UPDATE commodity SET num = 1000 WHERE com_id = 'com12347';

SELECT buy_commodity_directly('com12347', 'tb12345', 'com12347tb12345', 1);
SELECT * FROM order_;

SELECT * FROM commodity WHERE com_id = 'com12347';
SELECT * FROM food WHERE com_id = 'com12347';
SELECT * FROM logistics;
SELECT * FROM red_packet;
SELECT * FROM consumer_recieve_redpacket;

-- 店铺上架商品
DROP FUNCTION store_launch_commodity(IN product_id VARCHAR(20),IN user_id VARCHAR(20),IN launch_num INT);
CREATE OR REPLACE FUNCTION store_launch_commodity(
    IN product_id VARCHAR(20),
    IN store_id VARCHAR(20),
	IN launch_num INT
) RETURNS VOID AS $$
BEGIN
    UPDATE commodity SET num = num - launch_num WHERE com_id = product_id;
	INSERT INTO sell VALUES (store_id,com_id,CURRENT_TIMESTAMP) ;
END;
$$ LANGUAGE plpgsql;


-- 退货
DROP FUNCTION return_commodity(IN product_id VARCHAR(20),IN user_id VARCHAR(20),IN buy_num INT);
CREATE OR REPLACE FUNCTION return_commodity(
    IN product_id VARCHAR(20),
    IN user_id VARCHAR(20),
	IN buy_num INT
) RETURNS VOID AS $$
DECLARE
    total_payment DOUBLE PRECISION;
BEGIN
    UPDATE commodity SET num = num + buy_num WHERE com_id = product_id;
	UPDATE order_ SET state = 'REFUNDING' WHERE com_id = product_id AND user_id = user_id;
END;
$$ LANGUAGE plpgsql;

DELETE FROM users WHERE user_id = 'tb12347';
INSERT INTO users
VALUES ('tb12345', 'Daming', '123456789', 'F', '13981367234', 'consumer');
INSERT INTO users
VALUES ('tb12342', 'Amy', 'a123456789', 'M', '13981367555', 'consumer');
INSERT INTO users
VALUES ('tb12341', 'LingLing', 'l123456789', 'M', '13221367234','consumer');
INSERT INTO users
VALUES ('tb12343', 'David', 'd123456789', 'F', '13987767234', 'consumer');
INSERT INTO users
VALUES ('tb12344', 'sally', 's123456789', 'M', '13984467234', 'consumer');
INSERT INTO users
VALUES ('tb12347', 'Tom', 't123456789', 'F', '13984467234', 'seller');
INSERT INTO users
VALUES ('tb12346', 'Jerry', 'j123456789', 'M', '13921367234', 'seller');
INSERT INTO users
VALUES ('tb12348', 'Jack', 'j1234567689', 'M', '15581367234', 'seller');
INSERT INTO users
VALUES ('tb12349', 'Rose', 'r123456789', 'F', '15481367234', 'seller');
INSERT INTO users
VALUES ('tb12340', 'Lily', 'l123456789', 'F', '15681367234', 'seller');

ALTER TABLE consumer
ALTER COLUMN headshot TYPE VARCHAR(500); 
INSERT INTO consumer
VALUES ('tb12345', 'Daming', '123456789', 'F', '13981367234', 'T', 5, 'http://img.alicdn.com/sns_logo/i2/2212109088345/O1CN01rsPVu02BW3rihzm8e_!!2212109088345-0-ggpersonal.jpg', 0, 0, 5, 10.5, 500, '13981367234', 'consumer','com12347+1 com12348+1');
INSERT INTO consumer
VALUES ('tb12342', 'Amy', 'a123456789', 'M', '13981367555', 'T', 5, 'http://img.alicdn.com/sns_logo/i2/2212109088345/O1CN01rsPVu02BW3rihzm8e_!!2212109088345-0-ggpersonal.jpg', 5, 5, 5, 20.5, 50, '13981367555', 'consumer','com12349+2 com12348+2');
INSERT INTO consumer
VALUES ('tb12341', 'LingLing', 'l123456789', 'M', '13221367234', 'T', 5, 'http://img.alicdn.com/sns_logo/i2/2212109088345/O1CN01rsPVu02BW3rihzm8e_!!2212109088345-0-ggpersonal.jpg', 10, 3, 8, 30.5, 900, '13221367234', 'consumer');
INSERT INTO consumer
VALUES ('tb12343', 'David', 'd123456789', 'F', '13987767234', 'T', 5, 'http://img.alicdn.com/sns_logo/i2/2212109088345/O1CN01rsPVu02BW3rihzm8e_!!2212109088345-0-ggpersonal.jpg', 10, 10, 5, 100.5, 1500, '13987767234', 'consumer');
INSERT INTO consumer
VALUES ('tb12344', 'sally', 's123456789', 'M', '13984467234', 'T', 5, 'http://img.alicdn.com/sns_logo/i2/2212109088345/O1CN01rsPVu02BW3rihzm8e_!!2212109088345-0-ggpersonal.jpg', 30, 30, 9, 120.5, 2500, '13984467234', 'consumer','com12347+1');

SELECT * FROM consumer;

-- 主账号
INSERT INTO seller
VALUES ('tb12347', 'Tom', 't123456789', 'F', '13984467234', 'seller','T','T');
INSERT INTO seller
VALUES ('tb12346', 'Jerry', 'j123456789', 'M', '13921367234', 'seller','T','F');
INSERT INTO seller
VALUES ('tb12348', 'Jack', 'j1234567689', 'M', '15581367234', 'seller','F','T');
INSERT INTO seller
VALUES ('tb12349', 'Rose', 'r123456789', 'F', '15481367234', 'seller','T','F');
INSERT INTO seller
VALUES ('tb12340', 'Lily', 'l123456789', 'F', '15681367234', 'seller','T','T');



SELECT * FROM consumer;
SELECT * FROM users;
SELECT * FROM seller;

--交易系统数据测试

--店铺大表

INSERT INTO store
VALUES ('400672256','Time food','food',
'https://img.alicdn.com/imgextra/i1/O1CN01pz5irY1bhDKceJor2_!!6000000003496-2-tps-144-144.png',
'tb12346','567 West Main Street, Oakville, Canada',
1231234.23,421343,21344,67,123134,4,5,'2018-05-15',0
);

INSERT INTO store
VALUES ('400672257','Fashion women wear','cloth',
'https://img.alicdn.com/imgextra/i1/O1CN01pz5irY1bhDKceJor2_!!6000000003496-2-tps-144-144.png',
'tb12347','1234 Maple Street, Springfield, USA',
141241.12,214120,12312,70,14223,3,5,'2020-01-10',0
);

INSERT INTO store
VALUES ('400672258','happy toy','toy',
'https://img.alicdn.com/imgextra/i1/O1CN01pz5irY1bhDKceJor2_!!6000000003496-2-tps-144-144.png',
'tb12348','789 Elmwood Avenue, Portland, USA',
412442.2,4124,423,40,2223,2,4,'2022-09-23',0
);

--商品大表
INSERT INTO commodity
VALUES ('com12347', 'cookie', 'food', 5.99, 1,1000,'very delicious',
'Deliciously crisp and golden, these cookies are the perfect blend of sweetness and crunch. Each bite is filled with rich flavors, leaving a lingering vanilla aroma. Indulge in these irresistible treats that will surely satisfy your cravings. Enjoy them with a cup of tea or share them with friends for a delightful snack experience.',
'Seven days no reason to return','2023-07-01',
'https://img.alicdn.com/imgextra/i3/1408820149/O1CN01B67UhV1CyHv3krMdJ_!!0-saturn_solar.jpg_460x460q90.jpg_.webp');

INSERT INTO commodity
VALUES ('com12348', 'River snail rice noodle', 'food', 10.99, 0,5000,'smelly but delicious',
'A popular dish from southern China, this bowl of noodles is filled with complex flavors and textures. The slippery rice noodles are topped with a savory sauce made from pickled bamboo shoots, spicy chili oil, minced pork, and fragrant herbs. Served with vinegar and peanuts for extra zing and crunch, this dish is a must-try for any food lover.',
'Seven days no reason to return','2023-05-20',
'https://img.alicdn.com/imgextra/i1/2485630119/O1CN01XjB5ap1CkY1ioEbec_!!0-saturn_solar.jpg_460x460q90.jpg_.webp');

INSERT INTO commodity
VALUES ('com12349', 'spicy strip', 'food', 0.5, 0,100000,'Taste of childhood',
'Spicy and addictive, these chewy strips pack a punch of flavor. Infused with a blend of fiery spices, they offer a satisfying kick with every bite. Whether you enjoy them as a snack on the go or as a zesty addition to your favorite dishes, these spicy sticks are sure to spice up your day.',
'Seven days no reason to return','2023-01-15',
'https://img.alicdn.com/imgextra/i4/101025137/O1CN014tAkX81nonUmjbfXL_!!0-saturn_solar.jpg_460x460q90.jpg_.webp');

INSERT INTO commodity
VALUES ('com12350', 'Braised beef noodles', 'food', 3.5, 0,10000,'filling',
'Spicy and addictive, these chewy strips pack a punch of flavor. Infused with a blend of fiery spices, they offer a satisfying kick with every bite. Whether you enjoy them as a snack on the go or as a zesty addition to your favorite dishes, these spicy sticks are sure to spice up your day.',
'Seven days no reason to return','2023-10-02',
'https://g-search3.alicdn.com/img/bao/uploaded/i4/i1/2371566698/O1CN014qvI8h1zLjhoHxwEH_!!0-item_pic.jpg_460x460q90.jpg_.webp');

INSERT INTO commodity
VALUES ('com12351', 'jelly', 'food', 20.99, 5.99,10000,'chewy and delicious',
'Delightfully wobbly and bursting with fruity sweetness, this jelly treat is a playful indulgence for all ages. Each colorful, translucent cube offers a refreshing burst of flavor, making it a fun and satisfying snack. Whether enjoyed on its own or as part of a dessert, this jiggly delight is sure to bring joy.',
'Seven days no reason to return','2023-11-01',
'https://img.alicdn.com/imgextra/i3/2908500146/O1CN01UEccf61CwujVuykdl_!!0-saturn_solar.jpg_460x460q90.jpg_.webp');

INSERT INTO commodity
VALUES ('com13310', 'Down jacket', 'cloth', 399, 100,2000,'Fit like a glove',
'Designed for warmth and style, this down jacket is the ultimate winter companion. Filled with soft and insulating down feathers, it provides exceptional insulation against the cold. Its sleek and quilted design offers a trendy and modern look, while the cozy hood and zippered pockets add practicality. Stay snug and fashionable in this essential winter apparel.',
'Seven days no reason to return','2023-10-20',
'https://g-search1.alicdn.com/img/bao/uploaded/i4/i2/3422193418/O1CN01BV60Ej1b7UcX4Zjyz_!!3422193418.jpg_460x460q90.jpg_.webp');

INSERT INTO commodity
VALUES ('com13410', 'Midea air conditioner', 'ele_appliment', 2999, 200,2000,'Very cool',
'This air conditioner is the perfect solution for keeping your home cool and comfortable. With its powerful cooling capabilities, it quickly cools down any room. Its sleek and compact design fits seamlessly into any space, while its energy-efficient technology ensures minimal electricity consumption. Control the temperature with ease using the remote control or programmable timer, making it a convenient and reliable option for hot summer months.',
'Seven days no reason to return','2023-02-27',
'https://img.alicdn.com/imgextra/i2/100532851/O1CN01CqyVgR1Wvo3NORkOs_!!2-saturn_solar.png_460x460q90.jpg_.webp');

INSERT INTO commodity
VALUES ('com13510', 'vivo mobile phone', 'ele_product', 99999, 2000,2000,'Too expensive',
'Sleek and powerful, this smartphone is a technological marvel. Its high-resolution display brings visuals to life with vibrant colors and sharp details. With a lightning-fast processor and ample storage capacity, it effortlessly handles multitasking and stores all your important files. Capture stunning photos with its advanced camera system and stay connected with fast internet speeds. This phone merges style and functionality seamlessly, making it a must-have gadget.',
'Seven days no reason to return','2023-10-27',
'https://img.alicdn.com/imgextra/i2/14867192/O1CN01mHqjjR22zzPUyJaeD_!!0-saturn_solar.jpg_460x460q90.jpg_.webp');

INSERT INTO commodity
VALUES ('com13610', 'Liquid foundation', 'makeup', 49, 0,2000,'Flawless coverage, radiant complexion',
'This foundation is a game-changer. It provides seamless coverage that blurs imperfections, leaving a natural and glowing complexion. Its lightweight formula blends effortlessly, ensuring a smooth and even finish. With long-lasting staying power, it keeps your skin looking flawless all day. Enhance your beauty with this exceptional foundation.',
'Seven days no reason to return','2023-11-27',
'https://img.alicdn.com/imgextra/i1/30321366/O1CN01GuqGrB1Lxfims8RoA_!!0-saturn_solar.jpg_460x460q90.jpg_.webp');

INSERT INTO commodity
VALUES ('com13710', 'Ultraman', 'toy', 20.3, 0,5000,'Children like it very much',
'Unleash your creativity with this versatile toy. With endless possibilities, it sparks imagination and promotes hands-on learning. Its durable construction ensures long-lasting fun, while its vibrant colors and unique design capture attention. Whether building, stacking, or creating imaginative stories, this toy guarantees hours of entertainment for children of all ages.',
'Seven days no reason to return','2022-12-15',
'https://g-search1.alicdn.com/img/bao/uploaded/i4/i1/1898884903/O1CN01YPWF7r1m5coIGl8h2_!!1898884903.jpg_460x460q90.jpg_.webp');


--食品小表
INSERT INTO food
VALUES ('com12347', 'cookie', 'food', 5.99, 1,1000,'very delicious',
'Deliciously crisp and golden, these cookies are the perfect blend of sweetness and crunch. Each bite is filled with rich flavors, leaving a lingering vanilla aroma. Indulge in these irresistible treats that will surely satisfy your cravings. Enjoy them with a cup of tea or share them with friends for a delightful snack experience.',
'Seven days no reason to return','2023-07-01',
'https://img.alicdn.com/imgextra/i3/1408820149/O1CN01B67UhV1CyHv3krMdJ_!!0-saturn_solar.jpg_460x460q90.jpg_.webp',
180,'1kg','Original taste');

INSERT INTO food
VALUES ('com12348', 'River snail rice noodle', 'food', 10.99, 0,5000,'smelly but delicious',
'A popular dish from southern China, this bowl of noodles is filled with complex flavors and textures. The slippery rice noodles are topped with a savory sauce made from pickled bamboo shoots, spicy chili oil, minced pork, and fragrant herbs. Served with vinegar and peanuts for extra zing and crunch, this dish is a must-try for any food lover.',
'Seven days no reason to return','2023-05-20',
'https://img.alicdn.com/imgextra/i1/2485630119/O1CN01XjB5ap1CkY1ioEbec_!!0-saturn_solar.jpg_460x460q90.jpg_.webp',
90,'225g','Original taste');

INSERT INTO food
VALUES ('com12349', 'spicy strip', 'food', 0.5, 0,100000,'Taste of childhood',
'Spicy and addictive, these chewy strips pack a punch of flavor. Infused with a blend of fiery spices, they offer a satisfying kick with every bite. Whether you enjoy them as a snack on the go or as a zesty addition to your favorite dishes, these spicy sticks are sure to spice up your day.',
'Seven days no reason to return','2023-01-15',
'https://img.alicdn.com/imgextra/i4/101025137/O1CN014tAkX81nonUmjbfXL_!!0-saturn_solar.jpg_460x460q90.jpg_.webp',
360,'50g','Original taste');

INSERT INTO food
VALUES ('com12350', 'Braised beef noodles', 'food', 3.5, 0,10000,'filling',
'Spicy and addictive, these chewy strips pack a punch of flavor. Infused with a blend of fiery spices, they offer a satisfying kick with every bite. Whether you enjoy them as a snack on the go or as a zesty addition to your favorite dishes, these spicy sticks are sure to spice up your day.',
'Seven days no reason to return','2023-10-02',
'https://g-search3.alicdn.com/img/bao/uploaded/i4/i1/2371566698/O1CN014qvI8h1zLjhoHxwEH_!!0-item_pic.jpg_460x460q90.jpg_.webp',
90,'225g','Braised beef');

INSERT INTO food
VALUES ('com12351', 'jelly', 'food', 20.99, 5.99,10000,'chewy and delicious',
'Delightfully wobbly and bursting with fruity sweetness, this jelly treat is a playful indulgence for all ages. Each colorful, translucent cube offers a refreshing burst of flavor, making it a fun and satisfying snack. Whether enjoyed on its own or as part of a dessert, this jiggly delight is sure to bring joy.',
'Seven days no reason to return','2023-11-01',
'https://img.alicdn.com/imgextra/i3/2908500146/O1CN01UEccf61CwujVuykdl_!!0-saturn_solar.jpg_460x460q90.jpg_.webp',
360,'100g','orange');

--店铺商品关系表

INSERT INTO sell
VALUES ('400672256','com12347','2023-08-01');
INSERT INTO sell
VALUES ('400672256','com12348','2023-05-30');
INSERT INTO sell
VALUES ('400672256','com12349','2023-02-18');
INSERT INTO sell
VALUES ('400672256','com12350','2023-10-07');
INSERT INTO sell
VALUES ('400672256','com12351','2023-11-10');

SELECT * FROM commodity;
SELECT * FROM store;
SELECT * FROM food;
SELECT * FROM sell;

--查询一个店铺的所有商品
SELECT com_id FROM ((store NATURAL JOIN sell)NATURAL JOIN commodity)
WHERE store_id = '400672256';

--聊天系统的数据测试
INSERT INTO message
VALUES ('ms0000000000001','文本','你吃饭了吗？','2022-01-10 12:00:00');
INSERT INTO message
VALUES ('ms0000000000002','文本','还没有','2022-01-10 12:00:30');
INSERT INTO message
VALUES ('ms0000000000003','文本','今天布置了什么作业了？','2022-01-12 18:00:00');
INSERT INTO message
VALUES ('ms0000000000004','文本','数值计算的编程作业','2022-01-12 18:30:53');
INSERT INTO message
VALUES ('ms0000000000005','文本','edge detection','2022-01-12 18:44:24');
INSERT INTO message
VALUES ('ms0000000000006','文本','一会去哪吃？','2022-01-10 12:00:47');
INSERT INTO message
VALUES ('ms0000000000007','文本','兰园二楼','2022-01-10 12:01:20');
INSERT INTO message
VALUES ('ms0000000000008','图片','D:\image\2.jpg','2022-01-12 18:50:55');
INSERT INTO message
VALUES ('ms0000000000009','视频','D:\video\2.mp4','2022-01-12 18:51:56');
--对话消息总表数据
INSERT INTO conversation_message
VALUES ('ms0000000000001','文本','你吃饭了吗？','2022-01-10 12:00:00', true);
INSERT INTO conversation_message
VALUES ('ms0000000000002','文本','还没有','2022-01-10 12:00:30', false);
INSERT INTO conversation_message
VALUES ('ms0000000000006','文本','一会去哪吃？','2022-01-10 12:00:47', false);
INSERT INTO conversation_message
VALUES ('ms0000000000007','文本','兰园二楼','2022-01-10 12:01:20', false);
--群组消息总表数据
INSERT INTO group_message
VALUES ('ms0000000000003','文本','今天布置了什么作业了？','2022-01-12 18:00:00');
INSERT INTO group_message
VALUES ('ms0000000000004','文本','数值计算的编程作业','2022-01-12 18:30:53');
INSERT INTO group_message
VALUES ('ms0000000000005','文本','edge detection','2022-01-12 18:44:24');
INSERT INTO group_message
VALUES ('ms0000000000008','图片','D:\image\2.jpg','2022-01-12 18:50:55');
INSERT INTO group_message
VALUES ('ms0000000000009','视频','D:\video\2.mp4','2022-01-12 18:51:56');
DROP TABLE message;
DROP TABLE group_message;
SELECT * FROM message;
SELECT * FROM conversation_message;
SELECT * FROM group_message;

--评价消息总表数据
INSERT INTO evaluation
VALUES ('ev0000000000001','文本','这件衣服的材质很好,穿起来很舒服', false, '好', '2023-02-22 14:00:00',
	   false, 0, 0, 5, 5, 5, 5);
INSERT INTO evaluation
VALUES ('ev0000000000002','文本','这个充电宝充电慢，还会漏电', false, '差', '2023-03-12 15:30:53',
	   false, 0, 0, 1, 1, 3, 3);
INSERT INTO evaluation
VALUES ('ev0000000000003','文本','这个薯片很好吃，值得一买', true, '好', '2023-04-11 08:12:22',
	   false, 0, 0, 5, 5, 4, 5);
INSERT INTO evaluation
VALUES ('ev0000000000003','文本','这个薯片很好吃，值得一买', true, '好', '2023-04-11 08:12:22',
	   false, 0, 0, 5, 5, 4, 2);
INSERT INTO evaluation
VALUES ('ev0000000000004','文本','这件衣服真的很好看，还便宜', false, '好', '2023-02-23 10:10:55',
	   true, 25, 30, 5, 5, 5, 5);
INSERT INTO evaluation
VALUES ('ev0000000000005','图片','D:\image\1.jpg', false, '好', '2023-03-23 11:25:35',
	   false, 10, 3, 5, 4, 4, 5);
INSERT INTO evaluation
VALUES ('ev0000000000006','视频','D:\video\1.mp4', false, '好', '2023-01-25 16:55:45',
	   false, 0, 0, 5, 5, 5, 5);
SELECT * FROM evaluation;
DELETE FROM evaluation WHERE evaluation_id = 'ev0000000000005';

DROP TABLE evaluation;
--好友消息索引表数据
INSERT INTO friend_and_friend
VALUES ('tb12345', 'tb12342', 'ms0000000000001');
INSERT INTO friend_and_friend
VALUES ('tb12342', 'tb12345', 'ms0000000000002');
INSERT INTO friend_and_friend
VALUES ('tb12345', 'tb12342', 'ms0000000000006');
INSERT INTO friend_and_friend
VALUES ('tb12342', 'tb12345', 'ms0000000000007');
SELECT * FROM friend_and_friend;