<?php
namespace Unlockafe_addons\Widgets;

use Elementor\Widget_Base;
use Elementor\Controls_Manager;
use Elementor\Group_Control_Typography;
use Elementor\Group_Control_Border;
use Elementor\Group_Control_Box_Shadow;
use Elementor\Group_Control_Background;

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

class Unlock_Google_Sheet extends Widget_Base
{

    public function get_name()
    {
        return 'unlockafe-google-sheet';
    }

    public function get_title()
    {
        return esc_html__('Google Sheet', 'unlock-addons-for-elementor');
    }

    public function get_icon()
    {
        return 'eicon-table ulicon';
    }

    public function get_categories()
    {
        return ['unlockafe-addons'];
    }

    public function get_keywords()
    {
        return ['unlock', 'google', 'sheet', 'table', 'data'];
    }
    public function get_script_depends()
    {
        return ['unlockafe-google-sheet'];
    }

    public function get_style_depends()
    {
        return ['unlockafe-google-sheet-style'];
    }

    protected function register_controls()
    {
        $this->start_controls_section(
            'section_content',
            [
                'label' => esc_html__('Google Sheet Settings', 'unlock-addons-for-elementor'),
            ]
        );

        $this->add_control(
            'google_sheet_settings_notice',
            [
                'type' => Controls_Manager::RAW_HTML,
                'raw' => sprintf(
                    __('Note: Your Google API Key is managed globally. You can configure it in the %s settings page.', 'unlock-addons-for-elementor'),
                    '<a href="' . admin_url('admin.php?page=unlock-addons-integrations') . '" target="_blank">' . __('Unlock Addons', 'unlock-addons-for-elementor') . '</a>'
                ),
                'content_classes' => 'elementor-panel-alert elementor-panel-alert-info',
            ]
        );


        $this->add_control(
            'sheet_url',
            [
                'label' => esc_html__('Sheet URL', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::TEXT,
                'label_block' => true,
                'default' => esc_html__('', 'unlock-addons-for-elementor'),
                'placeholder' => esc_html__('Enter your sheet url', 'unlock-addons-for-elementor'),
            ]
        );

        $this->add_control(
            'sheet_range',
            [
                'label' => esc_html__('Data Range', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::TEXT,
                'label_block' => true,
                'default' => esc_html__('', 'unlock-addons-for-elementor'),
                'placeholder' => esc_html__('e.g., Sheet1!A1:D10', 'unlock-addons-for-elementor'),
            ]
        );

        $this->add_control(
            'include_headers',
            [
                'label' => esc_html__('First Row as Headers', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::SWITCHER,
                'label_on' => esc_html__('Yes', 'unlock-addons-for-elementor'),
                'label_off' => esc_html__('No', 'unlock-addons-for-elementor'),
                'return_value' => 'yes',
                'default' => 'yes',
            ]
        );

        $this->add_control(
            'enable_sorting',
            [
                'label' => esc_html__('Enable Table Sorting', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::SWITCHER,
                'label_on' => esc_html__('Yes', 'unlock-addons-for-elementor'),
                'label_off' => esc_html__('No', 'unlock-addons-for-elementor'),
                'return_value' => 'yes',
                'default' => 'yes',
                'description' => esc_html__('Allow users to sort the table by clicking on column headers.', 'unlock-addons-for-elementor'),
            ]
        );

        $this->end_controls_section();

        $this->start_controls_section(
            'section_presets',
            [
                'label' => esc_html__('Presets', 'unlock-addons-for-elementor'),
            ]
        );

        $this->add_control(
            'table_template',
            [
                'label' => esc_html__('Designs', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::VISUAL_CHOICE,
                'default' => 'professional',
                'options' => [
                    'vibrant' => [
                        'title' => esc_html__('Vibrant', 'unlock-addons-for-elementor'),
                        'image' => plugins_url('assets/images/vibrant.svg', \UNLOCKAFE_ADDONS_BASE),
                    ],
                    'professional' => [
                        'title' => esc_html__('Professional', 'unlock-addons-for-elementor'),
                        'image' => plugins_url('assets/images/professional.svg', \UNLOCKAFE_ADDONS_BASE),
                    ],
                    'midnight' => [
                        'title' => esc_html__('Midnight', 'unlock-addons-for-elementor'),
                        'image' => plugins_url('assets/images/midnight.svg', \UNLOCKAFE_ADDONS_BASE),
                    ],
                    'airy' => [
                        'title' => esc_html__('Airy', 'unlock-addons-for-elementor'),
                        'image' => plugins_url('assets/images/airy.svg', \UNLOCKAFE_ADDONS_BASE),
                    ]
                ],
                'prefix_class' => 'unlockafe-gs-template-',
                'columns' => 2,
            ]
        );

        $this->end_controls_section();

        $this->start_controls_section(
            'section_table_features',
            [
                'label' => esc_html__('Table Features', 'unlock-addons-for-elementor'),
            ]
        );

        $this->add_control(
            'enable_search',
            [
                'label' => esc_html__('Enable Search', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::SWITCHER,
                'default' => 'yes',
            ]
        );

        $this->add_control(
            'enable_pagination',
            [
                'label' => esc_html__('Enable Pagination', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::SWITCHER,
                'default' => 'yes',
            ]
        );

        $this->add_control(
            'show_entries',
            [
                'label' => esc_html__('Enable Entries Dropdown', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::SWITCHER,
                'default' => 'yes',
                'condition' => [
                    'enable_pagination' => 'yes',
                ],
            ]
        );

        $this->add_control(
            'entries_per_page',
            [
                'label' => esc_html__('Entries Per Page', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::NUMBER,
                'default' => 10,
                'condition' => [
                    'enable_pagination' => 'yes',
                ],
            ]
        );

        $this->end_controls_section();

        $this->start_controls_section(
            'section_table_style',
            [
                'label' => esc_html__('Table Style', 'unlock-addons-for-elementor'),
                'tab' => Controls_Manager::TAB_STYLE,
            ]
        );

        $this->start_controls_tabs('tabs_table_style');

        // Header Tab
        $this->start_controls_tab(
            'tab_table_header',
            [
                'label' => esc_html__('Header', 'unlock-addons-for-elementor'),
            ]
        );

        $this->add_control(
            'table_header_background',
            [
                'label' => esc_html__('Background Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table th' => 'background-color: {{VALUE}};',
                ],
            ]
        );

        $this->add_control(
            'table_header_text_color',
            [
                'label' => esc_html__('Text Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table th' => 'color: {{VALUE}};',
                ],
            ]
        );

        $this->add_group_control(
            Group_Control_Typography::get_type(),
            [
                'name' => 'header_typography',
                'label' => esc_html__('Typography', 'unlock-addons-for-elementor'),
                'selector' => '{{WRAPPER}} .unlockafe-google-sheet-table th',
            ]
        );

        $this->add_responsive_control(
            'header_padding',
            [
                'label' => esc_html__('Padding', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::DIMENSIONS,
                'size_units' => ['px', 'em', '%'],
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table th' => 'padding: {{TOP}}{{UNIT}} {{RIGHT}}{{UNIT}} {{BOTTOM}}{{UNIT}} {{LEFT}}{{UNIT}};',
                ],
            ]
        );

        $this->add_group_control(
            Group_Control_Border::get_type(),
            [
                'name' => 'header_border',
                'label' => esc_html__('Border', 'unlock-addons-for-elementor'),
                'selector' => '{{WRAPPER}} .unlockafe-google-sheet-table th',
            ]
        );

        $this->end_controls_tab();

        // Body Tab
        $this->start_controls_tab(
            'tab_table_body',
            [
                'label' => esc_html__('Body', 'unlock-addons-for-elementor'),
            ]
        );

        $this->add_control(
            'table_odd_row_background',
            [
                'label' => esc_html__('Odd Row Background', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table tbody tr:nth-child(odd) td' => 'background-color: {{VALUE}};',
                ],
            ]
        );

        $this->add_control(
            'table_odd_row_text_color',
            [
                'label' => esc_html__('Odd Row Text Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table tbody tr:nth-child(odd) td' => 'color: {{VALUE}};',
                ],
            ]
        );

        $this->add_control(
            'table_even_row_background',
            [
                'label' => esc_html__('Even Row Background', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table tbody tr:nth-child(even) td' => 'background-color: {{VALUE}};',
                ],
            ]
        );

        $this->add_control(
            'table_even_row_text_color',
            [
                'label' => esc_html__('Even Row Text Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table tbody tr:nth-child(even) td' => 'color: {{VALUE}};',
                ],
            ]
        );

        $this->add_group_control(
            Group_Control_Typography::get_type(),
            [
                'name' => 'body_typography',
                'label' => esc_html__('Typography', 'unlock-addons-for-elementor'),
                'selector' => '{{WRAPPER}} .unlockafe-google-sheet-table tbody td',
            ]
        );

        $this->add_responsive_control(
            'body_padding',
            [
                'label' => esc_html__('Cell Padding', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::DIMENSIONS,
                'size_units' => ['px', 'em', '%'],
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table tbody td' => 'padding: {{TOP}}{{UNIT}} {{RIGHT}}{{UNIT}} {{BOTTOM}}{{UNIT}} {{LEFT}}{{UNIT}};',
                ],
            ]
        );

        $this->add_group_control(
            Group_Control_Border::get_type(),
            [
                'name' => 'body_border',
                'label' => esc_html__('Cell Border', 'unlock-addons-for-elementor'),
                'selector' => '{{WRAPPER}} .unlockafe-google-sheet-table td',
            ]
        );

        $this->add_responsive_control(
            'table_border_radius',
            [
                'label' => esc_html__('Border Radius', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::DIMENSIONS,
                'size_units' => ['px', '%'],
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table' => 'border-radius: {{TOP}}{{UNIT}} {{RIGHT}}{{UNIT}} {{BOTTOM}}{{UNIT}} {{LEFT}}{{UNIT}}; overflow: hidden;',
                ],
            ]
        );

        $this->end_controls_tab();

        $this->end_controls_tabs();

        $this->end_controls_section();

        $this->start_controls_section(
            'section_search_box_style',
            [
                'label' => esc_html__('Search Box Style', 'unlock-addons-for-elementor'),
                'tab' => \Elementor\Controls_Manager::TAB_STYLE,
                'condition' => [
                    'enable_search' => 'yes',
                ],
            ]
        );

        $this->add_control(
            'search_box_background',
            [
                'label' => esc_html__('Background Color', 'unlock-addons-for-elementor'),
                'type' => \Elementor\Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-search-input' => 'background-color: {{VALUE}};',
                ],
            ]
        );

        $this->add_control(
            'search_box_text_color',
            [
                'label' => esc_html__('Text Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-search-input' => 'color: {{VALUE}};',
                ],
            ]
        );

        $this->add_control(
            'search_box_placeholder_color',
            [
                'label' => esc_html__('Placeholder Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-search-input::placeholder' => 'color: {{VALUE}};',
                    '{{WRAPPER}} .unlockafe-search-input::-ms-input-placeholder' => 'color: {{VALUE}};',
                    '{{WRAPPER}} .unlockafe-search-input::-moz-placeholder' => 'color: {{VALUE}};',
                ],
            ]
        );

        $this->add_group_control(
            Group_Control_Typography::get_type(),
            [
                'name' => 'search_box_typography',
                'label' => esc_html__('Typography', 'unlock-addons-for-elementor'),
                'selector' => '{{WRAPPER}} .unlockafe-search-input',
            ]
        );

        $this->add_group_control(
            Group_Control_Border::get_type(),
            [
                'name' => 'search_box_border',
                'label' => esc_html__('Border', 'unlock-addons-for-elementor'),
                'selector' => '{{WRAPPER}} .unlockafe-search-input',
            ]
        );

        $this->add_responsive_control(
            'search_box_border_radius',
            [
                'label' => esc_html__('Border Radius', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::DIMENSIONS,
                'size_units' => ['px', '%'],
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-search-input' => 'border-radius: {{TOP}}{{UNIT}} {{RIGHT}}{{UNIT}} {{BOTTOM}}{{UNIT}} {{LEFT}}{{UNIT}};',
                ],
            ]
        );

        $this->add_responsive_control(
            'search_box_margin',
            [
                'label' => esc_html__('Margin', 'unlock-addons-for-elementor'),
                'type' => \Elementor\Controls_Manager::DIMENSIONS,
                'size_units' => ['px', '%', 'em'],
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-search-container' => 'margin: {{TOP}}{{UNIT}} {{RIGHT}}{{UNIT}} {{BOTTOM}}{{UNIT}} {{LEFT}}{{UNIT}};',
                ],
            ]
        );

        $this->add_responsive_control(
            'search_box_padding',
            [
                'label' => esc_html__('Padding', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::DIMENSIONS,
                'size_units' => ['px', 'em', '%'],
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-search-input' => 'padding: {{TOP}}{{UNIT}} {{RIGHT}}{{UNIT}} {{BOTTOM}}{{UNIT}} {{LEFT}}{{UNIT}};',
                ],
            ]
        );

        $this->end_controls_section();

        $this->start_controls_section(
            'section_entries_dropdown_style',
            [
                'label' => esc_html__('Entries Dropdown Style', 'unlock-addons-for-elementor'),
                'tab' => \Elementor\Controls_Manager::TAB_STYLE,
                'condition' => [
                    'show_entries' => 'yes',
                ],
            ]
        );

        $this->add_responsive_control(
            'entries_dropdown_margin',
            [
                'label' => esc_html__('Margin', 'unlock-addons-for-elementor'),
                'type' => \Elementor\Controls_Manager::DIMENSIONS,
                'size_units' => ['px', '%', 'em'],
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-entries-per-page-container' => 'margin: {{TOP}}{{UNIT}} {{RIGHT}}{{UNIT}} {{BOTTOM}}{{UNIT}} {{LEFT}}{{UNIT}};',
                ],
            ]
        );

        $this->end_controls_section();

        // Table Alignment Section
        $this->start_controls_section(
            'section_table_alignment',
            [
                'label' => esc_html__('Table Alignment', 'unlock-addons-for-elementor'),
                'tab' => Controls_Manager::TAB_STYLE,
            ]
        );

        $this->add_control(
            'header_alignment',
            [
                'label' => esc_html__('Header Alignment', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::CHOOSE,
                'options' => [
                    'left' => [
                        'title' => esc_html__('Left', 'unlock-addons-for-elementor'),
                        'icon' => 'eicon-text-align-left',
                    ],
                    'center' => [
                        'title' => esc_html__('Center', 'unlock-addons-for-elementor'),
                        'icon' => 'eicon-text-align-center',
                    ],
                    'right' => [
                        'title' => esc_html__('Right', 'unlock-addons-for-elementor'),
                        'icon' => 'eicon-text-align-right',
                    ],
                ],
                'default' => 'left',
                'toggle' => true,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table thead th' => 'text-align: {{VALUE}};',
                ],
            ]
        );

        $this->add_control(
            'content_alignment',
            [
                'label' => esc_html__('Content Alignment', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::CHOOSE,
                'options' => [
                    'left' => [
                        'title' => esc_html__('Left', 'unlock-addons-for-elementor'),
                        'icon' => 'eicon-text-align-left',
                    ],
                    'center' => [
                        'title' => esc_html__('Center', 'unlock-addons-for-elementor'),
                        'icon' => 'eicon-text-align-center',
                    ],
                    'right' => [
                        'title' => esc_html__('Right', 'unlock-addons-for-elementor'),
                        'icon' => 'eicon-text-align-right',
                    ],
                ],
                'default' => 'left',
                'toggle' => true,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-google-sheet-table tbody td' => 'text-align: {{VALUE}};',
                ],
            ]
        );

        $this->end_controls_section();

        $this->start_controls_section(
            'section_pagination_style',
            [
                'label' => esc_html__('Pagination', 'unlock-addons-for-elementor'),
                'tab' => Controls_Manager::TAB_STYLE,
            ]
        );

        $this->start_controls_tabs('tabs_pagination_style');

        $this->start_controls_tab(
            'tab_pagination_normal',
            [
                'label' => esc_html__('Normal', 'unlock-addons-for-elementor'),
            ]
        );

        $this->add_control(
            'pagination_color',
            [
                'label' => esc_html__('Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-table-pagination .paginate_button' => 'color: {{VALUE}}',
                ],
            ]
        );

        $this->add_control(
            'pagination_background_color',
            [
                'label' => esc_html__('Background Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-table-pagination .paginate_button' => 'background-color: {{VALUE}}',
                ],
            ]
        );

        $this->add_responsive_control(
            'pagination_width',
            [
                'label' => esc_html__('Width', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::SLIDER,
                'size_units' => ['px'],
                'range' => [
                    'px' => [
                        'min' => 20,
                        'max' => 100,
                    ],
                ],
                'default' => [
                    'unit' => 'px',
                    'size' => 40,
                ],
                'selectors' => [],
            ]
        );

        $this->add_responsive_control(
            'pagination_height',
            [
                'label' => esc_html__('Height', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::SLIDER,
                'size_units' => ['px'],
                'range' => [
                    'px' => [
                        'min' => 20,
                        'max' => 100,
                    ],
                ],
                'default' => [
                    'unit' => 'px',
                    'size' => 40,
                ],
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-table-pagination .paginate_button' => 'width: {{pagination_width.SIZE}}{{pagination_width.UNIT}}; height: {{SIZE}}{{UNIT}}; line-height: {{SIZE}}{{UNIT}}; display: inline-flex; align-items: center; justify-content: center;'
                ],
            ]
        );

        $this->end_controls_tab();

        $this->start_controls_tab(
            'tab_pagination_hover',
            [
                'label' => esc_html__('Hover', 'unlock-addons-for-elementor'),
            ]
        );

        $this->add_control(
            'pagination_hover_color',
            [
                'label' => esc_html__('Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-table-pagination .paginate_button:hover' => 'color: {{VALUE}}',
                ],
            ]
        );

        $this->add_control(
            'pagination_hover_background_color',
            [
                'label' => esc_html__('Background Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-table-pagination .paginate_button:hover' => 'background-color: {{VALUE}}',
                ],
            ]
        );

        $this->end_controls_tab();

        $this->start_controls_tab(
            'tab_pagination_active',
            [
                'label' => esc_html__('Active', 'unlock-addons-for-elementor'),
            ]
        );

        $this->add_control(
            'pagination_active_color',
            [
                'label' => esc_html__('Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-table-pagination .paginate_button.current' => 'color: {{VALUE}}',
                ],
            ]
        );

        $this->add_control(
            'pagination_active_background_color',
            [
                'label' => esc_html__('Background Color', 'unlock-addons-for-elementor'),
                'type' => Controls_Manager::COLOR,
                'selectors' => [
                    '{{WRAPPER}} .unlockafe-table-pagination .paginate_button.current' => 'background-color: {{VALUE}}',
                ],
            ]
        );

        $this->end_controls_tab();

        $this->end_controls_tabs();

        $this->end_controls_section();
    }

    protected function render()
    {
        $settings = $this->get_settings_for_display();
        $widget_id = 'unlockafe-google-sheet-' . $this->get_id();

        $sheet_url = $settings['sheet_url'] ?? '';
        $sheet_range = $settings['sheet_range'] ?? '';

        $include_headers = ($settings['include_headers'] ?? 'yes') === 'yes';
        $enable_sorting = ($settings['enable_sorting'] ?? 'yes') === 'yes';
        $enable_pagination = ($settings['enable_pagination'] ?? 'yes') === 'yes';
        $enable_search = ($settings['enable_search'] ?? 'yes') === 'yes';
        $show_entries = ($settings['show_entries'] ?? 'yes') === 'yes';
        $entries_per_page = $settings['entries_per_page'] ?? 10;
        $show_troubleshooting = ($settings['show_troubleshooting'] ?? 'no') === 'yes';

        $api_key_options = get_option('unlockafe_google_sheets_settings');
        $api_key = !empty($api_key_options['api_key']) ? $api_key_options['api_key'] : '';
        $error_message = '';
        $tbody = '';
        $thead = '';
        $sheet_id = '';

        if (!empty($sheet_url)) {
            preg_match('/spreadsheets\/d\/([a-zA-Z0-9-_]+)(?:\/.*)?/', $sheet_url, $matches);
            $sheet_id = $matches[1] ?? '';
        }

        if (empty($api_key)) {
            $error_message = __('Google API Key is not set. Please configure it in the Unlock Addons settings.', 'unlock-addons-for-elementor');
        } elseif (empty($sheet_url)) {
            $error_message = __('Please Enter Your Google Sheet url in the widget settings.', 'unlock-addons-for-elementor');
        } elseif (empty($sheet_id)) {
            $error_message = __('Invalid Google Sheet URL. Could not find the Sheet ID.', 'unlock-addons-for-elementor');
        } else {
            $transient_key = 'unlockafe_sheet_' . md5($sheet_id . $sheet_range);

            if (\Elementor\Plugin::$instance->editor->is_edit_mode()) {
                delete_transient($transient_key);
            }

            $values = get_transient($transient_key);

            if ($values === false) {
                $range_param = !empty($sheet_range) ? rawurlencode($sheet_range) : 'A1:Z1000';
                $api_url = sprintf(
                    'https://sheets.googleapis.com/v4/spreadsheets/%s/values/%s?key=%s',
                    $sheet_id,
                    $range_param,
                    $api_key
                );

                $response = wp_remote_get($api_url, ['timeout' => 15]);

                if (is_wp_error($response)) {
                    $error_message = $response->get_error_message();
                    $values = []; // Set values to empty on error
                } else {
                    $response_code = wp_remote_retrieve_response_code($response);
                    $body = wp_remote_retrieve_body($response);
                    $data = json_decode($body, true);

                    if ($response_code === 200 && !empty($data['values'])) {
                        $values = $data['values'];
                        set_transient($transient_key, $values, HOUR_IN_SECONDS); // Cache for 1 hour
                    } else {
                        $error_message = $data['error']['message'] ?? __('Unknown error fetching data from Google Sheets.', 'unlock-addons-for-elementor');
                        $values = []; // Ensure values is an empty array on error
                    }
                }
            }
        }

        if (empty($error_message) && !empty($values)) {
            $headers = [];
            if ($include_headers && !empty($values)) {
                $headers = array_shift($values);
                $thead .= '<thead><tr>';
                foreach ($headers as $header) {
                    $thead .= '<th>' . esc_html($header) . '</th>';
                }
                $thead .= '</tr></thead>';
            }

            $tbody .= '<tbody>';
            foreach ($values as $row) {
                $tbody .= '<tr>';
                $col_count = $include_headers && !empty($headers) ? count($headers) : count($row);
                for ($i = 0; $i < $col_count; $i++) {
                    $cell = $row[$i] ?? '';
                    // Check if the cell content is a valid URL
                    if (filter_var($cell, FILTER_VALIDATE_URL)) {
                        $tbody .= '<td><a href="' . esc_url($cell) . '" target="_blank" rel="noopener noreferrer">' . esc_html($cell) . '</a></td>';
                    } else {
                        $tbody .= '<td>' . esc_html($cell) . '</td>';
                    }
                }
                $tbody .= '</tr>';
            }
            $tbody .= '</tbody>';
        } elseif (empty($error_message)) {
            $error_message = __('No data found in the specified range or the sheet is empty.', 'unlock-addons-for-elementor');
        }

        ?>
        <style>
            #<?php echo esc_attr($widget_id); ?> .unlockafe-google-sheet-table td a {
                color: blue !important;
            }
        </style>
        <div class="unlockafe-google-sheet-container"
                id="<?php echo esc_attr($widget_id); ?>" data-sorting="<?php echo $enable_sorting ? 'true' : 'false'; ?>"
                data-pagination="<?php echo $enable_pagination ? 'true' : 'false'; ?>"
                data-search="<?php echo $enable_search ? 'true' : 'false'; ?>"
                data-show-entries="<?php echo $show_entries ? 'true' : 'false'; ?>"
                data-entries-per-page="<?php echo esc_attr($entries_per_page); ?>">
            <?php if (!empty($error_message)): ?>
                <div class="unlockafe-google-sheet-error">
                    <p><strong><?php echo esc_html__('Warning:', 'unlock-addons-for-elementor'); ?></strong>
                        <?php echo esc_html($error_message); ?></p>
                    <?php if ($show_troubleshooting): ?>
                        <p><strong><?php echo esc_html__('Troubleshooting steps:', 'unlock-addons-for-elementor'); ?></strong></p>
                        <ol>
                            <li><?php echo esc_html__('Verify your Google API key is correct and has Google Sheets API enabled.', 'unlock-addons-for-elementor'); ?>
                            </li>
                            <li><?php echo esc_html__('Make sure your Google Sheet is shared with "Anyone with the link" or "Public on the web".', 'unlock-addons-for-elementor'); ?>
                            </li>
                            <li><?php echo esc_html__('Check that your Sheet ID is correct (the long string from the sheet URL).', 'unlock-addons-for-elementor'); ?>
                            </li>
                            <li><?php echo esc_html__('Verify the sheet range format (e.g., Sheet1!A1:D10).', 'unlock-addons-for-elementor'); ?>
                            </li>
                        </ol>
                    <?php endif; ?>
                </div>
            <?php else: ?>
                <div class="unlockafe-table-controls">
                    <?php if ($show_entries): ?>
                        <div class="unlockafe-entries-container">
                            <label>
                                <?php _e('Show', 'unlock-addons-for-elementor'); ?>
                                <select class="unlockafe-entries-select">
                                    <option value="10" <?php selected($entries_per_page, 10); ?>>10</option>
                                    <option value="25" <?php selected($entries_per_page, 25); ?>>25</option>
                                    <option value="50" <?php selected($entries_per_page, 50); ?>>50</option>
                                    <option value="100" <?php selected($entries_per_page, 100); ?>>100</option>
                                </select>
                                <?php _e('entries', 'unlock-addons-for-elementor'); ?>
                            </label>
                        </div>
                    <?php endif; ?>

                    <?php if ($enable_search): ?>
                        <div class="unlockafe-search-container">
                            <label>
                                <?php _e('Search:', 'unlock-addons-for-elementor'); ?>
                                <input type="search" class="unlockafe-search-input" placeholder="<?php _e('Search...', 'unlock-addons-for-elementor'); ?>">
                            </label>
                        </div>
                    <?php endif; ?>
                </div>

                <div class="unlockafe-google-sheet-table-container">
                    <table class="unlockafe-google-sheet-table">
                        <?php echo $thead; ?>
                        <?php echo $tbody; ?>
                    </table>

                    <?php if ($enable_pagination): ?>
                        <div class="unlockafe-table-bottom-controls">
                            <div class="unlockafe-table-info"></div>
                            <div class="unlockafe-table-pagination"></div>
                        </div>
                    <?php endif; ?>
                </div>
            <?php endif; ?>
        </div>
        <?php
    }


}