<?php
/**
 * ShipmentManager — Fulfillment business logic.
 *
 * Manages shipments: create from order items, update tracking/status,
 * calculate shipped quantities per order item.
 *
 * DB tables:
 *   tc_shipments      — id, order_id, shipping_method_id, status_id,
 *                        tracking_number, carrier, comments, shipped_at,
 *                        created_at, updated_at
 *   tc_shipment_items — id, shipment_id, order_item_id, product_id, quantity
 *
 * @package TitanCart\Modules\Shipping
 */

namespace TitanCart\Modules\Shipping;

use TitanCart\DB\Database;

defined( 'ABSPATH' ) || exit;

class ShipmentManager {

    private Database $db;

    public function __construct() {
        $this->db = titancart_db();
    }

    // ══════════════════════════════════════════════════════════════════════════
    // LIST
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Paginated list of shipments, filterable by order, status, carrier, date.
     */
    public function list_shipments( array $args = [] ): array {
        $page     = max( 1, (int) ( $args['page']     ?? 1 ) );
        $per_page = min( 100, max( 1, (int) ( $args['per_page'] ?? 20 ) ) );
        $offset   = ( $page - 1 ) * $per_page;

        $where  = [];
        $params = [];

        if ( ! empty( $args['order_id'] ) ) {
            $where[]  = 's.order_id = ?i';
            $params[] = (int) $args['order_id'];
        }
        if ( ! empty( $args['status_id'] ) ) {
            $where[]  = 's.status_id = ?i';
            $params[] = (int) $args['status_id'];
        }
        if ( ! empty( $args['carrier'] ) ) {
            $where[]  = 's.carrier = ?s';
            $params[] = $args['carrier'];
        }
        if ( ! empty( $args['search'] ) ) {
            $like     = '%' . $this->db->escape_like( $args['search'] ) . '%';
            $where[]  = '(s.tracking_number LIKE ?s OR o.order_number LIKE ?s)';
            $params[] = $like;
            $params[] = $like;
        }
        if ( ! empty( $args['date_from'] ) ) {
            $where[]  = 's.created_at >= ?s';
            $params[] = $args['date_from'] . ' 00:00:00';
        }
        if ( ! empty( $args['date_to'] ) ) {
            $where[]  = 's.created_at <= ?s';
            $params[] = $args['date_to'] . ' 23:59:59';
        }

        $where_sql = $where ? 'WHERE ' . implode( ' AND ', $where ) : '';

        $count_params = $params;
        $total = (int) $this->db->getField(
            "SELECT COUNT(*)
             FROM {$this->db->table('shipments')} s
             LEFT JOIN {$this->db->table('orders')} o ON o.id = s.order_id
             {$where_sql}",
            ...$count_params
        );

        $list_params   = $params;
        $list_params[] = $per_page;
        $list_params[] = $offset;

        $rows = $this->db->getArray(
            "SELECT s.*,
                    o.order_number,
                    o.billing_first_name, o.billing_last_name, o.billing_email,
                    COALESCE(os.handle, '') AS status_handle,
                    COALESCE(os.color,  '#71797E') AS status_color,
                    COALESCE(ost.name,  os.handle, 'Pending') AS status_label,
                    COALESCE(smt.name,  '') AS shipping_method_name
             FROM {$this->db->table('shipments')} s
             LEFT JOIN {$this->db->table('orders')} o ON o.id = s.order_id
             LEFT JOIN {$this->db->table('order_statuses')} os ON os.id = s.status_id
             LEFT JOIN {$this->db->table('order_status_translations')} ost
                 ON ost.status_id = os.id AND ost.language_code = 'en'
             LEFT JOIN {$this->db->table('shipping_method_translations')} smt
                 ON smt.method_id = s.shipping_method_id AND smt.language_code = 'en'
             {$where_sql}
             ORDER BY s.created_at DESC
             LIMIT ?i OFFSET ?i",
            ...$list_params
        );

        // Attach item counts.
        if ( ! empty( $rows ) ) {
            $sids = array_column( $rows, 'id' );
            $counts = $this->db->getHash(
                "SELECT shipment_id, SUM(quantity) AS total_qty
                 FROM {$this->db->table('shipment_items')}
                 WHERE shipment_id IN (?a)
                 GROUP BY shipment_id",
                $sids
            );
            foreach ( $rows as &$r ) {
                $r['item_count'] = (int) ( $counts[ $r['id'] ] ?? 0 );
            }
            unset( $r );
        }

        return [
            'shipments' => $rows ?: [],
            'total'     => $total,
            'pages'     => $total > 0 ? (int) ceil( $total / $per_page ) : 1,
            'page'      => $page,
            'per_page'  => $per_page,
        ];
    }

