<?php

namespace Meliconnect\Meliconnect\Modules\Importer\Models;

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

use Error;
use Meliconnect\Meliconnect\Core\Helpers\Helper;

class UserListingToImport
{

    private static $table_name;

    // Este método se llama automáticamente cuando se accede por primera vez a la clase
    public static function init()
    {
        global $wpdb;

        self::$table_name = $wpdb->prefix . 'meliconnect_user_listings_to_import';
    }

    public static function create_or_skip_meli_user_listings_ids_to_import($meli_user, $meli_user_listings_ids)
    {
        global $wpdb;

        self::init();

        $table_name = self::$table_name;

        $import_process_id = hash('sha256', $meli_user->user_id . time() . bin2hex(random_bytes(8)));

        foreach ($meli_user_listings_ids as $listing_id) {
            // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $existing_row = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM $table_name WHERE meli_user_id = %d AND meli_listing_id = %s",
                    $meli_user->user_id,
                    $listing_id
                )
            );

            if (! $existing_row) {
                // Only cretaes the row if it doesn't exist
                $wpdb->insert(
                    $table_name,
                    array(
                        'process_id'      => $import_process_id,
                        'meli_user_id'    => $meli_user->user_id,
                        'meli_listing_id' => $listing_id,
                    )
                );
            }
            // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        }

