-- =========================================================================
-- CHƯƠNG 3 / LAB 5: TƯƠNG TÁC CƠ SỞ DỮ LIỆU VỚI MYSQL VÀ PDO (NÂNG CAO)
-- File khởi tạo cấu trúc bảng (Schema) và dữ liệu mẫu (Seed Data)
-- Database: labdb
-- =========================================================================

CREATE DATABASE IF NOT EXISTS `labdb` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `labdb`;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `order_items`;
DROP TABLE IF EXISTS `orders`;
DROP TABLE IF EXISTS `products`;
DROP TABLE IF EXISTS `categories`;
DROP TABLE IF EXISTS `customers`;

SET FOREIGN_KEY_CHECKS = 1;

-- 1. Bảng loại hàng (categories)
CREATE TABLE `categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Bảng sản phẩm (products)
CREATE TABLE `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,
    `description` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Bảng khách hàng (customers)
CREATE TABLE `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;

-- 4. Bảng đơn hàng (orders)
CREATE TABLE `orders` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `customer_id` INT NOT NULL,
    `order_date` DATE NOT NULL,
    `status` VARCHAR(50) NOT NULL DEFAULT 'completed',
    CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Bảng chi tiết đơn hàng (order_items)
CREATE TABLE `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,
    CONSTRAINT `fk_order_items_order` FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_order_items_product` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================================
-- DỮ LIỆU MẪU (SEED DATA)
-- =========================================================================

-- Thêm 6 loại hàng (categories)
INSERT INTO `categories` (`id`, `name`, `description`) VALUES
(1, 'Điện thoại', 'Smartphone chính hãng Apple, Samsung, Xiaomi'),
(2, 'Laptop', 'Máy tính xách tay văn phòng, đồ họa và gaming'),
(3, 'Phụ kiện', 'Cáp sạc, củ sạc, chuột, bàn phím, ốp lưng'),
(4, 'Máy tính bảng', 'iPad và máy tính bảng Android'),
(5, 'Thiết bị âm thanh', 'Tai nghe Bluetooth, loa di động'),
(6, 'Thiết bị mạng', 'Router Wi-Fi, bộ kích sóng (chưa có sản phẩm mẫu)');

-- Thêm 21 sản phẩm (products)
-- Loại 1 (Điện thoại): 6 sản phẩm (> 5 sản phẩm để thỏa Bài 03)
-- Loại 3 (Phụ kiện): 7 sản phẩm (> 5 sản phẩm để thỏa Bài 03)
-- Có 4 sản phẩm (id: 6, 17, 19, 21) chưa từng được đặt hàng để thỏa Bài 06 & Bài 12
INSERT INTO `products` (`id`, `category_id`, `name`, `price`, `stock`, `description`) VALUES
(1, 1, 'iPhone 15 Pro Max 256GB', 29990000, 15, 'Flagship Apple chip A17 Pro khung Titan'),
(2, 1, 'Samsung Galaxy S24 Ultra', 27990000, 12, 'Điện thoại AI tích hợp bút S-Pen'),
(3, 1, 'Xiaomi 14 Ultra 5G', 24990000, 8, 'Camera Leica chuyên nghiệp'),
(4, 1, 'iPhone 13 128GB', 13990000, 25, 'Điện thoại Apple hiệu năng ổn định'),
(5, 1, 'Samsung Galaxy A55 5G', 8990000, 30, 'Điện thoại tầm trung bán chạy'),
(6, 1, 'Nokia 105 4G Pro', 650000, 40, 'Điện thoại phổ thông pin bền (Chưa có đơn đặt)'),

(7, 2, 'MacBook Air M3 13 inch', 26990000, 10, 'Laptop mỏng nhẹ pin 18 giờ'),
(8, 2, 'Laptop Dell XPS 15', 38990000, 5, 'Laptop cao cấp màn hình OLED'),
(9, 2, 'ASUS ROG Strix G16', 31990000, 7, 'Laptop Gaming cấu hình mạnh'),
(10, 2, 'HP Pavilion 15', 15490000, 18, 'Laptop học tập - văn phòng'),

(11, 3, 'Củ sạc nhanh Anker 20W', 250000, 100, 'Sạc nhanh PD nhỏ gọn'),
(12, 3, 'Cáp sạc Type-C Baseus 100W', 120000, 150, 'Cáp bọc dù siêu bền'),
(13, 3, 'Chuột không dây Logitech MX Master 3S', 2290000, 25, 'Chuột công thái học chuyên nghiệp'),
(14, 3, 'Bàn phím cơ Keychron K8 Pro', 2190000, 20, 'Bàn phím cơ không dây Hotswap'),
(15, 3, 'Pin sạc dự phòng Xiaomi 10000mAh', 390000, 80, 'Sạc dự phòng 22.5W'),
(16, 3, 'Ốp lưng MagSafe iPhone 15 Pro Max', 450000, 60, 'Ốp lưng chống sốc trong suốt'),
(17, 3, 'Giá đỡ Laptop hợp kim nhôm', 320000, 45, 'Chân đế tản nhiệt (Chưa có đơn đặt)'),

(18, 4, 'iPad Air 6 M2 11 inch', 16490000, 14, 'Máy tính bảng Apple chip M2'),
(19, 4, 'Samsung Galaxy Tab S9 FE', 8490000, 11, 'Kèm bút S-Pen tiện lợi (Chưa có đơn đặt)'),

(20, 5, 'Tai nghe AirPods Pro 2 USB-C', 5690000, 22, 'Chống ồn chủ động ANC'),
(21, 5, 'Loa Bluetooth JBL Flip 6', 2890000, 16, 'Loa chống nước IP67 (Chưa có đơn đặt)');

-- Thêm 8 khách hàng (customers)
INSERT INTO `customers` (`id`, `name`, `email`, `phone`, `address`) VALUES
(1, 'Nguyễn Văn An', 'nguyenvanan@example.com', '0901234567', 'Quận 1, TP. Hồ Chí Minh'),
(2, 'Trần Thị Bích Ngọc', 'bichngoc.tran@example.com', '0912345678', 'Quận Cầu Giấy, Hà Nội'),
(3, 'Lê Hoàng Nam', 'hoangnam.le@example.com', '0987654321', 'Quận Hải Châu, Đà Nẵng'),
(4, 'Phạm Minh Tuấn', 'minhtuan.pham@example.com', '0933445566', 'TP. Thủ Đức, TP. Hồ Chí Minh'),
(5, 'Võ Thị Thanh Hà', 'thanhha.vo@example.com', '0977889900', 'Quận Ninh Kiều, Cần Thơ'),
(6, 'Đặng Quốc Khánh', 'quockhanh.dang@example.com', '0966554433', 'TP. Biên Hòa, Đồng Nai'),
(7, 'Hoàng Gia Bảo', 'giabao.hoang@example.com', '0944112233', 'TP. Nha Trang, Khánh Hòa'),
(8, 'Bùi Phương Thảo', 'phuongthao.bui@example.com', '0922334455', 'TP. Huế, Thừa Thiên Huế');

-- Thêm 10 đơn hàng (orders) trải qua nhiều ngày
INSERT INTO `orders` (`id`, `customer_id`, `order_date`, `status`) VALUES
(1, 1, '2025-02-01', 'completed'),
(2, 2, '2025-02-01', 'completed'),
(3, 3, '2025-02-02', 'completed'),
(4, 1, '2025-02-03', 'completed'),
(5, 4, '2025-02-03', 'completed'),
(6, 5, '2025-02-04', 'completed'),
(7, 6, '2025-02-05', 'completed'),
(8, 7, '2025-02-05', 'completed'),
(9, 2, '2025-02-06', 'completed'),
(10, 3, '2025-02-07', 'completed');

-- Thêm chi tiết đơn hàng (order_items)
-- Lưu ý:
-- Khách hàng 6 (Đặng Quốc Khánh, order 7) chỉ mua 2x250.000 + 2x120.000 = 740.000 VNĐ (< 1.000.000 VNĐ)
-- Khách hàng 7 (Hoàng Gia Bảo, order 8) chỉ mua 1x390.000 + 1x450.000 = 840.000 VNĐ (< 1.000.000 VNĐ)
-- Khách hàng 8 (Bùi Phương Thảo) chưa có đơn hàng nào (0 VNĐ)
-- Các khách hàng 1, 2, 3, 4, 5 đều có tổng chi tiêu > 1.000.000 VNĐ
-- Khách hàng 3 (Lê Hoàng Nam) mua số lượng sản phẩm lớn nhất (tổng 14 sản phẩm)
INSERT INTO `order_items` (`id`, `order_id`, `product_id`, `quantity`, `price`) VALUES
(1, 1, 1, 1, 29990000),   -- Order 1 (KH 1): 1 iPhone 15 Pro Max
(2, 1, 16, 2, 450000),    -- Order 1 (KH 1): 2 Ốp lưng MagSafe
(3, 2, 7, 1, 26990000),   -- Order 2 (KH 2): 1 MacBook Air M3
(4, 2, 13, 1, 2290000),   -- Order 2 (KH 2): 1 Chuột Logitech MX Master 3S
(5, 3, 11, 5, 250000),    -- Order 3 (KH 3): 5 Củ sạc Anker 20W
(6, 3, 12, 6, 120000),    -- Order 3 (KH 3): 6 Cáp sạc Baseus 100W
(7, 3, 15, 2, 390000),    -- Order 3 (KH 3): 2 Pin dự phòng Xiaomi
(8, 4, 20, 1, 5690000),   -- Order 4 (KH 1): 1 AirPods Pro 2
(9, 4, 14, 1, 2190000),   -- Order 4 (KH 1): 1 Bàn phím Keychron K8 Pro
(10, 5, 2, 1, 27990000),  -- Order 5 (KH 4): 1 Galaxy S24 Ultra
(11, 5, 8, 1, 38990000),  -- Order 5 (KH 4): 1 Laptop Dell XPS 15
(12, 6, 4, 1, 13990000),  -- Order 6 (KH 5): 1 iPhone 13 128GB
(13, 6, 5, 1, 8990000),   -- Order 6 (KH 5): 1 Galaxy A55 5G
(14, 6, 18, 1, 16490000), -- Order 6 (KH 5): 1 iPad Air 6 M2
(15, 7, 11, 2, 250000),   -- Order 7 (KH 6): 2 Củ sạc Anker (500k)
(16, 7, 12, 2, 120000),   -- Order 7 (KH 6): 2 Cáp Baseus (240k) -> Tổng KH 6 = 740.000 VNĐ
(17, 8, 15, 1, 390000),   -- Order 8 (KH 7): 1 Pin dự phòng (390k)
(18, 8, 16, 1, 450000),   -- Order 8 (KH 7): 1 Ốp lưng (450k) -> Tổng KH 7 = 840.000 VNĐ
(19, 9, 9, 1, 31990000),  -- Order 9 (KH 2): 1 ASUS ROG Strix G16
(20, 9, 10, 1, 15490000), -- Order 9 (KH 2): 1 HP Pavilion 15
(21, 10, 3, 1, 24990000); -- Order 10 (KH 3): 1 Xiaomi 14 Ultra 5G (Tổng SL của KH 3 = 5+6+2+1 = 14)