<?php
require_once 'Logger.php';

$vendorPath = __DIR__ . '/../vendor/autoload.php';
$phpspreadsheetAvailable = false;
if (file_exists($vendorPath)) {
    require_once $vendorPath;
    $phpspreadsheetAvailable = true;
}

class LocalBackupManager {
    private $conn;
    private $logger;
    private $backupDir;
    private $tempDir;
    private $archiveDir;

    public function __construct($conn, $logger = null) {
        $this->conn = $conn;
        $this->logger = $logger;

        if (!$this->conn) {
            throw new Exception("Database connection is null");
        }

        try {
            $this->conn->query("SELECT 1");
        } catch (Exception $e) {
            throw new Exception("Database connection test failed: " . $e->getMessage());
        }

        $this->backupDir = __DIR__ . '/../backups';
        $this->tempDir = $this->backupDir . '/temp';
        $this->archiveDir = $this->backupDir . '/archive';

        $this->ensureDirectoriesExist();
    }

    private function ensureDirectoriesExist() {
        $dirs = [$this->backupDir, $this->tempDir, $this->archiveDir];

        foreach ($dirs as $dir) {
            if (!file_exists($dir)) {
                mkdir($dir, 0755, true);
            }
        }
    }

    public function createLocalBackup($userId = null) {
        $backupId = $this->startBackup('local', $userId);

        try {
            $this->updateBackupStatus($backupId, 'in_progress');

            $startTime = microtime(true);
            $files = [];

            $exportMethods = [
                'exportBranchStocks',
                'exportExpenses',
                'exportSalesData',
                'exportLogs',
                'exportFinancials',
                'exportDocuments'
            ];

            foreach ($exportMethods as $method) {
                try {
                    $result = $this->$method($backupId);
                    if ($result) {
                        if (is_array($result)) {
                            $files = array_merge($files, $result);
                        } else {
                            $files[] = $result;
                        }
                    }
                } catch (Exception $e) {
                    error_log("Failed to export {$method}: " . $e->getMessage());
                }
            }

            $archivePath = $this->createCompressedArchive($files, $backupId);

            $executionTime = round(microtime(true) - $startTime, 2);

            $this->completeBackup($backupId, $archivePath, $executionTime);

            return [
                'success' => true,
                'backup_id' => $backupId,
                'file_path' => $archivePath,
                'file_name' => basename($archivePath),
                'execution_time' => $executionTime
            ];

        } catch (Exception $e) {
            $this->failBackup($backupId, $e->getMessage());
            throw $e;
        }
    }

    public function exportBranchStocks($backupId) {
        $sql = "SELECT
                    i.*,
                    b.branch_name,
                    'N/A' as warehouse_name,
                    'N/A' as category_name
                FROM items i
                LEFT JOIN branches b ON i.current_location_id = b.branch_id
                ORDER BY i.item_id";

        return $this->exportToExcel($sql, 'branch_stocks', $backupId, 'items');
    }

    public function exportExpenses($backupId) {
        $sql = "SELECT
                    e.*,
                    b.branch_name,
                    ec.category_name,
                    u.username as created_by_name
                FROM operational_expenses e
                LEFT JOIN branches b ON e.branch_id = b.branch_id
                LEFT JOIN expense_categories ec ON e.expense_category_id = ec.category_id
                LEFT JOIN users u ON e.user_id = u.user_id
                ORDER BY e.expense_date DESC, e.created_at DESC";

        return $this->exportToExcel($sql, 'expenses', $backupId, 'operational_expenses');
    }

    public function exportSalesData($backupId) {
        $sql = "SELECT
                    si.*,
                    i.item_name,
                    i.item_details,
                    i.unique_code as item_unique_code,
                    b.branch_name,
                    u.username as sold_by_name
                FROM sold_items si
                LEFT JOIN items i ON si.item_id = i.item_id
                LEFT JOIN branches b ON si.branch_id = b.branch_id
                LEFT JOIN users u ON si.sold_by = u.user_id
                ORDER BY si.sold_at DESC";

        return $this->exportToExcel($sql, 'sales_data', $backupId, 'sold_items');
    }

