-- =========================================================================
-- BÀI 12: LIỆT KÊ TẤT CẢ SẢN PHẨM VÀ SỐ LẦN ĐƯỢC ĐẶT HÀNG (CHƯA ĐẶT HIỂN THỊ 0)
-- Kỹ thuật sử dụng: LEFT JOIN, COUNT(oi.id), COALESCE(SUM(oi.quantity), 0), GROUP BY
-- =========================================================================

USE `labdb`;

-- Cấu trúc bảng liên quan (Schema):
CREATE TABLE IF NOT EXISTS `categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_id` INT NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `price` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    `stock` INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_items` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT NOT NULL,
    `product_id` INT NOT NULL,
    `quantity` INT NOT NULL DEFAULT 1,
    `price` DECIMAL(15, 2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Câu truy vấn chính của Bài 12:
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    c.name AS category_name,
    p.price,
    COUNT(oi.id) AS order_times,
    COALESCE(SUM(oi.quantity), 0) AS total_quantity_ordered
FROM products p
INNER JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, c.name, p.price
ORDER BY order_times DESC, p.id ASC;