<?php

class Backpack_Database
{
    /**
     * Constructor method.
     *
     * @param Backpack $backpack An instance of the Backpack class.
     *
     * @return void
     */
    public function __construct(private Backpack $backpack)
    {
        //
    }

    /**
     * Performs a database backup and saves it as a file, with optional compression and export.
     *
     * This method determines the backup method (e.g., "mysqldump" or PHP), creates a backup of the database,
     * compresses the backup file if specified in the schedule, and optionally exports it to an external storage (e.g., S3).
     *
     * @param array $schedule An array containing the backup schedule and other related configuration settings,
     *                        including the database backup method and compression type.
     * @param bool  $export   A flag indicating whether the backup file should be exported. Defaults to true.
     *
     * @return false|string Returns the path to the backup file (compressed if applicable) on success,
     *                      or false on failure.
     */
    public function backup(array $schedule, bool $export = true): false|string
    {
        $this->backpack->log("Beginning database backup for task {$schedule['id']}.");

        if ($schedule["settings"]["database"]["method"] === "mysqldump") {
            $file = $this->mysqldump();
        } elseif ($schedule["settings"]["database"]["method"] === "PHP") {
            $file = $this->php();
        } else {
            $this->backpack->log("Unknown database backup method: {$schedule['settings']["database"]['method']}.");
            return false;
        }

        $compressor = new Backpack_Compress($this->backpack);

        if ($schedule["settings"]["database"]["compression"] === "none") {
            // No compression
            $compressedFile = $file;
        } elseif ($schedule["settings"]["database"]["compression"] === "zip") {
            // Compress the file
            $compressedFile = $compressor->zip($file . '.zip', $file);

            // Delete the original file
            unlink($file);
        } else {
            $this->backpack->log("Unknown compression method: {$schedule['settings']["database"]['compression']}.");
            return false;
        }

        if ($export === false) {
            return $compressedFile;
        }

        // Export the file
        $this->backpack->log("Exporting file to S3.");

        try {
            // Get the site URL from WordPress
            $domain       = $this->backpack->getDomainName();
            $date         = Carbon\Carbon::now()->format('Y-m-d');
            $baseFilename = basename($compressedFile);

            $export = new Backpack_Export($this->backpack);
            $export->toS3("{$domain}/{$date}/{$baseFilename}", $compressedFile, (int) $schedule["id"]);
        } catch (\Exception $e) {
            $this->backpack->log("Failed to export file to S3. Exception: " . $e->getMessage());
        }

        $this->backpack->log("File exported to S3.");

        return $compressedFile;
    }

    /**
     * Creates a MySQL database dump and saves it to a specified backup path.
     *
     * @return string|false The path to the created database dump file on success, or false on failure.
     */
    private function mysqldump(): string|false
    {
        // Get the credentials for the database
        $credentials = $this->fetchCredentials();
        $backupPath  = $this->backpack->verifyBackupPath();

        if (! $backupPath) {
            $this->backpack->log("Unable to verify backup path.");
            return false;
        }

        $this->backpack->log("Dumping database {$credentials['name']} to {$backupPath}.");

        $filename = sprintf('%s_backup_%s.sql', $this->backpack->getDomainName(), Carbon\Carbon::now($this->backpack->getTimezone())->format('Y-m-d_H-i-s'));

        $binary = get_option('mws_backpack_mysqldump_path');

        // Prepare the `mysqldump` command
        $command = sprintf(
            '%s --host=%s --user=%s --password=%s %s > %s/%s',
            escapeshellarg($binary),
            escapeshellarg($credentials['host']),
            escapeshellarg($credentials['username']),
            escapeshellarg($credentials['password']),
            escapeshellarg($credentials['name']),
            escapeshellarg($backupPath),
            $filename
        );

        // Execute the command and capture output and return status
        $output    = [];
        $returnVar = 0;
        exec($command, $output, $returnVar);

        if ($returnVar !== 0) {
            $this->backpack->log('Database backup failed. Command: ' . $command);
            $this->backpack->log('Error output: ' . implode("\n", $output));
            return false;
        }

        $this->backpack->log('Database backup completed successfully.');

        return sprintf('%s/%s', $backupPath, $filename);
    }

