-- =====================================================================
-- Plateforme de formation — Schéma métier MySQL 8 / MariaDB 10.6+
-- Encodage utf8mb4, moteur InnoDB.
-- Ce fichier est la RÉFÉRENCE métier. À l'amorçage, il sera traduit en
-- migrations Laravel (database/migrations/). Voir BOOTSTRAP.md.
--
-- NB : les tables techniques de Laravel (migrations, cache, jobs,
-- password_reset_tokens, sessions...) sont créées par le framework et
-- ne figurent pas ici.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- Comptes & rôles
-- ---------------------------------------------------------------------
CREATE TABLE users (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name            VARCHAR(150) NOT NULL,
  email           VARCHAR(190) NOT NULL,
  phone           VARCHAR(30) NULL,
  password        VARCHAR(255) NOT NULL,
  role            ENUM('admin','formateur','apprenant') NOT NULL DEFAULT 'apprenant',
  organisation    VARCHAR(190) NULL,          -- entreprise / administration
  email_verified_at TIMESTAMP NULL,
  created_at      TIMESTAMP NULL,
  updated_at      TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Catalogue
-- ---------------------------------------------------------------------
CREATE TABLE categories (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name        VARCHAR(150) NOT NULL,
  slug        VARCHAR(170) NOT NULL,
  description TEXT NULL,
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_categories_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE formations (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id   BIGINT UNSIGNED NULL,
  code          VARCHAR(20) NULL,             -- ex. IA-U1, IA-A2...
  title         VARCHAR(200) NOT NULL,
  slug          VARCHAR(220) NOT NULL,
  summary       VARCHAR(500) NULL,
  description   LONGTEXT NULL,                -- page de vente
  objectives    LONGTEXT NULL,
  prerequisites LONGTEXT NULL,
  level         ENUM('debutant','confirme','expert') NOT NULL DEFAULT 'debutant',
  duration_days DECIMAL(4,1) NULL,            -- durée indicative
  price         DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency      CHAR(3) NOT NULL DEFAULT 'XOF',
  cover_path    VARCHAR(255) NULL,
  is_published  TINYINT(1) NOT NULL DEFAULT 0,
  created_at    TIMESTAMP NULL,
  updated_at    TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_formations_slug (slug),
  KEY idx_formations_category (category_id),
  CONSTRAINT fk_formations_category FOREIGN KEY (category_id)
      REFERENCES categories (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Contenu pédagogique : modules -> leçons
CREATE TABLE formation_modules (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  formation_id  BIGINT UNSIGNED NOT NULL,
  title         VARCHAR(200) NOT NULL,
  position      INT NOT NULL DEFAULT 0,
  created_at    TIMESTAMP NULL,
  updated_at    TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY idx_modules_formation (formation_id),
  CONSTRAINT fk_modules_formation FOREIGN KEY (formation_id)
      REFERENCES formations (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lessons (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  module_id       BIGINT UNSIGNED NOT NULL,
  title           VARCHAR(200) NOT NULL,
  type            ENUM('video','texte','quiz','document','live') NOT NULL DEFAULT 'texte',
  content         LONGTEXT NULL,              -- texte/markdown ou JSON quiz
  media_url       VARCHAR(500) NULL,          -- URL vidéo/document (externalisé)
  duration_min    INT NULL,
  position        INT NOT NULL DEFAULT 0,
  is_free_preview TINYINT(1) NOT NULL DEFAULT 0,
  created_at      TIMESTAMP NULL,
  updated_at      TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY idx_lessons_module (module_id),
  CONSTRAINT fk_lessons_module FOREIGN KEY (module_id)
      REFERENCES formation_modules (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Sessions blended (promos datées)
-- ---------------------------------------------------------------------
CREATE TABLE sessions_formation (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  formation_id   BIGINT UNSIGNED NOT NULL,
  formateur_id   BIGINT UNSIGNED NULL,
  title          VARCHAR(200) NULL,          -- ex. "Promo Cotonou — juin 2026"
  mode           ENUM('presentiel','visio','blended','elearning') NOT NULL DEFAULT 'blended',
  start_date     DATE NULL,
  end_date       DATE NULL,
  location       VARCHAR(255) NULL,          -- adresse si présentiel
  meeting_url    VARCHAR(500) NULL,          -- lien visio si distanciel
  capacity       INT NULL,
  price_override DECIMAL(12,2) NULL,         -- prix spécifique à la session
  status         ENUM('planifiee','ouverte','complete','terminee','annulee') NOT NULL DEFAULT 'planifiee',
  created_at     TIMESTAMP NULL,
  updated_at     TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY idx_sessions_formation (formation_id),
  KEY idx_sessions_formateur (formateur_id),
  CONSTRAINT fk_sessions_formation FOREIGN KEY (formation_id)
      REFERENCES formations (id) ON DELETE CASCADE,
  CONSTRAINT fk_sessions_formateur FOREIGN KEY (formateur_id)
      REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Inscriptions & progression
-- ---------------------------------------------------------------------
CREATE TABLE enrollments (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       BIGINT UNSIGNED NOT NULL,
  formation_id  BIGINT UNSIGNED NOT NULL,
  session_id    BIGINT UNSIGNED NULL,        -- NULL = e-learning pur
  status        ENUM('active','suspendue','terminee','annulee') NOT NULL DEFAULT 'active',
  progress      TINYINT UNSIGNED NOT NULL DEFAULT 0,  -- 0..100
  enrolled_at   TIMESTAMP NULL,
  completed_at  TIMESTAMP NULL,
  created_at    TIMESTAMP NULL,
  updated_at    TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_enrollment (user_id, formation_id, session_id),
  KEY idx_enroll_formation (formation_id),
  KEY idx_enroll_session (session_id),
  CONSTRAINT fk_enroll_user FOREIGN KEY (user_id)
      REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_enroll_formation FOREIGN KEY (formation_id)
      REFERENCES formations (id) ON DELETE CASCADE,
  CONSTRAINT fk_enroll_session FOREIGN KEY (session_id)
      REFERENCES sessions_formation (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lesson_completions (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  enrollment_id BIGINT UNSIGNED NOT NULL,
  lesson_id     BIGINT UNSIGNED NOT NULL,
  completed_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_completion (enrollment_id, lesson_id),
  CONSTRAINT fk_completion_enroll FOREIGN KEY (enrollment_id)
      REFERENCES enrollments (id) ON DELETE CASCADE,
  CONSTRAINT fk_completion_lesson FOREIGN KEY (lesson_id)
      REFERENCES lessons (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Boutique & paiement (Mobile Money : FedaPay / KkiaPay)
-- ---------------------------------------------------------------------
CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NOT NULL,
  reference   VARCHAR(40) NOT NULL,
  total       DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency    CHAR(3) NOT NULL DEFAULT 'XOF',
  status      ENUM('pending','paid','failed','refunded','cancelled') NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMP NULL,
  updated_at  TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_orders_reference (reference),
  KEY idx_orders_user (user_id),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
      REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_items (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id     BIGINT UNSIGNED NOT NULL,
  formation_id BIGINT UNSIGNED NOT NULL,
  session_id   BIGINT UNSIGNED NULL,
  unit_price   DECIMAL(12,2) NOT NULL DEFAULT 0,
  quantity     INT NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  KEY idx_items_order (order_id),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id)
      REFERENCES orders (id) ON DELETE CASCADE,
  CONSTRAINT fk_items_formation FOREIGN KEY (formation_id)
      REFERENCES formations (id) ON DELETE RESTRICT,
  CONSTRAINT fk_items_session FOREIGN KEY (session_id)
      REFERENCES sessions_formation (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id               BIGINT UNSIGNED NOT NULL,
  provider               ENUM('fedapay','kkiapay') NOT NULL,
  provider_transaction_id VARCHAR(120) NULL,
  amount                 DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency               CHAR(3) NOT NULL DEFAULT 'XOF',
  status                 ENUM('pending','succeeded','failed','refunded') NOT NULL DEFAULT 'pending',
  raw_payload            JSON NULL,           -- réponse/webhook brut
  paid_at                TIMESTAMP NULL,
  created_at             TIMESTAMP NULL,
  updated_at             TIMESTAMP NULL,
  PRIMARY KEY (id),
  KEY idx_payments_order (order_id),
  KEY idx_payments_txn (provider, provider_transaction_id),
  CONSTRAINT fk_payments_order FOREIGN KEY (order_id)
      REFERENCES orders (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Attestations
-- ---------------------------------------------------------------------
CREATE TABLE certificates (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  enrollment_id BIGINT UNSIGNED NOT NULL,
  code          VARCHAR(40) NOT NULL,         -- code de vérification unique
  pdf_path      VARCHAR(255) NULL,
  issued_at     TIMESTAMP NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_certificates_code (code),
  KEY idx_certificates_enroll (enrollment_id),
  CONSTRAINT fk_certificates_enroll FOREIGN KEY (enrollment_id)
      REFERENCES enrollments (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
