-- Core entities
-- Users table is deliberately minimal: authentication concerns
-- belong in a separate auth service in most production systems
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()  -- TIMESTAMPTZ, not TIMESTAMP: always store with timezone
);

CREATE TABLE addresses (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    type VARCHAR(20), -- 'shipping', 'billing'
    street TEXT NOT NULL,
    city VARCHAR(100),
    country VARCHAR(2),  -- ISO 3166-1 alpha-2: enforce at app level or CHECK constraint
    postal_code VARCHAR(20),
    is_default BOOLEAN DEFAULT false
    -- Pitfall: is_default has no DB-level uniqueness per user+type.
    -- Enforce "only one default per user per type" in application logic
    -- or use a partial unique index:
    -- CREATE UNIQUE INDEX ON addresses(user_id, type) WHERE is_default = true;
);

-- Product catalog
-- Categories use ltree for efficient ancestor/descendant queries.
-- Think of ltree like a file path: 'Electronics.Phones.Smartphones'
-- This avoids expensive recursive CTEs for "find all products in
-- Electronics and subcategories" queries.
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES categories(id),
    path ltree  -- requires: CREATE EXTENSION ltree;
    -- Example path: 'electronics.phones.smartphones'
    -- Query all children: WHERE path <@ 'electronics'
);

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,  -- SKU = Stock Keeping Unit, the merchant's identifier
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category_id INT REFERENCES categories(id),
    base_price DECIMAL(10,2) NOT NULL,  -- DECIMAL, not FLOAT: never use floating point for money
    attributes JSONB DEFAULT '{}',  -- Flexible attributes (color options, material, etc.)
    -- Why JSONB here? Product attributes vary wildly across categories.
    -- A T-shirt has "size" and "color"; a laptop has "RAM" and "screen size".
    -- A fully normalized EAV (Entity-Attribute-Value) model is a performance nightmare.
    -- JSONB gives you flexible schema with GIN index support for fast lookups.
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE product_variants (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT REFERENCES products(id),
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100),  -- "Red, Large"
    price_modifier DECIMAL(10,2) DEFAULT 0,
    attributes JSONB NOT NULL  -- {"color": "red", "size": "L"}
);

-- Inventory
-- This is a separate table from product_variants on purpose.
-- Inventory changes MUCH more frequently than product data.
-- Separating them means inventory updates don't cause lock
-- contention on the heavily-read product_variants table.
-- This is the "hot table / cold table" separation pattern.
CREATE TABLE inventory (
    variant_id BIGINT PRIMARY KEY REFERENCES product_variants(id),
    quantity INT NOT NULL DEFAULT 0,    -- Total physical stock
    reserved INT NOT NULL DEFAULT 0,    -- Reserved by in-progress checkouts
    -- Available = quantity - reserved
    CHECK (quantity >= 0),
    CHECK (reserved >= 0),
    CHECK (reserved <= quantity)  -- Cannot reserve more than exists
);

-- Orders
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending',
    total DECIMAL(12,2) NOT NULL,
    shipping_address_id BIGINT REFERENCES addresses(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT REFERENCES orders(id),
    variant_id BIGINT REFERENCES product_variants(id),
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    CHECK (quantity > 0)
);

-- Reviews
CREATE TABLE reviews (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT REFERENCES products(id),
    user_id BIGINT REFERENCES users(id),
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    body TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(product_id, user_id)
);

