<?php

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

class AI_eShop_Chat_Business_Info_Manager {

    
    private static $instance = null;

    
    private $table_name;

    
    const TYPE_STORE_LOCATION = 'store_location';
    const TYPE_OPENING_HOURS = 'opening_hours';
    const TYPE_SHIPPING = 'shipping';
    const TYPE_CONTACT = 'contact';
    const TYPE_RETURNS = 'returns';
    const TYPE_FAQ = 'faq';
    const TYPE_CUSTOM = 'custom';

    
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    
    private function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'aieo_chat_business_info';

        
        add_action('wp_ajax_aieo_get_business_info_entries', array($this, 'ajax_get_entries'));
        add_action('wp_ajax_aieo_save_business_info', array($this, 'ajax_save_entry'));
        add_action('wp_ajax_aieo_delete_business_info', array($this, 'ajax_delete_entry'));
        add_action('wp_ajax_aieo_sync_wc_table_rates', array($this, 'ajax_sync_wc_table_rates'));
        add_action('wp_ajax_aieo_import_page_as_business_info', array($this, 'ajax_import_page'));
    }

    
    public function create_table() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            info_type varchar(50) NOT NULL,
            title varchar(255) NOT NULL,
            content TEXT NOT NULL,
            priority int(11) DEFAULT 0 COMMENT 'Higher priority = shown first',
            is_active tinyint(1) DEFAULT 1,
            metadata TEXT DEFAULT NULL COMMENT 'JSON for extra data like coordinates, postal codes',
            source varchar(50) DEFAULT 'manual' COMMENT 'manual, wc_table_rates, wc_zones, etc.',
            source_id varchar(100) DEFAULT NULL COMMENT 'ID from source if synced',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY info_type (info_type),
            KEY is_active (is_active),
            KEY priority (priority),
            KEY source (source, source_id)
        ) $charset_collate;";

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

    
    public function get_info_types() {
        return array(
            self::TYPE_STORE_LOCATION => __('Store Locations', 'ai-eshop-optimizer'),
            self::TYPE_OPENING_HOURS => __('Opening Hours', 'ai-eshop-optimizer'),
            self::TYPE_SHIPPING => __('Shipping Information', 'ai-eshop-optimizer'),
            self::TYPE_CONTACT => __('Contact Information', 'ai-eshop-optimizer'),
            self::TYPE_RETURNS => __('Returns & Refunds', 'ai-eshop-optimizer'),
            self::TYPE_FAQ => __('Frequently Asked Questions', 'ai-eshop-optimizer'),
            self::TYPE_CUSTOM => __('Custom Information', 'ai-eshop-optimizer'),
        );
    }

    
    public function save_entry($data) {
        global $wpdb;

        $defaults = array(
            'info_type' => self::TYPE_CUSTOM,
            'title' => '',
            'content' => '',
            'priority' => 0,
            'is_active' => 1,
            'metadata' => null,
            'source' => 'manual',
            'source_id' => null,
        );

        $data = wp_parse_args($data, $defaults);

        
        if (is_array($data['metadata'])) {
            $data['metadata'] = json_encode($data['metadata']);
        }

        
        if (!empty($data['id'])) {
            $id = $data['id'];
            unset($data['id']);

            $wpdb->update(
                $this->table_name,
                $data,
                array('id' => $id),
                array('%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s'),
                array('%d')
            );

            return $id;
        }

        
        $wpdb->insert(
            $this->table_name,
            $data,
            array('%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s')
        );

        return $wpdb->insert_id;
    }

    
    public function get_entry($id) {
        global $wpdb;

        $entry = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$this->table_name} WHERE id = %d",
            $id
        ));

        if ($entry && $entry->metadata) {
            $entry->metadata = json_decode($entry->metadata, true);
        }

        return $entry;
    }

    
    public function get_entries($args = array()) {
        global $wpdb;

        $defaults = array(
            'info_type' => null,
            'is_active' => null,
            'source' => null,
            'orderby' => 'priority',
            'order' => 'DESC',
            'limit' => 100,
        );

        $args = wp_parse_args($args, $defaults);

        $where = array('1=1');
        $values = array();

        if ($args['info_type'] !== null) {
            $where[] = 'info_type = %s';
            $values[] = $args['info_type'];
        }

        if ($args['is_active'] !== null) {
            $where[] = 'is_active = %d';
            $values[] = $args['is_active'];
        }

        if ($args['source'] !== null) {
            $where[] = 'source = %s';
            $values[] = $args['source'];
        }

        $where_clause = implode(' AND ', $where);
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']);
        $limit = absint($args['limit']);

        $sql = "SELECT * FROM {$this->table_name} WHERE {$where_clause} ORDER BY {$orderby} LIMIT {$limit}";

        if (!empty($values)) {
            
            $sql = $wpdb->prepare($sql, $values);
        }

        
        $entries = $wpdb->get_results($sql);

        foreach ($entries as &$entry) {
            if ($entry->metadata) {
                $entry->metadata = json_decode($entry->metadata, true);
            }
        }

        return $entries;
    }

    
    public function delete_entry($id) {
        global $wpdb;

        return $wpdb->delete(
            $this->table_name,
            array('id' => $id),
            array('%d')
        ) !== false;
    }

    
    public function delete_by_source($source) {
        global $wpdb;

        return $wpdb->delete(
            $this->table_name,
            array('source' => $source),
            array('%s')
        );
    }

    
    public function get_context_for_chat($types = null) {
        $entries = $this->get_entries(array(
            'is_active' => 1,
            'orderby' => 'info_type, priority',
            'order' => 'ASC',
            'limit' => 500,
        ));

        if (empty($entries)) {
            return '';
        }

        
        if ($types !== null && is_array($types)) {
            $entries = array_filter($entries, function($entry) use ($types) {
                return in_array($entry->info_type, $types);
            });
        }

        
        $grouped = array();
        foreach ($entries as $entry) {
            if (!isset($grouped[$entry->info_type])) {
                $grouped[$entry->info_type] = array();
            }
            $grouped[$entry->info_type][] = $entry;
        }

        
        $output = array();
        $type_labels = $this->get_info_types();

        foreach ($grouped as $type => $type_entries) {
            $type_label = isset($type_labels[$type]) ? $type_labels[$type] : ucfirst($type);
            $section = "## {$type_label}\n\n";

            foreach ($type_entries as $entry) {
                $section .= "### {$entry->title}\n";
                $section .= "{$entry->content}\n\n";

                
                if (!empty($entry->metadata)) {
                    if ($type === self::TYPE_STORE_LOCATION) {
                        if (!empty($entry->metadata['address'])) {
                            $section .= "Address: {$entry->metadata['address']}\n";
                        }
                        if (!empty($entry->metadata['phone'])) {
                            $section .= "Phone: {$entry->metadata['phone']}\n";
                        }
                        if (!empty($entry->metadata['email'])) {
                            $section .= "Email: {$entry->metadata['email']}\n";
                        }
                    }
                    $section .= "\n";
                }
            }

            $output[] = $section;
        }

        return implode("\n", $output);
    }

    
    private function format_price_plain($price) {
        $currency_symbol = get_woocommerce_currency_symbol();
        $decimals = wc_get_price_decimals();
        $decimal_sep = wc_get_price_decimal_separator();
        $thousand_sep = wc_get_price_thousand_separator();

        $formatted = number_format((float) $price, $decimals, $decimal_sep, $thousand_sep);

        
        $position = get_option('woocommerce_currency_pos', 'left');
        switch ($position) {
            case 'left':
                return $currency_symbol . $formatted;
            case 'right':
                return $formatted . $currency_symbol;
            case 'left_space':
                return $currency_symbol . ' ' . $formatted;
            case 'right_space':
                return $formatted . ' ' . $currency_symbol;
            default:
                return $currency_symbol . $formatted;
        }
    }

    
    public function sync_wc_table_rates() {
        global $wpdb;

        
        if (!class_exists('WC_Shipping_Zones')) {
            return array(
                'success' => false,
                'message' => __('WooCommerce is not active', 'ai-eshop-optimizer'),
            );
        }

        
        $shipping_zones = WC_Shipping_Zones::get_zones();

        
        $zone_0 = WC_Shipping_Zones::get_zone(0);
        $shipping_zones['0'] = array(
            'zone_id' => 0,
            'zone_name' => __('Rest of the World', 'ai-eshop-optimizer'),
            'shipping_methods' => $zone_0->get_shipping_methods(),
        );

        
        $this->delete_by_source('wc_shipping_zones');
        $this->delete_by_source('wc_table_rates'); 

        $synced = 0;
        $table_rates_table = $wpdb->prefix . 'woocommerce_shipping_table_rates';
        $has_table_rate_plugin = class_exists('WC_Table_Rate_Shipping');

        foreach ($shipping_zones as $zone) {
            $zone_id = $zone['zone_id'];
            $zone_name = $zone['zone_name'];

            
            $zone_obj = WC_Shipping_Zones::get_zone($zone_id);
            $zone_locations = $zone_obj->get_zone_locations();

            $countries = array();
            $states = array();
            $postcodes = array();

            foreach ($zone_locations as $location) {
                if ($location->type === 'postcode') {
                    $postcodes[] = $location->code;
                } elseif ($location->type === 'state') {
                    $parts = explode(':', $location->code);
                    if (count($parts) === 2) {
                        $state_list = WC()->countries->get_states($parts[0]);
                        $states[] = isset($state_list[$parts[1]]) ? $state_list[$parts[1]] : $location->code;
                    } else {
                        $states[] = $location->code;
                    }
                } elseif ($location->type === 'country') {
                    $countries[] = isset(WC()->countries->countries[$location->code])
                        ? WC()->countries->countries[$location->code]
                        : $location->code;
                }
            }

            
            if ($zone_id != 0 && empty($countries) && empty($states) && empty($postcodes)) {
                continue;
            }

            
            $shipping_options = array();
            $lowest_price = null;
            $has_free_shipping = false;
            $free_shipping_conditions = '';

            foreach ($zone['shipping_methods'] as $method) {
                
                if (!$method->is_enabled()) {
                    continue;
                }

                $method_info = array(
                    'name' => $method->title,
                    'type' => $method->id,
                    'min_cost' => null,
                );

                
                if ($method->id === 'table_rate' && $has_table_rate_plugin) {
                    $instance_id = $method->instance_id;

                    $rates = $wpdb->get_results($wpdb->prepare(
                        "SELECT rate_cost FROM {$table_rates_table}
                        WHERE shipping_method_id = %d
                        ORDER BY rate_cost ASC
                        LIMIT 1",
                        $instance_id
                    ), ARRAY_A);

                    if (!empty($rates)) {
                        $method_info['min_cost'] = floatval($rates[0]['rate_cost']);
                    }
                }

                
                if ($method->id === 'flat_rate') {
                    $cost = $method->get_option('cost', 0);
                    if (!empty($cost)) {
                        
                        if (is_numeric($cost)) {
                            $method_info['min_cost'] = floatval($cost);
                        } else {
                            
                            $method_info['min_cost'] = 'varies';
                        }
                    }
                }

                
                if ($method->id === 'free_shipping') {
                    $has_free_shipping = true;
                    $method_info['min_cost'] = 0;

                    $min_amount = floatval($method->get_option('min_amount', 0));
                    $requires = $method->get_option('requires', '');

                    $conditions = array();
                    if ($min_amount > 0) {
                        $conditions[] = sprintf(
                            
                            __('orders over %s', 'ai-eshop-optimizer'),
                            $this->format_price_plain($min_amount)
                        );
                    }
                    if ($requires === 'coupon') {
                        $conditions[] = __('with free shipping coupon', 'ai-eshop-optimizer');
                    } elseif ($requires === 'both' && $min_amount > 0) {
                        $conditions[] = __('and free shipping coupon', 'ai-eshop-optimizer');
                    }

                    if (!empty($conditions)) {
                        $free_shipping_conditions = implode(' ', $conditions);
                    }
                }

                
                if ($method->id === 'local_pickup' || $method->id === 'pickup_location') {
                    $cost = $method->get_option('cost', 0);
                    $method_info['min_cost'] = is_numeric($cost) ? floatval($cost) : 0;
                }

                
                if ($method_info['min_cost'] !== null && $method_info['min_cost'] !== 'varies') {
                    if ($method->id !== 'free_shipping' || empty($free_shipping_conditions)) {
                        if ($lowest_price === null || floatval($method_info['min_cost']) < $lowest_price) {
                            $lowest_price = floatval($method_info['min_cost']);
                        }
                    }
                }

                $shipping_options[] = $method_info;
            }

            
            if (empty($shipping_options)) {
                continue;
            }

            
            $content_parts = array();

            
            
            $content_parts[] = sprintf(__('SHIPPING ZONE: %s', 'ai-eshop-optimizer'), $zone_name);
            $content_parts[] = '';

            
            if (!empty($countries)) {
                $content_parts[] = __('Countries covered', 'ai-eshop-optimizer') . ': ' . implode(', ', $countries);
            }
            if (!empty($states)) {
                $content_parts[] = __('Regions/States covered', 'ai-eshop-optimizer') . ': ' . implode(', ', $states);
            }
            if (!empty($postcodes)) {
                
                $content_parts[] = __('Postal codes covered', 'ai-eshop-optimizer') . ': ' . implode(', ', $postcodes);
            }
            if ($zone_id == 0) {
                $content_parts[] = __('This zone covers all locations not included in other shipping zones.', 'ai-eshop-optimizer');
            }

            $content_parts[] = '';

            
            $content_parts[] = __('AVAILABLE SHIPPING OPTIONS:', 'ai-eshop-optimizer');

            foreach ($shipping_options as $option) {
                if ($option['min_cost'] === null) {
                    $content_parts[] = sprintf('• %s', $option['name']);
                } elseif ($option['min_cost'] === 'varies') {
                    $content_parts[] = sprintf('• %s: %s', $option['name'], __('price varies based on order', 'ai-eshop-optimizer'));
                } elseif ($option['min_cost'] == 0 && $option['type'] === 'free_shipping') {
                    if (!empty($free_shipping_conditions)) {
                        $content_parts[] = sprintf('• %s: %s %s', $option['name'], __('FREE', 'ai-eshop-optimizer'), $free_shipping_conditions);
                    } else {
                        $content_parts[] = sprintf('• %s: %s', $option['name'], __('FREE', 'ai-eshop-optimizer'));
                    }
                } elseif ($option['min_cost'] == 0) {
                    $content_parts[] = sprintf('• %s: %s', $option['name'], __('FREE', 'ai-eshop-optimizer'));
                } else {
                    $content_parts[] = sprintf(
                        '• %s: %s %s',
                        $option['name'],
                        __('from', 'ai-eshop-optimizer'),
                        $this->format_price_plain((float) $option['min_cost'])
                    );
                }
            }

            $content_parts[] = '';

            
            if ($lowest_price !== null) {
                if ($lowest_price == 0) {
                    $content_parts[] = __('Shipping can be FREE for this zone.', 'ai-eshop-optimizer');
                } else {
                    $content_parts[] = sprintf(
                        
                        __('Shipping for this zone starts at %s.', 'ai-eshop-optimizer'),
                        $this->format_price_plain($lowest_price)
                    );
                }
            }
            $content_parts[] = __('Exact shipping cost depends on order contents and delivery address. Please add items to cart and proceed to checkout to see exact shipping cost for your order.', 'ai-eshop-optimizer');

            
            $metadata = array(
                'zone_id' => $zone_id,
                'zone_name' => $zone_name,
                'countries' => $countries,
                'states' => $states,
                'postcodes' => $postcodes,
                'shipping_options' => $shipping_options,
                'lowest_price' => $lowest_price,
                'has_free_shipping' => $has_free_shipping,
            );

            
            $this->save_entry(array(
                'info_type' => self::TYPE_SHIPPING,
                
                'title' => sprintf(__('Shipping to %s', 'ai-eshop-optimizer'), $zone_name),
                'content' => implode("\n", $content_parts),
                'priority' => ($zone_id == 0) ? 5 : 10, 
                'source' => 'wc_shipping_zones',
                'source_id' => 'zone_' . $zone_id,
                'metadata' => $metadata,
            ));

            $synced++;
        }

        return array(
            'success' => true,
            'synced' => $synced,
            'message' => sprintf(
                
                __('Successfully synced %d shipping zones', 'ai-eshop-optimizer'),
                $synced
            ),
        );
    }

    
    public function get_counts_by_type() {
        global $wpdb;

        $results = $wpdb->get_results(
            "SELECT info_type, COUNT(*) as count
            FROM {$this->table_name}
            GROUP BY info_type",
            OBJECT_K
        );

        $counts = array();
        $types = $this->get_info_types();

        foreach ($types as $type => $label) {
            $counts[$type] = isset($results[$type]) ? (int) $results[$type]->count : 0;
        }

        return $counts;
    }

    
    public function table_exists() {
        global $wpdb;
        return $wpdb->get_var("SHOW TABLES LIKE '{$this->table_name}'") === $this->table_name;
    }

    
    
    

    
    public function ajax_get_entries() {
        check_ajax_referer('aieo_business_info_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer')));
        }

        $entries = $this->get_entries();

        
        foreach ($entries as &$entry) {
            $entry->priority = $this->priority_to_label($entry->priority);
        }

        wp_send_json_success(array('entries' => $entries));
    }

    
    public function ajax_save_entry() {
        check_ajax_referer('aieo_business_info_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer')));
        }

        $title = isset($_POST['title']) ? sanitize_text_field(wp_unslash( $_POST['title'] )) : '';
        $content = isset($_POST['content']) ? sanitize_textarea_field(wp_unslash( $_POST['content'] )) : '';
        $info_type = isset($_POST['info_type']) ? sanitize_text_field(wp_unslash( $_POST['info_type'] )) : 'faq';
        $priority_level = isset($_POST['priority']) ? sanitize_text_field(wp_unslash( $_POST['priority'] )) : 'normal';

        if (empty($title) || empty($content)) {
            wp_send_json_error(array('message' => __('Title and content are required', 'ai-eshop-optimizer')));
        }

        
        $priority_map = array(
            'normal' => 0,
            'high' => 10,
            'critical' => 20,
        );
        $priority = isset($priority_map[$priority_level]) ? $priority_map[$priority_level] : 0;

        $entry_id = $this->save_entry(array(
            'title' => $title,
            'content' => $content,
            'info_type' => $info_type,
            'priority' => $priority,
        ));

        if ($entry_id) {
            wp_send_json_success(array(
                'message' => __('Entry saved successfully', 'ai-eshop-optimizer'),
                'entry_id' => $entry_id,
            ));
        } else {
            wp_send_json_error(array('message' => __('Failed to save entry', 'ai-eshop-optimizer')));
        }
    }

    
    public function ajax_delete_entry() {
        check_ajax_referer('aieo_business_info_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer')));
        }

        $entry_id = isset($_POST['entry_id']) ? absint($_POST['entry_id']) : 0;

        if (!$entry_id) {
            wp_send_json_error(array('message' => __('Invalid entry ID', 'ai-eshop-optimizer')));
        }

        if ($this->delete_entry($entry_id)) {
            wp_send_json_success(array('message' => __('Entry deleted', 'ai-eshop-optimizer')));
        } else {
            wp_send_json_error(array('message' => __('Failed to delete entry', 'ai-eshop-optimizer')));
        }
    }

    
    public function ajax_sync_wc_table_rates() {
        check_ajax_referer('aieo_business_info_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer')));
        }

        $result = $this->sync_wc_table_rates();

        if ($result['success']) {
            wp_send_json_success($result);
        } else {
            wp_send_json_error($result);
        }
    }

    
    public function ajax_import_page() {
        check_ajax_referer('aieo_business_info_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer')));
        }

        $page_id = isset($_POST['page_id']) ? absint($_POST['page_id']) : 0;
        $info_type = isset($_POST['info_type']) ? sanitize_text_field(wp_unslash( $_POST['info_type'] )) : 'faq';

        if (!$page_id) {
            wp_send_json_error(array('message' => __('Please select a page', 'ai-eshop-optimizer')));
        }

        $page = get_post($page_id);
        if (!$page || $page->post_type !== 'page') {
            wp_send_json_error(array('message' => __('Page not found', 'ai-eshop-optimizer')));
        }

        
        $content = $page->post_content;
        $content = strip_shortcodes($content);
        $content = wp_strip_all_tags($content);
        $content = preg_replace('/\s+/', ' ', $content); 
        $content = trim($content);

        if (empty($content)) {
            wp_send_json_error(array('message' => __('Page has no content to import', 'ai-eshop-optimizer')));
        }

        $entry_id = $this->save_entry(array(
            'title' => $page->post_title,
            'content' => $content,
            'info_type' => $info_type,
            'priority' => 5,
            'source' => 'page_import',
            'source_id' => $page_id,
        ));

        if ($entry_id) {
            wp_send_json_success(array(
                'message' => sprintf(
                    
                    __('Imported "%1$s" as %2$s information', 'ai-eshop-optimizer'),
                    $page->post_title,
                    $this->get_info_types()[$info_type] ?? $info_type
                ),
                'entry_id' => $entry_id,
            ));
        } else {
            wp_send_json_error(array('message' => __('Failed to import page', 'ai-eshop-optimizer')));
        }
    }

    
    public function priority_to_label($priority) {
        if ($priority >= 20) return 'critical';
        if ($priority >= 10) return 'high';
        return 'normal';
    }
}
