<?php
if ( ! defined( 'ABSPATH' ) ) exit;

/**
 * Admin-facing functionality for CodeDE Table Viewer (Lite).
 * No design editor, no filter/sort/search, no license system.
 */
class CDTV_Admin {

    private $plugin_name;
    private $version;

    public function __construct( $plugin_name, $version ) {
        $this->plugin_name = $plugin_name;
        $this->version     = $version;
    }

    /* -----------------------------------------------------------------------
     * Assets
     * -------------------------------------------------------------------- */

    public function enqueue_styles( $hook ) {
        if ( false === strpos( $hook, 'cdtv' ) ) return;
        wp_enqueue_style(
            $this->plugin_name . '-admin',
            CDTV_PLUGIN_URL . 'admin/css/cdtv-admin.css',
            array(),
            $this->version
        );
    }

    public function enqueue_scripts( $hook ) {
        if ( false === strpos( $hook, 'cdtv' ) ) return;
        wp_enqueue_script(
            $this->plugin_name . '-admin',
            CDTV_PLUGIN_URL . 'admin/js/cdtv-admin.js',
            array( 'jquery' ),
            $this->version,
            true
        );
        wp_localize_script(
            $this->plugin_name . '-admin',
            'cdtvAdmin',
            array(
                'ajaxUrl'    => admin_url( 'admin-ajax.php' ),
                'nonce'      => wp_create_nonce( 'cdtv_admin_nonce' ),
                'isLicensed' => cdtv_is_licensed(),
                'upgradeUrl' => ( function_exists( 'cdtv_freemius' ) && cdtv_freemius() )
                    ? esc_url( cdtv_freemius()->get_upgrade_url() )
                    : 'https://checkout.freemius.com/plugin/28358/plan/46738/',
                'strings' => array(
                    'uploadSuccess' => esc_html__( 'File uploaded successfully!', 'codede-table-viewer' ),
                    'uploadError'   => esc_html__( 'Upload failed. Please try again.', 'codede-table-viewer' ),
                    'confirmDelete' => esc_html__( 'Are you sure you want to delete this table?', 'codede-table-viewer' ),
                    'saving'               => esc_html__( 'Saving…', 'codede-table-viewer' ),
                    'saved'                => esc_html__( 'Saved!', 'codede-table-viewer' ),
                ),
            )
        );
    }

    /* -----------------------------------------------------------------------
     * Admin Menu
     * -------------------------------------------------------------------- */

    public function add_admin_menu() {
        add_menu_page(
            esc_html__( 'CodeDE Table I/E', 'codede-table-viewer' ),
            esc_html__( 'CodeDE Table I/E', 'codede-table-viewer' ),
            'manage_options',
            'cdtv-tables',
            array( $this, 'page_tables' ),
            'dashicons-media-spreadsheet',
            58
        );
        add_submenu_page(
            'cdtv-tables',
            esc_html__( 'All Tables', 'codede-table-viewer' ),
            esc_html__( 'All Tables', 'codede-table-viewer' ),
            'manage_options',
            'cdtv-tables',
            array( $this, 'page_tables' )
        );
        add_submenu_page(
            'cdtv-tables',
            esc_html__( 'Add New Table', 'codede-table-viewer' ),
            esc_html__( 'Add New Table', 'codede-table-viewer' ),
            'manage_options',
            'cdtv-import',
            array( $this, 'page_import' )
        );
        $design_label = cdtv_is_licensed()
            ? esc_html__( 'Design Generator', 'codede-table-viewer' )
            : esc_html__( 'Design Generator 🔒', 'codede-table-viewer' );
        add_submenu_page(
            'cdtv-tables',
            $design_label,
            $design_label,
            'manage_options',
            'cdtv-design',
            array( $this, 'page_design' )
        );
        add_submenu_page(
            'cdtv-tables',
            esc_html__( 'Settings', 'codede-table-viewer' ),
            esc_html__( 'Settings', 'codede-table-viewer' ),
            'manage_options',
            'cdtv-settings',
            array( $this, 'page_settings' )
        );
        $upgrade_label = cdtv_is_licensed()
            ? esc_html__( 'Features', 'codede-table-viewer' )
            : esc_html__( 'Get Pro ⭐', 'codede-table-viewer' );
        add_submenu_page(
            'cdtv-tables',
            $upgrade_label,
            $upgrade_label,
            'manage_options',
            'cdtv-upgrade',
            array( $this, 'page_upgrade' )
        );
    }

    /* -----------------------------------------------------------------------
     * Settings API
     * -------------------------------------------------------------------- */

