

-- Create Products table
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    product_description TEXT,
    category_name VARCHAR(100) NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    stock_quantity INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CHECK (unit_price >= 0),
    CHECK (stock_quantity >= 0)
);

-- Begin transaction for data insertion
BEGIN TRANSACTION;

-- Insert sample products
INSERT INTO products (product_id, product_name, product_description, category_name, unit_price, stock_quantity) VALUES (101, 'Wireless Mouse', 'Ergonomic wireless mouse with 2.4GHz connectivity', 'Electronics', 29.99, 150);
INSERT INTO products (product_id, product_name, product_description, category_name, unit_price, stock_quantity) VALUES (102, 'Mechanical Keyboard', 'RGB backlit mechanical gaming keyboard', 'Electronics', 89.99, 75);
INSERT INTO products (product_id, product_name, product_description, category_name, unit_price, stock_quantity) VALUES (103, 'USB-C Cable', 'High-speed USB-C charging and data cable', 'Accessories', 12.99, 500);
INSERT INTO products (product_id, product_name, product_description, category_name, unit_price, stock_quantity) VALUES (104, 'Monitor Stand', 'Adjustable ergonomic monitor stand with storage', 'Furniture', 49.99, 100);
INSERT INTO products (product_id, product_name, product_description, category_name, unit_price, stock_quantity) VALUES (105, 'Desk Lamp', 'LED desk lamp with adjustable brightness', 'Furniture', 34.99, 200);

COMMIT;

-- Another query with table aliases
SELECT
    product_catalog.product_name,
    product_catalog.unit_price,
    product_catalog.stock_quantity,
    category_info.category_name
FROM public.products AS product_catalog
INNER JOIN categories AS category_info
    ON product_catalog.category_id = category_info.category_id
WHERE product_catalog.stock_quantity > 0;

-- Drop temporary tables (will be removed in aggressive compression)
ALTER TABLE products ADD COLUMN is_featured BOOLEAN DEFAULT FALSE;
