-- Create table with constraints
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Insert with conflict handling
INSERT INTO users (email, name)
VALUES ('john@example.com', 'John')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        customer_id,
        SUM(amount) as total_amount
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date), customer_id
)
SELECT
    month,
    customer_id,
    total_amount,
    RANK() OVER (PARTITION BY month ORDER BY total_amount DESC) as rank,
    LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY month) as prev_month
FROM monthly_sales;

SELECT
    id,
    data->>'name' as name,
    data->'tags' as tags,
    jsonb_array_length(data->'tags') as tag_count
FROM products
WHERE data @> '{"featured": true}'
  AND data ? 'category';

-- Partial index for filtered queries
CREATE INDEX CONCURRENTLY idx_active_users
ON users (email) WHERE is_active = true;

-- Composite index for covering queries
CREATE INDEX idx_orders_customer
ON orders (customer_id, created_at DESC)
INCLUDE (status, amount);

-- GIN index for JSONB
CREATE INDEX idx_products_data
ON products USING GIN (data);

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 as level
    FROM categories WHERE parent_id IS NULL

    UNION ALL

    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;