<?php

namespace Easymailing\App\Controller\Admin;

use Easymailing\App\Core\Http\Request;
use Easymailing\App\Core\Provider\Woocommerce\Model\BatchSynchronizationStatus;
use Easymailing\App\Core\Provider\Woocommerce\Model\LogType;
use Easymailing\App\Core\Provider\Woocommerce\Model\SkipReason;
use Easymailing\App\Core\Provider\Woocommerce\Model\SynchronizationStatus;
use Easymailing\App\Core\Provider\Woocommerce\Service\MetadataHelper;

class DashboardController extends BaseSynchronizationController
{
    public function indexAction(?string $message = null, ?string $type = null)
    {
        $this->checkConfig();
        $this->checkGeneralData();

        $request = $this->getRequest();
        if ($request->getMethod() === Request::METHOD_POST) {
            $this->handlePostRequest($request);
            return;
        }

        $countData = $this->getEasymailingData();
        $errorData = $this->getErrorData();
        $errorItems = $this->getErrorItems();
        $skippedData = $this->getSkippedData();

        $syncStatus = get_option(MetadataHelper::getBatchSynchronizationStatus());
        $isSyncing = in_array($syncStatus, [
            BatchSynchronizationStatus::IN_PROGRESS,
            BatchSynchronizationStatus::SCHEDULED,
        ], true);

		$parameters = [
			'current_page' => 0,
			"number_of_customers" => $countData['customers'],
			'number_of_orders' => $countData['orders'],
			'number_of_products' => $countData['products'],
			'number_of_categories' => $countData['categories'],
			'errors_customers' => $errorData['customers'],
			'errors_orders' => $errorData['orders'],
			'errors_products' => $errorData['products'],
			'errors_categories' => $errorData['categories'],
			'total_errors' => array_sum($errorData),
			'error_customers_items' => $errorItems['customers'],
			'error_orders_items' => $errorItems['orders'],
			'error_products_items' => $errorItems['products'],
			'error_categories_items' => $errorItems['categories'],
			'skipped_customers_count' => $skippedData['customers']['total'],
			'skipped_customers_text' => $this->formatSkippedReasons($skippedData['customers']['reasons']),
			'skipped_customers_items' => $skippedData['customers']['items'],
			'skipped_orders_count' => $skippedData['orders']['total'],
			'skipped_orders_text' => $this->formatSkippedReasons($skippedData['orders']['reasons']),
			'skipped_orders_items' => $skippedData['orders']['items'],
			'total_skipped' => $skippedData['customers']['total'] + $skippedData['orders']['total'],
			'is_syncing' => $isSyncing,
			'audience' => $this->getAudienceFromSynchronizationConfig(),
			'groupIri' => $this->getSynchronizationConfig()->getGroup(),
		];

		if($message && $type){
			$parameters['admin_notice_message'] = $message;
            $parameters['admin_notice_type'] = $type;
		}

        $this->render('Admin/Synchronization/index.php', $parameters);
    }

    private function handlePostRequest($request): void
    {
        $action = $request->request->get('action');

        if ($action === 'retry_failed') {
            try {
                $count = $this->retryFailedItems();
                $this->getFlashBag()->add(
                    'success',
                    sprintf(
                        /* translators: %d: number of items queued for retry */
                        __('Se han reencolado %d elementos fallidos para reintentar la sincronización.', 'easymailing'),
                        $count
                    )
                );
            } catch (\Exception $e) {
                $this->getContainer()->get('log_manager')->createLog(LogType::ERROR, $e->getMessage());
                $this->getFlashBag()->add('error', sprintf("%s: %s", __('Error al reintentar la sincronización', 'easymailing'), $e->getMessage()));
            }
            $this->redirectToMenuPage('easymailing_synchronization');
            return;
        }

        if ($action === 'dismiss_failed') {
            try {
                $count = $this->getContainer()->get('failed_synchronization_cleaner')->dismissFailedItems();
                $this->getFlashBag()->add(
                    'success',
                    sprintf(
                        /* translators: %d: number of failed items dismissed */
                        __('Se han descartado %d elementos fallidos. No se volverán a mostrar para reintento.', 'easymailing'),
                        $count
                    )
                );
            } catch (\Exception $e) {
                $this->getContainer()->get('log_manager')->createLog(LogType::ERROR, $e->getMessage());
                $this->getFlashBag()->add('error', sprintf("%s: %s", __('Error al descartar elementos fallidos', 'easymailing'), $e->getMessage()));
            }
            $this->redirectToMenuPage('easymailing_synchronization');
            return;
        }

        $this->redirectToMenuPage('easymailing_synchronization');
    }