    public function exportLogs($backupId) {
        $sql = "SELECT
                    l.*,
                    u.username as user_name
                FROM system_logs l
                LEFT JOIN users u ON l.user_id = u.user_id
                ORDER BY l.timestamp DESC";

        return $this->exportToExcel($sql, 'activity_logs', $backupId, 'system_logs');
    }

    public function exportFinancials($backupId) {
        $files = [];

        $loanSql = "SELECT
                        ll.*,
                        b.branch_name,
                        u.username as created_by_name
                    FROM loans_laybuys ll
                    LEFT JOIN branches b ON ll.branch_id = b.branch_id
                    LEFT JOIN users u ON ll.user_id = u.user_id
                    ORDER BY ll.created_at DESC";

        $files[] = $this->exportToExcel($loanSql, 'loans_laybuys', $backupId, 'loans_laybuys');

        $depositSql = "SELECT
                          d.*,
                          b.branch_name
                        FROM deposits d
                        LEFT JOIN branches b ON d.branch_id = b.branch_id
                        ORDER BY d.deposit_date DESC";

        $files[] = $this->exportToExcel($depositSql, 'deposits', $backupId, 'deposits');

        return $files;
    }

    public function exportDocuments($backupId) {
        $files = [];

        $quoteSql = "SELECT
                        q.*,
                        b.branch_name,
                        u.username as created_by_name
                      FROM quotations q
                      LEFT JOIN branches b ON q.branch_id = b.branch_id
                      LEFT JOIN users u ON q.created_by = u.user_id
                      ORDER BY q.created_at DESC";

        $files[] = $this->exportToExcel($quoteSql, 'quotations', $backupId, 'quotations');

        $orderSql = "SELECT
                        o.*,
                        b.branch_name,
                        u.username as created_by_name
                      FROM orders o
                      LEFT JOIN branches b ON o.branch_id = b.branch_id
                      LEFT JOIN users u ON o.created_by = u.user_id
                      ORDER BY o.created_at DESC";

        $files[] = $this->exportToExcel($orderSql, 'orders', $backupId, 'orders');

        return $files;
    }

    private function exportToExcel($sql, $filename, $backupId, $tableName) {
        global $phpspreadsheetAvailable;

        if (!$phpspreadsheetAvailable) {
            error_log("PhpSpreadsheet not available, falling back to SQL export for {$tableName}");
            return $this->exportToFile($sql, $filename, $backupId, $tableName);
        }

        try {
            if (!$this->conn) {
                error_log("Database connection not available for {$tableName} export");
                return null;
            }

            $stmt = $this->conn->prepare($sql);
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

            if (empty($results)) {
                return null;
            }

            $timestamp = date('Y-m-d_His');
            $filePath = $this->tempDir . "/{$filename}_{$timestamp}.xlsx";

            $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            $columns = array_keys($results[0]);
            foreach ($columns as $colIndex => $columnName) {
                $sheet->setCellValueByColumnAndRow($colIndex + 1, 1, $columnName);
                $sheet->getStyleByColumnAndRow($colIndex + 1, 1)->getFont()->setBold(true);
            }

            foreach ($results as $rowIndex => $row) {
                foreach ($columns as $colIndex => $columnName) {
                    $value = $row[$columnName];
                    $sheet->setCellValueByColumnAndRow($colIndex + 1, $rowIndex + 2, $value);
                }
            }

            foreach ($columns as $colIndex => $columnName) {
                $sheet->getColumnDimensionByColumn($colIndex + 1)->setAutoSize(true);
            }

            $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
            $writer->save($filePath);

            if ($backupId > 0) {
                $this->recordBackupFile($backupId, basename($filePath), 'xlsx', $tableName, count($results), filesize($filePath));
            }

            return $filePath;

        } catch (Exception $e) {
            error_log("Error exporting {$tableName} to Excel: " . $e->getMessage() . ", falling back to SQL export");
            return $this->exportToFile($sql, $filename, $backupId, $tableName);
        }
    }

