# 用户表
CREATE TABLE USER_ACCOUNT (
USER_ID VARCHAR(32) NOT NULL,
USERNAME VARCHAR(100) NOT NULL,
NICKNAME VARCHAR(100) NOT NULL,
PASSWORD VARCHAR(200) NOT NULL,
EMAIL VARCHAR(100),
PHONE VARCHAR(20),
AVATAR VARCHAR(500),
GENDER VARCHAR(10),
BIRTH_DATE DATE,
LOCATION VARCHAR(200),
BIO VARCHAR(500),
FOLLOWER_COUNT INTEGER DEFAULT 0,
FOLLOWING_COUNT INTEGER DEFAULT 0,
POST_COUNT INTEGER DEFAULT 0,
USER_STATUS VARCHAR(10) NOT NULL,
REGISTER_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
LAST_LOGIN_TIME TIMESTAMP,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_USER_ACCOUNT PRIMARY KEY (USER_ID)
) ORGANIZE BY ROW;

# 用户关注表
CREATE TABLE USER_FOLLOW (
FOLLOW_ID VARCHAR(32) NOT NULL,
FOLLOWER_ID VARCHAR(32) NOT NULL,
FOLLOWING_ID VARCHAR(32) NOT NULL,
FOLLOW_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_USER_FOLLOW PRIMARY KEY (FOLLOW_ID)
) ORGANIZE BY ROW;

