<?php
defined('ABSPATH') or die('Direct access not allowed');

// This is the ONLY file that touches $wpdb directly. All other classes use the methods below.
//
// Custom plugin tables (wp_vollstart_rd_*) require direct queries and are not object-cache
// targets, so the two informational DirectDatabaseQuery sniffs below are disabled file-wide.
// The SQL-injection sniff (WordPress.DB.PreparedSQL.*) is deliberately NOT blanket-disabled:
// every unavoidable false positive carries a narrowly scoped, justified phpcs:ignore at its
// exact line. All SQL strings are hardcoded internal templates; user data is always bound
// through wpdb::prepare().
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

/**
 * VollstartRD_DB — Database layer.
 *
 * SRP: Defines tables, creates/migrates schema, provides CRUD operations.
 * No business logic here.
 */
class VollstartRD_DB {

    private string $dbversion = '0.2.1';
    private string $dbprefix;
    private array $table_names = [];
    private array $tables = ['options', 'options_history', 'customers', 'customer_history', 'products', 'product_history', 'repairs', 'status_log', 'messages', 'files', 'repair_types', 'repair_type_history', 'checklists', 'checklist_items', 'repair_type_checklists', 'repair_checklist_items', 'error_logs', 'email_logs', 'ratings'];
    private $MAIN;

    public function __construct($MAIN) {
        $this->MAIN = $MAIN;
        global $wpdb;
        $this->dbprefix = $wpdb->prefix . 'vollstart_rd_';
        foreach ($this->tables as $t) {
            $this->table_names[$t] = $this->dbprefix . $t;
        }

        // Auto-migrate: checks version, returns immediately if up-to-date
        $this->installiereTabellen(false);
    }

    public function getDBVersion(): string {
        return $this->dbversion;
    }

    public function getTabelle(string $table): string {
        if (!isset($this->table_names[$table])) {
            throw new Exception(esc_html("Unknown table: {$table}"));
        }
        return $this->table_names[$table];
    }

    public function getTables(): array {
        return $this->tables;
    }

    // --- CRUD ---

    /**
     * Execute a DB callback with auto-repair on failure.
     * Pattern from Event Tickets: on first error, run installiereTabellen
     * and retry exactly once. If the retry also fails, throw exception.
     *
     * @param callable $fn      The DB operation to execute
     * @param bool     $again   Internal flag — true on first attempt, false on retry
     * @return mixed Whatever the callback returns
     */
    private function withAutoRepair(callable $fn, bool $again = true) {
        global $wpdb;
        $wpdb->last_error = '';

        // Suppress error output on first attempt (will retry after repair)
        $oldSuppress = $wpdb->suppress_errors;
        if ($again) $wpdb->suppress_errors = true;

        try {
            $result = $fn();
        } catch (Exception $e) {
            $wpdb->suppress_errors = $oldSuppress;
            if ($again) {
                $this->installiereTabellen(true);
                return $this->withAutoRepair($fn, false);
            }
            throw $e;
        }

        if (!empty($wpdb->last_error)) {
            $error = $wpdb->last_error;
            $wpdb->suppress_errors = $oldSuppress;
            if ($again) {
                $this->logError($error, 'withAutoRepair', 'Attempting schema repair');
                $this->installiereTabellen(true);
                return $this->withAutoRepair($fn, false);
            }
            $this->logError($error, 'withAutoRepair', 'Failed after schema repair');
            throw new Exception('VollstartRD DB error: ' . esc_html($error));
        }

        $wpdb->suppress_errors = $oldSuppress;
        return $result;
    }

    /**
     * Validate that a string is a safe SQL identifier (table/column name).
     * Used for defense-in-depth on values that come from internal trusted
     * sources (the plugin's own schema definitions), never from user input.
     */
    private function isValidIdentifier(string $s): bool {
        return (bool) preg_match('/^[a-zA-Z0-9_]+$/', $s);
    }

