descrybe/apps/api/sql/schema/031_support_ticket_detail.sql

-- +goose Up
-- Support auto series (agent 5/10): richer ticket detail, message auto metadata,
-- category taxonomy, activity timeline. Agent 3: add KB/templates in 032_* (do not
-- re-ALTER these ticket/message columns). Agent 4: reuse auto_reply_* + message
-- auto_* fields for AI outcomes — do not duplicate.

ALTER TABLE support_tickets DROP CONSTRAINT IF EXISTS support_tickets_category_check;

CREATE TABLE IF NOT EXISTS support_categories (
  slug TEXT PRIMARY KEY,
  label TEXT NOT NULL,
  parent_slug TEXT REFERENCES support_categories(slug) ON DELETE SET NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  match_intents TEXT[] NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO support_categories (slug, label, sort_order, match_intents) VALUES
  ('billing', 'Billing', 10, ARRAY['billing','invoice','stripe']),
  ('billing_credits', 'Billing / credits', 11, ARRAY['credits','quota']),
  ('bug', 'Bug / error', 20, ARRAY['bug','error','crash']),
  ('account', 'Account / access', 30, ARRAY['login','password','access']),
  ('integrations', 'Integrations', 40, ARRAY['woocommerce','shopify','api']),
  ('processing', 'Processing / AI', 50, ARRAY['processing','ai','gpt']),
  ('export', 'Export / channels', 60, ARRAY['export','feed','channel']),
  ('other', 'Other', 100, ARRAY[]::TEXT[])
ON CONFLICT (slug) DO NOTHING;

ALTER TABLE support_tickets
  ADD COLUMN IF NOT EXISTS tags TEXT[] NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS related_product_id UUID REFERENCES processed_products(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS related_sku TEXT,
  ADD COLUMN IF NOT EXISTS customer_context JSONB NOT NULL DEFAULT '{}'::jsonb,
  ADD COLUMN IF NOT EXISTS auto_reply_disabled BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS auto_reply_status TEXT NOT NULL DEFAULT 'none',
  ADD COLUMN IF NOT EXISTS auto_reply_attempted_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS auto_reply_message_id UUID,
  ADD COLUMN IF NOT EXISTS auto_reply_meta JSONB NOT NULL DEFAULT '{}'::jsonb;

-- +goose StatementBegin
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'support_tickets_auto_reply_message_id_fkey'
  ) THEN
    ALTER TABLE support_tickets
      ADD CONSTRAINT support_tickets_auto_reply_message_id_fkey
      FOREIGN KEY (auto_reply_message_id) REFERENCES support_messages(id) ON DELETE SET NULL;
  END IF;
END $$;
-- +goose StatementEnd

ALTER TABLE support_tickets DROP CONSTRAINT IF EXISTS support_tickets_auto_reply_status_check;
ALTER TABLE support_tickets
  ADD CONSTRAINT support_tickets_auto_reply_status_check
  CHECK (auto_reply_status IN (
    'none', 'matched', 'ai_draft', 'ai_sent', 'skipped', 'failed', 'handed_off'
  ));

ALTER TABLE support_tickets DROP CONSTRAINT IF EXISTS support_tickets_related_sku_len_check;
ALTER TABLE support_tickets
  ADD CONSTRAINT support_tickets_related_sku_len_check
  CHECK (related_sku IS NULL OR char_length(related_sku) <= 128);

CREATE INDEX IF NOT EXISTS support_tickets_tags_gin_idx
  ON support_tickets USING GIN (tags);

CREATE INDEX IF NOT EXISTS support_tickets_auto_reply_status_idx
  ON support_tickets (auto_reply_status)
  WHERE auto_reply_status <> 'none';

CREATE INDEX IF NOT EXISTS support_tickets_related_product_idx
  ON support_tickets (related_product_id)
  WHERE related_product_id IS NOT NULL;

ALTER TABLE support_messages
  ADD COLUMN IF NOT EXISTS auto_source TEXT,
  ADD COLUMN IF NOT EXISTS auto_confidence REAL,
  ADD COLUMN IF NOT EXISTS auto_ref_type TEXT,
  ADD COLUMN IF NOT EXISTS auto_ref_id UUID,
  ADD COLUMN IF NOT EXISTS is_auto_reply BOOLEAN NOT NULL DEFAULT false;

ALTER TABLE support_messages DROP CONSTRAINT IF EXISTS support_messages_auto_source_check;
ALTER TABLE support_messages
  ADD CONSTRAINT support_messages_auto_source_check
  CHECK (auto_source IS NULL OR auto_source IN ('kb', 'template', 'ai'));

CREATE TABLE IF NOT EXISTS support_ticket_activity (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ticket_id UUID NOT NULL REFERENCES support_tickets(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  kind TEXT NOT NULL,
  actor_role TEXT NOT NULL DEFAULT 'system'
    CHECK (actor_role IN ('user', 'agent', 'system', 'ai')),
  actor_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  message_id UUID REFERENCES support_messages(id) ON DELETE SET NULL,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT support_ticket_activity_kind_check CHECK (kind IN (
    'created',
    'customer_message',
    'agent_message',
    'system_message',
    'auto_reply',
    'ai_draft',
    'ai_sent',
    'ai_failed',
    'handed_off',
    'claimed',
    'released',
    'status_changed',
    'auto_disabled',
    'auto_enabled',
    'note'
  ))
);

CREATE INDEX IF NOT EXISTS support_ticket_activity_ticket_idx
  ON support_ticket_activity (ticket_id, created_at ASC);

CREATE INDEX IF NOT EXISTS support_ticket_activity_company_idx
  ON support_ticket_activity (company_id, created_at DESC);

ALTER TABLE support_notifications DROP CONSTRAINT IF EXISTS support_notifications_kind_check;
ALTER TABLE support_notifications
  ADD CONSTRAINT support_notifications_kind_check
  CHECK (kind IN (
    'ticket_created',
    'agent_reply',
    'status_changed',
    'user_reply',
    'ticket_claimed',
    'csat_requested',
    'auto_reply'
  ));

-- +goose Down
ALTER TABLE support_notifications DROP CONSTRAINT IF EXISTS support_notifications_kind_check;
ALTER TABLE support_notifications
  ADD CONSTRAINT support_notifications_kind_check
  CHECK (kind IN (
    'ticket_created',
    'agent_reply',
    'status_changed',
    'user_reply',
    'ticket_claimed',
    'csat_requested'
  ));

DROP INDEX IF EXISTS support_ticket_activity_company_idx;
DROP INDEX IF EXISTS support_ticket_activity_ticket_idx;
DROP TABLE IF EXISTS support_ticket_activity;

ALTER TABLE support_messages DROP CONSTRAINT IF EXISTS support_messages_auto_source_check;
ALTER TABLE support_messages
  DROP COLUMN IF EXISTS is_auto_reply,
  DROP COLUMN IF EXISTS auto_ref_id,
  DROP COLUMN IF EXISTS auto_ref_type,
  DROP COLUMN IF EXISTS auto_confidence,
  DROP COLUMN IF EXISTS auto_source;

DROP INDEX IF EXISTS support_tickets_related_product_idx;
DROP INDEX IF EXISTS support_tickets_auto_reply_status_idx;
DROP INDEX IF EXISTS support_tickets_tags_gin_idx;

ALTER TABLE support_tickets DROP CONSTRAINT IF EXISTS support_tickets_auto_reply_message_id_fkey;
ALTER TABLE support_tickets DROP CONSTRAINT IF EXISTS support_tickets_related_sku_len_check;
ALTER TABLE support_tickets DROP CONSTRAINT IF EXISTS support_tickets_auto_reply_status_check;
ALTER TABLE support_tickets
  DROP COLUMN IF EXISTS auto_reply_meta,
  DROP COLUMN IF EXISTS auto_reply_message_id,
  DROP COLUMN IF EXISTS auto_reply_attempted_at,
  DROP COLUMN IF EXISTS auto_reply_status,
  DROP COLUMN IF EXISTS auto_reply_disabled,
  DROP COLUMN IF EXISTS customer_context,
  DROP COLUMN IF EXISTS related_sku,
  DROP COLUMN IF EXISTS related_product_id,
  DROP COLUMN IF EXISTS tags;

DROP TABLE IF EXISTS support_categories;

ALTER TABLE support_tickets DROP CONSTRAINT IF EXISTS support_tickets_category_check;
ALTER TABLE support_tickets
  ADD CONSTRAINT support_tickets_category_check
  CHECK (category IN ('billing', 'bug', 'account', 'other'));