72 lines
2.1 KiB
MySQL
72 lines
2.1 KiB
MySQL
|
-- CreateSchema
|
||
|
|
CREATE SCHEMA IF NOT EXISTS "public";
|
||
|
|
|
||
|
|
-- CreateEnum
|
||
|
|
CREATE TYPE "OrderStatus" AS ENUM ('PENDING', 'PAID', 'RECHARGING', 'COMPLETED', 'EXPIRED', 'CANCELLED', 'FAILED', 'REFUNDING', 'REFUNDED', 'REFUND_FAILED');
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "orders" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"user_id" INTEGER NOT NULL,
|
||
|
|
"user_email" TEXT,
|
||
|
|
"user_name" TEXT,
|
||
|
|
"amount" DECIMAL(10,2) NOT NULL,
|
||
|
|
"recharge_code" TEXT NOT NULL,
|
||
|
|
"status" "OrderStatus" NOT NULL DEFAULT 'PENDING',
|
||
|
|
"payment_type" TEXT NOT NULL,
|
||
|
|
"zpay_trade_no" TEXT,
|
||
|
|
"pay_url" TEXT,
|
||
|
|
"qr_code" TEXT,
|
||
|
|
"qr_code_img" TEXT,
|
||
|
|
"refund_amount" DECIMAL(10,2),
|
||
|
|
"refund_reason" TEXT,
|
||
|
|
"refund_at" TIMESTAMP(3),
|
||
|
|
"force_refund" BOOLEAN NOT NULL DEFAULT false,
|
||
|
|
"expires_at" TIMESTAMP(3) NOT NULL,
|
||
|
|
"paid_at" TIMESTAMP(3),
|
||
|
|
"completed_at" TIMESTAMP(3),
|
||
|
|
"failed_at" TIMESTAMP(3),
|
||
|
|
"failed_reason" TEXT,
|
||
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
|
|
"client_ip" TEXT,
|
||
|
|
|
||
|
|
CONSTRAINT "orders_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateTable
|
||
|
|
CREATE TABLE "audit_logs" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"order_id" TEXT NOT NULL,
|
||
|
|
"action" TEXT NOT NULL,
|
||
|
|
"detail" TEXT,
|
||
|
|
"operator" TEXT,
|
||
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE UNIQUE INDEX "orders_recharge_code_key" ON "orders"("recharge_code");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "orders_user_id_idx" ON "orders"("user_id");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "orders_status_idx" ON "orders"("status");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "orders_expires_at_idx" ON "orders"("expires_at");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "orders_created_at_idx" ON "orders"("created_at");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "audit_logs_order_id_idx" ON "audit_logs"("order_id");
|
||
|
|
|
||
|
|
-- CreateIndex
|
||
|
|
CREATE INDEX "audit_logs_created_at_idx" ON "audit_logs"("created_at");
|
||
|
|
|
||
|
|
-- AddForeignKey
|
||
|
|
ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_order_id_fkey" FOREIGN KEY ("order_id") REFERENCES "orders"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|