-- ─────────────────────────────────────────────
-- Schéma de la base chipoutsbox (MySQL / o2switch)
-- ─────────────────────────────────────────────

SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET NAMES utf8mb4;

-- ── Table : artisan_registrations ──────────────
-- Fiches artisans inscrites depuis le formulaire.
CREATE TABLE IF NOT EXISTS `artisan_registrations` (
  `id`              INT AUTO_INCREMENT PRIMARY KEY,
  `slug`            VARCHAR(255) NOT NULL,
  `company_name`    VARCHAR(255) DEFAULT NULL,
  `first_name`      VARCHAR(255) DEFAULT NULL,
  `last_name`       VARCHAR(255) DEFAULT NULL,
  `email`           VARCHAR(255) DEFAULT NULL,
  `phone`           VARCHAR(50)  DEFAULT NULL,
  `trade`           VARCHAR(255) DEFAULT NULL,
  `trade_slug`      VARCHAR(255) DEFAULT NULL,
  `city`            VARCHAR(255) DEFAULT NULL,
  `intervention_cities` TEXT DEFAULT NULL,
  `bio`             TEXT DEFAULT NULL,
  `website`         VARCHAR(255) DEFAULT NULL,
  `logo_url`        VARCHAR(255) DEFAULT NULL,
  `photo_url`       VARCHAR(255) DEFAULT NULL,
  `gallery_urls`    TEXT DEFAULT NULL,
  `certifications`  TEXT DEFAULT NULL,
  `insurance`       VARCHAR(255) DEFAULT NULL,
  `is_premium`      TINYINT(1) NOT NULL DEFAULT 0,
  `is_verified`     TINYINT(1) NOT NULL DEFAULT 0,
  `is_published`    TINYINT(1) NOT NULL DEFAULT 0,
  `draft_token`     VARCHAR(64)  DEFAULT NULL,
  `published_at`    DATETIME DEFAULT NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_slug` (`slug`),
  KEY `idx_email` (`email`),
  KEY `idx_trade` (`trade_slug`),
  KEY `idx_city` (`city`),
  KEY `idx_published` (`is_published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Table : projects ──────────────────────────
-- Projets / demandes de devis déposés par les clients.
CREATE TABLE IF NOT EXISTS `projects` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `reference`     VARCHAR(64)  NOT NULL,
  `client_name`   VARCHAR(255) DEFAULT NULL,
  `client_email`  VARCHAR(255) DEFAULT NULL,
  `client_phone`  VARCHAR(50)  DEFAULT NULL,
  `city`          VARCHAR(255) DEFAULT NULL,
  `trade`         VARCHAR(255) DEFAULT NULL,
  `trade_slug`    VARCHAR(255) DEFAULT NULL,
  `sub_tasks`     TEXT DEFAULT NULL,
  `surface`       VARCHAR(50)  DEFAULT NULL,
  `budget`        VARCHAR(50)  DEFAULT NULL,
  `description`   TEXT DEFAULT NULL,
  `status`        VARCHAR(50)  NOT NULL DEFAULT 'pending',
  `assigned_artisan_id` INT DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_reference` (`reference`),
  KEY `idx_trade` (`trade_slug`),
  KEY `idx_city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Table : reviews ───────────────────────────
CREATE TABLE IF NOT EXISTS `reviews` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `artisan_id`   INT DEFAULT NULL,
  `client_name`   VARCHAR(255) DEFAULT NULL,
  `rating`        TINYINT NOT NULL DEFAULT 5,
  `comment`       TEXT DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_artisan` (`artisan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Table : subscriptions ─────────────────────
-- Abonnements Premium des artisans.
CREATE TABLE IF NOT EXISTS `subscriptions` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `artisan_id`    INT DEFAULT NULL,
  `email`         VARCHAR(255) DEFAULT NULL,
  `plan`          VARCHAR(50)  NOT NULL DEFAULT 'premium',
  `status`        VARCHAR(50)  NOT NULL DEFAULT 'active',
  `started_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at`    DATETIME DEFAULT NULL,
  KEY `idx_artisan` (`artisan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Table : visits ────────────────────────────
-- Visites / rendez-vous de chantier.
CREATE TABLE IF NOT EXISTS `visits` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `artisan_id`    INT DEFAULT NULL,
  `client_name`   VARCHAR(255) DEFAULT NULL,
  `client_email`  VARCHAR(255) DEFAULT NULL,
  `client_phone`  VARCHAR(50)  DEFAULT NULL,
  `city`          VARCHAR(255) DEFAULT NULL,
  `scheduled_at`  DATETIME DEFAULT NULL,
  `status`        VARCHAR(50)  NOT NULL DEFAULT 'pending',
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_artisan` (`artisan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Table : support_tickets ──────────────────
CREATE TABLE IF NOT EXISTS `support_tickets` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(255) DEFAULT NULL,
  `email`         VARCHAR(255) DEFAULT NULL,
  `subject`       VARCHAR(255) DEFAULT NULL,
  `message`       TEXT DEFAULT NULL,
  `status`        VARCHAR(50)  NOT NULL DEFAULT 'open',
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Table : price_overrides ───────────────────
-- Overrides de tarifs depuis la page /admin-prix.
CREATE TABLE IF NOT EXISTS `price_overrides` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `guide_slug`    VARCHAR(255) NOT NULL,
  `item_key`      VARCHAR(255) NOT NULL,
  `price`         VARCHAR(50)  DEFAULT NULL,
  `unit`          VARCHAR(50)  DEFAULT NULL,
  `note`          TEXT DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_override` (`guide_slug`, `item_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;