Database Schema

// src/lib/server/db.js
import postgres from 'postgres';

const sql = postgres(process.env.DATABASE_URL, {
  max: 10,                    // Connection pool size
  idle_timeout: 20,           // Close idle connections after 20s
  connect_timeout: 10,        // Connection timeout
  transform: {
    undefined: null           // Transform undefined to null
  }
});

export default sql;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(100),
  avatar_url TEXT,
  country VARCHAR(2),           -- ISO country code
  preferred_currency VARCHAR(3) DEFAULT 'USD',
  email_verified BOOLEAN DEFAULT false,
  is_admin BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token VARCHAR(255) UNIQUE NOT NULL,
  user_agent TEXT,
  ip_address INET,
  expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_sessions_token ON sessions(token);
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_expires ON sessions(expires_at);

CREATE TABLE api_keys (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  key_hash VARCHAR(255) NOT NULL,
  key_prefix VARCHAR(8) NOT NULL,  -- First 8 chars for identification
  scopes TEXT[] DEFAULT '{}',       -- Permission scopes
  last_used_at TIMESTAMP WITH TIME ZONE,
  expires_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash);

CREATE TABLE breweries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  country VARCHAR(2),
  city VARCHAR(100),
  website TEXT,
  logo_url TEXT,
  description TEXT,
  founded_year INTEGER,
  is_verified BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_breweries_name ON breweries(name);
CREATE INDEX idx_breweries_country ON breweries(country);

CREATE TABLE beers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  brewery_id UUID REFERENCES breweries(id),
  name VARCHAR(255) NOT NULL,
  style VARCHAR(100),
  substyle VARCHAR(100),
  abv DECIMAL(4,2),              -- Alcohol by volume
  ibu INTEGER,                    -- International Bitterness Units
  description TEXT,
  image_url TEXT,
  barcode VARCHAR(50),
  untappd_id VARCHAR(50),
  ratebeer_id VARCHAR(50),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_beers_name ON beers(name);
CREATE INDEX idx_beers_brewery ON beers(brewery_id);
CREATE INDEX idx_beers_style ON beers(style);
CREATE INDEX idx_beers_barcode ON beers(barcode);
CREATE UNIQUE INDEX idx_beers_untappd ON beers(untappd_id) WHERE untappd_id IS NOT NULL;

CREATE TABLE user_collection (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  beer_id UUID NOT NULL REFERENCES beers(id),
  quantity INTEGER DEFAULT 1,
  container_type VARCHAR(50) DEFAULT 'bottle', -- bottle, can, keg, growler
  container_size_ml INTEGER,
  storage_location VARCHAR(255),
  notes TEXT,
  is_for_trade BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_collection_user ON user_collection(user_id);
CREATE INDEX idx_collection_beer ON user_collection(beer_id);
CREATE INDEX idx_collection_trade ON user_collection(is_for_trade) WHERE is_for_trade = true;

CREATE TABLE collection_acquisitions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  collection_item_id UUID NOT NULL REFERENCES user_collection(id) ON DELETE CASCADE,
  quantity INTEGER DEFAULT 1,
  purchase_price DECIMAL(10,2),
  purchase_currency VARCHAR(3) DEFAULT 'USD',
  purchase_date DATE,
  purchase_location VARCHAR(255),
  batch_number VARCHAR(100),
  best_before DATE,
  notes TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_acquisitions_item ON collection_acquisitions(collection_item_id);
CREATE INDEX idx_acquisitions_date ON collection_acquisitions(purchase_date);

CREATE TABLE collection_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  collection_item_id UUID REFERENCES user_collection(id) ON DELETE SET NULL,
  beer_id UUID NOT NULL REFERENCES beers(id),
  action VARCHAR(50) NOT NULL,    -- 'consumed', 'gifted', 'sold', 'expired', 'broken'
  quantity INTEGER DEFAULT 1,
  action_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  notes TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_history_user ON collection_history(user_id);
CREATE INDEX idx_history_action_date ON collection_history(action_date);

CREATE TABLE ratings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  beer_id UUID NOT NULL REFERENCES beers(id),
  transaction_id UUID REFERENCES transactions(id),
  overall_rating DECIMAL(2,1) CHECK (overall_rating >= 1 AND overall_rating <= 5),
  aroma_rating DECIMAL(2,1),
  appearance_rating DECIMAL(2,1),
  taste_rating DECIMAL(2,1),
  mouthfeel_rating DECIMAL(2,1),
  review_text TEXT,
  tasting_notes JSONB DEFAULT '{}',  -- Flavor descriptors
  serving_type VARCHAR(50),           -- draft, bottle, can
  serving_temperature VARCHAR(50),
  is_public BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, beer_id)            -- One rating per user per beer
);

