-- Create hypertable (automatically partitions by time)
CREATE TABLE transactions (
    transaction_id UUID PRIMARY KEY,
    store_id UUID NOT NULL,
    product_id UUID NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    quantity INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL
);

-- Convert to hypertable (CRITICAL for time-series performance)
SELECT create_hypertable('transactions', 'timestamp');

-- Retention policy (keep only last 2 years)
SELECT add_retention_policy('transactions', INTERVAL '2 years');

-- All tables have customer_id
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    customer_id UUID NOT NULL,  -- Tenant isolation
    sku VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    -- ... other columns
    UNIQUE(customer_id, sku)  -- SKUs unique per customer
);

-- Row-level security (RLS)
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Policy: Users only see their customer's data
CREATE POLICY customer_isolation ON products
    USING (customer_id = current_setting('app.current_customer_id')::UUID);

-- In application, set customer context per request
SET LOCAL app.current_customer_id = '<customer_uuid>';

-- Foreign key indexes (ALWAYS)
CREATE INDEX idx_transactions_store ON transactions(store_id);
CREATE INDEX idx_transactions_product ON transactions(product_id);
CREATE INDEX idx_transactions_customer ON transactions(customer_id);

-- Composite indexes (for common query patterns)
CREATE INDEX idx_transactions_store_time ON transactions(store_id, timestamp DESC);
CREATE INDEX idx_inventory_store_product_time ON inventory_levels(store_id, product_id, timestamp DESC);

-- Partial indexes (for filtered queries)
CREATE INDEX idx_alerts_open ON alerts(store_id, created_at DESC) 
    WHERE status = 'open';

-- GIN index (for JSONB columns)
CREATE INDEX idx_metadata_gin ON anomalies USING GIN(metadata);

-- Example slow query
EXPLAIN ANALYZE
SELECT 
    p.name,
    SUM(t.quantity) AS total_sold
FROM transactions t
JOIN products p ON t.product_id = p.product_id
WHERE t.timestamp >= NOW() - INTERVAL '30 days'
GROUP BY p.name
ORDER BY total_sold DESC
LIMIT 10;

-- Look for:
-- ❌ Seq Scan (full table scan) - needs index
-- ❌ Hash Join - might need better indexes
-- ✅ Index Scan - good!
-- ✅ Bitmap Index Scan - good for OR conditions

-- Query with time filter - only scans relevant partitions
SELECT * FROM transactions 
WHERE timestamp >= '2026-02-01' AND timestamp < '2026-03-01';
-- TimescaleDB automatically skips other months

-- BAD: Compute on application side
results = db.execute("SELECT * FROM transactions WHERE ...")
total = sum(r['quantity'] for r in results)

-- GOOD: Let database aggregate
total = db.execute(
    "SELECT SUM(quantity) FROM transactions WHERE ..."
).scalar()

-- BAD: Sort all rows, then limit
SELECT * FROM products ORDER BY created_at DESC LIMIT 10;

-- GOOD: Use index to get top 10 directly
CREATE INDEX idx_products_created_desc ON products(created_at DESC);
-- Now query uses index scan, only fetches 10 rows

-- Create materialized view (refreshed automatically)
CREATE MATERIALIZED VIEW daily_sales
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 day', timestamp) AS day,
    store_id,
    product_id,
    SUM(quantity) AS quantity_sold,
    SUM(total_amount) AS revenue
FROM transactions
GROUP BY day, store_id, product_id;

-- Refresh policy (every hour)
SELECT add_continuous_aggregate_policy('daily_sales',
    start_offset => INTERVAL '1 month',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

-- Now queries on daily_sales are instant (pre-computed)
SELECT day, SUM(revenue) FROM daily_sales 
WHERE day >= '2026-02-01'
GROUP BY day;

CREATE TABLE inventory_levels (
    id BIGSERIAL PRIMARY KEY,
    store_id UUID REFERENCES stores(store_id),
    product_id UUID REFERENCES products(product_id),
    timestamp TIMESTAMP NOT NULL,
    quantity_on_hand INT NOT NULL,
    quantity_allocated INT DEFAULT 0,
    
    -- Constraints
    CHECK (quantity_on_hand >= 0),  -- Can't be negative
    CHECK (quantity_allocated >= 0),
    CHECK (quantity_allocated <= quantity_on_hand),  -- Can't allocate more than on-hand
    
    -- Computed column (auto-calculated, stored)
    quantity_available INT GENERATED ALWAYS AS 
        (quantity_on_hand - quantity_allocated) STORED
);

-- Unique constraints
CREATE UNIQUE INDEX idx_reorder_points_current ON reorder_points(store_id, product_id)
    WHERE effective_to IS NULL;  -- Only one "current" reorder point per store/product

# migrations/versions/001_create_stores_table.py
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'stores',
        sa.Column('store_id', sa.UUID(), primary_key=True),
        sa.Column('customer_id', sa.UUID(), nullable=False),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('created_at', sa.TIMESTAMP(), server_default=sa.func.now())
    )
    
    op.create_index('idx_stores_customer', 'stores', ['customer_id'])

