-- CreateTable
CREATE TABLE "cf_app_installations" (
    "id" TEXT NOT NULL,
    "installation_id" INTEGER NOT NULL,
    "account_id" INTEGER NOT NULL,
    "account_login" TEXT NOT NULL,
    "account_type" TEXT NOT NULL,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,
    "is_active" BOOLEAN NOT NULL DEFAULT true,

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

-- CreateTable
CREATE TABLE "repositories" (
    "id" TEXT NOT NULL,
    "github_repo_id" TEXT NOT NULL,
    "installation_id" INTEGER NOT NULL,
    "name" TEXT NOT NULL,
    "full_name" TEXT NOT NULL,
    "is_private" BOOLEAN NOT NULL,
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "has_github_action" BOOLEAN NOT NULL DEFAULT false,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "last_optimized" TIMESTAMP(3),
    "optimizations_limit" INTEGER,
    "optimizations_used" INTEGER NOT NULL DEFAULT 0,

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

-- CreateTable
CREATE TABLE "repository_members" (
    "id" TEXT NOT NULL,
    "repository_id" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "role" TEXT NOT NULL,
    "added_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "added_by" TEXT,

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

-- CreateTable
CREATE TABLE "optimization_events" (
    "id" TEXT NOT NULL,
    "event_type" TEXT NOT NULL,
    "user_id" TEXT,
    "repository_id" TEXT,
    "trace_id" TEXT NOT NULL,
    "pr_id" TEXT,
    "api_key_id" INTEGER,
    "metadata" JSONB,
    "is_optimization_found" BOOLEAN,
    "current_username" TEXT,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateIndex
CREATE UNIQUE INDEX "cf_app_installations_installation_id_key" ON "cf_app_installations"("installation_id");

-- CreateIndex
CREATE UNIQUE INDEX "repositories_github_repo_id_key" ON "repositories"("github_repo_id");

-- CreateIndex
CREATE UNIQUE INDEX "repositories_full_name_key" ON "repositories"("full_name");

-- CreateIndex
CREATE INDEX "repository_members_user_id_idx" ON "repository_members"("user_id");

-- CreateIndex
CREATE UNIQUE INDEX "repository_members_repository_id_user_id_key" ON "repository_members"("repository_id", "user_id");

-- CreateIndex
CREATE UNIQUE INDEX "optimization_events_trace_id_key" ON "optimization_events"("trace_id");

-- CreateIndex
CREATE UNIQUE INDEX "optimization_events_pr_id_key" ON "optimization_events"("pr_id");

-- CreateIndex
CREATE INDEX "optimization_events_event_type_created_at_idx" ON "optimization_events"("event_type", "created_at");

-- CreateIndex
CREATE INDEX "optimization_events_repository_id_event_type_idx" ON "optimization_events"("repository_id", "event_type");

-- CreateIndex
CREATE INDEX "optimization_events_user_id_event_type_idx" ON "optimization_events"("user_id", "event_type");

-- CreateIndex
CREATE INDEX "optimization_events_trace_id_idx" ON "optimization_events"("trace_id");

-- AddForeignKey
ALTER TABLE "repositories" ADD CONSTRAINT "repositories_installation_id_fkey" FOREIGN KEY ("installation_id") REFERENCES "cf_app_installations"("installation_id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "repository_members" ADD CONSTRAINT "repository_members_repository_id_fkey" FOREIGN KEY ("repository_id") REFERENCES "repositories"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "repository_members" ADD CONSTRAINT "repository_members_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("user_id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "optimization_events" ADD CONSTRAINT "optimization_events_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("user_id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "optimization_events" ADD CONSTRAINT "optimization_events_repository_id_fkey" FOREIGN KEY ("repository_id") REFERENCES "repositories"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "optimization_events" ADD CONSTRAINT "optimization_events_api_key_id_fkey" FOREIGN KEY ("api_key_id") REFERENCES "cf_api_keys"("id") ON DELETE SET NULL ON UPDATE CASCADE;
