-- Sprint 3.6c — Analyse patterns (Premium).
--
-- Table pattern_analyses : snapshot des patterns récurrents détectés sur les
-- 5 dernières productions corrigées + exercices long terme + indice de préparation.
--
-- Stratégie d'invalidation : on INSERT un nouveau row à chaque recompute (pas
-- d'UPDATE), pour garder un historique des analyses. La plus récente est
-- récupérée via ORDER BY created_at DESC LIMIT 1.
--
-- À exécuter manuellement via `supabase db push` (Règle F).

CREATE TABLE IF NOT EXISTS pattern_analyses (
  id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id              UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  productions_ids      UUID[] NOT NULL,
  patterns             JSONB NOT NULL,
  exercises            JSONB NOT NULL,
  preparation_index    INTEGER NOT NULL,
  preparation_message  TEXT NOT NULL,
  analyzed_count       INTEGER NOT NULL DEFAULT 5,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE pattern_analyses
  DROP CONSTRAINT IF EXISTS pattern_analyses_preparation_index_check,
  ADD  CONSTRAINT pattern_analyses_preparation_index_check
    CHECK (preparation_index BETWEEN 0 AND 100);

CREATE INDEX IF NOT EXISTS pattern_analyses_user_created_idx
  ON pattern_analyses (user_id, created_at DESC);

ALTER TABLE pattern_analyses ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Utilisateur voit ses analyses" ON pattern_analyses;
CREATE POLICY "Utilisateur voit ses analyses"
  ON pattern_analyses FOR SELECT
  USING (auth.uid() = user_id);