-- ============================================
-- Table: cupidose_banque_ressources
-- Description: Gestion de la banque de ressources (images, vidéos, fichiers)
-- Date: 2026-01-13
-- ============================================

CREATE TABLE IF NOT EXISTS `cupidose_banque_ressources` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nom_fichier` VARCHAR(255) NOT NULL COMMENT 'Nom original du fichier',
    `nom_stockage` VARCHAR(255) NOT NULL COMMENT 'Nom du fichier sur le serveur',
    `chemin_relatif` VARCHAR(500) NOT NULL COMMENT 'Chemin relatif depuis uploads/ressources/',
    `chemin_complet` VARCHAR(500) NOT NULL COMMENT 'Chemin complet du fichier',
    `type_fichier` ENUM('image', 'video', 'document', 'autre') NOT NULL DEFAULT 'autre',
    `extension` VARCHAR(10) NOT NULL,
    `taille_octets` BIGINT(20) NOT NULL DEFAULT 0,
    `taille_formatee` VARCHAR(20) NULL COMMENT 'Taille lisible (ex: 2.5 MB)',
    
    -- Métadonnées
    `description` TEXT NULL COMMENT 'Description du fichier',
    `tags` VARCHAR(500) NULL COMMENT 'Tags séparés par des virgules',
    `categorie` VARCHAR(100) NULL COMMENT 'Catégorie du fichier',
    `dossier_parent` VARCHAR(500) NULL COMMENT 'Chemin du dossier parent',
    
    -- Informations utilisateur
    `utilisateur_id` INT(11) NOT NULL COMMENT 'ID de l\'utilisateur qui a uploadé',
    `utilisateur_nom` VARCHAR(255) NOT NULL COMMENT 'Nom de l\'utilisateur',
    `date_creation` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `date_modification` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    `modifie_par_id` INT(11) NULL COMMENT 'ID du dernier utilisateur qui a modifié',
    `modifie_par_nom` VARCHAR(255) NULL COMMENT 'Nom du dernier utilisateur',
    
    -- Permissions et partage
    `est_public` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=tous les collaborateurs, 0=restreint',
    `partage_avec` TEXT NULL COMMENT 'JSON des IDs utilisateurs autorisés si restreint',
    `roles_autorises` VARCHAR(255) NULL COMMENT 'Rôles autorisés (Entreprise,Dirigeant,etc)',
    
    -- Statut
    `est_actif` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=actif, 0=supprimé (soft delete)',
    `date_suppression` DATETIME NULL,
    `supprime_par_id` INT(11) NULL,
    `supprime_par_nom` VARCHAR(255) NULL,
    
    -- Statistiques
    `nb_telechargements` INT(11) NOT NULL DEFAULT 0,
    `derniere_consultation` DATETIME NULL,
    
    PRIMARY KEY (`id`),
    KEY `idx_utilisateur` (`utilisateur_id`),
    KEY `idx_type` (`type_fichier`),
    KEY `idx_dossier` (`dossier_parent`),
    KEY `idx_actif` (`est_actif`),
    KEY `idx_date_creation` (`date_creation`),
    KEY `idx_nom_fichier` (`nom_fichier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Banque de ressources - Fichiers et métadonnées';

-- ============================================
-- Table: cupidose_banque_ressources_historique
-- Description: Historique de toutes les actions sur les fichiers
-- ============================================

CREATE TABLE IF NOT EXISTS `cupidose_banque_ressources_historique` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `ressource_id` INT(11) NULL COMMENT 'ID du fichier concerné (NULL si dossier)',
    `type_action` ENUM('upload', 'suppression', 'deplacement', 'telechargement', 'modification', 'creation_dossier', 'suppression_dossier') NOT NULL,
    `description` TEXT NOT NULL COMMENT 'Description détaillée de l\'action',
    `donnees_avant` TEXT NULL COMMENT 'JSON des données avant modification',
    `donnees_apres` TEXT NULL COMMENT 'JSON des données après modification',
    `chemin_source` VARCHAR(500) NULL,
    `chemin_destination` VARCHAR(500) NULL,
    `utilisateur_id` INT(11) NOT NULL,
    `utilisateur_nom` VARCHAR(255) NOT NULL,
    `adresse_ip` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `date_action` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    KEY `idx_ressource` (`ressource_id`),
    KEY `idx_utilisateur` (`utilisateur_id`),
    KEY `idx_type_action` (`type_action`),
    KEY `idx_date` (`date_action`),
    FOREIGN KEY (`ressource_id`) REFERENCES `cupidose_banque_ressources` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Historique des actions sur la banque de ressources';

-- ============================================
-- Table: cupidose_banque_ressources_dossiers
-- Description: Gestion des dossiers de la banque de ressources
-- ============================================

CREATE TABLE IF NOT EXISTS `cupidose_banque_ressources_dossiers` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nom_dossier` VARCHAR(255) NOT NULL,
    `chemin_relatif` VARCHAR(500) NOT NULL COMMENT 'Chemin depuis uploads/ressources/',
    `chemin_complet` VARCHAR(500) NOT NULL,
    `dossier_parent` VARCHAR(500) NULL COMMENT 'Chemin du parent (NULL si racine)',
    `description` TEXT NULL,
    `couleur` VARCHAR(7) NULL COMMENT 'Code couleur hex pour l\'affichage',
    `icone` VARCHAR(50) NULL COMMENT 'Emoji ou classe icône',
    
    -- Permissions
    `est_public` TINYINT(1) NOT NULL DEFAULT 1,
    `partage_avec` TEXT NULL COMMENT 'JSON des IDs utilisateurs',
    `roles_autorises` VARCHAR(255) NULL,
    
    -- Métadonnées
    `utilisateur_id` INT(11) NOT NULL,
    `utilisateur_nom` VARCHAR(255) NOT NULL,
    `date_creation` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `date_modification` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    `modifie_par_id` INT(11) NULL,
    `modifie_par_nom` VARCHAR(255) NULL,
    
    -- Statut
    `est_actif` TINYINT(1) NOT NULL DEFAULT 1,
    `date_suppression` DATETIME NULL,
    `supprime_par_id` INT(11) NULL,
    
    -- Statistiques
    `nb_fichiers` INT(11) NOT NULL DEFAULT 0 COMMENT 'Nombre de fichiers dans ce dossier',
    `taille_totale` BIGINT(20) NOT NULL DEFAULT 0 COMMENT 'Taille totale en octets',
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_chemin_unique` (`chemin_relatif`),
    KEY `idx_parent` (`dossier_parent`),
    KEY `idx_utilisateur` (`utilisateur_id`),
    KEY `idx_actif` (`est_actif`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Dossiers de la banque de ressources';

-- ============================================
-- Vue: vue_ressources_avec_utilisateur
-- Description: Vue combinant ressources et infos utilisateur
-- ============================================

CREATE OR REPLACE VIEW `vue_ressources_avec_utilisateur` AS
SELECT 
    r.*,
    u.nom as createur_nom_complet,
    u.prenom as createur_prenom,
    u.email as createur_email,
    COUNT(h.id) as nb_actions_historique
FROM 
    cupidose_banque_ressources r
LEFT JOIN 
    utilisateurs u ON r.utilisateur_id = u.id
LEFT JOIN 
    cupidose_banque_ressources_historique h ON r.id = h.ressource_id
WHERE 
    r.est_actif = 1
GROUP BY 
    r.id;
