-- =============================================================================
-- MissioFlow — Schema de base de donnees
-- =============================================================================
--
-- Source de verite pour la structure de la base de donnees.
-- Regenere apres chaque migration reussie depuis la DB demo.
--
-- Derniere mise a jour : 2026-04-12 (chantier 3 — Stripe + signup + onboarding)
-- Version MySQL : 8.0
--
-- =============================================================================



--
-- Temporary view structure for view `active_alerts_with_user`
--

/*!50001 DROP VIEW IF EXISTS `active_alerts_with_user`*/;
/*!50001 CREATE VIEW `active_alerts_with_user` AS SELECT 
 1 AS `id`,
 1 AS `type`,
 1 AS `severity`,
 1 AS `source_ip`,
 1 AS `user_id`,
 1 AS `event_count`,
 1 AS `first_occurrence`,
 1 AS `last_occurrence`,
 1 AS `details`,
 1 AS `status`,
 1 AS `created_at`,
 1 AS `resolved_at`,
 1 AS `resolved_by`,
 1 AS `technicien_nom`,
 1 AS `technicien_email`,
 1 AS `technicien_role`*/;

--
-- Table structure for table `activities_log`
--

CREATE TABLE `activities_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `type` varchar(50) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text,
  `user_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_activities_log_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `alert_thresholds`
--

