-- ============================================
-- WEBHOOKS: Webhook Configuration Table
-- ============================================

-- Main webhooks table
CREATE TABLE webhooks.webhooks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,

  -- Webhook config
  name VARCHAR(100) NOT NULL,
  description TEXT,
  url TEXT NOT NULL,
  secret TEXT NOT NULL, -- HMAC signing secret (encrypted in app layer)

  -- Events subscribed (stored as text array for flexibility)
  events TEXT[] NOT NULL DEFAULT '{}',

  -- Custom headers to send
  headers JSONB DEFAULT '{}',

  -- Status
  is_active BOOLEAN NOT NULL DEFAULT true,

  -- Metadata
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by UUID REFERENCES users.users(id),

  -- Constraints
  CONSTRAINT webhooks_url_valid CHECK (url ~ '^https://'),
  CONSTRAINT webhooks_events_not_empty CHECK (array_length(events, 1) > 0 OR events = '{}')
);

-- Indexes
CREATE INDEX idx_webhooks_tenant ON webhooks.webhooks(tenant_id);
CREATE INDEX idx_webhooks_active ON webhooks.webhooks(tenant_id, is_active) WHERE is_active = true;

-- RLS
ALTER TABLE webhooks.webhooks ENABLE ROW LEVEL SECURITY;

CREATE POLICY webhooks_tenant_isolation ON webhooks.webhooks
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Comment
COMMENT ON TABLE webhooks.webhooks IS 'Webhook configurations per tenant';
COMMENT ON COLUMN webhooks.webhooks.secret IS 'HMAC-SHA256 signing secret for payload verification';
COMMENT ON COLUMN webhooks.webhooks.events IS 'Array of event types this webhook subscribes to';
COMMENT ON COLUMN webhooks.webhooks.headers IS 'Custom HTTP headers to include in webhook requests';