<?php
/**
 * Database Helper Class
 *
 * A site migration has to read and write every table of the site, including
 * tables created by other plugins, with SQL that comes from the backup dump.
 * Those statements can't use $wpdb->prepare() (they are complete statements
 * already escaped by the export) and must not be cached, so direct queries are
 * intended here. Table names always come from SHOW TABLES of this database
 * or are validated against /^[A-Za-z0-9_$]+$/ before use.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery
 */

if (!defined('ABSPATH')) {
    exit;
}

class WPMigPro_Database {

    /**
     * Tables that belong to this plugin. They are never exported nor overwritten on restore,
     * so every site keeps its own backup list and logs.
     */
    public static $own_tables = array('wpmigpro_backups', 'wpmigpro_logs', 'wpmigpro_migrations');

    /**
     * Run a full SQL statement (from the dump, or built with self::escape()).
     * Returns false on error.
     */
    public static function query($sql) {
        global $wpdb;
        $suppress = $wpdb->suppress_errors(true);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- complete statement from the backup dump / built with esc_sql().
        $result = $wpdb->query($sql);
        $wpdb->suppress_errors($suppress);
        return $result !== false;
    }

    /**
     * SELECT returning associative rows.
     */
    public static function rows($sql) {
        global $wpdb;
        $suppress = $wpdb->suppress_errors(true);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- built with $wpdb->prepare() / esc_sql() by the caller.
        $rows = $wpdb->get_results($sql, ARRAY_A);
        $wpdb->suppress_errors($suppress);
        return is_array($rows) ? $rows : array();
    }

    public static function last_error() {
        global $wpdb;
        return $wpdb->last_error;
    }

    /**
     * Escape a value for a quoted SQL string literal (newlines included, so dump lines stay single-line).
     */
    public static function escape($value) {
        global $wpdb;
        return $wpdb->remove_placeholder_escape(esc_sql((string) $value));
    }

    public static function is_valid_table_name($name) {
        return (bool) preg_match('/^[A-Za-z0-9_$]+$/', $name);
    }

    /**
     * Tables of this WordPress install (current prefix only), minus our own tables.
     */
    public static function get_site_tables() {
        global $wpdb;
        $tables = $wpdb->get_col($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($wpdb->prefix) . '%'));
        $own = array_map(function ($t) use ($wpdb) { return $wpdb->prefix . $t; }, self::$own_tables);
        return array_values(array_filter(array_diff($tables, $own), array(__CLASS__, 'is_valid_table_name')));
    }

    /**
     * Tables whose name starts with $prefix.
     */
    public static function tables_with_prefix($prefix) {
        global $wpdb;
        $tables = $wpdb->get_col($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($prefix) . '%'));
        return array_values(array_filter((array) $tables, array(__CLASS__, 'is_valid_table_name')));
    }

    /**
     * Export database to SQL file.
     *
     * Format: one statement per line for INSERTs (values are escaped, so they never
     * contain raw newlines), multi-row INSERTs of up to 100 rows, and rows read in
     * chunks so big tables don't exhaust memory.
     */
    public static function export_database($backup_name, $log = null) {
        global $wpdb;

        $output_file = WPMIGPRO_TEMP_DIR . '/' . $backup_name . '-database.sql';
        $handle = WPMigPro_FS::open($output_file, 'w');

        if (!$handle) {
            return array('success' => false, 'message' => __('Cannot create database export file', 'enmigrate-pro'));
        }

        WPMigPro_FS::write($handle, '-- Database export for ' . get_bloginfo('name') . "\n");
        WPMigPro_FS::write($handle, '-- Created: ' . current_time('mysql') . "\n");
        WPMigPro_FS::write($handle, '-- URL: ' . home_url() . "\n");
        WPMigPro_FS::write($handle, '-- Prefix: ' . $wpdb->prefix . "\n\n");
        WPMigPro_FS::write($handle, "SET NAMES utf8mb4;\n");
        WPMigPro_FS::write($handle, "SET FOREIGN_KEY_CHECKS=0;\n\n");

        /**
         * Filters the tables included in the database export.
         *
         * @param string[] $tables Table names of this site.
         */
        $tables = array_values(array_filter((array) apply_filters('wpmigpro_export_tables', self::get_site_tables()), array(__CLASS__, 'is_valid_table_name')));
        $total_rows = 0;

        foreach ($tables as $index => $table) {
            $create_table = $wpdb->get_row($wpdb->prepare('SHOW CREATE TABLE %i', $table), ARRAY_N);
            if (empty($create_table[1])) {
                WPMigPro_Logger::log("Export: cannot read structure of $table", 'warning');
                continue;
            }

            WPMigPro_FS::write($handle, "DROP TABLE IF EXISTS `$table`;\n");
            WPMigPro_FS::write($handle, $create_table[1] . ";\n");

            $count = (int) $wpdb->get_var($wpdb->prepare('SELECT COUNT(*) FROM %i', $table));
            if ($log) {
                /* translators: 1: table name, 2: number of rows, 3: table number, 4: total tables */
                call_user_func($log, sprintf(__('Database: %1$s (%2$d rows) [%3$d/%4$d]', 'enmigrate-pro'), $table, $count, $index + 1, count($tables)));
            }

            $chunk = 1000;
            for ($offset = 0; $offset < $count; $offset += $chunk) {
                $rows = self::rows($wpdb->prepare('SELECT * FROM %i LIMIT %d, %d', $table, $offset, $chunk));
                if (empty($rows)) {
                    if (self::last_error()) {
                        WPMigPro_Logger::log("Export: error reading $table: " . self::last_error(), 'warning');
                    }
                    break;
                }

                $columns = '`' . implode('`, `', array_keys($rows[0])) . '`';
                $values = array();
                foreach ($rows as $row) {
                    $vals = array();
                    foreach ($row as $value) {
                        $vals[] = ($value === null) ? 'NULL' : "'" . self::escape($value) . "'";
                    }
                    $values[] = '(' . implode(', ', $vals) . ')';

                    if (count($values) >= 100) {
                        WPMigPro_FS::write($handle, "INSERT INTO `$table` ($columns) VALUES " . implode(', ', $values) . ";\n");
                        $values = array();
                    }
                    $total_rows++;
                }
                if (!empty($values)) {
                    WPMigPro_FS::write($handle, "INSERT INTO `$table` ($columns) VALUES " . implode(', ', $values) . ";\n");
                }
                unset($rows);
            }
            WPMigPro_FS::write($handle, "\n");
        }

        WPMigPro_FS::write($handle, "SET FOREIGN_KEY_CHECKS=1;\n");
        WPMigPro_FS::close($handle);

        WPMigPro_Logger::log('Export: ' . count($tables) . " tables, $total_rows rows");

        return array(
            'success' => true,
            'file' => $output_file,
            'size' => filesize($output_file),
            'tables' => count($tables),
            'rows' => $total_rows,
        );
    }
}