-- =========================================================================
-- BÀI 07: TÌM THÔNG TIN KHÁCH HÀNG MUA NHIỀU SỐ LƯỢNG SẢN PHẨM NHẤT
-- Kỹ thuật sử dụng: SUM(oi.quantity), GROUP BY, ORDER BY DESC, LIMIT 1
-- =========================================================================

USE `labdb`;

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

CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `customer_id` INT NOT NULL,
    `order_date` DATE NOT NULL,
    `status` VARCHAR(50) NOT NULL DEFAULT 'completed'
) 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 07:
SELECT 
    c.id AS customer_id,
    c.name AS customer_name,
    c.email,
    c.phone,
    c.address,
    COUNT(DISTINCT o.id) AS total_orders,
    SUM(oi.quantity) AS total_quantity_bought,
    SUM(oi.quantity * oi.price) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name, c.email, c.phone, c.address
ORDER BY total_quantity_bought DESC
LIMIT 1;