# Basic schema with users, products, orders
cat templates/basic-schema.sql

# Relationship patterns (1:1, 1:M, M:M)
cat templates/relationships.sql

# Constraint examples
cat templates/constraints.sql

# Audit patterns
cat templates/audit-columns.sql

CREATE TABLE your_table (
  -- Primary key (required)
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Business columns with proper types
  name VARCHAR(200) NOT NULL,  -- Use appropriate lengths

  -- Audit columns (always include)
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- ❌ Bad
CREATE TABLE users (
  email VARCHAR(255),
  name VARCHAR(100)
);

-- ✅ Good
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL
);

-- ❌ Bad
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID  -- No constraint!
);

-- ✅ Good
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

-- Index the foreign key
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- ❌ Bad
CREATE TABLE products (
  name VARCHAR(MAX),
  sku VARCHAR(MAX),
  status VARCHAR(MAX)
);

-- ✅ Good
CREATE TABLE products (
  name VARCHAR(200) NOT NULL,
  sku VARCHAR(50) UNIQUE NOT NULL,
  status VARCHAR(20) NOT NULL
    CHECK (status IN ('draft', 'active', 'archived'))
);

-- ❌ Bad
CREATE TABLE events (
  event_date VARCHAR(50)  -- '2025-12-15' or 'Dec 15, 2025'?
);

-- ✅ Good
CREATE TABLE events (
  event_date DATE NOT NULL,  -- Validated, sortable
  event_time TIMESTAMPTZ     -- With timezone
);

-- Always index foreign keys
CREATE TABLE order_items (
  order_id UUID NOT NULL REFERENCES orders(id),
  product_id UUID NOT NULL REFERENCES products(id)
);

-- ✅ Required indexes
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- ❌ Bad
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200)
);

-- ✅ Good
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- Auto-update trigger (PostgreSQL)
CREATE TRIGGER products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- ❌ Bad (EAV)
CREATE TABLE product_attributes (
  product_id UUID,
  attribute_name VARCHAR(100),  -- 'color', 'size', 'price'
  attribute_value TEXT           -- Everything as text!
);

-- ✅ Good (Structured + JSONB)
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL,  -- Required fields as columns
  color VARCHAR(50),              -- Common attributes as columns
  size VARCHAR(20),
  attributes JSONB                -- Optional/dynamic attributes
);

-- Index JSONB
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

-- Primary Keys
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- OR for performance-critical:
id BIGSERIAL PRIMARY KEY

-- Text
name VARCHAR(200) NOT NULL
description TEXT
code CHAR(10)  -- Fixed-length codes only

-- Numbers
price DECIMAL(10,2) NOT NULL  -- Money: NEVER use FLOAT
quantity INT NOT NULL
rating DECIMAL(3,2)  -- 0.00 to 9.99

-- Dates/Times
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL  -- With timezone
event_date DATE
duration INTERVAL

-- Boolean
is_active BOOLEAN DEFAULT true NOT NULL

-- JSON
attributes JSONB  -- Binary, faster, indexable

-- Enum Alternative (preferred over ENUM type)
status VARCHAR(20) NOT NULL
  CHECK (status IN ('draft', 'active', 'archived'))

-- MySQL doesn't have:
TIMESTAMPTZ  -- Use TIMESTAMP (stored as UTC)
gen_random_uuid()  -- Use UUID() function
JSONB  -- Use JSON (same performance in 8.0+)

-- MySQL equivalent:
id CHAR(36) PRIMARY KEY DEFAULT (UUID())
-- OR:
id BIGINT AUTO_INCREMENT PRIMARY KEY

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
attributes JSON

CREATE TABLE users (
  email VARCHAR(MAX),           -- Issue: No primary key, VARCHAR(MAX)
  password VARCHAR(MAX),
  created VARCHAR(50)           -- Issue: Date as string
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_email VARCHAR(MAX),      -- Issue: No foreign key
  total VARCHAR(20),            -- Issue: Money as string
  status VARCHAR(MAX)           -- Issue: No validation
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'canceled')),
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);