-- ════════════════════════════════════════════════════════════════════════
--  Palimpseste v2 — schéma MySQL / MariaDB (OVH mutualisé)
--  Encodage utf8mb4 partout (accents, communes, emoji dans les notes).
--  Fuseau applicatif : Europe/Paris (géré côté PHP/JS, stockage en UTC).
-- ════════════════════════════════════════════════════════════════════════

SET NAMES utf8mb4;
SET time_zone = '+00:00';   -- stockage en UTC ; conversion Paris à l'affichage

-- ── Utilisateurs ─────────────────────────────────────────────────────────
-- Comptes sur invitation (pas d'inscription ouverte). L'admin crée les comptes.
CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  username      VARCHAR(40)   NOT NULL,            -- sert au nommage des fichiers
  display_name  VARCHAR(80)   NOT NULL,
  password_hash VARCHAR(255)  NOT NULL,            -- password_hash() PHP (bcrypt)
  role          ENUM('admin','member') NOT NULL DEFAULT 'member',
  created_at    DATETIME      NOT NULL DEFAULT UTC_TIMESTAMP(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Sessions (auth par token, compatible offline-first) ──────────────────
-- Token long-lived stocké côté client ; révoquable côté serveur.
CREATE TABLE IF NOT EXISTS sessions (
  token       CHAR(64)      NOT NULL,              -- aléatoire, hex 32 octets
  user_id     INT UNSIGNED  NOT NULL,
  created_at  DATETIME      NOT NULL DEFAULT UTC_TIMESTAMP(),
  last_seen   DATETIME      NOT NULL DEFAULT UTC_TIMESTAMP(),
  PRIMARY KEY (token),
  KEY idx_user (user_id),
  CONSTRAINT fk_sess_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Mots-clés (partagés entre tous, données communes) ────────────────────
CREATE TABLE IF NOT EXISTS keywords (
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  label  VARCHAR(60)  NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_label (label)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ════════════════════════════════════════════════════════════════════════
--  Objets de terrain : traces, waypoints. Données PARTAGÉES (tous les voient).
--  Chaque objet porte un uuid généré côté CLIENT → permet la création offline
--  sans collision et la déduplication à l'import (cf. cahier des charges pt 16).
-- ════════════════════════════════════════════════════════════════════════

-- ── Traces GPS ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tracks (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid          CHAR(36)     NOT NULL,             -- UUID v4 client
  user_id       INT UNSIGNED NOT NULL,
  name          VARCHAR(255) NOT NULL,             -- AAAAMMJJ_HHMM_user_commDep_commArr
  recorded_at   DATETIME     NOT NULL,             -- début d'enregistrement (UTC)
  -- Géométrie : liste de points [lon,lat,ele,time] en JSON compact.
  -- Sur mutualisé OVH, pas d'extension spatiale fiable → JSON + bbox indexée.
  geojson       MEDIUMTEXT   NOT NULL,             -- LineString GeoJSON
  -- Boîte englobante pour filtrage rapide par zone sans parser le GeoJSON
  bbox_min_lat  DOUBLE       NOT NULL,
  bbox_min_lon  DOUBLE       NOT NULL,
  bbox_max_lat  DOUBLE       NOT NULL,
  bbox_max_lon  DOUBLE       NOT NULL,
  length_m      DOUBLE       NOT NULL DEFAULT 0,   -- longueur calculée (mètres)
  -- Rattachement administratif (résolu via geo.api.gouv.fr à la synchro)
  dept_code     VARCHAR(3)   DEFAULT NULL,         -- ex. '24'
  commune_start VARCHAR(120) DEFAULT NULL,
  commune_end   VARCHAR(120) DEFAULT NULL,
  notes         TEXT         DEFAULT NULL,
  -- Métadonnées de synchro
  created_at    DATETIME     NOT NULL DEFAULT UTC_TIMESTAMP(),
  updated_at    DATETIME     NOT NULL DEFAULT UTC_TIMESTAMP(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_uuid (uuid),
  KEY idx_user (user_id),
  KEY idx_dept (dept_code),
  KEY idx_recorded (recorded_at),
  KEY idx_bbox (bbox_min_lat, bbox_min_lon, bbox_max_lat, bbox_max_lon),
  CONSTRAINT fk_track_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Waypoints ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS waypoints (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid        CHAR(36)     NOT NULL,
  user_id     INT UNSIGNED NOT NULL,
  name        VARCHAR(255) DEFAULT NULL,
  lat         DOUBLE       NOT NULL,
  lon         DOUBLE       NOT NULL,
  ele         DOUBLE       DEFAULT NULL,
  recorded_at DATETIME     NOT NULL,
  dept_code   VARCHAR(3)   DEFAULT NULL,
  commune     VARCHAR(120) DEFAULT NULL,
  notes       TEXT         DEFAULT NULL,
  created_at  DATETIME     NOT NULL DEFAULT UTC_TIMESTAMP(),
  updated_at  DATETIME     NOT NULL DEFAULT UTC_TIMESTAMP(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_uuid (uuid),
  KEY idx_user (user_id),
  KEY idx_dept (dept_code),
  KEY idx_recorded (recorded_at),
  KEY idx_pos (lat, lon),
  CONSTRAINT fk_wpt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Liaisons mots-clés (n..n) ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS track_keywords (
  track_id   INT UNSIGNED NOT NULL,
  keyword_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (track_id, keyword_id),
  CONSTRAINT fk_tk_track   FOREIGN KEY (track_id)   REFERENCES tracks(id)   ON DELETE CASCADE,
  CONSTRAINT fk_tk_keyword FOREIGN KEY (keyword_id) REFERENCES keywords(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS waypoint_keywords (
  waypoint_id INT UNSIGNED NOT NULL,
  keyword_id  INT UNSIGNED NOT NULL,
  PRIMARY KEY (waypoint_id, keyword_id),
  CONSTRAINT fk_wk_wpt     FOREIGN KEY (waypoint_id) REFERENCES waypoints(id) ON DELETE CASCADE,
  CONSTRAINT fk_wk_keyword FOREIGN KEY (keyword_id)  REFERENCES keywords(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ════════════════════════════════════════════════════════════════════════
--  === [MODULE PHOTO — à implémenter] ===
--  Table créée dès maintenant pour éviter une migration ultérieure.
--  Le jour de l'implémentation : recevoir le blob JPEG compressé (<500 Ko,
--  ~800×600, qualité 70%), le stocker dans /uploads/{user_id}/photos/{uuid}.jpg,
--  et insérer une ligne ici. Une photo peut être liée à un waypoint (optionnel)
--  ou autonome (juste géolocalisée). La file de sync client utilisera type 'photo'.
--  Voir aussi : api/photos.php (à créer) et le balisage [MODULE PHOTO] côté JS.
-- ════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS photos (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid        CHAR(36)     NOT NULL,
  user_id     INT UNSIGNED NOT NULL,
  waypoint_id INT UNSIGNED DEFAULT NULL,           -- lien optionnel vers un waypoint
  filename    VARCHAR(255) NOT NULL,               -- chemin relatif dans /uploads
  lat         DOUBLE       NOT NULL,
  lon         DOUBLE       NOT NULL,
  ele         DOUBLE       DEFAULT NULL,
  taken_at    DATETIME     NOT NULL,
  dept_code   VARCHAR(3)   DEFAULT NULL,
  commune     VARCHAR(120) DEFAULT NULL,
  notes       TEXT         DEFAULT NULL,
  created_at  DATETIME     NOT NULL DEFAULT UTC_TIMESTAMP(),
  updated_at  DATETIME     NOT NULL DEFAULT UTC_TIMESTAMP(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_uuid (uuid),
  KEY idx_user (user_id),
  KEY idx_wpt (waypoint_id),
  KEY idx_dept (dept_code),
  CONSTRAINT fk_photo_user FOREIGN KEY (user_id)     REFERENCES users(id)     ON DELETE CASCADE,
  CONSTRAINT fk_photo_wpt  FOREIGN KEY (waypoint_id) REFERENCES waypoints(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS photo_keywords (
  photo_id   INT UNSIGNED NOT NULL,
  keyword_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (photo_id, keyword_id),
  CONSTRAINT fk_pk_photo   FOREIGN KEY (photo_id)   REFERENCES photos(id)   ON DELETE CASCADE,
  CONSTRAINT fk_pk_keyword FOREIGN KEY (keyword_id) REFERENCES keywords(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Routes composées (pt 17-19 : tronçons assemblés en itinéraire à suivre) ─
CREATE TABLE IF NOT EXISTS routes (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid        CHAR(36)     NOT NULL,
  user_id     INT UNSIGNED NOT NULL,
  name        VARCHAR(255) NOT NULL,
  geojson     MEDIUMTEXT   NOT NULL,               -- LineString assemblée
  length_m    DOUBLE       NOT NULL DEFAULT 0,
  created_at  DATETIME     NOT NULL DEFAULT UTC_TIMESTAMP(),
  updated_at  DATETIME     NOT NULL DEFAULT UTC_TIMESTAMP(),
  PRIMARY KEY (id),
  UNIQUE KEY uq_uuid (uuid),
  KEY idx_user (user_id),
  CONSTRAINT fk_route_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
