# 用户服务数据库
CREATE DATABASE USERDB;

# 订单服务数据库
CREATE DATABASE ORDERDB;

# 商品服务数据库
CREATE DATABASE PRODUCTDB;

# 支付服务数据库
CREATE DATABASE PAYMENTDB;

# 交易数据库
CREATE DATABASE TRADINGDB;

# 报表数据库
CREATE DATABASE REPORTDB;

# 日志数据库
CREATE DATABASE LOGDB;

# 用户服务数据库
CONNECT TO USERDB;

CREATE TABLE USER_INFO (
USER_ID VARCHAR(32) NOT NULL,
USER_NAME VARCHAR(100) NOT NULL,
EMAIL VARCHAR(100),
PHONE VARCHAR(20),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_USER_INFO PRIMARY KEY (USER_ID)
);

CREATE TABLE USER_PROFILE (
USER_ID VARCHAR(32) NOT NULL,
NICKNAME VARCHAR(50),
AVATAR VARCHAR(200),
CONSTRAINT PK_USER_PROFILE PRIMARY KEY (USER_ID)
);

CONNECT RESET;

# 订单服务数据库
CONNECT TO ORDERDB;

CREATE TABLE ORDER_MASTER (
ORDER_ID VARCHAR(32) NOT NULL,
USER_ID VARCHAR(32) NOT NULL,
ORDER_STATUS VARCHAR(20) NOT NULL,
TOTAL_AMOUNT DECIMAL(18, 2) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ORDER_MASTER PRIMARY KEY (ORDER_ID)
);

CREATE TABLE ORDER_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
ORDER_ID VARCHAR(32) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
QUANTITY INTEGER NOT NULL,
UNIT_PRICE DECIMAL(18, 2) NOT NULL,
CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (DETAIL_ID)
);

CONNECT RESET;

# 使用XA事务
XA START ‘xid1’;
INSERT INTO USERDB.USER_INFO VALUES (‘U001’, ‘user1’, ‘user1@email.com’, ‘13800138000’, CURRENT TIMESTAMP);
XA END ‘xid1’;
XA PREPARE ‘xid1’;
XA COMMIT ‘xid1’;

# 使用消息队列实现最终一致性
CREATE TABLE EVENT_LOG (
EVENT_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
EVENT_TYPE VARCHAR(50) NOT NULL,
EVENT_DATA VARCHAR(1000) NOT NULL,
EVENT_STATUS VARCHAR(20) NOT NULL DEFAULT ‘PENDING’,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_EVENT_LOG PRIMARY KEY (EVENT_ID)
);

CREATE INDEX IDX_EVENT_STATUS ON EVENT_LOG(EVENT_STATUS, CREATE_TIME);