<?php
namespace GrCop;

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

if (!class_exists('WP_List_Table')) {
    require_once ABSPATH . 'wp-admin/includes/class-wp-list-table.php';
}

class GrCop_Log extends \WP_List_Table
{
    public function __construct()
    {
        parent::__construct([
            'singular' => __('Odstop od pogodbe', 'gr-odstop-od-pogodbe'),
            'plural'   => __('Odstopi od pogodbe', 'gr-odstop-od-pogodbe'),
            'ajax'     => false
        ]);
        
        $this->process_actions();
    }

    /**
     * Creates custom DB table.
     */
    public static function create_table()
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'grcop_withdrawals';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            order_id BIGINT(20) UNSIGNED NOT NULL,
            customer_name VARCHAR(255) NOT NULL,
            customer_email VARCHAR(100) NOT NULL,
            items_json TEXT NOT NULL,
            refund_method VARCHAR(50) DEFAULT 'default',
            refund_details TEXT DEFAULT NULL,
            status VARCHAR(50) DEFAULT 'pending',
            ip_address VARCHAR(45) NOT NULL,
            user_agent VARCHAR(255) NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY order_id (order_id)
        ) $charset_collate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
    }

    /**
     * Inserts new withdrawal request.
     */
    public static function log_withdrawal($order_id, $name, $email, $items, $refund_method, $refund_details, $status = 'pending')
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'grcop_withdrawals';
        
        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
        $ip = isset($_SERVER['REMOTE_ADDR']) ? sanitize_text_field(wp_unslash($_SERVER['REMOTE_ADDR'])) : '';
        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
        $user_agent = isset($_SERVER['HTTP_USER_AGENT']) ? sanitize_text_field(wp_unslash($_SERVER['HTTP_USER_AGENT'])) : '';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $wpdb->insert(
            $table_name,
            [
                'order_id'       => intval($order_id),
                'customer_name'  => sanitize_text_field($name),
                'customer_email' => sanitize_email($email),
                'items_json'     => wp_json_encode($items),
                'refund_method'  => sanitize_text_field($refund_method),
                'refund_details' => sanitize_textarea_field($refund_details),
                'status'         => sanitize_text_field($status),
                'ip_address'     => sanitize_text_field($ip),
                'user_agent'     => sanitize_text_field($user_agent),
                'created_at'     => current_time('mysql')
            ],
            ['%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
        );

        return $wpdb->insert_id;
    }

    /**
     * Updates the status of a withdrawal request and syncs it with a WooCommerce order note.
     */
    public static function update_withdrawal_status($id, $new_status, $comment = '')
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'grcop_withdrawals';

        // Retrieve existing request record
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table_name WHERE id = %d", intval($id)));
        if (!$row) {
            return false;
        }

        // Perform status update in DB
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $updated = $wpdb->update(
            $table_name,
            ['status' => sanitize_text_field($new_status)],
            ['id' => intval($id)],
            ['%s'],
            ['%d']
        );

        if ($updated === false) {
            return false;
        }

        // Add corresponding order note to WooCommerce order
        $order = wc_get_order($row->order_id);
        if ($order) {
            $status_labels = [
                'pending'  => __('V obdelavi', 'gr-odstop-od-pogodbe'),
                'approved' => __('Odobreno', 'gr-odstop-od-pogodbe'),
                'refunded' => __('Povrnjeno', 'gr-odstop-od-pogodbe'),
                'rejected' => __('Zavrnjeno', 'gr-odstop-od-pogodbe')
            ];
            $label = $status_labels[$new_status] ?? $new_status;

            $note = sprintf(
                /* translators: %s: new status label */
                __("Status zahtevka za odstop od pogodbe je bil spremenjen v: %s.", "gr-odstop-od-pogodbe"),
                $label
            );
            if (!empty($comment)) {
                $note .= "\n" . sprintf(
                    /* translators: %s: comment text */
                    __("Opomba/razlog: %s", "gr-odstop-od-pogodbe"),
                    $comment
                );
            }
            $order->add_order_note($note);
        }

        // Send email to customer if enabled
        $enable_email = get_option('grcop_enable_email_' . $new_status, 'yes');
        if ('yes' === $enable_email && in_array($new_status, ['approved', 'rejected', 'refunded'])) {
            $customer_email = $row->customer_email;
            if (!empty($customer_email)) {
                $customer_name = $row->customer_name;
                $date_time = current_time('mysql');

                $items = json_decode($row->items_json, true);
                $returned_items_summary = [];
                if (is_array($items)) {
                    foreach ($items as $item) {
                        $returned_items_summary[] = $item['name'] . ' x ' . $item['qty'];
                    }
                }
                $returned_items_str = implode("\n", $returned_items_summary);

                $method_labels = [
                    'original' => __('Originalno plačilno sredstvo', 'gr-odstop-od-pogodbe'),
                    'iban'     => __('Vračilo na bančni račun (TRR)', 'gr-odstop-od-pogodbe'),
                    'coupon'   => __('Dobropis / Kupon', 'gr-odstop-od-pogodbe')
                ];
                $refund_method = $row->refund_method;
                $refund_details = $row->refund_details;
                $refund_details_str = $method_labels[$refund_method] ?? $refund_method;
                if (!empty($refund_details)) {
                    $refund_details_str .= "\nTRR/Podrobnosti: " . $refund_details;
                }

                $instructions = get_option('grcop_return_address', '');
                if (empty($instructions)) {
                    $instructions = \GrCop\GrCop_Main::get_default_store_address();
                }

                $comment_section = '';
                if (!empty($comment)) {
                    $comment_section = sprintf(
                        /* translators: %s: comment text */
                        __("Opomba / razlog:\n%s", 'gr-odstop-od-pogodbe'),
                        $comment
                    );
                }

                $placeholders = [
                    '{order_id}'            => $row->order_id,
                    '{customer_name}'       => $customer_name,
                    '{date_time}'           => $date_time,
                    '{returned_items}'      => $returned_items_str,
                    '{refund_details}'      => $refund_details_str,
                    '{return_instructions}' => $instructions,
                    '{comment}'             => $comment,
                    '{comment_section}'     => $comment_section,
                ];

                $default_subjects = [
                    'approved' => __('Zahtevek za odstop od pogodbe odobren - Naročilo #{order_id}', 'gr-odstop-od-pogodbe'),
                    'rejected' => __('Zahtevek za odstop od pogodbe zavrnjen - Naročilo #{order_id}', 'gr-odstop-od-pogodbe'),
                    'refunded' => __('Kupnina za odstop od pogodbe povrnjena - Naročilo #{order_id}', 'gr-odstop-od-pogodbe')
                ];

                $site_name = get_bloginfo('name');
                if (empty($site_name)) {
                    $site_name = __('trgovine', 'gr-odstop-od-pogodbe');
                }

                $default_bodies = [
                    'approved' => sprintf("Spoštovani {customer_name},\n\nObveščamo vas, da je bil vaš zahtevek za odstop od pogodbe za naročilo #{order_id} odobren.\n\n{comment_section}\n\nLep pozdrav,\nEkipa %s", $site_name),
                    'rejected' => sprintf("Spoštovani {customer_name},\n\nObveščamo vas, da je bil vaš zahtevek za odstop od pogodbe za naročilo #{order_id} zavrnjen.\n\n{comment_section}\n\nLep pozdrav,\nEkipa %s", $site_name),
                    'refunded' => sprintf("Spoštovani {customer_name},\n\nObveščamo vas, da je bila kupnina za vaš odstop od pogodbe za naročilo #{order_id} povrnjena.\n\n{comment_section}\n\nLep pozdrav,\nEkipa %s", $site_name)
                ];

                $subject_option_key = 'grcop_email_' . $new_status . '_subject';
                $body_option_key = 'grcop_email_' . $new_status . '_body';

                $subject = get_option($subject_option_key, $default_subjects[$new_status]);
                if (empty($subject)) {
                    $subject = $default_subjects[$new_status];
                }
                $body = get_option($body_option_key, $default_bodies[$new_status]);
                if (empty($body)) {
                    $body = $default_bodies[$new_status];
                }

                $subject = strtr($subject, $placeholders);
                $body = strtr($body, $placeholders);

                $headers = ['Content-Type: text/html; charset=UTF-8'];
                $mailer = WC()->mailer();
                // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
                $wrapped_body = $mailer->wrap_message($subject, wpautop(make_clickable(esc_html($body))));
                $mailer->send($customer_email, $subject, $wrapped_body, $headers);
            }
        }

        return true;
    }

    /**
     * Exports all database entries to CSV.
     */
    public static function export_to_csv()
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'grcop_withdrawals';
        
        // Fetch all rows
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows = $wpdb->get_results("SELECT * FROM $table_name ORDER BY created_at DESC", ARRAY_A);

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="grcop-odstopi-od-pogodbe-' . gmdate('Y-m-d') . '.csv"');
        
        $output = fopen('php://output', 'w');
        // UTF-8 BOM for Excel
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fputs
        fputs($output, "\xEF\xBB\xBF");

        // Headers
        fputcsv($output, [
            'ID',
            'Številka naročila (Order ID)',
            'Ime in priimek kupca',
            'E-pošta kupca',
            'Izdelki za vračilo',
            'Način vračila',
            'Podrobnosti vračila (npr. IBAN)',
            'Status',
            'IP Naslov',
            'Datum in čas'
        ]);

        foreach ($rows as $row) {
            $items = json_decode($row['items_json'], true);
            $items_str = '';
            if (is_array($items)) {
                $item_parts = [];
                foreach ($items as $item) {
                    $item_parts[] = $item['name'] . ' (Količina: ' . $item['qty'] . ')';
                }
                $items_str = implode(', ', $item_parts);
            }

            fputcsv($output, [
                $row['id'],
                $row['order_id'],
                $row['customer_name'],
                $row['customer_email'],
                $items_str,
                $row['refund_method'],
                $row['refund_details'],
                $row['status'],
                $row['ip_address'],
                $row['created_at']
            ]);
        }

        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
        fclose($output);
        exit;
    }

    /**
     * Process list table actions (deleting and changing status).
     */
    public function process_actions()
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'grcop_withdrawals';

        // Single delete action
        if (isset($_GET['action']) && 'delete' === $_GET['action'] && isset($_GET['id'])) {
            $id = intval($_GET['id']);
            $nonce = isset($_GET['_wpnonce']) ? sanitize_text_field(wp_unslash($_GET['_wpnonce'])) : '';

            if (wp_verify_nonce($nonce, 'grcop_delete_row_' . $id)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->delete($table_name, ['id' => $id], ['%d']);
                wp_safe_redirect(admin_url('admin.php?page=grcop-withdrawals-log&deleted=1'));
                exit;
            }
        }

        // Change status action
        if (isset($_GET['action']) && 'change_status' === $_GET['action'] && isset($_GET['id']) && isset($_GET['status'])) {
            $id = intval($_GET['id']);
            // phpcs:ignore WordPress.Security.NonceVerification.Recommended
            $new_status = sanitize_text_field(wp_unslash($_GET['status']));
            $nonce = isset($_GET['_wpnonce']) ? sanitize_text_field(wp_unslash($_GET['_wpnonce'])) : '';

            if (wp_verify_nonce($nonce, 'grcop_status_row_' . $id)) {
                $comment = isset($_GET['comment']) ? sanitize_textarea_field(wp_unslash($_GET['comment'])) : '';
                self::update_withdrawal_status($id, $new_status, $comment);
                wp_safe_redirect(admin_url('admin.php?page=grcop-withdrawals-log&status_updated=1'));
                exit;
            }
        }
    }

    /**
     * Columns definition.
     */
    public function get_columns()
    {
        return [
            'order_id'       => __('Naročilo', 'gr-odstop-od-pogodbe'),
            'customer'       => __('Kupec', 'gr-odstop-od-pogodbe'),
            'items'          => __('Vračilo artiklov', 'gr-odstop-od-pogodbe'),
            'refund_details' => __('Podrobnosti vračila', 'gr-odstop-od-pogodbe'),
            'status'         => __('Status', 'gr-odstop-od-pogodbe'),
            'ip_address'     => __('IP Naslov', 'gr-odstop-od-pogodbe'),
            'created_at'     => __('Datum in čas', 'gr-odstop-od-pogodbe'),
            'actions'        => __('Dejanja', 'gr-odstop-od-pogodbe')
        ];
    }

    /**
     * Default column renderer.
     */
    public function column_default($item, $column_name)
    {
        switch ($column_name) {
            case 'ip_address':
                return esc_html($item['ip_address']);
            case 'created_at':
                return esc_html($item['created_at']);
            default:
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_print_r
                return print_r($item, true); // backup debug
        }
    }

    /**
     * Render Order ID Column.
     */
    public function column_order_id($item)
    {
        $order = wc_get_order($item['order_id']);
        if ($order) {
            $url = admin_url('post.php?post=' . $item['order_id'] . '&action=edit');
            if (class_exists('\Automattic\WooCommerce\Internal\Admin\Orders\PageController')) {
                // Support HPOS
                $url = \Automattic\WooCommerce\Utilities\OrderUtil::get_order_admin_edit_url($item['order_id']);
            }
            return sprintf(
                '<strong><a href="%s">#%s</a></strong>',
                esc_url($url),
                esc_html($item['order_id'])
            );
        }
        return esc_html($item['order_id']) . ' ' . __('(Naročilo ne obstaja)', 'gr-odstop-od-pogodbe');
    }

    /**
     * Render Customer Column.
     */
    public function column_customer($item)
    {
        return sprintf(
            '<strong>%s</strong><br><a href="mailto:%s">%s</a>',
            esc_html($item['customer_name']),
            esc_attr($item['customer_email']),
            esc_html($item['customer_email'])
        );
    }

    /**
     * Render Items Column.
     */
    public function column_items($item)
    {
        $items = json_decode($item['items_json'], true);
        if (!is_array($items) || empty($items)) {
            return '<span class="description">' . __('Ni podatkov', 'gr-odstop-od-pogodbe') . '</span>';
        }

        $html = '<ul style="margin: 0; padding-left: 15px; list-style-type: square;">';
        foreach ($items as $itm) {
            $html .= sprintf(
                '<li>%s (Količina: <strong>%s</strong>)</li>',
                esc_html($itm['name']),
                esc_html($itm['qty'])
            );
        }
        $html .= '</ul>';
        return $html;
    }

    /**
     * Render Refund Details Column.
     */
    public function column_refund_details($item)
    {
        $method_labels = [
            'original' => __('Originalno plačilno sredstvo', 'gr-odstop-od-pogodbe'),
            'iban'     => __('Vračilo na TRR (IBAN)', 'gr-odstop-od-pogodbe'),
            'coupon'   => __('Dobropis / Kupon', 'gr-odstop-od-pogodbe'),
            'default'  => __('Zakonito vračilo', 'gr-odstop-od-pogodbe'),
        ];
        
        $method = $method_labels[$item['refund_method']] ?? esc_html($item['refund_method']);
        $html = '<strong>' . $method . '</strong>';
        if (!empty($item['refund_details'])) {
            $html .= '<br><span class="description">' . esc_html($item['refund_details']) . '</span>';
        }
        return $html;
    }

    /**
     * Render Status Column.
     */
    public function column_status($item)
    {
        $status = $item['status'];
        $class = 'grcop-status-' . esc_attr($status);
        $labels = [
            'pending'  => __('V obdelavi', 'gr-odstop-od-pogodbe'),
            'approved' => __('Odobreno', 'gr-odstop-od-pogodbe'),
            'refunded' => __('Povrnjeno', 'gr-odstop-od-pogodbe'),
            'rejected' => __('Zavrnjeno', 'gr-odstop-od-pogodbe')
        ];
        
        $label = $labels[$status] ?? ucfirst($status);
        
        return sprintf(
            '<span class="grcop-status-badge %s">%s</span>',
            $class,
            esc_html($label)
        );
    }

    /**
     * Render Actions Column.
     */
    public function column_actions($item)
    {
        $id = $item['id'];
        
        $actions = [];
        
        // Status transitions nonces
        $nonce_status = wp_create_nonce('grcop_status_row_' . $id);
        $nonce_delete = wp_create_nonce('grcop_delete_row_' . $id);

        $base_url = admin_url('admin.php?page=grcop-withdrawals-log');

        if ($item['status'] !== 'approved') {
            $actions['approve'] = sprintf(
                '<a href="%s" class="button button-small" onclick="return (function(el) { var r = prompt(\'%s\'); if (r === null) return false; if (r.trim() !== \'\') { el.href += \'&comment=\' + encodeURIComponent(r); } return true; })(this);">%s</a>',
                esc_url(add_query_arg(['action' => 'change_status', 'id' => $id, 'status' => 'approved', '_wpnonce' => $nonce_status], $base_url)),
                esc_js(__('Vnesite neobvezno opombo/navodila za kupca ob odobritvi:', 'gr-odstop-od-pogodbe')),
                __('Odobri', 'gr-odstop-od-pogodbe')
            );
        }

        if ($item['status'] !== 'refunded') {
            $actions['refund'] = sprintf(
                '<a href="%s" class="button button-small button-primary" onclick="return (function(el) { var r = prompt(\'%s\'); if (r === null) return false; if (r.trim() !== \'\') { el.href += \'&comment=\' + encodeURIComponent(r); } return true; })(this);">%s</a>',
                esc_url(add_query_arg(['action' => 'change_status', 'id' => $id, 'status' => 'refunded', '_wpnonce' => $nonce_status], $base_url)),
                esc_js(__('Vnesite neobvezno opombo za kupca ob povračilu:', 'gr-odstop-od-pogodbe')),
                __('Označi kot povrnjeno', 'gr-odstop-od-pogodbe')
            );
        }

        if ($item['status'] !== 'rejected') {
            $actions['reject'] = sprintf(
                '<a href="%s" class="button button-small grcop-button-danger" onclick="return (function(el) { var r = prompt(\'%s\'); if (r === null) return false; if (r.trim() !== \'\') { el.href += \'&comment=\' + encodeURIComponent(r); } return true; })(this);">%s</a>',
                esc_url(add_query_arg(['action' => 'change_status', 'id' => $id, 'status' => 'rejected', '_wpnonce' => $nonce_status], $base_url)),
                esc_js(__('Vnesite razlog za zavrnitev (bo poslan kupcu):', 'gr-odstop-od-pogodbe')),
                __('Zavrni', 'gr-odstop-od-pogodbe')
            );
        }

        $actions['delete'] = sprintf(
            '<a href="%s" class="submitdelete" style="color:#a00; text-decoration:none; margin-left:8px;" onclick="return confirm(\'%s\')">%s</a>',
            esc_url(add_query_arg(['action' => 'delete', 'id' => $id, '_wpnonce' => $nonce_delete], $base_url)),
            esc_js(__('Ali ste prepričani, da želite izbrisati ta zapis?', 'gr-odstop-od-pogodbe')),
            __('Izbriši', 'gr-odstop-od-pogodbe')
        );

        return implode(' ', $actions);
    }

    /**
     * Define sortable columns.
     */
    public function get_sortable_columns()
    {
        return [
            'order_id'   => ['order_id', true],
            'created_at' => ['created_at', true],
            'status'     => ['status', false]
        ];
    }

    /**
     * Query data and prepare items.
     */
    public function prepare_items()
    {
        global $wpdb;
        $table_name = $wpdb->prefix . 'grcop_withdrawals';

        // Set column headers (required for WP_List_Table)
        $columns = $this->get_columns();
        $hidden = [];
        $sortable = $this->get_sortable_columns();
        $this->_column_headers = [$columns, $hidden, $sortable];

        // Handle pagination
        $per_page = 20;
        $current_page = $this->get_pagenum();
        $offset = ($current_page - 1) * $per_page;

        // Sorting
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        $orderby = isset($_GET['orderby']) ? sanitize_sql_orderby(wp_unslash($_GET['orderby'])) : 'created_at';
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
        $order = isset($_GET['order']) && 'asc' === strtolower(wp_unslash($_GET['order'])) ? 'ASC' : 'DESC';
        
        $allowed_orderby = ['created_at', 'order_id', 'status'];
        if (!in_array($orderby, $allowed_orderby)) {
            $orderby = 'created_at';
        }

        // Count query
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $total_items = $wpdb->get_var("SELECT COUNT(id) FROM $table_name");

        // Query data
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $this->items = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table_name ORDER BY $orderby $order LIMIT %d OFFSET %d", $per_page, $offset), ARRAY_A);

        $this->set_pagination_args([
            'total_items' => $total_items,
            'per_page'    => $per_page,
            'total_pages' => ceil($total_items / $per_page)
        ]);
    }
}