    /**
     * Creates a backup of the database and saves it as an SQL file.
     *
     * This method connects to the database, retrieves the schema and data of all tables,
     * and writes this information to a backup file in SQL format. The backup file includes
     * the table creation structure and the data insertion commands.
     *
     * @return string|false Returns the path to the backup file on success or false on failure.
     */
    private function php(): string|false
    {
        // Fetch the database credentials
        $credentials = $this->fetchCredentials();
        $backupPath  = $this->backpack->verifyBackupPath();

        if (! $backupPath) {
            $this->backpack->log("Unable to verify backup path.");
            return false;
        }

        $this->backpack->log("Dumping database {$credentials['name']} using PHP to {$backupPath}.");

        // Connect to the database
        try {
            $dsn = sprintf('mysql:host=%s;dbname=%s', $credentials['host'], $credentials['name']);
            $pdo = new PDO($dsn, $credentials['username'], $credentials['password']);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            $this->backpack->log('Database connection failed: ' . $e->getMessage());
            return false;
        }

        $filename = sprintf('%s_backup_%s.sql', $this->backpack->getDomainName(), Carbon\Carbon::now($this->backpack->getTimezone())->format('Y-m-d_H-i-s'));

        $backupFile = sprintf('%s/%s', $backupPath, $filename);

        try {
            // Open the backup file for writing
            $fileHandle = fopen($backupFile, 'w');
            if (! $fileHandle) {
                $this->backpack->log("Failed to open file for writing: {$backupFile}");
                return false;
            }

            // Get server version
            $version = $pdo->query('SELECT version()')->fetchAll(PDO::FETCH_COLUMN);

            // Add a header to the backup file
            fwrite($fileHandle, "-- BackPack Database Backup\n");
            fwrite($fileHandle, "-- Database Name: {$credentials['name']}\n");
            fwrite($fileHandle, "-- Backup Date: " . Carbon\Carbon::now('UTC')->format('Y-m-d H:i:s e') . "\n\n");
            fwrite($fileHandle, "-- PHP Version: " . PHP_VERSION . "\n");
            fwrite($fileHandle, "-- Server Version: " . $version[0] . "\n");
            fwrite($fileHandle, "-- BackPack Version: " . $this->backpack->version . "\n\n");

            fwrite($fileHandle, "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n");
            fwrite($fileHandle, "START TRANSACTION\n");
            fwrite($fileHandle, "SET time_zone = \"+00:00\";\n\n");

            // Fetch all tables
            $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);

            foreach ($tables as $table) {
                // Dump table schema
                $createTableStmt = $pdo->query("SHOW CREATE TABLE `{$table}`")->fetch(PDO::FETCH_ASSOC);
                fwrite($fileHandle, "DROP TABLE IF EXISTS `{$table}`;\n");
                fwrite($fileHandle, $createTableStmt['Create Table'] . ";\n\n");

                // Dump table data
                $rows = $pdo->query("SELECT * FROM `{$table}`")->fetchAll(PDO::FETCH_ASSOC);

                if (! empty($rows)) {
                    // As a performance fix (and to avoid hitting max_allowed_packet) we'll limit the size of the
                    // INSERT statement.
                    if (count($rows) > 100) {
                        $chunkedRows = array_chunk($rows, 100);

                        foreach ($chunkedRows as $chunk) {
                            fwrite($fileHandle, "INSERT INTO `{$table}` VALUES \n");

                            $values = [];
                            foreach ($chunk as $row) {
                                $escapedRow = array_map([$pdo, 'quote'], $row);
                                $values[]   = '(' . implode(', ', $escapedRow) . ')';
                            }

                            fwrite($fileHandle, implode(",\n", $values) . ";\n\n");
                        }
                    } else {
                        fwrite($fileHandle, "INSERT INTO `{$table}` VALUES \n");

                        $values = [];
                        foreach ($rows as $row) {
                            $escapedRow = array_map([$pdo, 'quote'], $row);
                            $values[]   = '(' . implode(', ', $escapedRow) . ')';
                        }

                        fwrite($fileHandle, implode(",\n", $values) . ";\n\n");
                    }
                }
            }

            fclose($fileHandle);

            $this->backpack->log("Database backup completed and saved to {$backupFile}.");

            return $backupFile;
        } catch (Exception $e) {
            if (isset($fileHandle) && is_resource($fileHandle)) {
                fclose($fileHandle);
            }

            $this->backpack->log('Error during database backup: ' . $e->getMessage());
        }

        return false;
    }

    /**
     * Fetches the database credentials.
     *
     * @return array An associative array containing database connection details such as name, host, username, password, charset, and collation.
     */
    private function fetchCredentials(): array
    {
        global $wpdb;

        return [
            "name"      => DB_NAME,
            "host"      => DB_HOST,
            "username"  => DB_USER,
            "password"  => DB_PASSWORD,
            // "charset"   => $wpdb->charset,
            // "collation" => $wpdb->collate,
        ];
    }
}
