95 lines
4.0 KiB
SQL
95 lines
4.0 KiB
SQL
-- Phase 9: WHMCS integration and billing suspension
|
|
|
|
CREATE TYPE "WhmcsServiceStatus" AS ENUM ('PENDING', 'ACTIVE', 'SUSPENDED', 'TERMINATED');
|
|
CREATE TYPE "IntegrationOperationStatus" AS ENUM ('COMPLETED', 'FAILED');
|
|
|
|
ALTER TABLE "game_servers"
|
|
ADD COLUMN IF NOT EXISTS "billingSuspendedAt" TIMESTAMPTZ(3),
|
|
ADD COLUMN IF NOT EXISTS "billingSuspendReason" TEXT;
|
|
|
|
CREATE TABLE IF NOT EXISTS "whmcs_installations" (
|
|
"id" TEXT NOT NULL,
|
|
"integrationId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"apiSecret" TEXT NOT NULL,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"metadata" JSONB,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
CONSTRAINT "whmcs_installations_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "whmcs_installations_integrationId_key"
|
|
ON "whmcs_installations"("integrationId");
|
|
|
|
CREATE TABLE IF NOT EXISTS "whmcs_client_links" (
|
|
"id" TEXT NOT NULL,
|
|
"installationId" TEXT NOT NULL,
|
|
"externalClientId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
CONSTRAINT "whmcs_client_links_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "whmcs_client_links_userId_key" ON "whmcs_client_links"("userId");
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "whmcs_client_links_installationId_externalClientId_key"
|
|
ON "whmcs_client_links"("installationId", "externalClientId");
|
|
|
|
CREATE TABLE IF NOT EXISTS "whmcs_service_links" (
|
|
"id" TEXT NOT NULL,
|
|
"installationId" TEXT NOT NULL,
|
|
"externalServiceId" TEXT NOT NULL,
|
|
"serverId" TEXT NOT NULL,
|
|
"externalProductId" TEXT,
|
|
"status" "WhmcsServiceStatus" NOT NULL DEFAULT 'PENDING',
|
|
"suspendedAt" TIMESTAMPTZ(3),
|
|
"terminatedAt" TIMESTAMPTZ(3),
|
|
"metadata" JSONB,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
CONSTRAINT "whmcs_service_links_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "whmcs_service_links_serverId_key" ON "whmcs_service_links"("serverId");
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "whmcs_service_links_installationId_externalServiceId_key"
|
|
ON "whmcs_service_links"("installationId", "externalServiceId");
|
|
CREATE INDEX IF NOT EXISTS "whmcs_service_links_installationId_status_idx"
|
|
ON "whmcs_service_links"("installationId", "status");
|
|
|
|
CREATE TABLE IF NOT EXISTS "integration_operations" (
|
|
"id" TEXT NOT NULL,
|
|
"installationId" TEXT NOT NULL,
|
|
"idempotencyKey" TEXT NOT NULL,
|
|
"operation" TEXT NOT NULL,
|
|
"externalRef" TEXT,
|
|
"status" "IntegrationOperationStatus" NOT NULL DEFAULT 'COMPLETED',
|
|
"result" JSONB,
|
|
"error" TEXT,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "integration_operations_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "integration_operations_idempotencyKey_key"
|
|
ON "integration_operations"("idempotencyKey");
|
|
CREATE INDEX IF NOT EXISTS "integration_operations_installationId_createdAt_idx"
|
|
ON "integration_operations"("installationId", "createdAt");
|
|
|
|
ALTER TABLE "whmcs_client_links"
|
|
ADD CONSTRAINT "whmcs_client_links_installationId_fkey"
|
|
FOREIGN KEY ("installationId") REFERENCES "whmcs_installations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "whmcs_client_links"
|
|
ADD CONSTRAINT "whmcs_client_links_userId_fkey"
|
|
FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "whmcs_service_links"
|
|
ADD CONSTRAINT "whmcs_service_links_installationId_fkey"
|
|
FOREIGN KEY ("installationId") REFERENCES "whmcs_installations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "whmcs_service_links"
|
|
ADD CONSTRAINT "whmcs_service_links_serverId_fkey"
|
|
FOREIGN KEY ("serverId") REFERENCES "game_servers"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "integration_operations"
|
|
ADD CONSTRAINT "integration_operations_installationId_fkey"
|
|
FOREIGN KEY ("installationId") REFERENCES "whmcs_installations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|