    public function register_settings() {
        register_setting( 'cdtv_settings', 'cdtv_language', array(
            'sanitize_callback' => 'sanitize_text_field',
        ) );
        register_setting( 'cdtv_settings', 'cdtv_decimal_separator', array(
            'sanitize_callback' => function( $v ) {
                $v = sanitize_text_field( (string) $v );
                return ( mb_strlen( $v ) === 1 ) ? $v : '.';
            },
        ) );
    }

    /* -----------------------------------------------------------------------
     * Page Callbacks
     * -------------------------------------------------------------------- */

    public function page_tables() {
        if ( ! current_user_can( 'manage_options' ) ) return;
        require_once CDTV_PLUGIN_DIR . 'admin/partials/cdtv-admin-display.php';
    }

    public function page_import() {
        if ( ! current_user_can( 'manage_options' ) ) return;
        require_once CDTV_PLUGIN_DIR . 'admin/partials/cdtv-admin-import.php';
    }

    public function page_design() {
        if ( ! current_user_can( 'manage_options' ) ) return;
        require_once CDTV_PLUGIN_DIR . 'admin/partials/cdtv-admin-design.php';
    }

    public function page_settings() {
        if ( ! current_user_can( 'manage_options' ) ) return;
        require_once CDTV_PLUGIN_DIR . 'admin/partials/cdtv-admin-settings.php';
    }

    public function page_upgrade() {
        if ( ! current_user_can( 'manage_options' ) ) return;
        require_once CDTV_PLUGIN_DIR . 'admin/partials/cdtv-admin-upgrade.php';
    }

    /* -----------------------------------------------------------------------
     * AJAX: File Upload
     * -------------------------------------------------------------------- */

    public function ajax_upload_file() {
        check_ajax_referer( 'cdtv_admin_nonce', 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) wp_die( -1 );

        if ( empty( $_FILES['file'] ) || ! isset( $_FILES['file']['name'], $_FILES['file']['tmp_name'] ) ) {
            wp_send_json_error( array( 'message' => esc_html__( 'No file received.', 'codede-table-viewer' ) ) );
        }

        $allowed_types = array( 'xlsx', 'xls', 'csv' );
        $file_name     = sanitize_file_name( wp_unslash( $_FILES['file']['name'] ) );
        $ext           = strtolower( pathinfo( $file_name, PATHINFO_EXTENSION ) );

        if ( ! in_array( $ext, $allowed_types, true ) ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Invalid file type. Only .xlsx, .xls, .csv allowed.', 'codede-table-viewer' ) ) );
        }

        $allowed_mimes = array(
            'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'xls'  => 'application/vnd.ms-excel',
            'csv'  => 'text/csv',
        );

        add_filter( 'upload_dir', array( $this, 'cdtv_upload_dir' ) );
        $upload_result = wp_handle_upload(
            $_FILES['file'], // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
            array( 'test_form' => false, 'mimes' => $allowed_mimes )
        );
        remove_filter( 'upload_dir', array( $this, 'cdtv_upload_dir' ) );

        if ( isset( $upload_result['error'] ) ) {
            wp_send_json_error( array( 'message' => $upload_result['error'] ) );
        }

        $target_file = $upload_result['file'];
        $has_header  = ! empty( $_POST['has_header'] ) && '1' === sanitize_text_field( wp_unslash( $_POST['has_header'] ) );
        $parser      = new CDTV_Parser();
        $result      = $parser->parse( $target_file, $ext, $has_header );

        if ( is_wp_error( $result ) ) {
            wp_delete_file( $target_file );
            wp_send_json_error( array( 'message' => $result->get_error_message() ) );
        }

        $transient_key = 'cdtv_upload_' . get_current_user_id();
        set_transient( $transient_key, array(
            'file_path' => $target_file,
            'file_type' => $ext,
            'file_name' => basename( $target_file ),
        ), HOUR_IN_SECONDS );

        $upload_cell_styles  = array();
        $upload_cell_haligns = array();
        foreach ( (array) ( $result['cell_styles'] ?? array() ) as $ri => $cols ) {
            if ( $ri < 10 ) $upload_cell_styles[ $ri ] = $cols;
        }
        foreach ( (array) ( $result['cell_haligns'] ?? array() ) as $ri => $cols ) {
            if ( $ri < 10 ) $upload_cell_haligns[ $ri ] = $cols;
        }

        wp_send_json_success( array(
            'headers'       => $result['headers'],
            'preview_rows'  => array_slice( $result['rows'], 0, 10 ),
            'row_count'     => $result['row_count'],
            'column_count'  => $result['column_count'],
            'header_styles' => $result['header_styles'] ?? array(),
            'col_haligns'   => $result['col_haligns']   ?? array(),
            'cell_haligns'  => $upload_cell_haligns,
            'cell_styles'   => $upload_cell_styles,
        ) );
    }

