<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Data_sync_helper {
    
    private $CI;
    private $offline_db;
    private $online_db;
    
    public function __construct() {
        $this->CI =& get_instance();
        $this->CI->load->database();
        $this->CI->load->helper('dual_mode');
    }
    
    /**
     * Initialize database connections
     */
    private function init_connections() {
        // Get database configurations
        $offline_config = $this->CI->dual_mode_helper->get_database_config('offline');
        $online_config = $this->CI->dual_mode_helper->get_database_config('online');
        
        // Create offline connection
        $this->offline_db = new mysqli(
            $offline_config['hostname'],
            $offline_config['username'],
            $offline_config['password'],
            $offline_config['database']
        );
        
        // Create online connection
        $this->online_db = new mysqli(
            $online_config['hostname'],
            $online_config['username'],
            $online_config['password'],
            $online_config['database']
        );
        
        if ($this->offline_db->connect_error || $this->online_db->connect_error) {
            return false;
        }
        
        return true;
    }
    
    /**
     * Check if online database is accessible
     */
    public function is_online_accessible() {
        $online_config = $this->CI->dual_mode_helper->get_database_config('online');
        
        $connection = @mysqli_connect(
            $online_config['hostname'],
            $online_config['username'],
            $online_config['password'],
            $online_config['database']
        );
        
        if ($connection) {
            mysqli_close($connection);
            return true;
        }
        
        return false;
    }
    
    /**
     * Sync data from offline to online
     */
    public function sync_offline_to_online($tables = null) {
        if (!$this->init_connections()) {
            return ['success' => false, 'message' => 'Failed to initialize database connections'];
        }
        
        // Get tables to sync (default: all tables)
        if ($tables === null) {
            $tables = $this->get_syncable_tables();
        }
        
        $sync_results = [];
        $total_synced = 0;
        
        foreach ($tables as $table) {
            $result = $this->sync_table($table, 'offline_to_online');
            $sync_results[$table] = $result;
            
            if ($result['success']) {
                $total_synced += $result['records_synced'];
            }
        }
        
        // Update sync timestamp
        $this->update_sync_timestamp();
        
        return [
            'success' => true,
            'message' => "Sync completed. {$total_synced} records synced.",
            'details' => $sync_results
        ];
    }
    
    /**
     * Sync data from online to offline
     */
    public function sync_online_to_offline($tables = null) {
        if (!$this->init_connections()) {
            return ['success' => false, 'message' => 'Failed to initialize database connections'];
        }
        
        // Get tables to sync (default: all tables)
        if ($tables === null) {
            $tables = $this->get_syncable_tables();
        }
        
        $sync_results = [];
        $total_synced = 0;
        
        foreach ($tables as $table) {
            $result = $this->sync_table($table, 'online_to_offline');
            $sync_results[$table] = $result;
            
            if ($result['success']) {
                $total_synced += $result['records_synced'];
            }
        }
        
        // Update sync timestamp
        $this->update_sync_timestamp();
        
        return [
            'success' => true,
            'message' => "Sync completed. {$total_synced} records synced.",
            'details' => $sync_results
        ];
    }
    
    /**
     * Sync a specific table
     */
    private function sync_table($table, $direction) {
        try {
            if ($direction === 'offline_to_online') {
                $source_db = $this->offline_db;
                $target_db = $this->online_db;
            } else {
                $source_db = $this->online_db;
                $target_db = $this->offline_db;
            }
            
            // Get records from source that are newer than last sync
            $last_sync = $this->get_last_sync_time();
            $query = "SELECT * FROM `{$table}` WHERE `updated_at` > '{$last_sync}' OR `created_at` > '{$last_sync}'";
            
            $result = $source_db->query($query);
            if (!$result) {
                return ['success' => false, 'message' => "Failed to query source table: {$table}"];
            }
            
            $records_synced = 0;
            
            while ($row = $result->fetch_assoc()) {
                // Check if record exists in target
                $check_query = "SELECT id FROM `{$table}` WHERE id = {$row['id']}";
                $check_result = $target_db->query($check_query);
                
                if ($check_result && $check_result->num_rows > 0) {
                    // Update existing record
                    $update_query = $this->build_update_query($table, $row);
                    if ($target_db->query($update_query)) {
                        $records_synced++;
                    }
                } else {
                    // Insert new record
                    $insert_query = $this->build_insert_query($table, $row);
                    if ($target_db->query($insert_query)) {
                        $records_synced++;
                    }
                }
            }
            
            return [
                'success' => true,
                'records_synced' => $records_synced,
                'message' => "Synced {$records_synced} records from {$table}"
            ];
            
        } catch (Exception $e) {
            return [
                'success' => false,
                'message' => "Error syncing table {$table}: " . $e->getMessage()
            ];
        }
    }
    
    /**
     * Build UPDATE query
     */
    private function build_update_query($table, $data) {
        $set_parts = [];
        foreach ($data as $column => $value) {
            if ($column !== 'id') {
                $escaped_value = mysqli_real_escape_string($this->online_db, $value);
                $set_parts[] = "`{$column}` = '{$escaped_value}'";
            }
        }
        
        return "UPDATE `{$table}` SET " . implode(', ', $set_parts) . " WHERE id = {$data['id']}";
    }
    
    /**
     * Build INSERT query
     */
    private function build_insert_query($table, $data) {
        $columns = array_keys($data);
        $values = array_values($data);
        
        $escaped_values = array_map(function($value) {
            return "'" . mysqli_real_escape_string($this->online_db, $value) . "'";
        }, $values);
        
        return "INSERT INTO `{$table}` (`" . implode('`, `', $columns) . "`) VALUES (" . implode(', ', $escaped_values) . ")";
    }
    
    /**
     * Get tables that can be synced
     */
    private function get_syncable_tables() {
        // Define tables that should be synced
        $syncable_tables = [
            'db_users',
            'db_customers',
            'db_products',
            'db_sales',
            'db_sale_items',
            'db_inventory',
            'db_suppliers',
            'db_purchases',
            'db_expenses',
            'db_settings'
        ];
        
        return $syncable_tables;
    }
    
    /**
     * Get last sync time
     */
    private function get_last_sync_time() {
        $setting = $this->CI->db->where('setting_key', 'last_sync_time')->get('db_system_settings')->row();
        return $setting ? $setting->setting_value : '1970-01-01 00:00:00';
    }
    
    /**
     * Update sync timestamp
     */
    private function update_sync_timestamp() {
        $this->CI->db->where('setting_key', 'last_sync_time')->update('db_system_settings', [
            'setting_value' => date('Y-m-d H:i:s')
        ]);
    }
    
    /**
     * Auto-sync when switching to online mode
     */
    public function auto_sync_on_online() {
        $current_mode = $this->CI->dual_mode_helper->get_current_mode();
        
        if ($current_mode === 'online' && $this->is_online_accessible()) {
            // Check if auto-sync is enabled
            $auto_sync_setting = $this->CI->db->where('setting_key', 'auto_sync_on_online')->get('db_system_settings')->row();
            
            if ($auto_sync_setting && $auto_sync_setting->setting_value === '1') {
                return $this->sync_offline_to_online();
            }
        }
        
        return ['success' => false, 'message' => 'Auto-sync not enabled or conditions not met'];
    }
    
    /**
     * Manual sync with conflict resolution
     */
    public function manual_sync_with_conflicts($direction = 'offline_to_online') {
        if (!$this->init_connections()) {
            return ['success' => false, 'message' => 'Failed to initialize database connections'];
        }
        
        $conflicts = [];
        $sync_results = [];
        
        $tables = $this->get_syncable_tables();
        
        foreach ($tables as $table) {
            $conflicts[$table] = $this->detect_conflicts($table);
            
            if (empty($conflicts[$table])) {
                // No conflicts, proceed with sync
                $result = $this->sync_table($table, $direction);
                $sync_results[$table] = $result;
            } else {
                // Has conflicts, require manual resolution
                $sync_results[$table] = [
                    'success' => false,
                    'message' => 'Conflicts detected, manual resolution required',
                    'conflicts' => $conflicts[$table]
                ];
            }
        }
        
        return [
            'success' => true,
            'message' => 'Sync completed with conflict detection',
            'results' => $sync_results,
            'conflicts' => $conflicts
        ];
    }
    
    /**
     * Detect conflicts between databases
     */
    private function detect_conflicts($table) {
        $conflicts = [];
        
        // Get records that exist in both databases but have different updated_at timestamps
        $query = "
            SELECT o.id, o.updated_at as offline_updated, n.updated_at as online_updated
            FROM `{$table}` o
            INNER JOIN `{$table}` n ON o.id = n.id
            WHERE o.updated_at != n.updated_at
        ";
        
        $result = $this->offline_db->query($query);
        
        while ($row = $result->fetch_assoc()) {
            $conflicts[] = [
                'id' => $row['id'],
                'offline_updated' => $row['offline_updated'],
                'online_updated' => $row['online_updated']
            ];
        }
        
        return $conflicts;
    }
    
    /**
     * Get sync status
     */
    public function get_sync_status() {
        $last_sync = $this->get_last_sync_time();
        $is_online_accessible = $this->is_online_accessible();
        
        return [
            'last_sync_time' => $last_sync,
            'is_online_accessible' => $is_online_accessible,
            'sync_enabled' => $this->is_sync_enabled(),
            'auto_sync_enabled' => $this->is_auto_sync_enabled()
        ];
    }
    
    /**
     * Check if sync is enabled
     */
    private function is_sync_enabled() {
        $setting = $this->CI->db->where('setting_key', 'sync_enabled')->get('db_system_settings')->row();
        return $setting ? (bool)$setting->setting_value : false;
    }
    
    /**
     * Check if auto-sync is enabled
     */
    private function is_auto_sync_enabled() {
        $setting = $this->CI->db->where('setting_key', 'auto_sync_on_online')->get('db_system_settings')->row();
        return $setting ? (bool)$setting->setting_value : false;
    }
}
