CREATE TABLE orders (
    -- Základní sloupce
    id INT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10,2),
    discount_percent DECIMAL(5,2),
    created_at TIMESTAMP,
    
    -- Generated columns
    -- Finanční výpočty
    subtotal DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    discount_amount DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price * discount_percent / 100) VIRTUAL,
    final_price DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price * (1 - discount_percent / 100)) STORED,
    
    -- Časové údaje
    year INT GENERATED ALWAYS AS (YEAR(created_at)) VIRTUAL,
    quarter INT GENERATED ALWAYS AS (QUARTER(created_at)) VIRTUAL,
    is_weekend BOOLEAN GENERATED ALWAYS AS (DAYOFWEEK(created_at) IN (1, 7)) VIRTUAL,
    
    -- Kategorizace
    price_category VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN unit_price < 100 THEN 'Budget'
            WHEN unit_price < 500 THEN 'Standard'
            ELSE 'Premium'
        END
    ) VIRTUAL,
    
    -- Indexy na generated columns
    INDEX idx_year_quarter (year, quarter),
    INDEX idx_final_price (final_price),
    INDEX idx_price_category (price_category)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    birth_date DATE,
    salary DECIMAL(10,2),
    department VARCHAR(50),
    
    -- Text manipulace
    full_name VARCHAR(101) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    email_domain VARCHAR(50) GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL,
    initials CHAR(2) GENERATED ALWAYS AS (CONCAT(LEFT(first_name, 1), LEFT(last_name, 1))) STORED,
    
    -- Věkové a finanční výpočty
    age INT GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE)) VIRTUAL,
    age_category VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE) < 25 THEN 'Junior'
            WHEN TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE) < 35 THEN 'Mid'
            ELSE 'Senior'
        END
    ) VIRTUAL,
    
    salary_band CHAR(1) GENERATED ALWAYS AS (
        CASE 
            WHEN salary < 30000 THEN 'A'
            WHEN salary < 50000 THEN 'B'
            WHEN salary < 80000 THEN 'C'
            ELSE 'D'
        END
    ) STORED,
    
    -- Indexy
    INDEX idx_email_domain (email_domain),
    INDEX idx_age_category (age_category),
    INDEX idx_salary_band (salary_band)
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    price DECIMAL(10,2),
    stock INT,
    category VARCHAR(50),
    
    -- Stavové sloupce
    stock_status VARCHAR(20) GENERATED ALWAYS AS (
        CASE 
            WHEN stock = 0 THEN 'Out of Stock'
            WHEN stock < 10 THEN 'Low Stock'
            WHEN stock < 50 THEN 'In Stock'
            ELSE 'Well Stocked'
        END
    ) VIRTUAL,
    
    -- Vyhledávání
    search_text TEXT GENERATED ALWAYS AS (
        CONCAT_WS(' ', name, description, category)
    ) STORED,
    
    -- Příznaky
    is_premium BOOLEAN GENERATED ALWAYS AS (price > 1000) VIRTUAL,
    needs_restock BOOLEAN GENERATED ALWAYS AS (stock < 20) VIRTUAL,
    
    -- Indexy
    INDEX idx_stock_status (stock_status),
    FULLTEXT INDEX idx_search (search_text)
);