-- Product listing with category tree
WITH RECURSIVE cat_tree AS (
    SELECT id, name, parent_id, ARRAY[name] AS path
    FROM categories WHERE id = $category_id
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.path || c.name
    FROM categories c
    JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT p.*, array_agg(pv.id) AS variant_ids
FROM products p
JOIN cat_tree ct ON p.category_id = ct.id
LEFT JOIN product_variants pv ON pv.product_id = p.id
GROUP BY p.id;

-- Checkout with inventory reservation
-- This is the critical section: two customers must not both "win"
-- the last item. FOR UPDATE acquires a row-level lock, which is like
-- putting a "hold" sign on the shelf while you walk to the register.
BEGIN;
SELECT * FROM inventory 
WHERE variant_id = $variant_id 
FOR UPDATE;  -- Row-level lock: blocks other transactions on THIS row only

UPDATE inventory 
SET reserved = reserved + $quantity
WHERE variant_id = $variant_id
  AND (quantity - reserved) >= $quantity;

-- Check affected rows, if 0 then out of stock
COMMIT;

-- Product with aggregated reviews
SELECT 
    p.*,
    COUNT(r.id) AS review_count,
    AVG(r.rating) AS avg_rating,
    jsonb_build_object(
        '5', COUNT(*) FILTER (WHERE r.rating = 5),
        '4', COUNT(*) FILTER (WHERE r.rating = 4),
        '3', COUNT(*) FILTER (WHERE r.rating = 3),
        '2', COUNT(*) FILTER (WHERE r.rating = 2),
        '1', COUNT(*) FILTER (WHERE r.rating = 1)
    ) AS rating_distribution
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
WHERE p.id = $product_id
GROUP BY p.id;

┌─────────────────────────────────────────────────────────────────────────────┐
│                    E-COMMERCE SCALING                                        │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   Read Path (90% of traffic):                                               │
│   ┌─────────────┐     ┌─────────────┐     ┌─────────────┐                  │
│   │   CDN       │────▶│   Redis     │────▶│   Replica   │                  │
│   │ (static)    │     │ (products)  │     │  (queries)  │                  │
│   └─────────────┘     └─────────────┘     └─────────────┘                  │
│                                                                              │
│   Write Path (10% of traffic):                                              │
│   ┌─────────────┐     ┌─────────────┐                                       │
│   │    App      │────▶│   Primary   │                                       │
│   │  (orders)   │     │  (writes)   │                                       │
│   └─────────────┘     └─────────────┘                                       │
│                                                                              │
│   Indexing:                                                                  │
│   • products(category_id, created_at DESC) - listings                       │
│   • products USING GIN(attributes) - attribute search                       │
│   • inventory(variant_id) - stock checks                                    │
│   • orders(user_id, created_at DESC) - order history                        │
│                                                                              │
│   Partitioning:                                                              │
│   • orders by created_at (monthly) - historical data                        │
│   • reviews by product_id hash - even distribution                          │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

-- Time-series events with partitioning
-- Partitioning is non-negotiable for time-series at scale.
-- Without it, deleting 30-day-old data requires a massive DELETE
-- that generates WAL, bloats the table, and tanks performance.
-- With partitioning, you just DROP the old partition: instant, zero I/O.
CREATE TABLE events (
    id BIGSERIAL,
    event_time TIMESTAMPTZ NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    user_id BIGINT,
    properties JSONB,
    -- Note: event_time is FIRST in the primary key. This is required
    -- because partition keys must be part of the PK, and putting it
    -- first gives us efficient range queries on time.
    PRIMARY KEY (event_time, id)
) PARTITION BY RANGE (event_time);

-- Create monthly partitions
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
-- ... more partitions

-- Pre-aggregated summaries
CREATE TABLE event_stats_hourly (
    hour TIMESTAMPTZ NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    count BIGINT DEFAULT 0,
    unique_users BIGINT DEFAULT 0,
    PRIMARY KEY (hour, event_type)
);

CREATE TABLE event_stats_daily (
    date DATE NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    count BIGINT DEFAULT 0,
    unique_users BIGINT DEFAULT 0,
    properties_agg JSONB,  -- aggregated metrics
    PRIMARY KEY (date, event_type)
);

-- HyperLogLog for unique counts (requires extension)
-- HLL is a probabilistic data structure that counts distinct values
-- using ~1.2KB of memory regardless of cardinality. The trick:
-- you CAN'T merge COUNT(DISTINCT) across time buckets (counting
-- 100 unique users per hour doesn't tell you how many unique users
-- per day, because users overlap). HLL sketches CAN be merged.
-- Error rate: ~2%. For dashboards, this is perfectly acceptable.
CREATE EXTENSION IF NOT EXISTS hll;

ALTER TABLE event_stats_hourly 
ADD COLUMN user_hll hll DEFAULT hll_empty();

-- Batch insert with conflict handling
INSERT INTO events (event_time, event_type, user_id, properties)
VALUES 
    ($1, $2, $3, $4),
    ($5, $6, $7, $8),
    -- ... batch of 1000
ON CONFLICT DO NOTHING;

-- Continuous aggregation (run every hour)
INSERT INTO event_stats_hourly (hour, event_type, count, unique_users, user_hll)
SELECT 
    date_trunc('hour', event_time) AS hour,
    event_type,
    COUNT(*) AS count,
    COUNT(DISTINCT user_id) AS unique_users,
    hll_add_agg(hll_hash_bigint(user_id)) AS user_hll
