-- Growth Pack 2026-05-13: reviews v2 (reply/photo/votes), services,
-- bookings, profile Q&A, phone verification, multi-language translations,
-- web push subscriptions.

-- Reviews — dodaj counter polja
ALTER TABLE "Review"
  ADD COLUMN "helpfulCount" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN "notHelpfulCount" INTEGER NOT NULL DEFAULT 0;

-- Review Reply
CREATE TABLE "ReviewReply" (
  "id"         TEXT NOT NULL,
  "reviewId"   TEXT NOT NULL,
  "authorId"   TEXT,
  "authorName" TEXT NOT NULL,
  "body"       TEXT NOT NULL,
  "createdAt"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt"  TIMESTAMP(3) NOT NULL,
  CONSTRAINT "ReviewReply_pkey" PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "ReviewReply_reviewId_key" ON "ReviewReply"("reviewId");
ALTER TABLE "ReviewReply" ADD CONSTRAINT "ReviewReply_reviewId_fkey"
  FOREIGN KEY ("reviewId") REFERENCES "Review"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- Review Photos
CREATE TABLE "ReviewPhoto" (
  "id"        TEXT NOT NULL,
  "reviewId"  TEXT NOT NULL,
  "mediaId"   TEXT NOT NULL,
  "sortOrder" INTEGER NOT NULL DEFAULT 0,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "ReviewPhoto_pkey" PRIMARY KEY ("id")
);
CREATE INDEX "ReviewPhoto_reviewId_sortOrder_idx" ON "ReviewPhoto"("reviewId", "sortOrder");
ALTER TABLE "ReviewPhoto" ADD CONSTRAINT "ReviewPhoto_reviewId_fkey"
  FOREIGN KEY ("reviewId") REFERENCES "Review"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "ReviewPhoto" ADD CONSTRAINT "ReviewPhoto_mediaId_fkey"
  FOREIGN KEY ("mediaId") REFERENCES "Media"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- Review Vote
CREATE TABLE "ReviewVote" (
  "id"         TEXT NOT NULL,
  "reviewId"   TEXT NOT NULL,
  "visitorKey" TEXT NOT NULL,
  "helpful"    BOOLEAN NOT NULL,
  "createdAt"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "ReviewVote_pkey" PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "ReviewVote_reviewId_visitorKey_key" ON "ReviewVote"("reviewId", "visitorKey");
CREATE INDEX "ReviewVote_reviewId_idx" ON "ReviewVote"("reviewId");
ALTER TABLE "ReviewVote" ADD CONSTRAINT "ReviewVote_reviewId_fkey"
  FOREIGN KEY ("reviewId") REFERENCES "Review"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- Company Services (Cenovnik)
CREATE TABLE "CompanyService" (
  "id"          TEXT NOT NULL,
  "companyId"   TEXT NOT NULL,
  "name"        TEXT NOT NULL,
  "description" TEXT,
  "priceRsd"    INTEGER,
  "priceMode"   TEXT NOT NULL DEFAULT 'fixed',
  "priceMaxRsd" INTEGER,
  "durationMin" INTEGER,
  "category"    TEXT,
  "sortOrder"   INTEGER NOT NULL DEFAULT 0,
  "isActive"    BOOLEAN NOT NULL DEFAULT true,
  "createdAt"   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt"   TIMESTAMP(3) NOT NULL,
  CONSTRAINT "CompanyService_pkey" PRIMARY KEY ("id")
);
CREATE INDEX "CompanyService_companyId_sortOrder_idx" ON "CompanyService"("companyId", "sortOrder");
CREATE INDEX "CompanyService_companyId_isActive_idx" ON "CompanyService"("companyId", "isActive");
ALTER TABLE "CompanyService" ADD CONSTRAINT "CompanyService_companyId_fkey"
  FOREIGN KEY ("companyId") REFERENCES "Company"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- Booking Slot
CREATE TABLE "BookingSlot" (
  "id"        TEXT NOT NULL,
  "companyId" TEXT NOT NULL,
  "startAt"   TIMESTAMP(3) NOT NULL,
  "endAt"     TIMESTAMP(3) NOT NULL,
  "capacity"  INTEGER NOT NULL DEFAULT 1,
  "booked"    INTEGER NOT NULL DEFAULT 0,
  "isActive"  BOOLEAN NOT NULL DEFAULT true,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "BookingSlot_pkey" PRIMARY KEY ("id")
);
CREATE INDEX "BookingSlot_companyId_startAt_idx" ON "BookingSlot"("companyId", "startAt");
CREATE INDEX "BookingSlot_companyId_isActive_startAt_idx" ON "BookingSlot"("companyId", "isActive", "startAt");
ALTER TABLE "BookingSlot" ADD CONSTRAINT "BookingSlot_companyId_fkey"
  FOREIGN KEY ("companyId") REFERENCES "Company"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- Booking
CREATE TABLE "Booking" (
  "id"                 TEXT NOT NULL,
  "slotId"             TEXT NOT NULL,
  "companyId"          TEXT NOT NULL,
  "customerName"       TEXT NOT NULL,
  "customerEmail"      TEXT NOT NULL,
  "customerPhone"      TEXT NOT NULL,
  "status"             TEXT NOT NULL DEFAULT 'PENDING',
  "notes"              TEXT,
  "serviceId"          TEXT,
  "cancellationReason" TEXT,
  "confirmedAt"        TIMESTAMP(3),
  "cancelledAt"        TIMESTAMP(3),
  "createdAt"          TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt"          TIMESTAMP(3) NOT NULL,
  CONSTRAINT "Booking_pkey" PRIMARY KEY ("id")
);
CREATE INDEX "Booking_companyId_status_idx" ON "Booking"("companyId", "status");
CREATE INDEX "Booking_customerEmail_idx" ON "Booking"("customerEmail");
CREATE INDEX "Booking_slotId_idx" ON "Booking"("slotId");
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_slotId_fkey"
  FOREIGN KEY ("slotId") REFERENCES "BookingSlot"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_companyId_fkey"
  FOREIGN KEY ("companyId") REFERENCES "Company"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_serviceId_fkey"
  FOREIGN KEY ("serviceId") REFERENCES "CompanyService"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- ProfileQA (Per-company Q&A)
CREATE TABLE "ProfileQA" (
  "id"           TEXT NOT NULL,
  "companyId"    TEXT NOT NULL,
  "question"     TEXT NOT NULL,
  "answer"       TEXT,
  "askerName"    TEXT NOT NULL,
  "askerEmail"   TEXT,
  "answeredAt"   TIMESTAMP(3),
  "isApproved"   BOOLEAN NOT NULL DEFAULT false,
  "spamScore"    INTEGER NOT NULL DEFAULT 0,
  "helpfulCount" INTEGER NOT NULL DEFAULT 0,
  "createdAt"    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt"    TIMESTAMP(3) NOT NULL,
  CONSTRAINT "ProfileQA_pkey" PRIMARY KEY ("id")
);
CREATE INDEX "ProfileQA_companyId_isApproved_createdAt_idx" ON "ProfileQA"("companyId", "isApproved", "createdAt");
CREATE INDEX "ProfileQA_companyId_answeredAt_idx" ON "ProfileQA"("companyId", "answeredAt");
ALTER TABLE "ProfileQA" ADD CONSTRAINT "ProfileQA_companyId_fkey"
  FOREIGN KEY ("companyId") REFERENCES "Company"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- Phone Verification
CREATE TABLE "PhoneVerification" (
  "id"         TEXT NOT NULL,
  "phone"      TEXT NOT NULL,
  "code"       TEXT NOT NULL,
  "purpose"    TEXT NOT NULL,
  "refId"      TEXT,
  "attempts"   INTEGER NOT NULL DEFAULT 0,
  "verifiedAt" TIMESTAMP(3),
  "expiresAt"  TIMESTAMP(3) NOT NULL,
  "ip"         TEXT,
  "createdAt"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "PhoneVerification_pkey" PRIMARY KEY ("id")
);
CREATE INDEX "PhoneVerification_phone_purpose_idx" ON "PhoneVerification"("phone", "purpose");
CREATE INDEX "PhoneVerification_expiresAt_idx" ON "PhoneVerification"("expiresAt");

-- Company Translation
CREATE TABLE "CompanyTranslation" (
  "id"          TEXT NOT NULL,
  "companyId"   TEXT NOT NULL,
  "locale"      TEXT NOT NULL,
  "description" TEXT,
  "slogan"      TEXT,
  "source"      TEXT NOT NULL DEFAULT 'manual',
  "generatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt"   TIMESTAMP(3) NOT NULL,
  CONSTRAINT "CompanyTranslation_pkey" PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "CompanyTranslation_companyId_locale_key" ON "CompanyTranslation"("companyId", "locale");
CREATE INDEX "CompanyTranslation_companyId_idx" ON "CompanyTranslation"("companyId");
ALTER TABLE "CompanyTranslation" ADD CONSTRAINT "CompanyTranslation_companyId_fkey"
  FOREIGN KEY ("companyId") REFERENCES "Company"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- Push Subscription
CREATE TABLE "PushSubscription" (
  "id"         TEXT NOT NULL,
  "endpoint"   TEXT NOT NULL,
  "p256dh"     TEXT NOT NULL,
  "auth"       TEXT NOT NULL,
  "visitorKey" TEXT,
  "userId"     TEXT,
  "topics"     TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  "isActive"   BOOLEAN NOT NULL DEFAULT true,
  "lastSeenAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "createdAt"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "PushSubscription_pkey" PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "PushSubscription_endpoint_key" ON "PushSubscription"("endpoint");
CREATE INDEX "PushSubscription_visitorKey_idx" ON "PushSubscription"("visitorKey");
CREATE INDEX "PushSubscription_isActive_lastSeenAt_idx" ON "PushSubscription"("isActive", "lastSeenAt");
ALTER TABLE "PushSubscription" ADD CONSTRAINT "PushSubscription_userId_fkey"
  FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;