/**
 * Descomplicar® Crescimento Digital
 * https://descomplicar.pt
 */

<?php

/**
 * Desk_moloni_sync_log_model.php
 * 
 * Model for desk_moloni_sync_log table
 * Handles comprehensive audit log of all synchronization operations
 * 
 * @package DeskMoloni\Models
 * @author Database Design Specialist
 * @version 3.0
 */

defined('BASEPATH') or exit('No direct script access allowed');

require_once(dirname(__FILE__) . '/Desk_moloni_model.php');

class Desk_moloni_sync_log_model extends Desk_moloni_model
{
    /**
     * Table name - must match Perfex CRM naming convention
     */
    private $table = 'tbldeskmoloni_sync_log';
    
    /**
     * Valid operation types
     */
    private $validOperationTypes = [
        'create', 'update', 'delete', 'status_change', 'client_portal_access'
    ];
    
    /**
     * Valid entity types
     */
    private $validEntityTypes = [
        'client', 'product', 'invoice', 'estimate', 'credit_note', 'document'
    ];
    
    /**
     * Valid directions
     */
    private $validDirections = [
        'perfex_to_moloni', 'moloni_to_perfex', 'client_portal'
    ];
    
    /**
     * Valid status values
     */
    private $validStatuses = [
        'success', 'error', 'warning'
    ];
    
    public function __construct()
    {
        parent::__construct();
        // Use Perfex CRM table naming convention: tbl + module_prefix + table_name
        $this->table = 'tbldeskmoloni_sync_log';
    }
    
