-- ═══════════════════════════════════════════════════════════════════════════
-- SYSTÈME UNIFIÉ LIVRES & APPLICATIONS
-- Base de données MySQL
-- ═══════════════════════════════════════════════════════════════════════════
-- 
-- À exécuter dans phpMyAdmin sur o2switch
-- Base : sc1juyb4851_trousse
--
-- ═══════════════════════════════════════════════════════════════════════════

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ═══════════════════════════════════════════════════════════════════════════
-- TABLE: utilisateurs
-- Utilisateurs unifiés (acheteurs livres + utilisateurs apps)
-- ═══════════════════════════════════════════════════════════════════════════
DROP TABLE IF EXISTS `utilisateurs`;
CREATE TABLE `utilisateurs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) DEFAULT NULL,
    `prenom` VARCHAR(100) NOT NULL,
    `nom` VARCHAR(100) NOT NULL,
    `genre` ENUM('homme', 'femme', 'autre') DEFAULT NULL,
    
    -- Parrainage
    `code_parrainage` VARCHAR(20) UNIQUE,           -- Code unique pour parrainer (PARRAIN-XXXXXX)
    `parrain_id` INT UNSIGNED DEFAULT NULL,          -- Qui l'a parrainé
    `nb_parrainages` INT UNSIGNED DEFAULT 0,         -- Nombre de filleuls convertis
    `statut_parrainage` ENUM('standard', 'actif', 'vip', 'ambassadeur') DEFAULT 'standard',
    `mois_gratuits_cumules` INT UNSIGNED DEFAULT 0,  -- Total mois gratuits gagnés
    
    -- Abonnement applications
    `date_expiration_app` DATE DEFAULT NULL,         -- Date fin accès apps
    `tarif_annuel_bloque` DECIMAL(5,2) DEFAULT NULL, -- Tarif bloqué à vie (VIP = 4.90€)
    
    -- Challenge annuel
    `parrainages_annee_courante` INT UNSIGNED DEFAULT 0,
    `challenge_complete` BOOLEAN DEFAULT FALSE,
    
    -- Tracking
    `provenance_initiale` VARCHAR(50) DEFAULT NULL,  -- Comment il nous a connu
    `source_inscription` ENUM('site', 'amazon', 'cabinet', 'pharmacie', 'magasin', 'autre') DEFAULT 'site',
    
    -- Métadonnées
    `date_inscription` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `derniere_connexion` DATETIME DEFAULT NULL,
    `email_verifie` BOOLEAN DEFAULT FALSE,
    `actif` BOOLEAN DEFAULT TRUE,
    
    INDEX `idx_email` (`email`),
    INDEX `idx_code_parrainage` (`code_parrainage`),
    INDEX `idx_parrain` (`parrain_id`),
    INDEX `idx_statut` (`statut_parrainage`),
    
    FOREIGN KEY (`parrain_id`) REFERENCES `utilisateurs`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- TABLE: livres
-- Catalogue des livres (synchronisé avec config.php)
-- ═══════════════════════════════════════════════════════════════════════════
DROP TABLE IF EXISTS `livres`;
CREATE TABLE `livres` (
    `id` VARCHAR(50) PRIMARY KEY,                    -- ID unique (ex: 'piqures', 'drainage')
    `titre` VARCHAR(255) NOT NULL,
    `collection` VARCHAR(100) DEFAULT NULL,
    `description` TEXT,
    `prix` DECIMAL(6,2) NOT NULL,
    `prix_amazon` DECIMAL(6,2) DEFAULT NULL,
    `icone` VARCHAR(10) DEFAULT '📖',
    `couleur` VARCHAR(7) DEFAULT '#2C5545',
    `prefixe_code` VARCHAR(5) NOT NULL UNIQUE,       -- Pour générer codes (DRA, PIQ, etc.)
    `woocommerce_id` INT UNSIGNED DEFAULT NULL,
    `amazon_url` VARCHAR(500) DEFAULT NULL,
    `statut` ENUM('publie', 'preparation', 'bientot') DEFAULT 'preparation',
    `date_sortie` DATE DEFAULT NULL,
    `app_module` VARCHAR(50) DEFAULT NULL,           -- Module app associé
    `ordre` INT UNSIGNED DEFAULT 0,
    `date_creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_statut` (`statut`),
    INDEX `idx_prefixe` (`prefixe_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- TABLE: codes_livres