    // ══════════════════════════════════════════════════════════════════════════
    // GET SINGLE
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get a single shipment with items.
     */
    public function get_shipment( int $id ): ?array {
        $row = $this->db->getRow(
            "SELECT s.*,
                    o.order_number,
                    o.billing_first_name, o.billing_last_name, o.billing_email,
                    COALESCE(os.handle, '') AS status_handle,
                    COALESCE(os.color,  '#71797E') AS status_color,
                    COALESCE(ost.name,  os.handle, 'Pending') AS status_label,
                    COALESCE(smt.name, '') AS shipping_method_name
             FROM {$this->db->table('shipments')} s
             LEFT JOIN {$this->db->table('orders')} o ON o.id = s.order_id
             LEFT JOIN {$this->db->table('order_statuses')} os ON os.id = s.status_id
             LEFT JOIN {$this->db->table('order_status_translations')} ost
                 ON ost.status_id = os.id AND ost.language_code = 'en'
             LEFT JOIN {$this->db->table('shipping_method_translations')} smt
                 ON smt.method_id = s.shipping_method_id AND smt.language_code = 'en'
             WHERE s.id = ?i LIMIT 1",
            $id
        );

        if ( ! $row ) {
            return null;
        }

        $row['items'] = $this->get_items( $id );
        return $row;
    }