    /* -----------------------------------------------------------------------
     * AJAX: Save Table
     * -------------------------------------------------------------------- */

    public function ajax_save_table() {
        check_ajax_referer( 'cdtv_admin_nonce', 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) wp_die( -1 );

        $title         = sanitize_text_field( wp_unslash( $_POST['title'] ?? '' ) );
        $has_header    = ! empty( $_POST['has_header'] ) && '1' === sanitize_text_field( wp_unslash( $_POST['has_header'] ) );
        $table_id      = isset( $_POST['table_id'] ) ? absint( $_POST['table_id'] ) : 0;
        $custom_css    = isset( $_POST['custom_css'] ) ? wp_strip_all_tags( wp_unslash( $_POST['custom_css'] ) ) : '';
        $rows_per_page   = max( 0, absint( wp_unslash( $_POST['rows_per_page'] ?? 0 ) ) );
        $scroll_mode_raw = sanitize_text_field( wp_unslash( $_POST['scroll_mode'] ?? 'auto' ) );
        $scroll_mode     = in_array( $scroll_mode_raw, array( 'auto', 'always', 'off' ), true ) ? $scroll_mode_raw : 'auto';
        $reset_styles         = ! empty( $_POST['reset_styles'] ) && '1' === sanitize_text_field( wp_unslash( $_POST['reset_styles'] ) );
        $enable_search        = ! empty( $_POST['enable_search'] )       && '1' === sanitize_text_field( wp_unslash( $_POST['enable_search'] ) );
        $enable_sort          = ! empty( $_POST['enable_sort'] )         && '1' === sanitize_text_field( wp_unslash( $_POST['enable_sort'] ) );
        $enable_excel_format  = ! empty( $_POST['enable_excel_format'] ) && '1' === sanitize_text_field( wp_unslash( $_POST['enable_excel_format'] ) );
        $border_preset   = sanitize_text_field( wp_unslash( $_POST['border_preset'] ?? 'none' ) );
        $border_style    = sanitize_text_field( wp_unslash( $_POST['border_style']  ?? 'solid' ) );
        $border_color    = sanitize_hex_color( wp_unslash( $_POST['border_color']   ?? '#dddddd' ) ) ?: '#dddddd';
        $border_width    = max( 1, min( 5, absint( wp_unslash( $_POST['border_width'] ?? 1 ) ) ) );
        $border_data     = array(
            'preset' => in_array( $border_preset, array( 'none', 'outer', 'all' ), true ) ? $border_preset : 'none',
            'style'  => in_array( $border_style, array( 'solid', 'dashed', 'dotted', 'double' ), true ) ? $border_style : 'solid',
            'color'  => $border_color,
            'width'  => $border_width,
        );

        $transient_key = 'cdtv_upload_' . get_current_user_id();
        $upload_data   = get_transient( $transient_key );

        if ( ! $upload_data ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Session expired. Please re-upload the file.', 'codede-table-viewer' ) ) );
        }

        $parser = new CDTV_Parser();
        $result = $parser->parse( $upload_data['file_path'], $upload_data['file_type'], $has_header );

        if ( is_wp_error( $result ) ) {
            wp_send_json_error( array( 'message' => $result->get_error_message() ) );
        }

        if ( $table_id > 0 ) {
            wp_update_post( array( 'ID' => $table_id, 'post_title' => $title ) );
        } else {
            $table_id = wp_insert_post( array(
                'post_type'   => 'cdtv_table',
                'post_title'  => $title ?: esc_html__( 'Untitled Table', 'codede-table-viewer' ),
                'post_status' => 'publish',
            ) );
        }

        if ( is_wp_error( $table_id ) ) {
            wp_send_json_error( array( 'message' => $table_id->get_error_message() ) );
        }

        update_post_meta( $table_id, '_cdtv_file_path',    $upload_data['file_path'] );
        update_post_meta( $table_id, '_cdtv_file_type',    $upload_data['file_type'] );
        update_post_meta( $table_id, '_cdtv_file_name',    $upload_data['file_name'] );
        update_post_meta( $table_id, '_cdtv_parsed_data',  $result );
        update_post_meta( $table_id, '_cdtv_column_count', $result['column_count'] );
        update_post_meta( $table_id, '_cdtv_row_count',    $result['row_count'] );
        update_post_meta( $table_id, '_cdtv_has_header',       $has_header ? 1 : 0 );
        update_post_meta( $table_id, '_cdtv_custom_css',       sanitize_textarea_field( $custom_css ) );
        update_post_meta( $table_id, '_cdtv_border_settings',  $border_data );
        update_post_meta( $table_id, '_cdtv_rows_per_page',    $rows_per_page );
        update_post_meta( $table_id, '_cdtv_scroll_mode',      $scroll_mode );
        update_post_meta( $table_id, '_cdtv_reset_styles',          $reset_styles        ? 1 : 0 );
        update_post_meta( $table_id, '_cdtv_enable_search',         $enable_search       ? 1 : 0 );
        update_post_meta( $table_id, '_cdtv_enable_sort',           $enable_sort         ? 1 : 0 );
        update_post_meta( $table_id, '_cdtv_enable_excel_format',   $enable_excel_format ? 1 : 0 );

        delete_transient( $transient_key );

        $shortcode = '[cdtv id="' . $table_id . '"]';

        wp_send_json_success( array(
            'table_id'  => $table_id,
            'shortcode' => $shortcode,
            'message'   => esc_html__( 'Table saved successfully!', 'codede-table-viewer' ),
        ) );
    }

