<?php
/**
 * Best Sellers Extension
 *
 * Tracks product sales counts and provides query helpers for
 * "Best Sellers" and "On Sale" product lists.
 *
 * Hooks:
 *   - titancart_order_placed → increments sales_count on each product
 *   - titancart_order_status_changed → decrements on cancellation/refund
 *
 * Settings read from tc_settings section = 'bestsellers':
 *   final_sale_from (%), sales_amount_from (#), period, last_days
 *
 * DB columns referenced:
 *   products: id, slug, sku, price, list_price, sales_count,
 *             stock_quantity, stock_status, rating_avg, rating_count, status, parent_id
 *   product_translations: product_id, language_code, name, short_desc
 *   product_images: product_id, wp_attachment_id, position
 *   order_items: order_id, product_id, quantity
 *
 * @package TitanCart\Extensions
 */

namespace TitanCart\Extensions;

use TitanCart\REST\Extensions\ExtensionsController;

defined( 'ABSPATH' ) || exit;

class BestsellersExtension {

    public static function init(): void {
        if ( ! ExtensionsController::is_active( 'bestsellers' ) ) {
            return;
        }

        // Increment sales_count when an order's payment COMMITS (not merely
        // placed) — a declined order must not inflate sales_count. KI-04.
        add_action( 'titancart_order_committed', [ self::class, 'increment_sales' ], 10, 3 );

        // Decrement on cancellation / refund.
        add_action( 'titancart_order_status_changed', [ self::class, 'maybe_decrement_sales' ], 10, 3 );

        // Register REST endpoint for bestsellers / on-sale lists.
        add_action( 'rest_api_init', [ self::class, 'register_routes' ] );
    }

    /* ══════════════════════════════════════════════════════════════════════
     * SALES COUNT — INCREMENT ON ORDER PLACED
     * ══════════════════════════════════════════════════════════════════════ */

    /**
     * Called via `titancart_order_committed` hook (genuine paid commit, KI-04).
     *
     * Reads line items from the PERSISTED tc_order_items by order_id rather than
     * from the event $totals — the async/3DS commit dispatch passes an empty
     * $totals. Mirrors maybe_decrement_sales() below. Idempotency is provided by
     * the once-per-order commit transient.
     *
     * @param int   $order_id
     * @param array $order_data
     * @param array $totals     Empty on the deferred (Stripe Elements) commit path.
     */
    public static function increment_sales( int $order_id, array $order_data = [], array $totals = [] ): void {
        $db = titancart_db();

        $items = $db->getArray(
            "SELECT product_id, quantity FROM {$db->table('order_items')} WHERE order_id = ?i",
            $order_id
        );

        foreach ( (array) $items as $item ) {
            $pid = (int) ( $item['product_id'] ?? 0 );
            $qty = (int) ( $item['quantity']   ?? 1 );

            if ( $pid > 0 && $qty > 0 ) {
                $db->query(
                    "UPDATE {$db->table('products')}
                     SET sales_count = sales_count + ?i
                     WHERE id = ?i",
                    $qty, $pid
                );
            }
        }
    }

    /* ══════════════════════════════════════════════════════════════════════
     * SALES COUNT — DECREMENT ON CANCEL / REFUND
     * ══════════════════════════════════════════════════════════════════════ */

    /**
     * Called via `titancart_order_status_changed`.
     *
     * @param int    $order_id
     * @param string $new_status
     * @param string $old_status
     */
    public static function maybe_decrement_sales( int $order_id, string $new_status, string $old_status ): void {
        // Only decrement when moving TO cancelled/refunded FROM a non-cancelled status.
        $cancel_statuses = [ 'cancelled', 'refunded' ];

        if ( ! in_array( $new_status, $cancel_statuses, true ) || in_array( $old_status, $cancel_statuses, true ) ) {
            return;
        }

        $db    = titancart_db();
        $items = $db->getArray(
            "SELECT product_id, quantity FROM {$db->table('order_items')} WHERE order_id = ?i",
            $order_id
        );

        foreach ( $items as $item ) {
            $pid = (int) $item['product_id'];
            $qty = (int) $item['quantity'];

            if ( $pid > 0 && $qty > 0 ) {
                $db->query(
                    "UPDATE {$db->table('products')}
                     SET sales_count = GREATEST(0, CAST(sales_count AS SIGNED) - ?i)
                     WHERE id = ?i",
                    $qty, $pid
                );
            }
        }
    }

    /* ══════════════════════════════════════════════════════════════════════
     * QUERY HELPERS — static methods other code can call
     * ══════════════════════════════════════════════════════════════════════ */