    /**
     * Get shipment items with product names.
     */
    public function get_items( int $shipment_id ): array {
        return $this->db->getArray(
            "SELECT si.*,
                    oi.name AS product_name, oi.sku AS product_code,
                    oi.quantity AS ordered_qty
             FROM {$this->db->table('shipment_items')} si
             LEFT JOIN {$this->db->table('order_items')} oi ON oi.id = si.order_item_id
             WHERE si.shipment_id = ?i
             ORDER BY si.id ASC",
            $shipment_id
        );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // GET SHIPMENTS BY ORDER
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get all shipments for a given order.
     */
    public function get_by_order( int $order_id ): array {
        $rows = $this->db->getArray(
            "SELECT s.*,
                    COALESCE(os.handle, '') AS status_handle,
                    COALESCE(os.color,  '#71797E') AS status_color,
                    COALESCE(ost.name,  os.handle, 'Pending') AS status_label
             FROM {$this->db->table('shipments')} s
             LEFT JOIN {$this->db->table('order_statuses')} os ON os.id = s.status_id
             LEFT JOIN {$this->db->table('order_status_translations')} ost
                 ON ost.status_id = os.id AND ost.language_code = 'en'
             WHERE s.order_id = ?i
             ORDER BY s.created_at ASC",
            $order_id
        );

        foreach ( $rows as &$r ) {
            $r['items'] = $this->get_items( (int) $r['id'] );
        }
        unset( $r );

        return $rows;
    }

    // ══════════════════════════════════════════════════════════════════════════
    // SHIPPED QUANTITIES
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get how many of each order item have already been shipped.
     * Returns [ order_item_id => shipped_qty ].
     */
    public function get_shipped_quantities( int $order_id ): array {
        return $this->db->getHash(
            "SELECT si.order_item_id, SUM(si.quantity) AS shipped
             FROM {$this->db->table('shipment_items')} si
             INNER JOIN {$this->db->table('shipments')} s ON s.id = si.shipment_id
             WHERE s.order_id = ?i
             GROUP BY si.order_item_id",
            $order_id
        );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // CREATE
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Create a shipment from an order.
     *
     * @param array $data {
     *   order_id: int (required),
     *   items: array of {order_item_id, quantity} (required, at least one),
     *   tracking_number?: string,
     *   carrier?: string,
     *   shipping_method_id?: int,
     *   status_id?: int,
     *   comments?: string,
     *   shipped_at?: string (DATETIME),
     * }
     * @return int|false  Shipment ID or false on failure.
     */
    public function create( array $data ): int|false {
        $order_id = (int) ( $data['order_id'] ?? 0 );
        $items    = $data['items'] ?? [];

        if ( ! $order_id || empty( $items ) ) {
            return false;
        }

        // Validate quantities against unshipped.
        $shipped = $this->get_shipped_quantities( $order_id );

        // Get order items to validate.
        $order_items = $this->db->getArray(
            "SELECT id, quantity FROM {$this->db->table('order_items')}
             WHERE order_id = ?i",
            $order_id
        );
        $order_item_map = [];
        foreach ( $order_items as $oi ) {
            $order_item_map[ (int) $oi['id'] ] = (int) $oi['quantity'];
        }

        $valid_items = [];
        foreach ( $items as $item ) {
            $oi_id = (int) ( $item['order_item_id'] ?? 0 );
            $qty   = (int) ( $item['quantity'] ?? 0 );

            if ( $oi_id <= 0 || $qty <= 0 ) {
                continue;
            }

            $ordered  = $order_item_map[ $oi_id ] ?? 0;
            $already  = (int) ( $shipped[ $oi_id ] ?? 0 );
            $remaining = $ordered - $already;

            // Clamp qty to remaining unshipped.
            $qty = min( $qty, $remaining );
            if ( $qty <= 0 ) {
                continue;
            }

            $valid_items[] = [
                'order_item_id' => $oi_id,
                'product_id'    => $this->db->getField(
                    "SELECT product_id FROM {$this->db->table('order_items')} WHERE id = ?i",
                    $oi_id
                ) ?: 0,
                'quantity'      => $qty,
            ];
        }

        if ( empty( $valid_items ) ) {
            return false;
        }

        // Get default fulfillment status.
        $status_id = (int) ( $data['status_id'] ?? 0 );
        if ( ! $status_id ) {
            $status_id = (int) $this->db->getField(
                "SELECT id FROM {$this->db->table('order_statuses')}
                 WHERE type = 'fulfillment' AND is_default = 1 LIMIT 1"
            ) ?: 0;
        }

        $shipment_id = $this->db->insert( 'shipments', [
            'order_id'           => $order_id,
            'shipping_method_id' => ! empty( $data['shipping_method_id'] ) ? (int) $data['shipping_method_id'] : null,
            'status_id'          => $status_id ?: null,
            'tracking_number'    => sanitize_text_field( $data['tracking_number'] ?? '' ),
            'carrier'            => sanitize_text_field( $data['carrier'] ?? '' ),
            'comments'           => wp_kses_post( $data['comments'] ?? '' ),
            'shipped_at'         => $this->local_to_utc( $data['shipped_at'] ?? null ),
        ] );

        if ( ! $shipment_id ) {
            return false;
        }

        foreach ( $valid_items as $vi ) {
            $this->db->insert( 'shipment_items', [
                'shipment_id'   => $shipment_id,
                'order_item_id' => $vi['order_item_id'],
                'product_id'    => $vi['product_id'],
                'quantity'      => $vi['quantity'],
            ] );
        }

        return $shipment_id;
    }

    /**
     * Convert an admin-entered ship date/time — a store-local wall-clock value from
     * a <input type="datetime-local"> — into a UTC 'Y-m-d H:i:s' string for storage.
     * This matches the UTC-in-DB convention every display layer assumes (Pattern #146:
     * server emits UTC, PHP titancart_format_date / admin tcFmtDate convert UTC→local). Before
     * S285 shipped_at was stored verbatim as local wall-clock, so the display layers
     * shifted it back ~tz-offset hours and early-morning times rolled to the prior day.
     * Empty / unparseable input → null (clears the ship date).
     */
    private function local_to_utc( $local ): ?string {
        $local = trim( (string) ( $local ?? '' ) );
        if ( $local === '' ) {
            return null;
        }
        try {
            // Interpret the admin-entered wall-clock in the STORE's display timezone
            // (titancart_timezone() = appearance.timezone, e.g. America/New_York) — NOT
            // wp_timezone() (WP core site tz, which is UTC here). Both display layers
            // (customer titancart_format_date, admin browser render) treat the stored value as
            // UTC and render in the store tz, so the entry must be store-tz→UTC to
            // round-trip. Falls back to WP site tz when appearance.timezone is empty.
            $dt = new \DateTime( $local, \titancart_timezone() );
            $dt->setTimezone( new \DateTimeZone( 'UTC' ) );
            return $dt->format( 'Y-m-d H:i:s' );
        } catch ( \Exception $e ) {
            return null;
        }
    }

    /**
     * Convert a stored UTC ship datetime back to a store-local value formatted for a
     * <input type="datetime-local"> ('Y-m-d\TH:i'), so the admin edit form round-trips
     * what was entered instead of showing a tz-shifted value. Empty/unparseable → ''.
     */
    public function utc_to_input( $utc ): string {
        $utc = trim( (string) ( $utc ?? '' ) );
        if ( $utc === '' ) {
            return '';
        }
        try {
            // Convert stored UTC back to the store's display timezone (titancart_timezone(),
            // matching the customer + admin renderers) for the datetime-local picker.
            $dt = new \DateTime( $utc, new \DateTimeZone( 'UTC' ) );
            $dt->setTimezone( \titancart_timezone() );
            return $dt->format( 'Y-m-d\TH:i' );
        } catch ( \Exception $e ) {
            return '';
        }
    }

    // ══════════════════════════════════════════════════════════════════════════
    // UPDATE
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Update shipment fields (tracking, carrier, status, comments).
     */
    public function update( int $id, array $data ): bool {
        $allowed = [ 'tracking_number', 'carrier', 'status_id', 'comments', 'shipped_at', 'shipping_method_id' ];
        $fields  = [];

        foreach ( $allowed as $key ) {
            if ( array_key_exists( $key, $data ) ) {
                $fields[ $key ] = $data[ $key ];
            }
        }

        if ( empty( $fields ) ) {
            return false;
        }

        if ( isset( $fields['tracking_number'] ) ) {
            $fields['tracking_number'] = sanitize_text_field( $fields['tracking_number'] );
        }
        if ( isset( $fields['carrier'] ) ) {
            $fields['carrier'] = sanitize_text_field( $fields['carrier'] );
        }
        if ( isset( $fields['comments'] ) ) {
            $fields['comments'] = wp_kses_post( $fields['comments'] );
        }
        if ( array_key_exists( 'shipped_at', $fields ) ) {
            // S285: store the admin-entered local wall-clock as UTC (Pattern #146).
            $fields['shipped_at'] = $this->local_to_utc( $fields['shipped_at'] );
        }

        $this->db->update( 'shipments', $fields, [ 'id' => $id ] );
        return true;
    }

    // ══════════════════════════════════════════════════════════════════════════
    // DELETE
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Delete a shipment and its items.
     */
    public function delete( int $id ): bool {
        $this->db->delete( 'shipment_items', [ 'shipment_id' => $id ] );
        $this->db->delete( 'shipments', [ 'id' => $id ] );
        return true;
    }

    // ══════════════════════════════════════════════════════════════════════════
    // NEEDS-FULFILLMENT QUEUE  (roadmap #11, option b)
    //
    // A *derived* view of shippable orders that still have unshipped items.
    // Deliberately does NOT auto-INSERT placeholder tc_shipments rows — an
    // order simply appears in this queue until the owner acts on it (via the
    // existing S282 "Create Shipment" picker), at which point its shipped items
    // count up and it drops out of the queue on its own. No bookkeeping rows,
    // no meaningless "pending" shipments.
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Order-status handles that count as "fulfillable" — an order in one of
     * these statuses, with shippable items still to ship, appears in the
     * Needs-Fulfillment queue.
     *
     * Defaults to 'processing' (OrderManager::STATUS_PROCESSING) — the same
     * status the S282 Create-Shipment order picker filters to, and the
     * canonical "paid, ready to fulfill" state. 'pending' is unpaid, 'on_hold'
     * is deliberately paused, and 'completed'/'cancelled'/'refunded'/'failed'
     * are terminal, so none of those are surfaced by default. Filterable so a
     * store can widen the set (e.g. add 'on_hold') without a code change.
     *
     * @return string[]  Non-empty, sanitised status handles.
     */
    public function fulfillable_order_statuses(): array {
        $statuses = apply_filters( 'titancart_fulfillable_order_statuses', [ 'processing' ] );

        $out = [];
        foreach ( (array) $statuses as $handle ) {
            $handle = sanitize_key( (string) $handle );
            if ( $handle !== '' ) {
                $out[] = $handle;
            }
        }

        return $out ?: [ 'processing' ];
    }

    /**
     * Per-order remaining-shippable-units aggregate, shared by the queue list
     * and its counts so all three read identical semantics.
     *
     * Counts ONLY order items whose product currently requires shipping
     * (tc_products.requires_shipping = 1 — the canonical shipping gate, the
     * same column CheckoutManager::cart_needs_shipping() reads). For each such
     * item: remaining = ordered quantity − quantity already shipped, where
     * shipped = SUM(shipment_items.quantity) across the order's shipments (the
     * same basis as get_shipped_quantities()). Clamped at 0 (defensive against
     * over-shipping) and CAST to SIGNED to avoid UNSIGNED subtraction wrap.
     * HAVING keeps only orders with at least one shippable unit still to ship,
     * so digital-only orders (every item requires_shipping = 0 — e.g. the
     * all-digital marketplace) never appear.
     *
     * @return string  A subquery selecting (order_id, remaining_units).
     */
    private function needs_fulfillment_agg_sql(): string {
        return "SELECT oi.order_id,
                       SUM( GREATEST( CAST(oi.quantity AS SIGNED) - CAST(COALESCE(sh.shipped, 0) AS SIGNED), 0 ) ) AS remaining_units
                FROM {$this->db->table('order_items')} oi
                INNER JOIN {$this->db->table('products')} p
                    ON p.id = oi.product_id AND p.requires_shipping = 1
                LEFT JOIN (
                    SELECT si.order_item_id, SUM(si.quantity) AS shipped
                    FROM {$this->db->table('shipment_items')} si
                    GROUP BY si.order_item_id
                ) sh ON sh.order_item_id = oi.id
                GROUP BY oi.order_id
                HAVING remaining_units > 0";
    }

    /**
     * Derived "Needs Fulfillment" queue: shippable orders awaiting fulfillment.
     *
     * An order qualifies when ALL of: not soft-deleted; its status handle is in
     * fulfillable_order_statuses(); and it has at least one shippable item with
     * unshipped quantity remaining (see needs_fulfillment_agg_sql()).
     *
     * @param array $args { page?:int, per_page?:int }
     * @return array { orders: array, total:int, pages:int, page:int, per_page:int }
     */
    public function list_needs_fulfillment( array $args = [] ): array {
        $page     = max( 1, (int) ( $args['page']     ?? 1 ) );
        $per_page = min( 100, max( 1, (int) ( $args['per_page'] ?? 20 ) ) );
        $offset   = ( $page - 1 ) * $per_page;

        $statuses = $this->fulfillable_order_statuses();
        if ( empty( $statuses ) ) {
            return [ 'orders' => [], 'total' => 0, 'pages' => 1, 'page' => $page, 'per_page' => $per_page ];
        }

        $agg = $this->needs_fulfillment_agg_sql();

        $total = (int) $this->db->getField(
            "SELECT COUNT(*)
             FROM {$this->db->table('orders')} o
             INNER JOIN {$this->db->table('order_statuses')} os ON os.id = o.status_id
             INNER JOIN ( {$agg} ) agg ON agg.order_id = o.id
             WHERE o.deleted_at IS NULL
               AND os.handle IN (?a)",
            $statuses
        );

        $rows = $this->db->getArray(
            "SELECT o.id, o.order_number, o.total, o.currency, o.created_at,
                    o.billing_first_name, o.billing_last_name, o.billing_email,
                    COALESCE(os.handle, '')       AS status_handle,
                    COALESCE(os.color,  '#71797E') AS status_color,
                    COALESCE(ost.name,  os.handle) AS status_label,
                    agg.remaining_units
             FROM {$this->db->table('orders')} o
             INNER JOIN {$this->db->table('order_statuses')} os ON os.id = o.status_id
             LEFT JOIN {$this->db->table('order_status_translations')} ost
                 ON ost.status_id = os.id AND ost.language_code = 'en'
             INNER JOIN ( {$agg} ) agg ON agg.order_id = o.id
             WHERE o.deleted_at IS NULL
               AND os.handle IN (?a)
             ORDER BY o.created_at ASC
             LIMIT ?i OFFSET ?i",
            $statuses, $per_page, $offset
        );

        foreach ( $rows as &$r ) {
            $r['remaining_units'] = (int) $r['remaining_units'];
            $r['total']           = (float) $r['total'];
        }
        unset( $r );

        return [
            'orders'   => $rows ?: [],
            'total'    => $total,
            'pages'    => $total > 0 ? (int) ceil( $total / $per_page ) : 1,
            'page'     => $page,
            'per_page' => $per_page,
        ];
    }

    /**
     * Count of orders currently awaiting fulfillment (for the queue tab badge).
     * Identical qualifying logic to list_needs_fulfillment().
     */
    public function count_needs_fulfillment(): int {
        $statuses = $this->fulfillable_order_statuses();
        if ( empty( $statuses ) ) {
            return 0;
        }

        $agg = $this->needs_fulfillment_agg_sql();

        return (int) $this->db->getField(
            "SELECT COUNT(*)
             FROM {$this->db->table('orders')} o
             INNER JOIN {$this->db->table('order_statuses')} os ON os.id = o.status_id
             INNER JOIN ( {$agg} ) agg ON agg.order_id = o.id
             WHERE o.deleted_at IS NULL
               AND os.handle IN (?a)",
            $statuses
        );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // FULFILLMENT STATUSES
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get all fulfillment-type statuses.
     */
    public function get_fulfillment_statuses(): array {
        return $this->db->getArray(
            "SELECT os.*, COALESCE(ost.name, os.handle) AS label
             FROM {$this->db->table('order_statuses')} os
             LEFT JOIN {$this->db->table('order_status_translations')} ost
                 ON ost.status_id = os.id AND ost.language_code = 'en'
             WHERE os.type = 'fulfillment'
             ORDER BY os.position ASC"
        );
    }

    /**
     * Get carriers list (from shipping methods that are realtime type).
     */
    public function get_carriers(): array {
        return [
            [ 'code' => 'usps',   'name' => 'USPS' ],
            [ 'code' => 'ups',    'name' => 'UPS' ],
            [ 'code' => 'fedex',  'name' => 'FedEx' ],
            [ 'code' => 'dhl',    'name' => 'DHL' ],
            [ 'code' => 'other',  'name' => 'Other' ],
        ];
    }

    /**
     * Get tracking URL for a carrier + tracking number.
     */
    public function get_tracking_url( string $carrier, string $tracking ): string {
        if ( ! $tracking ) {
            return '';
        }
        return match ( strtolower( $carrier ) ) {
            'usps'  => 'https://tools.usps.com/go/TrackConfirmAction?tLabels=' . urlencode( $tracking ),
            'ups'   => 'https://www.ups.com/track?tracknum=' . urlencode( $tracking ),
            'fedex' => 'https://www.fedex.com/fedextrack/?trknbr=' . urlencode( $tracking ),
            'dhl'   => 'https://www.dhl.com/en/express/tracking.html?AWB=' . urlencode( $tracking ),
            default => '',
        };
    }
}
