-- Add TurnstileWidget table and Dealer relationship
-- Uses IF NOT EXISTS patterns for idempotency across environments

-- Create TurnstileWidget table if it doesn't exist
CREATE TABLE IF NOT EXISTS "TurnstileWidget" (
    "id" TEXT NOT NULL,
    "cloudflareWidgetId" TEXT NOT NULL,
    "sitekey" TEXT NOT NULL,
    "secretKeyEncrypted" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "isPrimary" BOOLEAN NOT NULL DEFAULT false,
    "hostnameCount" INTEGER NOT NULL DEFAULT 0,
    "maxHostnames" INTEGER NOT NULL DEFAULT 15,
    "isActive" BOOLEAN NOT NULL DEFAULT true,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

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

-- Add unique constraints if they don't exist
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'TurnstileWidget_cloudflareWidgetId_key'
    ) THEN
        ALTER TABLE "TurnstileWidget" ADD CONSTRAINT "TurnstileWidget_cloudflareWidgetId_key" UNIQUE ("cloudflareWidgetId");
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'TurnstileWidget_sitekey_key'
    ) THEN
        ALTER TABLE "TurnstileWidget" ADD CONSTRAINT "TurnstileWidget_sitekey_key" UNIQUE ("sitekey");
    END IF;
END $$;

-- Add index if it doesn't exist
CREATE INDEX IF NOT EXISTS "TurnstileWidget_isActive_hostnameCount_idx" ON "TurnstileWidget"("isActive", "hostnameCount");

-- Add turnstileWidgetId column to Dealer if it doesn't exist
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'Dealer' AND column_name = 'turnstileWidgetId'
    ) THEN
        ALTER TABLE "Dealer" ADD COLUMN "turnstileWidgetId" TEXT;
    END IF;
END $$;

-- Add foreign key if it doesn't exist
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'Dealer_turnstileWidgetId_fkey'
    ) THEN
        ALTER TABLE "Dealer" ADD CONSTRAINT "Dealer_turnstileWidgetId_fkey"
        FOREIGN KEY ("turnstileWidgetId") REFERENCES "TurnstileWidget"("id") ON DELETE SET NULL ON UPDATE CASCADE;
    END IF;
END $$;