    private function exportToFile($sql, $filename, $backupId, $tableName) {
        try {
            if (!$this->conn) {
                error_log("Database connection not available for {$tableName} export");
                return null;
            }

            $stmt = $this->conn->prepare($sql);
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

            $timestamp = date('Y-m-d_His');
            $filePath = $this->tempDir . "/{$filename}_{$timestamp}.sql";

            $sqlContent = "-- {$tableName} table backup\n";
            $sqlContent .= "-- Generated on: " . date('Y-m-d H:i:s') . "\n";
            $sqlContent .= "-- Records: " . count($results) . "\n\n";

            if (!empty($results)) {
                $columns = array_keys($results[0]);
                $sqlContent .= "INSERT INTO `{$tableName}` (`" . implode('`, `', $columns) . "`) VALUES\n";

                $values = [];
                foreach ($results as $row) {
                    $rowValues = [];
                    foreach ($row as $value) {
                        if ($value === null) {
                            $rowValues[] = 'NULL';
                        } else {
                            $rowValues[] = $this->conn->quote($value);
                        }
                    }
                    $values[] = "(" . implode(', ', $rowValues) . ")";
                }

                $sqlContent .= implode(",\n", $values) . ";\n";
            }

            file_put_contents($filePath, $sqlContent);

            if ($backupId > 0) {
                $this->recordBackupFile($backupId, basename($filePath), 'sql', $tableName, count($results), filesize($filePath));
            }

            return $filePath;

        } catch (Exception $e) {
            error_log("Error exporting {$tableName}: " . $e->getMessage());
            return null;
        }
    }

    private function createCompressedArchive($files, $backupId) {
        $timestamp = date('Y-m-d_His');
        $archiveName = "local_backup_{$timestamp}_{$backupId}.zip";
        $archivePath = $this->archiveDir . '/' . $archiveName;

        $allFiles = [];
        foreach ($files as $file) {
            if (is_array($file)) {
                $allFiles = array_merge($allFiles, $file);
            } else {
                $allFiles[] = $file;
            }
        }

        $zip = new ZipArchive();
        if ($zip->open($archivePath, ZipArchive::CREATE) === TRUE) {
            foreach ($allFiles as $file) {
                if (file_exists($file)) {
                    $zip->addFile($file, basename($file));
                }
            }

            $metadata = $this->createMetadataFile($backupId);
            $zip->addFromString('backup_metadata.json', $metadata);

            $zip->close();

            foreach ($allFiles as $file) {
                if (file_exists($file)) {
                    unlink($file);
                }
            }

            return $archivePath;
        } else {
            throw new Exception("Failed to create ZIP archive");
        }
    }

    private function createMetadataFile($backupId) {
        $metadata = [
            'backup_id' => $backupId,
            'backup_type' => 'local_excel',
            'created_at' => date('Y-m-d H:i:s'),
            'database_name' => $this->getDatabaseName(),
            'server_info' => php_uname(),
            'php_version' => phpversion(),
            'format' => 'Excel (.xlsx files)',
            'tables_backed_up' => [
                'items', 'operational_expenses', 'sold_items', 'system_logs',
                'loans_laybuys', 'deposits', 'quotations', 'orders', 'branches', 'users'
            ],
            'instructions' => 'Each table is exported as a separate Excel file. Open with Microsoft Excel or compatible spreadsheet software.'
        ];

        return json_encode($metadata, JSON_PRETTY_PRINT);
    }

    private function getDatabaseName() {
        try {
            $stmt = $this->conn->query("SELECT DATABASE() as db_name");
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result['db_name'] ?? 'unknown';
        } catch (Exception $e) {
            return 'unknown';
        }
    }

    private function startBackup($type, $userId) {
        $sql = "INSERT INTO backup_history (backup_type, status, created_by)
                VALUES (:backup_type, 'in_progress', :created_by)";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':backup_type', $type, PDO::PARAM_STR);
        $stmt->bindValue(':created_by', $userId, PDO::PARAM_INT);
        $stmt->execute();

        return $this->conn->lastInsertId();
    }