-- Codes d'activation imprimés dans les livres
-- ═══════════════════════════════════════════════════════════════════════════
DROP TABLE IF EXISTS `codes_livres`;
CREATE TABLE `codes_livres` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(20) NOT NULL UNIQUE,              -- Ex: DRA-A1B2C3D4
    `livre_id` VARCHAR(50) NOT NULL,
    `type` ENUM('standard', 'vip', 'promo') DEFAULT 'standard',
    `utilise` BOOLEAN DEFAULT FALSE,
    `utilisateur_id` INT UNSIGNED DEFAULT NULL,
    `date_utilisation` DATETIME DEFAULT NULL,
    `provenance` VARCHAR(50) DEFAULT NULL,           -- Où le livre a été acheté
    `date_creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_code` (`code`),
    INDEX `idx_livre` (`livre_id`),
    INDEX `idx_utilise` (`utilise`),
    
    FOREIGN KEY (`livre_id`) REFERENCES `livres`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`utilisateur_id`) REFERENCES `utilisateurs`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- TABLE: achats_livres
-- Historique des achats de livres (tous canaux)
-- ═══════════════════════════════════════════════════════════════════════════
DROP TABLE IF EXISTS `achats_livres`;
CREATE TABLE `achats_livres` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `utilisateur_id` INT UNSIGNED NOT NULL,
    `livre_id` VARCHAR(50) NOT NULL,
    `canal` ENUM('site', 'amazon', 'cabinet', 'pharmacie', 'magasin', 'autre') NOT NULL,
    `prix_paye` DECIMAL(6,2) DEFAULT NULL,
    `reduction_appliquee` DECIMAL(5,2) DEFAULT 0,    -- Réduction parrainage
    `code_livre_utilise` VARCHAR(20) DEFAULT NULL,
    `woocommerce_order_id` INT UNSIGNED DEFAULT NULL,
    `parrain_id` INT UNSIGNED DEFAULT NULL,          -- Si achat via parrainage
    `date_achat` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_utilisateur` (`utilisateur_id`),
    INDEX `idx_livre` (`livre_id`),
    INDEX `idx_canal` (`canal`),
    INDEX `idx_parrain` (`parrain_id`),
    INDEX `idx_date` (`date_achat`),
    
    FOREIGN KEY (`utilisateur_id`) REFERENCES `utilisateurs`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`livre_id`) REFERENCES `livres`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`parrain_id`) REFERENCES `utilisateurs`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- TABLE: parrainages
-- Historique complet des parrainages (livres + apps)
-- ═══════════════════════════════════════════════════════════════════════════
DROP TABLE IF EXISTS `parrainages`;
CREATE TABLE `parrainages` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `parrain_id` INT UNSIGNED NOT NULL,
    `filleul_id` INT UNSIGNED DEFAULT NULL,
    `filleul_email` VARCHAR(255) NOT NULL,
    `type` ENUM('livre', 'app', 'mixte') DEFAULT 'livre',
    `statut` ENUM('invite', 'inscrit', 'converti', 'annule') DEFAULT 'invite',
    
    -- Avantages accordés
    `reduction_filleul` DECIMAL(5,2) DEFAULT 0,      -- -10% accordés
    `mois_parrain` INT UNSIGNED DEFAULT 0,           -- +3 mois accordés
    
    -- Tracking
    `livre_achete_id` VARCHAR(50) DEFAULT NULL,
    `canal_achat` VARCHAR(50) DEFAULT NULL,
    `montant_achat` DECIMAL(6,2) DEFAULT NULL,
    
    -- Commission ambassadeur
    `commission_calculee` DECIMAL(6,2) DEFAULT 0,
    `commission_versee` BOOLEAN DEFAULT FALSE,
    
    -- Dates
    `date_invitation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `date_inscription` DATETIME DEFAULT NULL,
    `date_conversion` DATETIME DEFAULT NULL,
    `annee` YEAR AS (YEAR(`date_conversion`)) STORED,
    
    INDEX `idx_parrain` (`parrain_id`),
    INDEX `idx_filleul` (`filleul_id`),
    INDEX `idx_statut` (`statut`),
    INDEX `idx_annee` (`annee`),
    
    FOREIGN KEY (`parrain_id`) REFERENCES `utilisateurs`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`filleul_id`) REFERENCES `utilisateurs`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`livre_achete_id`) REFERENCES `livres`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- TABLE: modules_utilisateurs