        return true;
    }

    public static function apply_item_manual_match($meli_item_id, $woo_product_id) {}





    public static function update_meli_user_listings_extra_data_to_import($meli_user_listings_data)
    {
        global $wpdb;

        self::init();

        $table_name = self::$table_name;

        $vinculated_products  = Helper::getPostsWithMetaArray('meliconnect_meli_listing_id');
        $vinculated_templates = Helper::getPostsWithMetaArray('meliconnect_meli_template_id');

        $families        = self::groupListingsByFamily($meli_user_listings_data);

        //Helper::logData( 'Families: ' . $families, 'import-actions' );


        $family_parents  = self::calculateFamilyParents($families);
        //Helper::logData( 'family_parents: ' . $family_parents, 'import-actions' );

        foreach ($meli_user_listings_data as $meli_listing_id => $listing_data) {

            self::updateListingRow(
                $meli_listing_id,
                $listing_data,
                $family_parents,
                $vinculated_products,
                $vinculated_templates,
                $table_name
            );
        }

        return true;
    }

    private static function groupListingsByFamily($meli_user_listings_data)
    {
        $families = [];

        foreach ($meli_user_listings_data as $meli_listing_id => $listing_data) {

            $family_id = $listing_data['family_id'] ?? null;

            if (!$family_id) {
                continue;
            }

            if (!isset($families[$family_id])) {
                $families[$family_id] = [];
            }

            $placed = false;

            // 🔥 intentar meterlo en un grupo compatible
            foreach ($families[$family_id] as &$group) {

                if (self::areSameProduct($group[0], $listing_data)) {
                    $group[$meli_listing_id] = $listing_data;
                    $placed = true;
                    break;
                }
            }

            // 🔥 si no encaja en ninguno → crear nuevo grupo
            if (!$placed) {
                $families[$family_id][] = [
                    $meli_listing_id => $listing_data
                ];
            }
        }

        return $families;
    }

    private static function areSameProduct($itemA, $itemB)
    {
        $attrsA = isset($itemA['attributes']) ? $itemA['attributes'] : [];
        $attrsB = isset($itemB['attributes']) ? $itemB['attributes'] : [];



        $keys = [
            'BRAND',
            'MODEL',
            'LINE',
            'ALPHANUMERIC_MODEL'
        ];

        foreach ($keys as $key) {

            $a = self::getAttrValue($attrsA, $key);
            $b = self::getAttrValue($attrsB, $key);

            if ($a !== $b) {
                return false;
            }
        }

        return true;
    }

    private static function getAttrValue($attrs, $key)
    {
        if (empty($attrs)) {
            return null;
        }

        foreach ($attrs as $attr) {

            // 🔥 soporta array u objeto
            if (is_object($attr)) {
                $attr = (array) $attr;
            }

            if (($attr['id'] ?? null) === $key) {
                return self::normalize($attr['value_id'] ?? $attr['value_name'] ?? null);
            }
        }

        return null;
    }

    private static function normalize($value)
    {
        return strtolower(trim((string) $value));
    }




    private static function calculateFamilyParents($families)
    {

        $family_parents = [];

        // 1. Obtener todos los family_ids que estamos procesando
        $family_ids = array_keys($families);

        if (empty($family_ids)) {
            return $family_parents;
        }

        // 2. Buscar TODOS los parents existentes en una sola query
        $existing_parents_query = get_posts([
            'post_type'      => 'product',
            'posts_per_page' => -1,
            'fields'         => 'ids',
            'meta_query'     => [
                [
                    'key'     => 'meliconnect_meli_family_id',
                    'value'   => $family_ids,
                    'compare' => 'IN',
                ],
                [
                    'key'   => 'meliconnect_meli_family_is_parent',
                    'value' => 1,
                ],
            ],
        ]);

        // 3. Armar mapa: family_id → meli_listing_id
        $existing_parents_map = [];

        if (! empty($existing_parents_query)) {
            foreach ($existing_parents_query as $post_id) {

                $family_id       = get_post_meta($post_id, 'meliconnect_meli_family_id', true);
                $meli_listing_id = get_post_meta($post_id, 'meliconnect_meli_listing_id', true);

                if ($family_id && $meli_listing_id) {
                    $existing_parents_map[$family_id] = $meli_listing_id;
                }
            }
        }

        // 4. Resolver parent por cada familia
        foreach ($families as $family_id => $listings) {

            // 4.1 Si ya existe en WP → usarlo
            if (isset($existing_parents_map[$family_id])) {
                $family_parents[$family_id] = $existing_parents_map[$family_id];
                continue;
            }

            // 4.2 Fallback determinístico → menor meli_listing_id
            $listing_ids = array_keys($listings);

            sort($listing_ids, SORT_STRING);

            $family_parents[$family_id] = $listing_ids[0];
        }

        return $family_parents;
    }

    private static function updateListingRow(
        $meli_listing_id,
        $listing_data,
        $family_parents,
        $vinculated_products,
        $vinculated_templates,
        $table_name
    ) {
        global $wpdb;

        $listing_data['vinculated_product_id']  = $vinculated_products[$meli_listing_id] ?? '';
        $listing_data['vinculated_template_id'] = $vinculated_templates[$meli_listing_id] ?? '';

        // 🔥 NUEVO: resolver parent por listing (no por family_id)
        $parent_data = $family_parents[$meli_listing_id] ?? null;

        $meli_family_is_parent  = 1;
        $meli_parent_listing_id = null;

        if ($parent_data) {
            $meli_family_is_parent  = $parent_data['is_parent'];
            $meli_parent_listing_id = $parent_data['parent_id'];
        }

        // 🔥 NUEVO: determinar tipo correctamente
        $is_variable = false;

        // Caso 1: tiene variaciones nativas de ML
        if (!empty($listing_data['variations'])) {
            $is_variable = true;
        }

        // Caso 2: pertenece a una familia válida (agrupada correctamente)
        if ($parent_data && $parent_data['is_parent'] === 1) {
            // solo el parent representa el variable
            $is_variable = true;
        }

        $meli_product_type = $is_variable ? 'variable' : 'simple';

        Helper::logData(
            'meli_listing_id' . var_export($meli_listing_id, true),
            'import-actions'
        );



        $listing_data_to_update = array(
            'meli_listing_title' => !empty($listing_data['family_name'])
                ? $listing_data['family_name']
                : ($listing_data['title'] ?? ''),

            'meli_response'            => wp_json_encode($listing_data),
            'meli_status'              => $listing_data['status'] ?? '',
            'meli_sub_status'          => isset($listing_data['sub_status']) ? wp_json_encode($listing_data['sub_status']) : '',
            'meli_product_type'        => $meli_product_type,

            'meli_family_id'           => $listing_data['family_id'] ?? '',
            'meli_family_name'         => $listing_data['family_name'] ?? '',

            'meli_family_is_parent'    => $meli_family_is_parent,
            'meli_parent_listing_id'   => $meli_parent_listing_id,

            'meli_user_product_id'     => $listing_data['user_product_id'] ?? '',
            'meli_seller_custom_field' => $listing_data['seller_custom_field'] ?? '',

            'vinculated_product_id'    => $listing_data['vinculated_product_id'],
            'vinculated_template_id'   => $listing_data['vinculated_template_id'],

            'import_status'            => 'pending',
        );


        $result = $wpdb->update(
            $table_name,
            $listing_data_to_update,
            array(
                'meli_listing_id' => $meli_listing_id,
            )
        );

        if ($result === false) {

            Helper::logData(
                array(
                    'message'       => 'Error updating listing row',
                    'meli_listing_id' => $meli_listing_id,
                    'last_error'    => $wpdb->last_error,
                    'last_query'    => $wpdb->last_query,
                    'data'          => $listing_data_to_update,
                ),
                'import-errors'
            );
        } else {

            /* Helper::logData(
                array(
                    'message'        => 'Listing updated successfully',
                    'meli_listing_id'=> $meli_listing_id,
                    'rows_affected'  => $wpdb->rows_affected,
                    'last_query'     => $wpdb->last_query,
                ),
                'import-actions'
            ); */
        }
    }


    public static function reset_meli_user_listings()
    {
        global $wpdb;
        self::init();

        $table_name = self::$table_name;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $result = $wpdb->query("DELETE FROM {$table_name}");

        // Verificación del resultado
        if ($result) {
            wp_send_json_success(esc_html__('User listings reset successfully', 'meliconnect'));
            return;
        }

        wp_send_json_error(esc_html__('There was an error resetting the user listings', 'meliconnect'));
        return;
    }



    public static function get_user_listings_count()
    {
        global $wpdb;
        self::init();

        $table_name = self::$table_name;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $result = $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}");

        return $result;
    }

    public static function get_user_listings_to_import($meli_listings_ids = array())
    {
        global $wpdb;

        self::init();

        $table_name = self::$table_name;

        // Solo parents
        $where = " WHERE(meli_family_is_parent = 1 OR meli_family_is_parent IS NULL)";

        if (empty($meli_listings_ids)) {
            $where .= "AND import_status = 'pending'";
        }

        if (! empty($meli_listings_ids)) {

            $placeholders = implode(
                ',',
                array_fill(0, count($meli_listings_ids), '%s')
            );

            $where .= $wpdb->prepare(
                " AND meli_listing_id IN ($placeholders)",
                $meli_listings_ids
            );
        }

        $query = "
            SELECT *
            FROM {$table_name}
            {$where}
            ORDER BY id ASC
        ";

        return $wpdb->get_results($query);
    }

    public static function get_user_listings_to_import_filtered(array $filters)
    {
        global $wpdb;
        self::init();

        $table_name = self::$table_name;
        $where      = [];
        $params     = [];

        // Solo pendientes
        if (empty($filters)) {
            $where[] = "(import_status = 'pending' )";
        }

        // Solo parents (evita duplicados de variaciones)
        $where[] = "(meli_family_is_parent = 1 OR meli_family_is_parent IS NULL)";

        // Search
        if (! empty($filters['search'])) {
            $where[] = "(meli_listing_title LIKE %s OR meli_listing_id LIKE %s OR meli_sku LIKE %s)";
            $search  = '%' . $wpdb->esc_like($filters['search']) . '%';

            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }

        // Seller
        if (! empty($filters['seller'])) {
            $where[]  = "meli_user_id = %d";
            $params[] = (int) $filters['seller'];
        }

        // Vinculation
        if (! empty($filters['vinculation_filter'])) {

            if ($filters['vinculation_filter'] === 'no_product') {
                $where[] = "(vinculated_product_id IS NULL OR vinculated_product_id = 0)";
            }

            if ($filters['vinculation_filter'] === 'yes_product') {
                $where[] = "(vinculated_product_id IS NOT NULL AND vinculated_product_id > 0)";
            }
        }

        // Status
        if (! empty($filters['listing_status_filter'])) {
            $where[]  = "meli_status = %s";
            $params[] = $filters['listing_status_filter'];
        }

        // Listing type
        if (! empty($filters['listing_type_filter'])) {
            $where[]  = "meli_product_type = %s";
            $params[] = $filters['listing_type_filter'];
        }

        $sql = "SELECT * FROM {$table_name}";

        if ($where) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
        return $params
            ? $wpdb->get_results($wpdb->prepare($sql, ...$params))
            : $wpdb->get_results($sql);
        // phpcs:enable
    }


    public static function update_vinculated_product($user_listing_id, $product_id, $match_by)
    {
        global $wpdb;

        self::init();

        $table_name = self::$table_name;

        Helper::logData('Updating user listing with id: ' . $user_listing_id, 'import-actions');

        $is_match_by_sku   = ($match_by == 'sku') ? 1 : 0;
        $is_match_by_name  = ($match_by == 'name') ? 1 : 0;
        $is_match_manually = ($match_by == 'manual') ? 1 : 0;

        $data_to_update = array(
            'vinculated_product_id'     => $product_id,
            'is_product_match_by_sku'   => $is_match_by_sku,
            'is_product_match_by_name'  => $is_match_by_name,
            'is_product_match_manually' => $is_match_manually,
            'updated_at'                => current_time('mysql'),
        );

        Helper::logData('Data to update:' . wp_json_encode($data_to_update), 'import-actions');

        $result = $wpdb->update(
            $table_name,
            $data_to_update,
            array(
                'id' => $user_listing_id,
            ),
            array(
                '%d',  // Formato para vinculated_product_id
                '%d',  // Formato para is_product_match_by_sku
                '%d',  // Formato para is_product_match_by_name
                '%s',   // Formato para updated_at
            ),
            array(
                '%d',   // Formato para el campo id
            )
        );

        if ($result === false) {
            Helper::logData('Failed to update the vinculated product with id:' . $product_id, 'import-actions');
            $error = $wpdb->last_error;
            Helper::logData($error, 'import-actions');
            return false;
        }

        return true;
    }

    public static function update_processing_listings($status)
    {
        global $wpdb;

        self::init();

        $table_name = self::$table_name;

        // Validar el status
        if (empty($status)) {
            Helper::logData('Invalid status provided for update', 'import-actions');
            return false;
        }

        $result = $wpdb->update(
            $table_name,
            array('import_status' => $status),
            array('import_status' => 'processing'),
            array('%s'),
            array('%s')
        );

        if ($result === false) {
            $error = $wpdb->last_error;
            Helper::logData($error, 'import-actions');
            return false;
        }

        // Retornar el número de filas afectadas
        return $result;
    }

    public static function update_vinculated_product_ids($meliListingIds)
    {
        global $wpdb;

        if (empty($meliListingIds)) {
            return;
        }

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
        $placeholders = implode(',', array_fill(0, count($meliListingIds), '%s'));

        $postmeta_results = $wpdb->get_results(
            $wpdb->prepare(
                "
			SELECT post_id, meta_value
			FROM {$wpdb->postmeta}
			WHERE meta_key = 'meliconnect_meli_listing_id'
			AND meta_value IN ($placeholders)
			",
                ...$meliListingIds
            )
        );
        // phpcs:enable

        if (! empty($postmeta_results)) {
            foreach ($postmeta_results as $postmeta) {
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->update(
                    'wp_meliconnect_user_listings_to_import',
                    array('vinculated_product_id' => $postmeta->post_id),
                    array('meli_listing_id' => $postmeta->meta_value),
                    array('%d'),
                    array('%s')
                );
                // phpcs:enable
            }
        }
    }

    public static function clear_matches($meli_listing_ids = 'all')
    {
        global $wpdb;

        // Nombre de tabla seguro
        $table_name = esc_sql($wpdb->prefix . 'meliconnect_user_listings_to_import');

        // Asegurar que $meli_listing_ids sea array si no es 'all'
        if (! is_array($meli_listing_ids) && $meli_listing_ids !== 'all') {
            $meli_listing_ids = array($meli_listing_ids);
        }

        Helper::logData('Clearing matches for meli_listing_ids: ' . wp_json_encode($meli_listing_ids), 'import-actions');

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.NotPrepared
        // Construir query y argumentos dinámicamente
        if (is_array($meli_listing_ids) && ! empty($meli_listing_ids)) {
            // placeholders para los IDs
            $placeholders = implode(',', array_fill(0, count($meli_listing_ids), '%s'));

            // argumentos para $wpdb->prepare(): primero updated_at, luego IDs
            $args = array_merge(array(current_time('mysql')), $meli_listing_ids);

            $sql = "
            UPDATE {$table_name}
            SET vinculated_product_id = NULL,
                is_product_match_by_sku = 0,
                is_product_match_by_name = 0,
                is_product_match_manually = 0,
                updated_at = %s
            WHERE meli_listing_id IN ($placeholders)
            AND (
                is_product_match_by_sku = 1
                OR is_product_match_by_name = 1
                OR is_product_match_manually = 1
            )
        ";
            Helper::logData('SQL: ' . $sql, 'import-actions');

            $result = $wpdb->query($wpdb->prepare($sql, ...$args));
        } else {
            // Caso "all": no hay IDs, solo updated_at
            $sql = "
            UPDATE {$table_name}
            SET vinculated_product_id = NULL,
                is_product_match_by_sku = 0,
                is_product_match_by_name = 0,
                is_product_match_manually = 0,
                updated_at = %s
            WHERE is_product_match_by_sku = 1
                OR is_product_match_by_name = 1
                OR is_product_match_manually = 1
        ";
            Helper::logData('SQL: ' . $sql, 'import-actions');

            $result = $wpdb->query($wpdb->prepare($sql, current_time('mysql')));
        }
        // phpcs:enable

        // Manejo de errores
        if ($result === false) {
            Helper::logData('Failed to clear the matches: ' . $wpdb->last_error, 'import-actions');
            return false;
        }

        return true;
    }








    public static function get_not_vinculated_user_listings_to_import()
    {
        global $wpdb;
        self::init();

        $table_name = self::$table_name;

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared	
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table_name} 
            WHERE vinculated_product_id IS NULL 
            OR vinculated_product_id = '' 
            OR vinculated_product_id = %d",
                0
            )
        );
        // phpcs:enable
    }

    public static function update_user_listing_item_import_status($meli_listing_ids, $import_status)
    {
        global $wpdb;
        self::init();

        $table_name = self::$table_name;

        if (empty($meli_listing_ids) || empty($import_status)) {
            return false;
        }

        if (! is_array($meli_listing_ids)) {
            $meli_listing_ids = array($meli_listing_ids);
        }

        $placeholders = implode(',', array_fill(0, count($meli_listing_ids), '%s'));

        // argumentos dinámicos: primero import_status, luego los IDs
        $args = array_merge(array($import_status), $meli_listing_ids);
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
        return $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$table_name} 
             SET import_status = %s 
             WHERE meli_listing_id IN ($placeholders)",
                ...$args
            )
        );
        // phpcs:enable
    }



    public static function get_user_listings_count_by_status($status)
    {
        global $wpdb;
        self::init();

        $table_name = self::$table_name;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $result = $wpdb->get_var("SELECT COUNT(*) FROM {$table_name} WHERE meli_status = '{$status}'");

        return $result;
    }

    public static function get_user_listing_by_listing_id($meli_listing_id)
    {
        global $wpdb;
        self::init();

        $table_name = self::$table_name;

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$table_name} WHERE meli_listing_id = %s",
                $meli_listing_id
            )
        );
        // phpcs:enable
    }





    public static function unlink_woo_product($meli_listing_id)
    {
        if (empty($meli_listing_id)) {
            return false;
        }

        global $wpdb;
        self::init();

        $table_name = self::$table_name;

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $result = $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$table_name} SET vinculated_product_id = 0 WHERE meli_listing_id = %s",
                $meli_listing_id
            )
        );
        // phpcs:enable

        if ($result === false) {
            Helper::logData('Error unlinking product from user listing: ' . $wpdb->last_error);
            return false;
        }

        return $result;
    }

    public static function mark_as_deleted($meli_listing_id)
    {
        if (empty($meli_listing_id)) {
            return false;
        }

        global $wpdb;
        self::init();

        $table_name = self::$table_name;

        $result = $wpdb->update(
            $table_name,
            [
                'vinculated_product_id' => 0,
                'import_status'         => 'pending',
            ],
            [
                'meli_listing_id' => $meli_listing_id,
            ]
        );

        if ($result === false) {
            Helper::logData('Error updating listing: ' . $wpdb->last_error, 'delete-worker');
            return false;
        }

        return $result;
    }
}
