314 lines
10 KiB
SQL
314 lines
10 KiB
SQL
-- CreateSchema
|
|
CREATE SCHEMA IF NOT EXISTS "public";
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "public"."ProductType" AS ENUM ('hamkar', 'khanegi');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "public"."UserRole" AS ENUM ('super_admin', 'viewer');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "public"."SmsLedgerType" AS ENUM ('charge', 'debit', 'adjust');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."users" (
|
|
"id" TEXT NOT NULL,
|
|
"mobile" TEXT NOT NULL,
|
|
"first_name" TEXT NOT NULL,
|
|
"last_name" TEXT NOT NULL,
|
|
"email" TEXT,
|
|
"password_hash" TEXT NOT NULL,
|
|
"role" "public"."UserRole" NOT NULL DEFAULT 'super_admin',
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "users_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."tenants" (
|
|
"id" TEXT NOT NULL,
|
|
"product" "public"."ProductType" NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"name_fa" TEXT NOT NULL,
|
|
"name_en" TEXT,
|
|
"domain" TEXT NOT NULL,
|
|
"domains" JSONB,
|
|
"avatar_url" TEXT,
|
|
"primary_color" TEXT,
|
|
"secondary_color" TEXT,
|
|
"background_color" TEXT,
|
|
"node_server" TEXT NOT NULL,
|
|
"node_ws" TEXT,
|
|
"app_url" TEXT,
|
|
"admin_url" TEXT,
|
|
"remote_api_token" TEXT,
|
|
"sms_settings_cache" JSONB,
|
|
"sms_synced_at" TIMESTAMP(3),
|
|
"app_ui_config" JSONB,
|
|
"app_version" TEXT NOT NULL DEFAULT '1',
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"notes" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "tenants_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."sms_accounts" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"balance_rials" INTEGER NOT NULL DEFAULT 0,
|
|
"price_per_part_rials" INTEGER NOT NULL DEFAULT 2500,
|
|
"is_blocked" BOOLEAN NOT NULL DEFAULT false,
|
|
"alert_critical_rials" INTEGER NOT NULL DEFAULT 10000,
|
|
"alert_warning_rials" INTEGER NOT NULL DEFAULT 100000,
|
|
"gateway_provider" TEXT,
|
|
"melipayamak_user" TEXT,
|
|
"melipayamak_password" TEXT,
|
|
"melipayamak_from" TEXT,
|
|
"panel_username" TEXT,
|
|
"panel_password" TEXT,
|
|
"panel_from" TEXT,
|
|
"charge_card" TEXT,
|
|
"charge_iban" TEXT,
|
|
"charge_holder" TEXT,
|
|
"charge_instruction" TEXT,
|
|
"extra_settings" JSONB,
|
|
"today_messages" INTEGER NOT NULL DEFAULT 0,
|
|
"today_parts" INTEGER NOT NULL DEFAULT 0,
|
|
"today_cost_rials" INTEGER NOT NULL DEFAULT 0,
|
|
"month_messages" INTEGER NOT NULL DEFAULT 0,
|
|
"month_parts" INTEGER NOT NULL DEFAULT 0,
|
|
"month_cost_rials" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "sms_accounts_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."sms_ledgers" (
|
|
"id" TEXT NOT NULL,
|
|
"sms_account_id" TEXT NOT NULL,
|
|
"type" "public"."SmsLedgerType" NOT NULL,
|
|
"amount_rials" INTEGER NOT NULL,
|
|
"balance_after_rials" INTEGER,
|
|
"parts" INTEGER,
|
|
"note" TEXT,
|
|
"created_by_id" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "sms_ledgers_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."sms_send_logs" (
|
|
"id" TEXT NOT NULL,
|
|
"sms_account_id" TEXT NOT NULL,
|
|
"receiver" TEXT NOT NULL,
|
|
"body" TEXT NOT NULL,
|
|
"parts" INTEGER NOT NULL DEFAULT 1,
|
|
"price_per_part_rials" INTEGER NOT NULL,
|
|
"total_amount_rials" INTEGER NOT NULL,
|
|
"provider" TEXT,
|
|
"source" TEXT,
|
|
"is_alert" BOOLEAN NOT NULL DEFAULT false,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "sms_send_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."audit_logs" (
|
|
"id" TEXT NOT NULL,
|
|
"action" TEXT NOT NULL,
|
|
"entity_type" TEXT NOT NULL,
|
|
"entity_id" TEXT,
|
|
"tenant_id" TEXT,
|
|
"user_id" TEXT,
|
|
"before" JSONB,
|
|
"after" JSONB,
|
|
"meta" JSONB,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."server_accounts" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"is_dedicated" BOOLEAN NOT NULL DEFAULT false,
|
|
"server_name" TEXT,
|
|
"specs" TEXT,
|
|
"price_rials" INTEGER,
|
|
"charged_at" TIMESTAMP(3),
|
|
"plan_months" INTEGER,
|
|
"expires_at" TIMESTAMP(3),
|
|
"description" TEXT,
|
|
"alert_days_before" INTEGER NOT NULL DEFAULT 3,
|
|
"last_alert_date" TIMESTAMP(3),
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "server_accounts_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."server_charges" (
|
|
"id" TEXT NOT NULL,
|
|
"server_account_id" TEXT NOT NULL,
|
|
"charged_at" TIMESTAMP(3) NOT NULL,
|
|
"plan_months" INTEGER NOT NULL,
|
|
"price_rials" INTEGER NOT NULL,
|
|
"expires_at" TIMESTAMP(3) NOT NULL,
|
|
"note" TEXT,
|
|
"created_by_id" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "server_charges_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."plugin_subscriptions" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"specs" TEXT,
|
|
"price_rials" INTEGER,
|
|
"charged_at" TIMESTAMP(3),
|
|
"plan_months" INTEGER,
|
|
"expires_at" TIMESTAMP(3),
|
|
"description" TEXT,
|
|
"alert_days_before" INTEGER NOT NULL DEFAULT 3,
|
|
"last_alert_date" TIMESTAMP(3),
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "plugin_subscriptions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."plugin_charges" (
|
|
"id" TEXT NOT NULL,
|
|
"plugin_id" TEXT NOT NULL,
|
|
"charged_at" TIMESTAMP(3) NOT NULL,
|
|
"plan_months" INTEGER NOT NULL,
|
|
"price_rials" INTEGER NOT NULL,
|
|
"expires_at" TIMESTAMP(3) NOT NULL,
|
|
"note" TEXT,
|
|
"created_by_id" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "plugin_charges_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "users_mobile_key" ON "public"."users"("mobile");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "users_email_key" ON "public"."users"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "tenants_slug_key" ON "public"."tenants"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "tenants_domain_key" ON "public"."tenants"("domain");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "tenants_product_idx" ON "public"."tenants"("product");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "tenants_is_active_idx" ON "public"."tenants"("is_active");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "sms_accounts_tenant_id_key" ON "public"."sms_accounts"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "sms_ledgers_sms_account_id_idx" ON "public"."sms_ledgers"("sms_account_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "sms_ledgers_created_at_idx" ON "public"."sms_ledgers"("created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "sms_ledgers_type_idx" ON "public"."sms_ledgers"("type");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "sms_send_logs_sms_account_id_idx" ON "public"."sms_send_logs"("sms_account_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "sms_send_logs_created_at_idx" ON "public"."sms_send_logs"("created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "sms_send_logs_provider_idx" ON "public"."sms_send_logs"("provider");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "audit_logs_tenant_id_idx" ON "public"."audit_logs"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "audit_logs_user_id_idx" ON "public"."audit_logs"("user_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "audit_logs_created_at_idx" ON "public"."audit_logs"("created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "server_accounts_tenant_id_key" ON "public"."server_accounts"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "server_accounts_expires_at_idx" ON "public"."server_accounts"("expires_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "server_accounts_is_dedicated_idx" ON "public"."server_accounts"("is_dedicated");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "server_charges_server_account_id_idx" ON "public"."server_charges"("server_account_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "server_charges_created_at_idx" ON "public"."server_charges"("created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "plugin_subscriptions_tenant_id_idx" ON "public"."plugin_subscriptions"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "plugin_subscriptions_expires_at_idx" ON "public"."plugin_subscriptions"("expires_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "plugin_subscriptions_is_active_idx" ON "public"."plugin_subscriptions"("is_active");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "plugin_charges_plugin_id_idx" ON "public"."plugin_charges"("plugin_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "plugin_charges_created_at_idx" ON "public"."plugin_charges"("created_at");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."sms_accounts" ADD CONSTRAINT "sms_accounts_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."sms_ledgers" ADD CONSTRAINT "sms_ledgers_sms_account_id_fkey" FOREIGN KEY ("sms_account_id") REFERENCES "public"."sms_accounts"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."sms_send_logs" ADD CONSTRAINT "sms_send_logs_sms_account_id_fkey" FOREIGN KEY ("sms_account_id") REFERENCES "public"."sms_accounts"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."audit_logs" ADD CONSTRAINT "audit_logs_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."audit_logs" ADD CONSTRAINT "audit_logs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."server_accounts" ADD CONSTRAINT "server_accounts_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."server_charges" ADD CONSTRAINT "server_charges_server_account_id_fkey" FOREIGN KEY ("server_account_id") REFERENCES "public"."server_accounts"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."plugin_subscriptions" ADD CONSTRAINT "plugin_subscriptions_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."plugin_charges" ADD CONSTRAINT "plugin_charges_plugin_id_fkey" FOREIGN KEY ("plugin_id") REFERENCES "public"."plugin_subscriptions"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|