    /* -----------------------------------------------------------------------
     * AJAX: Delete Table
     * -------------------------------------------------------------------- */

    public function ajax_delete_table() {
        check_ajax_referer( 'cdtv_admin_nonce', 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) wp_die( -1 );

        $table_id = absint( $_POST['table_id'] ?? 0 );
        if ( ! $table_id ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Invalid table ID.', 'codede-table-viewer' ) ) );
        }

        $file_path = get_post_meta( $table_id, '_cdtv_file_path', true );
        if ( $file_path && file_exists( $file_path ) ) {
            wp_delete_file( $file_path );
        }

        wp_delete_post( $table_id, true );
        wp_send_json_success( array( 'message' => esc_html__( 'Table deleted.', 'codede-table-viewer' ) ) );
    }

    /* -----------------------------------------------------------------------
     * AJAX: Get Table Preview (for edit mode)
     * -------------------------------------------------------------------- */

    public function ajax_get_table_preview() {
        check_ajax_referer( 'cdtv_admin_nonce', 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) wp_die( -1 );

        $table_id = absint( $_POST['table_id'] ?? 0 );
        if ( ! $table_id ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Invalid table ID.', 'codede-table-viewer' ) ) );
        }

        $parsed = get_post_meta( $table_id, '_cdtv_parsed_data', true );
        if ( ! $parsed ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Table data not found.', 'codede-table-viewer' ) ) );
        }

        // Slice sparse cell-style/halign maps to first 10 rows only
        $cell_styles  = array();
        $cell_haligns = array();
        foreach ( (array) ( $parsed['cell_styles'] ?? array() ) as $row_idx => $cols ) {
            if ( $row_idx < 10 ) $cell_styles[ $row_idx ] = $cols;
        }
        foreach ( (array) ( $parsed['cell_haligns'] ?? array() ) as $row_idx => $cols ) {
            if ( $row_idx < 10 ) $cell_haligns[ $row_idx ] = $cols;
        }

        wp_send_json_success( array(
            'headers'       => $parsed['headers'],
            'preview_rows'  => array_slice( $parsed['rows'], 0, 10 ),
            'row_count'     => $parsed['row_count'],
            'column_count'  => $parsed['column_count'],
            'header_styles' => $parsed['header_styles'] ?? array(),
            'col_haligns'   => $parsed['col_haligns']   ?? array(),
            'cell_haligns'  => $cell_haligns,
            'cell_styles'   => $cell_styles,
        ) );
    }

    /* -----------------------------------------------------------------------
     * AJAX: Save Meta Only (title + css, no new file upload)
     * -------------------------------------------------------------------- */