# 用户好友表
CREATE TABLE USER_FRIEND (
FRIEND_ID VARCHAR(32) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
FRIEND_USER_ID VARCHAR(32) NOT NULL,
FRIEND_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_USER_FRIEND PRIMARY KEY (FRIEND_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_USER_USERNAME ON USER_ACCOUNT(USERNAME);
CREATE INDEX IDX_USER_NICKNAME ON USER_ACCOUNT(NICKNAME);
CREATE UNIQUE INDEX IDX_USER_FOLLOW_RELATION ON USER_FOLLOW(FOLLOWER_ID, FOLLOWING_ID);
CREATE INDEX IDX_USER_FOLLOW_FOLLOWER ON USER_FOLLOW(FOLLOWER_ID, CREATE_TIME DESC);
CREATE INDEX IDX_USER_FOLLOW_FOLLOWING ON USER_FOLLOW(FOLLOWING_ID, CREATE_TIME DESC);
CREATE UNIQUE INDEX IDX_USER_FRIEND_RELATION ON USER_FRIEND(USER_ID, FRIEND_USER_ID);
CREATE INDEX IDX_USER_FRIEND_USER ON USER_FRIEND(USER_ID);

# 用户动态表
CREATE TABLE USER_POST (
POST_ID VARCHAR(32) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
POST_TYPE VARCHAR(20) NOT NULL,
CONTENT VARCHAR(5000),
IMAGES VARCHAR(2000),
VIDEO_URL VARCHAR(500),
LOCATION VARCHAR(200),
LIKE_COUNT INTEGER DEFAULT 0,
COMMENT_COUNT INTEGER DEFAULT 0,
SHARE_COUNT INTEGER DEFAULT 0,
POST_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_USER_POST PRIMARY KEY (POST_ID)
) ORGANIZE BY ROW;

# 动态点赞表
CREATE TABLE POST_LIKE (
LIKE_ID VARCHAR(32) NOT NULL,
POST_ID VARCHAR(32) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_POST_LIKE PRIMARY KEY (LIKE_ID)
) ORGANIZE BY ROW;

# 动态评论表
CREATE TABLE POST_COMMENT (
COMMENT_ID VARCHAR(32) NOT NULL,
POST_ID VARCHAR(32) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
PARENT_COMMENT_ID VARCHAR(32),
CONTENT VARCHAR(2000) NOT NULL,
LIKE_COUNT INTEGER DEFAULT 0,
COMMENT_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_POST_COMMENT PRIMARY KEY (COMMENT_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE INDEX IDX_USER_POST_USER ON USER_POST(USER_ID, CREATE_TIME DESC);
CREATE INDEX IDX_USER_POST_STATUS ON USER_POST(POST_STATUS, CREATE_TIME DESC);
CREATE UNIQUE INDEX IDX_POST_LIKE_RELATION ON POST_LIKE(POST_ID, USER_ID);
CREATE INDEX IDX_POST_LIKE_POST ON POST_LIKE(POST_ID);
CREATE INDEX IDX_POST_COMMENT_POST ON POST_COMMENT(POST_ID, CREATE_TIME DESC);
CREATE INDEX IDX_POST_COMMENT_PARENT ON POST_COMMENT(PARENT_COMMENT_ID);

# 关注用户存储过程
CREATE OR REPLACE PROCEDURE SP_FOLLOW_USER(
IN p_follower_id VARCHAR(32),
IN p_following_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_count INTEGER;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘关注成功’;

IF p_follower_id = p_following_id THEN
SET p_result_code = ‘FAIL’;
SET p_result_msg = ‘不能关注自己’;
RETURN;
END IF;

SELECT COUNT(*) INTO v_count
FROM USER_FOLLOW
WHERE FOLLOWER_ID = p_follower_id AND FOLLOWING_ID = p_following_id;

IF v_count > 0 THEN
SET p_result_code = ‘FAIL’;
SET p_result_msg = ‘已关注该用户’;
RETURN;
END IF;

INSERT INTO USER_FOLLOW (
FOLLOW_ID, FOLLOWER_ID, FOLLOWING_ID, FOLLOW_STATUS
) VALUES (
‘FOL’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISSFF6’),
p_follower_id,
p_following_id,
‘ACTIVE’
);

UPDATE USER_ACCOUNT
SET FOLLOWING_COUNT = FOLLOWING_COUNT + 1,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE USER_ID = p_follower_id;

UPDATE USER_ACCOUNT
SET FOLLOWER_COUNT = FOLLOWER_COUNT + 1,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE USER_ID = p_following_id;

END;

# 取消关注存储过程
CREATE OR REPLACE PROCEDURE SP_UNFOLLOW_USER(
IN p_follower_id VARCHAR(32),
IN p_following_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_count INTEGER;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘取消关注成功’;

SELECT COUNT(*) INTO v_count
FROM USER_FOLLOW
WHERE FOLLOWER_ID = p_follower_id AND FOLLOWING_ID = p_following_id;

IF v_count = 0 THEN
SET p_result_code = ‘FAIL’;
SET p_result_msg = ‘未关注该用户’;
RETURN;
END IF;

DELETE FROM USER_FOLLOW
WHERE FOLLOWER_ID = p_follower_id AND FOLLOWING_ID = p_following_id;

UPDATE USER_ACCOUNT
SET FOLLOWING_COUNT = FOLLOWING_COUNT – 1,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE USER_ID = p_follower_id;

UPDATE USER_ACCOUNT
SET FOLLOWER_COUNT = FOLLOWER_COUNT – 1,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE USER_ID = p_following_id;

END;

# 发布动态存储过程
CREATE OR REPLACE PROCEDURE SP_CREATE_POST(
IN p_user_id VARCHAR(32),
IN p_post_type VARCHAR(20),
IN p_content VARCHAR(5000),
IN p_images VARCHAR(2000),
IN p_video_url VARCHAR(500),
IN p_location VARCHAR(200),
OUT p_post_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘发布成功’;
SET p_post_id = ‘POST’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISSFF6’);

INSERT INTO USER_POST (
POST_ID, USER_ID, POST_TYPE, CONTENT,
IMAGES, VIDEO_URL, LOCATION, POST_STATUS
) VALUES (
p_post_id, p_user_id, p_post_type, p_content,
p_images, p_video_url, p_location, ‘ACTIVE’
);

UPDATE USER_ACCOUNT
SET POST_COUNT = POST_COUNT + 1,
UPDATE_TIME = CURRENT TIMESTAMP
WHERE USER_ID = p_user_id;

END;

# 私信表
CREATE TABLE PRIVATE_MESSAGE (
MESSAGE_ID VARCHAR(32) NOT NULL,
SENDER_ID VARCHAR(32) NOT NULL,
RECEIVER_ID VARCHAR(32) NOT NULL,
CONTENT VARCHAR(5000) NOT NULL,
MESSAGE_TYPE VARCHAR(20) NOT NULL,
IS_READ CHAR(1) DEFAULT ‘N’,
READ_TIME TIMESTAMP,
MESSAGE_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRIVATE_MESSAGE PRIMARY KEY (MESSAGE_ID)
) ORGANIZE BY ROW;

# 通知表
CREATE TABLE NOTIFICATION (
NOTIFICATION_ID VARCHAR(32) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
NOTIFICATION_TYPE VARCHAR(20) NOT NULL,
RELATED_ID VARCHAR(32),
RELATED_TYPE VARCHAR(20),
TITLE VARCHAR(200),
CONTENT VARCHAR(1000),
IS_READ CHAR(1) DEFAULT ‘N’,
READ_TIME TIMESTAMP,
NOTIFICATION_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_NOTIFICATION PRIMARY KEY (NOTIFICATION_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE INDEX IDX_PRIVATE_MESSAGE_SENDER ON PRIVATE_MESSAGE(SENDER_ID, CREATE_TIME DESC);
CREATE INDEX IDX_PRIVATE_MESSAGE_RECEIVER ON PRIVATE_MESSAGE(RECEIVER_ID, CREATE_TIME DESC);
CREATE INDEX IDX_NOTIFICATION_USER ON NOTIFICATION(USER_ID, IS_READ, CREATE_TIME DESC);

# 查询用户的关注动态
SELECT
p.POST_ID,
p.USER_ID,
u.NICKNAME,
u.AVATAR,
p.POST_TYPE,
p.CONTENT,
p.IMAGES,
p.VIDEO_URL,
p.LIKE_COUNT,
p.COMMENT_COUNT,
p.CREATE_TIME
FROM USER_POST p
INNER JOIN USER_FOLLOW f ON p.USER_ID = f.FOLLOWING_ID
INNER JOIN USER_ACCOUNT u ON p.USER_ID = u.USER_ID
WHERE f.FOLLOWER_ID = ‘U001’
AND f.FOLLOW_STATUS = ‘ACTIVE’
AND p.POST_STATUS = ‘ACTIVE’
ORDER BY p.CREATE_TIME DESC
FETCH FIRST 20 ROWS ONLY;

# 查询用户的未读消息
SELECT
m.MESSAGE_ID,
m.SENDER_ID,
u.NICKNAME,
u.AVATAR,
m.CONTENT,
m.MESSAGE_TYPE,
m.CREATE_TIME
FROM PRIVATE_MESSAGE m
INNER JOIN USER_ACCOUNT u ON m.SENDER_ID = u.USER_ID
WHERE m.RECEIVER_ID = ‘U001’
AND m.IS_READ = ‘N’
AND m.MESSAGE_STATUS = ‘ACTIVE’
ORDER BY m.CREATE_TIME DESC;

# 查询用户的未读通知
SELECT
n.NOTIFICATION_ID,
n.NOTIFICATION_TYPE,
n.TITLE,
n.CONTENT,
n.CREATE_TIME
FROM NOTIFICATION n
WHERE n.USER_ID = ‘U001’
AND n.IS_READ = ‘N’
AND n.NOTIFICATION_STATUS = ‘ACTIVE’
ORDER BY n.CREATE_TIME DESC;

# 标记消息为已读
CREATE OR REPLACE PROCEDURE SP_MARK_MESSAGE_READ(
IN p_message_id VARCHAR(32),
IN p_user_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_count INTEGER;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘操作成功’;

SELECT COUNT(*) INTO v_count
FROM PRIVATE_MESSAGE
WHERE MESSAGE_ID = p_message_id AND RECEIVER_ID = p_user_id;

IF v_count = 0 THEN
SET p_result_code = ‘FAIL’;
SET p_result_msg = ‘消息不存在’;
RETURN;
END IF;

UPDATE PRIVATE_MESSAGE
SET IS_READ = ‘Y’,
READ_TIME = CURRENT TIMESTAMP
WHERE MESSAGE_ID = p_message_id;

END;