#4 - Thiết kế schema tối ưu cho hiệu năng cao

-- Bảng normalized
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Truy vấn yêu cầu JOIN
SELECT o.id, o.order_date, c.name as customer_name,
       SUM(oi.price * oi.quantity) as total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.order_date, c.name;

-- Bảng denormalized
CREATE TABLE orders_denormalized (
    id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),  -- Denormalized từ customers
    order_date DATE,
    total_amount DECIMAL(10,2),  -- Pre-calculated
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Truy vấn đơn giản hơn
SELECT id, order_date, customer_name, total_amount
FROM orders_denormalized;

-- MySQL: Partitioning theo range
CREATE TABLE orders (
    id INT NOT NULL,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION future VALUES LESS THAN MAXVALUE
);

-- PostgreSQL: Partitioning theo range
CREATE TABLE orders (
    id INT NOT NULL,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2021 PARTITION OF orders
    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE orders_2022 PARTITION OF orders
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- Thay vì một bảng lớn
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    description TEXT,
    specifications TEXT,
    images TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- Chia thành các bảng nhỏ hơn
CREATE TABLE products_core (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE products_details (
    product_id INT PRIMARY KEY,
    description TEXT,
    specifications TEXT,
    FOREIGN KEY (product_id) REFERENCES products_core(id)
);

CREATE TABLE products_media (
    product_id INT PRIMARY KEY,
    images TEXT,
    FOREIGN KEY (product_id) REFERENCES products_core(id)
);

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- Truy vấn con trực tiếp
SELECT * FROM categories WHERE parent_id = 5;

-- Truy vấn toàn bộ cây (recursive CTE)
WITH RECURSIVE category_tree AS (
    -- Base case: root categories
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case: child categories
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    lft INT NOT NULL,
    rgt INT NOT NULL,
    INDEX (lft),
    INDEX (rgt)
);

-- Truy vấn toàn bộ subtree (rất hiệu quả)
SELECT * FROM categories
WHERE lft BETWEEN
    (SELECT lft FROM categories WHERE id = 5) AND
    (SELECT rgt FROM categories WHERE id = 5)
ORDER BY lft;

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    path VARCHAR(255),
    INDEX (path)
);

-- Truy vấn toàn bộ subtree
SELECT * FROM categories WHERE path LIKE '1/5/%' OR path = '1/5';

-- PostgreSQL: Sử dụng ltree extension
CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    path ltree,
    INDEX (path)
);

-- Truy vấn subtree
SELECT * FROM categories WHERE path <@ 'root.electronics';

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE friendships (
    user_id1 INT,
    user_id2 INT,
    created_at TIMESTAMP,
    PRIMARY KEY (user_id1, user_id2),
    FOREIGN KEY (user_id1) REFERENCES users(id),
    FOREIGN KEY (user_id2) REFERENCES users(id),
    CHECK (user_id1 < user_id2)  -- Đảm bảo không có duplicate
);

-- Tìm bạn bè của user
SELECT u.*
FROM users u
JOIN friendships f ON (u.id = f.user_id1 OR u.id = f.user_id2)
WHERE (f.user_id1 = 123 OR f.user_id2 = 123) AND u.id != 123;

-- Tìm bạn chung (PostgreSQL)
WITH user1_friends AS (
    SELECT CASE WHEN user_id1 = 123 THEN user_id2 ELSE user_id1 END AS friend_id
    FROM friendships
    WHERE user_id1 = 123 OR user_id2 = 123
),
user2_friends AS (
    SELECT CASE WHEN user_id1 = 456 THEN user_id2 ELSE user_id1 END AS friend_id
    FROM friendships
    WHERE user_id1 = 456 OR user_id2 = 456
)
SELECT u.*
FROM users u
JOIN user1_friends uf1 ON u.id = uf1.friend_id
JOIN user2_friends uf2 ON u.id = uf2.friend_id;

-- PostgreSQL: Partitioning theo tháng
CREATE TABLE metrics (
    timestamp TIMESTAMP NOT NULL,
    device_id INT NOT NULL,
    temperature FLOAT,
    humidity FLOAT,
    PRIMARY KEY (timestamp, device_id)
) PARTITION BY RANGE (timestamp);

CREATE TABLE metrics_2023_01 PARTITION OF metrics
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE metrics_2023_02 PARTITION OF metrics
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- TimescaleDB extension cho PostgreSQL
CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE metrics (
    timestamp TIMESTAMP NOT NULL,
    device_id INT NOT NULL,
    temperature FLOAT,
    humidity FLOAT
);

-- Chuyển đổi thành hypertable
SELECT create_hypertable('metrics', 'timestamp');

-- Truy vấn hiệu quả
SELECT time_bucket('1 hour', timestamp) AS hour,
       AVG(temperature) AS avg_temp
FROM metrics
WHERE device_id = 123
  AND timestamp BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY hour
ORDER BY hour;

-- Bảng chi tiết
CREATE TABLE metrics_raw (
    timestamp TIMESTAMP NOT NULL,
    device_id INT NOT NULL,
    temperature FLOAT,
    humidity FLOAT,
    PRIMARY KEY (timestamp, device_id)
);