FROM events
WHERE event_time >= $last_processed_hour
  AND event_time < $current_hour
GROUP BY 1, 2
ON CONFLICT (hour, event_type) DO UPDATE SET
    count = event_stats_hourly.count + EXCLUDED.count,
    unique_users = EXCLUDED.unique_users,
    user_hll = hll_union(event_stats_hourly.user_hll, EXCLUDED.user_hll);

-- Dashboard query (uses pre-aggregates)
SELECT 
    date_trunc('day', hour) AS date,
    event_type,
    SUM(count) AS total_events,
    hll_cardinality(hll_union_agg(user_hll)) AS unique_users
FROM event_stats_hourly
WHERE hour >= NOW() - INTERVAL '7 days'
GROUP BY 1, 2
ORDER BY 1, 2;

-- Drill-down to raw events
SELECT 
    event_time,
    event_type,
    properties
FROM events
WHERE event_time >= $start AND event_time < $end
  AND event_type = $type
ORDER BY event_time DESC
LIMIT 100;

-- Drop old partitions (automated)
-- This is the magic of partitioning for time-series: deleting
-- 30 days of data is O(1) -- just drop the table. No vacuum needed.
-- Compare this to DELETE FROM events WHERE event_time < '2023-02-01'
-- which would scan millions of rows, generate massive WAL, and leave
-- behind dead tuples that autovacuum would struggle to clean.
DROP TABLE events_2023_01;  -- Instant, no locking

-- Compress old data with TimescaleDB (optional)
-- Or move to columnar storage (Citus columnar)
-- Columnar storage can achieve 10-20x compression for analytics data
-- because columns within a block tend to have similar values.
SELECT alter_table_set_access_method('events_2023_12', 'columnar');

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE follows (
    follower_id BIGINT NOT NULL REFERENCES users(id),
    following_id BIGINT NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (follower_id, following_id)
);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    author_id BIGINT NOT NULL REFERENCES users(id),
    content TEXT NOT NULL,
    media_urls TEXT[],
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Fan-out timeline (for users with < 10K followers)
-- This is the "inbox" model: when someone you follow posts,
-- a copy is placed in YOUR timeline. Like a mail carrier delivering
-- a letter to every subscriber's mailbox.
-- Trade-off: writes are expensive (one row per follower), but reads
-- are a single index scan on (user_id, created_at) -- extremely fast.
CREATE TABLE timelines (
    user_id BIGINT NOT NULL,
    post_id BIGINT NOT NULL REFERENCES posts(id),
    author_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    -- Composite PK: user_id first for "my feed" queries,
    -- created_at DESC for chronological ordering,
    -- post_id for uniqueness tie-breaking
    PRIMARY KEY (user_id, created_at DESC, post_id)
);

-- Celebrity posts (for users with > 10K followers)
-- NOT fanned out -- merged at read time instead.
-- If a celebrity with 10M followers posts, we do NOT write 10M timeline rows.
-- Instead, at feed-read time, we fetch their latest posts and merge them
-- with the pre-materialized timeline. This is the "pull" model for celebrities.
CREATE INDEX idx_posts_author_time ON posts(author_id, created_at DESC);

-- On new post from regular user (< 10K followers)
INSERT INTO timelines (user_id, post_id, author_id, created_at)
SELECT 
    follower_id,
    $post_id,
    $author_id,
    $created_at
FROM follows
WHERE following_id = $author_id;

-- Async worker for large fan-outs
-- Use message queue (RabbitMQ, SQS) for batching

-- Get user's celebrity followings
WITH celebrities AS (
    SELECT following_id 
    FROM follows f
    JOIN user_stats us ON us.user_id = f.following_id
    WHERE f.follower_id = $user_id 
      AND us.follower_count > 10000
),
-- Get pre-materialized timeline
materialized AS (
    SELECT post_id, author_id, created_at
    FROM timelines
    WHERE user_id = $user_id
      AND created_at > NOW() - INTERVAL '7 days'
    ORDER BY created_at DESC
    LIMIT 100
),
-- Get celebrity posts
celebrity_posts AS (
    SELECT id AS post_id, author_id, created_at
    FROM posts
    WHERE author_id IN (SELECT following_id FROM celebrities)
      AND created_at > NOW() - INTERVAL '7 days'
    ORDER BY created_at DESC
    LIMIT 100
)
-- Merge and return
SELECT p.*
FROM posts p
JOIN (
    SELECT * FROM materialized
    UNION ALL
    SELECT * FROM celebrity_posts
) feed ON feed.post_id = p.id
ORDER BY feed.created_at DESC
LIMIT 20;

