-- backend/migrations/p38_managed_tokens.sql
-- OAuth Managed Tokens: connettori one-click (GitHub, Google, Instagram, …)
-- Eseguire nel SQL Editor di Supabase (idempotente).
--
-- Tabella: managed_tokens
-- Scopo: store encrypted OAuth access/refresh token per provider, per user.
-- Token cifrati con Fernet (VAULT_KEY) prima della scrittura — mai in chiaro su DB.

CREATE TABLE IF NOT EXISTS public.managed_tokens (
  id            TEXT        PRIMARY KEY DEFAULT gen_random_uuid()::TEXT,
  user_id       TEXT        NOT NULL DEFAULT 'default',
  provider      TEXT        NOT NULL,                    -- 'github' | 'google' | 'instagram'
  access_token  TEXT        NOT NULL DEFAULT '',         -- encrypted (Fernet)
  refresh_token TEXT        NOT NULL DEFAULT '',         -- encrypted (Fernet, vuoto se non fornito)
  expires_at    BIGINT      NOT NULL DEFAULT 0,          -- UNIX ms, 0 = non-expiring
  scope         TEXT        NOT NULL DEFAULT '',         -- scopes concessi dal provider
  raw_meta      TEXT        NOT NULL DEFAULT '{}',       -- JSON extra (email, avatar, ecc.)
  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);

-- RLS: solo service_role può leggere/scrivere (token sono segreti)
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 $$;

-- Tabella oauth_states: CSRF state tokens (pulizia automatica dopo 10min)
-- Usata dal backend per validare i callback OAuth (state parameter)
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
);

-- Pulizia automatica stati scaduti (>10min = 600000ms)
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;
$$;