    /**
     * Reencola los elementos con synchronization_status='error' para sincronizarlos
     * de nuevo, sin tocar ningún meta. El BatchResultProcessManager ya limpió los
     * identificadores huérfanos tras el error anterior y el Fix B reactivo pudo
     * haber adoptado resource_ids válidos; borrarlos aquí destruiría ese recovery
     * y forzaría otro 422 "already used" en un bucle infinito.
     *
     * Se activa el flag retry_only_mode para que los BatchOperations filtren y
     * solo incluyan items en error (no resincronizan todo).
     */
    private function retryFailedItems(): int
    {
        global $wpdb;

        $statusKey = MetadataHelper::getSynchronizationStatus();

        $userCount = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->usermeta} WHERE meta_key = %s AND meta_value = 'error'",
            $statusKey
        ));
        $postCount = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE meta_key = %s AND meta_value = 'error'",
            $statusKey
        ));
        $termCount = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->termmeta} WHERE meta_key = %s AND meta_value = 'error'",
            $statusKey
        ));

        $total = $userCount + $postCount + $termCount;

        if ($total === 0) {
            return 0;
        }

        update_option(MetadataHelper::getBatchSynchronizationStatus(), BatchSynchronizationStatus::SCHEDULED);
        $this->getContainer()->get('batch_enqueue_manager')->enqueue(true);

        return $total;
    }

    private function getSkippedData(): array
    {
        global $wpdb;
        $statusKey = MetadataHelper::getSynchronizationStatus();
        $reasonKey = MetadataHelper::getSkipReason();

        $customersResults = $wpdb->get_results($wpdb->prepare("
            SELECT r.meta_value AS reason, COUNT(*) AS c
            FROM {$wpdb->usermeta} s
            LEFT JOIN {$wpdb->usermeta} r ON r.user_id = s.user_id AND r.meta_key = %s
            WHERE s.meta_key = %s AND s.meta_value = %s
            GROUP BY r.meta_value
        ", $reasonKey, $statusKey, SynchronizationStatus::SKIPPED));

        $ordersResults = $wpdb->get_results($wpdb->prepare("
            SELECT r.meta_value AS reason, COUNT(*) AS c
            FROM {$wpdb->postmeta} s
            INNER JOIN {$wpdb->posts} p ON s.post_id = p.ID
            LEFT JOIN {$wpdb->postmeta} r ON r.post_id = s.post_id AND r.meta_key = %s
            WHERE s.meta_key = %s AND s.meta_value = %s
              AND p.post_type IN ('shop_order', 'shop_order_placehold')
            GROUP BY r.meta_value
        ", $reasonKey, $statusKey, SynchronizationStatus::SKIPPED));

        return [
            'customers' => array_merge(
                $this->reduceSkipped($customersResults),
                ['items' => $this->getSkippedCustomerItems()]
            ),
            'orders'    => array_merge(
                $this->reduceSkipped($ordersResults),
                ['items' => $this->getSkippedOrderItems()]
            ),
        ];
    }

    private function getSkippedCustomerItems(int $limit = 200): array
    {
        global $wpdb;
        $statusKey = MetadataHelper::getSynchronizationStatus();
        $reasonKey = MetadataHelper::getSkipReason();

        $rows = $wpdb->get_results($wpdb->prepare("
            SELECT u.ID, u.user_email, u.display_name, r.meta_value AS reason
            FROM {$wpdb->users} u
            INNER JOIN {$wpdb->usermeta} s ON s.user_id = u.ID AND s.meta_key = %s AND s.meta_value = %s
            LEFT JOIN {$wpdb->usermeta} r ON r.user_id = u.ID AND r.meta_key = %s
            ORDER BY u.ID DESC
            LIMIT %d
        ", $statusKey, SynchronizationStatus::SKIPPED, $reasonKey, $limit));

        $items = [];
        foreach ($rows as $row) {
            $id = (int) $row->ID;
            $code = (string) $row->reason;
            $items[] = [
                'id'       => $id,
                'label'    => $row->display_name ?: ($row->user_email ?: '#' . $id),
                'email'    => $row->user_email ?: '',
                'reason'   => SkipReason::getLabel($code),
                'hint'     => SkipReason::getHint($code),
                'edit_url' => admin_url('user-edit.php?user_id=' . $id),
            ];
        }

        return $items;
    }

    private function getSkippedOrderItems(int $limit = 200): array
    {
        global $wpdb;
        $statusKey = MetadataHelper::getSynchronizationStatus();
        $reasonKey = MetadataHelper::getSkipReason();

        $rows = $wpdb->get_results($wpdb->prepare("
            SELECT p.ID, r.meta_value AS reason
            FROM {$wpdb->postmeta} s
            INNER JOIN {$wpdb->posts} p ON s.post_id = p.ID
            LEFT JOIN {$wpdb->postmeta} r ON r.post_id = s.post_id AND r.meta_key = %s
            WHERE s.meta_key = %s AND s.meta_value = %s
              AND p.post_type IN ('shop_order', 'shop_order_placehold')
            ORDER BY p.ID DESC
            LIMIT %d
        ", $reasonKey, $statusKey, SynchronizationStatus::SKIPPED, $limit));

        $items = [];
        foreach ($rows as $row) {
            $id = (int) $row->ID;
            $order = function_exists('wc_get_order') ? wc_get_order($id) : null;
            $number = $order ? $order->get_order_number() : (string) $id;
            $email = $order ? $order->get_billing_email() : '';
            $code = (string) $row->reason;
            $items[] = [
                'id'       => $id,
                'label'    => sprintf(__('Pedido #%s', 'easymailing'), $number),
                'email'    => $email,
                'reason'   => SkipReason::getLabel($code),
                'hint'     => SkipReason::getHint($code),
                'edit_url' => $this->getOrderEditUrl($id),
            ];
        }

        return $items;
    }

    private function getOrderEditUrl(int $orderId): string
    {
        // HPOS (custom order tables) usa admin.php?page=wc-orders; legacy usa post.php.
        if (class_exists('Automattic\\WooCommerce\\Utilities\\OrderUtil')
            && \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled()) {
            return admin_url('admin.php?page=wc-orders&action=edit&id=' . $orderId);
        }

        return admin_url('post.php?post=' . $orderId . '&action=edit');
    }

    private function reduceSkipped($results): array
    {
        $total = 0;
        $reasons = [];
        foreach ($results as $row) {
            $count = (int) $row->c;
            $total += $count;
            $reasons[$row->reason ?: 'unknown'] = $count;
        }

        return ['total' => $total, 'reasons' => $reasons];
    }

    private function formatSkippedReasons(array $reasons): string
    {
        $parts = [];
        foreach ($reasons as $reason => $count) {
            $parts[] = sprintf('%d %s', $count, SkipReason::getLabel($reason));
        }

        return implode(' · ', $parts);
    }

    private function getErrorItems(int $limit = 200): array
    {
        global $wpdb;
        $statusKey = MetadataHelper::getSynchronizationStatus();
        $errorKey = MetadataHelper::getErrorMessage();

        $customerRows = $wpdb->get_results($wpdb->prepare("
            SELECT u.ID, u.user_email, u.display_name, e.meta_value AS error_message
            FROM {$wpdb->users} u
            INNER JOIN {$wpdb->usermeta} s ON s.user_id = u.ID AND s.meta_key = %s AND s.meta_value = 'error'
            LEFT JOIN {$wpdb->usermeta} e ON e.user_id = u.ID AND e.meta_key = %s
            ORDER BY u.ID DESC
            LIMIT %d
        ", $statusKey, $errorKey, $limit));

        $customers = [];
        foreach ($customerRows as $row) {
            $id = (int) $row->ID;
            $customers[] = [
                'id'       => $id,
                'label'    => $row->display_name ?: ($row->user_email ?: '#' . $id),
                'email'    => $row->user_email ?: '',
                'error'    => (string) ($row->error_message ?? ''),
                'edit_url' => admin_url('user-edit.php?user_id=' . $id),
            ];
        }

        $orderRows = $wpdb->get_results($wpdb->prepare("
            SELECT p.ID, e.meta_value AS error_message
            FROM {$wpdb->postmeta} s
            INNER JOIN {$wpdb->posts} p ON s.post_id = p.ID
            LEFT JOIN {$wpdb->postmeta} e ON e.post_id = p.ID AND e.meta_key = %s
            WHERE s.meta_key = %s AND s.meta_value = 'error'
              AND p.post_type IN ('shop_order', 'shop_order_placehold')
            ORDER BY p.ID DESC
            LIMIT %d
        ", $errorKey, $statusKey, $limit));

        $orders = [];
        foreach ($orderRows as $row) {
            $id = (int) $row->ID;
            $order = function_exists('wc_get_order') ? wc_get_order($id) : null;
            $number = $order ? $order->get_order_number() : (string) $id;
            $email = $order ? $order->get_billing_email() : '';
            $orders[] = [
                'id'       => $id,
                'label'    => sprintf(__('Pedido #%s', 'easymailing'), $number),
                'email'    => $email,
                'error'    => (string) ($row->error_message ?? ''),
                'edit_url' => $this->getOrderEditUrl($id),
            ];
        }

        $productRows = $wpdb->get_results($wpdb->prepare("
            SELECT p.ID, p.post_title, e.meta_value AS error_message
            FROM {$wpdb->postmeta} s
            INNER JOIN {$wpdb->posts} p ON s.post_id = p.ID
            LEFT JOIN {$wpdb->postmeta} e ON e.post_id = p.ID AND e.meta_key = %s
            WHERE s.meta_key = %s AND s.meta_value = 'error'
              AND p.post_type IN ('product', 'product_variation')
            ORDER BY p.ID DESC
            LIMIT %d
        ", $errorKey, $statusKey, $limit));

        $products = [];
        foreach ($productRows as $row) {
            $id = (int) $row->ID;
            $products[] = [
                'id'       => $id,
                'label'    => $row->post_title ?: sprintf('#%d', $id),
                'email'    => '',
                'error'    => (string) ($row->error_message ?? ''),
                'edit_url' => admin_url('post.php?post=' . $id . '&action=edit'),
            ];
        }

        $categoryRows = $wpdb->get_results($wpdb->prepare("
            SELECT t.term_id, t.name, e.meta_value AS error_message
            FROM {$wpdb->termmeta} m
            INNER JOIN {$wpdb->terms} t ON m.term_id = t.term_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
            LEFT JOIN {$wpdb->termmeta} e ON e.term_id = t.term_id AND e.meta_key = %s
            WHERE m.meta_key = %s AND m.meta_value = 'error'
              AND tt.taxonomy = 'product_cat'
            ORDER BY t.term_id DESC
            LIMIT %d
        ", $errorKey, $statusKey, $limit));

        $categories = [];
        foreach ($categoryRows as $row) {
            $id = (int) $row->term_id;
            $categories[] = [
                'id'       => $id,
                'label'    => $row->name ?: sprintf('#%d', $id),
                'email'    => '',
                'error'    => (string) ($row->error_message ?? ''),
                'edit_url' => admin_url('term.php?taxonomy=product_cat&tag_ID=' . $id),
            ];
        }

        return [
            'customers'  => $customers,
            'orders'     => $orders,
            'products'   => $products,
            'categories' => $categories,
        ];
    }

    private function getErrorData(): array
    {
        global $wpdb;
        $statusKey = MetadataHelper::getSynchronizationStatus();

        $ordersCount = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*)
            FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
            WHERE pm.meta_key = %s
              AND pm.meta_value = %s
              AND p.post_type IN ('shop_order', 'shop_order_placehold')
        ", $statusKey, SynchronizationStatus::ERROR));

        $productsCount = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*)
            FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
            WHERE pm.meta_key = %s
              AND pm.meta_value = %s
              AND p.post_type IN ('product', 'product_variation')
        ", $statusKey, SynchronizationStatus::ERROR));

        $categoriesCount = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*)
            FROM {$wpdb->termmeta} tm
            INNER JOIN {$wpdb->terms} t ON tm.term_id = t.term_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
            WHERE tm.meta_key = %s
              AND tm.meta_value = %s
              AND tt.taxonomy = 'product_cat'
        ", $statusKey, SynchronizationStatus::ERROR));

        $customersCount = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*)
            FROM {$wpdb->usermeta} um
            WHERE um.meta_key = %s
              AND um.meta_value = %s
        ", $statusKey, SynchronizationStatus::ERROR));

        return [
            'orders' => intval($ordersCount),
            'customers' => intval($customersCount),
            'products' => intval($productsCount),
            'categories' => intval($categoriesCount),
        ];
    }


    function get_woocommerce_orders_count()
    {
        $args = array(
            'limit' => -1,
            //'status' => array('wc-completed', 'wc-processing', 'wc-on-hold'),
        );
        $orders = wc_get_orders($args);
        return count($orders);
    }

    function get_woocommerce_products_count()
    {
        $args = array(
            //'status' => 'publish',
            'limit' => -1,
        );
        $products = wc_get_products($args);
        return count($products);
    }
    // Función para obtener el número de cupones
    function get_woocommerce_coupons_count()
    {
        global $wpdb;
        $coupons_count = $wpdb->get_var("
        SELECT COUNT(*) 
        FROM {$wpdb->prefix}posts
        WHERE post_type = 'shop_coupon'
    ");
        return $coupons_count;
    }

	private function getEasymailingData()
	{
		global $wpdb;

		try {
			// Consultar pedidos sincronizados
			$ordersCount = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) 
            FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
            WHERE pm.meta_key = %s 
              AND pm.meta_value = %s 
              AND p.post_type IN ('shop_order', 'shop_order_placehold')
        ", MetadataHelper::getSynchronizationStatus(), SynchronizationStatus::SUCCESS));

			// Consultar productos sincronizados
			$productsCount = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) 
            FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
            WHERE pm.meta_key = %s 
              AND pm.meta_value = %s 
              AND p.post_type = 'product'
        ", MetadataHelper::getSynchronizationStatus(), SynchronizationStatus::SUCCESS));

			// Consultar categorías sincronizadas
			$categoriesCount = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) 
            FROM {$wpdb->termmeta} tm
            INNER JOIN {$wpdb->terms} t ON tm.term_id = t.term_id
            INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
            WHERE tm.meta_key = %s 
              AND tm.meta_value = %s 
              AND tt.taxonomy = 'product_cat'
        ", MetadataHelper::getSynchronizationStatus(), SynchronizationStatus::SUCCESS));

			// Consultar clientes sincronizados
			$customersCount = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) 
            FROM {$wpdb->usermeta} um
            WHERE um.meta_key = %s 
              AND um.meta_value = %s
        ", MetadataHelper::getSynchronizationStatus(), SynchronizationStatus::SUCCESS));

			// Retornar los resultados como un array
			return [
				'orders' => intval($ordersCount),
				'customers' => intval($customersCount),
				'products' => intval($productsCount),
				'categories' => intval($categoriesCount),
			];
		} catch (\Exception $e) {
			$this->render('Admin/Synchronization/error.php', [
				'message' => __('No se han podido obtener los datos de Easymailing.', 'easymailing'),
				'current_page' => 0,
			]);
			wp_die();
		}
	}
}