-- Enable RLS
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    plan VARCHAR(20) DEFAULT 'free',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    email VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'member',
    UNIQUE(tenant_id, email)
);

CREATE TABLE projects (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    project_id BIGINT REFERENCES projects(id),
    title VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'open',
    assignee_id BIGINT REFERENCES users(id)
);

-- Row Level Security
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_projects ON projects
    USING (tenant_id = current_setting('app.tenant_id')::UUID);

CREATE POLICY tenant_isolation_tasks ON tasks
    USING (tenant_id = current_setting('app.tenant_id')::UUID);

-- Set tenant context (in application)
-- SET app.tenant_id = 'uuid-here';

# Application middleware
# CRITICAL: This is the security boundary. If you forget to set the
# tenant context, RLS policies default to blocking all rows (safe).
# But if you set the WRONG tenant ID, that user sees another tenant's data.
# Always derive tenant_id from the authenticated session, never from
# user-supplied input like query parameters or request headers.
def set_tenant_context(tenant_id):
    """Set tenant context for RLS"""
    with get_db_connection() as conn:
        # Use parameterized query to prevent SQL injection in the SET command
        conn.execute("SET app.tenant_id = %s", (str(tenant_id),))
        yield conn
        conn.execute("RESET app.tenant_id")  # Clean up: never leak context to next request

# PgBouncer with tenant routing (advanced)
# Route large tenants to dedicated pools
# Pitfall: PgBouncer in transaction mode reuses connections across tenants.
# You MUST set tenant context at the start of every transaction, not just
# at connection acquisition, because the connection may have been used
# by a different tenant in the previous transaction.

-- Test: Ensure tenant A can't see tenant B's data
SET app.tenant_id = 'tenant-a-uuid';

SELECT * FROM projects;  -- Should only see tenant A

-- Try to access tenant B (should fail silently - return 0 rows)
SELECT * FROM projects WHERE tenant_id = 'tenant-b-uuid';
-- Returns 0 rows due to RLS

-- Bypass for admin operations
SET ROLE admin_role;  -- Must have BYPASSRLS
SELECT * FROM projects;  -- Sees all tenants

-- Event store (append-only -- NEVER update or delete events)
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,  -- Global ordering: critical for subscription processing
    stream_id UUID NOT NULL,   -- Groups related events (e.g., all events for order-123)
    stream_type VARCHAR(100) NOT NULL,  -- 'Order', 'User', etc.
    event_type VARCHAR(100) NOT NULL,   -- 'OrderCreated', 'ItemAdded'
    event_data JSONB NOT NULL,          -- The event payload
    metadata JSONB DEFAULT '{}',        -- Correlation IDs, causation IDs, user who triggered it
    version INT NOT NULL,               -- Per-stream version number
    created_at TIMESTAMPTZ DEFAULT NOW(),
    -- This UNIQUE constraint is your optimistic concurrency control.
    -- Two concurrent transactions trying to append version 4 to the
    -- same stream: one wins, one gets a unique violation. The loser
    -- must reload state, re-apply business logic, and retry.
    -- This is how you get consistency WITHOUT pessimistic locking.
    UNIQUE(stream_id, version)
);

-- Example events
INSERT INTO events (stream_id, stream_type, event_type, event_data, version)
VALUES 
    ('order-123', 'Order', 'OrderCreated', 
     '{"customer_id": 456, "items": []}', 1),
    ('order-123', 'Order', 'ItemAdded', 
     '{"product_id": 789, "quantity": 2}', 2),
    ('order-123', 'Order', 'OrderSubmitted', 
     '{"submitted_at": "2024-01-15T10:30:00Z"}', 3);