    /**
     * Get best-selling products.
     *
     * @param  int    $limit
     * @param  string $period  'all' | 'today' | 'last_days'
     * @param  int    $last_days
     * @return array
     */
    public static function get_bestsellers( int $limit = 12, ?string $period = null, ?int $last_days = null ): array {
        $db = titancart_db();
        $s  = self::settings( $db );

        $min_sales = (int) $s['sales_amount_from'];
        $period    = $period    ?? $s['period'];
        $last_days = $last_days ?? (int) $s['last_days'];

        $where = "p.status = 'active' AND p.parent_id = 0 AND p.deleted_at IS NULL AND p.sales_count >= ?i";
        $args  = [ $min_sales ];

        // KI-121 (0.2.27): storefront listing — collapse "display as a single
        // product" groups to their representative (roadmap #15). Bound-free
        // fragment, so the positional $args stay untouched.
        $where .= ' AND ' . \TitanCart\Modules\Products\ProductRepository::single_group_exclusion_sql( 'p' );

        // Period filter
        if ( $period === 'today' ) {
            $where .= " AND p.updated_at >= CURDATE()";
        } elseif ( $period === 'last_days' && $last_days > 0 ) {
            $where  .= " AND p.updated_at >= DATE_SUB(?s, INTERVAL ?i DAY)";
            $args[]  = titancart_now_utc(); // KI-24: UTC base, not MySQL-tz NOW()
            $args[]  = $last_days;
        }

        $args[] = $limit;

        $rows = $db->getArray(
            "SELECT p.id, p.slug, p.sku, p.price, p.list_price, p.sales_count,
                    p.stock_quantity, p.stock_status, p.rating_avg, p.rating_count,
                    pt.name, pt.short_desc
             FROM {$db->table('products')} p
             LEFT JOIN {$db->table('product_translations')} pt
                    ON pt.product_id = p.id AND pt.language_code = 'en'
             WHERE {$where}
             ORDER BY p.sales_count DESC
             LIMIT ?i",
            ...$args
        );

        // KI-121 (0.2.27): representative rows take the group's display name.
        $rows = \TitanCart\Modules\Products\ProductRepository::attach_group_single_display( $rows );

        return self::apply_effective_prices( self::attach_images( $db, $rows ) );
    }

    /**
     * Get "on sale" products (list_price > price by at least X%).
     *
     * @param  int $limit
     * @return array
     */
    public static function get_on_sale( int $limit = 12 ): array {
        $db = titancart_db();
        $s  = self::settings( $db );

        $min_discount = (int) $s['final_sale_from']; // e.g. 40 = 40%

        // KI-121 (0.2.27): same single-display collapse as get_bestsellers().
        $single_excl = \TitanCart\Modules\Products\ProductRepository::single_group_exclusion_sql( 'p' );

        $rows = $db->getArray(
            "SELECT p.id, p.slug, p.sku, p.price, p.list_price, p.sales_count,
                    p.stock_quantity, p.stock_status, p.rating_avg, p.rating_count,
                    pt.name, pt.short_desc,
                    ROUND((1 - p.price / p.list_price) * 100, 0) AS discount_pct
             FROM {$db->table('products')} p
             LEFT JOIN {$db->table('product_translations')} pt
                    ON pt.product_id = p.id AND pt.language_code = 'en'
             WHERE p.status = 'active'
               AND p.parent_id = 0
               AND p.deleted_at IS NULL
               AND p.list_price > 0
               AND p.list_price > p.price
               AND ROUND((1 - p.price / p.list_price) * 100, 0) >= ?i
               AND {$single_excl}
             ORDER BY discount_pct DESC
             LIMIT ?i",
            $min_discount, $limit
        );

        $rows = \TitanCart\Modules\Products\ProductRepository::attach_group_single_display( $rows );

        return self::apply_effective_prices( self::attach_images( $db, $rows ) );
    }