    public function ajax_save_table_meta() {
        check_ajax_referer( 'cdtv_admin_nonce', 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) wp_die( -1 );

        $table_id      = absint( $_POST['table_id'] ?? 0 );
        $title         = sanitize_text_field( wp_unslash( $_POST['title'] ?? '' ) );
        $custom_css    = isset( $_POST['custom_css'] ) ? sanitize_textarea_field( wp_unslash( $_POST['custom_css'] ) ) : '';
        $rows_per_page   = max( 0, absint( wp_unslash( $_POST['rows_per_page'] ?? 0 ) ) );
        $scroll_mode_raw = sanitize_text_field( wp_unslash( $_POST['scroll_mode'] ?? 'auto' ) );
        $scroll_mode     = in_array( $scroll_mode_raw, array( 'auto', 'always', 'off' ), true ) ? $scroll_mode_raw : 'auto';
        $reset_styles         = ! empty( $_POST['reset_styles'] ) && '1' === sanitize_text_field( wp_unslash( $_POST['reset_styles'] ) );
        $enable_search        = ! empty( $_POST['enable_search'] )       && '1' === sanitize_text_field( wp_unslash( $_POST['enable_search'] ) );
        $enable_sort          = ! empty( $_POST['enable_sort'] )         && '1' === sanitize_text_field( wp_unslash( $_POST['enable_sort'] ) );
        $enable_excel_format  = ! empty( $_POST['enable_excel_format'] ) && '1' === sanitize_text_field( wp_unslash( $_POST['enable_excel_format'] ) );
        $border_preset   = sanitize_text_field( wp_unslash( $_POST['border_preset'] ?? 'none' ) );
        $border_style    = sanitize_text_field( wp_unslash( $_POST['border_style']  ?? 'solid' ) );
        $border_color    = sanitize_hex_color( wp_unslash( $_POST['border_color']   ?? '#dddddd' ) ) ?: '#dddddd';
        $border_width    = max( 1, min( 5, absint( wp_unslash( $_POST['border_width'] ?? 1 ) ) ) );
        $border_data     = array(
            'preset' => in_array( $border_preset, array( 'none', 'outer', 'all' ), true ) ? $border_preset : 'none',
            'style'  => in_array( $border_style, array( 'solid', 'dashed', 'dotted', 'double' ), true ) ? $border_style : 'solid',
            'color'  => $border_color,
            'width'  => $border_width,
        );

        if ( ! $table_id ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Invalid table ID.', 'codede-table-viewer' ) ) );
        }

        wp_update_post( array(
            'ID'         => $table_id,
            'post_title' => $title ?: esc_html__( 'Untitled Table', 'codede-table-viewer' ),
        ) );
        update_post_meta( $table_id, '_cdtv_custom_css',      $custom_css );
        update_post_meta( $table_id, '_cdtv_border_settings', $border_data );
        update_post_meta( $table_id, '_cdtv_rows_per_page',   $rows_per_page );
        update_post_meta( $table_id, '_cdtv_scroll_mode',     $scroll_mode );
        update_post_meta( $table_id, '_cdtv_reset_styles',         $reset_styles        ? 1 : 0 );
        update_post_meta( $table_id, '_cdtv_enable_search',        $enable_search       ? 1 : 0 );
        update_post_meta( $table_id, '_cdtv_enable_sort',          $enable_sort         ? 1 : 0 );
        update_post_meta( $table_id, '_cdtv_enable_excel_format',  $enable_excel_format ? 1 : 0 );

        wp_send_json_success( array(
            'table_id'  => $table_id,
            'shortcode' => '[cdtv id="' . $table_id . '"]',
            'message'   => esc_html__( 'Table saved successfully!', 'codede-table-viewer' ),
        ) );
    }

    /* -----------------------------------------------------------------------
     * AJAX: Export Table as CSV
     * -------------------------------------------------------------------- */

    public function ajax_export_table() {
        check_ajax_referer( 'cdtv_admin_nonce', 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) wp_die( -1 );

        $table_id = absint( $_POST['table_id'] ?? 0 );
        $parsed   = get_post_meta( $table_id, '_cdtv_parsed_data', true );

        if ( ! $parsed ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Table not found.', 'codede-table-viewer' ) ) );
        }

        $headers  = $parsed['headers'];
        $rows     = $parsed['rows'];
        $filename = sanitize_title( get_the_title( $table_id ) ) ?: 'export';

        header( 'Content-Type: text/csv; charset=utf-8' );
        header( 'Content-Disposition: attachment; filename="' . sanitize_file_name( $filename ) . '.csv"' );
        // phpcs:disable WordPress.WP.AlternativeFunctions
        $out = fopen( 'php://output', 'w' );
        fwrite( $out, "\xEF\xBB\xBF" ); // UTF-8 BOM
        fputcsv( $out, $headers );
        foreach ( $rows as $row ) {
            fputcsv( $out, $row );
        }
        fclose( $out );
        // phpcs:enable
        exit;
    }

    /* -----------------------------------------------------------------------
     * AJAX: Export Table as XLSX (Pro)
     * -------------------------------------------------------------------- */

