-- Add a keywords column (tags, use cases, category names, vendor) to the full-text vector at weight B.
ALTER TABLE tools ADD COLUMN IF NOT EXISTS keywords text NOT NULL DEFAULT '';
DROP VIEW IF EXISTS public_tools;
ALTER TABLE tools DROP COLUMN search_vector;
ALTER TABLE tools ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', f_unaccent(coalesce(name,''))), 'A') ||
    setweight(to_tsvector('english', f_unaccent(f_join(aliases))), 'A') ||
    setweight(to_tsvector('english', f_unaccent(coalesce(summary,''))), 'B') ||
    setweight(to_tsvector('english', f_unaccent(coalesce(keywords,''))), 'B') ||
    setweight(to_tsvector('english', f_unaccent(f_join(capabilities))), 'C') ||
    setweight(to_tsvector('english', f_unaccent(coalesce(description,''))), 'D')
  ) STORED;
CREATE INDEX tools_search_idx ON tools USING gin(search_vector);
CREATE VIEW public_tools AS
  SELECT id, slug, name, aliases, vendor_id, homepage_url, docs_url, repo_url, pricing_url, license_url, privacy_url, security_url,
         logo_url, logo_attribution, product_type, product_status, summary, description, capabilities, workflows, advantages, limitations,
         prerequisites, audiences, comparison_notes, operating_systems, execution_modes, interfaces, deployment_models, integrations,
         data_formats, languages, source_model, license_spdx, pricing_model, pricing_checked_at, privacy_posture, data_residency,
         self_hostable, confidence, uncertainty_note, depth, quality_score, link_state, featured, reviewed_at, checked_at, published_at,
         updated_at, created_at, search_vector, embedding
  FROM tools WHERE visibility = 'public' AND editorial_status = 'published' AND duplicate_of IS NULL;
DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='tools_reader') THEN EXECUTE 'GRANT SELECT ON public_tools TO tools_reader'; END IF;
          IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='tools_app') THEN EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON public_tools TO tools_app'; END IF; END $$;