CREATE INDEX idx_ratings_user ON ratings(user_id);
CREATE INDEX idx_ratings_beer ON ratings(beer_id);
CREATE INDEX idx_ratings_overall ON ratings(overall_rating);

CREATE TABLE marketplace_listings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  seller_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  collection_item_id UUID REFERENCES user_collection(id),
  beer_id UUID NOT NULL REFERENCES beers(id),
  title VARCHAR(255) NOT NULL,
  description TEXT,
  quantity INTEGER DEFAULT 1,
  price DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'USD',
  shipping_cost DECIMAL(10,2),
  ships_to TEXT[],                    -- Array of country codes
  condition VARCHAR(50),              -- 'new', 'aged', 'vintage'
  photos TEXT[],
  status VARCHAR(50) DEFAULT 'active', -- 'active', 'sold', 'cancelled', 'expired'
  views_count INTEGER DEFAULT 0,
  expires_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_listings_seller ON marketplace_listings(seller_id);
CREATE INDEX idx_listings_status ON marketplace_listings(status);
CREATE INDEX idx_listings_beer ON marketplace_listings(beer_id);
CREATE INDEX idx_listings_price ON marketplace_listings(price);

CREATE TABLE transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  listing_id UUID NOT NULL REFERENCES marketplace_listings(id),
  buyer_id UUID NOT NULL REFERENCES users(id),
  seller_id UUID NOT NULL REFERENCES users(id),
  quantity INTEGER DEFAULT 1,
  item_price DECIMAL(10,2) NOT NULL,
  shipping_cost DECIMAL(10,2),
  total_price DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'USD',
  stripe_payment_intent_id VARCHAR(255),
  stripe_transfer_id VARCHAR(255),
  status VARCHAR(50) DEFAULT 'pending',
  -- pending, paid, shipped, delivered, completed, cancelled, refunded
  shipping_address JSONB,
  tracking_number VARCHAR(100),
  shipped_at TIMESTAMP WITH TIME ZONE,
  delivered_at TIMESTAMP WITH TIME ZONE,
  completed_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_transactions_buyer ON transactions(buyer_id);
CREATE INDEX idx_transactions_seller ON transactions(seller_id);
CREATE INDEX idx_transactions_status ON transactions(status);
CREATE INDEX idx_transactions_stripe ON transactions(stripe_payment_intent_id);

CREATE TABLE notification_preferences (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  email_marketing BOOLEAN DEFAULT false,
  email_transactions BOOLEAN DEFAULT true,
  email_collection_alerts BOOLEAN DEFAULT true,
  push_enabled BOOLEAN DEFAULT false,
  push_subscription JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE user_follows (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  follower_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  following_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(follower_id, following_id),
  CHECK (follower_id != following_id)  -- Users cannot follow themselves
);

CREATE INDEX idx_user_follows_follower ON user_follows(follower_id);
CREATE INDEX idx_user_follows_following ON user_follows(following_id);

CREATE TABLE activity_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  event_type VARCHAR(50) NOT NULL,  -- 'collection_add', 'rating', 'listing_create', 'follow', 'consume'
  target_type VARCHAR(50),          -- 'beer', 'collection_item', 'rating', 'listing', 'user'
  target_id UUID,
  metadata JSONB DEFAULT '{}',
  is_public BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_activity_events_user ON activity_events(user_id);
CREATE INDEX idx_activity_events_created ON activity_events(created_at DESC);
CREATE INDEX idx_activity_events_type ON activity_events(event_type);

CREATE TABLE likes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  target_type VARCHAR(50) NOT NULL,  -- 'rating', 'collection_item', 'listing'
  target_id UUID NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, target_type, target_id)
);

CREATE INDEX idx_likes_target ON likes(target_type, target_id);
CREATE INDEX idx_likes_user ON likes(user_id);

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  target_type VARCHAR(50) NOT NULL,  -- 'rating', 'collection_item', 'listing'
  target_id UUID NOT NULL,
  content TEXT NOT NULL,
  parent_id UUID REFERENCES comments(id) ON DELETE CASCADE,  -- For threaded comments
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_comments_target ON comments(target_type, target_id);
CREATE INDEX idx_comments_user ON comments(user_id);

-- Social profile visibility settings (added by migration 00015)
ALTER TABLE users 
ADD COLUMN is_public BOOLEAN DEFAULT false,
ADD COLUMN show_collection BOOLEAN DEFAULT false,
ADD COLUMN show_ratings BOOLEAN DEFAULT false,
ADD COLUMN show_stats BOOLEAN DEFAULT true;

-- Feature preferences (added by migration 00016)
ALTER TABLE users 
ADD COLUMN feature_collection BOOLEAN DEFAULT true NOT NULL,
ADD COLUMN feature_marketplace BOOLEAN DEFAULT false NOT NULL,
ADD COLUMN feature_social BOOLEAN DEFAULT false NOT NULL,
ADD COLUMN discoverable BOOLEAN DEFAULT true NOT NULL,
ADD COLUMN onboarding_completed BOOLEAN DEFAULT false NOT NULL;

