-- ============================================================================
-- Search excellence: Postgres full-text (tsvector) za "Company".
--
-- Pokreni (Prisma 7 — datasource se čita iz prisma.config.ts):
--   npx prisma db execute --file scripts/sql/search-vectors.sql
--
-- Idempotentno (IF NOT EXISTS / CREATE OR REPLACE). Težine:
--   name            -> A
--   kategorije/grad -> B
--   slogan          -> B
--   opis/adresa     -> C
--
-- "unaccent" se koristi da latinična pretraga radi i bez dijakritike
-- (npr. "cevap" pronalazi "ćevap"). Konfiguracija je "simple" jer srpski
-- nema ugrađen FTS dictionary u Postgres-u — "simple" + unaccent daje
-- predvidiv, stabilan rezultat.
-- ============================================================================

CREATE EXTENSION IF NOT EXISTS unaccent;

-- 1) Kolona (Prisma je već možda dodao kroz db push; IF NOT EXISTS je safety net)
ALTER TABLE "Company" ADD COLUMN IF NOT EXISTS "searchVector" tsvector;

-- 2) Immutable wrapper oko unaccent (potreban za index/generated izraze)
CREATE OR REPLACE FUNCTION bp_unaccent_immutable(text)
  RETURNS text
  LANGUAGE sql
  IMMUTABLE
  PARALLEL SAFE
AS $$
  SELECT unaccent('unaccent', $1)
$$;

-- 3) Funkcija koja sklapa weighted tsvector za jednu kompaniju
CREATE OR REPLACE FUNCTION bp_company_search_vector("companyRow" "Company")
  RETURNS tsvector
  LANGUAGE plpgsql
  STABLE
AS $$
DECLARE
  category_text text;
  city_text     text;
BEGIN
  SELECT string_agg(coalesce(c."nameSr", '') || ' ' || coalesce(c."nameEn", '') || ' ' || coalesce(c."slug", ''), ' ')
    INTO category_text
    FROM "CompanyCategory" cc
    JOIN "Category" c ON c."id" = cc."categoryId"
   WHERE cc."companyId" = "companyRow"."id";

  SELECT string_agg(coalesce(r."nameSr", ''), ' ')
    INTO city_text
    FROM "RegionNode" r
   WHERE r."id" IN (
     "companyRow"."cityId",
     "companyRow"."municipalityId",
     "companyRow"."neighborhoodId"
   );

  RETURN
    setweight(to_tsvector('simple', bp_unaccent_immutable(coalesce("companyRow"."name", ''))), 'A') ||
    setweight(to_tsvector('simple', bp_unaccent_immutable(coalesce(category_text, ''))), 'B') ||
    setweight(to_tsvector('simple', bp_unaccent_immutable(coalesce(city_text, ''))), 'B') ||
    setweight(to_tsvector('simple', bp_unaccent_immutable(coalesce("companyRow"."slogan", ''))), 'B') ||
    setweight(to_tsvector('simple', bp_unaccent_immutable(coalesce("companyRow"."description", ''))), 'C') ||
    setweight(to_tsvector('simple', bp_unaccent_immutable(coalesce("companyRow"."address", ''))), 'C');
END;
$$;

-- 4) Trigger funkcija — popunjava searchVector pri INSERT/UPDATE
CREATE OR REPLACE FUNCTION bp_company_search_vector_trigger()
  RETURNS trigger
  LANGUAGE plpgsql
AS $$
BEGIN
  NEW."searchVector" := bp_company_search_vector(NEW);
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_company_search_vector ON "Company";
CREATE TRIGGER trg_company_search_vector
  BEFORE INSERT OR UPDATE OF "name", "slogan", "description", "address",
    "cityId", "municipalityId", "neighborhoodId"
  ON "Company"
  FOR EACH ROW
  EXECUTE FUNCTION bp_company_search_vector_trigger();

-- 5) GIN index (Prisma @@index([searchVector], type: Gin) ovo takođe pravi;
--    IF NOT EXISTS je safety net za db execute pre db push-a)
CREATE INDEX IF NOT EXISTS "Company_searchVector_idx"
  ON "Company" USING GIN ("searchVector");

-- 6) Backfill svih postojećih redova
UPDATE "Company" c
   SET "searchVector" = bp_company_search_vector(c.*)
 WHERE "searchVector" IS NULL;