<?php
/**
 * Database operations and CSV-protocol processing for the
 * Studiou WC Order Print Statuses plugin.
 */

defined('ABSPATH') || exit;

class Studiou_DB_Manager {
    /**
     * Order statuses that block automated print-pipeline transitions.
     * Cancelled / refunded orders should not be silently reactivated by a bulk
     * action; completed orders are already finished.
     */
    const TERMINAL_STATUSES = array('cancelled', 'refunded', 'failed', 'completed');

    /**
     * Return one row per (order × line-item) for the print-shop export.
     *
     * Design notes:
     * - Multiple product categories are concatenated into prod_cat instead of
     *   multiplying the row.
     * - Simple (non-variable) products are included via LEFT JOIN; their
     *   prod_var / prod_var_type fields will be NULL.
     * - The image URL is resolved in PHP via wp_get_attachment_url() rather
     *   than reading wp_posts.guid, which the codex warns is not guaranteed
     *   to be a URL (breaks under offloaded media / multisite uploads paths).
     * - The variation attribute is joined on terms.slug — WC stores the slug
     *   in `attribute_pa_format` postmeta, not the term name.
     * - qty is summed across duplicate line items (a customer adding the
     *   same variation twice without merging produces two rows in the
     *   lookup table; MIN() would silently undercount).
     *
     * @param int[] $order_ids
     * @return array Each element is an stdClass with fields:
     *               order_no, prod_cat, prod_name, prod_var, prod_var_type,
     *               prod_img_url, qty, email.
     */
    public static function get_orders_for_printing($order_ids) {
        global $wpdb;

        // Explicit callback so the filter doesn't accidentally drop legitimate
        // zero values if this helper is ever repurposed for non-ID lists.
        $order_ids = array_filter(
            array_map('intval', (array) $order_ids),
            function ($id) { return $id > 0; }
        );
        if (empty($order_ids)) {
            return array();
        }

        $orders_table = $wpdb->prefix . 'wc_orders';
        if (!self::table_exists($orders_table)) {
            UtilsLog::log("get_orders_for_printing: required HPOS table {$orders_table} is missing. Is WooCommerce HPOS enabled?");
            UtilsLog::message(
                __('Export requires WooCommerce HPOS (custom order tables). Enable High-Performance Order Storage in WooCommerce → Settings → Advanced → Features, or contact support.', 'studiou-wc-ord-print-statuses'),
                'error'
            );
            return array();
        }

        $lookup_table = $wpdb->prefix . 'wc_order_product_lookup';
        if (!self::table_exists($lookup_table)) {
            UtilsLog::log("get_orders_for_printing: required table {$lookup_table} is missing. Is WC analytics enabled?");
            UtilsLog::message(
                __('Export requires the WooCommerce analytics lookup table (wc_order_product_lookup). Enable WooCommerce Analytics or contact support.', 'studiou-wc-ord-print-statuses'),
                'error'
            );
            return array();
        }

        $placeholders = implode(',', array_fill(0, count($order_ids), '%d'));

        // Raise the GROUP_CONCAT byte cap (default 1024) so multi-category
        // products with long names are not silently truncated. Use GREATEST
        // so we never *lower* a setting another piece of code may rely on.
        $wpdb->query("SET SESSION group_concat_max_len = GREATEST(@@SESSION.group_concat_max_len, 65535)");

        // Non-aggregated SELECT columns are wrapped in MIN()/SUM() to remain
        // compliant with MySQL's ONLY_FULL_GROUP_BY mode (default in 5.7+).
        // qty uses SUM() because a single (order, product, variation) group
        // may contain multiple lookup rows when a customer added the same
        // variation twice without cart-merge.
        $sql = "
            SELECT
                `orders`.`id` AS `order_no`,
                GROUP_CONCAT(DISTINCT `t`.`name` ORDER BY `t`.`name` SEPARATOR ', ') AS `prod_cat`,
                MIN(`products`.`post_title`)             AS `prod_name`,
                MIN(`product_variations`.`post_title`)   AS `prod_var`,
                MIN(`product_variations_name`.`name`)    AS `prod_var_type`,
                MIN(`product_meta`.`meta_value`)         AS `prod_img_id`,
                SUM(`order_products`.`product_qty`)      AS `qty`,
                MIN(`orders`.`billing_email`)            AS `email`
            FROM `{$wpdb->prefix}wc_orders` `orders`
            JOIN `{$wpdb->prefix}wc_order_product_lookup` `order_products`
                ON `orders`.`id` = `order_products`.`order_id`
            JOIN `{$wpdb->posts}` `products`
                ON `products`.`ID` = `order_products`.`product_id`
            LEFT JOIN `{$wpdb->posts}` `product_variations`
                ON `product_variations`.`ID` = `order_products`.`variation_id`
            LEFT JOIN `{$wpdb->postmeta}` `product_variations_attr`
                ON `product_variations_attr`.`post_id` = `product_variations`.`ID`
                AND `product_variations_attr`.`meta_key` = 'attribute_pa_format'
            LEFT JOIN `{$wpdb->terms}` `product_variations_name`
                ON `product_variations_name`.`slug` = `product_variations_attr`.`meta_value`
            LEFT JOIN `{$wpdb->postmeta}` `product_meta`
                ON `product_meta`.`post_id` = `products`.`ID`
                AND `product_meta`.`meta_key` = '_thumbnail_id'
            LEFT JOIN `{$wpdb->term_relationships}` `tr`
                ON `tr`.`object_id` = `products`.`ID`
            LEFT JOIN `{$wpdb->term_taxonomy}` `tt`
                ON `tt`.`term_taxonomy_id` = `tr`.`term_taxonomy_id`
                AND `tt`.`taxonomy` = 'product_cat'
            LEFT JOIN `{$wpdb->terms}` `t`
                ON `t`.`term_id` = `tt`.`term_id`
            WHERE `orders`.`id` IN ($placeholders)
            GROUP BY
                `orders`.`id`,
                `order_products`.`product_id`,
                `order_products`.`variation_id`
            ORDER BY `orders`.`id`
        ";

        $prepared = $wpdb->prepare($sql, $order_ids);
        $results  = $wpdb->get_results($prepared);

        if ($results === false) {
            UtilsLog::log('SQL error in get_orders_for_printing: ' . $wpdb->last_error);
            return array();
        }

        // Resolve the attachment ID to a URL via WP's API — respects
        // offloaded-media plugins, multisite upload paths, and the
        // wp_get_attachment_url filter chain. Normalise integer-valued
        // qty (SUM returns DECIMAL → "2.0000") and CSV-escape every value
        // to neutralise Excel/LibreOffice formula injection.
        foreach ($results as $row) {
            $attachment_id     = isset($row->prod_img_id) ? (int) $row->prod_img_id : 0;
            $row->prod_img_url = $attachment_id ? (string) wp_get_attachment_url($attachment_id) : '';
            unset($row->prod_img_id);

            if (isset($row->qty)) {
                $row->qty = self::format_quantity($row->qty);
            }

            foreach (get_object_vars($row) as $field => $value) {
                $row->$field = self::csv_escape($value);
            }
        }
        return $results;
    }

