-- Schéma Supabase pour Caplogg (Budget étudiant)
-- À exécuter dans l’éditeur SQL du projet Supabase (Dashboard > SQL Editor).

-- Config globale (singleton)
CREATE TABLE IF NOT EXISTS config (
  id TEXT PRIMARY KEY DEFAULT 'default',
  app_name TEXT NOT NULL DEFAULT 'Budget étudiant',
  app_favicon_url TEXT,
  partenaires_universitaires JSONB NOT NULL DEFAULT '[]',
  partenaires_projet JSONB NOT NULL DEFAULT '[]',
  updated_at TIMESTAMPTZ DEFAULT now()
);
-- Pour bases existantes : ALTER TABLE config ADD COLUMN IF NOT EXISTS app_favicon_url TEXT;

-- Établissements
CREATE TABLE IF NOT EXISTS establishments (
  id TEXT PRIMARY KEY,
  nom TEXT NOT NULL,
  ville TEXT NOT NULL,
  theme JSONB NOT NULL DEFAULT '{}',
  campuses JSONB DEFAULT '[]',
  maintenance BOOLEAN DEFAULT false,
  maintenance_message TEXT DEFAULT '',
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Licences (codes d’accès par établissement)
CREATE TABLE IF NOT EXISTS licences (
  id TEXT PRIMARY KEY,
  code TEXT NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('session', 'participant', 'etudiant', 'admin')),
  establishment_id TEXT NOT NULL REFERENCES establishments(id) ON DELETE CASCADE,
  actif BOOLEAN DEFAULT true,
  limite_appareils INTEGER DEFAULT 0,
  require_account BOOLEAN DEFAULT false,
  date_fin TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);
-- Pour bases existantes : ALTER TABLE licences ADD COLUMN IF NOT EXISTS require_account BOOLEAN DEFAULT false;

CREATE INDEX IF NOT EXISTS idx_licences_establishment ON licences(establishment_id);
CREATE INDEX IF NOT EXISTS idx_licences_code ON licences(code);

-- Appareils (licence + device pour déduplication)
CREATE TABLE IF NOT EXISTS devices (
  licence_id TEXT NOT NULL,
  device_id TEXT DEFAULT '',
  last_activity TIMESTAMPTZ NOT NULL DEFAULT now(),
  auth_version INTEGER NOT NULL DEFAULT 0,
  revoked_at TIMESTAMPTZ,
  PRIMARY KEY (licence_id, device_id)
);
-- Pour bases existantes :
ALTER TABLE devices ADD COLUMN IF NOT EXISTS auth_version INTEGER NOT NULL DEFAULT 0;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS revoked_at TIMESTAMPTZ;

-- Sessions en direct (code 6 caractères)
CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY,
  code TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('waiting', 'active', 'ended')),
  establishment_id TEXT NOT NULL REFERENCES establishments(id) ON DELETE CASCADE,
  duration_minutes INTEGER DEFAULT 15,
  started_at BIGINT,
  participants JSONB DEFAULT '[]',
  joined_device_ids JSONB DEFAULT '[]',
  join_tokens JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_sessions_code ON sessions(code);
CREATE INDEX IF NOT EXISTS idx_sessions_establishment ON sessions(establishment_id);

-- Parcours (liste des simulations : un seul enregistrement contenant le tableau)
CREATE TABLE IF NOT EXISTS parcours (
  id TEXT PRIMARY KEY DEFAULT 'single',
  data JSONB NOT NULL DEFAULT '[]',
  updated_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO parcours (id, data) VALUES ('single', '[]')
ON CONFLICT (id) DO NOTHING;

-- Feedback (retours établissements → super-admin)
CREATE TABLE IF NOT EXISTS feedback (
  id TEXT PRIMARY KEY,
  message TEXT,
  priority TEXT,
  establishment_id TEXT,
  establishment_nom TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  messages JSONB DEFAULT '[]',
  status TEXT DEFAULT 'en_cours',
  reply TEXT,
  replied_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ,
  note NUMERIC
);

CREATE INDEX IF NOT EXISTS idx_feedback_establishment ON feedback(establishment_id);

-- Scénarios (un blob JSON par établissement)
CREATE TABLE IF NOT EXISTS scenarios (
  establishment_id TEXT PRIMARY KEY REFERENCES establishments(id) ON DELETE CASCADE,
  data JSONB NOT NULL DEFAULT '{}',
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Scénario de référence (utilisé pour les établissements sans scénario propre)
CREATE TABLE IF NOT EXISTS scenario_default (
  id TEXT PRIMARY KEY DEFAULT 'single',
  data JSONB NOT NULL DEFAULT '{}',
  updated_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO scenario_default (id, data) VALUES ('single', '{}')
ON CONFLICT (id) DO NOTHING;

-- Textes de référence (singleton, fusionnés avec textes établissement)
CREATE TABLE IF NOT EXISTS reference_texts (
  id TEXT PRIMARY KEY DEFAULT 'default',
  data JSONB NOT NULL DEFAULT '{}',
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Textes par établissement (overrides)
CREATE TABLE IF NOT EXISTS establishment_texts (
  establishment_id TEXT PRIMARY KEY REFERENCES establishments(id) ON DELETE CASCADE,
  data JSONB NOT NULL DEFAULT '{}',
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Comptes utilisateurs (email/mot de passe grand public).
-- Le blob JSONB "data" contient l'objet utilisateur complet (hash scrypt, tokens
-- de reset/vérification, préférences) : mêmes champs que le stockage fichier.
CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  data JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- Récaps partagés (liens publics de partage de simulation)
CREATE TABLE IF NOT EXISTS shared_recaps (
  id TEXT PRIMARY KEY,
  data JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE shared_recaps ENABLE ROW LEVEL SECURITY;

-- Données initiales
INSERT INTO config (id, app_name) VALUES ('default', 'Budget étudiant')
ON CONFLICT (id) DO NOTHING;

-- ── Row Level Security (à activer si accès direct Supabase hors service_role) ──
-- Le backend utilise service_role et bypass RLS ; ces policies protègent un accès anon/authenticated futur.

ALTER TABLE establishments ENABLE ROW LEVEL SECURITY;
ALTER TABLE licences ENABLE ROW LEVEL SECURITY;
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE parcours ENABLE ROW LEVEL SECURITY;
ALTER TABLE scenarios ENABLE ROW LEVEL SECURITY;

-- Exemple : isolation par establishment_id (adapter selon modèle auth Supabase)
-- CREATE POLICY tenant_isolation_sessions ON sessions
--   FOR ALL USING (establishment_id = current_setting('app.establishment_id', true));