migrations/MASTER_SCHEMA.sql

-- ══════════════════════════════════════════════════════════════════════════════
-- AGENTE AI — Schema Supabase completo
-- Incolla questo file nel SQL Editor: Dashboard → SQL Editor → New query → Run
-- Tutti gli statement sono idempotenti (IF NOT EXISTS / CREATE OR REPLACE).
-- ══════════════════════════════════════════════════════════════════════════════


-- ── 0. Estensioni ─────────────────────────────────────────────────────────────
CREATE EXTENSION IF NOT EXISTS vector;


-- ══════════════════════════════════════════════════════════════════════════════
-- 1. MEMORIA EPISODICA — tabella episodes
-- ══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.episodes (
    id      BIGSERIAL   PRIMARY KEY,
    type    TEXT        NOT NULL,
    task    TEXT        NOT NULL,
    output  TEXT,
    success INTEGER     DEFAULT 1,
    ts      TEXT        NOT NULL,
    tags    TEXT        DEFAULT '[]'
);

CREATE INDEX IF NOT EXISTS idx_episodes_type ON public.episodes (type);
CREATE INDEX IF NOT EXISTS idx_episodes_ts   ON public.episodes (ts);

ALTER TABLE public.episodes ENABLE ROW LEVEL SECURITY;
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='episodes' AND policyname='anon full access') THEN
    CREATE POLICY "anon full access" ON public.episodes FOR ALL TO anon USING (true) WITH CHECK (true);
  END IF;
END $$;


-- ══════════════════════════════════════════════════════════════════════════════
-- 2. MEMORIA SEMANTICA — tabella semantic_memory + pgvector
-- ══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.semantic_memory (
    id        TEXT    PRIMARY KEY,
    content   TEXT    NOT NULL,
    metadata  JSONB   DEFAULT '{}'
);

ALTER TABLE public.semantic_memory
    ADD COLUMN IF NOT EXISTS embedding vector(384);

ALTER TABLE public.semantic_memory ENABLE ROW LEVEL SECURITY;
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='semantic_memory' AND policyname='anon full access') THEN
    CREATE POLICY "anon full access" ON public.semantic_memory FOR ALL TO anon USING (true) WITH CHECK (true);
  END IF;
END $$;

CREATE OR REPLACE FUNCTION match_semantic_memory(
    query_embedding vector(384),
    match_count     int   DEFAULT 5,
    match_threshold float DEFAULT 0.2
)
RETURNS TABLE (id text, content text, metadata jsonb, similarity float)
LANGUAGE sql STABLE AS $$
    SELECT id, content, metadata,
           (1 - (embedding <=> query_embedding))::float AS similarity
    FROM public.semantic_memory
    WHERE embedding IS NOT NULL
      AND (1 - (embedding <=> query_embedding)) > match_threshold
    ORDER BY embedding <=> query_embedding
    LIMIT match_count;
$$;


-- ══════════════════════════════════════════════════════════════════════════════
-- 3. MEMORIA AGENTE — tabella agent_memory (incident registry + decision memory)
-- ══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.agent_memory (
    key        TEXT        PRIMARY KEY,
    value      TEXT        NOT NULL DEFAULT '{}',
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.agent_memory ENABLE ROW LEVEL SECURITY;
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='agent_memory' AND policyname='anon full access') THEN
    CREATE POLICY "anon full access" ON public.agent_memory FOR ALL TO anon USING (true) WITH CHECK (true);
  END IF;
END $$;


-- ══════════════════════════════════════════════════════════════════════════════
-- 4. TASK AGENTE — tabelle agent_tasks + agent_task_events
-- ══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.agent_tasks (
    task_id     TEXT    PRIMARY KEY,
    goal        TEXT    NOT NULL,
    status      TEXT    NOT NULL DEFAULT 'QUEUED',
    max_steps   INTEGER NOT NULL DEFAULT 8,
    context     TEXT    NOT NULL DEFAULT '[]',
    created_at  BIGINT  NOT NULL DEFAULT 0,
    updated_at  BIGINT  NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS public.agent_task_events (
    id           BIGSERIAL PRIMARY KEY,
    task_id      TEXT      NOT NULL REFERENCES public.agent_tasks(task_id) ON DELETE CASCADE,
    event_index  INTEGER   NOT NULL,
    event_data   TEXT      NOT NULL,
    created_at   BIGINT    NOT NULL DEFAULT 0,
    UNIQUE (task_id, event_index)
);

CREATE INDEX IF NOT EXISTS idx_agent_task_events_lookup
    ON public.agent_task_events (task_id, event_index ASC);

ALTER TABLE public.agent_tasks       ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.agent_task_events ENABLE ROW LEVEL SECURITY;
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='agent_tasks' AND policyname='anon full access') THEN
    CREATE POLICY "anon full access" ON public.agent_tasks       FOR ALL TO anon USING (true) WITH CHECK (true);
    CREATE POLICY "anon full access" ON public.agent_task_events FOR ALL TO anon USING (true) WITH CHECK (true);
  END IF;
END $$;

CREATE OR REPLACE FUNCTION purge_old_agent_tasks() RETURNS void
LANGUAGE plpgsql AS $$
DECLARE cutoff BIGINT := (EXTRACT(EPOCH FROM NOW()) * 1000 - 7 * 86400000)::BIGINT;
BEGIN DELETE FROM public.agent_tasks WHERE updated_at < cutoff; END;
$$;