CREATE TABLE `alert_thresholds` (
  `id` int NOT NULL AUTO_INCREMENT,
  `metric_name` varchar(100) NOT NULL,
  `threshold_value` int NOT NULL,
  `time_window_minutes` int NOT NULL,
  `alert_type` varchar(50) NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `metric_name` (`metric_name`),
  KEY `idx_metric_active` (`metric_name`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `api_metrics`
--

CREATE TABLE `api_metrics` (
  `id` int NOT NULL AUTO_INCREMENT,
  `endpoint` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `method` enum('GET','POST','PUT','DELETE','PATCH') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `source_ip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_status` int DEFAULT NULL,
  `response_time_ms` int DEFAULT NULL,
  `request_size_bytes` int DEFAULT '0',
  `response_size_bytes` int DEFAULT '0',
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  `is_suspicious` tinyint(1) DEFAULT '0',
  `rate_limit_exceeded` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_endpoint_timestamp` (`endpoint`,`timestamp`),
  KEY `idx_ip_timestamp` (`source_ip`,`timestamp`),
  KEY `idx_suspicious` (`is_suspicious`),
  KEY `idx_rate_limit` (`rate_limit_exceeded`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `app_config`
--

CREATE TABLE `app_config` (
  `cle` varchar(100) NOT NULL,
  `valeur` text NOT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`cle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `banned_ips`
--

CREATE TABLE `banned_ips` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL COMMENT 'Adresse IP (IPv4 ou IPv6)',
  `reason` text COMMENT 'Raison du bannissement',
  `banned_by` int NOT NULL COMMENT 'Admin qui a banni',
  `ban_type` enum('temporary','permanent') DEFAULT 'temporary',
  `expires_at` timestamp NULL DEFAULT NULL COMMENT 'Date d''expiration pour les bans temporaires',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_address` (`ip_address`),
  KEY `banned_by` (`banned_by`),
  KEY `idx_banned_ips_ip` (`ip_address`),
  KEY `idx_banned_ips_expires` (`expires_at`),
  CONSTRAINT `banned_ips_ibfk_1` FOREIGN KEY (`banned_by`) REFERENCES `techniciens` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `client_activity_log`
--

CREATE TABLE `client_activity_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_user_id` int NOT NULL,
  `action_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'login, view_intervention, download_report, etc.',
  `details` json DEFAULT NULL COMMENT 'Détails de l''action',
  `ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_client_timestamp` (`client_user_id`,`timestamp`),
  KEY `idx_action` (`action_type`),
  CONSTRAINT `client_activity_log_ibfk_1` FOREIGN KEY (`client_user_id`) REFERENCES `clients_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Logs d''activité des clients du portail';

--
-- Table structure for table `clients_portail`
--

CREATE TABLE `clients_portail` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Email du client (identifiant unique)',
  `nom` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nom de famille',
  `prenom` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Prénom',
  `password_hash` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Hash du mot de passe (NULL si pas encore créé)',
  `actif` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Compte actif (1) ou désactivé (0)',
  `invitation_envoyee` tinyint(1) DEFAULT '0' COMMENT 'Email d''invitation envoyé',
  `date_invitation` datetime DEFAULT NULL COMMENT 'Date d''envoi de l''invitation',
  `date_activation` datetime DEFAULT NULL COMMENT 'Date de création du mot de passe',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_login` datetime DEFAULT NULL COMMENT 'Dernière connexion',
  `failed_login_attempts` int DEFAULT '0' COMMENT 'Tentatives échouées (sécurité)',
  `locked_until` datetime DEFAULT NULL COMMENT 'Verrouillage temporaire si trop de tentatives',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_email` (`email`),
  KEY `idx_actif` (`actif`),
  KEY `idx_nom_prenom` (`nom`,`prenom`),
  KEY `idx_clients_portail_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Clients du portail - accès aux données de leurs sites';

--
-- Table structure for table `clients_portail_activity_log`
--

CREATE TABLE `clients_portail_activity_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_portail_id` int NOT NULL,
  `action_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'login, view_intervention, download_report, password_created, etc.',
  `site_id` int DEFAULT NULL COMMENT 'Site concerné par l''action',
  `details` json DEFAULT NULL COMMENT 'Détails de l''action',
  `ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_client_timestamp` (`client_portail_id`,`timestamp`),
  KEY `idx_action` (`action_type`),
  KEY `idx_site` (`site_id`),
  CONSTRAINT `clients_portail_activity_log_ibfk_1` FOREIGN KEY (`client_portail_id`) REFERENCES `clients_portail` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clients_portail_activity_log_ibfk_2` FOREIGN KEY (`site_id`) REFERENCES `sites_clients` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Logs d''activité des clients du portail';

--
-- Table structure for table `clients_portail_invitation_tokens`
--

CREATE TABLE `clients_portail_invitation_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_portail_id` int NOT NULL COMMENT 'ID du client invité',
  `token` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Token unique pour créer le mot de passe',
  `expires_at` datetime NOT NULL COMMENT 'Date d''expiration du token (72h)',
  `used` tinyint(1) DEFAULT '0' COMMENT 'Token utilisé (1) ou pas encore (0)',
  `used_at` datetime DEFAULT NULL COMMENT 'Date d''utilisation du token',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `idx_token` (`token`),
  KEY `idx_expires` (`expires_at`),
  KEY `idx_client` (`client_portail_id`),
  CONSTRAINT `clients_portail_invitation_tokens_ibfk_1` FOREIGN KEY (`client_portail_id`) REFERENCES `clients_portail` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tokens d''invitation pour créer le mot de passe client';

--
-- Table structure for table `clients_portail_sites_access`
--

CREATE TABLE `clients_portail_sites_access` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_portail_id` int NOT NULL COMMENT 'ID du client portail',
  `site_id` int NOT NULL COMMENT 'ID du site accessible',
  `niveau_acces` enum('lecture','complet') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'lecture' COMMENT 'lecture=voir données, complet=voir+commenter',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL COMMENT 'Admin qui a créé l''accès',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_client_site` (`client_portail_id`,`site_id`),
  KEY `idx_client` (`client_portail_id`),
  KEY `idx_site` (`site_id`),
  CONSTRAINT `clients_portail_sites_access_ibfk_1` FOREIGN KEY (`client_portail_id`) REFERENCES `clients_portail` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clients_portail_sites_access_ibfk_2` FOREIGN KEY (`site_id`) REFERENCES `sites_clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Droits d''accès clients portail vers sites';

--
-- Table structure for table `clients_sites_access`
--

CREATE TABLE `clients_sites_access` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_user_id` int NOT NULL COMMENT 'ID du compte client',
  `site_id` int NOT NULL COMMENT 'ID du site accessible',
  `niveau_acces` enum('lecture','complet') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'lecture' COMMENT 'lecture=voir rapports, complet=voir+commenter',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL COMMENT 'Admin qui a créé l''accès',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_access` (`client_user_id`,`site_id`),
  KEY `idx_client` (`client_user_id`),
  KEY `idx_site` (`site_id`),
  CONSTRAINT `clients_sites_access_ibfk_1` FOREIGN KEY (`client_user_id`) REFERENCES `clients_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `clients_sites_access_ibfk_2` FOREIGN KEY (`site_id`) REFERENCES `sites_clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Droits d''accès clients vers sites';

--
-- Table structure for table `clients_users`
--

CREATE TABLE `clients_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Email de connexion unique',
  `password_hash` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Hash bcrypt du mot de passe',
  `nom_entreprise` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nom de l''entreprise cliente',
  `nom_contact` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nom du contact principal',
  `prenom_contact` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Prénom du contact principal',
  `telephone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Téléphone de contact',
  `actif` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Compte actif (1) ou désactivé (0)',
  `notes_admin` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Notes internes admin',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_login` datetime DEFAULT NULL COMMENT 'Dernière connexion',
  `failed_login_attempts` int DEFAULT '0' COMMENT 'Tentatives échouées (sécurité)',
  `locked_until` datetime DEFAULT NULL COMMENT 'Verrouillage temporaire si trop de tentatives',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_email` (`email`),
  KEY `idx_actif` (`actif`),
  KEY `idx_entreprise` (`nom_entreprise`),
  KEY `idx_clients_users_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Comptes de connexion pour les clients du portail';

--
-- Table structure for table `coefficients_gaz`
--

CREATE TABLE `coefficients_gaz` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nom_gaz` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `coefficient` decimal(10,4) NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `company_info`
--

CREATE TABLE `company_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `name` varchar(100) NOT NULL,
  `app_name` varchar(100) NOT NULL DEFAULT 'MissioFlow',
  `address` varchar(255) NOT NULL,
  `city` varchar(100) NOT NULL,
  `postal_code` varchar(10) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `website` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `siret` varchar(50) NOT NULL,
  `naf_ape` varchar(20) NOT NULL,
  `tva_number` varchar(50) NOT NULL,
  `legal_form` varchar(100) NOT NULL,
  `logo_path` varchar(255) DEFAULT NULL,
  `color_primary` varchar(7) NOT NULL DEFAULT '#1252A6',
  `color_secondary` varchar(7) NOT NULL DEFAULT '#14488C',
  `color_accent` varchar(7) NOT NULL DEFAULT '#04BFBF',
  `color_accent_alt` varchar(7) NOT NULL DEFAULT '#04BFAD',
  `favicon_path` varchar(255) DEFAULT NULL,
  `email_from_name` varchar(100) DEFAULT NULL,
  `email_from_address` varchar(100) DEFAULT NULL,
  `meta_theme_color` varchar(7) DEFAULT '#1252A6',
  `footer_text` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_company_info_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `daily_reports`
--

CREATE TABLE `daily_reports` (
  `id` int NOT NULL AUTO_INCREMENT,
  `report_date` date NOT NULL,
  `total_users_active` int DEFAULT '0',
  `total_api_requests` int DEFAULT '0',
  `total_errors` int DEFAULT '0',
  `total_security_alerts` int DEFAULT '0',
  `top_user_activity` json DEFAULT NULL,
  `top_errors` json DEFAULT NULL,
  `performance_metrics` json DEFAULT NULL,
  `security_summary` json DEFAULT NULL,
  `generated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `report_date` (`report_date`),
  KEY `idx_report_date` (`report_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Temporary view structure for view `daily_user_stats`
--

/*!50001 DROP VIEW IF EXISTS `daily_user_stats`*/;
/*!50001 CREATE VIEW `daily_user_stats` AS SELECT 
 1 AS `activity_date`,
 1 AS `user_id`,
 1 AS `user_name`,
 1 AS `user_role`,
 1 AS `total_actions`,
 1 AS `suspicious_actions`,
 1 AS `dangerous_actions`*/;

--
-- Table structure for table `demandes_devis`
--

CREATE TABLE `demandes_devis` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `intervention_source_id` int NOT NULL COMMENT 'Intervention qui a genere la demande (panne detectee dans son workflow)',
  `site_id` int NOT NULL,
  `machine_id` int NOT NULL,
  `description_probleme` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Problemes agreges depuis les etapes workflow en panne/probleme',
  `statut` enum('a_traiter','en_cours','traitee','annulee') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'a_traiter',
  `devis_id` int DEFAULT NULL COMMENT 'Devis cree par l admin en reponse (NULL tant que non traitee)',
  `intervention_creee_id` int DEFAULT NULL COMMENT 'Nouvelle intervention de reparation creee depuis le devis',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `traitee_at` datetime DEFAULT NULL,
  `traitee_by` int DEFAULT NULL COMMENT 'user_id admin ayant traite la demande',
  PRIMARY KEY (`id`),
  KEY `idx_demandes_devis_statut` (`statut`),
  KEY `idx_demandes_devis_site` (`site_id`),
  KEY `idx_demandes_devis_machine` (`machine_id`),
  KEY `idx_demandes_devis_source` (`intervention_source_id`),
  KEY `fk_demande_devis_devis` (`devis_id`),
  KEY `fk_demande_devis_intervention_creee` (`intervention_creee_id`),
  KEY `idx_demandes_devis_tenant` (`tenant_id`),
  CONSTRAINT `fk_demande_devis_devis` FOREIGN KEY (`devis_id`) REFERENCES `devis` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_demande_devis_intervention_creee` FOREIGN KEY (`intervention_creee_id`) REFERENCES `interventions` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_demande_devis_intervention_source` FOREIGN KEY (`intervention_source_id`) REFERENCES `interventions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_demande_devis_machine` FOREIGN KEY (`machine_id`) REFERENCES `machines` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_demande_devis_site` FOREIGN KEY (`site_id`) REFERENCES `sites_clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `devis`
--

CREATE TABLE `devis` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `numero_devis` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `site_id` int DEFAULT NULL,
  `machine_id` int DEFAULT NULL,
  `date_devis` date DEFAULT NULL,
  `montant` decimal(10,2) DEFAULT NULL,
  `statut` enum('en_attente','accepte','refuse','termine') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'en_attente',
  `facture_id` int DEFAULT NULL,
  `fichier_pdf_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fichiers_pdf` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_devis_machine` (`machine_id`),
  KEY `idx_devis_site` (`site_id`),
  KEY `idx_devis_facture_id` (`facture_id`),
  KEY `idx_devis_tenant` (`tenant_id`),
  CONSTRAINT `devis_ibfk_facture` FOREIGN KEY (`facture_id`) REFERENCES `factures` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `facture_relances`
--

CREATE TABLE `facture_relances` (
  `id` int NOT NULL AUTO_INCREMENT,
  `facture_id` int NOT NULL,
  `type_relance` enum('email','telephone','courrier') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'email',
  `destinataire_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sujet` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contenu` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `resultat` enum('envoyee','echec') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'envoyee',
  `erreur_detail` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_relances_facture_id` (`facture_id`),
  KEY `idx_relances_created_at` (`created_at`),
  CONSTRAINT `facture_relances_ibfk_facture` FOREIGN KEY (`facture_id`) REFERENCES `factures` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `facture_envois`
--

CREATE TABLE `facture_envois` (
  `id` int NOT NULL AUTO_INCREMENT,
  `facture_id` int NOT NULL,
  `destinataire_email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'mainteneur | contact_2..6 | archive',
  `sujet` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `resultat` enum('envoyee','echec') COLLATE utf8mb4_unicode_ci NOT NULL,
  `erreur_detail` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_facture_envois_facture` (`facture_id`),
  KEY `idx_facture_envois_created` (`created_at`),
  CONSTRAINT `fk_facture_envois_facture` FOREIGN KEY (`facture_id`) REFERENCES `factures` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `factures`
--

CREATE TABLE `factures` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `numero_facture` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `intervention_id` int NOT NULL,
  `rapport_id` int DEFAULT NULL,
  `site_id` int DEFAULT NULL,
  `montant_ht` decimal(10,2) NOT NULL DEFAULT '0.00',
  `tva` decimal(5,2) NOT NULL DEFAULT '20.00',
  `montant_ttc` decimal(10,2) NOT NULL DEFAULT '0.00',
  `statut` enum('brouillon','envoyee','payee','en_retard','annulee') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'brouillon',
  `date_emission` date DEFAULT NULL,
  `date_echeance` date DEFAULT NULL,
  `date_paiement` date DEFAULT NULL,
  `mode_paiement` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_paiement` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `fichier_pdf_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fichiers_pdf` json DEFAULT NULL,
  `relance_count` int NOT NULL DEFAULT '0',
  `derniere_relance_at` datetime DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_numero_facture` (`numero_facture`),
  KEY `idx_factures_intervention_id` (`intervention_id`),
  KEY `idx_factures_rapport_id` (`rapport_id`),
  KEY `idx_factures_site_id` (`site_id`),
  KEY `idx_factures_statut` (`statut`),
  KEY `idx_factures_date_echeance` (`date_echeance`),
  KEY `idx_factures_date_emission` (`date_emission`),
  KEY `factures_ibfk_created_by` (`created_by`),
  KEY `idx_factures_tenant` (`tenant_id`),
  CONSTRAINT `factures_ibfk_created_by` FOREIGN KEY (`created_by`) REFERENCES `techniciens` (`id`) ON DELETE SET NULL,
  CONSTRAINT `factures_ibfk_intervention` FOREIGN KEY (`intervention_id`) REFERENCES `interventions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `factures_ibfk_rapport` FOREIGN KEY (`rapport_id`) REFERENCES `rapports` (`id`) ON DELETE SET NULL,
  CONSTRAINT `factures_ibfk_site` FOREIGN KEY (`site_id`) REFERENCES `sites_clients` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `intervention_photos`
--

CREATE TABLE `intervention_photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `intervention_id` int NOT NULL,
  `filename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `original_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_size` int NOT NULL,
  `mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uploaded_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_intervention_id` (`intervention_id`),
  KEY `idx_uploaded_at` (`uploaded_at`),
  KEY `idx_intervention_photos_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `intervention_workflow_step_files`
--

CREATE TABLE `intervention_workflow_step_files` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `workflow_step_id` bigint unsigned NOT NULL,
  `stored_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `original_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_size` int DEFAULT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `deleted_at` datetime DEFAULT NULL,
  `uploaded_by` int DEFAULT NULL,
  `uploaded_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_workflow_step_files_lookup` (`workflow_step_id`,`is_deleted`,`uploaded_at`),
  CONSTRAINT `fk_workflow_step_files_step` FOREIGN KEY (`workflow_step_id`) REFERENCES `intervention_workflow_steps` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `intervention_workflow_steps`
--

CREATE TABLE `intervention_workflow_steps` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `workflow_id` bigint unsigned NOT NULL,
  `step_key` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `step_sort_order` int unsigned NOT NULL,
  `step_row_number` int unsigned DEFAULT NULL,
  `template_step_id_source` int unsigned DEFAULT NULL,
  `phase` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action_label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `item` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `input_kind` enum('section','display','prerequisite','text','number','choice','boolean','photo','info') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT '0',
  `is_extension` tinyint(1) NOT NULL DEFAULT '0',
  `requires_comment` tinyint(1) NOT NULL DEFAULT '0',
  `comment_rule` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rules_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `options_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_visible` tinyint(1) NOT NULL DEFAULT '1',
  `is_completed` tinyint(1) NOT NULL DEFAULT '0',
  `value_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `comment_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `validation_errors_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `completed_by` int DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `last_saved_at` datetime 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`),
  UNIQUE KEY `uq_workflow_step_key` (`workflow_id`,`step_key`),
  UNIQUE KEY `uq_workflow_step_sort` (`workflow_id`,`step_sort_order`),
  KEY `idx_workflow_steps_completed` (`workflow_id`,`is_completed`),
  CONSTRAINT `fk_workflow_steps_workflow` FOREIGN KEY (`workflow_id`) REFERENCES `intervention_workflows` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `intervention_workflows`
--

CREATE TABLE `intervention_workflows` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `intervention_id` int NOT NULL,
  `template_id_source` int unsigned DEFAULT NULL,
  `template_name_snapshot` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `template_machine_type_value_snapshot` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `template_version_snapshot` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `template_snapshot_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `selection_source` enum('manual_override_id','manual_override_type','intervention_rule','machine_type','fallback') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('not_started','in_progress','completed','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'not_started',
  `current_step_key` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `started_by` int DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `version` int unsigned NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_workflow_intervention` (`intervention_id`),
  KEY `idx_workflow_status_step` (`status`,`current_step_key`),
  KEY `idx_workflow_template_source` (`template_id_source`),
  KEY `idx_intervention_workflows_tenant` (`tenant_id`),
  CONSTRAINT `fk_workflow_intervention` FOREIGN KEY (`intervention_id`) REFERENCES `interventions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_workflow_template_source` FOREIGN KEY (`template_id_source`) REFERENCES `report_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `interventions`
--

CREATE TABLE `interventions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `numero_r` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `site_id` int NOT NULL,
  `machine_id` int NOT NULL,
  `devis_id` int DEFAULT NULL,
  `technicien_id` int DEFAULT NULL,
  `type_intervention` enum('diagnostic','travaux','maintenance','reparation','visite_technique','maintenance_preventive','maintenance_corrective','depannage_urgence','controle_reglementaire','mise_en_service','arret_definitif','planification_controle_fuite') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'maintenance',
  `priorite` enum('basse','normale','haute','urgente') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'normale',
  `statut` enum('planifiee','en_cours','terminee','reportee','annulee') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'planifiee',
  `date_prevue` datetime NOT NULL,
  `date_debut` datetime DEFAULT NULL,
  `date_fin` datetime DEFAULT NULL,
  `duree_estimee_minutes` int DEFAULT '120',
  `duree_reelle_minutes` int DEFAULT NULL,
  `titre` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `observations_technicien` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `probleme_detecte` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `actions_effectuees` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `pieces_changees` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Pièces changées - texte libre',
  `temperature_avant` decimal(5,2) DEFAULT NULL,
  `temperature_apres` decimal(5,2) DEFAULT NULL,
  `pression_avant` decimal(5,2) DEFAULT NULL,
  `pression_apres` decimal(5,2) DEFAULT NULL,
  `charge_fluide_kg` decimal(6,3) DEFAULT NULL,
  `fuite_detectee` tinyint(1) DEFAULT '0',
  `quantite_fuite_kg` decimal(6,3) DEFAULT NULL,
  `conforme_reglementation` tinyint(1) DEFAULT NULL,
  `certificat_genere` tinyint(1) DEFAULT '0',
  `signature_client` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `signature_technicien` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `photos` json DEFAULT NULL,
  `documents` json DEFAULT NULL,
  `cout_main_oeuvre` decimal(8,2) DEFAULT NULL,
  `cout_pieces` decimal(8,2) DEFAULT NULL,
  `cout_total` decimal(8,2) DEFAULT NULL,
  `facture_generee` tinyint(1) DEFAULT '0',
  `numero_facture` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `fluide_utilise` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Type de fluide utilisé (R410A, R32, etc.)',
  `quantite_fluide` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Quantité de fluide (ex: 15.50 kg)',
  `coefficient_gaz` decimal(8,4) DEFAULT NULL COMMENT 'Coefficient du gaz',
  `teq_co2` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Équivalent CO2 (ex: 32.36 t)',
  `visites_annuelles` int DEFAULT NULL COMMENT 'Nombre de visites annuelles prévues',
  `temperature_evaporation` decimal(5,2) DEFAULT NULL COMMENT 'Température d''évaporation',
  `temperature_condensation` decimal(5,2) DEFAULT NULL COMMENT 'Température de condensation',
  `recommandations` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Recommandations du technicien',
  `report_type_override` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `report_template_id_override` int unsigned DEFAULT NULL,
  `report_selection_source` enum('override_id','override_type','rule','machine','fallback') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `report_template_id_selected` int unsigned DEFAULT NULL,
  `workflow_status` enum('not_started','in_progress','completed','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'not_started',
  `missions_fusionnees` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_interventions_site_date` (`site_id`,`date_prevue`),
  KEY `idx_interventions_statut` (`statut`),
  KEY `idx_interventions_numero_r` (`numero_r`),
  KEY `idx_interventions_devis_id` (`devis_id`),
  KEY `idx_interventions_workflow_status` (`workflow_status`),
  KEY `idx_interventions_report_template_override` (`report_template_id_override`),
  KEY `idx_interventions_report_template_selected` (`report_template_id_selected`),
  KEY `idx_interventions_tenant` (`tenant_id`),
  CONSTRAINT `fk_interventions_devis` FOREIGN KEY (`devis_id`) REFERENCES `devis` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_interventions_report_template_override` FOREIGN KEY (`report_template_id_override`) REFERENCES `report_templates` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_interventions_report_template_selected` FOREIGN KEY (`report_template_id_selected`) REFERENCES `report_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `ip_blacklist`
--

CREATE TABLE `ip_blacklist` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `blocked_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `blocked_until` datetime DEFAULT NULL,
  `is_permanent` tinyint(1) DEFAULT '0',
  `auto_generated` tinyint(1) DEFAULT '1',
  `created_by` int DEFAULT NULL,
  `created_by_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_count` int DEFAULT '0',
  `last_attempt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_address` (`ip_address`),
  KEY `idx_ip_active` (`ip_address`,`blocked_until`),
  KEY `idx_auto_generated` (`auto_generated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `machines`
--

CREATE TABLE `machines` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `site_id` int NOT NULL,
  `nom_machine` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `marque` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `modele` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `numero_serie` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `annee` int DEFAULT NULL,
  `gaz` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quantite_gaz` decimal(10,2) DEFAULT NULL,
  `coefficient_gaz` decimal(10,4) DEFAULT NULL,
  `detecteur_fuite_present` tinyint(1) DEFAULT '0' COMMENT 'Détecteur de fuite présent (0=non, 1=oui)',
  `type_condensation_air` tinyint(1) DEFAULT '0' COMMENT 'Condensation par air',
  `type_condensation_eau` tinyint(1) DEFAULT '0' COMMENT 'Condensation par eau',
  `type_condensation_deporte` tinyint(1) DEFAULT '0' COMMENT 'Condenseur air déporté',
  `teq_co2` decimal(10,2) GENERATED ALWAYS AS ((`quantite_gaz` * `coefficient_gaz`)) STORED,
  `visites_obligatoires_annee` int GENERATED ALWAYS AS ((case when ((`quantite_gaz` * `coefficient_gaz`) < 5) then 1 when ((`quantite_gaz` * `coefficient_gaz`) < 50) then 2 when ((`quantite_gaz` * `coefficient_gaz`) < 500) then 4 else 12 end)) STORED,
  `frequence_controle_cerfa` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Fréquence contrôle établissement cerfa (calculée auto)',
  `frequence_controle_contrat` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Fréquence contrôle vendu au contrat',
  `date_derniere_maintenance` date DEFAULT NULL COMMENT 'Date de la dernière maintenance connue',
  `date_premiere_intervention_annuelle` date DEFAULT NULL COMMENT 'Date de la premiere intervention annuelle - SOURCE UNIQUE du calcul planification. Si NULL, aucune intervention auto generee.',
  `date_prochaine_maintenance` date DEFAULT NULL COMMENT 'Date prochaine maintenance (planning)',
  `type_rapport_affecte` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Type de rapport PDF à générer',
  `prelevement_huile_requis` tinyint(1) DEFAULT '0' COMMENT 'Prélèvement huile requis (si non: acide test obligatoire 1 fois/an)',
  `prelevement_huile_frequence` int DEFAULT '1',
  `analyse_huile_requise` tinyint(1) DEFAULT '0' COMMENT 'Analyse d''huile requise',
  `analyse_huile_frequence` int DEFAULT '1' COMMENT 'Fréquence analyse d''huile (années)',
  `test_acide_requis` tinyint(1) DEFAULT '0' COMMENT 'Test acide requis',
  `test_acide_frequence` int DEFAULT '1' COMMENT 'Fréquence test acide (années)',
  `nettoyage_condenseur_entreprise` tinyint(1) DEFAULT '0' COMMENT 'Nettoyage condenseurs Ã  charge de l''entreprise',
  `analyse_vibratoire_requise` tinyint(1) DEFAULT '0' COMMENT 'Analyse vibratoire requise',
  `machine_sous_astreinte` tinyint(1) DEFAULT '0' COMMENT 'Machine sous astreinte (0=non, 1=oui)',
  `sous_contrat_site` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Machine couverte par le contrat de maintenance du site (0=non, 1=oui)',
  `cerfa_obligatoire` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'CERFA obligatoire a chaque visite sur cette machine (0=non, 1=oui)',
  `machine_sous_contrat` tinyint(1) DEFAULT '0' COMMENT 'Maintenance supplementaire prevue au contrat (0=non, 1=oui)',
  `garantie_etendue` tinyint(1) DEFAULT '0' COMMENT 'Machine avec garantie étendue (0=non, 1=oui)',
  `localisation` enum('toiture','sous_sol','niveau_0','autre') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Localisation de la machine',
  `moyen_acces_ascenseur` tinyint(1) DEFAULT '0' COMMENT 'Accès par ascenseur',
  `moyen_acces_monte_charge` tinyint(1) DEFAULT '0' COMMENT 'Accès par monte-charge',
  `moyen_acces_escalier` tinyint(1) DEFAULT '0' COMMENT 'Accès par escalier',
  `moyen_acces_libre` tinyint(1) DEFAULT '0' COMMENT 'Accès libre',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `puissance` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Puissance de la machine (kW, ch, etc.)',
  `photos` json DEFAULT NULL COMMENT 'Tableau JSON des chemins photos de la machine',
  `date_fin_contrat` date DEFAULT NULL,
  `rappel_fin_contrat_envoye` tinyint(1) NOT NULL DEFAULT '0',
  `statut` enum('actif','inactif') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'actif',
  PRIMARY KEY (`id`),
  KEY `idx_machine_sous_astreinte` (`machine_sous_astreinte`),
  KEY `idx_prochaine_maintenance` (`date_prochaine_maintenance`),
  KEY `idx_type_rapport` (`type_rapport_affecte`),
  KEY `idx_machine_sous_contrat` (`machine_sous_contrat`),
  KEY `idx_garantie_etendue` (`garantie_etendue`),
  KEY `idx_machines_statut` (`statut`),
  KEY `idx_machines_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `mobile_devices`
--

CREATE TABLE `mobile_devices` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `user_id` int NOT NULL,
  `device_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `device_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `platform` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_ip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_user_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_seen_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_mobile_devices_user_device` (`user_id`,`device_id`),
  KEY `idx_mobile_devices_user` (`user_id`),
  KEY `idx_mobile_devices_tenant` (`tenant_id`),
  CONSTRAINT `fk_mobile_devices_technicien` FOREIGN KEY (`user_id`) REFERENCES `techniciens` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `mobile_refresh_tokens`
--

CREATE TABLE `mobile_refresh_tokens` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `user_id` int NOT NULL,
  `token_hash` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `device_id` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `device_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `expires_at` datetime NOT NULL,
  `last_used_at` datetime DEFAULT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `revoked_reason` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `replaced_by_token_hash` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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`),
  UNIQUE KEY `uq_mobile_refresh_tokens_hash` (`token_hash`),
  KEY `idx_mobile_refresh_tokens_user` (`user_id`),
  KEY `idx_mobile_refresh_tokens_expires` (`expires_at`),
  KEY `idx_mobile_refresh_tokens_active` (`user_id`,`revoked_at`,`expires_at`),
  KEY `idx_mobile_refresh_tokens_tenant` (`tenant_id`),
  CONSTRAINT `fk_mobile_refresh_tokens_technicien` FOREIGN KEY (`user_id`) REFERENCES `techniciens` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `notifications_habilitations`
--

CREATE TABLE `notifications_habilitations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `technicien_id` int NOT NULL,
  `type_habilitation` enum('detection_fuite','capteur_temperature','balance') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `date_expiration` date NOT NULL,
  `date_envoi_notification` datetime NOT NULL,
  `type_notification` enum('3_mois_avant','mensuel') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_envoye` tinyint(1) DEFAULT '0',
  `email_destinataire` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'contact@missioflow.fr',
  `erreur_envoi` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_unique_notification` (`technicien_id`,`type_habilitation`,`date_envoi_notification`,`type_notification`),
  KEY `idx_technicien_type` (`technicien_id`,`type_habilitation`),
  KEY `idx_date_expiration` (`date_expiration`),
  KEY `idx_email_envoye` (`email_envoye`),
  KEY `idx_notifications_habilitations_tenant` (`tenant_id`),
  CONSTRAINT `fk_notifications_technicien` FOREIGN KEY (`technicien_id`) REFERENCES `techniciens` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Suivi des notifications d''expiration des habilitations techniciens';

--
-- Table structure for table `password_reset_tokens`
--

CREATE TABLE `password_reset_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `expires_at` datetime NOT NULL,
  `used` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `idx_email` (`email`),
  KEY `idx_token` (`token`),
  KEY `idx_expires` (`expires_at`),
  KEY `idx_password_reset_tokens_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `pdf_generated`
--

CREATE TABLE `pdf_generated` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `intervention_id` int NOT NULL,
  `filename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_size` int DEFAULT NULL,
  `type` enum('final','provisional','summary') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'final',
  `generated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `generated_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_intervention` (`intervention_id`),
  KEY `idx_generated_at` (`generated_at`),
  KEY `idx_pdf_generated_tenant` (`tenant_id`),
  CONSTRAINT `pdf_generated_ibfk_1` FOREIGN KEY (`intervention_id`) REFERENCES `interventions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `plans`
--

CREATE TABLE `plans` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Code machine : starter, pro, enterprise',
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nom affiche : Starter, Pro, Enterprise',
  `monthly_price` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT 'Prix mensuel HT en euros',
  `yearly_price` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT 'Prix annuel HT en euros (affichage)',
  `max_technicians` int NOT NULL DEFAULT '3' COMMENT 'Quota techniciens (-1 = illimite)',
  `max_sites` int NOT NULL DEFAULT '10' COMMENT 'Quota sites (-1 = illimite)',
  `max_machines` int NOT NULL DEFAULT '50' COMMENT 'Quota machines (-1 = illimite)',
  `max_templates` int NOT NULL DEFAULT '3' COMMENT 'Quota templates de rapport (-1 = illimite)',
  `features` json NOT NULL COMMENT 'Liste des features activees (tableau de strings)',
  `stripe_price_id_monthly` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Stripe Price ID pour l abonnement mensuel',
  `stripe_price_id_yearly` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Stripe Price ID pour l abonnement annuel',
  `visible` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1 = visible en self-service, 0 = sur devis',
  `sort_order` tinyint NOT NULL DEFAULT '0' COMMENT 'Ordre d affichage sur la page tarifs',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_plans_code` (`code`),
  KEY `idx_plans_visible` (`visible`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Plans SaaS statiques — Starter, Pro, Enterprise';

--
-- Table structure for table `prelevements_huile`
--

CREATE TABLE `prelevements_huile` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `intervention_id` int NOT NULL,
  `machine_id` int NOT NULL,
  `site_id` int NOT NULL,
  `workflow_id` int DEFAULT NULL,
  `numero_prelevement` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'N° du prélèvement saisi dans le workflow',
  `date_intervention` datetime DEFAULT NULL COMMENT 'Date prévue de l''intervention',
  `date_fin_intervention` datetime DEFAULT NULL COMMENT 'Date de fin réelle de l''intervention',
  `pdf_analyse_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Chemin relatif du PDF d''analyse laboratoire',
  `pdf_analyse_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nom original du fichier PDF d''analyse',
  `pdf_analyse_uploaded_at` timestamp NULL DEFAULT NULL,
  `statut` enum('en_attente','analyse_recue') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'en_attente',
  `notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_intervention_prelevement` (`intervention_id`,`numero_prelevement`),
  KEY `idx_ph_machine_id` (`machine_id`),
  KEY `idx_ph_site_id` (`site_id`),
  KEY `idx_ph_statut` (`statut`),
  KEY `idx_ph_workflow_id` (`workflow_id`),
  KEY `idx_prelevements_huile_tenant` (`tenant_id`),
  CONSTRAINT `fk_ph_intervention` FOREIGN KEY (`intervention_id`) REFERENCES `interventions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ph_machine` FOREIGN KEY (`machine_id`) REFERENCES `machines` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ph_site` FOREIGN KEY (`site_id`) REFERENCES `sites_clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Prélèvements d''huile réalisés lors des interventions - suivi analyses laboratoire';

--
-- Table structure for table `rapports`
--

CREATE TABLE `rapports` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `intervention_id` int NOT NULL,
  `statut_rapport` enum('provisoire','termine') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'provisoire',
  `pdf_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pdf_filename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pdf_generated_at` datetime DEFAULT NULL,
  `titre` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `contenu` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `observations` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `pieces_utilisees` json DEFAULT NULL,
  `temps_passe_minutes` int DEFAULT NULL,
  `cout_intervention` decimal(8,2) DEFAULT NULL,
  `problemes_detectes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `actions_correctives` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `recommandations` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `signature_technicien` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `signature_client` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `photos_avant` json DEFAULT NULL,
  `photos_apres` json DEFAULT NULL,
  `documents_joints` json DEFAULT NULL,
  `date_creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modification` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `modified_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_rapports_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `report_template_phases`
--

CREATE TABLE `report_template_phases` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `template_id` int unsigned NOT NULL,
  `sort_order` tinyint unsigned NOT NULL DEFAULT '1',
  `phase_key` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'ex: phase_1, phase_2…',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Titre court lisible — ex: Avant intervention',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Titre affiché dans le formulaire',
  `objective` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Description / objectif de la phase',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_phases_template` (`template_id`),
  KEY `idx_phases_order` (`template_id`,`sort_order`),
  CONSTRAINT `fk_phases_template` FOREIGN KEY (`template_id`) REFERENCES `report_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `report_template_selection_rules_v2`
--

CREATE TABLE `report_template_selection_rules_v2` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `intervention_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `machine_type_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `report_type_override` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `template_id` int unsigned NOT NULL,
  `priority` smallint unsigned NOT NULL DEFAULT '100',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `valid_from` datetime DEFAULT NULL,
  `valid_to` datetime DEFAULT NULL,
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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`),
  KEY `idx_rt_rules_v2_active_priority` (`is_active`,`priority`,`id`),
  KEY `idx_rt_rules_v2_intervention` (`intervention_type`,`is_active`,`priority`),
  KEY `idx_rt_rules_v2_machine` (`machine_type_value`(191),`is_active`,`priority`),
  KEY `idx_rt_rules_v2_override` (`report_type_override`(191),`is_active`,`priority`),
  KEY `idx_rt_rules_v2_template` (`template_id`),
  CONSTRAINT `fk_rt_rules_v2_template` FOREIGN KEY (`template_id`) REFERENCES `report_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `report_template_step_options`
--

CREATE TABLE `report_template_step_options` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `step_id` int unsigned NOT NULL,
  `option_order` tinyint unsigned NOT NULL,
  `option_label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `option_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `requires_comment` tinyint unsigned NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_step_option_order` (`step_id`,`option_order`),
  KEY `idx_options_step` (`step_id`),
  CONSTRAINT `fk_options_step` FOREIGN KEY (`step_id`) REFERENCES `report_template_steps` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `report_template_steps`
--

CREATE TABLE `report_template_steps` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `template_id` int unsigned NOT NULL,
  `phase_id` int unsigned DEFAULT NULL COMMENT 'FK → report_template_phases.id',
  `sort_order` int unsigned NOT NULL,
  `step_row_number` int unsigned DEFAULT NULL,
  `context` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Sous-contexte de la question (ex: BLOC HYDRAULIQUE)',
  `action_label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `item` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `input_kind` enum('section','display','prerequisite','text','number','choice','boolean','photo','info') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'text',
  `is_required` tinyint unsigned NOT NULL DEFAULT '0',
  `parent_step_id` int unsigned DEFAULT NULL COMMENT 'NULL = step autonome ; non-NULL = extension du step parent',
  `requires_comment` tinyint unsigned NOT NULL DEFAULT '0',
  `rules_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_steps_template_sort` (`template_id`,`sort_order`),
  KEY `idx_steps_template_row` (`template_id`,`step_row_number`),
  KEY `idx_steps_template` (`template_id`),
  KEY `idx_steps_phase` (`phase_id`),
  KEY `idx_steps_parent` (`parent_step_id`),
  CONSTRAINT `fk_steps_parent` FOREIGN KEY (`parent_step_id`) REFERENCES `report_template_steps` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_steps_phase` FOREIGN KEY (`phase_id`) REFERENCES `report_template_phases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_steps_template` FOREIGN KEY (`template_id`) REFERENCES `report_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `report_templates`
--

CREATE TABLE `report_templates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `machine_type_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `version_label` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'v1',
  `status` enum('draft','published','archived') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft',
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `source` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'manual',
  `created_by` int DEFAULT NULL,
  `updated_by` int 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`),
  KEY `idx_report_templates_machine_type` (`machine_type_value`),
  KEY `idx_report_templates_status` (`status`),
  KEY `idx_report_templates_created_by` (`created_by`),
  KEY `idx_report_templates_updated_by` (`updated_by`),
  KEY `idx_report_templates_tenant` (`tenant_id`),
  CONSTRAINT `fk_report_templates_created_by` FOREIGN KEY (`created_by`) REFERENCES `techniciens` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_report_templates_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `techniciens` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `security_alerts`
--

CREATE TABLE `security_alerts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` enum('ddos','intrusion','repeated_failures','suspicious_activity','api_abuse') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `severity` enum('low','medium','high','critical') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `source_ip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_count` int DEFAULT '1',
  `first_occurrence` datetime NOT NULL,
  `last_occurrence` datetime NOT NULL,
  `details` json DEFAULT NULL,
  `status` enum('active','acknowledged','resolved') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` datetime DEFAULT NULL,
  `resolved_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_type_severity` (`type`,`severity`),
  KEY `idx_status_created` (`status`,`created_at`),
  KEY `idx_source_ip` (`source_ip`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `security_resolutions`
--

CREATE TABLE `security_resolutions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `alert_id` int NOT NULL,
  `solution_id` int DEFAULT NULL,
  `resolved_by` int NOT NULL,
  `resolution_notes` text,
  `actions_executed` json DEFAULT NULL COMMENT 'Actions automatiques qui ont été exécutées',
  `execution_results` json DEFAULT NULL COMMENT 'Résultats de l''exécution des actions',
  `resolved_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `alert_id` (`alert_id`),
  KEY `solution_id` (`solution_id`),
  KEY `resolved_by` (`resolved_by`),
  CONSTRAINT `security_resolutions_ibfk_1` FOREIGN KEY (`alert_id`) REFERENCES `security_alerts` (`id`),
  CONSTRAINT `security_resolutions_ibfk_2` FOREIGN KEY (`solution_id`) REFERENCES `security_solutions` (`id`),
  CONSTRAINT `security_resolutions_ibfk_3` FOREIGN KEY (`resolved_by`) REFERENCES `techniciens` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `security_solutions`
--

CREATE TABLE `security_solutions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT 'Nom de la solution',
  `category` enum('block','monitor','investigate','escalate','ignore') NOT NULL COMMENT 'Catégorie de la solution',
  `description` text COMMENT 'Description détaillée de la solution',
  `automatic_actions` json DEFAULT NULL COMMENT 'Actions automatiques à exécuter (JSON)',
  `applicable_to` json DEFAULT NULL COMMENT 'Types d''alertes auxquels cette solution s''applique',
  `severity_level` enum('low','medium','high','critical') DEFAULT NULL COMMENT 'Niveau de sévérité recommandé',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `sites_clients`
--

CREATE TABLE `sites_clients` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `nom_lieu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `numero_rue` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rue` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `adresse_complement_1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `adresse_complement_2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ville` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `code_postal` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pays` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'France',
  `adresse_complete` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mainteneur_nom` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_nom_2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nom contact 2',
  `contact_nom_3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nom contact 3',
  `contact_nom_4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nom contact 4',
  `contact_nom_5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nom contact 5',
  `contact_nom_6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nom contact 6',
  `mode_intervention` enum('mainteneur','direct') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `numero_contrat_commande` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `adresse_backup` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `email_responsable` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_email_2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Email contact secondaire',
  `contact_email_3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Email contact tertiaire',
  `contact_email_4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Email contact 4',
  `contact_email_5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Email contact 5',
  `contact_email_6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Email contact 6',
  `telephone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_telephone_2` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Téléphone contact secondaire',
  `contact_telephone_3` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Téléphone contact tertiaire',
  `contact_telephone_4` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Téléphone contact 4',
  `contact_telephone_5` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Téléphone contact 5',
  `contact_telephone_6` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Téléphone contact 6',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `contact_principal` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nom de la personne de contact pour ce site',
  `sous_contrat` tinyint(1) DEFAULT '0' COMMENT 'Site sous contrat de maintenance',
  `date_debut_contrat` date DEFAULT NULL,
  `plan_prevention_numero` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Numéro du plan de prévention',
  `plan_prevention_date_signature` date DEFAULT NULL COMMENT 'Date de signature du plan de prévention',
  `plan_prevention_signataires` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Liste des signataires MissioFlow',
  `date_renouvellement_contrat` date DEFAULT NULL COMMENT 'Date de renouvellement du contrat de maintenance',
  `site_industriel` tinyint(1) DEFAULT '0' COMMENT 'Site industriel',
  `site_tertiaire` tinyint(1) DEFAULT '0' COMMENT 'Site tertiaire',
  `site_chimique` tinyint(1) DEFAULT '0' COMMENT 'Site chimique',
  `contrainte_acces` tinyint(1) DEFAULT '0' COMMENT 'Contrainte d''accès particulière',
  `contrainte_acces_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Description de la contrainte d''accès',
  `rappel_60j_envoye` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Flag : rappel 60 jours avant echeance du contrat deja envoye',
  `rappel_30j_envoye` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Flag : rappel 30 jours avant echeance du contrat deja envoye',
  `rappel_15j_envoye` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Flag : rappel 15 jours avant echeance du contrat deja envoye',
  PRIMARY KEY (`id`),
  KEY `idx_sites_ville` (`ville`),
  KEY `idx_sites_code_postal` (`code_postal`),
  KEY `idx_sous_contrat` (`sous_contrat`),
  KEY `idx_site_industriel` (`site_industriel`),
  KEY `idx_site_tertiaire` (`site_tertiaire`),
  KEY `idx_site_chimique` (`site_chimique`),
  KEY `idx_contrainte_acces` (`contrainte_acces`),
  KEY `idx_sites_clients_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Temporary view structure for view `suspicious_ips`
--

/*!50001 DROP VIEW IF EXISTS `suspicious_ips`*/;
/*!50001 CREATE VIEW `suspicious_ips` AS SELECT 
 1 AS `source_ip`,
 1 AS `total_requests`,
 1 AS `error_requests`,
 1 AS `suspicious_requests`,
 1 AS `last_activity`,
 1 AS `suspicious_percentage`*/;

--
-- Temporary view structure for view `suspicious_ips_view`
--

/*!50001 DROP VIEW IF EXISTS `suspicious_ips_view`*/;
/*!50001 CREATE VIEW `suspicious_ips_view` AS SELECT 
 1 AS `source_ip`,
 1 AS `total_requests`,
 1 AS `error_requests`,
 1 AS `suspicious_requests`,
 1 AS `last_activity`,
 1 AS `suspicious_percentage`*/;

--
-- Table structure for table `taches_annuelles`
--

CREATE TABLE `taches_annuelles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `machine_id` int NOT NULL,
  `type_tache` enum('prelevement_huile','controle_etancheite','autre') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `annee` int NOT NULL,
  `effectuee` tinyint(1) DEFAULT '0',
  `date_effectuee` date DEFAULT NULL,
  `intervention_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_taches_annuelles_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `technicien_plan_prevention_sites`
--

CREATE TABLE `technicien_plan_prevention_sites` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `technicien_id` int NOT NULL,
  `site_id` int NOT NULL,
  `valide` tinyint(1) DEFAULT '0',
  `date_validation` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_technicien_site` (`technicien_id`,`site_id`),
  KEY `idx_technicien_plan_prevention_technicien` (`technicien_id`),
  KEY `idx_technicien_plan_prevention_site` (`site_id`),
  KEY `idx_technicien_plan_prevention_sites_tenant` (`tenant_id`),
  CONSTRAINT `technicien_plan_prevention_sites_ibfk_1` FOREIGN KEY (`technicien_id`) REFERENCES `techniciens` (`id`) ON DELETE CASCADE,
  CONSTRAINT `technicien_plan_prevention_sites_ibfk_2` FOREIGN KEY (`site_id`) REFERENCES `sites_clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `technicien_qr_tokens`
--

CREATE TABLE `technicien_qr_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `technicien_id` int NOT NULL,
  `token` varchar(64) NOT NULL,
  `payload` longtext NOT NULL,
  `expires_at` datetime DEFAULT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `revoked_by` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `idx_qr_technicien` (`technicien_id`),
  KEY `idx_qr_token` (`token`),
  KEY `idx_qr_expires` (`expires_at`),
  KEY `idx_qr_revoked` (`revoked_at`),
  KEY `idx_technicien_qr_tokens_tenant` (`tenant_id`),
  CONSTRAINT `fk_qr_technicien` FOREIGN KEY (`technicien_id`) REFERENCES `techniciens` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `techniciens`
--

CREATE TABLE `techniciens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `nom` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `prenom` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password_hash` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `telephone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `numero_habilitation_fluide` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_embauche` date DEFAULT NULL,
  `date_etalonage_detection_fuite` date DEFAULT NULL,
  `date_etalonage_capteur_temperature` date DEFAULT NULL,
  `date_etalonage_balance` date DEFAULT NULL,
  `actif` tinyint(1) DEFAULT '1',
  `force_password_change` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `role` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'technicien',
  `plaque_vehicule` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Plaque d''immatriculation du véhicule',
  `piece_identite_pdf` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Chemin fichier PDF pièce identité',
  `permis_pdf` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Chemin fichier PDF permis de conduire - rappel annuel auto',
  `documents_annexe_pdf` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Chemins fichiers PDF documents annexes (JSON array)',
  `lien_habilitation_pdf` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Chemin PDF habilitation + QR code',
  `date_validite_outillage` date DEFAULT NULL COMMENT 'Date validité contrôle outillage - rappel auto',
  `numero_attestation_capacite` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'N° attestation capacité',
  `date_validite_attestation_capacite` date DEFAULT NULL COMMENT 'Date validité attestation - rappel auto',
  `habilitation_electrique` tinyint(1) DEFAULT '0' COMMENT 'Habilitation électrique oui/non',
  `date_validite_electrique` date DEFAULT NULL COMMENT 'Date validité habilitation électrique',
  `travail_hauteur` tinyint(1) DEFAULT '0' COMMENT 'Habilitation travail en hauteur',
  `date_validite_hauteur` date DEFAULT NULL COMMENT 'Date validité travail hauteur',
  `habilitation_chimique` tinyint(1) DEFAULT '0' COMMENT 'Habilitation chimique',
  `date_validite_chimique` date DEFAULT NULL COMMENT 'Date validité habilitation chimique',
  `habilitation_nacelle` tinyint(1) DEFAULT '0' COMMENT 'Habilitation nacelle',
  `date_validite_nacelle` date DEFAULT NULL COMMENT 'Date validité habilitation nacelle',
  `habilitation_electrique_pdf` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Document PDF habilitation électrique',
  `travail_hauteur_pdf` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Document PDF travail en hauteur',
  `habilitation_chimique_pdf` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Document PDF habilitation chimique',
  `habilitation_nacelle_pdf` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Document PDF habilitation nacelle',
  PRIMARY KEY (`id`),
  KEY `idx_techniciens_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `techniciens_invitation_tokens`
--

CREATE TABLE `techniciens_invitation_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `technicien_id` int NOT NULL,
  `token` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `expires_at` datetime NOT NULL,
  `used` tinyint(1) NOT NULL DEFAULT '0',
  `used_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_token` (`token`),
  KEY `idx_technicien_id` (`technicien_id`),
  KEY `idx_techniciens_invitation_tokens_tenant` (`tenant_id`),
  CONSTRAINT `fk_tech_invitation_technicien` FOREIGN KEY (`technicien_id`) REFERENCES `techniciens` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `tenant_subscriptions`
--

CREATE TABLE `tenant_subscriptions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL COMMENT 'FK vers tenants.id',
  `plan_id` int NOT NULL COMMENT 'FK vers plans.id',
  `billing_period` enum('monthly','yearly') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'monthly' COMMENT 'Periodicite de facturation',
  `status` enum('trialing','active','past_due','canceled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'trialing',
  `current_period_start` datetime DEFAULT NULL COMMENT 'Debut de la periode de facturation courante',
  `current_period_end` datetime DEFAULT NULL COMMENT 'Fin de la periode de facturation courante',
  `trial_end` datetime DEFAULT NULL COMMENT 'Date de fin de l essai 14 jours',
  `canceled_at` datetime DEFAULT NULL COMMENT 'Date d annulation par le client',
  `stripe_subscription_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'ID Stripe de la subscription',
  `stripe_customer_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'ID Stripe du customer',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ts_tenant_active` (`tenant_id`,`status`),
  KEY `idx_ts_tenant` (`tenant_id`),
  KEY `idx_ts_plan` (`plan_id`),
  KEY `idx_ts_status` (`status`),
  CONSTRAINT `fk_ts_plan` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_ts_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Abonnement actif par tenant â€” un row = une souscription';

--
-- Table structure for table `tenant_activation_tokens`
--

CREATE TABLE `tenant_activation_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `token` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Token unique envoye par email',
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Email du prospect',
  `tenant_id` int DEFAULT NULL COMMENT 'FK vers tenants.id (null tant que le tenant n est pas cree)',
  `stripe_customer_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'ID Stripe du customer',
  `stripe_subscription_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'ID Stripe de la subscription',
  `plan_code` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Plan choisi (starter, pro)',
  `billing_period` enum('monthly','yearly') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'monthly',
  `company_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nom d entreprise saisi au checkout',
  `used` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = token consomme',
  `expires_at` datetime NOT NULL COMMENT 'Expiration du token (72h apres creation)',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_activation_token` (`token`),
  KEY `idx_activation_email` (`email`),
  KEY `idx_activation_tenant` (`tenant_id`),
  KEY `idx_activation_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tokens d activation post-paiement Stripe pour le signup self-service';

--
-- Table structure for table `tenants`
--

CREATE TABLE `tenants` (
  `id` int NOT NULL AUTO_INCREMENT,
  `slug` varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Sous-domaine unique — ex: acme -> acme.missioflow.fr',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nom commercial du tenant',
  `admin_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Email principal de l admin du tenant',
  `status` enum('trial','active','suspended','canceled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'trial',
  `plan` enum('starter','pro','enterprise') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'starter',
  `trial_end_at` datetime DEFAULT NULL,
  `stripe_customer_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stripe_subscription_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `onboarding_completed` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = onboarding wizard termine',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tenants_slug` (`slug`),
  KEY `idx_tenants_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table centrale SaaS — un row = une entreprise cliente';

--
-- Table structure for table `user_activity_monitoring`
--

CREATE TABLE `user_activity_monitoring` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tenant_id` int NOT NULL DEFAULT '1' COMMENT 'FK vers tenants.id',
  `user_id` int NOT NULL,
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_role` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `risk_level` enum('normal','suspicious','dangerous') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'normal',
  `details` json DEFAULT NULL,
  `ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `session_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  `flagged_by_system` tinyint(1) DEFAULT '0',
  `reviewed_by_admin` tinyint(1) DEFAULT '0',
  `admin_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `idx_user_timestamp` (`user_id`,`timestamp`),
  KEY `idx_risk_level` (`risk_level`),
  KEY `idx_flagged` (`flagged_by_system`),
  KEY `idx_user_name` (`user_name`),
  KEY `idx_user_activity_monitoring_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Temporary view structure for view `vue_interventions_completes`
--

/*!50001 DROP VIEW IF EXISTS `vue_interventions_completes`*/;
/*!50001 CREATE VIEW `vue_interventions_completes` AS SELECT 
 1 AS `id`,
 1 AS `tenant_id`,
 1 AS `numero_r`,
 1 AS `site_id`,
 1 AS `machine_id`,
 1 AS `devis_id`,
 1 AS `technicien_id`,
 1 AS `type_intervention`,
 1 AS `priorite`,
 1 AS `statut`,
 1 AS `date_prevue`,
 1 AS `date_debut`,
 1 AS `date_fin`,
 1 AS `duree_estimee_minutes`,
 1 AS `duree_reelle_minutes`,
 1 AS `titre`,
 1 AS `description`,
 1 AS `observations_technicien`,
 1 AS `probleme_detecte`,
 1 AS `actions_effectuees`,
 1 AS `pieces_changees`,
 1 AS `temperature_avant`,
 1 AS `temperature_apres`,
 1 AS `pression_avant`,
 1 AS `pression_apres`,
 1 AS `charge_fluide_kg`,
 1 AS `fuite_detectee`,
 1 AS `quantite_fuite_kg`,
 1 AS `conforme_reglementation`,
 1 AS `certificat_genere`,
 1 AS `signature_client`,
 1 AS `signature_technicien`,
 1 AS `photos`,
 1 AS `documents`,
 1 AS `cout_main_oeuvre`,
 1 AS `cout_pieces`,
 1 AS `cout_total`,
 1 AS `facture_generee`,
 1 AS `numero_facture`,
 1 AS `created_at`,
 1 AS `updated_at`,
 1 AS `created_by`,
 1 AS `fluide_utilise`,
 1 AS `quantite_fluide`,
 1 AS `coefficient_gaz`,
 1 AS `teq_co2`,
 1 AS `visites_annuelles`,
 1 AS `temperature_evaporation`,
 1 AS `temperature_condensation`,
 1 AS `recommandations`,
 1 AS `report_type_override`,
 1 AS `report_template_id_override`,
 1 AS `report_selection_source`,
 1 AS `report_template_id_selected`,
 1 AS `workflow_status`,
 1 AS `missions_fusionnees`,
 1 AS `site_nom`,
 1 AS `site_adresse`,
 1 AS `machine_nom`,
 1 AS `machine_gaz`,
 1 AS `machine_marque`,
 1 AS `machine_modele`,
 1 AS `technicien_nom`,
 1 AS `technicien_prenom`,
 1 AS `technicien_email`,
 1 AS `devis_numero`,
 1 AS `duree_calculee_minutes`,
 1 AS `en_retard`*/;

--
-- Table structure for table `vue_stats_sites`
--

CREATE TABLE `vue_stats_sites` (
  `id` int DEFAULT NULL,
  `nom` varchar(255) DEFAULT NULL,
  `total_interventions` bigint DEFAULT NULL,
  `interventions_terminees` bigint DEFAULT NULL,
  `depannages_urgences` bigint DEFAULT NULL,
  `maintenances_preventives` bigint DEFAULT NULL,
  `derniere_intervention_date` datetime DEFAULT NULL,
  `cout_moyen` decimal(12,6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `vue_stats_techniciens`
--

CREATE TABLE `vue_stats_techniciens` (
  `id` int DEFAULT NULL,
  `nom` varchar(100) DEFAULT NULL,
  `prenom` varchar(100) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `total_interventions` bigint DEFAULT NULL,
  `interventions_terminees` bigint DEFAULT NULL,
  `interventions_en_cours` bigint DEFAULT NULL,
  `interventions_planifiees` bigint DEFAULT NULL,
  `interventions_urgentes` bigint DEFAULT NULL,
  `duree_moyenne_minutes` decimal(14,4) DEFAULT NULL,
  `interventions_en_retard` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `workflow_idempotency_keys`
--

CREATE TABLE `workflow_idempotency_keys` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `idempotency_key` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `action` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `workflow_id` bigint unsigned NOT NULL,
  `user_id` int NOT NULL,
  `step_key` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `http_status` smallint unsigned NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_idempotency_lookup` (`idempotency_key`,`action`,`workflow_id`,`user_id`),
  KEY `idx_idempotency_expires_at` (`expires_at`),
  KEY `fk_idempotency_workflow` (`workflow_id`),
  CONSTRAINT `fk_idempotency_workflow` FOREIGN KEY (`workflow_id`) REFERENCES `intervention_workflows` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Final view structure for view `active_alerts_with_user`
--

/*!50001 DROP VIEW IF EXISTS `active_alerts_with_user`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`demo_user`@`%` SQL SECURITY INVOKER */
/*!50001 VIEW `active_alerts_with_user` AS select `sa`.`id` AS `id`,`sa`.`type` AS `type`,`sa`.`severity` AS `severity`,`sa`.`source_ip` AS `source_ip`,`sa`.`user_id` AS `user_id`,`sa`.`event_count` AS `event_count`,`sa`.`first_occurrence` AS `first_occurrence`,`sa`.`last_occurrence` AS `last_occurrence`,`sa`.`details` AS `details`,`sa`.`status` AS `status`,`sa`.`created_at` AS `created_at`,`sa`.`resolved_at` AS `resolved_at`,`sa`.`resolved_by` AS `resolved_by`,`u`.`nom` AS `technicien_nom`,`u`.`email` AS `technicien_email`,`u`.`role` AS `technicien_role` from (`security_alerts` `sa` left join `techniciens` `u` on((`sa`.`user_id` = `u`.`id`))) where (`sa`.`status` = 'active') order by `sa`.`severity` desc,`sa`.`created_at` desc */;

--
-- Final view structure for view `daily_user_stats`
--

/*!50001 DROP VIEW IF EXISTS `daily_user_stats`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`demo_user`@`%` SQL SECURITY INVOKER */
/*!50001 VIEW `daily_user_stats` AS select cast(`uam`.`timestamp` as date) AS `activity_date`,`u`.`id` AS `user_id`,`u`.`nom` AS `user_name`,`u`.`role` AS `user_role`,count(0) AS `total_actions`,count((case when (`uam`.`risk_level` = 'suspicious') then 1 end)) AS `suspicious_actions`,count((case when (`uam`.`risk_level` = 'dangerous') then 1 end)) AS `dangerous_actions` from (`user_activity_monitoring` `uam` join `techniciens` `u` on((`uam`.`user_id` = `u`.`id`))) group by cast(`uam`.`timestamp` as date),`u`.`id` order by `activity_date` desc,`total_actions` desc */;

--
-- Final view structure for view `suspicious_ips`
--

/*!50001 DROP VIEW IF EXISTS `suspicious_ips`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`demo_user`@`%` SQL SECURITY INVOKER */
/*!50001 VIEW `suspicious_ips` AS select `api_metrics`.`source_ip` AS `source_ip`,count(0) AS `total_requests`,count((case when (`api_metrics`.`response_status` >= 400) then 1 end)) AS `error_requests`,count((case when (`api_metrics`.`is_suspicious` = 1) then 1 end)) AS `suspicious_requests`,max(`api_metrics`.`timestamp`) AS `last_activity`,((count((case when (`api_metrics`.`is_suspicious` = 1) then 1 end)) / count(0)) * 100) AS `suspicious_percentage` from `api_metrics` where (`api_metrics`.`timestamp` >= (now() - interval 24 hour)) group by `api_metrics`.`source_ip` having ((`suspicious_percentage` > 20) or (`total_requests` > 200)) order by `suspicious_percentage` desc,`total_requests` desc */;

--
-- Final view structure for view `suspicious_ips_view`
--

/*!50001 DROP VIEW IF EXISTS `suspicious_ips_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`demo_user`@`%` SQL SECURITY INVOKER */
/*!50001 VIEW `suspicious_ips_view` AS select `api_metrics`.`source_ip` AS `source_ip`,count(0) AS `total_requests`,sum((case when (`api_metrics`.`response_status` >= 400) then 1 else 0 end)) AS `error_requests`,sum((case when (`api_metrics`.`is_suspicious` = 1) then 1 else 0 end)) AS `suspicious_requests`,max(`api_metrics`.`timestamp`) AS `last_activity`,round(((sum((case when (`api_metrics`.`is_suspicious` = 1) then 1 else 0 end)) / count(0)) * 100),2) AS `suspicious_percentage` from `api_metrics` where (`api_metrics`.`timestamp` >= (now() - interval 24 hour)) group by `api_metrics`.`source_ip` having ((`suspicious_percentage` > 20) or (`total_requests` > 200)) order by `suspicious_percentage` desc,`total_requests` desc */;

--
-- Final view structure for view `vue_interventions_completes`
--

/*!50001 DROP VIEW IF EXISTS `vue_interventions_completes`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`demo_user`@`%` SQL SECURITY INVOKER */
/*!50001 VIEW `vue_interventions_completes` AS select `i`.`id` AS `id`,`i`.`tenant_id` AS `tenant_id`,`i`.`numero_r` AS `numero_r`,`i`.`site_id` AS `site_id`,`i`.`machine_id` AS `machine_id`,`i`.`devis_id` AS `devis_id`,`i`.`technicien_id` AS `technicien_id`,`i`.`type_intervention` AS `type_intervention`,`i`.`priorite` AS `priorite`,`i`.`statut` AS `statut`,`i`.`date_prevue` AS `date_prevue`,`i`.`date_debut` AS `date_debut`,`i`.`date_fin` AS `date_fin`,`i`.`duree_estimee_minutes` AS `duree_estimee_minutes`,`i`.`duree_reelle_minutes` AS `duree_reelle_minutes`,`i`.`titre` AS `titre`,`i`.`description` AS `description`,`i`.`observations_technicien` AS `observations_technicien`,`i`.`probleme_detecte` AS `probleme_detecte`,`i`.`actions_effectuees` AS `actions_effectuees`,`i`.`pieces_changees` AS `pieces_changees`,`i`.`temperature_avant` AS `temperature_avant`,`i`.`temperature_apres` AS `temperature_apres`,`i`.`pression_avant` AS `pression_avant`,`i`.`pression_apres` AS `pression_apres`,`i`.`charge_fluide_kg` AS `charge_fluide_kg`,`i`.`fuite_detectee` AS `fuite_detectee`,`i`.`quantite_fuite_kg` AS `quantite_fuite_kg`,`i`.`conforme_reglementation` AS `conforme_reglementation`,`i`.`certificat_genere` AS `certificat_genere`,`i`.`signature_client` AS `signature_client`,`i`.`signature_technicien` AS `signature_technicien`,`i`.`photos` AS `photos`,`i`.`documents` AS `documents`,`i`.`cout_main_oeuvre` AS `cout_main_oeuvre`,`i`.`cout_pieces` AS `cout_pieces`,`i`.`cout_total` AS `cout_total`,`i`.`facture_generee` AS `facture_generee`,`i`.`numero_facture` AS `numero_facture`,`i`.`created_at` AS `created_at`,`i`.`updated_at` AS `updated_at`,`i`.`created_by` AS `created_by`,`i`.`fluide_utilise` AS `fluide_utilise`,`i`.`quantite_fluide` AS `quantite_fluide`,`i`.`coefficient_gaz` AS `coefficient_gaz`,`i`.`teq_co2` AS `teq_co2`,`i`.`visites_annuelles` AS `visites_annuelles`,`i`.`temperature_evaporation` AS `temperature_evaporation`,`i`.`temperature_condensation` AS `temperature_condensation`,`i`.`recommandations` AS `recommandations`,`i`.`report_type_override` AS `report_type_override`,`i`.`report_template_id_override` AS `report_template_id_override`,`i`.`report_selection_source` AS `report_selection_source`,`i`.`report_template_id_selected` AS `report_template_id_selected`,`i`.`workflow_status` AS `workflow_status`,`i`.`missions_fusionnees` AS `missions_fusionnees`,`s`.`nom_lieu` AS `site_nom`,`s`.`adresse_complete` AS `site_adresse`,`m`.`nom_machine` AS `machine_nom`,`m`.`gaz` AS `machine_gaz`,`m`.`marque` AS `machine_marque`,`m`.`modele` AS `machine_modele`,`t`.`nom` AS `technicien_nom`,`t`.`prenom` AS `technicien_prenom`,`t`.`email` AS `technicien_email`,`d`.`numero_devis` AS `devis_numero`,(case when ((`i`.`statut` = 'terminee') and (`i`.`date_fin` is not null)) then timestampdiff(MINUTE,`i`.`date_debut`,`i`.`date_fin`) else NULL end) AS `duree_calculee_minutes`,(case when ((`i`.`date_prevue` < now()) and (`i`.`statut` in ('planifiee','reportee'))) then true else false end) AS `en_retard` from ((((`interventions` `i` join `sites_clients` `s` on(((`i`.`site_id` = `s`.`id`) and (`i`.`tenant_id` = `s`.`tenant_id`)))) join `machines` `m` on(((`i`.`machine_id` = `m`.`id`) and (`i`.`tenant_id` = `m`.`tenant_id`)))) left join `techniciens` `t` on(((`i`.`technicien_id` = `t`.`id`) and (`i`.`tenant_id` = `t`.`tenant_id`)))) left join `devis` `d` on(((`i`.`devis_id` = `d`.`id`) and (`i`.`tenant_id` = `d`.`tenant_id`)))) */;


