<?php
if (!defined('ABSPATH')) die('Restricted Access');

class AIFLT_Abandoned_Carts_Logic {

    /**
     * Retrieves a list of abandoned carts, defined as sessions with cart intent but no sale.
     * This logic correctly identifies carts abandoned via AJAX from any page.
     *
     * @param string|null $aiflt_start_date The start date for the filter (e.g., '2025-09-01').
     * @param string|null $aiflt_end_date   The end date for the filter (e.g., '2025-09-30').
     * @param int         $aiflt_items_per_page The number of items to show per page. -1 for all.
     * @param int         $aiflt_offset The offset for pagination.
     * @param string      $aiflt_customer_type The type of customer to filter by ('all', 'guest', 'registered').
     * @return array An array containing 'carts' for the current page and 'total_carts'.
     */
    public static function get_abandoned_carts($aiflt_start_date = null, $aiflt_end_date = null, $aiflt_items_per_page = 10, $aiflt_offset = 0, $aiflt_customer_type = 'all') {
        global $wpdb;
        $funnel_table = esc_sql($wpdb->prefix . 'aiflt_funnel_data');

        // First, get all abandoned session IDs for the given date range and customer type.
        $all_abandoned_session_ids = self::get_abandoned_session_ids( $aiflt_start_date, $aiflt_end_date, $aiflt_customer_type );

        if ( empty( $all_abandoned_session_ids ) ) {
            return ['carts' => [], 'total_carts' => 0];
        }

        // Fetch the latest non-empty cart snapshot for every abandoned session first.
        // Pagination is applied after filtering so the metric cards and table footer use the same count.
        $placeholders = implode( ', ', array_fill( 0, count( $all_abandoned_session_ids ), '%s' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom analytics table query; table name is plugin-owned and values are prepared.

        $raw_data = $wpdb->get_results(
            $wpdb->prepare(
                "
                SELECT f.* FROM {$funnel_table} f
                INNER JOIN (
                    SELECT session_id, MAX(timestamp) as max_ts
                    FROM {$funnel_table}
                    WHERE session_id IN ($placeholders)
                      AND cart_data IS NOT NULL
                      AND cart_data != '[]'
                    GROUP BY session_id
                ) AS latest
                  ON f.session_id = latest.session_id
                 AND f.timestamp = latest.max_ts
                ORDER BY f.timestamp DESC
                ",
                $all_abandoned_session_ids
            ),
            ARRAY_A
        );

        $all_valid_carts = [];
        foreach ($raw_data as $event) {
            $user_email = '';
            if ($event['user_id'] > 0) {
                $user_data = get_userdata($event['user_id']);
                if ($user_data) {
                    $user_email = $user_data->user_email;
                }
            }

            $aiflt_cart_data = maybe_unserialize($event['cart_data']);
            $aiflt_item_count = isset($aiflt_cart_data['item_count']) ? (int) $aiflt_cart_data['item_count'] : 0;
            if ($aiflt_item_count < 1) {
                continue;
            }

            $aiflt_cart_contents = [];
            if (isset($aiflt_cart_data['contents']) && is_array($aiflt_cart_data['contents'])) {
                foreach($aiflt_cart_data['contents'] as $aiflt_item) {
                    $aiflt_cart_contents[] = [
                        'name' => isset($aiflt_item['name']) ? $aiflt_item['name'] : __('Unknown product', 'ai-flash-tune'),
                        'quantity' => isset($aiflt_item['quantity']) ? (int) $aiflt_item['quantity'] : 1,
                    ];
                }
            }

            $all_valid_carts[] = [
                'customer' => ($event['user_id'] > 0 && get_userdata($event['user_id'])) ? get_userdata($event['user_id'])->display_name : __('Guest', 'ai-flash-tune'),
                'customer_email' => $user_email,
                'contents' => $aiflt_cart_contents,
                'total' => $aiflt_cart_data['cart_total'] ?? (function_exists('wc_price') ? wc_price(0) : '0.00'),
                'abandoned_on' => gmdate('Y-m-d H:i', strtotime($event['timestamp'])),
                'status' => __('Abandoned', 'ai-flash-tune'),
            ];
        }

        $aiflt_total_carts = count($all_valid_carts);
        $paged_carts = ($aiflt_items_per_page > 0)
            ? array_slice($all_valid_carts, $aiflt_offset, $aiflt_items_per_page)
            : $all_valid_carts;

        return ['carts' => $paged_carts, 'total_carts' => $aiflt_total_carts];
    }
    
    /**
     * Checks whether a plugin-owned table contains a column before using it in SQL.
     * This prevents runtime errors on older installs where addon migrations have not run yet.
     *
     * @param string $table  Full table name.
     * @param string $column Column name to check.
     * @return bool
     */
    private static function aiflt_table_column_exists($table, $column) {
        global $wpdb;

        $table  = esc_sql($table);
        $column = sanitize_key($column);

        if (empty($table) || empty($column)) {
            return false;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name is escaped and column lookup value is prepared.
        return (bool) $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM {$table} LIKE %s", $column));
    }
    /**
     * Retrieves high-level metrics (total carts, lost revenue) for the Abandoned Carts page.
     * This version is corrected to calculate totals across ALL abandoned carts, ignoring pagination,
     * and only counts non-empty carts to ensure consistency.
     */
    public static function get_abandoned_carts_metrics($aiflt_start_date = null, $aiflt_end_date = null, $aiflt_customer_type = 'all') {
        // This is the single source of truth for identifying abandoned sessions.
        $abandoned_session_ids = self::get_abandoned_session_ids($aiflt_start_date, $aiflt_end_date, $aiflt_customer_type);

        // If there are no sessions with cart intent, there's nothing to do.
        if (empty($abandoned_session_ids)) {
            return [
                'total_abandoned_carts' => 0,
                'total_lost_revenue'    => 0,
                'recovery_rate'         => '0%',
            ];
        }

        global $wpdb;
        $funnel_table = $wpdb->prefix . 'aiflt_funnel_data';

        $aiflt_total_abandoned_count = 0;
        $aiflt_total_lost_revenue = 0;

        // Fetch the latest cart_data for ALL identified abandoned sessions.
        $placeholders = implode( ', ', array_fill( 0, count( $abandoned_session_ids ), '%s' ) );

        $aiflt_cart_data_results = $wpdb->get_col(
            $wpdb->prepare(
                "
                SELECT f.cart_data
                FROM {$funnel_table} f
                INNER JOIN (
                    SELECT session_id, MAX(timestamp) as max_ts
                    FROM {$funnel_table}
                    WHERE session_id IN ($placeholders) 
                      AND cart_data IS NOT NULL 
                      AND cart_data != '[]'
                    GROUP BY session_id
                ) AS latest 
                  ON f.session_id = latest.session_id 
                 AND f.timestamp = latest.max_ts
                ",
                $abandoned_session_ids
            )
        );

        // Now, iterate through all results to calculate the grand totals, filtering out empty carts.
        foreach ($aiflt_cart_data_results as $aiflt_cart_data_serialized) {
            $aiflt_cart_data = maybe_unserialize($aiflt_cart_data_serialized);

            // This is the critical check: only process carts that actually had items.
            if (is_array($aiflt_cart_data) && !empty($aiflt_cart_data['item_count']) && $aiflt_cart_data['item_count'] > 0) {
                // Increment the total count only for valid, non-empty carts.
                $aiflt_total_abandoned_count++;

                if (!empty($aiflt_cart_data['cart_total'])) {
                    $aiflt_cart_total_clean = html_entity_decode($aiflt_cart_data['cart_total']);
                    $aiflt_cart_total_clean = wp_strip_all_tags($aiflt_cart_total_clean);
                    $aiflt_cart_total_clean = preg_replace('/[^\d.]/', '', $aiflt_cart_total_clean);
                    $aiflt_total_lost_revenue += floatval($aiflt_cart_total_clean);
                }
            }
        }

        // --- Recovery rate calculation ---
        // Prefer token attribution from the Cart Recovery addon. Fallback to the legacy email log if present.
        $aiflt_recovery_rate = 0;
        $recovered_count = 0;
        $aiflt_start_date_sql = $aiflt_start_date ? $aiflt_start_date . ' 00:00:00' : gmdate('Y-m-d H:i:s', strtotime('-30 days'));
        $aiflt_end_date_sql   = $aiflt_end_date ? $aiflt_end_date . ' 23:59:59' : gmdate('Y-m-d H:i:s');

        $tokens_table = esc_sql($wpdb->prefix . 'aiftcr_recovery_tokens');
        $tokens_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tokens_table));
        if ($tokens_exists === $tokens_table) {
            $recovered_date_expression = self::aiflt_table_column_exists($tokens_table, 'recovered_at')
                ? 'COALESCE(recovered_at, created_at)'
                : 'created_at';

            $recovered_count = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT session_id)
                 FROM {$tokens_table}
                 WHERE recovered_order_id IS NOT NULL
                   AND recovered_order_id > 0
                   AND {$recovered_date_expression} BETWEEN %s AND %s",
                $aiflt_start_date_sql,
                $aiflt_end_date_sql
            ));
        } else {
            $log_table = esc_sql($wpdb->prefix . 'aiftcr_email_log');
            $log_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $log_table));
            if ($log_exists === $log_table) {
                $recovered_count = (int) $wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(DISTINCT session_id)
                     FROM {$log_table}
                     WHERE status = %s
                       AND sent_at BETWEEN %s AND %s",
                    'Recovered',
                    $aiflt_start_date_sql,
                    $aiflt_end_date_sql
                ));
            }
        }

        $aiflt_total_abandoned_carts_for_rate = count($abandoned_session_ids);
        if ($aiflt_total_abandoned_carts_for_rate > 0) {
            $aiflt_recovery_rate = round(($recovered_count / $aiflt_total_abandoned_carts_for_rate) * 100, 2);
        }
        // --- END: Recovery rate calculation ---

        return [
            'total_abandoned_carts' => $aiflt_total_abandoned_count,
            'total_lost_revenue'    => $aiflt_total_lost_revenue,
            'recovery_rate'         => $aiflt_recovery_rate . '%',
        ];
    }

    /**
     * Helper function to get a clean list of abandoned session IDs. This is the single source of truth for identifying abandonments.
     */
    public static function get_abandoned_session_ids01($aiflt_start_date = null, $aiflt_end_date = null, $aiflt_customer_type = 'all', $aiflt_device_type = 'all') {
        global $wpdb;
        $funnel_table   = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $behavior_table = esc_sql($wpdb->prefix . 'aiflt_behavior_data');

        $aiflt_start_date_sql = $aiflt_start_date ? $aiflt_start_date . ' 00:00:00' : gmdate('Y-m-d H:i:s', strtotime('-30 days'));
        $aiflt_end_date_sql   = $aiflt_end_date ? $aiflt_end_date . ' 23:59:59' : gmdate('Y-m-d H:i:s');

        // --- Build dynamic WHERE conditions ---
        $where_parts = ["timestamp BETWEEN %s AND %s"];
        $params = [$aiflt_start_date_sql, $aiflt_end_date_sql];

        // Customer type filter
        if ($aiflt_customer_type === 'guest') {
            $where_parts[] = "user_id = %d";
            $params[] = 0;
        } elseif ($aiflt_customer_type === 'registered') {
            $where_parts[] = "user_id > %d";
            $params[] = 0;
        }

        // Device type filter
        if (!empty($aiflt_device_type) && $aiflt_device_type !== 'all') {
            $where_parts[] = "device_type = %s";
            $params[] = $aiflt_device_type;
        }

        $where_clause = implode(' AND ', $where_parts);

        // --- Step 1: Completed sessions ---
        $completed_sessions = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT DISTINCT session_id 
                 FROM {$funnel_table} 
                 WHERE page_name = 'sale' AND {$where_clause}",
                ...$params
            )
        );

        // --- Step 2: Sessions with cart intent ---
        $sessions_with_cart_intent = $wpdb->get_col(
            $wpdb->prepare(
                "
                (SELECT DISTINCT session_id FROM {$funnel_table} 
                 WHERE page_name = 'cart' AND {$where_clause})
                UNION
                (SELECT DISTINCT session_id FROM {$behavior_table} 
                 WHERE behavior_type = 'add_to_cart' AND {$where_clause})
                ",
                ...array_merge($params, $params)
            )
        );

        // --- Step 3: Return the difference ---
        return array_diff($sessions_with_cart_intent, $completed_sessions);
    }
    /**
     * Helper function to get a clean list of abandoned session IDs. This is the single source of truth for identifying abandonments.
     * @param string|null $aiflt_start_date 
     * @param string|null $aiflt_end_date 
     * @param string      $aiflt_customer_type
     * @param string      $aiflt_device_type ('all', 'desktop', or 'mobile')
     * @return array
     */
    public static function get_abandoned_session_ids($aiflt_start_date = null, $aiflt_end_date = null, $aiflt_customer_type = 'all', $aiflt_device_type = 'all') {
        global $wpdb;
        $funnel_table   = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $behavior_table = esc_sql($wpdb->prefix . 'aiflt_behavior_data');

        $aiflt_start_date_sql = $aiflt_start_date ? $aiflt_start_date . ' 00:00:00' : gmdate('Y-m-d H:i:s', strtotime('-30 days'));
        $aiflt_end_date_sql   = $aiflt_end_date ? $aiflt_end_date . ' 23:59:59' : gmdate('Y-m-d H:i:s');

        // --- Filters ---
        $customer_filter_funnel = '';
        $customer_filter_behavior = '';
        if ($aiflt_customer_type === 'guest') {
            $customer_filter_funnel   = " AND user_id = 0";
            $customer_filter_behavior = " AND user_id = 0";
        } elseif ($aiflt_customer_type === 'registered') {
            $customer_filter_funnel   = " AND user_id > 0";
            $customer_filter_behavior = " AND user_id > 0";
        }

        // Device filter
        $device_filter_funnel = '';
        $device_args = [];
        if ($aiflt_device_type !== 'all') {
            $device_filter_funnel = " AND device_type = %s";
            $device_args[] = $aiflt_device_type;
        }

        $base_args = [$aiflt_start_date_sql, $aiflt_end_date_sql];

        // --- Step 1: Completed Sessions ---
        // --- Step 1: Completed Sessions (Must check final order status) ---
        $completed_sessions_sql = "
            SELECT DISTINCT T1.session_id
            FROM {$funnel_table} T1
            INNER JOIN {$wpdb->prefix}wc_order_stats T2 
                ON T1.order_id = T2.order_id
            WHERE T1.page_name = 'checkout_attempt' -- Look for sessions that started an order process
              AND T1.timestamp BETWEEN %s AND %s
              {$customer_filter_funnel}
              {$device_filter_funnel}
              AND T2.status NOT IN ('wc-cancelled', 'wc-failed', 'wc-refunded')
        ";
        
        // Ensure the arguments are passed correctly: [ts1, ts2, device_type]
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is safely constructed using prefixed table names and controlled internal strings for conditional filtering.
        $completed_sessions = $wpdb->get_col($wpdb->prepare(
            $completed_sessions_sql,
            ...array_merge($base_args, $device_args)
        ));

        // --- Step 2: Sessions with Cart Intent ---
        $sessions_with_cart_intent = $wpdb->get_col(
            $wpdb->prepare(
                "
                (SELECT DISTINCT session_id
                   FROM {$funnel_table}
                  WHERE page_name = 'cart'
                    AND timestamp BETWEEN %s AND %s
                    {$customer_filter_funnel}
                    {$device_filter_funnel})
                UNION
                (SELECT DISTINCT session_id
                   FROM {$behavior_table}
                  WHERE behavior_type = 'add_to_cart'
                    AND timestamp BETWEEN %s AND %s
                    {$customer_filter_behavior})
                ",
                ...array_merge($base_args, $device_args, $base_args)
            )
        );

        // --- Step 3: Filter by Device (if needed) ---
        if ($aiflt_device_type !== 'all' && !empty($sessions_with_cart_intent)) {
            $placeholders = implode(', ', array_fill(0, count($sessions_with_cart_intent), '%s'));
            $sessions_matching_device = $wpdb->get_col(
                $wpdb->prepare(
                    "
                    SELECT T1.session_id
                    FROM {$funnel_table} T1
                    INNER JOIN (
                        SELECT session_id, MAX(timestamp) AS max_ts
                        FROM {$funnel_table}
                        WHERE session_id IN ({$placeholders})
                        GROUP BY session_id
                    ) T2 ON T1.session_id = T2.session_id AND T1.timestamp = T2.max_ts
                    WHERE T1.device_type = %s
                    ",
                    ...array_merge($sessions_with_cart_intent, [$aiflt_device_type])
                )
            );
            $sessions_with_cart_intent = $sessions_matching_device;
        }

        // --- Step 4: Return Abandoned Sessions ---
        return array_diff($sessions_with_cart_intent, $completed_sessions);
    }
    
    /**
     * Placeholder for the AI Abandonment Reason Predictor logic.
     *
     * @return string The AI-generated reason prediction.
     */
    public static function get_abandonment_reason_prediction() {
        $prediction = __('AI analysis suggests a high bounce rate on the cart page is linked to unexpected shipping costs. Try showing a shipping calculator earlier in the funnel.', 'ai-flash-tune');
        return $prediction;
    }
}
