-- tools.spboucher.ai — initial schema. Applied by scripts/migrate.ts (tracks drizzle/*.sql in schema_migrations).
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
DO $$ BEGIN CREATE EXTENSION IF NOT EXISTS vector; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'pgvector unavailable: %', SQLERRM; END $$;

-- immutable unaccent wrapper usable in generated columns / indexes
CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS $$ SELECT public.unaccent('public.unaccent', $1) $$;
-- array_to_string is only STABLE; wrap it for use in generated columns
CREATE OR REPLACE FUNCTION f_join(text[]) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS $$ SELECT array_to_string($1, ' ') $$;

-- ---------- identity ----------
CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  display_name text NOT NULL,
  password_hash text NOT NULL,
  role text NOT NULL DEFAULT 'owner' CHECK (role IN ('owner','editor')),
  totp_secret text,
  totp_enabled boolean NOT NULL DEFAULT false,
  must_change_password boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash text NOT NULL UNIQUE,
  csrf_secret text NOT NULL,
  mfa_passed boolean NOT NULL DEFAULT false,
  user_agent text, ip text,
  created_at timestamptz NOT NULL DEFAULT now(),
  last_seen_at timestamptz NOT NULL DEFAULT now(),
  rotated_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  absolute_expires_at timestamptz NOT NULL,
  revoked_at timestamptz
);
CREATE INDEX sessions_user_idx ON sessions(user_id);
CREATE TABLE login_attempts (
  id bigserial PRIMARY KEY, ip text NOT NULL, email text, success boolean NOT NULL, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX login_attempts_ip_idx ON login_attempts(ip, created_at);
CREATE INDEX login_attempts_email_idx ON login_attempts(email, created_at);
CREATE TABLE webauthn_credentials (
  id text PRIMARY KEY, user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  public_key text NOT NULL, counter bigint NOT NULL DEFAULT 0, transports text[] NOT NULL DEFAULT '{}',
  device_type text, backed_up boolean NOT NULL DEFAULT false, label text NOT NULL DEFAULT 'Passkey',
  created_at timestamptz NOT NULL DEFAULT now(), last_used_at timestamptz
);
CREATE TABLE webauthn_challenges (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id uuid REFERENCES users(id) ON DELETE CASCADE,
  challenge text NOT NULL, kind text NOT NULL CHECK (kind IN ('register','auth')), created_at timestamptz NOT NULL DEFAULT now()
);

-- ---------- taxonomy & reference ----------
CREATE TABLE vendors (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text NOT NULL UNIQUE, name text NOT NULL, website_url text,
  description text, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE categories (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text NOT NULL UNIQUE, name text NOT NULL, description text NOT NULL DEFAULT '',
  parent_id uuid REFERENCES categories(id) ON DELETE RESTRICT, sort_order int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX categories_parent_idx ON categories(parent_id);
CREATE TABLE tags (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text NOT NULL, name text NOT NULL,
  kind text NOT NULL DEFAULT 'tag' CHECK (kind IN ('tag','use_case')), UNIQUE (slug, kind)
);

-- ---------- tools ----------
CREATE TABLE tools (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug text NOT NULL UNIQUE,
  name text NOT NULL,
  aliases text[] NOT NULL DEFAULT '{}',
  vendor_id uuid REFERENCES vendors(id) ON DELETE SET NULL,
  homepage_url text NOT NULL,
  docs_url text, repo_url text, pricing_url text, license_url text, privacy_url text, security_url text,
  logo_url text, logo_attribution text,
  product_type text NOT NULL,
  product_status text NOT NULL DEFAULT 'active' CHECK (product_status IN ('active','maintenance','deprecated','discontinued','renamed','unknown')),
  summary text NOT NULL,
  description text NOT NULL,
  capabilities text[] NOT NULL DEFAULT '{}', workflows text[] NOT NULL DEFAULT '{}', advantages text[] NOT NULL DEFAULT '{}',
  limitations text[] NOT NULL DEFAULT '{}', prerequisites text[] NOT NULL DEFAULT '{}', audiences text[] NOT NULL DEFAULT '{}',
  comparison_notes text,
  operating_systems text[] NOT NULL DEFAULT '{}', execution_modes text[] NOT NULL DEFAULT '{}', interfaces text[] NOT NULL DEFAULT '{}',
  deployment_models text[] NOT NULL DEFAULT '{}', integrations text[] NOT NULL DEFAULT '{}', data_formats text[] NOT NULL DEFAULT '{}', languages text[] NOT NULL DEFAULT '{}',
  source_model text NOT NULL CHECK (source_model IN ('open-source','proprietary','source-available','mixed')),
  license_spdx text,
  pricing_model text[] NOT NULL DEFAULT '{}',
  pricing_checked_at date,
  privacy_posture text, data_residency text,
  self_hostable boolean,
  -- provenance / editorial
  confidence text NOT NULL DEFAULT 'medium' CHECK (confidence IN ('high','medium','low')),
  uncertainty_note text,
  discovery_method text NOT NULL DEFAULT 'editorial-research',
  depth text NOT NULL DEFAULT 'standard' CHECK (depth IN ('deep','standard')),
  editorial_status text NOT NULL DEFAULT 'draft' CHECK (editorial_status IN ('draft','reviewed','published','deprecated')),
  visibility text NOT NULL DEFAULT 'public' CHECK (visibility IN ('public','private')),
  quality_score int NOT NULL DEFAULT 0 CHECK (quality_score BETWEEN 0 AND 100),
  duplicate_of uuid REFERENCES tools(id) ON DELETE SET NULL,
  link_state text NOT NULL DEFAULT 'unchecked' CHECK (link_state IN ('unchecked','ok','redirected','broken')),
  featured boolean NOT NULL DEFAULT false,
  author text NOT NULL DEFAULT 'editorial',
  reviewed_at date, reviewed_by text, checked_at timestamptz, published_at timestamptz,
  content_hash text,
  embedding_text text,
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(),
  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(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 INDEX tools_name_trgm_idx ON tools USING gin (f_unaccent(lower(name)) gin_trgm_ops);
CREATE INDEX tools_public_idx ON tools(visibility, editorial_status) WHERE visibility='public' AND editorial_status='published';
CREATE INDEX tools_vendor_idx ON tools(vendor_id);
CREATE INDEX tools_status_idx ON tools(editorial_status);
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_extension WHERE extname='vector') THEN
    EXECUTE 'ALTER TABLE tools ADD COLUMN embedding vector(1536)';
    EXECUTE 'ALTER TABLE tools ADD COLUMN embedding_model text';
    EXECUTE 'CREATE INDEX tools_embedding_idx ON tools USING hnsw (embedding vector_cosine_ops)';
  ELSE
    EXECUTE 'ALTER TABLE tools ADD COLUMN embedding jsonb';
    EXECUTE 'ALTER TABLE tools ADD COLUMN embedding_model text';
  END IF;
END $$;

CREATE TABLE tool_categories (
  tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE, category_id uuid NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
  is_primary boolean NOT NULL DEFAULT false, label text, PRIMARY KEY (tool_id, category_id)
);
CREATE INDEX tool_categories_cat_idx ON tool_categories(category_id);
CREATE UNIQUE INDEX tool_categories_one_primary ON tool_categories(tool_id) WHERE is_primary;
CREATE TABLE tool_tags (
  tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE, tag_id uuid NOT NULL REFERENCES tags(id) ON DELETE CASCADE, PRIMARY KEY (tool_id, tag_id)
);
CREATE INDEX tool_tags_tag_idx ON tool_tags(tag_id);
CREATE TABLE tool_alternatives (
  tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE, alternative_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE,
  note text, PRIMARY KEY (tool_id, alternative_id), CHECK (tool_id <> alternative_id)
);
CREATE TABLE sources (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), url text NOT NULL UNIQUE, canonical_url text, title text,
  source_type text NOT NULL, http_status int, final_url text, content_hash text, fetched_at timestamptz, fetch_error text,
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE tool_sources (
  tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE, source_id uuid NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
  supports text[] NOT NULL DEFAULT '{}', checked_at date NOT NULL, note text, PRIMARY KEY (tool_id, source_id)
);
CREATE TABLE collections (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text NOT NULL UNIQUE, title text NOT NULL, description text NOT NULL,
  published boolean NOT NULL DEFAULT false, sort_order int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE collection_items (
  collection_id uuid NOT NULL REFERENCES collections(id) ON DELETE CASCADE, tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE,
  position int NOT NULL DEFAULT 0, blurb text, PRIMARY KEY (collection_id, tool_id)
);

-- ---------- owner-private ----------
CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text NOT NULL UNIQUE, name text NOT NULL, description text,
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE owner_notes (
  tool_id uuid PRIMARY KEY REFERENCES tools(id) ON DELETE CASCADE,
  use_state text NOT NULL DEFAULT 'candidate' CHECK (use_state IN ('active','testing','candidate','archived','rejected')),
  rating smallint CHECK (rating BETWEEN 1 AND 5), notes text, cluster_compat text NOT NULL DEFAULT 'unknown' CHECK (cluster_compat IN ('yes','partial','no','unknown')),
  priority text NOT NULL DEFAULT 'medium' CHECK (priority IN ('high','medium','low')), subscription_status text,
  deployment_urls jsonb NOT NULL DEFAULT '[]', custom_tags text[] NOT NULL DEFAULT '{}', review_at date,
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE project_tools (
  project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE, tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE,
  role text, PRIMARY KEY (project_id, tool_id)
);

-- ---------- editorial history, audit, jobs ----------
CREATE TABLE tool_revisions (
  id bigserial PRIMARY KEY, tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE, actor text NOT NULL, reason text,
  snapshot jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX tool_revisions_tool_idx ON tool_revisions(tool_id, created_at DESC);
CREATE TABLE audit_log (
  id bigserial PRIMARY KEY, actor_id uuid, actor text NOT NULL, action text NOT NULL, entity_type text, entity_id text,
  details jsonb NOT NULL DEFAULT '{}', ip text, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX audit_log_created_idx ON audit_log(created_at DESC);
CREATE OR REPLACE FUNCTION audit_log_immutable() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'audit_log is append-only'; END $$;
CREATE TRIGGER audit_log_no_update BEFORE UPDATE OR DELETE ON audit_log FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();
CREATE TRIGGER tool_revisions_no_update BEFORE UPDATE OR DELETE ON tool_revisions FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();

CREATE TABLE jobs (
  id bigserial PRIMARY KEY, kind text NOT NULL, payload jsonb NOT NULL DEFAULT '{}', dedupe_key text UNIQUE,
  status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','running','succeeded','failed','dead','cancelled')),
  priority int NOT NULL DEFAULT 5, attempts int NOT NULL DEFAULT 0, max_attempts int NOT NULL DEFAULT 5,
  run_at timestamptz NOT NULL DEFAULT now(), locked_at timestamptz, locked_by text, started_at timestamptz, finished_at timestamptz,
  last_error text, result jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX jobs_ready_idx ON jobs(status, run_at, priority) WHERE status='queued';
CREATE TABLE link_checks (
  id bigserial PRIMARY KEY, tool_id uuid REFERENCES tools(id) ON DELETE CASCADE, field text NOT NULL, url text NOT NULL,
  ok boolean NOT NULL, status_code int, final_url text, redirected boolean NOT NULL DEFAULT false, error text, duration_ms int,
  checked_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX link_checks_tool_idx ON link_checks(tool_id, checked_at DESC);
CREATE TABLE error_reports (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), tool_id uuid REFERENCES tools(id) ON DELETE SET NULL, tool_slug text, message text NOT NULL,
  contact text, ip_hash text, status text NOT NULL DEFAULT 'open' CHECK (status IN ('open','resolved','dismissed')), created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE search_events (
  id bigserial PRIMARY KEY, query text NOT NULL, result_count int NOT NULL, mode text NOT NULL, duration_ms int, created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE settings (
  key text PRIMARY KEY, value jsonb NOT NULL, updated_at timestamptz NOT NULL DEFAULT now()
);

-- ---------- public boundary: read-only views exposing only publishable columns ----------
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;
CREATE VIEW public_collections AS SELECT id, slug, title, description, sort_order, updated_at FROM collections WHERE published;
CREATE VIEW public_link_checks AS SELECT tool_id, field, url, ok, status_code, redirected, checked_at FROM link_checks;