-- Modules d'application débloqués par utilisateur
-- ═══════════════════════════════════════════════════════════════════════════
DROP TABLE IF EXISTS `modules_utilisateurs`;
CREATE TABLE `modules_utilisateurs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `utilisateur_id` INT UNSIGNED NOT NULL,
    `module_id` VARCHAR(50) NOT NULL,                -- Ex: 'piqures', 'drainage'
    `livre_id` VARCHAR(50) DEFAULT NULL,             -- Livre qui a débloqué le module
    `date_activation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `date_expiration` DATE DEFAULT NULL,
    `actif` BOOLEAN DEFAULT TRUE,
    
    UNIQUE KEY `unique_user_module` (`utilisateur_id`, `module_id`),
    INDEX `idx_utilisateur` (`utilisateur_id`),
    INDEX `idx_module` (`module_id`),
    
    FOREIGN KEY (`utilisateur_id`) REFERENCES `utilisateurs`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`livre_id`) REFERENCES `livres`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- TABLE: commissions_ambassadeur
-- Suivi des commissions pour les ambassadeurs
-- ═══════════════════════════════════════════════════════════════════════════
DROP TABLE IF EXISTS `commissions_ambassadeur`;
CREATE TABLE `commissions_ambassadeur` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ambassadeur_id` INT UNSIGNED NOT NULL,
    `parrainage_id` INT UNSIGNED NOT NULL,
    `montant_vente` DECIMAL(8,2) NOT NULL,
    `taux_commission` DECIMAL(4,2) DEFAULT 15.00,
    `montant_commission` DECIMAL(8,2) NOT NULL,
    `statut` ENUM('en_attente', 'valide', 'verse', 'annule') DEFAULT 'en_attente',
    `date_vente` DATETIME NOT NULL,
    `date_validation` DATETIME DEFAULT NULL,
    `date_versement` DATETIME DEFAULT NULL,
    
    INDEX `idx_ambassadeur` (`ambassadeur_id`),
    INDEX `idx_statut` (`statut`),
    
    FOREIGN KEY (`ambassadeur_id`) REFERENCES `utilisateurs`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`parrainage_id`) REFERENCES `parrainages`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- TABLE: challenges_annuels
-- Suivi des challenges par utilisateur
-- ═══════════════════════════════════════════════════════════════════════════
DROP TABLE IF EXISTS `challenges_annuels`;
CREATE TABLE `challenges_annuels` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `utilisateur_id` INT UNSIGNED NOT NULL,
    `annee` YEAR NOT NULL,
    `objectif` INT UNSIGNED DEFAULT 5,
    `progression` INT UNSIGNED DEFAULT 0,
    `complete` BOOLEAN DEFAULT FALSE,
    `date_completion` DATETIME DEFAULT NULL,
    `recompense_livre_id` VARCHAR(50) DEFAULT NULL,  -- Livre choisi si challenge complété
    `bonus_mois_attribues` INT UNSIGNED DEFAULT 0,
    
    UNIQUE KEY `unique_user_annee` (`utilisateur_id`, `annee`),
    INDEX `idx_utilisateur` (`utilisateur_id`),
    
    FOREIGN KEY (`utilisateur_id`) REFERENCES `utilisateurs`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- TABLE: sessions
-- Sessions utilisateurs pour authentification
-- ═══════════════════════════════════════════════════════════════════════════
DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `utilisateur_id` INT UNSIGNED NOT NULL,
    `token` VARCHAR(255) NOT NULL UNIQUE,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(500) DEFAULT NULL,
    `date_creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `date_expiration` DATETIME NOT NULL,
    `actif` BOOLEAN DEFAULT TRUE,
    
    INDEX `idx_token` (`token`),
    INDEX `idx_utilisateur` (`utilisateur_id`),
    INDEX `idx_expiration` (`date_expiration`),
    
    FOREIGN KEY (`utilisateur_id`) REFERENCES `utilisateurs`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- TABLE: logs
-- Logs d'activité pour debugging et analytics
-- ═══════════════════════════════════════════════════════════════════════════
DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `type` ENUM('info', 'warning', 'error', 'debug') DEFAULT 'info',
    `action` VARCHAR(100) NOT NULL,
    `utilisateur_id` INT UNSIGNED DEFAULT NULL,
    `details` JSON DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `date_creation` DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_type` (`type`),
    INDEX `idx_action` (`action`),
    INDEX `idx_date` (`date_creation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- DONNÉES INITIALES
