76 lines
2.6 KiB
SQL
76 lines
2.6 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "BookingStatus" AS ENUM ('PENDING', 'AWAITING_PAY', 'PAID', 'CANCELLED', 'REFUNDED', 'EXPIRED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "PaymentProvider" AS ENUM ('MANUAL', 'MIDTRANS');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "PaymentStatus" AS ENUM ('PENDING', 'AWAITING', 'PAID', 'FAILED', 'EXPIRED', 'CANCELLED', 'REFUNDED');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Booking" (
|
|
"id" TEXT NOT NULL,
|
|
"tripId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"participantId" TEXT NOT NULL,
|
|
"amount" INTEGER NOT NULL,
|
|
"currency" TEXT NOT NULL DEFAULT 'IDR',
|
|
"status" "BookingStatus" NOT NULL DEFAULT 'PENDING',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Booking_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Payment" (
|
|
"id" TEXT NOT NULL,
|
|
"bookingId" TEXT NOT NULL,
|
|
"provider" "PaymentProvider" NOT NULL,
|
|
"externalOrderId" TEXT NOT NULL,
|
|
"externalTxId" TEXT,
|
|
"method" TEXT,
|
|
"amount" INTEGER NOT NULL,
|
|
"status" "PaymentStatus" NOT NULL DEFAULT 'PENDING',
|
|
"rawCallback" JSONB,
|
|
"snapToken" TEXT,
|
|
"expiresAt" TIMESTAMP(3),
|
|
"paidAt" TIMESTAMP(3),
|
|
"failedAt" TIMESTAMP(3),
|
|
"rejectionReason" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Payment_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Booking_participantId_key" ON "Booking"("participantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Booking_tripId_status_idx" ON "Booking"("tripId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Booking_userId_idx" ON "Booking"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Payment_externalOrderId_key" ON "Payment"("externalOrderId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Payment_bookingId_status_idx" ON "Payment"("bookingId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Payment_provider_status_idx" ON "Payment"("provider", "status");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_tripId_fkey" FOREIGN KEY ("tripId") REFERENCES "Trip"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_participantId_fkey" FOREIGN KEY ("participantId") REFERENCES "TripParticipant"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Payment" ADD CONSTRAINT "Payment_bookingId_fkey" FOREIGN KEY ("bookingId") REFERENCES "Booking"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|