-- Intelligence Alerts Table
-- Used by frequentAudit.js for unified alerting system
-- Tracks quality audit failures, auto-resolves when tests pass

CREATE TABLE IF NOT EXISTS intelligence_alerts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  alert_type VARCHAR(100) NOT NULL,
  severity VARCHAR(20) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  evidence JSONB DEFAULT '{}',
  suggested_action TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  resolved_at TIMESTAMPTZ,
  resolution_notes TEXT,
  was_true_positive BOOLEAN
);

-- Index for checking duplicate alerts
CREATE INDEX IF NOT EXISTS idx_intelligence_alerts_type ON intelligence_alerts(alert_type);

-- Index for finding unresolved alerts
CREATE INDEX IF NOT EXISTS idx_intelligence_alerts_unresolved ON intelligence_alerts(resolved_at)
WHERE resolved_at IS NULL;

-- Index for recent alerts lookup
CREATE INDEX IF NOT EXISTS idx_intelligence_alerts_created ON intelligence_alerts(created_at DESC);

-- RLS policies
ALTER TABLE intelligence_alerts ENABLE ROW LEVEL SECURITY;

-- Allow all operations for authenticated users
CREATE POLICY "intelligence_alerts_authenticated_all" ON intelligence_alerts
FOR ALL TO authenticated
USING (true) WITH CHECK (true);

-- Allow service role full access
CREATE POLICY "intelligence_alerts_service_all" ON intelligence_alerts
FOR ALL TO service_role
USING (true) WITH CHECK (true);

-- Updated_at trigger
CREATE OR REPLACE FUNCTION update_intelligence_alerts_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS intelligence_alerts_updated_at ON intelligence_alerts;
CREATE TRIGGER intelligence_alerts_updated_at
  BEFORE UPDATE ON intelligence_alerts
  FOR EACH ROW
  EXECUTE FUNCTION update_intelligence_alerts_updated_at();