    public function ajax_export_xlsx() {
        check_ajax_referer( 'cdtv_admin_nonce', 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) wp_die( -1 );
        if ( ! cdtv_is_licensed() ) {
            wp_send_json_error( array( 'message' => esc_html__( 'XLSX export requires a Pro license.', 'codede-table-viewer' ) ) );
        }
        $table_id = absint( $_POST['table_id'] ?? 0 );
        $parsed   = get_post_meta( $table_id, '_cdtv_parsed_data', true );
        if ( ! $parsed ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Table not found.', 'codede-table-viewer' ) ) );
        }

        $headers  = $parsed['headers'];
        $rows     = $parsed['rows'];
        $filename = sanitize_title( get_the_title( $table_id ) ) ?: 'export';

        // phpcs:disable WordPress.WP.AlternativeFunctions
        $xlsx = $this->build_xlsx( $headers, $rows );
        // phpcs:enable

        header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
        header( 'Content-Disposition: attachment; filename="' . sanitize_file_name( $filename ) . '.xlsx"' );
        header( 'Content-Length: ' . strlen( $xlsx ) );
        header( 'Cache-Control: max-age=0' );
        // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
        echo $xlsx;
        exit;
    }

    /**
     * Build a minimal XLSX binary using PHP's ZipArchive.
     * No external library required — XLSX is a ZIP of XML files.
     *
     * Numeric cell values are written as numbers so Excel formulas work on them.
     * String values use a shared-string table for correct Unicode handling.
     *
     * @param array $headers Column header labels.
     * @param array $rows    2-D array of body rows.
     * @return string  Raw binary content of the .xlsx file.
     */
    private function build_xlsx( array $headers, array $rows ): string {

        // ── Column letter (0-indexed → "A", "Z", "AA", …) ──────────────────
        $col_letter = static function ( int $col ): string {
            $col++; // convert to 1-based
            $result = '';
            while ( $col > 0 ) {
                $col--;
                $result = chr( 65 + ( $col % 26 ) ) . $result;
                $col    = intdiv( $col, 26 );
            }
            return $result;
        };

        // ── Shared strings (text cells only) ────────────────────────────────
        $strings   = array();
        $str_index = array();
        $all_rows  = array_merge( array( $headers ), $rows );

        foreach ( $all_rows as $row ) {
            foreach ( $row as $cell ) {
                $val = (string) $cell;
                // Only add to shared strings if not purely numeric
                if ( ! is_numeric( $val ) && ! isset( $str_index[ $val ] ) ) {
                    $str_index[ $val ] = count( $strings );
                    $strings[]         = $val;
                }
            }
        }

        // ── Sheet XML ────────────────────────────────────────────────────────
        $sheet_xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
            . '<sheetData>';

        foreach ( $all_rows as $ri => $row ) {
            $rnum       = $ri + 1;
            $sheet_xml .= '<row r="' . $rnum . '">';
            foreach ( $row as $ci => $cell ) {
                $ref = $col_letter( $ci ) . $rnum;
                $val = (string) $cell;
                if ( is_numeric( $val ) && '' !== $val ) {
                    // Numeric cell — no t attribute, raw value
                    $sheet_xml .= '<c r="' . $ref . '"><v>' . htmlspecialchars( $val, ENT_XML1, 'UTF-8' ) . '</v></c>';
                } else {
                    // Shared string cell
                    $idx        = $str_index[ $val ] ?? 0;
                    $sheet_xml .= '<c r="' . $ref . '" t="s"><v>' . $idx . '</v></c>';
                }
            }
            $sheet_xml .= '</row>';
        }

        $sheet_xml .= '</sheetData></worksheet>';

        // ── Shared strings XML ───────────────────────────────────────────────
        $cnt = count( $strings );
        $ss  = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
             . '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"'
             . ' count="' . $cnt . '" uniqueCount="' . $cnt . '">';
        foreach ( $strings as $s ) {
            $ss .= '<si><t xml:space="preserve">' . htmlspecialchars( $s, ENT_XML1, 'UTF-8' ) . '</t></si>';
        }
        $ss .= '</sst>';

        // ── Assemble ZIP ─────────────────────────────────────────────────────
        $tmp = wp_tempnam( 'cdtv_xlsx_' );
        $zip = new ZipArchive();
        $zip->open( $tmp, ZipArchive::OVERWRITE );

        $zip->addFromString( '[Content_Types].xml',
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
            . '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
            . '<Default Extension="xml"  ContentType="application/xml"/>'
            . '<Override PartName="/xl/workbook.xml"'
            . '  ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
            . '<Override PartName="/xl/worksheets/sheet1.xml"'
            . '  ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
            . '<Override PartName="/xl/sharedStrings.xml"'
            . '  ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>'
            . '</Types>' );

        $zip->addFromString( '_rels/.rels',
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
            . '<Relationship Id="rId1"'
            . '  Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"'
            . '  Target="xl/workbook.xml"/>'
            . '</Relationships>' );

        $zip->addFromString( 'xl/workbook.xml',
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"'
            . '  xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
            . '<sheets><sheet name="Sheet1" sheetId="1" r:id="rId1"/></sheets>'
            . '</workbook>' );

        $zip->addFromString( 'xl/_rels/workbook.xml.rels',
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
            . '<Relationship Id="rId1"'
            . '  Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"'
            . '  Target="worksheets/sheet1.xml"/>'
            . '<Relationship Id="rId2"'
            . '  Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"'
            . '  Target="sharedStrings.xml"/>'
            . '</Relationships>' );

        $zip->addFromString( 'xl/worksheets/sheet1.xml', $sheet_xml );
        $zip->addFromString( 'xl/sharedStrings.xml', $ss );

        $zip->close();

        // phpcs:ignore WordPress.WP.AlternativeFunctions
        $content = file_get_contents( $tmp );
        wp_delete_file( $tmp );

        return $content;
    }

