-- ==============================================================================
-- Migration: Notifications, Messagerie, RGPD
-- Date: 2026-01-26
-- ==============================================================================

-- ==============================================================================
-- TABLE: notifications (Notifications utilisateurs)
-- ==============================================================================
CREATE TABLE IF NOT EXISTS notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL COMMENT 'Destinataire',
    type VARCHAR(50) NOT NULL COMMENT 'Type: inspection_assigned, report_ready, message, system...',
    title VARCHAR(255) NOT NULL,
    message TEXT,
    data JSON COMMENT 'Données additionnelles (lien, entité concernée...)',
    icon VARCHAR(50) DEFAULT 'bell' COMMENT 'Icône Bootstrap',
    color VARCHAR(20) DEFAULT 'primary' COMMENT 'Couleur: primary, success, warning, danger...',
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    link VARCHAR(500) COMMENT 'Lien vers la ressource concernée',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL COMMENT 'Date d\'expiration auto',
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_unread (user_id, is_read, created_at),
    INDEX idx_type (type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: conversations (Conversations messagerie)
-- ==============================================================================
CREATE TABLE IF NOT EXISTS conversations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) COMMENT 'Titre optionnel (pour groupes)',
    type ENUM('direct', 'group', 'inspection', 'order') DEFAULT 'direct',
    entity_type VARCHAR(50) COMMENT 'Type entité liée: inspection, order, client...',
    entity_id INT UNSIGNED COMMENT 'ID entité liée',
    created_by INT UNSIGNED,
    last_message_at TIMESTAMP NULL,
    last_message_preview VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_last_message (last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: conversation_participants (Participants aux conversations)
-- ==============================================================================
CREATE TABLE IF NOT EXISTS conversation_participants (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    role ENUM('member', 'admin') DEFAULT 'member',
    is_muted BOOLEAN DEFAULT FALSE,
    last_read_at TIMESTAMP NULL,
    unread_count INT UNSIGNED DEFAULT 0,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    left_at TIMESTAMP NULL,
    
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uk_conversation_user (conversation_id, user_id),
    INDEX idx_user_conversations (user_id, left_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: messages (Messages)
-- ==============================================================================
CREATE TABLE IF NOT EXISTS messages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT UNSIGNED NOT NULL,
    sender_id INT UNSIGNED COMMENT 'NULL pour messages système',
    content TEXT NOT NULL,
    type ENUM('text', 'file', 'image', 'system') DEFAULT 'text',
    file_path VARCHAR(500),
    file_name VARCHAR(255),
    file_size INT UNSIGNED,
    mentions JSON COMMENT 'Liste des @mentions user_ids',
    is_edited BOOLEAN DEFAULT FALSE,
    edited_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_conversation_date (conversation_id, created_at),
    INDEX idx_sender (sender_id),
    FULLTEXT idx_content (content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: message_reads (Lectures des messages - pour groupe)
-- ==============================================================================
CREATE TABLE IF NOT EXISTS message_reads (
    message_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (message_id, user_id),
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: gdpr_requests (Demandes RGPD)
-- ==============================================================================
CREATE TABLE IF NOT EXISTS gdpr_requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED COMMENT 'Utilisateur concerné',
    request_type ENUM('access', 'export', 'deletion', 'rectification', 'restriction') NOT NULL,
    status ENUM('pending', 'in_progress', 'completed', 'rejected') DEFAULT 'pending',
    requester_email VARCHAR(255) NULL COMMENT 'Email du demandeur (si externe)',
    requester_name VARCHAR(255),
    requester_proof VARCHAR(500) COMMENT 'Pièce justificative',
    notes TEXT,
    processed_by INT UNSIGNED,
    processed_at TIMESTAMP NULL,
    export_file VARCHAR(500) COMMENT 'Chemin fichier export',
    export_expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_user (user_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- TABLE: data_retention_rules (Règles de rétention des données)
-- ==============================================================================
CREATE TABLE IF NOT EXISTS data_retention_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    data_type VARCHAR(50) NOT NULL COMMENT 'Type: users, inspections, reports...',
    description VARCHAR(255) COMMENT 'Description de la règle',
    retention_days INT UNSIGNED NOT NULL COMMENT 'Durée en jours avant anonymisation',
    action ENUM('anonymize', 'delete', 'archive') DEFAULT 'anonymize',
    is_active BOOLEAN DEFAULT TRUE,
    last_run_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_data_type (data_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Règles par défaut (10 ans pour données comptables/légales)
INSERT INTO data_retention_rules (data_type, description, retention_days, action) VALUES
('audit_logs', 'Logs d\'audit et traçabilité', 3650, 'archive'),
('inspections', 'Inspections et rapports', 3650, 'anonymize'),
('messages', 'Messages de la messagerie interne', 1825, 'delete'),
('notifications', 'Notifications utilisateurs', 90, 'delete')
ON DUPLICATE KEY UPDATE retention_days = VALUES(retention_days);

-- ==============================================================================
-- Ajouter colonnes RGPD à users si pas présentes
-- ==============================================================================
-- ALTER TABLE users ADD COLUMN IF NOT EXISTS anonymized_at TIMESTAMP NULL;
-- ALTER TABLE users ADD COLUMN IF NOT EXISTS data_exported_at TIMESTAMP NULL;
