-- 1. Core entities in 3NF
CREATE TABLE customers (
  id          BIGSERIAL PRIMARY KEY,
  email       TEXT        NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  sku         TEXT        NOT NULL UNIQUE,
  name        TEXT        NOT NULL,
  price_cents INT         NOT NULL CHECK (price_cents >= 0)
);

-- 2. Orders reference customers - foreign key with index
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT      NOT NULL REFERENCES customers(id),
  status      TEXT        NOT NULL DEFAULT 'pending'
                          CHECK (status IN ('pending','confirmed','shipped','cancelled')),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- 3. Junction table for order line items
CREATE TABLE order_items (
  id          BIGSERIAL PRIMARY KEY,
  order_id    BIGINT      NOT NULL REFERENCES orders(id),
  product_id  BIGINT      NOT NULL REFERENCES products(id),
  quantity    INT         NOT NULL CHECK (quantity > 0),
  unit_price_cents INT    NOT NULL
);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Composite index: filter first on equality columns, then range/sort
-- Serves: WHERE org_id = ? AND status = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_org_status_created
  ON orders(org_id, status, created_at DESC);

-- Partial index: only index the rows you actually query
-- Saves space and stays small even as the table grows
CREATE INDEX idx_orders_pending
  ON orders(customer_id, created_at)
  WHERE status = 'pending';

-- Covering index: include non-filter columns to avoid heap fetch
-- The query can be answered entirely from the index (index-only scan)
CREATE INDEX idx_products_sku_covering
  ON products(sku)
  INCLUDE (name, price_cents);

-- Check index usage - drop indexes with low scans
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Always use EXPLAIN ANALYZE (BUFFERS) for real execution data
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.email, sum(oi.quantity * oi.unit_price_cents)
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'pending'
GROUP BY o.id, c.email;

-- Phase 1 (expand): add nullable column, old code ignores it
ALTER TABLE orders ADD COLUMN notes TEXT;

-- Phase 2 (backfill): run in batches to avoid locking
DO $$
DECLARE batch_size INT := 1000;
        last_id    BIGINT := 0;
BEGIN
  LOOP
    UPDATE orders
    SET notes = ''
    WHERE id > last_id AND id <= last_id + batch_size AND notes IS NULL;

    GET DIAGNOSTICS last_id = ROW_COUNT;
    EXIT WHEN last_id = 0;
    PERFORM pg_sleep(0.05); -- yield to avoid lock contention
    last_id := last_id + batch_size;
  END LOOP;
END $$;

-- Phase 3 (contract): add NOT NULL constraint after all rows are filled
ALTER TABLE orders ALTER COLUMN notes SET NOT NULL;
ALTER TABLE orders ALTER COLUMN notes SET DEFAULT '';

-- Soft delete pattern
ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMPTZ;

-- Partial index keeps active-record queries fast
CREATE INDEX idx_customers_active ON customers(email) WHERE deleted_at IS NULL;

-- Application queries always filter
SELECT * FROM customers WHERE deleted_at IS NULL AND email = $1;

-- Hard delete with archival (for GDPR / data retention)
WITH deleted AS (
  DELETE FROM customers WHERE id = $1 RETURNING *
)
INSERT INTO customers_archive SELECT *, now() AS archived_at FROM deleted;

undefined

> In **transaction mode**, prepared statements and `SET` commands do not persist across
> connections. Use `DEALLOCATE ALL` or disable prepared statements in your driver
> (`prepared_statement_cache_size=0` in JDBC).

-- Range partition by month (good for time-series, logs, events)
CREATE TABLE events (
  id         BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  type       TEXT        NOT NULL,
  payload    JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01
  PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02
  PARTITION OF events FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automate with pg_partman extension
SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control      => 'created_at',
  p_type         => 'native',
  p_interval     => 'monthly'
);

-- Partition pruning - Postgres skips partitions outside the WHERE range
EXPLAIN SELECT * FROM events WHERE created_at >= '2024-01-15';
-- Should show: Append -> Seq Scan on events_2024_01 (only one child scanned)