-- Bảng pre-aggregated theo giờ
CREATE TABLE metrics_hourly (
    hour TIMESTAMP NOT NULL,
    device_id INT NOT NULL,
    avg_temperature FLOAT,
    min_temperature FLOAT,
    max_temperature FLOAT,
    avg_humidity FLOAT,
    sample_count INT,
    PRIMARY KEY (hour, device_id)
);

-- Cập nhật định kỳ bằng job
INSERT INTO metrics_hourly (hour, device_id, avg_temperature, min_temperature, max_temperature, avg_humidity, sample_count)
SELECT
    DATE_TRUNC('hour', timestamp) AS hour,
    device_id,
    AVG(temperature) AS avg_temperature,
    MIN(temperature) AS min_temperature,
    MAX(temperature) AS max_temperature,
    AVG(humidity) AS avg_humidity,
    COUNT(*) AS sample_count
FROM metrics_raw
WHERE timestamp >= CURRENT_DATE - INTERVAL '1 day'
GROUP BY DATE_TRUNC('hour', timestamp), device_id
ON CONFLICT (hour, device_id) DO UPDATE
SET avg_temperature = EXCLUDED.avg_temperature,
    min_temperature = EXCLUDED.min_temperature,
    max_temperature = EXCLUDED.max_temperature,
    avg_humidity = EXCLUDED.avg_humidity,
    sample_count = EXCLUDED.sample_count;

-- PostgreSQL
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    attributes JSONB,
    INDEX idx_attributes USING GIN (attributes)
);

-- Truy vấn theo thuộc tính JSON
SELECT * FROM products
WHERE attributes @> '{"color": "red", "size": "large"}';

-- MySQL 8.0+
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    attributes JSON,
    INDEX idx_color ((CAST(attributes->>'$.color' AS CHAR(50))))
);

-- Truy vấn theo thuộc tính JSON
SELECT * FROM products
WHERE JSON_EXTRACT(attributes, '$.color') = 'red'
  AND JSON_EXTRACT(attributes, '$.size') = 'large';

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    category_id INT,
    color VARCHAR(50),      -- Extracted for frequent queries
    size VARCHAR(20),       -- Extracted for frequent queries
    other_attributes JSON,  -- Flexible attributes
    INDEX (category_id, color, size)
);

-- Bước 1: Expand - Thêm cột mới
ALTER TABLE customers ADD COLUMN contact_number VARCHAR(20);

-- Bước 2: Migrate - Chuyển dữ liệu
UPDATE customers SET contact_number = phone;
-- Cập nhật code để ghi vào cả hai cột

-- Bước 3: Contract - Loại bỏ cột cũ (sau khi đảm bảo mọi thứ hoạt động tốt)
ALTER TABLE customers DROP COLUMN phone;

// Laravel implementation với Feature Flags

class CustomerController extends Controller
{
    public function store(Request $request)
    {
        $customer = new Customer();
        $customer->name = $request->name;
        $customer->phone = $request->phone;

        // Sử dụng Laravel Feature Flags (ví dụ với package Laravel Feature Flags)
        if (Feature::active('NEW_CUSTOMER_SCHEMA')) {
            // Cách 1: Sử dụng Eloquent với schema mới
            $customer->contact_number = $request->phone;
            $customer->save();
        } else {
            // Cách 2: Sử dụng schema cũ
            $customer->save();
        }

        return response()->json(['message' => 'Customer created successfully']);
    }

    // Cách tiếp cận khác: Sử dụng Query Builder trực tiếp
    public function storeWithQueryBuilder(Request $request)
    {
        $data = [
            'name' => $request->name,
        ];

        if (config('features.new_customer_schema')) {
            // Sử dụng schema mới
            $data['contact_number'] = $request->phone;
        } else {
            // Sử dụng schema cũ
            $data['phone'] = $request->phone;
        }

        DB::table('customers')->insert($data);

        return response()->json(['message' => 'Customer created successfully']);
    }

    // Sử dụng Observer Pattern để xử lý cả hai schema
    public function storeWithObserver(Request $request)
    {
        $customer = new Customer();
        $customer->name = $request->name;
        $customer->phone = $request->phone; // Luôn lưu vào cột cũ

        // CustomerObserver sẽ tự động sao chép giá trị sang cột mới nếu feature flag được bật
        $customer->save();

        return response()->json(['message' => 'Customer created successfully']);
    }
}

// CustomerObserver.php
class CustomerObserver
{
    public function saving(Customer $customer)
    {
        if (Feature::active('NEW_CUSTOMER_SCHEMA') && isset($customer->phone)) {
            $customer->contact_number = $customer->phone;
        }
    }
}

-- Tạo view với schema cũ
CREATE OR REPLACE VIEW customers_v1 AS
SELECT id, name, contact_number AS phone
FROM customers;

-- Code cũ có thể tiếp tục sử dụng view này
SELECT * FROM customers_v1;

