changemaker.lite/changemaker-control-panel/api/prisma/migrations/0001_init/migration.sql

-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "public";

-- CreateEnum
CREATE TYPE "CcpRole" AS ENUM ('SUPER_ADMIN', 'OPERATOR', 'VIEWER');

-- CreateEnum
CREATE TYPE "InstanceStatus" AS ENUM ('PROVISIONING', 'RUNNING', 'STOPPED', 'ERROR', 'DESTROYING');

-- CreateEnum
CREATE TYPE "HealthStatus" AS ENUM ('HEALTHY', 'DEGRADED', 'UNHEALTHY', 'UNKNOWN');

-- CreateEnum
CREATE TYPE "BackupStatus" AS ENUM ('PENDING', 'IN_PROGRESS', 'COMPLETED', 'FAILED');

-- CreateEnum
CREATE TYPE "AuditAction" AS ENUM ('INSTANCE_CREATE', 'INSTANCE_UPDATE', 'INSTANCE_DELETE', 'INSTANCE_START', 'INSTANCE_STOP', 'INSTANCE_RESTART', 'INSTANCE_UPGRADE', 'BACKUP_CREATE', 'BACKUP_DELETE', 'PANGOLIN_SETUP', 'PANGOLIN_SYNC', 'USER_LOGIN', 'USER_CREATE', 'USER_UPDATE', 'USER_DELETE', 'SETTINGS_UPDATE');

-- CreateTable
CREATE TABLE "ccp_users" (
    "id" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "role" "CcpRole" NOT NULL DEFAULT 'OPERATOR',
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "ccp_users_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "ccp_refresh_tokens" (
    "id" TEXT NOT NULL,
    "token" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "expires_at" TIMESTAMP(3) NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "ccp_refresh_tokens_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "instances" (
    "id" TEXT NOT NULL,
    "slug" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "domain" TEXT NOT NULL,
    "status" "InstanceStatus" NOT NULL DEFAULT 'PROVISIONING',
    "status_message" TEXT,
    "base_path" TEXT NOT NULL,
    "compose_project" TEXT NOT NULL,
    "git_branch" TEXT NOT NULL DEFAULT 'v2',
    "git_commit" TEXT,
    "port_config" JSONB NOT NULL,
    "encrypted_secrets" TEXT NOT NULL,
    "enable_media" BOOLEAN NOT NULL DEFAULT false,
    "enable_chat" BOOLEAN NOT NULL DEFAULT false,
    "enable_gancio" BOOLEAN NOT NULL DEFAULT false,
    "enable_listmonk" BOOLEAN NOT NULL DEFAULT false,
    "enable_monitoring" BOOLEAN NOT NULL DEFAULT false,
    "admin_email" TEXT NOT NULL,
    "pangolin_site_id" TEXT,
    "pangolin_newt_id" TEXT,
    "pangolin_newt_secret" TEXT,
    "smtp_host" TEXT,
    "smtp_port" INTEGER,
    "smtp_user" TEXT,
    "smtp_from" TEXT,
    "email_test_mode" BOOLEAN NOT NULL DEFAULT true,
    "notes" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,
    "last_health_check" TIMESTAMP(3),

    CONSTRAINT "instances_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "port_allocations" (
    "id" TEXT NOT NULL,
    "port" INTEGER NOT NULL,
    "instance_id" TEXT NOT NULL,
    "service" TEXT NOT NULL,
    "notes" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "port_allocations_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "health_checks" (
    "id" TEXT NOT NULL,
    "instance_id" TEXT NOT NULL,
    "status" "HealthStatus" NOT NULL,
    "service_status" JSONB NOT NULL,
    "total_services" INTEGER NOT NULL,
    "healthy_services" INTEGER NOT NULL,
    "response_time_ms" INTEGER,
    "checked_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "health_checks_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "backups" (
    "id" TEXT NOT NULL,
    "instance_id" TEXT NOT NULL,
    "status" "BackupStatus" NOT NULL DEFAULT 'PENDING',
    "archive_path" TEXT,
    "size_bytes" BIGINT,
    "manifest" JSONB,
    "started_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "completed_at" TIMESTAMP(3),
    "error_message" TEXT,
    "s3_uploaded" BOOLEAN NOT NULL DEFAULT false,
    "s3_key" TEXT,

    CONSTRAINT "backups_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "audit_logs" (
    "id" TEXT NOT NULL,
    "user_id" TEXT,
    "instance_id" TEXT,
    "action" "AuditAction" NOT NULL,
    "details" JSONB,
    "ip_address" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "ccp_settings" (
    "key" TEXT NOT NULL,
    "value" JSONB NOT NULL,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "ccp_settings_pkey" PRIMARY KEY ("key")
);

-- CreateIndex
CREATE UNIQUE INDEX "ccp_users_email_key" ON "ccp_users"("email");

-- CreateIndex
CREATE UNIQUE INDEX "ccp_refresh_tokens_token_key" ON "ccp_refresh_tokens"("token");

-- CreateIndex
CREATE INDEX "ccp_refresh_tokens_user_id_idx" ON "ccp_refresh_tokens"("user_id");

-- CreateIndex
CREATE INDEX "ccp_refresh_tokens_expires_at_idx" ON "ccp_refresh_tokens"("expires_at");

-- CreateIndex
CREATE UNIQUE INDEX "instances_slug_key" ON "instances"("slug");

-- CreateIndex
CREATE UNIQUE INDEX "instances_domain_key" ON "instances"("domain");

-- CreateIndex
CREATE UNIQUE INDEX "instances_compose_project_key" ON "instances"("compose_project");

-- CreateIndex
CREATE UNIQUE INDEX "port_allocations_port_key" ON "port_allocations"("port");

-- CreateIndex
CREATE INDEX "port_allocations_instance_id_idx" ON "port_allocations"("instance_id");

-- CreateIndex
CREATE INDEX "health_checks_instance_id_checked_at_idx" ON "health_checks"("instance_id", "checked_at");

-- CreateIndex
CREATE INDEX "backups_instance_id_started_at_idx" ON "backups"("instance_id", "started_at");

-- CreateIndex
CREATE INDEX "audit_logs_instance_id_created_at_idx" ON "audit_logs"("instance_id", "created_at");

-- CreateIndex
CREATE INDEX "audit_logs_user_id_created_at_idx" ON "audit_logs"("user_id", "created_at");

-- CreateIndex
CREATE INDEX "audit_logs_action_created_at_idx" ON "audit_logs"("action", "created_at");

-- AddForeignKey
ALTER TABLE "ccp_refresh_tokens" ADD CONSTRAINT "ccp_refresh_tokens_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "ccp_users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "port_allocations" ADD CONSTRAINT "port_allocations_instance_id_fkey" FOREIGN KEY ("instance_id") REFERENCES "instances"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "health_checks" ADD CONSTRAINT "health_checks_instance_id_fkey" FOREIGN KEY ("instance_id") REFERENCES "instances"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "backups" ADD CONSTRAINT "backups_instance_id_fkey" FOREIGN KEY ("instance_id") REFERENCES "instances"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "ccp_users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_instance_id_fkey" FOREIGN KEY ("instance_id") REFERENCES "instances"("id") ON DELETE SET NULL ON UPDATE CASCADE;