<?php

require_once BASE_PATH . '/config/database.php';
require_once BASE_PATH . '/app/models/Notification.php';

class Order
{
    private PDO $db;
    private Notification $notificationModel;

    public function __construct()
    {
        $database = new Database();
        $this->db = $database->connect();
        $this->notificationModel = new Notification();
    }

    /**
     * Tạo đơn hàng bằng transaction.
     */
    public function createFromCart(
        int $customerId,
        int $cartId,
        array $shipping,
        string $paymentMethod,
        ?string $promotionCode,
        ?string $customerNote
    ): array {

        $this->db->beginTransaction();

        try {

            // ====================================================
            // 1. LOCK CART PRODUCTS
            // ====================================================

            $sql = "
                SELECT
                    ci.product_id,
                    ci.quantity,

                    p.name,
                    p.sku,
                    p.price,
                    p.sale_price,
                    p.stock_quantity,
                    p.status

                FROM cart_items ci

                INNER JOIN products p
                    ON p.id = ci.product_id

                WHERE ci.cart_id = :cart_id

                FOR UPDATE
            ";

            $stmt = $this->db->prepare($sql);

            $stmt->execute([
                'cart_id' => $cartId
            ]);

            $items = $stmt->fetchAll();

            if (empty($items)) {
                throw new RuntimeException(
                    'Giỏ hàng đang trống.'
                );
            }

            // ====================================================
            // 2. SERVER RECALCULATES PRICE
            // ====================================================

            $subtotal = 0;

            foreach ($items as &$item) {

                if ($item['status'] !== 'active') {

                    throw new RuntimeException(
                        'Sản phẩm "'
                        . $item['name']
                        . '" hiện không thể mua.'
                    );
                }

                $quantity =
                    (int) $item['quantity'];

                $stock =
                    (int) $item['stock_quantity'];

                if ($stock < $quantity) {

                    throw new RuntimeException(
                        'Sản phẩm "'
                        . $item['name']
                        . '" chỉ còn '
                        . $stock
                        . ' sản phẩm.'
                    );
                }

                $price =
                    (
                        $item['sale_price'] !== null
                        && (float) $item['sale_price']
                            < (float) $item['price']
                    )
                    ? (float) $item['sale_price']
                    : (float) $item['price'];

                $lineTotal =
                    $price * $quantity;

                $item['current_price'] =
                    $price;

                $item['line_total'] =
                    $lineTotal;

                $subtotal +=
                    $lineTotal;
            }

            unset($item);

            // ====================================================
            // 3. PROMOTION
            // ====================================================

            $promotionId = null;
            $discountAmount = 0;

            if (
                $promotionCode !== null
                && trim($promotionCode) !== ''
            ) {

                $promotionCode =
                    strtoupper(
                        trim($promotionCode)
                    );

                $stmt = $this->db->prepare("
                    SELECT *
                    FROM promotions

                    WHERE code = :code
                      AND is_active = 1
                      AND start_at <= NOW()
                      AND end_at >= NOW()

                      AND (
                            usage_limit IS NULL
                            OR used_count < usage_limit
                      )

                    LIMIT 1

                    FOR UPDATE
                ");

                $stmt->execute([
                    'code' => $promotionCode
                ]);

                $promotion =
                    $stmt->fetch();

                if (!$promotion) {

                    throw new RuntimeException(
                        'Mã khuyến mãi không hợp lệ hoặc đã hết hạn.'
                    );
                }

                if (
                    $subtotal
                    < (float)
                    $promotion['min_order_value']
                ) {

                    throw new RuntimeException(
                        'Đơn hàng chưa đạt giá trị tối thiểu để sử dụng mã khuyến mãi.'
                    );
                }

                if (
                    $promotion['discount_type']
                    === 'percent'
                ) {

                    $discountAmount =
                        $subtotal
                        * (
                            (float)
                            $promotion['discount_value']
                            / 100
                        );

                } else {

                    $discountAmount =
                        (float)
                        $promotion['discount_value'];
                }

                if (
                    $promotion['max_discount']
                    !== null
                ) {

                    $discountAmount =
                        min(
                            $discountAmount,
                            (float)
                            $promotion['max_discount']
                        );
                }

                $discountAmount =
                    min(
                        $discountAmount,
                        $subtotal
                    );

                $promotionId =
                    (int)
                    $promotion['id'];
            }

            // ====================================================
            // 4. SHIPPING FEE
            // ====================================================

            $stmt = $this->db->query("
                SELECT
                    shipping_fee,
                    free_shipping_from

                FROM shop_settings

                WHERE id = 1

                LIMIT 1
            ");

            $shopSettings =
                $stmt->fetch();

            $shippingFee =
                (float)
                ($shopSettings['shipping_fee'] ?? 0);

            $freeShippingFrom =
                $shopSettings['free_shipping_from']
                ?? null;

            if (
                $freeShippingFrom !== null
                && $subtotal >= (float) $freeShippingFrom
            ) {

                $shippingFee = 0;
            }

            // ====================================================
            // 5. TOTAL
            // ====================================================

            $totalAmount =
                max(
                    0,
                    $subtotal
                    - $discountAmount
                    + $shippingFee
                );

            // ====================================================
            // 6. ORDER CODE
            // ====================================================

            $orderCode =
                'PS'
                . date('ymdHis')
                . strtoupper(
                    bin2hex(
                        random_bytes(2)
                    )
                );

            // ====================================================
            // 7. INSERT ORDER
            // ====================================================

            $sql = "
                INSERT INTO orders (
                    order_code,
                    customer_id,
                    promotion_id,

                    recipient_name,
                    recipient_phone,
                    shipping_address,

                    subtotal,
                    discount_amount,
                    shipping_fee,
                    total_amount,

                    payment_method,
                    payment_status,
                    status,

                    customer_note
                )
                VALUES (
                    :order_code,
                    :customer_id,
                    :promotion_id,

                    :recipient_name,
                    :recipient_phone,
                    :shipping_address,

                    :subtotal,
                    :discount_amount,
                    :shipping_fee,
                    :total_amount,

                    :payment_method,
                    'unpaid',
                    'pending',

                    :customer_note
                )
            ";

            $stmt = $this->db->prepare($sql);

            $stmt->execute([
                'order_code' => $orderCode,
                'customer_id' => $customerId,
                'promotion_id' => $promotionId,

                'recipient_name' => $shipping['recipient_name'],
                'recipient_phone' => $shipping['phone'],
                'shipping_address' => $shipping['full_address'],

                'subtotal' => $subtotal,
                'discount_amount' => $discountAmount,
                'shipping_fee' => $shippingFee,
                'total_amount' => $totalAmount,

                'payment_method' => $paymentMethod,
                'customer_note' => $customerNote
            ]);

            $orderId =
                (int)
                $this->db->lastInsertId();

            // ====================================================
            // 8. ORDER ITEMS + STOCK
            // ====================================================

            $insertItem =
                $this->db->prepare("
                    INSERT INTO order_items (
                        order_id,
                        product_id,
                        product_name,
                        sku,
                        unit_price,
                        quantity,
                        line_total
                    )
                    VALUES (
                        :order_id,
                        :product_id,
                        :product_name,
                        :sku,
                        :unit_price,
                        :quantity,
                        :line_total
                    )
                ");

            $updateStock =
                $this->db->prepare("
                    UPDATE products

                    SET
                        status = CASE
                            WHEN status != 'hidden'
                                 AND stock_quantity - :status_quantity <= 0
                            THEN 'out_of_stock'
                            ELSE status
                        END,

                        stock_quantity =
                            stock_quantity - :stock_quantity,

                        sold_quantity =
                            sold_quantity + :sold_quantity

                    WHERE id = :product_id
                      AND stock_quantity >= :minimum_stock
                ");

            foreach ($items as $item) {

                $insertItem->execute([
                    'order_id' => $orderId,
                    'product_id' => $item['product_id'],
                    'product_name' => $item['name'],
                    'sku' => $item['sku'],
                    'unit_price' => $item['current_price'],
                    'quantity' => $item['quantity'],
                    'line_total' => $item['line_total']
                ]);

                $updateStock->execute([
                    'status_quantity' => $item['quantity'],
                    'stock_quantity' => $item['quantity'],
                    'sold_quantity' => $item['quantity'],
                    'minimum_stock' => $item['quantity'],
                    'product_id' => $item['product_id']
                ]);

                if ($updateStock->rowCount() !== 1) {

                    throw new RuntimeException(
                        'Không đủ tồn kho cho sản phẩm '
                        . $item['name']
                    );
                }
            }

            // ====================================================
            // 9. STATUS LOG
            // ====================================================

            $stmt = $this->db->prepare("
                INSERT INTO order_status_logs (
                    order_id,
                    changed_by,
                    old_status,
                    new_status,
                    note
                )
                VALUES (
                    :order_id,
                    :changed_by,
                    NULL,
                    'pending',
                    'Khách hàng tạo đơn hàng'
                )
            ");

            $stmt->execute([
                'order_id' => $orderId,
                'changed_by' => $customerId
            ]);

            $this->notificationModel->create(
                $customerId,
                'order.created',
                'Đặt hàng thành công',
                'Đơn hàng #' . $orderCode . ' đã được tạo.',
                '?url=customer/order&id=' . $orderId
            );

            // ====================================================
            // 10. PROMOTION USED COUNT
            // ====================================================

            if ($promotionId !== null) {

                $stmt = $this->db->prepare("
                    UPDATE promotions

                    SET used_count =
                        used_count + 1

                    WHERE id = :id
                ");

                $stmt->execute([
                    'id' => $promotionId
                ]);
            }

            // ====================================================
            // 11. CLEAR CART
            // ====================================================

            $stmt = $this->db->prepare("
                DELETE FROM cart_items
                WHERE cart_id = :cart_id
            ");

            $stmt->execute([
                'cart_id' => $cartId
            ]);

            $this->db->commit();

            return [
                'id' => $orderId,
                'order_code' => $orderCode,
                'subtotal' => $subtotal,
                'discount_amount' => $discountAmount,
                'shipping_fee' => $shippingFee,
                'total_amount' => $totalAmount
            ];

        } catch (Throwable $e) {

            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }

            throw $e;
        }
    }

    public function findByCodeForCustomer(
        string $orderCode,
        int $customerId
    ): ?array {

        $sql = "
            SELECT *
            FROM orders

            WHERE order_code = :order_code
              AND customer_id = :customer_id

            LIMIT 1
        ";

        $stmt = $this->db->prepare($sql);

        $stmt->execute([
            'order_code' => $orderCode,
            'customer_id' => $customerId
        ]);

        $order = $stmt->fetch();

        return $order ?: null;
    }

    public function getItems(
        int $orderId
    ): array {

        $stmt = $this->db->prepare("
            SELECT *
            FROM order_items
            WHERE order_id = :order_id
            ORDER BY id ASC
        ");

        $stmt->execute([
            'order_id' => $orderId
        ]);

        return $stmt->fetchAll();
    }
public function countForCustomer(
    int $customerId,
    string $status = ''
): int {

    $params = [
        'customer_id' =>
            $customerId
    ];


    $where = "
        customer_id = :customer_id
    ";


    if ($status !== '') {

        $where .= "
            AND status = :status
        ";

        $params['status'] =
            $status;
    }


    $stmt =
        $this->db->prepare("
            SELECT COUNT(*)

            FROM orders

            WHERE {$where}
        ");


    $stmt->execute($params);


    return (int)
        $stmt->fetchColumn();
}


/**
 * Return all status counters used by the customer dashboard in one indexed
 * scan instead of issuing one COUNT query for each status.
 */
public function getStatusSummaryForCustomer(
    int $customerId
): array {

    $stmt =
        $this->db->prepare("
            SELECT
                COUNT(*) AS total,
                COALESCE(SUM(status = 'pending'), 0) AS pending,
                COALESCE(SUM(status = 'shipping'), 0) AS shipping,
                COALESCE(SUM(status = 'completed'), 0) AS completed

            FROM orders

            WHERE customer_id = :customer_id
        ");

    $stmt->execute([
        'customer_id' => $customerId
    ]);

    return $stmt->fetch()
        ?: [
            'total' => 0,
            'pending' => 0,
            'shipping' => 0,
            'completed' => 0
        ];
}


public function getForCustomer(
    int $customerId,
    string $status = '',
    int $limit = 10,
    int $offset = 0
): array {

    $params = [
        'customer_id' =>
            $customerId
    ];


    $where = "
        o.customer_id = :customer_id
    ";


    if ($status !== '') {

        $where .= "
            AND o.status = :status
        ";

        $params['status'] =
            $status;
    }


    $limit =
        max(1, $limit);

    $offset =
        max(0, $offset);


    $sql = "
        SELECT
            o.*,

            (
                SELECT COUNT(*)

                FROM order_items oi

                WHERE oi.order_id = o.id
            ) AS item_count

        FROM orders o

        WHERE {$where}

        ORDER BY o.created_at DESC

        LIMIT {$limit}
        OFFSET {$offset}
    ";


    $stmt =
        $this->db->prepare($sql);

    $stmt->execute($params);


    return $stmt->fetchAll();
}


public function getRecentForCustomer(
    int $customerId,
    int $limit = 5
): array {

    return $this->getForCustomer(
        $customerId,
        '',
        $limit,
        0
    );
}


public function findByIdForCustomer(
    int $orderId,
    int $customerId
): ?array {

    $stmt =
        $this->db->prepare("
            SELECT *

            FROM orders

            WHERE id = :id
              AND customer_id = :customer_id

            LIMIT 1
        ");


    $stmt->execute([
        'id' =>
            $orderId,

        'customer_id' =>
            $customerId
    ]);


    $order =
        $stmt->fetch();


    return $order ?: null;
}


public function getStatusLogs(
    int $orderId
): array {

    $stmt =
        $this->db->prepare("
            SELECT
                osl.*,
                u.full_name AS changed_by_name

            FROM order_status_logs osl

            LEFT JOIN users u
                ON u.id = osl.changed_by

            WHERE osl.order_id = :order_id

            ORDER BY osl.created_at ASC
        ");


    $stmt->execute([
        'order_id' =>
            $orderId
    ]);


    return $stmt->fetchAll();
}
public function cancelByCustomer(
    int $orderId,
    int $customerId,
    string $reason
): void {

    $this->db->beginTransaction();


    try {

        // ====================================================
        // LOCK ORDER
        // ====================================================

        $stmt =
            $this->db->prepare("
                SELECT *

                FROM orders

                WHERE id = :id
                  AND customer_id = :customer_id

                LIMIT 1

                FOR UPDATE
            ");


        $stmt->execute([
            'id' =>
                $orderId,

            'customer_id' =>
                $customerId
        ]);


        $order =
            $stmt->fetch();


        if (!$order) {

            throw new RuntimeException(
                'Đơn hàng không tồn tại.'
            );
        }


        if ($order['status'] !== 'pending') {

            throw new RuntimeException(
                'Chỉ đơn hàng đang chờ xác nhận mới có thể hủy.'
            );
        }


        if (
            $order['payment_status']
            === 'paid'
        ) {

            throw new RuntimeException(
                'Đơn đã thanh toán không thể tự hủy. Vui lòng liên hệ cửa hàng.'
            );
        }


        // ====================================================
        // ORDER ITEMS
        // ====================================================

        $stmt =
            $this->db->prepare("
                SELECT *

                FROM order_items

                WHERE order_id = :order_id
            ");


        $stmt->execute([
            'order_id' =>
                $orderId
        ]);


        $items =
            $stmt->fetchAll();


        // ====================================================
        // RESTORE STOCK
        // ====================================================

        $restore =
            $this->db->prepare("
                UPDATE products

                SET
                    status = CASE
                        WHEN status = 'out_of_stock'
                             AND stock_quantity + :status_quantity > 0
                        THEN 'active'
                        ELSE status
                    END,

                    stock_quantity =
                        stock_quantity + :stock_quantity,

                    sold_quantity =
                        GREATEST(
                            sold_quantity - :sold_quantity,
                            0
                        )

                WHERE id = :product_id
            ");


        foreach ($items as $item) {

            $restore->execute([
                'status_quantity' =>
                    (int)
                    $item['quantity'],

                'stock_quantity' =>
                    (int)
                    $item['quantity'],

                'sold_quantity' =>
                    (int)
                    $item['quantity'],

                'product_id' =>
                    (int)
                    $item['product_id']
            ]);
        }


        // ====================================================
        // UPDATE ORDER
        // ====================================================

        $stmt =
            $this->db->prepare("
                UPDATE orders

                SET
                    status = 'cancelled',
                    cancelled_at = NOW(),
                    customer_note =
                        CASE
                            WHEN customer_note IS NULL
                                 OR customer_note = ''
                            THEN :reason_only

                            ELSE CONCAT(
                                customer_note,
                                '\nLý do hủy: ',
                                :reason_append
                            )
                        END

                WHERE id = :id
            ");


        $stmt->execute([
            'reason_only' =>
                'Lý do hủy: ' . $reason,

            'reason_append' =>
                $reason,

            'id' =>
                $orderId
        ]);


        // ====================================================
        // STATUS LOG
        // ====================================================

        $stmt =
            $this->db->prepare("
                INSERT INTO order_status_logs (
                    order_id,
                    changed_by,
                    old_status,
                    new_status,
                    note
                )
                VALUES (
                    :order_id,
                    :changed_by,
                    'pending',
                    'cancelled',
                    :note
                )
            ");


        $stmt->execute([
            'order_id' =>
                $orderId,

            'changed_by' =>
                $customerId,

            'note' =>
                'Khách hàng hủy đơn: '
                . $reason
        ]);


        // ====================================================
        // RESTORE PROMOTION USAGE
        // ====================================================

        if (
            $order['promotion_id']
            !== null
        ) {

            $stmt =
                $this->db->prepare("
                    UPDATE promotions

                    SET used_count =
                        GREATEST(
                            used_count - 1,
                            0
                        )

                    WHERE id = :id
                ");


            $stmt->execute([
                'id' =>
                    (int)
                    $order[
                        'promotion_id'
                    ]
            ]);
        }


        $this->db->commit();

    } catch (Throwable $e) {

        if (
            $this->db->inTransaction()
        ) {

            $this->db->rollBack();
        }

        throw $e;
    }
}
public function countAll(): int
{
    return (int)
        $this->db
            ->query("
                SELECT COUNT(*)
                FROM orders
            ")
            ->fetchColumn();
}


public function countByStatus(
    string $status
): int {

    $stmt =
        $this->db->prepare("
            SELECT COUNT(*)

            FROM orders

            WHERE status = :status
        ");

    $stmt->execute([
        'status' => $status
    ]);


    return (int)
        $stmt->fetchColumn();
}


public function getRevenueToday(): float
{
    $stmt =
        $this->db->query("
            SELECT
                COALESCE(
                    SUM(total_amount),
                    0
                )

            FROM orders

            WHERE status = 'completed'

              AND completed_at >= CURDATE()
              AND completed_at < CURDATE() + INTERVAL 1 DAY
        ");


    return (float)
        $stmt->fetchColumn();
}


public function getRevenueThisMonth(): float
{
    $stmt =
        $this->db->query("
            SELECT
                COALESCE(
                    SUM(total_amount),
                    0
                )

            FROM orders

            WHERE status = 'completed'

              AND completed_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
              AND completed_at < DATE_FORMAT(CURDATE(), '%Y-%m-01')
                  + INTERVAL 1 MONTH
        ");


    return (float)
        $stmt->fetchColumn();
}


public function getRecentOrders(
    int $limit = 8
): array {

    $limit =
        max(1, $limit);


    $sql = "
        SELECT
            o.*,
            u.full_name AS customer_name,
            u.phone AS customer_phone

        FROM orders o

        INNER JOIN users u
            ON u.id = o.customer_id

        ORDER BY o.created_at DESC

        LIMIT {$limit}
    ";


    return $this->db
        ->query($sql)
        ->fetchAll();
}
public function countForOwner(
    string $search = '',
    string $status = ''
): int {

    $params = [];

    $where = '1 = 1';


    if ($search !== '') {

        $where .= "
            AND (
                o.order_code LIKE :search_code
                OR o.recipient_name LIKE :search_recipient_name
                OR o.recipient_phone LIKE :search_recipient_phone
                OR u.full_name LIKE :search_customer_name
            )
        ";

        $searchValue = '%' . $search . '%';

        $params['search_code'] = $searchValue;
        $params['search_recipient_name'] = $searchValue;
        $params['search_recipient_phone'] = $searchValue;
        $params['search_customer_name'] = $searchValue;
    }


    if ($status !== '') {

        $where .= "
            AND o.status = :status
        ";

        $params['status'] =
            $status;
    }


    $stmt =
        $this->db->prepare("
            SELECT COUNT(*)

            FROM orders o

            INNER JOIN users u
                ON u.id = o.customer_id

            WHERE {$where}
        ");


    $stmt->execute($params);


    return (int)
        $stmt->fetchColumn();
}
public function getForOwner(
    string $search = '',
    string $status = '',
    int $limit = 15,
    int $offset = 0
): array {

    $params = [];

    $where = '1 = 1';


    if ($search !== '') {

        $where .= "
            AND (
                o.order_code LIKE :search_code
                OR o.recipient_name LIKE :search_recipient_name
                OR o.recipient_phone LIKE :search_recipient_phone
                OR u.full_name LIKE :search_customer_name
            )
        ";

        $searchValue = '%' . $search . '%';

        $params['search_code'] = $searchValue;
        $params['search_recipient_name'] = $searchValue;
        $params['search_recipient_phone'] = $searchValue;
        $params['search_customer_name'] = $searchValue;
    }


    if ($status !== '') {

        $where .= "
            AND o.status = :status
        ";

        $params['status'] =
            $status;
    }


    $limit =
        max(1, $limit);

    $offset =
        max(0, $offset);


    $sql = "
        SELECT
            o.*,

            u.full_name AS customer_name,
            u.phone AS customer_phone,

            (
                SELECT COUNT(*)

                FROM order_items oi

                WHERE oi.order_id = o.id
            ) AS item_count

        FROM orders o

        INNER JOIN users u
            ON u.id = o.customer_id

        WHERE {$where}

        ORDER BY o.created_at DESC

        LIMIT {$limit}
        OFFSET {$offset}
    ";


    $stmt =
        $this->db->prepare($sql);

    $stmt->execute($params);


    return $stmt->fetchAll();
}
public function findForOwner(
    int $orderId
): ?array {

    $stmt =
        $this->db->prepare("
            SELECT
                o.*,

                u.full_name AS customer_name,
                u.phone AS customer_phone,
                u.email AS customer_email

            FROM orders o

            INNER JOIN users u
                ON u.id = o.customer_id

            WHERE o.id = :id

            LIMIT 1
        ");


    $stmt->execute([
        'id' => $orderId
    ]);


    $order =
        $stmt->fetch();


    return $order ?: null;
}public function updateStatusByOwner(
    int $orderId,
    int $ownerId,
    string $newStatus,
    ?string $note = null
): void {

    $allowedTransitions = [

        'pending' => [
            'confirmed',
            'cancelled'
        ],

        'confirmed' => [
            'packing',
            'cancelled'
        ],

        'packing' => [
            'shipping',
            'cancelled'
        ],

        'shipping' => [
            'completed'
        ],

        'completed' => [],

        'cancelled' => []
    ];


    $this->db->beginTransaction();


    try {

        $stmt =
            $this->db->prepare("
                SELECT *

                FROM orders

                WHERE id = :id

                LIMIT 1

                FOR UPDATE
            ");


        $stmt->execute([
            'id' => $orderId
        ]);


        $order =
            $stmt->fetch();


        if (!$order) {

            throw new RuntimeException(
                'Đơn hàng không tồn tại.'
            );
        }


        $oldStatus =
            $order['status'];


        if ($oldStatus === $newStatus) {

            throw new RuntimeException(
                'Đơn hàng đã ở trạng thái này.'
            );
        }


        if (
            !isset(
                $allowedTransitions[
                    $oldStatus
                ]
            )

            || !in_array(
                $newStatus,
                $allowedTransitions[
                    $oldStatus
                ],
                true
            )
        ) {

            throw new RuntimeException(
                'Không thể chuyển từ trạng thái '
                . $oldStatus
                . ' sang '
                . $newStatus
                . '.'
            );
        }


        // ====================================================
        // CANCEL → RESTORE STOCK
        // ====================================================

        if ($newStatus === 'cancelled') {

            if (
                $order['payment_status']
                === 'paid'
            ) {

                throw new RuntimeException(
                    'Đơn đã thanh toán. Cần xử lý hoàn tiền trước khi hủy.'
                );
            }


            $stmt =
                $this->db->prepare("
                    SELECT *

                    FROM order_items

                    WHERE order_id = :order_id
                ");


            $stmt->execute([
                'order_id' =>
                    $orderId
            ]);


            $items =
                $stmt->fetchAll();


            $restoreStock =
                $this->db->prepare("
                    UPDATE products

                    SET
                        status = CASE
                            WHEN status = 'out_of_stock'
                                 AND stock_quantity + :status_quantity > 0
                            THEN 'active'
                            ELSE status
                        END,

                        stock_quantity =
                            stock_quantity
                            + :stock_quantity,

                        sold_quantity =
                            GREATEST(
                                sold_quantity
                                - :sold_quantity,
                                0
                            )

                    WHERE id = :product_id
                ");


            foreach ($items as $item) {

                $restoreStock->execute([
                    'status_quantity' =>
                        (int)
                        $item['quantity'],

                    'stock_quantity' =>
                        (int)
                        $item['quantity'],

                    'sold_quantity' =>
                        (int)
                        $item['quantity'],

                    'product_id' =>
                        (int)
                        $item['product_id']
                ]);
            }


            if (
                $order['promotion_id']
                !== null
            ) {

                $stmt =
                    $this->db->prepare("
                        UPDATE promotions

                        SET used_count =
                            GREATEST(
                                used_count - 1,
                                0
                            )

                        WHERE id = :id
                    ");


                $stmt->execute([
                    'id' =>
                        (int)
                        $order['promotion_id']
                ]);
            }
        }


        // ====================================================
        // UPDATE ORDER
        // ====================================================

        $confirmedAt = null;
        $completedAt = null;
        $cancelledAt = null;


        if (
            $newStatus === 'confirmed'
        ) {
            $confirmedAt =
                date('Y-m-d H:i:s');
        }


        if (
            $newStatus === 'completed'
        ) {
            $completedAt =
                date('Y-m-d H:i:s');
        }


        if (
            $newStatus === 'cancelled'
        ) {
            $cancelledAt =
                date('Y-m-d H:i:s');
        }


        $sql = "
            UPDATE orders

            SET
                status = :status,
                owner_note = :owner_note
        ";


        if ($confirmedAt !== null) {

            $sql .= ",
                confirmed_at = :confirmed_at
            ";
        }


        if ($completedAt !== null) {

            $sql .= ",
                completed_at = :completed_at
            ";
        }


        if ($cancelledAt !== null) {

            $sql .= ",
                cancelled_at = :cancelled_at
            ";
        }


        /*
         * COD hoàn thành:
         * coi như khách đã thanh toán khi nhận hàng.
         */
        if (
            $newStatus === 'completed'
            && $order['payment_method']
                === 'cod'
        ) {

            $sql .= ",
                payment_status = 'paid'
            ";
        }


        $sql .= "
            WHERE id = :id
        ";


        $params = [

            'status' =>
                $newStatus,

            'owner_note' =>
                $note ?: $order['owner_note'],

            'id' =>
                $orderId
        ];


        if ($confirmedAt !== null) {

            $params['confirmed_at'] =
                $confirmedAt;
        }


        if ($completedAt !== null) {

            $params['completed_at'] =
                $completedAt;
        }


        if ($cancelledAt !== null) {

            $params['cancelled_at'] =
                $cancelledAt;
        }


        $stmt =
            $this->db->prepare($sql);

        $stmt->execute($params);


        // ====================================================
        // LOG
        // ====================================================

        $stmt =
            $this->db->prepare("
                INSERT INTO order_status_logs (
                    order_id,
                    changed_by,
                    old_status,
                    new_status,
                    note
                )
                VALUES (
                    :order_id,
                    :changed_by,
                    :old_status,
                    :new_status,
                    :note
                )
            ");


        $stmt->execute([
            'order_id' =>
                $orderId,

            'changed_by' =>
                $ownerId,

            'old_status' =>
                $oldStatus,

            'new_status' =>
                $newStatus,

            'note' =>
                $note
        ]);


        $notificationMessages = [
            'confirmed' => [
                'order.confirmed',
                'Đơn hàng đã được xác nhận',
                'Đơn hàng #' . $order['order_code'] . ' đã được xác nhận.'
            ],
            'packing' => [
                'order.packing',
                'Đơn hàng đang được đóng gói',
                'Đơn hàng #' . $order['order_code'] . ' đang được đóng gói.'
            ],
            'shipping' => [
                'order.shipping',
                'Đơn hàng đang giao',
                'Đơn hàng #' . $order['order_code'] . ' đang được giao.'
            ],
            'completed' => [
                'order.completed',
                'Đơn hàng đã hoàn thành',
                'Đơn hàng #' . $order['order_code'] . ' đã hoàn thành.'
            ],
            'cancelled' => [
                'order.cancelled',
                'Đơn hàng đã bị hủy',
                'Đơn hàng #' . $order['order_code'] . ' đã bị hủy bởi cửa hàng.'
            ]
        ];

        if (isset($notificationMessages[$newStatus])) {
            [$type, $title, $message] = $notificationMessages[$newStatus];
            $this->notificationModel->create(
                (int) $order['customer_id'],
                $type,
                $title,
                $message,
                '?url=customer/order&id=' . $orderId
            );
        }


        $this->db->commit();


    } catch (Throwable $e) {

        if (
            $this->db->inTransaction()
        ) {

            $this->db->rollBack();
        }


        throw $e;
    }
}public function markPaidByOwner(
    int $orderId,
    int $ownerId
): void {

    $this->db->beginTransaction();


    try {

        $stmt =
            $this->db->prepare("
                SELECT *

                FROM orders

                WHERE id = :id

                LIMIT 1

                FOR UPDATE
            ");


        $stmt->execute([
            'id' => $orderId
        ]);


        $order =
            $stmt->fetch();


        if (!$order) {

            throw new RuntimeException(
                'Đơn hàng không tồn tại.'
            );
        }


        if (
            $order['status']
            === 'cancelled'
        ) {

            throw new RuntimeException(
                'Không thể xác nhận thanh toán cho đơn đã hủy.'
            );
        }


        if (
            $order['payment_status']
            === 'paid'
        ) {

            throw new RuntimeException(
                'Đơn hàng đã được xác nhận thanh toán.'
            );
        }


        $stmt =
            $this->db->prepare("
                UPDATE orders

                SET payment_status = 'paid'

                WHERE id = :id
            ");


        $stmt->execute([
            'id' => $orderId
        ]);


        $stmt =
            $this->db->prepare("
                INSERT INTO order_status_logs (
                    order_id,
                    changed_by,
                    old_status,
                    new_status,
                    note
                )
                VALUES (
                    :order_id,
                    :changed_by,
                    :old_status,
                    :new_status,
                    'Owner xác nhận đã nhận thanh toán'
                )
            ");


        $stmt->execute([
            'order_id' =>
                $orderId,

            'changed_by' =>
                $ownerId,

            'old_status' =>
                $order['status'],

            'new_status' =>
                $order['status']
        ]);


        $this->db->commit();


    } catch (Throwable $e) {

        if (
            $this->db->inTransaction()
        ) {

            $this->db->rollBack();
        }


        throw $e;
    }
}
}