def downgrade():
    op.drop_table('stores')

-- Step 1: Add column (nullable, with default)
ALTER TABLE products ADD COLUMN shelf_life_days INT DEFAULT NULL;

-- Step 2: Backfill data (in batches, off-peak hours)
UPDATE products SET shelf_life_days = 7 WHERE category = 'produce';
UPDATE products SET shelf_life_days = 30 WHERE category = 'dairy';

-- Step 3: Add NOT NULL constraint (after backfill complete)
ALTER TABLE products ALTER COLUMN shelf_life_days SET NOT NULL;

# Using SQLAlchemy (Python)
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool

engine = create_engine(
    'postgresql://user:pass@localhost/shelfops',
    poolclass=QueuePool,
    pool_size=10,  # Number of persistent connections
    max_overflow=20,  # Additional connections under load
    pool_pre_ping=True,  # Check connection health before use
    pool_recycle=3600  # Recycle connections every hour
)

-- Check current setting
SHOW max_connections;  -- Default: 100

-- Calculate: (num_app_instances × pool_size) + buffer
-- Example: 3 instances × 10 pool = 30 + 20 buffer = 50 connections

-- See what's running now
SELECT 
    pid,
    now() - query_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Kill long-running query
SELECT pg_terminate_backend(pid) WHERE pid = <pid>;

-- Enable slow query logging (queries > 100ms)
ALTER SYSTEM SET log_min_duration_statement = 100;  -- milliseconds
SELECT pg_reload_conf();

-- Logs appear in PostgreSQL log file

-- Find unused indexes (never scanned)
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Consider dropping unused indexes (after confirming they're not seasonal)

-- Get most recent inventory level per store/product
SELECT DISTINCT ON (store_id, product_id)
    store_id,
    product_id,
    timestamp,
    quantity_on_hand
FROM inventory_levels
ORDER BY store_id, product_id, timestamp DESC;

-- Faster with lateral join (if querying specific stores)
SELECT 
    s.store_id,
    p.product_id,
    latest.timestamp,
    latest.quantity_on_hand
FROM stores s
CROSS JOIN products p
CROSS JOIN LATERAL (
    SELECT timestamp, quantity_on_hand
    FROM inventory_levels i
    WHERE i.store_id = s.store_id AND i.product_id = p.product_id
    ORDER BY timestamp DESC
    LIMIT 1
) latest;

-- 7-day rolling average sales per store/product
SELECT 
    store_id,
    product_id,
    time_bucket('1 day', timestamp) AS day,
    AVG(quantity) OVER (
        PARTITION BY store_id, product_id
        ORDER BY time_bucket('1 day', timestamp)
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS avg_7d_sales
FROM transactions
WHERE timestamp >= NOW() - INTERVAL '30 days';

-- Don't create duplicate alerts for same store/product
INSERT INTO alerts (store_id, product_id, alert_type, message, ...)
VALUES (...)
ON CONFLICT (store_id, product_id, alert_type) 
    WHERE status = 'open'
DO UPDATE SET
    message = EXCLUDED.message,
    updated_at = NOW();

-- Requires unique index:
CREATE UNIQUE INDEX idx_alerts_dedup ON alerts(store_id, product_id, alert_type)
    WHERE status = 'open';

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

@pytest.fixture
def db_session():
    """Create test database session"""
    # Use separate test database
    engine = create_engine('postgresql://localhost/shelfops_test')
    
    # Run migrations
    alembic.command.upgrade(alembic_cfg, "head")
    
    Session = sessionmaker(bind=engine)
    session = Session()
    
    yield session
    
    # Rollback after test
    session.rollback()
    session.close()

def test_inventory_constraint(db_session):
    """Test that negative inventory is rejected"""
    from models import InventoryLevel
    
    # This should fail
    with pytest.raises(IntegrityError):
        db_session.add(InventoryLevel(
            store_id=uuid.uuid4(),
            product_id=uuid.uuid4(),
            quantity_on_hand=-5  # Negative - constraint violation
        ))
        db_session.commit()