    /**
     * Batch-load main images for a set of products.
     *
     * product_images stores wp_attachment_id, so we resolve URLs via
     * wp_get_attachment_image_url() in PHP — not in SQL.
     */
    private static function attach_images( $db, array $rows ): array {
        if ( empty( $rows ) ) {
            return $rows;
        }

        $ids = array_column( $rows, 'id' );

        // Fetch the first image (position 0 or lowest) for each product.
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '?i' ) );
        $images = $db->getArray(
            "SELECT pi.product_id, pi.wp_attachment_id
             FROM {$db->table('product_images')} pi
             INNER JOIN (
                 SELECT product_id, MIN(position) AS min_pos
                 FROM {$db->table('product_images')}
                 WHERE product_id IN ({$placeholders})
                 GROUP BY product_id
             ) first ON first.product_id = pi.product_id AND pi.position = first.min_pos
             WHERE pi.product_id IN ({$placeholders})",
            ...array_merge( $ids, $ids )
        );

        // Build lookup: product_id → URL
        $img_map = [];
        foreach ( $images as $img ) {
            $pid = (int) $img['product_id'];
            $att = (int) $img['wp_attachment_id'];
            if ( ! isset( $img_map[ $pid ] ) && $att > 0 ) {
                $img_map[ $pid ] = [
                    'image_url'   => wp_get_attachment_image_url( $att, 'medium' ) ?: '',
                    'image_thumb' => wp_get_attachment_image_url( $att, \TitanCart\Modules\Products\ImageService::cart_thumb_size() ) ?: '',
                ];
            }
        }

        // Merge into result rows
        foreach ( $rows as &$row ) {
            $pid = (int) $row['id'];
            $row['image_url']   = $img_map[ $pid ]['image_url']   ?? '';
            $row['image_thumb'] = $img_map[ $pid ]['image_thumb'] ?? '';
        }

        return $rows;
    }

    /**
     * Decorate each product row with the catalog-effective price so the
     * Best Sellers / On Sale surfaces (homepage tabs via tct_product_card()
     * + the public REST endpoints) match the shelf / PDP / cart price set by
     * the Catalog Promotions engine (#4 / KI-08, S271).
     *
     * Contract mirrors the storefront card/PDP/StorefrontApiController (S270):
     *   price      ← effective (lowest of manual list-price sale, Bulk-Buys
     *                tier, and any matching catalog promo)
     *   list_price ← on_sale ? compare_at (the regular/struck price)
     *                        : the row's original list_price
     *
     * Behaviour-preserving when no catalog promo applies: effective_price
     * reduces to the base price and on_sale reflects any pre-existing
     * list_price sale, so the decorated values equal the raw SQL values.
     *
     * Resolved at quantity 1 for the viewing shopper's usergroups (locked
     * S269). The row's own price/list_price are passed as the ?row fast-path,
     * so no extra SELECT is issued per product. The optional discount_pct
     * (on-sale list only) is recomputed from the decorated values so the
     * public JSON stays consistent rather than reflecting the raw SQL math.
     *
     * Gated on class_exists so the extension degrades to raw prices if the
     * Promotions module is ever unavailable.
     */
    private static function apply_effective_prices( array $rows ): array {
        if ( empty( $rows )
             || ! class_exists( '\TitanCart\Modules\Promotions\CatalogPromotionService' ) ) {
            return $rows;
        }

        $svc = \TitanCart\Modules\Promotions\CatalogPromotionService::instance();

        foreach ( $rows as &$row ) {
            $pid = (int) ( $row['id'] ?? 0 );
            if ( $pid <= 0 ) {
                continue;
            }

            $base_list = (float) ( $row['list_price'] ?? 0 );

            $eff = $svc->effective_price_for_viewer( $pid, 1, [
                'price'      => (float) ( $row['price'] ?? 0 ),
                'list_price' => $base_list,
            ] );

            $row['price']      = (float) $eff['price'];
            $row['list_price'] = ( ! empty( $eff['on_sale'] ) && $eff['compare_at'] !== null )
                ? (float) $eff['compare_at']
                : $base_list;

            // Keep the optional on-sale discount_pct consistent with the
            // decorated values (the raw SQL value would otherwise go stale).
            if ( array_key_exists( 'discount_pct', $row ) ) {
                $row['discount_pct'] = ( $row['list_price'] > 0 && $row['list_price'] > $row['price'] )
                    ? round( ( 1 - ( $row['price'] / $row['list_price'] ) ) * 100 )
                    : 0;
            }
        }
        unset( $row );

        return $rows;
    }

    /* ══════════════════════════════════════════════════════════════════════
     * PUBLIC REST API — /store/bestsellers and /store/on-sale
     * ══════════════════════════════════════════════════════════════════════ */

    public static function register_routes(): void {
        // WP.org R1 🅱4: public by design — read-only catalog lists (the same
        // products the public shop pages render), consumed by storefront
        // widgets for guest visitors. Nothing customer-identifying returned.
        register_rest_route( 'titancart/v1', '/store/bestsellers', [
            'methods'             => 'GET',
            'callback'            => [ self::class, 'api_bestsellers' ],
            'permission_callback' => '__return_true', // Public by design: anonymous storefront read (public catalog data only).
        ] );

        register_rest_route( 'titancart/v1', '/store/on-sale', [
            'methods'             => 'GET',
            'callback'            => [ self::class, 'api_on_sale' ],
            'permission_callback' => '__return_true', // Public by design: anonymous storefront read (public catalog data only).
        ] );
    }

    public static function api_bestsellers( \WP_REST_Request $request ): \WP_REST_Response {
        $limit = min( 50, max( 1, (int) ( $request->get_param( 'limit' ) ?? 12 ) ) );
        return new \WP_REST_Response( self::get_bestsellers( $limit ) );
    }

    public static function api_on_sale( \WP_REST_Request $request ): \WP_REST_Response {
        $limit = min( 50, max( 1, (int) ( $request->get_param( 'limit' ) ?? 12 ) ) );
        return new \WP_REST_Response( self::get_on_sale( $limit ) );
    }

    /* ══════════════════════════════════════════════════════════════════════
     * SETTINGS
     * ══════════════════════════════════════════════════════════════════════ */

    private static function settings( $db ): array {
        $rows = $db->getArray(
            "SELECT `key`, value FROM {$db->table('settings')} WHERE section = ?s",
            'bestsellers'
        );

        $vals = [];
        foreach ( $rows as $r ) {
            $vals[ $r['key'] ] = $r['value'];
        }

        return [
            'final_sale_from'  => $vals['final_sale_from']  ?? '40',
            'sales_amount_from'=> $vals['sales_amount_from'] ?? '1',
            'period'           => $vals['period']            ?? 'all',
            'last_days'        => $vals['last_days']         ?? '3',
        ];
    }
}