    private function updateBackupStatus($backupId, $status, $errorMessage = null) {
        $sql = "UPDATE backup_history SET status = :status";

        if ($errorMessage) {
            $sql .= ", error_message = :error_message";
        }

        $sql .= " WHERE backup_id = :backup_id";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':status', $status, PDO::PARAM_STR);
        $stmt->bindValue(':backup_id', $backupId, PDO::PARAM_INT);

        if ($errorMessage) {
            $stmt->bindValue(':error_message', $errorMessage, PDO::PARAM_STR);
        }

        $stmt->execute();
    }

    private function completeBackup($backupId, $filePath, $executionTime) {
        $fileSize = filesize($filePath);

        $sql = "UPDATE backup_history SET
                status = 'success',
                file_name = :file_name,
                file_size = :file_size,
                file_path = :file_path,
                execution_time = :execution_time
                WHERE backup_id = :backup_id";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':file_name', basename($filePath), PDO::PARAM_STR);
        $stmt->bindValue(':file_size', $fileSize, PDO::PARAM_INT);
        $stmt->bindValue(':file_path', $filePath, PDO::PARAM_STR);
        $stmt->bindValue(':execution_time', $executionTime, PDO::PARAM_INT);
        $stmt->bindValue(':backup_id', $backupId, PDO::PARAM_INT);
        $stmt->execute();
    }

    private function failBackup($backupId, $errorMessage) {
        $this->updateBackupStatus($backupId, 'failed', $errorMessage);
    }

    private function recordBackupFile($backupId, $fileName, $fileType, $tableName, $recordCount, $fileSize) {
        if ($backupId <= 0) {
            error_log("Skipping backup file record for backup_id: $backupId");
            return;
        }

        try {
            $checksum = hash_file('sha256', $this->tempDir . '/' . $fileName);

            $sql = "INSERT INTO backup_files (backup_id, file_name, file_type, table_name, record_count, file_size, checksum)
                    VALUES (:backup_id, :file_name, :file_type, :table_name, :record_count, :file_size, :checksum)";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindValue(':backup_id', $backupId, PDO::PARAM_INT);
            $stmt->bindValue(':file_name', $fileName, PDO::PARAM_STR);
            $stmt->bindValue(':file_type', $fileType, PDO::PARAM_STR);
            $stmt->bindValue(':table_name', $tableName, PDO::PARAM_STR);
            $stmt->bindValue(':record_count', $recordCount, PDO::PARAM_INT);
            $stmt->bindValue(':file_size', $fileSize, PDO::PARAM_INT);
            $stmt->bindValue(':checksum', $checksum, PDO::PARAM_STR);
            $stmt->execute();
        } catch (Exception $e) {
            error_log("Error recording backup file: " . $e->getMessage());
        }
    }

    public function getLocalBackupHistory($limit = 50) {
        try {
            $sql = "SELECT bh.*, u.username as created_by_name
                    FROM backup_history bh
                    LEFT JOIN users u ON bh.created_by = u.user_id
                    WHERE bh.backup_type = 'local'
                    ORDER BY bh.backup_date DESC
                    LIMIT :limit";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->execute();

            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting local backup history: " . $e->getMessage());
            return [];
        }
    }

    public function getBackupFilePath($backupId) {
        $sql = "SELECT file_path FROM backup_history
                WHERE backup_id = :backup_id AND backup_type = 'local' AND status = 'success'";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':backup_id', $backupId, PDO::PARAM_INT);
        $stmt->execute();

        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result ? $result['file_path'] : null;
    }

    public function deleteLocalBackup($backupId) {
        $filePath = $this->getBackupFilePath($backupId);
        if ($filePath && file_exists($filePath)) {
            unlink($filePath);
        }

        $sql = "DELETE FROM backup_history WHERE backup_id = :backup_id AND backup_type = 'local'";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':backup_id', $backupId, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->rowCount() > 0;
    }
}
?>