-- Quality Audits Table
-- Stores daily 0.1% quality audit results for trending

CREATE TABLE IF NOT EXISTS quality_audits (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  score INTEGER NOT NULL, -- Overall score 0-100
  passed INTEGER NOT NULL DEFAULT 0,
  failed INTEGER NOT NULL DEFAULT 0,
  warnings INTEGER NOT NULL DEFAULT 0,
  duration_ms INTEGER, -- How long the audit took
  results_json JSONB, -- Full audit results
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for querying audit history
CREATE INDEX IF NOT EXISTS idx_quality_audits_created_at ON quality_audits(created_at DESC);

-- RLS
ALTER TABLE quality_audits ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users to view audits
CREATE POLICY "Authenticated users can view quality audits"
  ON quality_audits FOR SELECT TO authenticated
  USING (true);

-- Only system can insert (no user policy needed - inserts happen server-side)

COMMENT ON TABLE quality_audits IS 'Daily 0.1% quality audit results for trending and alerts';