-- Partial index for discovering public profiles
CREATE INDEX idx_users_public ON users(is_public) WHERE is_public = true;

const collection = await sql`
  SELECT 
    uc.*,
    b.name as beer_name,
    b.style,
    b.abv,
    b.image_url,
    br.name as brewery_name,
    br.country as brewery_country,
    COALESCE(
      (SELECT json_agg(ca.* ORDER BY ca.purchase_date DESC)
       FROM collection_acquisitions ca
       WHERE ca.collection_item_id = uc.id),
      '[]'
    ) as acquisitions
  FROM user_collection uc
  JOIN beers b ON uc.beer_id = b.id
  LEFT JOIN breweries br ON b.brewery_id = br.id
  WHERE uc.user_id = ${userId}
  ORDER BY uc.created_at DESC
`;

const beers = await sql`
  SELECT 
    b.*,
    br.name as brewery_name,
    AVG(r.overall_rating) as avg_rating,
    COUNT(r.id) as rating_count
  FROM beers b
  LEFT JOIN breweries br ON b.brewery_id = br.id
  LEFT JOIN ratings r ON b.id = r.beer_id
  WHERE b.is_active = true
    ${name ? sql`AND b.name ILIKE ${'%' + name + '%'}` : sql``}
    ${style ? sql`AND b.style = ${style}` : sql``}
    ${minAbv ? sql`AND b.abv >= ${minAbv}` : sql``}
    ${maxAbv ? sql`AND b.abv <= ${maxAbv}` : sql``}
  GROUP BY b.id, br.name
  ORDER BY avg_rating DESC NULLS LAST
  LIMIT ${limit} OFFSET ${offset}
`;

const listings = await sql`
  SELECT 
    ml.*,
    b.name as beer_name,
    b.style,
    b.image_url,
    br.name as brewery_name,
    u.display_name as seller_name,
    (SELECT AVG(tr.seller_rating) 
     FROM transaction_ratings tr 
     JOIN transactions t ON tr.transaction_id = t.id
     WHERE t.seller_id = ml.seller_id) as seller_rating
  FROM marketplace_listings ml
  JOIN beers b ON ml.beer_id = b.id
  LEFT JOIN breweries br ON b.brewery_id = br.id
  JOIN users u ON ml.seller_id = u.id
  WHERE ml.status = 'active'
    AND (ml.expires_at IS NULL OR ml.expires_at > NOW())
    ${country ? sql`AND ${country} = ANY(ml.ships_to)` : sql``}
  ORDER BY ml.created_at DESC
  LIMIT ${limit} OFFSET ${offset}
`;

const [stats] = await sql`
  SELECT 
    COUNT(DISTINCT uc.id) as unique_beers,
    SUM(uc.quantity) as total_bottles,
    COUNT(DISTINCT b.style) as styles_count,
    COUNT(DISTINCT b.brewery_id) as breweries_count,
    COALESCE(SUM(ca.purchase_price * ca.quantity), 0) as total_value
  FROM user_collection uc
  JOIN beers b ON uc.beer_id = b.id
  LEFT JOIN collection_acquisitions ca ON uc.id = ca.collection_item_id
  WHERE uc.user_id = ${userId}
`;

migrations/
├── 00001_initial/
│   └── index.sql
├── 00002_marketplace_fields/
│   └── index.sql
└── ...

# Run pending migrations
npm run migrate

# Check migration status
npm run migrate:status

# Rollback last migration (if supported)
npm run migrate:down

# Create migration directory
mkdir migrations/00015_add_feature

# Create the SQL file
touch migrations/00015_add_feature/index.sql

-- migrations/00015_add_feature/index.sql

-- Add new column
ALTER TABLE beers ADD COLUMN flavor_profile JSONB;

-- Create new table
CREATE TABLE beer_awards (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  beer_id UUID REFERENCES beers(id) ON DELETE CASCADE,
  award_name VARCHAR(255) NOT NULL,
  year INTEGER,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_beer_awards_beer ON beer_awards(beer_id);

-- Full-text search on beer names
CREATE INDEX idx_beers_name_search ON beers 
  USING gin(to_tsvector('english', name));

-- Composite index for collection queries
CREATE INDEX idx_collection_user_beer ON user_collection(user_id, beer_id);

-- Partial index for active listings only
CREATE INDEX idx_active_listings ON marketplace_listings(created_at DESC)
  WHERE status = 'active';

-- JSONB index for tasting notes search
CREATE INDEX idx_ratings_tasting_notes ON ratings 
  USING gin(tasting_notes);