-- ═══════════════════════════════════════════════════════════════════════════

-- Insertion des livres (synchronisé avec config.php)
INSERT INTO `livres` (`id`, `titre`, `collection`, `description`, `prix`, `prix_amazon`, `icone`, `couleur`, `prefixe_code`, `woocommerce_id`, `statut`, `date_sortie`, `app_module`, `ordre`) VALUES
('homeo-uniciste', 'Connaissez-vous l\'Homéopathie Uniciste ?', 'Découverte', 'Introduction complète à l\'homéopathie uniciste et ses principes fondamentaux', 25.00, 25.00, '📖', '#2C5545', 'HOU', 2940, 'publie', '2024-01-01', NULL, 1),
('piqures', 'Piqûres & Morsures L\'ESSENTIEL', 'Ma Trousse Homéo Uniciste', 'Arbre de décision pour traiter piqûres d\'insectes et morsures', 19.00, 19.00, '🐝', '#E67E22', 'PIQ', 3575, 'publie', '2025-11-01', 'piqures', 2),
('drainage', 'Drainage Organique Homéopathique', 'Ma Trousse Homéo Uniciste', 'Protocoles de drainage pour les organes émonctoires', 35.00, 35.00, '🍃', '#27AE60', 'DRA', 3578, 'publie', '2026-01-15', 'drainage', 3),
('fievre-enfant-1', 'La Fièvre de l\'Enfant Tome 1', 'Ma Trousse Homéo Uniciste', 'Comprendre et accompagner la fièvre de l\'enfant avec l\'homéopathie', 25.00, 25.00, '🌡️', '#E74C3C', 'FIE', NULL, 'preparation', '2026-02-01', 'fievre-enfant', 4);

-- Générer quelques codes livres de test
INSERT INTO `codes_livres` (`code`, `livre_id`, `type`) VALUES
('HOU-TEST0001', 'homeo-uniciste', 'standard'),
('PIQ-TEST0001', 'piqures', 'standard'),
('DRA-TEST0001', 'drainage', 'standard'),
('FIE-TEST0001', 'fievre-enfant-1', 'standard');

SET FOREIGN_KEY_CHECKS = 1;

-- ═══════════════════════════════════════════════════════════════════════════
-- VUES UTILES
-- ═══════════════════════════════════════════════════════════════════════════

-- Vue: Statistiques parrainages par utilisateur
CREATE OR REPLACE VIEW `v_stats_parrainages` AS
SELECT 
    u.id,
    u.email,
    CONCAT(u.prenom, ' ', u.nom) as nom_complet,
    u.code_parrainage,
    u.nb_parrainages,
    u.statut_parrainage,
    u.mois_gratuits_cumules,
    u.date_expiration_app,
    COUNT(DISTINCT p.id) as total_invitations,
    COUNT(DISTINCT CASE WHEN p.statut = 'converti' THEN p.id END) as conversions,
    COALESCE(SUM(CASE WHEN p.statut = 'converti' THEN p.montant_achat END), 0) as ca_genere
FROM utilisateurs u
LEFT JOIN parrainages p ON u.id = p.parrain_id
GROUP BY u.id;

-- Vue: Statistiques ventes par canal
CREATE OR REPLACE VIEW `v_stats_canaux` AS
SELECT 
    canal,
    COUNT(*) as nb_ventes,
    SUM(prix_paye) as ca_total,
    AVG(prix_paye) as panier_moyen,
    COUNT(DISTINCT utilisateur_id) as nb_clients
FROM achats_livres
GROUP BY canal;

-- Vue: Statistiques par livre
CREATE OR REPLACE VIEW `v_stats_livres` AS
SELECT 
    l.id,
    l.titre,
    l.prix,
    COUNT(DISTINCT a.id) as nb_ventes,
    COALESCE(SUM(a.prix_paye), 0) as ca_total,
    COUNT(DISTINCT c.id) as codes_generes,
    COUNT(DISTINCT CASE WHEN c.utilise = 1 THEN c.id END) as codes_utilises
FROM livres l
LEFT JOIN achats_livres a ON l.id = a.livre_id
LEFT JOIN codes_livres c ON l.id = c.livre_id
GROUP BY l.id;