zabudko-ruandClaude Opus 5.5
45d7512d2b
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
825 lines
27 KiB
SQL
825 lines
27 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "Locale" AS ENUM ('ru', 'en', 'zh', 'hy', 'es');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "CategoryCode" AS ENUM ('HOME', 'DOCS', 'LEARNING', 'PERKS');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ServiceFlow" AS ENUM ('CLEANING', 'REPAIR', 'TRANSFER', 'LANGUAGE', 'CAREER', 'DOCUMENTS');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ProviderRole" AS ENUM ('CLEANER', 'HANDYMAN', 'DRIVER', 'TEACHER', 'CAREER_CONSULTANT', 'DOCS_AGENT');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "OrderStatus" AS ENUM ('NEW', 'ASSIGNED', 'EN_ROUTE', 'CLEANING', 'ON_SITE', 'ESTIMATE_APPROVED', 'REPAIRING', 'DRIVER_WAITING', 'RIDE_IN_PROGRESS', 'ENROLLED', 'LESSON_SCHEDULED', 'LESSON_IN_PROGRESS', 'LESSON_DONE', 'CONSULTANT_ASSIGNED', 'RESUME_RECEIVED', 'MEETING', 'PLAN_SENT', 'DOCS_REVIEW', 'NEEDS_MORE_DOCS', 'SUBMITTED', 'DONE', 'COMPLETED', 'CANCELLED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ActorType" AS ENUM ('CLIENT', 'PROVIDER', 'DISPATCHER', 'ADMIN', 'SYSTEM');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "OfferStatus" AS ENUM ('OFFERED', 'ACCEPTED', 'DECLINED', 'EXPIRED', 'REVOKED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "PaymentStatus" AS ENUM ('UNPAID', 'PAID', 'REFUNDED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "RateType" AS ENUM ('FIXED', 'PERCENT');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "AdminRole" AS ENUM ('OWNER', 'OPERATOR');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "SubscriptionPlan" AS ENUM ('BAREV_PLUS');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "SubscriptionSource" AS ENUM ('AGENCY_GIFT', 'MANUAL', 'PAID');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "SubscriptionStatus" AS ENUM ('ACTIVE', 'EXPIRED', 'CANCELLED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "PromoKind" AS ENUM ('AGENCY_GIFT', 'DISCOUNT');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "FileKind" AS ENUM ('PHOTO_REPORT', 'CLIENT_PHOTO', 'DOCUMENT_SCAN', 'RESUME', 'OTHER');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "EstimateStatus" AS ENUM ('PENDING', 'ACCEPTED', 'REJECTED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "TaskStatus" AS ENUM ('OPEN', 'DONE');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "PayoutStatus" AS ENUM ('DRAFT', 'APPROVED', 'PAID');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"telegramId" BIGINT NOT NULL,
|
|
"username" TEXT,
|
|
"firstName" TEXT,
|
|
"lastName" TEXT,
|
|
"phone" TEXT,
|
|
"locale" "Locale" NOT NULL DEFAULT 'ru',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Client" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"consentAt" TIMESTAMP(3),
|
|
"consentVersion" TEXT,
|
|
"agencyId" TEXT,
|
|
"agencyAttachedAt" TIMESTAMP(3),
|
|
"source" TEXT NOT NULL DEFAULT 'organic',
|
|
"utm" JSONB,
|
|
"giftUsedAt" TIMESTAMP(3),
|
|
"cardCode" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Client_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ProviderCompany" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"roles" "ProviderRole"[],
|
|
"rateType" "RateType" NOT NULL DEFAULT 'PERCENT',
|
|
"rateValue" INTEGER NOT NULL DEFAULT 70,
|
|
"contactPhone" TEXT,
|
|
"inviteCode" TEXT NOT NULL,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "ProviderCompany_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CompanyDispatcher" (
|
|
"id" TEXT NOT NULL,
|
|
"companyId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "CompanyDispatcher_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Provider" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT,
|
|
"name" TEXT NOT NULL,
|
|
"phone" TEXT,
|
|
"role" "ProviderRole" NOT NULL,
|
|
"companyId" TEXT,
|
|
"languages" "Locale"[],
|
|
"rateType" "RateType" NOT NULL DEFAULT 'PERCENT',
|
|
"rateValue" INTEGER NOT NULL DEFAULT 70,
|
|
"ratingSum" INTEGER NOT NULL DEFAULT 0,
|
|
"ratingCount" INTEGER NOT NULL DEFAULT 0,
|
|
"onShift" BOOLEAN NOT NULL DEFAULT false,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"inviteCode" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Provider_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "District" (
|
|
"id" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "District_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ProviderDistrict" (
|
|
"providerId" TEXT NOT NULL,
|
|
"districtId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "ProviderDistrict_pkey" PRIMARY KEY ("providerId","districtId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ProviderSchedule" (
|
|
"id" TEXT NOT NULL,
|
|
"providerId" TEXT NOT NULL,
|
|
"weekday" INTEGER NOT NULL,
|
|
"startMinute" INTEGER NOT NULL,
|
|
"endMinute" INTEGER NOT NULL,
|
|
|
|
CONSTRAINT "ProviderSchedule_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Category" (
|
|
"id" TEXT NOT NULL,
|
|
"code" "CategoryCode" NOT NULL,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"barevPlusDiscountPct" INTEGER NOT NULL DEFAULT 0,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
CONSTRAINT "Category_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Service" (
|
|
"id" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"categoryId" TEXT NOT NULL,
|
|
"flow" "ServiceFlow" NOT NULL,
|
|
"providerRole" "ProviderRole" NOT NULL,
|
|
"pricing" JSONB NOT NULL,
|
|
"paramsSchema" JSONB NOT NULL,
|
|
"slotConfig" JSONB NOT NULL,
|
|
"lateCancelFeePct" INTEGER NOT NULL DEFAULT 30,
|
|
"isOnline" BOOLEAN NOT NULL DEFAULT false,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Service_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ChecklistItem" (
|
|
"id" TEXT NOT NULL,
|
|
"serviceId" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"requiresPhoto" BOOLEAN NOT NULL DEFAULT false,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "ChecklistItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "PaymentMethod" (
|
|
"id" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"name" JSONB NOT NULL,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "PaymentMethod_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Order" (
|
|
"id" TEXT NOT NULL,
|
|
"number" SERIAL NOT NULL,
|
|
"clientId" TEXT NOT NULL,
|
|
"serviceId" TEXT NOT NULL,
|
|
"params" JSONB NOT NULL,
|
|
"scheduledAt" TIMESTAMP(3) NOT NULL,
|
|
"durationMinutes" INTEGER NOT NULL,
|
|
"isOnline" BOOLEAN NOT NULL DEFAULT false,
|
|
"addressText" TEXT,
|
|
"districtId" TEXT,
|
|
"lat" DOUBLE PRECISION,
|
|
"lng" DOUBLE PRECISION,
|
|
"note" TEXT,
|
|
"locale" "Locale" NOT NULL,
|
|
"basePrice" INTEGER NOT NULL,
|
|
"discountPct" INTEGER NOT NULL DEFAULT 0,
|
|
"discountAmount" INTEGER NOT NULL DEFAULT 0,
|
|
"totalPrice" INTEGER NOT NULL,
|
|
"status" "OrderStatus" NOT NULL DEFAULT 'NEW',
|
|
"providerId" TEXT,
|
|
"companyId" TEXT,
|
|
"paymentMethodId" TEXT,
|
|
"paymentStatus" "PaymentStatus" NOT NULL DEFAULT 'UNPAID',
|
|
"paidAt" TIMESTAMP(3),
|
|
"cancelledAt" TIMESTAMP(3),
|
|
"cancelFee" INTEGER NOT NULL DEFAULT 0,
|
|
"cancelReason" TEXT,
|
|
"completedAt" TIMESTAMP(3),
|
|
"repeatOfId" TEXT,
|
|
"reminded24hAt" TIMESTAMP(3),
|
|
"reminded1hAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Order_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "OrderStatusHistory" (
|
|
"id" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"fromStatus" "OrderStatus",
|
|
"toStatus" "OrderStatus" NOT NULL,
|
|
"event" TEXT,
|
|
"actorType" "ActorType" NOT NULL,
|
|
"actorId" TEXT,
|
|
"comment" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "OrderStatusHistory_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "OrderOffer" (
|
|
"id" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"providerId" TEXT,
|
|
"companyId" TEXT,
|
|
"status" "OfferStatus" NOT NULL DEFAULT 'OFFERED',
|
|
"expiresAt" TIMESTAMP(3),
|
|
"respondedAt" TIMESTAMP(3),
|
|
"createdById" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "OrderOffer_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "OrderChecklistItem" (
|
|
"id" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"requiresPhoto" BOOLEAN NOT NULL DEFAULT false,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"checkedAt" TIMESTAMP(3),
|
|
"fileId" TEXT,
|
|
|
|
CONSTRAINT "OrderChecklistItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "OrderFile" (
|
|
"id" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"kind" "FileKind" NOT NULL,
|
|
"storageKey" TEXT NOT NULL,
|
|
"mime" TEXT NOT NULL,
|
|
"size" INTEGER NOT NULL,
|
|
"uploadedBy" "ActorType" NOT NULL,
|
|
"uploadedById" TEXT,
|
|
"deleteAfter" TIMESTAMP(3),
|
|
"deletedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "OrderFile_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "FileAccessLog" (
|
|
"id" TEXT NOT NULL,
|
|
"fileId" TEXT NOT NULL,
|
|
"actorType" "ActorType" NOT NULL,
|
|
"actorId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "FileAccessLog_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Estimate" (
|
|
"id" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"items" JSONB NOT NULL,
|
|
"total" INTEGER NOT NULL,
|
|
"status" "EstimateStatus" NOT NULL DEFAULT 'PENDING',
|
|
"respondedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Estimate_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Message" (
|
|
"id" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"fromType" "ActorType" NOT NULL,
|
|
"fromUserId" TEXT,
|
|
"text" TEXT,
|
|
"fileId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Message_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Rating" (
|
|
"id" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"clientId" TEXT NOT NULL,
|
|
"providerId" TEXT,
|
|
"score" INTEGER NOT NULL,
|
|
"comment" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Rating_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "LessonPackage" (
|
|
"id" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"total" INTEGER NOT NULL,
|
|
"completed" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "LessonPackage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Payment" (
|
|
"id" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"provider" TEXT NOT NULL,
|
|
"amount" INTEGER NOT NULL,
|
|
"status" "PaymentStatus" NOT NULL,
|
|
"externalId" TEXT,
|
|
"markedById" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Payment_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "OperatorTask" (
|
|
"id" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"orderId" TEXT,
|
|
"status" "TaskStatus" NOT NULL DEFAULT 'OPEN',
|
|
"note" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"doneAt" TIMESTAMP(3),
|
|
|
|
CONSTRAINT "OperatorTask_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Agency" (
|
|
"id" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"commissionPct" INTEGER NOT NULL DEFAULT 10,
|
|
"contact" TEXT,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Agency_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AgencyCommission" (
|
|
"id" TEXT NOT NULL,
|
|
"agencyId" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"pct" INTEGER NOT NULL,
|
|
"amount" INTEGER NOT NULL,
|
|
"period" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "AgencyCommission_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "PromoCode" (
|
|
"id" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"kind" "PromoKind" NOT NULL,
|
|
"agencyId" TEXT,
|
|
"discountPct" INTEGER,
|
|
"maxUses" INTEGER,
|
|
"usedCount" INTEGER NOT NULL DEFAULT 0,
|
|
"validFrom" TIMESTAMP(3),
|
|
"validUntil" TIMESTAMP(3),
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "PromoCode_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "PromoCodeUse" (
|
|
"id" TEXT NOT NULL,
|
|
"promoId" TEXT NOT NULL,
|
|
"clientId" TEXT NOT NULL,
|
|
"orderId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "PromoCodeUse_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Subscription" (
|
|
"id" TEXT NOT NULL,
|
|
"clientId" TEXT NOT NULL,
|
|
"plan" "SubscriptionPlan" NOT NULL DEFAULT 'BAREV_PLUS',
|
|
"source" "SubscriptionSource" NOT NULL,
|
|
"status" "SubscriptionStatus" NOT NULL DEFAULT 'ACTIVE',
|
|
"startsAt" TIMESTAMP(3) NOT NULL,
|
|
"endsAt" TIMESTAMP(3) NOT NULL,
|
|
"remindedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Subscription_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Partner" (
|
|
"id" TEXT NOT NULL,
|
|
"category" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"offer" JSONB NOT NULL,
|
|
"terms" JSONB,
|
|
"address" TEXT,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Partner_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CardUsage" (
|
|
"id" TEXT NOT NULL,
|
|
"clientId" TEXT NOT NULL,
|
|
"partnerId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "CardUsage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Payout" (
|
|
"id" TEXT NOT NULL,
|
|
"providerId" TEXT,
|
|
"companyId" TEXT,
|
|
"periodStart" TIMESTAMP(3) NOT NULL,
|
|
"periodEnd" TIMESTAMP(3) NOT NULL,
|
|
"amount" INTEGER NOT NULL,
|
|
"status" "PayoutStatus" NOT NULL DEFAULT 'DRAFT',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Payout_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AdminUser" (
|
|
"id" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"passwordHash" TEXT NOT NULL,
|
|
"role" "AdminRole" NOT NULL DEFAULT 'OPERATOR',
|
|
"totpSecret" TEXT,
|
|
"totpEnabled" BOOLEAN NOT NULL DEFAULT false,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "AdminUser_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AdminSession" (
|
|
"id" TEXT NOT NULL,
|
|
"adminUserId" TEXT NOT NULL,
|
|
"tokenHash" TEXT NOT NULL,
|
|
"verified" BOOLEAN NOT NULL DEFAULT false,
|
|
"expiresAt" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "AdminSession_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AuditLog" (
|
|
"id" TEXT NOT NULL,
|
|
"adminUserId" TEXT,
|
|
"action" TEXT NOT NULL,
|
|
"entity" TEXT NOT NULL,
|
|
"entityId" TEXT,
|
|
"data" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "AuditLog_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Setting" (
|
|
"key" TEXT NOT NULL,
|
|
"value" JSONB NOT NULL,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Setting_pkey" PRIMARY KEY ("key")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "StartToken" (
|
|
"id" TEXT NOT NULL,
|
|
"locale" "Locale",
|
|
"agency" TEXT,
|
|
"utm" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "StartToken_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_telegramId_key" ON "User"("telegramId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Client_userId_key" ON "Client"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Client_cardCode_key" ON "Client"("cardCode");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Client_agencyId_idx" ON "Client"("agencyId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ProviderCompany_inviteCode_key" ON "ProviderCompany"("inviteCode");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "CompanyDispatcher_companyId_userId_key" ON "CompanyDispatcher"("companyId", "userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Provider_userId_key" ON "Provider"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Provider_inviteCode_key" ON "Provider"("inviteCode");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Provider_role_active_idx" ON "Provider"("role", "active");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "District_code_key" ON "District"("code");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ProviderSchedule_providerId_weekday_idx" ON "ProviderSchedule"("providerId", "weekday");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Category_code_key" ON "Category"("code");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Service_code_key" ON "Service"("code");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ChecklistItem_serviceId_code_key" ON "ChecklistItem"("serviceId", "code");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "PaymentMethod_code_key" ON "PaymentMethod"("code");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Order_number_key" ON "Order"("number");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Order_status_idx" ON "Order"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Order_scheduledAt_idx" ON "Order"("scheduledAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Order_clientId_idx" ON "Order"("clientId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Order_providerId_scheduledAt_idx" ON "Order"("providerId", "scheduledAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "OrderStatusHistory_orderId_createdAt_idx" ON "OrderStatusHistory"("orderId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "OrderOffer_orderId_status_idx" ON "OrderOffer"("orderId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "OrderOffer_providerId_status_idx" ON "OrderOffer"("providerId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "OrderOffer_companyId_status_idx" ON "OrderOffer"("companyId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "OrderChecklistItem_fileId_key" ON "OrderChecklistItem"("fileId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "OrderChecklistItem_orderId_code_key" ON "OrderChecklistItem"("orderId", "code");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "OrderFile_storageKey_key" ON "OrderFile"("storageKey");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Message_orderId_createdAt_idx" ON "Message"("orderId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Rating_orderId_key" ON "Rating"("orderId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "LessonPackage_orderId_key" ON "LessonPackage"("orderId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Agency_code_key" ON "Agency"("code");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "AgencyCommission_orderId_key" ON "AgencyCommission"("orderId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "AgencyCommission_agencyId_period_idx" ON "AgencyCommission"("agencyId", "period");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "PromoCode_code_key" ON "PromoCode"("code");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "PromoCodeUse_promoId_clientId_key" ON "PromoCodeUse"("promoId", "clientId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Subscription_clientId_endsAt_idx" ON "Subscription"("clientId", "endsAt");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "AdminUser_email_key" ON "AdminUser"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "AdminSession_tokenHash_key" ON "AdminSession"("tokenHash");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "AuditLog_entity_entityId_idx" ON "AuditLog"("entity", "entityId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Client" ADD CONSTRAINT "Client_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Client" ADD CONSTRAINT "Client_agencyId_fkey" FOREIGN KEY ("agencyId") REFERENCES "Agency"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CompanyDispatcher" ADD CONSTRAINT "CompanyDispatcher_companyId_fkey" FOREIGN KEY ("companyId") REFERENCES "ProviderCompany"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CompanyDispatcher" ADD CONSTRAINT "CompanyDispatcher_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Provider" ADD CONSTRAINT "Provider_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Provider" ADD CONSTRAINT "Provider_companyId_fkey" FOREIGN KEY ("companyId") REFERENCES "ProviderCompany"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ProviderDistrict" ADD CONSTRAINT "ProviderDistrict_providerId_fkey" FOREIGN KEY ("providerId") REFERENCES "Provider"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ProviderDistrict" ADD CONSTRAINT "ProviderDistrict_districtId_fkey" FOREIGN KEY ("districtId") REFERENCES "District"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ProviderSchedule" ADD CONSTRAINT "ProviderSchedule_providerId_fkey" FOREIGN KEY ("providerId") REFERENCES "Provider"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Service" ADD CONSTRAINT "Service_categoryId_fkey" FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ChecklistItem" ADD CONSTRAINT "ChecklistItem_serviceId_fkey" FOREIGN KEY ("serviceId") REFERENCES "Service"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Order" ADD CONSTRAINT "Order_clientId_fkey" FOREIGN KEY ("clientId") REFERENCES "Client"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Order" ADD CONSTRAINT "Order_serviceId_fkey" FOREIGN KEY ("serviceId") REFERENCES "Service"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Order" ADD CONSTRAINT "Order_districtId_fkey" FOREIGN KEY ("districtId") REFERENCES "District"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Order" ADD CONSTRAINT "Order_providerId_fkey" FOREIGN KEY ("providerId") REFERENCES "Provider"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Order" ADD CONSTRAINT "Order_companyId_fkey" FOREIGN KEY ("companyId") REFERENCES "ProviderCompany"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Order" ADD CONSTRAINT "Order_paymentMethodId_fkey" FOREIGN KEY ("paymentMethodId") REFERENCES "PaymentMethod"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OrderStatusHistory" ADD CONSTRAINT "OrderStatusHistory_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OrderOffer" ADD CONSTRAINT "OrderOffer_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OrderOffer" ADD CONSTRAINT "OrderOffer_providerId_fkey" FOREIGN KEY ("providerId") REFERENCES "Provider"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OrderOffer" ADD CONSTRAINT "OrderOffer_companyId_fkey" FOREIGN KEY ("companyId") REFERENCES "ProviderCompany"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OrderChecklistItem" ADD CONSTRAINT "OrderChecklistItem_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OrderChecklistItem" ADD CONSTRAINT "OrderChecklistItem_fileId_fkey" FOREIGN KEY ("fileId") REFERENCES "OrderFile"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OrderFile" ADD CONSTRAINT "OrderFile_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "FileAccessLog" ADD CONSTRAINT "FileAccessLog_fileId_fkey" FOREIGN KEY ("fileId") REFERENCES "OrderFile"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Estimate" ADD CONSTRAINT "Estimate_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Message" ADD CONSTRAINT "Message_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Message" ADD CONSTRAINT "Message_fromUserId_fkey" FOREIGN KEY ("fromUserId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Rating" ADD CONSTRAINT "Rating_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Rating" ADD CONSTRAINT "Rating_clientId_fkey" FOREIGN KEY ("clientId") REFERENCES "Client"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Rating" ADD CONSTRAINT "Rating_providerId_fkey" FOREIGN KEY ("providerId") REFERENCES "Provider"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "LessonPackage" ADD CONSTRAINT "LessonPackage_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Payment" ADD CONSTRAINT "Payment_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "OperatorTask" ADD CONSTRAINT "OperatorTask_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AgencyCommission" ADD CONSTRAINT "AgencyCommission_agencyId_fkey" FOREIGN KEY ("agencyId") REFERENCES "Agency"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AgencyCommission" ADD CONSTRAINT "AgencyCommission_orderId_fkey" FOREIGN KEY ("orderId") REFERENCES "Order"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "PromoCode" ADD CONSTRAINT "PromoCode_agencyId_fkey" FOREIGN KEY ("agencyId") REFERENCES "Agency"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "PromoCodeUse" ADD CONSTRAINT "PromoCodeUse_promoId_fkey" FOREIGN KEY ("promoId") REFERENCES "PromoCode"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "PromoCodeUse" ADD CONSTRAINT "PromoCodeUse_clientId_fkey" FOREIGN KEY ("clientId") REFERENCES "Client"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Subscription" ADD CONSTRAINT "Subscription_clientId_fkey" FOREIGN KEY ("clientId") REFERENCES "Client"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CardUsage" ADD CONSTRAINT "CardUsage_clientId_fkey" FOREIGN KEY ("clientId") REFERENCES "Client"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CardUsage" ADD CONSTRAINT "CardUsage_partnerId_fkey" FOREIGN KEY ("partnerId") REFERENCES "Partner"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Payout" ADD CONSTRAINT "Payout_providerId_fkey" FOREIGN KEY ("providerId") REFERENCES "Provider"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Payout" ADD CONSTRAINT "Payout_companyId_fkey" FOREIGN KEY ("companyId") REFERENCES "ProviderCompany"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AdminSession" ADD CONSTRAINT "AdminSession_adminUserId_fkey" FOREIGN KEY ("adminUserId") REFERENCES "AdminUser"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AuditLog" ADD CONSTRAINT "AuditLog_adminUserId_fkey" FOREIGN KEY ("adminUserId") REFERENCES "AdminUser"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|