-- ═══════════════════════════════════════════════════════════
--  APEFA – schema.sql  (VERSION CORRIGÉE NAMECHEAP)
--
--  ⚠️  IMPORTANT : NE PAS inclure CREATE DATABASE ni USE.
--      Sur Namecheap, la base est déjà créée via cPanel.
--      Importez CE fichier directement dans la base
--      cgwgxpdu_apefauser_db depuis phpMyAdmin.
--
--  Base cible  : cgwgxpdu_apefauser_db
--  Utilisateur : cgwgxpdu_admin1
-- ═══════════════════════════════════════════════════════════

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

-- ─── Table : demandes de devis ──────────────────────────────
CREATE TABLE IF NOT EXISTS `devis_requests` (
  `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `nom`            VARCHAR(120)  NOT NULL,
  `tel`            VARCHAR(30)   NOT NULL,
  `email`          VARCHAR(180)      NULL DEFAULT NULL,
  `localisation`   VARCHAR(220)  NOT NULL,
  `service`        VARCHAR(120)  NOT NULL,
  `superficie`     DECIMAL(10,2)     NULL DEFAULT NULL,
  `message`        TEXT              NULL DEFAULT NULL,
  `statut`         ENUM('nouveau','en_cours','traite','annule') NOT NULL DEFAULT 'nouveau',
  `notes_internes` TEXT              NULL DEFAULT NULL,
  `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_statut`     (`statut`),
  INDEX `idx_service`    (`service`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Table : messages de contact ────────────────────────────
CREATE TABLE IF NOT EXISTS `contacts` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom`        VARCHAR(120) NOT NULL,
  `email`      VARCHAR(180) NOT NULL,
  `sujet`      VARCHAR(220)     NULL DEFAULT NULL,
  `message`    TEXT         NOT NULL,
  `lu`         TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_lu` (`lu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Table : newsletter ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS `newsletter_subscribers` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email`         VARCHAR(180) NOT NULL,
  `statut`        ENUM('actif','desabonne') NOT NULL DEFAULT 'actif',
  `subscribed_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Table : journal activité ───────────────────────────────
CREATE TABLE IF NOT EXISTS `activity_log` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type`       VARCHAR(60)  NOT NULL,
  `ref_id`     INT UNSIGNED     NULL DEFAULT NULL,
  `ip`         VARCHAR(45)      NULL DEFAULT NULL,
  `user_agent` VARCHAR(300)     NULL DEFAULT NULL,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ─── Donnée de test ─────────────────────────────────────────
INSERT INTO `devis_requests` (`nom`,`tel`,`email`,`localisation`,`service`,`superficie`,`message`,`statut`)
VALUES ('Client Test','+237695000000','test@apefa.com','Yaoundé, Centre','Défrichage',3.0,'Import réussi.','nouveau');