    /* -----------------------------------------------------------------------
     * AJAX: Save Global Design (Pro)
     * -------------------------------------------------------------------- */

    public function ajax_save_design() {
        check_ajax_referer( 'cdtv_admin_nonce', 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) wp_die( -1 );
        if ( ! cdtv_is_licensed() ) {
            wp_send_json_error( array( 'message' => __( 'Pro license required.', 'codede-table-viewer' ) ) );
        }
        $mode     = sanitize_text_field( wp_unslash( $_POST['mode'] ?? 'global' ) );
        $table_id = absint( $_POST['table_id'] ?? 0 );
        $css      = wp_strip_all_tags( wp_unslash( $_POST['css'] ?? '' ) );

        $design = array(
            'hdr_bg'     => sanitize_hex_color( wp_unslash( $_POST['hdr_bg']     ?? '#2271b1' ) ) ?: '#2271b1',
            'hdr_color'  => sanitize_hex_color( wp_unslash( $_POST['hdr_color']  ?? '#ffffff' ) ) ?: '#ffffff',
            'hdr_size'   => max( 8, min( 36, absint( wp_unslash( $_POST['hdr_size']  ?? 14 ) ) ) ),
            'hdr_bold'   => ! empty( $_POST['hdr_bold'] ) && '1' === sanitize_text_field( wp_unslash( $_POST['hdr_bold'] ) ),
            'row_bg'     => sanitize_hex_color( wp_unslash( $_POST['row_bg']     ?? '#ffffff' ) ) ?: '#ffffff',
            'row_color'  => sanitize_hex_color( wp_unslash( $_POST['row_color']  ?? '#333333' ) ) ?: '#333333',
            'row_stripe' => sanitize_hex_color( wp_unslash( $_POST['row_stripe'] ?? '#f7f9fc' ) ) ?: '#f7f9fc',
            'row_hover'  => sanitize_hex_color( wp_unslash( $_POST['row_hover']  ?? '#eaf3fb' ) ) ?: '#eaf3fb',
            'row_height' => max( 20, min( 120, absint( wp_unslash( $_POST['row_height'] ?? 36 ) ) ) ),
            'bdr_style'  => in_array( $_POST['bdr_style'] ?? '', array( 'all', 'outer', 'none' ), true ) ? sanitize_text_field( wp_unslash( $_POST['bdr_style'] ) ) : 'all',
            'bdr_color'  => sanitize_hex_color( wp_unslash( $_POST['bdr_color']  ?? '#dddddd' ) ) ?: '#dddddd',
            'bdr_width'  => max( 1, min( 5, absint( wp_unslash( $_POST['bdr_width'] ?? 1 ) ) ) ),
        );

        if ( 'table' === $mode && $table_id > 0 ) {
            // Per-table: save into Custom CSS + search/sort/excel toggles + filter cols
            $enable_search        = ! empty( $_POST['enable_search'] )       && '1' === sanitize_text_field( wp_unslash( $_POST['enable_search'] ) );
            $enable_sort          = ! empty( $_POST['enable_sort'] )         && '1' === sanitize_text_field( wp_unslash( $_POST['enable_sort'] ) );
            $enable_excel_format  = ! empty( $_POST['enable_excel_format'] ) && '1' === sanitize_text_field( wp_unslash( $_POST['enable_excel_format'] ) );
            $filter_cols          = sanitize_text_field( wp_unslash( $_POST['filter_cols'] ?? '' ) );
            $rows_per_page_dg   = max( 0, absint( wp_unslash( $_POST['rows_per_page'] ?? 0 ) ) );
            $sort_indicator_style = in_array( $_POST['sort_indicator_style'] ?? '', array( 'badge', 'outline', 'text', 'none' ), true )
                ? sanitize_text_field( wp_unslash( $_POST['sort_indicator_style'] ) ) : 'badge';
            update_post_meta( $table_id, '_cdtv_custom_css',              $css );
            update_post_meta( $table_id, '_cdtv_enable_search',           $enable_search       ? 1 : 0 );
            update_post_meta( $table_id, '_cdtv_enable_sort',             $enable_sort         ? 1 : 0 );
            update_post_meta( $table_id, '_cdtv_enable_excel_format',     $enable_excel_format ? 1 : 0 );
            update_post_meta( $table_id, '_cdtv_filter_cols',             $filter_cols );
            update_post_meta( $table_id, '_cdtv_rows_per_page',           $rows_per_page_dg );
            update_post_meta( $table_id, '_cdtv_sort_indicator_style',    $sort_indicator_style );
            update_post_meta( $table_id, '_cdtv_table_design',            $design );
        } else {
            // Global: save site-wide
            update_option( 'cdtv_global_design',     $design );
            update_option( 'cdtv_global_design_css', $css );
        }

        wp_send_json_success( array( 'message' => __( 'Design saved!', 'codede-table-viewer' ) ) );
    }

