-- 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,
  date_fin TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

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(),
  PRIMARY KEY (licence_id, device_id)
);

-- 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()
);

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