init.sql

-- Initialize test database with sample data
--
-- TODO: Consider increasing test data volume for better LLM query analysis testing.
-- Current data sizes (users: 1000, products: 500, orders: 5000) are small enough
-- that the LLM correctly approves full table scans and leading wildcard LIKE queries.
-- For testing rejection scenarios, consider:
--   - 100K+ rows in users table to trigger full scan rejection
--   - Add a "large_events" table with 1M+ rows for clear rejection cases
-- See tests/features/analysis/real_bedrock.feature for related test comments.

-- Categories
CREATE TABLE IF NOT EXISTS categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

-- Users
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    is_active BOOLEAN DEFAULT TRUE
);

-- Products
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    category_id INTEGER REFERENCES categories(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Orders
CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10, 2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    shipped_at TIMESTAMP WITH TIME ZONE
);

-- Order Items
CREATE TABLE IF NOT EXISTS order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_products_category_id ON products(category_id);

-- Insert sample data
INSERT INTO categories (name, description) VALUES
    ('Electronics', 'Electronic devices and accessories'),
    ('Clothing', 'Apparel and fashion items'),
    ('Books', 'Books and publications'),
    ('Home & Garden', 'Home improvement and garden supplies'),
    ('Sports', 'Sports equipment and gear'),
    ('Toys', 'Toys and games'),
    ('Food', 'Food and beverages')
ON CONFLICT DO NOTHING;

-- Insert 1000 users
INSERT INTO users (username, email, password_hash, is_active)
SELECT
    'user' || i,
    'user' || i || '@example.com',
    'hashed_password_' || i,
    TRUE
FROM generate_series(1, 1000) AS i
ON CONFLICT DO NOTHING;

-- Insert 500 products
INSERT INTO products (name, description, price, stock_quantity, category_id)
SELECT
    'Product ' || i,
    'Description for product ' || i,
    (random() * 1000)::decimal(10,2),
    (random() * 100)::integer,
    (random() * 6 + 1)::integer
FROM generate_series(1, 500) AS i
ON CONFLICT DO NOTHING;

-- Insert 5000 orders
INSERT INTO orders (user_id, status, total_amount, created_at)
SELECT
    (random() * 999 + 1)::integer,
    (ARRAY['pending', 'processing', 'shipped', 'delivered'])[floor(random() * 4 + 1)::integer],
    (random() * 500)::decimal(10,2),
    NOW() - (random() * 365 || ' days')::interval
FROM generate_series(1, 5000) AS i
ON CONFLICT DO NOTHING;

-- Insert 15000 order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
    (random() * 4999 + 1)::integer,
    (random() * 499 + 1)::integer,
    (random() * 5 + 1)::integer,
    (random() * 100)::decimal(10,2)
FROM generate_series(1, 15000) AS i
ON CONFLICT DO NOTHING;

-- Test table for NULL handling in masking
-- This table has nullable sensitive columns to verify mask expressions preserve NULLs
CREATE TABLE IF NOT EXISTS test_nullable_pii (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),          -- nullable email
    phone VARCHAR(20),           -- nullable phone
    notes TEXT
);

-- Insert test data with NULLs
INSERT INTO test_nullable_pii (name, email, phone, notes) VALUES
    ('Alice', 'alice@example.com', '+1-555-1234', 'Has all fields'),
    ('Bob', NULL, '+1-555-5678', 'No email'),
    ('Charlie', 'charlie@example.com', NULL, 'No phone'),
    ('Diana', NULL, NULL, 'No email or phone'),
    ('Eve', 'eve@example.com', '+1-555-9999', NULL)
ON CONFLICT DO NOTHING;

-- Views
-- Simple view exposing sensitive data (email)
CREATE OR REPLACE VIEW user_emails AS
SELECT id, username, email FROM users;

-- View joining tables with sensitive data
CREATE OR REPLACE VIEW order_summary AS
SELECT
    o.id AS order_id,
    u.username,
    u.email,
    o.total_amount,
    o.status,
    o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id;

-- View with no sensitive data (for contrast)
CREATE OR REPLACE VIEW product_catalog AS
SELECT
    p.id,
    p.name,
    p.description,
    p.price,
    p.stock_quantity,
    c.name AS category_name
FROM products p
LEFT JOIN categories c ON c.id = p.category_id;

-- Analyze tables for query planning
ANALYZE;