-- ══════════════════════════════════════════════════════════════════════════════
-- 5. SKILL TRACKER — tabelle skill_stats + skill_patterns
-- ══════════════════════════════════════════════════════════════════════════════
CREATE OR REPLACE FUNCTION _touch_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$;

CREATE TABLE IF NOT EXISTS public.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)
);

CREATE INDEX IF NOT EXISTS idx_skill_stats_session ON public.skill_stats (session_id);
ALTER TABLE public.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 public.skill_stats FOR ALL TO anon USING (true) WITH CHECK (true);
  END IF;
END $$;
DROP TRIGGER IF EXISTS trg_skill_stats_updated_at ON public.skill_stats;
CREATE TRIGGER trg_skill_stats_updated_at BEFORE UPDATE ON public.skill_stats
    FOR EACH ROW EXECUTE FUNCTION _touch_updated_at();

CREATE TABLE IF NOT EXISTS public.skill_patterns (
    id             TEXT             PRIMARY KEY,
    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 public.skill_patterns (confidence DESC);
CREATE INDEX IF NOT EXISTS idx_skill_patterns_last_used  ON public.skill_patterns (last_used  DESC);
ALTER TABLE public.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 public.skill_patterns FOR ALL TO anon USING (true) WITH CHECK (true);
  END IF;
END $$;
DROP TRIGGER IF EXISTS trg_skill_patterns_updated_at ON public.skill_patterns;
CREATE TRIGGER trg_skill_patterns_updated_at BEFORE UPDATE ON public.skill_patterns
    FOR EACH ROW EXECUTE FUNCTION _touch_updated_at();


-- ══════════════════════════════════════════════════════════════════════════════
-- 6. FILE SYSTEM VIRTUALE — tabella vfs_files
-- ══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.vfs_files (
    id         TEXT        PRIMARY KEY,
    path       TEXT        NOT NULL,
    content    TEXT        NOT NULL DEFAULT '',
    mime_type  TEXT        NOT NULL DEFAULT 'text/plain',
    size       BIGINT      NOT NULL DEFAULT 0,
    created_at BIGINT      NOT NULL DEFAULT 0,
    updated_at BIGINT      NOT NULL DEFAULT 0
);

ALTER TABLE public.vfs_files ADD COLUMN IF NOT EXISTS version BIGINT NOT NULL DEFAULT 0;

CREATE INDEX IF NOT EXISTS idx_vfs_files_path    ON public.vfs_files (path);
CREATE INDEX IF NOT EXISTS vfs_files_version_idx ON public.vfs_files (id, version);

ALTER TABLE public.vfs_files ENABLE ROW LEVEL SECURITY;
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='vfs_files' AND policyname='anon full access') THEN
    CREATE POLICY "anon full access" ON public.vfs_files FOR ALL TO anon USING (true) WITH CHECK (true);
  END IF;
END $$;

CREATE OR REPLACE FUNCTION public._vfs_bump_version()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.version = OLD.version + 1; RETURN NEW; END;
$$;
DROP TRIGGER IF EXISTS trg_vfs_version ON public.vfs_files;
CREATE TRIGGER trg_vfs_version BEFORE UPDATE ON public.vfs_files
    FOR EACH ROW EXECUTE FUNCTION public._vfs_bump_version();


-- ══════════════════════════════════════════════════════════════════════════════
-- 7. TOKEN OAUTH — tabelle managed_tokens + oauth_states
-- ══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.managed_tokens (
    user_id       TEXT   NOT NULL DEFAULT 'default',
    provider      TEXT   NOT NULL,
    access_token  TEXT   NOT NULL DEFAULT '',
    refresh_token TEXT   NOT NULL DEFAULT '',
    expires_at    BIGINT NOT NULL DEFAULT 0,
    scope         TEXT   NOT NULL DEFAULT '',
    raw_meta      TEXT   NOT NULL DEFAULT '{}',
    created_at    BIGINT NOT NULL DEFAULT 0,
    updated_at    BIGINT NOT NULL DEFAULT 0,
    UNIQUE (user_id, provider)
);

CREATE INDEX IF NOT EXISTS managed_tokens_user_idx ON public.managed_tokens (user_id, provider);
ALTER TABLE public.managed_tokens ENABLE ROW LEVEL SECURITY;
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='managed_tokens' AND policyname='service only') THEN
    CREATE POLICY "service only" ON public.managed_tokens FOR ALL TO service_role USING (true) WITH CHECK (true);
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS public.oauth_states (
    state      TEXT   PRIMARY KEY,
    provider   TEXT   NOT NULL,
    user_id    TEXT   NOT NULL DEFAULT 'default',
    created_at BIGINT NOT NULL DEFAULT 0
);

CREATE OR REPLACE FUNCTION public._purge_oauth_states() RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
    DELETE FROM public.oauth_states
    WHERE created_at < (EXTRACT(EPOCH FROM NOW()) * 1000 - 600000)::BIGINT;
END;
$$;


-- ══════════════════════════════════════════════════════════════════════════════
-- FINE — Verifica eseguendo:
--   SELECT table_name FROM information_schema.tables
--   WHERE table_schema = 'public' ORDER BY table_name;
-- ══════════════════════════════════════════════════════════════════════════════