-- Outbound Webhooks System
-- Send event notifications to external services (Zapier, n8n, etc.)

CREATE TABLE IF NOT EXISTS webhook_endpoints (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  url TEXT NOT NULL,
  secret TEXT, -- For signing payloads (HMAC)
  events TEXT[] NOT NULL, -- ['lead.created', 'lead.converted', 'call.completed']
  is_active BOOLEAN DEFAULT true,
  retry_config JSONB DEFAULT '{"max_attempts": 3, "backoff_seconds": [60, 300, 900]}',
  headers JSONB DEFAULT '{}', -- Custom headers to include
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS webhook_deliveries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  endpoint_id UUID REFERENCES webhook_endpoints(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  payload JSONB NOT NULL,
  status TEXT DEFAULT 'pending', -- 'pending', 'success', 'failed', 'retrying'
  attempts INTEGER DEFAULT 0,
  max_attempts INTEGER DEFAULT 3,
  response_code INTEGER,
  response_body TEXT,
  error_message TEXT,
  next_retry_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_webhook_endpoints_active ON webhook_endpoints(is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_webhook_endpoints_created_by ON webhook_endpoints(created_by);
CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_endpoint ON webhook_deliveries(endpoint_id);
CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_status ON webhook_deliveries(status);
CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_pending ON webhook_deliveries(next_retry_at)
  WHERE status IN ('pending', 'retrying');
CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_created ON webhook_deliveries(created_at DESC);

-- RLS policies
ALTER TABLE webhook_endpoints ENABLE ROW LEVEL SECURITY;
ALTER TABLE webhook_deliveries ENABLE ROW LEVEL SECURITY;

CREATE POLICY "webhook_endpoints_authenticated_all"
  ON webhook_endpoints FOR ALL TO authenticated
  USING (true) WITH CHECK (true);

CREATE POLICY "webhook_endpoints_service_all"
  ON webhook_endpoints FOR ALL TO service_role
  USING (true) WITH CHECK (true);

CREATE POLICY "webhook_deliveries_authenticated_read"
  ON webhook_deliveries FOR SELECT TO authenticated
  USING (true);

CREATE POLICY "webhook_deliveries_service_all"
  ON webhook_deliveries FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Function to clean up old deliveries
CREATE OR REPLACE FUNCTION cleanup_old_webhook_deliveries()
RETURNS void AS $$
BEGIN
  -- Delete successful deliveries older than 7 days
  DELETE FROM webhook_deliveries
  WHERE status = 'success'
    AND completed_at < NOW() - INTERVAL '7 days';

  -- Delete failed deliveries older than 30 days
  DELETE FROM webhook_deliveries
  WHERE status = 'failed'
    AND completed_at < NOW() - INTERVAL '30 days';
END;
$$ LANGUAGE plpgsql;
