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

-- CreateEnum
CREATE TYPE "platform_devtracker"."DevItemStatus" AS ENUM ('DRAFT', 'REVIEWED', 'IN_DEVELOPMENT', 'IN_TESTING', 'USER_TESTING', 'DONE', 'CANCELLED', 'ARCHIVED');

-- CreateEnum
CREATE TYPE "platform_devtracker"."DevItemType" AS ENUM ('MODULE', 'FEATURE', 'BUG');

-- CreateEnum
CREATE TYPE "platform_devtracker"."DevItemPriority" AS ENUM ('P0', 'P1', 'P2', 'P3');

-- CreateEnum
CREATE TYPE "platform_devtracker"."DevItemSeverity" AS ENUM ('S1', 'S2', 'S3', 'S4');

-- CreateTable
CREATE TABLE "platform_devtracker"."dev_items" (
    "id" UUID NOT NULL,
    "code" VARCHAR(50) NOT NULL,
    "title" VARCHAR(200) NOT NULL,
    "description" TEXT,
    "item_type" "platform_devtracker"."DevItemType" NOT NULL,
    "status" "platform_devtracker"."DevItemStatus" NOT NULL DEFAULT 'DRAFT',
    "priority" "platform_devtracker"."DevItemPriority" NOT NULL,
    "severity" "platform_devtracker"."DevItemSeverity",
    "module_key" VARCHAR(100),
    "parent_id" UUID,
    "owner_id" UUID NOT NULL,
    "reporter_id" UUID NOT NULL,
    "reviewer_id" UUID,
    "reviewed_at" TIMESTAMPTZ(3),
    "start_at" TIMESTAMPTZ(3),
    "dev_eta_at" TIMESTAMPTZ(3),
    "test_eta_at" TIMESTAMPTZ(3),
    "eta_at" TIMESTAMPTZ(3),
    "dev_completed_at" TIMESTAMPTZ(3),
    "test_completed_at" TIMESTAMPTZ(3),
    "completed_at" TIMESTAMPTZ(3),
    "content" JSONB,
    "labels" JSONB,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,
    "deleted_at" TIMESTAMPTZ(3),

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

-- CreateTable
CREATE TABLE "platform_devtracker"."dev_item_sequences" (
    "prefix" VARCHAR(50) NOT NULL,
    "current" INTEGER NOT NULL DEFAULT 0,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

    CONSTRAINT "dev_item_sequences_pkey" PRIMARY KEY ("prefix")
);

-- CreateTable
CREATE TABLE "platform_devtracker"."release_notes" (
    "id" UUID NOT NULL,
    "version" VARCHAR(50) NOT NULL,
    "title" VARCHAR(200) NOT NULL,
    "content" JSONB,
    "released_at" TIMESTAMPTZ(3) NOT NULL,
    "created_by" UUID NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateIndex
CREATE UNIQUE INDEX "dev_items_code_key" ON "platform_devtracker"."dev_items"("code");

-- CreateIndex
CREATE INDEX "dev_items_status_idx" ON "platform_devtracker"."dev_items"("status");

-- CreateIndex
CREATE INDEX "dev_items_item_type_idx" ON "platform_devtracker"."dev_items"("item_type");

-- CreateIndex
CREATE INDEX "dev_items_priority_idx" ON "platform_devtracker"."dev_items"("priority");

-- CreateIndex
CREATE INDEX "dev_items_owner_id_idx" ON "platform_devtracker"."dev_items"("owner_id");

-- CreateIndex
CREATE INDEX "dev_items_reporter_id_idx" ON "platform_devtracker"."dev_items"("reporter_id");

-- CreateIndex
CREATE INDEX "dev_items_reviewer_id_idx" ON "platform_devtracker"."dev_items"("reviewer_id");

-- CreateIndex
CREATE INDEX "dev_items_parent_id_idx" ON "platform_devtracker"."dev_items"("parent_id");

-- CreateIndex
CREATE INDEX "dev_items_module_key_idx" ON "platform_devtracker"."dev_items"("module_key");

-- CreateIndex
CREATE INDEX "dev_items_eta_at_idx" ON "platform_devtracker"."dev_items"("eta_at");

-- CreateIndex
CREATE INDEX "dev_items_created_at_idx" ON "platform_devtracker"."dev_items"("created_at");

-- CreateIndex
CREATE INDEX "release_notes_released_at_idx" ON "platform_devtracker"."release_notes"("released_at");

-- CreateIndex
CREATE INDEX "release_notes_created_at_idx" ON "platform_devtracker"."release_notes"("created_at");

-- CreateIndex
CREATE UNIQUE INDEX "release_notes_version_key" ON "platform_devtracker"."release_notes"("version");

-- AddForeignKey
ALTER TABLE "platform_devtracker"."dev_items" ADD CONSTRAINT "dev_items_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "platform_devtracker"."dev_items"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_devtracker"."dev_items" ADD CONSTRAINT "dev_items_owner_id_fkey" FOREIGN KEY ("owner_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_devtracker"."dev_items" ADD CONSTRAINT "dev_items_reporter_id_fkey" FOREIGN KEY ("reporter_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_devtracker"."dev_items" ADD CONSTRAINT "dev_items_reviewer_id_fkey" FOREIGN KEY ("reviewer_id") REFERENCES "platform_iam"."users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_devtracker"."release_notes" ADD CONSTRAINT "release_notes_created_by_fkey" FOREIGN KEY ("created_by") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