    /**
     * Log synchronization operation
     * 
     * @param string $operationType Operation type (create, update, delete, status_change)
     * @param string $entityType Entity type
     * @param int|null $perfexId Perfex entity ID
     * @param int|null $moloniId Moloni entity ID
     * @param string $direction Sync direction
     * @param string $status Operation status (success, error, warning)
     * @param array|null $requestData Request data
     * @param array|null $responseData Response data
     * @param string|null $errorMessage Error message if applicable
     * @param int|null $executionTimeMs Execution time in milliseconds
     * @return int|false Log entry ID or false on failure
     */
    public function logOperation(
        $operationType,
        $entityType,
        $perfexId,
        $moloniId,
        $direction,
        $status,
        $requestData = null,
        $responseData = null,
        $errorMessage = null,
        $executionTimeMs = null
    ) {
        try {
            $data = [
                'operation_type' => $operationType,
                'entity_type' => $entityType,
                'perfex_id' => $perfexId ? (int)$perfexId : null,
                'moloni_id' => $moloniId ? (int)$moloniId : null,
                'direction' => $direction,
                'status' => $status,
                'request_data' => $requestData ? json_encode($requestData) : null,
                'response_data' => $responseData ? json_encode($responseData) : null,
                'error_message' => $errorMessage,
                'execution_time_ms' => $executionTimeMs ? (int)$executionTimeMs : null,
                'created_at' => date('Y-m-d H:i:s')
            ];
            
            // Validate data
            $validationErrors = $this->validateLogData($data);
            if (!empty($validationErrors)) {
                throw new Exception('Validation failed: ' . implode(', ', $validationErrors));
            }
            
            $result = $this->db->insert($this->table, $data);
            
            if ($result) {
                return $this->db->insert_id();
            }
            
            return false;
            
        } catch (Exception $e) {
            log_message('error', 'Desk-Moloni sync log error: ' . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Log successful operation
     * 
     * @param string $operationType Operation type
     * @param string $entityType Entity type
     * @param int|null $perfexId Perfex entity ID
     * @param int|null $moloniId Moloni entity ID
     * @param string $direction Sync direction
     * @param array|null $requestData Request data
     * @param array|null $responseData Response data
     * @param int|null $executionTimeMs Execution time in milliseconds
     * @return int|false Log entry ID or false on failure
     */
    public function logSuccess(
        $operationType,
        $entityType,
        $perfexId,
        $moloniId,
        $direction,
        $requestData = null,
        $responseData = null,
        $executionTimeMs = null
    ) {
        return $this->logOperation(
            $operationType,
            $entityType,
            $perfexId,
            $moloniId,
            $direction,
            'success',
            $requestData,
            $responseData,
            null,
            $executionTimeMs
        );
    }
    
    /**
     * Log error operation
     * 
     * @param string $operationType Operation type
     * @param string $entityType Entity type
     * @param int|null $perfexId Perfex entity ID
     * @param int|null $moloniId Moloni entity ID
     * @param string $direction Sync direction
     * @param string $errorMessage Error message
     * @param array|null $requestData Request data
     * @param array|null $responseData Response data
     * @param int|null $executionTimeMs Execution time in milliseconds
     * @return int|false Log entry ID or false on failure
     */
    public function logError(
        $operationType,
        $entityType,
        $perfexId,
        $moloniId,
        $direction,
        $errorMessage,
        $requestData = null,
        $responseData = null,
        $executionTimeMs = null
    ) {
        return $this->logOperation(
            $operationType,
            $entityType,
            $perfexId,
            $moloniId,
            $direction,
            'error',
            $requestData,
            $responseData,
            $errorMessage,
            $executionTimeMs
        );
    }
    
    /**
     * Log warning operation
     * 
     * @param string $operationType Operation type
     * @param string $entityType Entity type
     * @param int|null $perfexId Perfex entity ID
     * @param int|null $moloniId Moloni entity ID
     * @param string $direction Sync direction
     * @param string $warningMessage Warning message
     * @param array|null $requestData Request data
     * @param array|null $responseData Response data
     * @param int|null $executionTimeMs Execution time in milliseconds
     * @return int|false Log entry ID or false on failure
     */
    public function logWarning(
        $operationType,
        $entityType,
        $perfexId,
        $moloniId,
        $direction,
        $warningMessage,
        $requestData = null,
        $responseData = null,
        $executionTimeMs = null
    ) {
        return $this->logOperation(
            $operationType,
            $entityType,
            $perfexId,
            $moloniId,
            $direction,
            'warning',
            $requestData,
            $responseData,
            $warningMessage,
            $executionTimeMs
        );
    }
    
    /**
     * Get log entries by entity
     * 
     * @param string $entityType Entity type
     * @param int|null $perfexId Perfex entity ID
     * @param int|null $moloniId Moloni entity ID
     * @param int $limit Maximum number of entries
     * @return array Array of log entries
     */
    public function getLogsByEntity($entityType, $perfexId = null, $moloniId = null, $limit = 50)
    {
        try {
            $this->db->where('entity_type', $entityType);
            
            if ($perfexId !== null) {
                $this->db->where('perfex_id', (int)$perfexId);
            }
            
            if ($moloniId !== null) {
                $this->db->where('moloni_id', (int)$moloniId);
            }
            
            $query = $this->db->order_by('created_at', 'DESC')
                             ->limit($limit)
                             ->get($this->table);
            
            return $query->result();
            
        } catch (Exception $e) {
            log_message('error', 'Desk-Moloni sync log get by entity error: ' . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get error logs within date range
     * 
     * @param string $startDate Start date (Y-m-d H:i:s)
     * @param string $endDate End date (Y-m-d H:i:s)
     * @param int $limit Maximum number of entries
     * @return array Array of error log entries
     */
    public function getErrorLogs($startDate = null, $endDate = null, $limit = 100)
    {
        try {
            $this->db->where('status', 'error');
            
            if ($startDate !== null) {
                $this->db->where('created_at >=', $startDate);
            }
            
            if ($endDate !== null) {
                $this->db->where('created_at <=', $endDate);
            }
            
            $query = $this->db->order_by('created_at', 'DESC')
                             ->limit($limit)
                             ->get($this->table);
            
            return $query->result();
            
        } catch (Exception $e) {
            log_message('error', 'Desk-Moloni sync log get errors error: ' . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get performance statistics
     * 
     * @param string $startDate Start date for analysis
     * @param string $endDate End date for analysis
     * @return array Performance statistics
     */
    public function getPerformanceStats($startDate = null, $endDate = null)
    {
        try {
            if ($startDate === null) {
                $startDate = date('Y-m-d H:i:s', strtotime('-24 hours'));
            }
            
            if ($endDate === null) {
                $endDate = date('Y-m-d H:i:s');
            }
            
            $stats = [];
            
            // Overall statistics
            $query = $this->db->select('
                COUNT(*) as total_operations,
                AVG(execution_time_ms) as avg_execution_time,
                MAX(execution_time_ms) as max_execution_time,
                MIN(execution_time_ms) as min_execution_time
            ')
            ->where('created_at >=', $startDate)
            ->where('created_at <=', $endDate)
            ->where('execution_time_ms IS NOT NULL')
            ->get($this->table);
            
            $stats['overall'] = $query->row_array();
            
            // By status
            foreach ($this->validStatuses as $status) {
                $count = $this->db->where('status', $status)
                                 ->where('created_at >=', $startDate)
                                 ->where('created_at <=', $endDate)
                                 ->count_all_results($this->table);
                $stats['by_status'][$status] = $count;
            }
            
            // By entity type
            foreach ($this->validEntityTypes as $entityType) {
                $count = $this->db->where('entity_type', $entityType)
                                 ->where('created_at >=', $startDate)
                                 ->where('created_at <=', $endDate)
                                 ->count_all_results($this->table);
                $stats['by_entity'][$entityType] = $count;
            }
            
            // By operation type
            foreach ($this->validOperationTypes as $operationType) {
                $count = $this->db->where('operation_type', $operationType)
                                 ->where('created_at >=', $startDate)
                                 ->where('created_at <=', $endDate)
                                 ->count_all_results($this->table);
                $stats['by_operation'][$operationType] = $count;
            }
            
            // By direction
            foreach ($this->validDirections as $direction) {
                $count = $this->db->where('direction', $direction)
                                 ->where('created_at >=', $startDate)
                                 ->where('created_at <=', $endDate)
                                 ->count_all_results($this->table);
                $stats['by_direction'][$direction] = $count;
            }
            
            // Slow operations (> 5 seconds)
            $stats['slow_operations'] = $this->db->where('execution_time_ms >', 5000)
                                                ->where('created_at >=', $startDate)
                                                ->where('created_at <=', $endDate)
                                                ->count_all_results($this->table);
            
            return $stats;
            
        } catch (Exception $e) {
            log_message('error', 'Desk-Moloni sync log performance stats error: ' . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get recent activity summary
     * 
     * @param int $hours Number of hours to look back
     * @param int $limit Maximum number of entries
     * @return array Recent activity log entries
     */
    public function getRecentActivity($hours = 24, $limit = 50)
    {
        try {
            $startDate = date('Y-m-d H:i:s', strtotime("-{$hours} hours"));
            
            $query = $this->db->where('created_at >=', $startDate)
                             ->order_by('created_at', 'DESC')
                             ->limit($limit)
                             ->get($this->table);
            
            return $query->result();
            
        } catch (Exception $e) {
            log_message('error', 'Desk-Moloni sync log recent activity error: ' . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Clean up old log entries
     * 
     * @param int $olderThanDays Delete logs older than X days
     * @param bool $keepErrors Whether to keep error logs longer
     * @return int Number of entries deleted
     */
    public function cleanupOldLogs($olderThanDays = 365, $keepErrors = true)
    {
        try {
            $cutoffDate = date('Y-m-d H:i:s', strtotime("-{$olderThanDays} days"));
            
            $this->db->where('created_at <', $cutoffDate);
            
            if ($keepErrors) {
                // Don't delete error logs
                $this->db->where('status !=', 'error');
            }
            
            $result = $this->db->delete($this->table);
            
            return $this->db->affected_rows();
            
        } catch (Exception $e) {
            log_message('error', 'Desk-Moloni sync log cleanup error: ' . $e->getMessage());
            return 0;
        }
    }
    
    /**
     * Get log entry by ID
     * 
     * @param int $logId Log entry ID
     * @return object|null Log entry or null if not found
     */
    public function getLogById($logId)
    {
        try {
            $query = $this->db->where('id', (int)$logId)->get($this->table);
            return $query->num_rows() > 0 ? $query->row() : null;
            
        } catch (Exception $e) {
            log_message('error', 'Desk-Moloni sync log get by ID error: ' . $e->getMessage());
            return null;
        }
    }
    
    /**
     * Search logs by criteria
     * 
     * @param array $criteria Search criteria
     * @param int $limit Maximum number of results
     * @param int $offset Offset for pagination
     * @return array Search results
     */
    public function searchLogs($criteria, $limit = 50, $offset = 0)
    {
        try {
            // Entity type filter
            if (!empty($criteria['entity_type'])) {
                $this->db->where('entity_type', $criteria['entity_type']);
            }
            
            // Status filter
            if (!empty($criteria['status'])) {
                $this->db->where('status', $criteria['status']);
            }
            
            // Operation type filter
            if (!empty($criteria['operation_type'])) {
                $this->db->where('operation_type', $criteria['operation_type']);
            }
            
            // Direction filter
            if (!empty($criteria['direction'])) {
                $this->db->where('direction', $criteria['direction']);
            }
            
            // Date range filter
            if (!empty($criteria['start_date'])) {
                $this->db->where('created_at >=', $criteria['start_date']);
            }
            
            if (!empty($criteria['end_date'])) {
                $this->db->where('created_at <=', $criteria['end_date']);
            }
            
            // Entity ID filters
            if (!empty($criteria['perfex_id'])) {
                $this->db->where('perfex_id', (int)$criteria['perfex_id']);
            }
            
            if (!empty($criteria['moloni_id'])) {
                $this->db->where('moloni_id', (int)$criteria['moloni_id']);
            }
            
            // Error message search
            if (!empty($criteria['error_message'])) {
                $this->db->like('error_message', $criteria['error_message']);
            }
            
            // Execution time filter
            if (!empty($criteria['min_execution_time'])) {
                $this->db->where('execution_time_ms >=', (int)$criteria['min_execution_time']);
            }
            
            if (!empty($criteria['max_execution_time'])) {
                $this->db->where('execution_time_ms <=', (int)$criteria['max_execution_time']);
            }
            
            $query = $this->db->order_by('created_at', 'DESC')
                             ->limit($limit, $offset)
                             ->get($this->table);
            
            return $query->result();
            
        } catch (Exception $e) {
            log_message('error', 'Desk-Moloni sync log search error: ' . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Export logs to CSV format
     * 
     * @param array $criteria Search criteria
     * @param int $limit Maximum number of records
     * @return string CSV data
     */
    public function exportToCsv($criteria = [], $limit = 1000)
    {
        try {
            $logs = $this->searchLogs($criteria, $limit);
            
            if (empty($logs)) {
                return '';
            }
            
            $csv = [];
            
            // Headers
            $csv[] = [
                'ID', 'Operation Type', 'Entity Type', 'Perfex ID', 'Moloni ID',
                'Direction', 'Status', 'Error Message', 'Execution Time (ms)', 'Created At'
            ];
            
            // Data rows
            foreach ($logs as $log) {
                $csv[] = [
                    $log->id,
                    $log->operation_type,
                    $log->entity_type,
                    $log->perfex_id ?: '',
                    $log->moloni_id ?: '',
                    $log->direction,
                    $log->status,
                    $log->error_message ?: '',
                    $log->execution_time_ms ?: '',
                    $log->created_at
                ];
            }
            
            // Convert to CSV string
            $output = '';
            foreach ($csv as $row) {
                $output .= '"' . implode('","', $row) . '"' . "\n";
            }
            
            return $output;
            
        } catch (Exception $e) {
            log_message('error', 'Desk-Moloni sync log export error: ' . $e->getMessage());
            return '';
        }
    }
    
    /**
     * Validate log data
     * 
     * @param array $data Log data to validate
     * @return array Validation errors
     */
    private function validateLogData($data)
    {
        $errors = [];
        
        // Required fields
        $requiredFields = ['operation_type', 'entity_type', 'direction', 'status'];
        $errors = array_merge($errors, $this->validateRequiredFields($data, $requiredFields));
        
        // Operation type validation
        if (isset($data['operation_type']) && !$this->validateEnum($data['operation_type'], $this->validOperationTypes)) {
            $errors[] = 'Invalid operation type. Must be one of: ' . implode(', ', $this->validOperationTypes);
        }
        
        // Entity type validation
        if (isset($data['entity_type']) && !$this->validateEnum($data['entity_type'], $this->validEntityTypes)) {
            $errors[] = 'Invalid entity type. Must be one of: ' . implode(', ', $this->validEntityTypes);
        }
        
        // Direction validation
        if (isset($data['direction']) && !$this->validateEnum($data['direction'], $this->validDirections)) {
            $errors[] = 'Invalid direction. Must be one of: ' . implode(', ', $this->validDirections);
        }
        
        // Status validation
        if (isset($data['status']) && !$this->validateEnum($data['status'], $this->validStatuses)) {
            $errors[] = 'Invalid status. Must be one of: ' . implode(', ', $this->validStatuses);
        }
        
        // Entity ID validation - at least one must be present
        if (empty($data['perfex_id']) && empty($data['moloni_id'])) {
            $errors[] = 'At least one of perfex_id or moloni_id must be provided';
        }
        
        // Execution time validation
        if (isset($data['execution_time_ms']) && $data['execution_time_ms'] !== null) {
            if (!is_numeric($data['execution_time_ms']) || (int)$data['execution_time_ms'] < 0) {
                $errors[] = 'Execution time must be a non-negative integer';
            }
        }
        
        // JSON validation
        if (isset($data['request_data']) && !$this->validateJSON($data['request_data'])) {
            $errors[] = 'Request data must be valid JSON';
        }
        
        if (isset($data['response_data']) && !$this->validateJSON($data['response_data'])) {
            $errors[] = 'Response data must be valid JSON';
        }
        
        return $errors;
    }
    
    /**
     * Get valid operation types
     * 
     * @return array Valid operation types
     */
    public function getValidOperationTypes()
    {
        return $this->validOperationTypes;
    }
    
    /**
     * Get valid entity types
     * 
     * @return array Valid entity types
     */
    public function getValidEntityTypes()
    {
        return $this->validEntityTypes;
    }
    
    /**
     * Get valid directions
     * 
     * @return array Valid directions
     */
    public function getValidDirections()
    {
        return $this->validDirections;
    }
    
    /**
     * Get valid status values
     * 
     * @return array Valid status values
     */
    public function getValidStatuses()
    {
        return $this->validStatuses;
    }

    /**
     * Log client portal access for audit trail
     * 
     * @param array $logData Client portal access data
     * @return int|false Log entry ID or false on failure
     */
    public function logClientPortalAccess($logData)
    {
        try {
            $data = [
                'operation_type' => 'client_portal_access',
                'entity_type' => 'document',
                'perfex_id' => $logData['document_id'] ?? null,
                'moloni_id' => null,
                'direction' => 'client_portal',
                'status' => $logData['status'] ?? 'success',
                'request_data' => json_encode([
                    'client_id' => $logData['client_id'],
                    'action' => $logData['action'],
                    'ip_address' => $logData['ip_address'],
                    'user_agent' => $logData['user_agent']
                ]),
                'response_data' => null,
                'error_message' => $logData['error_message'] ?? null,
                'execution_time_ms' => null,
                'created_at' => $logData['timestamp'] ?? date('Y-m-d H:i:s')
            ];
            
            return $this->db->insert($this->table, $data) ? $this->db->insert_id() : false;
            
        } catch (Exception $e) {
            log_message('error', 'Client portal access log error: ' . $e->getMessage());
            return false;
        }
    }

    /**
     * Get client portal access logs
     * 
     * @param int $clientId Client ID
     * @param array $filters Optional filters
     * @param int $limit Maximum number of entries
     * @return array Client portal access logs
     */
    public function getClientPortalAccessLogs($clientId, array $filters = [], $limit = 50)
    {
        try {
            $this->db->where('operation_type', 'client_portal_access');
            $this->db->like('request_data', '"client_id":' . $clientId);
            
            // Apply filters
            if (isset($filters['action'])) {
                $this->db->like('request_data', '"action":"' . $filters['action'] . '"');
            }
            
            if (isset($filters['status'])) {
                $this->db->where('status', $filters['status']);
            }
            
            if (isset($filters['start_date'])) {
                $this->db->where('created_at >=', $filters['start_date']);
            }
            
            if (isset($filters['end_date'])) {
                $this->db->where('created_at <=', $filters['end_date']);
            }
            
            $query = $this->db->order_by('created_at', 'DESC')
                             ->limit($limit)
                             ->get($this->table);
            
            return $query->result();
            
        } catch (Exception $e) {
            log_message('error', 'Get client portal access logs error: ' . $e->getMessage());
            return [];
        }
    }

    /**
     * Get client portal access statistics
     * 
     * @param int $clientId Client ID
     * @param string $period Period for statistics (day, week, month)
     * @return array Access statistics
     */
    public function getClientPortalAccessStats($clientId, $period = 'week')
    {
        try {
            $startDate = date('Y-m-d H:i:s', strtotime('-1 ' . $period));
            
            $stats = [
                'total_accesses' => 0,
                'successful_accesses' => 0,
                'failed_accesses' => 0,
                'actions' => [],
                'documents_accessed' => 0
            ];
            
            // Total accesses
            $stats['total_accesses'] = $this->db->where('operation_type', 'client_portal_access')
                                               ->like('request_data', '"client_id":' . $clientId)
                                               ->where('created_at >=', $startDate)
                                               ->count_all_results($this->table);
            
            // Successful accesses
            $stats['successful_accesses'] = $this->db->where('operation_type', 'client_portal_access')
                                                   ->like('request_data', '"client_id":' . $clientId)
                                                   ->where('status', 'success')
                                                   ->where('created_at >=', $startDate)
                                                   ->count_all_results($this->table);
            
            // Failed accesses
            $stats['failed_accesses'] = $stats['total_accesses'] - $stats['successful_accesses'];
            
            // Actions breakdown
            $logs = $this->getClientPortalAccessLogs($clientId, ['start_date' => $startDate], 1000);
            $actionCounts = [];
            $documentIds = [];
            
            foreach ($logs as $log) {
                $requestData = json_decode($log->request_data, true);
                if ($requestData && isset($requestData['action'])) {
                    $action = $requestData['action'];
                    $actionCounts[$action] = ($actionCounts[$action] ?? 0) + 1;
                    
                    if ($log->perfex_id) {
                        $documentIds[] = $log->perfex_id;
                    }
                }
            }
            
            $stats['actions'] = $actionCounts;
            $stats['documents_accessed'] = count(array_unique($documentIds));
            
            return $stats;
            
        } catch (Exception $e) {
            log_message('error', 'Get client portal access stats error: ' . $e->getMessage());
            return [];
        }
    }

    /**
     * Clean up old client portal logs
     * 
     * @param int $olderThanDays Delete logs older than X days
     * @return int Number of entries deleted
     */
    public function cleanupClientPortalLogs($olderThanDays = 90)
    {
        try {
            $cutoffDate = date('Y-m-d H:i:s', strtotime("-{$olderThanDays} days"));
            
            $this->db->where('operation_type', 'client_portal_access')
                     ->where('created_at <', $cutoffDate);
            
            $result = $this->db->delete($this->table);
            
            return $this->db->affected_rows();
            
        } catch (Exception $e) {
            log_message('error', 'Cleanup client portal logs error: ' . $e->getMessage());
            return 0;
        }
    }
    
    /**
     * Get logs with related data using JOIN queries (optimized)
     * Prevents N+1 query problem
     * 
     * @param int $limit Number of records to return
     * @param int $offset Starting offset
     * @param array $filters Additional filters
     * @return array Logs with related data
     */
    public function get_logs_with_details($limit = 50, $offset = 0, $filters = [])
    {
        // Align to actual schema/columns and table names (tbldeskmoloni_*)
        $this->db->select('
            sl.id,
            sl.entity_type,
            sl.perfex_id as entity_id,
            sl.operation_type as action,
            sl.status,
            sl.error_message,
            sl.execution_time_ms as execution_time,
            sl.created_at,
            dm.moloni_id,
            dm.perfex_id as mapping_perfex_id,
            dm.entity_type as mapping_entity_type,
            dm.sync_status as mapping_sync_status,
            dm.last_sync_at as mapping_last_sync
        ');
        $this->db->from($this->table . ' sl');
        // LEFT JOINs to include related data
        $this->db->join('tbldeskmoloni_sync_queue sq', '1=0', 'left'); // queue relation not available in schema
        $this->db->join('tbldeskmoloni_mapping dm', 'sl.perfex_id = dm.perfex_id AND sl.entity_type = dm.entity_type', 'left');
        
        // Apply filters
        if (!empty($filters['entity_type'])) {
            $this->db->where('sl.entity_type', $filters['entity_type']);
        }
        
        if (!empty($filters['status'])) {
            $this->db->where('sl.status', $filters['status']);
        }
        
        if (!empty($filters['date_from'])) {
            $this->db->where('sl.created_at >=', $filters['date_from']);
        }
        
        if (!empty($filters['date_to'])) {
            $this->db->where('sl.created_at <=', $filters['date_to']);
        }
        
        // Ordering and pagination
        $this->db->order_by('sl.created_at', 'DESC');
        $this->db->limit($limit, $offset);
        
        $query = $this->db->get();
        $results = $query->result_array();
        
        desk_moloni_log('debug', "Fetched logs with details", [
            'count' => count($results),
            'limit' => $limit,
            'offset' => $offset,
            'filters' => $filters
        ], 'performance');
        
        return $results;
    }
    
    /**
     * Get sync statistics with single query
     * 
     * @param array $filters Date range and entity filters
     * @return array Statistics grouped by status, entity type, etc.
     */
    public function get_sync_statistics($filters = [])
    {
        // Build statistics query
        $this->db->select('
            COUNT(*) as total_syncs,
            SUM(CASE WHEN status = "success" THEN 1 ELSE 0 END) as successful_syncs,
            SUM(CASE WHEN status = "error" THEN 1 ELSE 0 END) as failed_syncs,
            SUM(CASE WHEN status = "pending" THEN 1 ELSE 0 END) as pending_syncs,
            AVG(execution_time_ms) as avg_execution_time,
            MAX(execution_time_ms) as max_execution_time,
            MIN(execution_time_ms) as min_execution_time,
            entity_type,
            DATE(created_at) as sync_date
        ');
        
        $this->db->from($this->table);
        
        // Apply date filters
        if (!empty($filters['date_from'])) {
            $this->db->where('created_at >=', $filters['date_from']);
        }
        
        if (!empty($filters['date_to'])) {
            $this->db->where('created_at <=', $filters['date_to']);
        }
        
        $this->db->group_by(['entity_type', 'DATE(created_at)']);
        $this->db->order_by('sync_date', 'DESC');
        
        $query = $this->db->get();
        return $query->result_array();
    }
    
    /**
     * Get recent activity with minimal data for dashboard
     * Optimized for speed
     * 
     * @param int $limit Number of recent activities
     * @return array Recent sync activities
     */
    public function get_recent_activity($limit = 10)
    {
        try {
            // Check if table exists first
            if (!$this->db->table_exists($this->table)) {
                log_message('info', 'Desk-Moloni sync log table does not exist yet');
                return [];
            }
            
            $this->db->reset_query();
            $this->db->select('
                entity_type,
                perfex_id as entity_id, 
                operation_type as action,
                status,
                created_at,
                execution_time_ms,
                direction,
                moloni_id
            ');
            
            $this->db->from($this->table);
            $this->db->where('created_at >', date('Y-m-d H:i:s', strtotime('-24 hours')));
            $this->db->order_by('created_at', 'DESC');
            $this->db->limit($limit);
            
            $query = $this->db->get();
            return $query->result_array();
            
        } catch (Exception $e) {
            log_message('error', 'Desk-Moloni get_recent_activity error: ' . $e->getMessage());
            return [];
        }
    }
}