-- GAP-SUP1: Supabase Skill Tracker persistence
-- Eseguire nel SQL Editor di Supabase:
--   https://app.supabase.com/project/_/sql/new
--
-- Tabella skill_stats: statistiche tool per sessione (backend skill_tracker.py)
-- Tabella skill_patterns: pattern tool sequenze (frontend skillPersistence.ts)

-- ─── skill_stats ──────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS skill_stats (
  session_id       text             NOT NULL,
  tool_name        text             NOT NULL,
  success_count    integer          NOT NULL DEFAULT 0,
  fail_count       integer          NOT NULL DEFAULT 0,
  last_used        double precision NOT NULL DEFAULT 0,
  total_latency_ms double precision NOT NULL DEFAULT 0,
  updated_at       timestamptz      NOT NULL DEFAULT now(),
  PRIMARY KEY (session_id, tool_name)
);

-- Index per query per sessione (GET ?session_id=eq.xxx)
CREATE INDEX IF NOT EXISTS idx_skill_stats_session ON skill_stats (session_id);

-- Row Level Security: anon può leggere e scrivere (chiave anon pubblica)
ALTER TABLE skill_stats ENABLE ROW LEVEL SECURITY;

DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'skill_stats' AND policyname = 'anon full access'
  ) THEN
    CREATE POLICY "anon full access" ON skill_stats
      FOR ALL TO anon
      USING (true)
      WITH CHECK (true);
  END IF;
END $$;

-- Trigger: aggiorna updated_at ad ogni upsert
CREATE OR REPLACE FUNCTION _touch_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$;

DROP TRIGGER IF EXISTS trg_skill_stats_updated_at ON skill_stats;
CREATE TRIGGER trg_skill_stats_updated_at
  BEFORE UPDATE ON skill_stats
  FOR EACH ROW EXECUTE FUNCTION _touch_updated_at();


-- ─── skill_patterns ────────────────────────────────────────────────────────────
-- Persistenza cross-device per skillPersistence.ts (frontend Dexie → Supabase cloud)

CREATE TABLE IF NOT EXISTS skill_patterns (
  id             text             PRIMARY KEY,   -- djb2(taskSignature)
  task_signature text             NOT NULL,
  tool_sequence  text[]           NOT NULL,
  success_count  integer          NOT NULL DEFAULT 0,
  total_count    integer          NOT NULL DEFAULT 0,
  last_used      bigint           NOT NULL DEFAULT 0,
  confidence     double precision NOT NULL DEFAULT 0,
  updated_at     timestamptz      NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_skill_patterns_confidence ON skill_patterns (confidence DESC);
CREATE INDEX IF NOT EXISTS idx_skill_patterns_last_used  ON skill_patterns (last_used  DESC);

ALTER TABLE skill_patterns ENABLE ROW LEVEL SECURITY;

DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'skill_patterns' AND policyname = 'anon full access'
  ) THEN
    CREATE POLICY "anon full access" ON skill_patterns
      FOR ALL TO anon
      USING (true)
      WITH CHECK (true);
  END IF;
END $$;

DROP TRIGGER IF EXISTS trg_skill_patterns_updated_at ON skill_patterns;
CREATE TRIGGER trg_skill_patterns_updated_at
  BEFORE UPDATE ON skill_patterns
  FOR EACH ROW EXECUTE FUNCTION _touch_updated_at();