-- Projection: Materialized current state
CREATE TABLE order_projections (
    order_id UUID PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    items JSONB NOT NULL DEFAULT '[]',
    status VARCHAR(20) NOT NULL,
    total DECIMAL(12,2),
    last_event_id BIGINT NOT NULL,  -- For replay
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Subscription checkpoints
CREATE TABLE subscription_checkpoints (
    subscription_id VARCHAR(100) PRIMARY KEY,
    last_processed_id BIGINT NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Rebuild projection from events
CREATE OR REPLACE FUNCTION rebuild_order_projection(p_order_id UUID)
RETURNS void AS $$
DECLARE
    evt RECORD;
    current_state JSONB := '{}';
BEGIN
    -- Delete existing projection
    DELETE FROM order_projections WHERE order_id = p_order_id;
    
    -- Replay all events
    FOR evt IN 
        SELECT * FROM events 
        WHERE stream_id = p_order_id 
        ORDER BY version
    LOOP
        current_state := apply_event(current_state, evt.event_type, evt.event_data);
    END LOOP;
    
    -- Insert rebuilt projection
    INSERT INTO order_projections (order_id, customer_id, items, status, last_event_id)
    VALUES (
        p_order_id,
        (current_state->>'customer_id')::BIGINT,
        current_state->'items',
        current_state->>'status',
        evt.id
    );
END;
$$ LANGUAGE plpgsql;

# Python async event processor
async def process_events():
    last_id = get_checkpoint('order_projection')
    
    while True:
        events = await db.fetch("""
            SELECT * FROM events 
            WHERE id > $1 
            ORDER BY id 
            LIMIT 100
        """, last_id)
        
        for event in events:
            await update_projection(event)
            last_id = event['id']
        
        await save_checkpoint('order_projection', last_id)
        await asyncio.sleep(0.1)

┌─────────────────────────────────────────────────────────────────────────────┐
│                    GLOBAL DATABASE ARCHITECTURE                              │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   US-EAST (Primary for US users)                                            │
│   ┌─────────────────────────────────────────────────────────────────────┐   │
│   │  ┌─────────────┐     ┌─────────────┐                               │   │
│   │  │   Primary   │────▶│   Replica   │ (local reads)                 │   │
│   │  │  (writes)   │     │             │                               │   │
│   │  └──────┬──────┘     └─────────────┘                               │   │
│   └─────────┼───────────────────────────────────────────────────────────┘   │
│             │ async replication (reference data only)                       │
│   ┌─────────┼───────────────────────────────────────────────────────────┐   │
│   │         ▼           EU-WEST (Primary for EU users)                 │   │
│   │  ┌─────────────┐     ┌─────────────┐                               │   │
│   │  │   Primary   │────▶│   Replica   │                               │   │
│   │  │  (writes)   │     │             │                               │   │
│   │  └─────────────┘     └─────────────┘                               │   │
│   │                                                                     │   │
│   │  EU user data ONLY stored here (GDPR)                              │   │
│   └─────────────────────────────────────────────────────────────────────┘   │
│                                                                              │
│   Data Routing:                                                             │
│   • User data: Route to home region based on user.region                   │
│   • Reference data: Replicated globally (products, configs)                │
│   • Cross-region queries: Federation layer or application-level           │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

-- Users partitioned by region
CREATE TABLE users (
    id BIGSERIAL,
    region VARCHAR(10) NOT NULL,  -- 'us', 'eu', 'asia'
    email VARCHAR(255) NOT NULL,
    data JSONB,
    PRIMARY KEY (region, id)
) PARTITION BY LIST (region);

CREATE TABLE users_us PARTITION OF users FOR VALUES IN ('us');
CREATE TABLE users_eu PARTITION OF users FOR VALUES IN ('eu');
CREATE TABLE users_asia PARTITION OF users FOR VALUES IN ('asia');

-- Reference data (replicated everywhere)
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    -- No region-specific data
);

-- Foreign data wrapper for cross-region queries
CREATE EXTENSION postgres_fdw;

CREATE SERVER eu_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'eu-db.example.com', port '5432', dbname 'app');

CREATE USER MAPPING FOR app_user
SERVER eu_server
OPTIONS (user 'app_user', password 'secret');

CREATE FOREIGN TABLE users_eu_remote (
    id BIGINT,
    region VARCHAR(10),
    email VARCHAR(255)
) SERVER eu_server OPTIONS (table_name 'users_eu');

-- Entity 1: [Name]
CREATE TABLE [entity_name] (
    id BIGSERIAL PRIMARY KEY,
    -- Add relevant columns with appropriate types
    -- Include timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Entity 2: [Name]
-- ...

-- Relationships (junction tables if needed)
-- ...

-- List all indexes with justification
CREATE INDEX idx_[table]_[columns] ON [table]([columns]) 
WHERE [condition if partial];
-- Justification: Supports [specific query pattern]

-- Example:
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);
-- Justification: Supports "recent orders by user" query (ORDER BY created_at DESC)

CREATE INDEX idx_products_category_active ON products(category_id) 
WHERE status = 'active';
-- Justification: Partial index for active product listings by category