CREATE TABLE "plugin_releases" (
    "id" UUID NOT NULL,
    "product" VARCHAR(50) NOT NULL,
    "plugin" VARCHAR(20) NOT NULL,
    "version" VARCHAR(20) NOT NULL,
    "r2_object_key" TEXT NOT NULL,
    "package_sha256" VARCHAR(64) NOT NULL,
    "package_signature" TEXT NOT NULL,
    "signature_key_id" VARCHAR(64) NOT NULL,
    "requires_wp" VARCHAR(20) NOT NULL,
    "tested_wp" VARCHAR(20) NOT NULL,
    "requires_php" VARCHAR(20) NOT NULL,
    "homepage_url" TEXT NOT NULL DEFAULT '',
    "description" TEXT NOT NULL DEFAULT '',
    "changelog" TEXT NOT NULL DEFAULT '',
    "is_stable" BOOLEAN NOT NULL DEFAULT true,
    "published_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

CREATE UNIQUE INDEX "plugin_releases_package_sha256_key" ON "plugin_releases"("package_sha256");
CREATE UNIQUE INDEX "plugin_releases_product_version_key" ON "plugin_releases"("product", "version");
CREATE INDEX "plugin_releases_plugin_is_stable_published_at_idx" ON "plugin_releases"("plugin", "is_stable", "published_at");

CREATE TABLE "plugin_release_entitlements" (
    "id" UUID NOT NULL,
    "release_id" UUID NOT NULL,
    "plan_tier" VARCHAR(50) NOT NULL,

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

CREATE UNIQUE INDEX "plugin_release_entitlements_release_id_plan_tier_key" ON "plugin_release_entitlements"("release_id", "plan_tier");
CREATE INDEX "plugin_release_entitlements_plan_tier_idx" ON "plugin_release_entitlements"("plan_tier");

ALTER TABLE "plugin_release_entitlements"
ADD CONSTRAINT "plugin_release_entitlements_release_id_fkey"
FOREIGN KEY ("release_id") REFERENCES "plugin_releases"("id")
ON DELETE CASCADE ON UPDATE CASCADE;
