-- Create products table with realistic data types
CREATE TABLE lorem_products (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2),
    category VARCHAR(100),
    status VARCHAR(50) DEFAULT 'draft',
    rating DECIMAL(3,2) CHECK (rating >= 0 AND rating <= 5),
    views INTEGER DEFAULT 0,
    in_stock BOOLEAN DEFAULT true,
    sku VARCHAR(50) UNIQUE,
    quantity INTEGER DEFAULT 0,
    date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert lorem ipsum data
INSERT INTO lorem_products (title, description, price, category, status, rating, views, in_stock, sku, quantity, date_created, last_updated) VALUES
('Lorem ipsum dolor sit amet', 'Consectetur adipiscing elit sed do eiusmod tempor incididunt ut labore et dolore magna aliqua', 29.99, 'Technology', 'active', 4.5, 1247, true, 'LOR-001', 45, '2024-01-15 10:30:00', '2024-01-20 14:22:00'),
('Ut enim ad minim veniam', 'Quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat', 45.50, 'Design', 'inactive', 3.8, 892, false, 'LOR-002', 0, '2024-01-16 09:15:00', '2024-01-19 16:45:00'),
('Duis aute irure dolor', 'In reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur', 12.75, 'Development', 'active', 4.2, 2156, true, 'LOR-003', 128, '2024-01-17 11:20:00', '2024-01-21 13:10:00'),
('Excepteur sint occaecat', 'Cupidatat non proident sunt in culpa qui officia deserunt mollit anim', 78.25, 'Marketing', 'pending', 4.7, 567, true, 'LOR-004', 23, '2024-01-18 08:45:00', '2024-01-18 08:45:00'),
('Sed ut perspiciatis unde', 'Omnis iste natus error sit voluptatem accusantium doloremque laudantium', 156.00, 'Business', 'active', 3.9, 1834, false, 'LOR-005', 0, '2024-01-19 12:00:00', '2024-01-22 15:30:00'),
('Totam rem aperiam eaque', 'Ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta', 89.99, 'Research', 'draft', 4.1, 445, true, 'LOR-006', 67, '2024-01-20 14:15:00', '2024-01-20 14:15:00'),
('Nemo enim ipsam voluptatem', 'Quia voluptas sit aspernatur aut odit aut fugit sed quia consequuntur magni', 234.50, 'Education', 'active', 4.6, 3421, true, 'LOR-007', 89, '2024-01-21 11:30:00', '2024-01-23 09:20:00'),
('Neque porro quisquam est', 'Qui dolorem ipsum quia dolor sit amet consectetur adipisci velit', 67.30, 'Health', 'inactive', 3.4, 234, false, 'LOR-008', 0, '2024-01-22 16:45:00', '2024-01-22 16:45:00'),
('Ut aliquip ex ea commodo', 'Consequat duis aute irure dolor in reprehenderit in voluptate velit esse', 198.75, 'Finance', 'active', 4.8, 2987, true, 'LOR-009', 156, '2024-01-23 13:20:00', '2024-01-24 10:15:00'),
('At vero eos et accusamus', 'Et iusto odio dignissimos ducimus qui blanditiis praesentium voluptatum', 43.20, 'Science', 'review', 4.0, 1567, true, 'LOR-010', 34, '2024-01-24 09:00:00', '2024-01-24 09:00:00');

-- Create indexes for better performance
CREATE INDEX idx_lorem_products_category ON lorem_products(category);
CREATE INDEX idx_lorem_products_status ON lorem_products(status);
CREATE INDEX idx_lorem_products_price ON lorem_products(price);
CREATE INDEX idx_lorem_products_rating ON lorem_products(rating);

-- Sample queries
-- SELECT * FROM lorem_products WHERE status = 'active' ORDER BY rating DESC;
-- SELECT category, COUNT(*), AVG(price) FROM lorem_products GROUP BY category;
-- SELECT * FROM lorem_products WHERE price BETWEEN 50 AND 200 AND in_stock = true;