    /* -----------------------------------------------------------------------
     * AJAX: Get Table Design Settings (Pro)
     * -------------------------------------------------------------------- */

    public function ajax_get_table_design() {
        check_ajax_referer( 'cdtv_admin_nonce', 'nonce' );
        if ( ! current_user_can( 'manage_options' ) || ! cdtv_is_licensed() ) wp_die( -1 );

        $table_id = absint( $_POST['table_id'] ?? 0 );
        if ( ! $table_id ) wp_send_json_error();

        $parsed  = get_post_meta( $table_id, '_cdtv_parsed_data', true );
        $headers = array_values( $parsed['headers'] ?? array() );

        $saved_design = get_post_meta( $table_id, '_cdtv_table_design', true );

        wp_send_json_success( array(
            'custom_css'          => (string) get_post_meta( $table_id, '_cdtv_custom_css',           true ),
            'enable_search'       => (bool)   get_post_meta( $table_id, '_cdtv_enable_search',        true ),
            'enable_sort'         => (bool)   get_post_meta( $table_id, '_cdtv_enable_sort',          true ),
            'enable_excel_format' => (bool)   get_post_meta( $table_id, '_cdtv_enable_excel_format',  true ),
            'rows_per_page'       => (int)    get_post_meta( $table_id, '_cdtv_rows_per_page',        true ),
            'scroll_mode'         => (string) get_post_meta( $table_id, '_cdtv_scroll_mode',          true ),
            'filter_cols'         => (string) get_post_meta( $table_id, '_cdtv_filter_cols',          true ),
            'headers'               => $headers,
            'design'                => is_array( $saved_design ) ? $saved_design : array(),
            'sort_indicator_style'  => (string) get_post_meta( $table_id, '_cdtv_sort_indicator_style', true ) ?: 'badge',
        ) );
    }

    /* -----------------------------------------------------------------------
     * Upload Dir Filter
     * -------------------------------------------------------------------- */

    public function cdtv_upload_dir( $dirs ) {
        $subdir         = '/' . CDTV_UPLOAD_DIR;
        $dirs['subdir'] = $subdir;
        $dirs['path']   = $dirs['basedir'] . $subdir;
        $dirs['url']    = $dirs['baseurl'] . $subdir;
        return $dirs;
    }

    /* -----------------------------------------------------------------------
     * Plugin Action Links
     * -------------------------------------------------------------------- */

    public function add_action_links( $links ) {
        $pro_link      = '<a href="' . esc_url( admin_url( 'admin.php?page=cdtv-upgrade' ) ) . '" style="color:#d63638;font-weight:bold;">' . esc_html__( 'Get Pro ⭐', 'codede-table-viewer' ) . '</a>';
        $settings_link = '<a href="' . esc_url( admin_url( 'admin.php?page=cdtv-settings' ) ) . '">' . esc_html__( 'Settings', 'codede-table-viewer' ) . '</a>';
        array_unshift( $links, $pro_link, $settings_link );
        return $links;
    }
}