-- Ví dụ Flyway migration
-- V1__Create_customers_table.sql
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    phone VARCHAR(20)
);

-- V2__Add_contact_number.sql
ALTER TABLE customers ADD COLUMN contact_number VARCHAR(20);

-- V3__Migrate_phone_data.sql
UPDATE customers SET contact_number = phone WHERE contact_number IS NULL;

-- V4__Remove_phone_column.sql
ALTER TABLE customers DROP COLUMN phone;

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    price DECIMAL(10,2),
    stock INT,
    category_id INT,
    brand_id INT,
    attributes TEXT,  -- Lưu dưới dạng JSON string
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(50),
    shipping_address TEXT,
    billing_address TEXT,
    payment_method VARCHAR(50),
    shipping_method VARCHAR(50),
    subtotal DECIMAL(10,2),
    tax DECIMAL(10,2),
    shipping_cost DECIMAL(10,2),
    total DECIMAL(10,2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    product_name VARCHAR(255),
    price DECIMAL(10,2),
    quantity INT,
    subtotal DECIMAL(10,2)
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    password VARCHAR(255),
    name VARCHAR(100),
    address TEXT,
    phone VARCHAR(20),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE reviews (
    id INT PRIMARY KEY,
    product_id INT,
    user_id INT,
    rating INT,
    comment TEXT,
    created_at TIMESTAMP
);

-- Bảng core products
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    brand_id INT,
    category_id INT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    INDEX (category_id),
    INDEX (brand_id)
);

-- Bảng product variants
CREATE TABLE product_variants (
    id INT PRIMARY KEY,
    product_id INT,
    sku VARCHAR(50),
    price DECIMAL(10,2),
    stock INT,
    attributes JSONB,  -- PostgreSQL JSONB type
    INDEX (product_id),
    INDEX idx_attributes USING GIN (attributes)  -- Index cho JSON search
);

-- Bảng product categories (hierarchical)
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT NULL,
    level INT,
    path VARCHAR(255),  -- Materialized path
    INDEX (parent_id),
    INDEX (path)
);

-- Bảng addresses (normalized)
CREATE TABLE addresses (
    id INT PRIMARY KEY,
    user_id INT,
    type VARCHAR(20),  -- 'shipping' or 'billing'
    name VARCHAR(100),
    street VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(50),
    phone VARCHAR(20),
    is_default BOOLEAN,
    INDEX (user_id, type)
);

-- Bảng orders với partitioning
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(50),
    shipping_address_id INT,
    billing_address_id INT,
    payment_method_id INT,
    shipping_method_id INT,
    subtotal DECIMAL(10,2),
    tax DECIMAL(10,2),
    shipping_cost DECIMAL(10,2),
    total DECIMAL(10,2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    INDEX (user_id),
    INDEX (status),
    INDEX (created_at)
) PARTITION BY RANGE (EXTRACT(YEAR_MONTH FROM created_at));

-- Tạo partition theo tháng
CREATE TABLE orders_202301 PARTITION OF orders
    FOR VALUES FROM (202301) TO (202302);

CREATE TABLE orders_202302 PARTITION OF orders
    FOR VALUES FROM (202302) TO (202303);

-- Và tiếp tục cho các tháng khác...

-- Materialized view cho báo cáo bán hàng theo ngày
CREATE MATERIALIZED VIEW daily_sales AS
SELECT
    DATE(o.created_at) AS sale_date,
    p.category_id,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.subtotal) AS total_sales
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN product_variants pv ON oi.product_variant_id = pv.id
JOIN products p ON pv.product_id = p.id
WHERE o.status = 'completed'
GROUP BY DATE(o.created_at), p.category_id;

-- Index cho materialized view
CREATE INDEX idx_daily_sales_date ON daily_sales(sale_date);
CREATE INDEX idx_daily_sales_category ON daily_sales(category_id);

-- Refresh materialized view
REFRESH MATERIALIZED VIEW daily_sales;

-- Bảng denormalized cho trang danh sách sản phẩm
CREATE TABLE product_listings (
    id INT PRIMARY KEY,
    product_id INT,
    variant_id INT,
    name VARCHAR(255),
    price DECIMAL(10,2),
    discount_price DECIMAL(10,2),
    category_id INT,
    brand_id INT,
    average_rating DECIMAL(3,2),
    review_count INT,
    stock_status VARCHAR(20),  -- 'in_stock', 'low_stock', 'out_of_stock'
    thumbnail_url VARCHAR(255),
    is_featured BOOLEAN,
    INDEX (category_id, price),
    INDEX (brand_id, price),
    INDEX (is_featured)
);

-- Trigger hoặc background job để cập nhật bảng này
CREATE TRIGGER update_product_listings
AFTER UPDATE ON product_variants
FOR EACH ROW
BEGIN
    UPDATE product_listings
    SET price = NEW.price,
        stock_status = CASE
            WHEN NEW.stock > 10 THEN 'in_stock'
            WHEN NEW.stock > 0 THEN 'low_stock'
            ELSE 'out_of_stock'
        END
    WHERE variant_id = NEW.id;
END;