    /**
     * Validate a column definition string. Internal trusted sources only.
     * Allowed: letters, digits, underscore, parens, comma, whitespace,
     * dash, dot, single-quote, backtick.
     */
    private function isValidColumnDef(string $s): bool {
        return (bool) preg_match('/^[a-zA-Z0-9_(),\s\-\.\':`]+$/', $s);
    }

    /**
     * Insert a row into a table.
     * @return int Insert ID
     */
    public function insert(string $table, array $fields): int {
        global $wpdb;
        if (empty($fields)) {
            throw new Exception('No fields provided for insert');
        }
        $tbl = $this->getTabelle($table);
        $this->withAutoRepair(function () use ($wpdb, $tbl, $fields) {
            return $wpdb->insert($tbl, $fields);
        });
        return (int) $wpdb->insert_id;
    }

    /**
     * Update rows in a table.
     * @return int|false Number of rows updated
     */
    public function update(string $table, array $fields, array $where) {
        global $wpdb;
        if (empty($fields)) {
            throw new Exception('No fields provided for update');
        }
        if (empty($where)) {
            throw new Exception('No where conditions provided for update');
        }
        $tbl = $this->getTabelle($table);
        return $this->withAutoRepair(function () use ($wpdb, $tbl, $fields, $where) {
            return $wpdb->update($tbl, $fields, $where);
        });
    }

    /**
     * Delete rows from a table.
     * @return int|false Number of rows deleted
     */
    public function delete(string $table, array $where) {
        global $wpdb;
        if (empty($where)) {
            throw new Exception('No where conditions provided for delete');
        }
        $tbl = $this->getTabelle($table);
        return $this->withAutoRepair(function () use ($wpdb, $tbl, $where) {
            return $wpdb->delete($tbl, $where);
        });
    }

    /**
     * Fetch rows from a table.
     * @return array Rows as associative arrays
     */
    public function fetch(string $table, array $where = []): array {
        global $wpdb;
        $tbl = $this->getTabelle($table);
        return $this->withAutoRepair(function () use ($wpdb, $tbl, $where) {
            if (!empty($where)) {
                $conditions = [];
                $values = [$tbl];
                foreach ($where as $col => $val) {
                    $conditions[] = "%i = %s";
                    $values[] = $col;
                    $values[] = $val;
                }
                // Template assembled only from the literal "%i = %s" fragment; the table name,
                // column names and values are all bound through wpdb::prepare() via $values.
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- placeholders only; every value bound via prepare().
                $result = $wpdb->get_results($wpdb->prepare("SELECT * FROM %i WHERE " . implode(' AND ', $conditions), $values), ARRAY_A);
            } else {
                $result = $wpdb->get_results($wpdb->prepare("SELECT * FROM %i", $tbl), ARRAY_A);
            }
            return is_array($result) ? $result : [];
        });
    }

    /**
     * Count rows in a table.
     */
    public function count(string $table): int {
        global $wpdb;
        $tbl = $this->getTabelle($table);
        return $this->withAutoRepair(function () use ($wpdb, $tbl) {
            return (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i", $tbl));
        });
    }

    /**
     * Escape a string for use in a LIKE clause.
     */
    public function escLike(string $str): string {
        global $wpdb;
        return $wpdb->esc_like($str);
    }

    /**
     * Run a prepared SELECT and return rows.
     *
     * Every call site passes a literal sprintf-style template with
     * hardcoded "%i"/"%s"/"%d" markers and a matching argument list
     * (at minimum the table name via %i), so every query is prepared.
     *
     * NOTE: the variadic is intentionally left untyped. A `mixed` type hint
     * requires PHP 8.0; on PHP 7.4 (our declared minimum) `mixed` is parsed as
     * a class name, so the call fatals at runtime with "must be an instance of
     * mixed". Do not add it back — an untyped variadic accepts anything anyway.
     */
    public function getResults(string $sql, ...$args): array {
        global $wpdb;
        return $this->withAutoRepair(function () use ($wpdb, $sql, $args) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is always a hardcoded literal SQL template passed by internal CRUD callers; $args holds every value bound via wpdb::prepare().
            $rows = $wpdb->get_results($wpdb->prepare($sql, $args), ARRAY_A);
            return is_array($rows) ? $rows : [];
        });
    }