    /**
     * Drop trailing zeros and the decimal point when the quantity is whole,
     * keeping a sensible decimal representation when it isn't.
     */
    private static function format_quantity($value) {
        if (!is_numeric($value)) {
            return (string) $value;
        }
        $float = (float) $value;
        if ((float) (int) $float === $float) {
            return (string) (int) $float;
        }
        return rtrim(rtrim(sprintf('%.4F', $float), '0'), '.');
    }

    /**
     * Defuse Excel / LibreOffice / Google Sheets formula execution by
     * prefixing values that begin with =, +, -, @, tab, or CR with a single
     * quote. OWASP-recommended pattern. The leading quote is hidden by
     * Excel when displayed but disables formula evaluation.
     */
    private static function csv_escape($value) {
        if (!is_string($value)) {
            return $value;
        }
        if ($value === '') {
            return $value;
        }
        $first = $value[0];
        if (in_array($first, array('=', '+', '-', '@', "\t", "\r"), true)) {
            return "'" . $value;
        }
        return $value;
    }

    /**
     * Check that a table exists. `$wpdb->esc_like()` escapes the LIKE
     * wildcards (`_`, `%`) that show up in conventional `$wpdb->prefix`
     * values like `wp_test_` — otherwise the underscores would match any
     * single character and could produce false positives.
     */
    private static function table_exists($table) {
        global $wpdb;
        $like  = $wpdb->esc_like($table);
        $found = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $like));
        return $found === $table;
    }

    /**
     * Bulk-set orders to "to-print", skipping orders in terminal states.
     *
     * @param int[] $order_ids
     * @return int Number of orders actually updated.
     */
    public static function set_orders_to_prepare_printing($order_ids) {
        return self::bulk_set_print_status($order_ids, 'to-print', 'to_print_date');
    }

    /**
     * Bulk-set orders to "in-print", skipping orders in terminal states.
     *
     * @param int[] $order_ids
     * @return int Number of orders actually updated.
     */
    public static function set_orders_to_in_printing($order_ids) {
        return self::bulk_set_print_status($order_ids, 'in-print', 'in_print_date');
    }

    private static function bulk_set_print_status($order_ids, $status_slug, $meta_key) {
        $updated_count = 0;
        $skipped       = 0;

        // Explanatory note for the order activity feed. Mirrors the
        // "Marked … by … Protocol import." pattern used in the import
        // handlers so the audit trail consistently records *how* a
        // transition was triggered.
        $note = ($status_slug === 'to-print')
            ? __('Marked Prepare to Printing by bulk action.', 'studiou-wc-ord-print-statuses')
            : __('Marked In Printing by bulk action.', 'studiou-wc-ord-print-statuses');

        foreach ((array) $order_ids as $order_id) {
            $order_id = (int) $order_id;
            $order    = wc_get_order($order_id);
            if (!$order) {
                UtilsLog::log('Order #' . $order_id . ' not found');
                continue;
            }
            if ($order->has_status(self::TERMINAL_STATUSES)) {
                UtilsLog::log('Order #' . $order_id . ' skipped (terminal status: ' . $order->get_status() . ')');
                $skipped++;
                continue;
            }
            $order->update_status($status_slug, $note);
            $order->update_meta_data($meta_key, current_time('mysql'));
            $order->save();
            $updated_count++;
            UtilsLog::log('Order #' . $order_id . ' marked as "' . $status_slug . '"');
        }

        if ($skipped > 0) {
            UtilsLog::message(
                sprintf(
                    _n(
                        '%d order skipped because its status is final (cancelled, refunded, failed, or completed).',
                        '%d orders skipped because their status is final (cancelled, refunded, failed, or completed).',
                        $skipped,
                        'studiou-wc-ord-print-statuses'
                    ),
                    $skipped
                ),
                'warning'
            );
        }
        return $updated_count;
    }

    /**
     * Import an InPrint protocol CSV.
     *
     * @param array $csv_data Rows produced by parse_csv().
     * @return array{updated_count:int,errors:string[]}
     */
    public static function import_inprint_protocol($csv_data) {
        $updated_count = 0;
        $errors        = array();

        foreach (self::dedupe_by_order_no($csv_data) as $row) {
            if (!isset($row['order_no']) || !isset($row['externalorder']) || !isset($row['externalorderdate'])) {
                $errors[] = __('Invalid row format in CSV.', 'studiou-wc-ord-print-statuses');
                continue;
            }
            $order = wc_get_order($row['order_no']);
            if (!$order) {
                UtilsLog::log('import_inprint_protocol: order ' . $row['order_no'] . ' not found');
                $errors[] = sprintf(__('Order %s not found.', 'studiou-wc-ord-print-statuses'), $row['order_no']);
                continue;
            }
            if ($order->has_status(self::TERMINAL_STATUSES)) {
                UtilsLog::log('import_inprint_protocol: order ' . $row['order_no'] . ' in terminal status ' . $order->get_status() . ' — skipped');
                $errors[] = sprintf(
                    __('Order %s is in a final state and was not updated.', 'studiou-wc-ord-print-statuses'),
                    $row['order_no']
                );
                continue;
            }
            $order->update_status(
                'in-print',
                __('Marked In Printing by InPrint Protocol import.', 'studiou-wc-ord-print-statuses')
            );
            $order->update_meta_data('in_print_date', current_time('mysql'));
            $order->update_meta_data('external_ref_ord_no', sanitize_text_field($row['externalorder']));
            $order->update_meta_data('external_ref_ord_date', self::normalise_csv_date($row['externalorderdate']));
            $order->save();
            $updated_count++;
            UtilsLog::log('import_inprint_protocol: order ' . $row['order_no'] . ' marked "in-print"');
        }

        return array('updated_count' => $updated_count, 'errors' => $errors);
    }

    /**
     * Import a Delivered protocol CSV.
     *
     * @param array $csv_data Rows produced by parse_csv().
     * @return array{updated_count:int,errors:string[]}
     */
    public static function import_delivered_protocol($csv_data) {
        $updated_count = 0;
        $errors        = array();

        // Statuses we should not silently flip to "completed". `completed`
        // itself is excluded from this list — re-running the same delivered
        // protocol on already-completed orders is a benign no-op transition.
        $non_completable = array('cancelled', 'refunded', 'failed');

        foreach (self::dedupe_by_order_no($csv_data) as $row) {
            if (!isset($row['order_no'])) {
                $errors[] = __('Invalid row format in CSV.', 'studiou-wc-ord-print-statuses');
                continue;
            }
            $order = wc_get_order($row['order_no']);
            if (!$order) {
                UtilsLog::log('import_delivered_protocol: order ' . $row['order_no'] . ' not found');
                $errors[] = sprintf(__('Order %s not found.', 'studiou-wc-ord-print-statuses'), $row['order_no']);
                continue;
            }
            if ($order->has_status($non_completable)) {
                UtilsLog::log('import_delivered_protocol: order ' . $row['order_no'] . ' in terminal status ' . $order->get_status() . ' — skipped');
                $errors[] = sprintf(
                    __('Order %s is in a final state and was not updated.', 'studiou-wc-ord-print-statuses'),
                    $row['order_no']
                );
                continue;
            }
            $order->update_status(
                'completed',
                __('Marked completed by Delivered Protocol import.', 'studiou-wc-ord-print-statuses')
            );
            $order->update_meta_data('external_ref_ord_delivered', current_time('mysql'));
            $order->save();
            $updated_count++;
            UtilsLog::log('import_delivered_protocol: order ' . $row['order_no'] . ' marked "completed"');
        }

        return array('updated_count' => $updated_count, 'errors' => $errors);
    }

    /**
     * Parse a CSV file into an array of associative rows.
     * Headers are normalised (lowercased, whitespace stripped) so files prepared
     * to either the legacy spec casing ("Order No") or the documented lowercase
     * form ("order_no") both work. A leading UTF-8 BOM is stripped.
     *
     * @param string $file_path
     * @return array Empty array on failure.
     */
    public static function parse_csv($file_path) {
        $csv_data = array();
        $handle   = fopen($file_path, 'r');
        if ($handle === false) {
            UtilsLog::log('parse_csv: failed to open ' . $file_path);
            return $csv_data;
        }
        try {
            // Strip a leading UTF-8 BOM if present so the first header parses
            // cleanly. The plugin's own exports include a BOM (for Excel),
            // and Notepad-saved CSVs frequently include one.
            $first_bytes = fread($handle, 3);
            if ($first_bytes !== "\xEF\xBB\xBF") {
                rewind($handle);
            }
            // Explicit empty-string escape suppresses the PHP 8.4 deprecation
            // notice for the implicit '\\' default.
            $headers = fgetcsv($handle, 0, ',', '"', '');
            if (!is_array($headers) || empty(array_filter($headers))) {
                UtilsLog::log('parse_csv: header row missing or empty');
                return $csv_data;
            }
            $headers = array_map(array(__CLASS__, 'normalise_header'), $headers);
            while (($data = fgetcsv($handle, 0, ',', '"', '')) !== false) {
                if (count($data) !== count($headers)) {
                    continue;
                }
                $csv_data[] = array_combine($headers, $data);
            }
        } finally {
            fclose($handle);
        }
        return $csv_data;
    }

    private static function normalise_header($header) {
        $header = strtolower(trim((string) $header));
        // Strip any UTF-8 BOM that survived the file-level guard.
        $header = preg_replace('/^\xEF\xBB\xBF/', '', $header);
        // Map "order no" → "order_no" by collapsing internal whitespace to underscore.
        $header = preg_replace('/\s+/', '_', $header);
        return $header;
    }

    private static function dedupe_by_order_no($rows) {
        $seen   = array();
        $unique = array();
        foreach ($rows as $row) {
            $raw_key = isset($row['order_no']) ? trim((string) $row['order_no']) : '';
            if ($raw_key === '') {
                continue;
            }
            // Case-insensitive dedup so alphabetic order numbers
            // ("ord-12345" vs "ORD-12345") collapse to a single row.
            $key = strtolower($raw_key);
            if (isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $unique[]   = $row;
        }
        return $unique;
    }

    /**
     * Normalise a free-form date string into a MySQL datetime in WP local time.
     * Returns '' (and logs) on parse failure so the UI does not silently
     * carry unparseable raw values through the round-trip.
     *
     * Avoids strtotime() entirely. WordPress sets the PHP timezone to UTC
     * very early in its boot (date_default_timezone_set('UTC')), so
     * strtotime() interprets timezone-less inputs as UTC. Combining that
     * with wp_date() — which expects a UTC timestamp and formats in WP
     * timezone — shifted "2026-05-12 14:30" forward by the WP offset.
     *
     * Two paths:
     * - Fast: ISO-like "YYYY-MM-DD[T ]HH:MM[:SS]" with no explicit timezone
     *   → pure string reformat, no shift. This is the common shape of
     *   what the print shop sends.
     * - Fallback: DateTimeImmutable($raw, wp_timezone()) — interprets
     *   timezone-less input as WP local; explicit-timezone input is honoured
     *   by the constructor (second arg ignored in that case) and then
     *   converted back to WP timezone via setTimezone() so the output is
     *   always WP-local.
     */
    private static function normalise_csv_date($raw) {
        $raw = sanitize_text_field((string) $raw);
        if ($raw === '') {
            return '';
        }
        // Fast path — common ISO-ish shape with no explicit timezone.
        if (preg_match('/^(\d{4})-(\d{2})-(\d{2})[T ](\d{2}):(\d{2})(?::(\d{2}))?$/', $raw, $m)) {
            $second = isset($m[6]) ? $m[6] : '00';
            return $m[1] . '-' . $m[2] . '-' . $m[3] . ' ' . $m[4] . ':' . $m[5] . ':' . $second;
        }
        // Fallback — let DateTimeImmutable handle locale dates and explicit
        // timezones. Strict: throws ValueError on garbage (e.g. "foo").
        try {
            $dt = new DateTimeImmutable($raw, wp_timezone());
            $dt = $dt->setTimezone(wp_timezone());
            return $dt->format('Y-m-d H:i:s');
        } catch (Exception $e) {
            UtilsLog::log('normalise_csv_date: could not parse "' . $raw . '"');
            return '';
        }
    }
}