-- 25_full_text_search_support.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Full‑text search over support tickets.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.

-- Full‑text search on support tickets
CREATE TABLE IF NOT EXISTS bank.support_tickets (
  ticket_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT REFERENCES bank.customers(customer_id),
  subject TEXT, body TEXT,
  tsv tsvector
);

CREATE TRIGGER support_tsv BEFORE INSERT OR UPDATE ON bank.support_tickets
FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(tsv, 'pg_catalog.english', subject, body);

CREATE INDEX IF NOT EXISTS support_tsv_idx ON bank.support_tickets USING gin(tsv);

-- Search
-- SELECT ticket_id FROM bank.support_tickets WHERE tsv @@ plainto_tsquery('english','lost card');