    /**
     * Run a prepared SELECT and return a single row.
     */
    public function getRow(string $sql, ...$args): ?array {
        global $wpdb;
        return $this->withAutoRepair(function () use ($wpdb, $sql, $args) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is always a hardcoded literal SQL template passed by internal CRUD callers; $args holds every value bound via wpdb::prepare().
            $row = $wpdb->get_row($wpdb->prepare($sql, $args), ARRAY_A);
            return $row ?: null;
        });
    }

    /**
     * Run a prepared SELECT and return a single value.
     */
    public function getVar(string $sql, ...$args) {
        global $wpdb;
        return $this->withAutoRepair(function () use ($wpdb, $sql, $args) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is always a hardcoded literal SQL template passed by internal CRUD callers; $args holds every value bound via wpdb::prepare().
            return $wpdb->get_var($wpdb->prepare($sql, $args));
        });
    }

    /**
     * Run a prepared SELECT and return a column.
     */
    public function getCol(string $sql, ...$args): array {
        global $wpdb;
        return $this->withAutoRepair(function () use ($wpdb, $sql, $args) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is always a hardcoded literal SQL template passed by internal CRUD callers; $args holds every value bound via wpdb::prepare().
            $col = $wpdb->get_col($wpdb->prepare($sql, $args));
            return is_array($col) ? $col : [];
        });
    }

    /**
     * Run a prepared query (INSERT/UPDATE/DELETE/ALTER).
     *
     * Every call site passes a literal sprintf-style template with
     * hardcoded "%i"/"%s"/"%d" markers and a matching argument list,
     * so the prepared string never mixes untrusted input at call time.
     */
    public function rawQuery(string $sql, ...$args) {
        global $wpdb;
        return $this->withAutoRepair(function () use ($wpdb, $sql, $args) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is always a hardcoded literal SQL template passed by internal CRUD callers; $args holds every value bound via wpdb::prepare().
            return $wpdb->query($wpdb->prepare($sql, $args));
        });
    }

    /**
     * Database server version string (e.g. "10.11.6-MariaDB").
     *
     * Read from the open connection instead of running "SELECT VERSION()",
     * which would have to pass through prepare() without a placeholder.
     */
    public function getServerVersion(): string {
        global $wpdb;
        return (string) $wpdb->db_server_info();
    }

    // --- Schema ---

    /**
     * Create or update all tables.
     *
     * Strategy: CREATE TABLE IF NOT EXISTS with only id + PRIMARY KEY,
     * then ALTER TABLE ADD COLUMN for each column individually.
     * If column already exists, the ALTER fails silently — this is expected.
     * This guarantees every column exists regardless of what was there before.
     */
    public function installiereTabellen(bool $force = false): void {
        $installed_ver = get_option('vollstart_rd_db_version', '');

        if (!$force && $installed_ver === $this->dbversion) {
            return;
        }

        global $wpdb;
        $charset = $wpdb->get_charset_collate();

        $schemas = $this->getTableSchemas();

        foreach ($schemas as $table => $schema) {
            $tbl = $this->table_names[$table];
            if (!$this->isValidIdentifier($tbl)) {
                continue; // internal allowlist; defensive
            }

            // 1. Create table with just id + PRIMARY KEY.
            //    $idDef and $charset are internal trusted strings (from plugin's own
            //    schema array, never user input) — interpolated after %i for the table.
            $idDef = $schema['id'] ?? 'int(32) unsigned NOT NULL AUTO_INCREMENT';
            if (!$this->isValidColumnDef($idDef)) {
                continue;
            }
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $idDef/$charset are internal constants from $schema; table name uses %i placeholder.
            $wpdb->query($wpdb->prepare("CREATE TABLE IF NOT EXISTS %i (id $idDef, PRIMARY KEY (id)) $charset", $tbl));

            // 2. Add each column individually (silently skip if exists).
            $wpdb->suppress_errors = true;
            foreach ($schema['columns'] as $col => $def) {
                if (!$this->isValidIdentifier($col) || !$this->isValidColumnDef($def)) {
                    continue;
                }
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $def is an internal trusted constant from $schema; table/column names use %i placeholders.
                $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN %i $def", $tbl, $col));
            }

            // 3. Add indexes (silently skip if exists).
            //    Index SQL strings come from internal schema; they may reference
            //    {TABLE} as a placeholder for the actual table name.
            foreach ($schema['indexes'] as $sql) {
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- index SQL is an internal trusted constant; table name substituted into trusted template.
                $wpdb->query(str_replace('{TABLE}', $tbl, $sql));
            }
            $wpdb->suppress_errors = false;
        }

        update_option('vollstart_rd_db_version', $this->dbversion);
    }

    /**
     * Table definitions. Each table has:
     * - id: primary key column definition (optional, defaults to int(32) unsigned AUTO_INCREMENT)
     * - columns: column_name => SQL definition (ALTER TABLE ADD COLUMN ...)
     * - indexes: array of CREATE INDEX / CREATE UNIQUE INDEX statements ({TABLE} placeholder)
     */
    private function getTableSchemas(): array {
        $s = [];

        // --- Options ---
        $s['options'] = [
            'columns' => [
                'option_key'   => "varchar(191) NOT NULL DEFAULT ''",
                'option_value' => "longtext NOT NULL",
            ],
            'indexes' => [
                "CREATE UNIQUE INDEX uk_option_key ON {TABLE} (option_key)",
            ],
        ];

        // --- Options History ---
        $s['options_history'] = [
            'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
            'columns' => [
                'option_key' => "varchar(191) NOT NULL DEFAULT ''",
                'old_value'  => "text NOT NULL",
                'new_value'  => "text NOT NULL",
                'changed_by' => "int(32) unsigned NOT NULL DEFAULT 0",
                'changed_at' => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ],
            'indexes' => [
                "CREATE INDEX idx_oh_key_date ON {TABLE} (option_key, changed_at)",
                "CREATE INDEX idx_oh_date ON {TABLE} (changed_at)",
            ],
        ];

        // --- Customers ---
        $s['customers'] = [
            'columns' => [
                'customer_number' => "varchar(50) NOT NULL DEFAULT ''",
                'name'            => "varchar(255) NOT NULL DEFAULT ''",
                'email'           => "varchar(255) NOT NULL DEFAULT ''",
                'phone'           => "varchar(50) NOT NULL DEFAULT ''",
                'address'         => "text NOT NULL",
                'vat_number'      => "varchar(50) NOT NULL DEFAULT ''",
                'notes'           => "text NOT NULL",
                'avg_rating'      => "decimal(2,1) NOT NULL DEFAULT 0.0",
                'created_at'      => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ],
            'indexes' => [
                "CREATE INDEX idx_cust_number ON {TABLE} (customer_number)",
                "CREATE INDEX idx_cust_email ON {TABLE} (email)",
            ],
        ];

        // --- Customer History ---
        $s['customer_history'] = [
            'columns' => [
                'customer_id' => "int(32) unsigned NOT NULL DEFAULT 0",
                'field_name'  => "varchar(50) NOT NULL DEFAULT ''",
                'old_value'   => "text NOT NULL",
                'new_value'   => "text NOT NULL",
                'changed_by'  => "int(32) unsigned NOT NULL DEFAULT 0",
                'changed_at'  => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ],
            'indexes' => [
                "CREATE INDEX idx_ch_cust ON {TABLE} (customer_id, changed_at)",
            ],
        ];

        // --- Products ---
        $s['products'] = [
            'columns' => [
                'customer_id'        => "int(32) unsigned NOT NULL DEFAULT 0",
                'name'               => "varchar(255) NOT NULL DEFAULT ''",
                'brand'              => "varchar(255) NOT NULL DEFAULT ''",
                'model'              => "varchar(255) NOT NULL DEFAULT ''",
                'serial_number'      => "varchar(255) NOT NULL DEFAULT ''",
                'image_storage_hash' => "varchar(64) NOT NULL DEFAULT ''",
                'pdf_storage_hash'   => "varchar(64) NOT NULL DEFAULT ''",
                'notes'              => "text NOT NULL",
                'service_interval_days'  => "int(11) DEFAULT NULL",
                'next_service_date'      => "date DEFAULT NULL",
                'service_reminder_sent'  => "tinyint(1) NOT NULL DEFAULT 0",
                'created_at'         => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ],
            'indexes' => [
                "CREATE INDEX idx_prod_cust ON {TABLE} (customer_id)",
                "CREATE INDEX idx_prod_serial ON {TABLE} (serial_number)",
            ],
        ];

        // --- Product History ---
        $s['product_history'] = [
            'columns' => [
                'product_id'  => "int(32) unsigned NOT NULL DEFAULT 0",
                'field_name'  => "varchar(50) NOT NULL DEFAULT ''",
                'old_value'   => "text NOT NULL",
                'new_value'   => "text NOT NULL",
                'changed_by'  => "int(32) unsigned NOT NULL DEFAULT 0",
                'changed_at'  => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ],
            'indexes' => [
                "CREATE INDEX idx_ph_prod ON {TABLE} (product_id, changed_at)",
            ],
        ];

        // --- Repairs ---
        $s['repairs'] = [
            'columns' => [
                'customer_id'           => "int(32) unsigned NOT NULL DEFAULT 0",
                'product_id'            => "int(32) unsigned DEFAULT NULL",
                'assigned_to'           => "int(32) unsigned DEFAULT NULL",
                'repair_type_id'        => "int(32) unsigned DEFAULT NULL",
                'repair_number'         => "varchar(50) NOT NULL DEFAULT ''",
                'access_code'           => "varchar(16) NOT NULL DEFAULT ''",
                'device_type'           => "varchar(255) NOT NULL DEFAULT ''",
                'device_brand'          => "varchar(255) NOT NULL DEFAULT ''",
                'device_model'          => "varchar(255) NOT NULL DEFAULT ''",
                'device_serial'         => "varchar(255) NOT NULL DEFAULT ''",
                'defect_description'    => "text NOT NULL",
                'status'                => "varchar(50) NOT NULL DEFAULT 'received'",
                'priority'              => "varchar(20) NOT NULL DEFAULT 'normal'",
                'estimated_date'        => "date DEFAULT NULL",
                'estimated_price'       => "decimal(10,2) DEFAULT NULL",
                'warranty_until'        => "date DEFAULT NULL",
                'recurring_interval_days' => "int(11) DEFAULT NULL",
                'internal_notes'        => "text NOT NULL",
                'pickup_reminder_sent'  => "tinyint(1) NOT NULL DEFAULT 0",
                'created_by_user_id'    => "int(32) unsigned NOT NULL DEFAULT 0",
                'created_by_staff_id'   => "int(32) unsigned NOT NULL DEFAULT 0",
                'created_at'            => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
                'wc_order_id'           => "int(32) unsigned DEFAULT NULL",
                'updated_at'            => "datetime DEFAULT NULL",
            ],
            'indexes' => [
                "CREATE UNIQUE INDEX uk_repair_number ON {TABLE} (repair_number)",
                "CREATE UNIQUE INDEX uk_access_code ON {TABLE} (access_code)",
                "CREATE INDEX idx_rep_cust ON {TABLE} (customer_id)",
                "CREATE INDEX idx_rep_assigned ON {TABLE} (assigned_to)",
                "CREATE INDEX idx_rep_status ON {TABLE} (status)",
                "CREATE INDEX idx_rep_priority ON {TABLE} (priority)",
            ],
        ];

        // --- Status Log ---
        $s['status_log'] = [
            'columns' => [
                'repair_id'  => "int(32) unsigned NOT NULL DEFAULT 0",
                'event_type' => "varchar(30) NOT NULL DEFAULT 'status_change'",
                'old_status' => "varchar(50) NOT NULL DEFAULT ''",
                'new_status' => "varchar(50) NOT NULL DEFAULT ''",
                'changed_by' => "varchar(255) NOT NULL DEFAULT ''",
                'note'       => "text NOT NULL",
                'created_at' => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ],
            'indexes' => [
                "CREATE INDEX idx_sl_repair ON {TABLE} (repair_id)",
            ],
        ];

        // --- Messages ---
        $s['messages'] = [
            'columns' => [
                'repair_id'   => "int(32) unsigned NOT NULL DEFAULT 0",
                'sender_type' => "varchar(20) NOT NULL DEFAULT 'staff'",
                'sender_name' => "varchar(255) NOT NULL DEFAULT ''",
                'message'     => "text NOT NULL",
                'read_at'     => "datetime DEFAULT NULL",
                'created_at'  => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ],
            'indexes' => [
                "CREATE INDEX idx_msg_repair ON {TABLE} (repair_id)",
            ],
        ];

        // --- Files ---
        $s['files'] = [
            'columns' => [
                'entity_type'    => "varchar(30) NOT NULL DEFAULT ''",
                'entity_id'      => "int(32) unsigned NOT NULL DEFAULT 0",
                'original_name'  => "varchar(255) NOT NULL DEFAULT ''",
                'stored_name'    => "varchar(255) NOT NULL DEFAULT ''",
                'storage_hash'   => "varchar(64) NOT NULL DEFAULT ''",
                'mime_type'      => "varchar(100) NOT NULL DEFAULT ''",
                'file_size'      => "int(32) unsigned NOT NULL DEFAULT 0",
                'download_token' => "varchar(64) NOT NULL DEFAULT ''",
                'caption'        => "varchar(255) NOT NULL DEFAULT ''",
                'phase'          => "varchar(50) NOT NULL DEFAULT ''",
                'is_thumbnail'   => "tinyint(1) NOT NULL DEFAULT 0",
                'temp_token'     => "varchar(64) NOT NULL DEFAULT ''",
                'created_at'     => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ],
            'indexes' => [
                "CREATE INDEX idx_file_entity ON {TABLE} (entity_type, entity_id)",
                "CREATE UNIQUE INDEX uk_file_token ON {TABLE} (download_token)",
            ],
        ];

        // --- Repair Types ---
        $s['repair_types'] = [
            'columns' => [
                'name'                     => "varchar(255) NOT NULL DEFAULT ''",
                'description'              => "text NOT NULL",
                'icon'                     => "varchar(50) NOT NULL DEFAULT 'admin-tools'",
                'estimated_duration_hours' => "decimal(5,1) NOT NULL DEFAULT 0.0",
                'default_price'            => "decimal(10,2) NOT NULL DEFAULT 0.00",
                'is_favorite'              => "tinyint(1) NOT NULL DEFAULT 0",
                'sort_order'               => "int(11) unsigned NOT NULL DEFAULT 0",
                'active'                   => "tinyint(1) NOT NULL DEFAULT 1",
            ],
            'indexes' => [],
        ];

        // --- Checklists (standalone step lists) ---
        $s['checklists'] = [
            'columns' => [
                'name'           => "varchar(255) NOT NULL DEFAULT ''",
                'description'    => "text NOT NULL",
                'trigger_status_start' => "varchar(50) NOT NULL DEFAULT ''",
                'trigger_status'       => "varchar(50) NOT NULL DEFAULT ''",
                'sort_order'           => "int(11) unsigned NOT NULL DEFAULT 0",
            ],
            'indexes' => [],
        ];

        // --- Repair Type History ---
        $s['repair_type_history'] = [
            'columns' => [
                'repair_type_id' => "int(32) unsigned NOT NULL DEFAULT 0",
                'field_name'     => "varchar(50) NOT NULL DEFAULT ''",
                'old_value'      => "text NOT NULL",
                'new_value'      => "text NOT NULL",
                'changed_by'     => "int(32) unsigned NOT NULL DEFAULT 0",
                'changed_at'     => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ],
            'indexes' => [
                "CREATE INDEX idx_rth_type ON {TABLE} (repair_type_id, changed_at)",
            ],
        ];

        // --- Repair Type ↔ Checklist assignment (many-to-many) ---
        $s['repair_type_checklists'] = [
            'columns' => [
                'repair_type_id' => "int(32) unsigned NOT NULL DEFAULT 0",
                'checklist_id'   => "int(32) unsigned NOT NULL DEFAULT 0",
                'sort_order'     => "int(11) unsigned NOT NULL DEFAULT 0",
            ],
            'indexes' => [
                "CREATE INDEX idx_rtcl_type ON {TABLE} (repair_type_id)",
                "CREATE INDEX idx_rtcl_cl ON {TABLE} (checklist_id)",
            ],
        ];

        // --- Checklist Items (template steps) ---
        $s['checklist_items'] = [
            'columns' => [
                'checklist_id'     => "int(32) unsigned NOT NULL DEFAULT 0",
                'title'            => "varchar(255) NOT NULL DEFAULT ''",
                'instructions'     => "text NOT NULL",
                'default_duration' => "decimal(5,1) NOT NULL DEFAULT 0.0",
                'default_price'    => "decimal(10,2) NOT NULL DEFAULT 0.00",
                'sort_order'       => "int(11) unsigned NOT NULL DEFAULT 0",
            ],
            'indexes' => [
                "CREATE INDEX idx_cli_checklist ON {TABLE} (checklist_id)",
            ],
        ];

        // --- Repair Checklist Items (per-repair instances, trackable) ---
        $s['repair_checklist_items'] = [
            'columns' => [
                'repair_id'         => "int(32) unsigned NOT NULL DEFAULT 0",
                'checklist_id'      => "int(32) unsigned DEFAULT NULL",
                'checklist_item_id' => "int(32) unsigned DEFAULT NULL",
                'group_name'        => "varchar(255) NOT NULL DEFAULT ''",
                'title'             => "varchar(255) NOT NULL DEFAULT ''",
                'instructions'      => "text NOT NULL",
                'default_duration'  => "decimal(5,1) NOT NULL DEFAULT 0.0",
                'default_price'     => "decimal(10,2) NOT NULL DEFAULT 0.00",
                'tracked_duration'  => "decimal(8,2) NOT NULL DEFAULT 0.00",
                'timer_started_at'  => "datetime DEFAULT NULL",
                'sort_order'        => "int(11) unsigned NOT NULL DEFAULT 0",
                'is_checked'        => "tinyint(1) NOT NULL DEFAULT 0",
                'checked_at'        => "datetime DEFAULT NULL",
                'checked_by'        => "varchar(255) NOT NULL DEFAULT ''",
            ],
            'indexes' => [
                "CREATE INDEX idx_rci_repair ON {TABLE} (repair_id)",
                "CREATE INDEX idx_rci_checklist ON {TABLE} (checklist_id)",
            ],
        ];

        // --- Error Logs ---
        $s['error_logs'] = [
            'columns' => [
                'time'          => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
                'exception_msg' => "text NOT NULL",
                'msg'           => "text NOT NULL",
                'caller_name'   => "varchar(255) NOT NULL DEFAULT ''",
            ],
            'indexes' => [
                "CREATE INDEX idx_el_time ON {TABLE} (time)",
            ],
        ];

        // --- Email Logs ---
        $s['email_logs'] = [
            'columns' => [
                'repair_id'  => "int(32) unsigned NOT NULL DEFAULT 0",
                'email_type' => "varchar(50) NOT NULL DEFAULT ''",
                'recipient'  => "varchar(255) NOT NULL DEFAULT ''",
                'subject'    => "varchar(500) NOT NULL DEFAULT ''",
                'body'       => "longtext NOT NULL",
                'success'    => "tinyint(1) NOT NULL DEFAULT 0",
                'sent_by'    => "varchar(255) NOT NULL DEFAULT ''",
                'created_at' => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ],
            'indexes' => [
                "CREATE INDEX idx_eml_repair ON {TABLE} (repair_id)",
                "CREATE INDEX idx_eml_date ON {TABLE} (created_at)",
            ],
        ];

        // --- Ratings ---
        $s['ratings'] = [
            'columns' => [
                'repair_id'     => "int(32) unsigned NOT NULL DEFAULT 0",
                'customer_id'   => "int(32) unsigned NOT NULL DEFAULT 0",
                'type'          => "varchar(20) NOT NULL DEFAULT ''",
                'rating'        => "tinyint(1) unsigned NOT NULL DEFAULT 0",
                'comment'       => "text NOT NULL",
                'phase'         => "varchar(50) NOT NULL DEFAULT ''",
                'rated_by'      => "varchar(50) NOT NULL DEFAULT ''",
                'token'         => "varchar(32) NOT NULL DEFAULT ''",
                'created_at'    => "datetime NOT NULL DEFAULT CURRENT_TIMESTAMP",
                'updated_at'    => "datetime DEFAULT NULL",
            ],
            'indexes' => [
                "CREATE INDEX idx_rat_repair ON {TABLE} (repair_id)",
                "CREATE INDEX idx_rat_customer ON {TABLE} (customer_id)",
                "CREATE INDEX idx_rat_type ON {TABLE} (type)",
                "CREATE UNIQUE INDEX uk_rat_token ON {TABLE} (token)",
            ],
        ];

        return $s;
    }

    // --- Error Logging ---

    /**
     * Log an error to the error_logs table.
     * Pattern from Event Tickets: logErrorToDB().
     * Keeps max 200 entries, auto-cleans oldest.
     */
    public function logError($exception, string $callerName = '', string $msg = ''): void {
        $exceptionMsg = '';
        if ($exception instanceof Exception) {
            $exceptionMsg = $exception->getMessage();
        } elseif (is_string($exception)) {
            $exceptionMsg = $exception;
        }

        try {
            global $wpdb;
            $tbl = $this->table_names['error_logs'] ?? '';
            if (empty($tbl)) return;

            $wpdb->insert($tbl, [
                'time'          => wp_date('Y-m-d H:i:s'),
                'exception_msg' => mb_substr(trim($exceptionMsg), 0, 5000),
                'msg'           => mb_substr(trim($msg), 0, 5000),
                'caller_name'   => mb_substr(trim($callerName), 0, 255),
            ]);

            // Keep max 200 entries
            $count = (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i", $tbl));
            if ($count > 200) {
                $cutoff = (int) $wpdb->get_var($wpdb->prepare("SELECT id FROM %i ORDER BY id DESC LIMIT 1 OFFSET 200", $tbl));
                if ($cutoff > 0) {
                    $wpdb->query($wpdb->prepare("DELETE FROM %i WHERE id <= %d", $tbl, $cutoff));
                }
            }
        } catch (Exception $e) {
            // Don't throw from error logger — would cause infinite loop
        }
    }

    /**
     * Get recent error logs for support display.
     */
    public function getErrorLogs(int $limit = 50): array {
        global $wpdb;
        $tbl = $this->table_names['error_logs'] ?? '';
        if (empty($tbl)) return [];

        try {
            return $wpdb->get_results(
                $wpdb->prepare("SELECT * FROM %i ORDER BY id DESC LIMIT %d", $tbl, $limit),
                ARRAY_A
            ) ?: [];
        } catch (Exception $e) {
            return [];
        }
    }

    /**
     * Clear all error logs.
     */
    public function clearErrorLogs(): void {
        global $wpdb;
        $tbl = $this->table_names['error_logs'] ?? '';
        if (!empty($tbl)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- intentional schema operation; table name parameterized via %i.
            $wpdb->query($wpdb->prepare("TRUNCATE TABLE %i", $tbl));
        }
    }

    /**
     * Drop all plugin tables. Called from uninstall.php if enabled.
     */
    public function dropAllTables(): void {
        global $wpdb;
        foreach ($this->table_names as $tbl) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- intentional schema operation; table name parameterized via %i.
            $wpdb->query($wpdb->prepare("DROP TABLE IF EXISTS %i", $tbl));
        }
        delete_option('vollstart_rd_db_version');
    }
}
