-- ============================================
-- IZOFIT CRM - Schéma de base de données
-- Version: 1.0
-- ============================================

SET CHARACTER SET utf8mb4;

-- ============================================
-- Table: izofit_leads (Demandes d'intervention)
-- ============================================
CREATE TABLE IF NOT EXISTS `izofit_leads` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Statut et priorité
    `status` ENUM('nouveau', 'a_rappeler', 'en_diagnostic', 'devis_envoye', 'planifie', 'termine', 'perdu') NOT NULL DEFAULT 'nouveau',
    `priority` ENUM('low', 'normal', 'high') NOT NULL DEFAULT 'normal',
    `assigned_to` INT UNSIGNED NULL,

    -- Informations client
    `customer_firstname` VARCHAR(100) NOT NULL,
    `customer_lastname` VARCHAR(100) NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `company` VARCHAR(150) NULL,
    `city` VARCHAR(100) NULL,
    `postal_code` VARCHAR(10) NULL,

    -- Informations équipement
    `equipment_type` VARCHAR(100) NOT NULL,
    `brand_model` VARCHAR(150) NULL,
    `issue_description` TEXT NOT NULL,

    -- Préférences
    `urgency` ENUM('non', 'sous_7_jours', 'urgent') NOT NULL DEFAULT 'non',
    `contact_preference` ENUM('appel', 'sms', 'email') NOT NULL DEFAULT 'appel',

    -- Tracking & source
    `source` VARCHAR(50) NOT NULL DEFAULT 'shopify',
    `utm_source` VARCHAR(100) NULL,
    `utm_medium` VARCHAR(100) NULL,
    `utm_campaign` VARCHAR(100) NULL,

    -- RGPD & Anti-spam
    `gdpr_consent` TINYINT(1) NOT NULL DEFAULT 0,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(500) NULL,
    `spam_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `is_spam` TINYINT(1) NOT NULL DEFAULT 0,
    `submission_time` INT UNSIGNED NULL COMMENT 'Temps de remplissage en secondes',

    -- Clés étrangères et index
    CONSTRAINT `fk_leads_assigned_to`
        FOREIGN KEY (`assigned_to`) REFERENCES `izofit_users`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE,

    INDEX `idx_leads_status` (`status`),
    INDEX `idx_leads_priority` (`priority`),
    INDEX `idx_leads_created_at` (`created_at`),
    INDEX `idx_leads_assigned_to` (`assigned_to`),
    INDEX `idx_leads_email` (`email`),
    INDEX `idx_leads_phone` (`phone`),
    INDEX `idx_leads_is_spam` (`is_spam`),
    INDEX `idx_leads_status_created` (`status`, `created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: izofit_lead_notes (Notes internes)
-- ============================================
CREATE TABLE IF NOT EXISTS `izofit_lead_notes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lead_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `content` TEXT NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT `fk_notes_lead`
        FOREIGN KEY (`lead_id`) REFERENCES `izofit_leads`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_notes_user`
        FOREIGN KEY (`user_id`) REFERENCES `izofit_users`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,

    INDEX `idx_notes_lead_id` (`lead_id`),
    INDEX `idx_notes_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: izofit_lead_history (Historique des changements)
-- ============================================
CREATE TABLE IF NOT EXISTS `izofit_lead_history` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lead_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NULL,
    `action_type` ENUM('status_change', 'priority_change', 'assignment_change', 'created', 'note_added', 'updated') NOT NULL,
    `old_value` VARCHAR(255) NULL,
    `new_value` VARCHAR(255) NULL,
    `description` VARCHAR(500) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT `fk_history_lead`
        FOREIGN KEY (`lead_id`) REFERENCES `izofit_leads`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_history_user`
        FOREIGN KEY (`user_id`) REFERENCES `izofit_users`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE,

    INDEX `idx_history_lead_id` (`lead_id`),
    INDEX `idx_history_created_at` (`created_at`),
    INDEX `idx_history_action_type` (`action_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: izofit_rate_limits (Anti-spam rate limiting)
-- ============================================
CREATE TABLE IF NOT EXISTS `izofit_rate_limits` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ip_address` VARCHAR(45) NOT NULL,
    `endpoint` VARCHAR(100) NOT NULL,
    `request_count` INT UNSIGNED NOT NULL DEFAULT 1,
    `first_request` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_request` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY `unique_ip_endpoint` (`ip_address`, `endpoint`),
    INDEX `idx_rate_limits_last_request` (`last_request`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Leads d'exemple
INSERT INTO `izofit_leads` (
    `status`, `priority`, `assigned_to`,
    `customer_firstname`, `customer_lastname`, `phone`, `email`, `company`, `city`, `postal_code`,
    `equipment_type`, `brand_model`, `issue_description`,
    `urgency`, `contact_preference`, `source`, `gdpr_consent`
) VALUES
(
    'nouveau', 'high', NULL,
    'Pierre', 'Durand', '0612345678', 'pierre.durand@email.com', 'CrossFit Lyon', 'Lyon', '69003',
    'Rameur Concept2', 'Model D', 'La chaîne fait un bruit anormal lors du tirage. Le compteur de distance semble également décalé.',
    'urgent', 'appel', 'shopify', 1
),
(
    'a_rappeler', 'normal', 2,
    'Sophie', 'Bernard', '0698765432', 'sophie.b@gmail.com', NULL, 'Marseille', '13008',
    'Echo Bike', 'Rogue Echo Bike', 'Grincement au niveau du pédalier, surtout à haute intensité.',
    'sous_7_jours', 'sms', 'shopify', 1
),
(
    'en_diagnostic', 'low', 2,
    'Marc', 'Lefebvre', '0654321098', 'marc.lefebvre@entreprise.fr', 'Salle Fitness Plus', 'Paris', '75011',
    'Tapis', 'NordicTrack Commercial 1750', 'Le tapis ralentit de façon aléatoire pendant l\'utilisation. Possible problème de moteur.',
    'non', 'email', 'shopify', 1
);

-- Historique d'exemple
INSERT INTO `izofit_lead_history` (`lead_id`, `user_id`, `action_type`, `old_value`, `new_value`, `description`) VALUES
(1, NULL, 'created', NULL, NULL, 'Demande créée via le formulaire Shopify'),
(2, NULL, 'created', NULL, NULL, 'Demande créée via le formulaire Shopify'),
(2, 1, 'status_change', 'nouveau', 'a_rappeler', 'Statut modifié'),
(2, 1, 'assignment_change', NULL, 'Jean Dupont', 'Lead assigné'),
(3, NULL, 'created', NULL, NULL, 'Demande créée via le formulaire Shopify'),
(3, 1, 'status_change', 'nouveau', 'en_diagnostic', 'Statut modifié'),
(3, 1, 'assignment_change', NULL, 'Jean Dupont', 'Lead assigné');

-- Notes d'exemple
INSERT INTO `izofit_lead_notes` (`lead_id`, `user_id`, `content`) VALUES
(2, 1, 'Client contacté, rappel prévu demain matin.'),
(3, 2, 'Diagnostic en cours : suspicion de problème sur la carte contrôleur.');
