PostgreSQL & Advanced Queries - Database & APIs

1# Install PostgreSQL (macOS)
2brew install postgresql@15
3brew services start postgresql@15
4
5# Create database and user
6createdb myapp_development
7createuser myapp_user --createdb --login
8
9# Install Node.js dependencies
10npm install pg dotenv bcryptjs
11npm install --save-dev @types/pg nodemon
12
13# Optional: Install query builder
14npm install knex # or prisma, sequelize

1const { Pool, Client } = require('pg');
2require('dotenv').config();
3
4class PostgreSQLConnection {
5  constructor() {
6    this.pool = null;
7    this.isConnected = false;
8    this.config = {
9      host: process.env.DB_HOST || 'localhost',
10      port: process.env.DB_PORT || 5432,
11      database: process.env.DB_NAME || 'myapp_development',
12      user: process.env.DB_USER || 'myapp_user',
13      password: process.env.DB_PASSWORD || '',
14
15      // Connection pool settings
16      max: 20, // Maximum number of clients in the pool
17      min: 2,  // Minimum number of clients in the pool
18      idleTimeoutMillis: 30000, // Close idle clients after 30 seconds
19      connectionTimeoutMillis: 2000, // Return error after 2 seconds if connection could not be established
20      maxUses: 7500, // Close (and replace) a connection after it has been used this many times
21
22      // SSL configuration for production
23      ssl: process.env.NODE_ENV === 'production' ? {
24        rejectUnauthorized: false // Set to true in production with proper certificates
25      } : false,
26
27      // Statement timeout
28      statement_timeout: 30000, // 30 seconds
29      query_timeout: 30000,
30
31      // Application name for monitoring
32      application_name: 'nodejs_app'
33    };
34  }
35
36  async initialize() {
37    try {
38      // Create connection pool
39      this.pool = new Pool(this.config);
40
41      // Test connection
42      const client = await this.pool.connect();
43      const result = await client.query('SELECT NOW() as current_time, version() as version');
44      client.release();
45
46      console.log('✅ PostgreSQL connected successfully');
47      console.log(\`📊 Database: \${this.config.database}\`);
48      console.log(\`🕐 Server time: \${result.rows[0].current_time}\`);
49      console.log(\`📝 Version: \${result.rows[0].version.split(' ')[0]} \${result.rows[0].version.split(' ')[1]}\`);
50
51      this.isConnected = true;
52
53      // Handle pool events
54      this.pool.on('connect', (client) => {
55        console.log(\`🔗 New client connected (PID: \${client.processID})\`);
56      });
57
58      this.pool.on('error', (err, client) => {
59        console.error('❌ Unexpected error on idle client', err);
60        this.isConnected = false;
61      });
62
63      this.pool.on('remove', (client) => {
64        console.log(\`🔌 Client removed (PID: \${client.processID})\`);
65      });
66
67      // Graceful shutdown
68      process.on('SIGINT', async () => {
69        await this.close();
70        process.exit(0);
71      });
72
73      process.on('SIGTERM', async () => {
74        await this.close();
75        process.exit(0);
76      });
77
78    } catch (error) {
79      console.error('❌ PostgreSQL connection failed:', error.message);
80      throw error;
81    }
82  }
83
84  // Get a client from the pool
85  async getClient() {
86    if (!this.pool) {
87      throw new Error('Database not initialized. Call initialize() first.');
88    }
89    return await this.pool.connect();
90  }
91
92  // Execute a simple query
93  async query(text, params = []) {
94    const start = Date.now();
95    try {
96      const result = await this.pool.query(text, params);
97      const duration = Date.now() - start;
98
99      // Log slow queries (> 100ms)
100      if (duration > 100) {
101        console.warn(\`🐌 Slow query (\${duration}ms): \${text.substring(0, 100)}...\`);
102      }
103
104      return result;
105    } catch (error) {
106      console.error('❌ Query error:', error.message);
107      console.error('📝 Query:', text);
108      console.error('📋 Params:', params);
109      throw error;
110    }
111  }
112
113  // Execute query with transaction
114  async transaction(callback) {
115    const client = await this.getClient();
116
117    try {
118      await client.query('BEGIN');
119      console.log('🔄 Transaction started');
120
121      const result = await callback(client);
122
123      await client.query('COMMIT');
124      console.log('✅ Transaction committed');
125
126      return result;
127    } catch (error) {
128      await client.query('ROLLBACK');
129      console.log('🔄 Transaction rolled back');
130      throw error;
131    } finally {
132      client.release();
133    }
134  }
135
136  // Get pool status
137  getPoolStatus() {
138    if (!this.pool) return { status: 'not_initialized' };
139
140    return {
141      totalCount: this.pool.totalCount,
142      idleCount: this.pool.idleCount,
143      waitingCount: this.pool.waitingCount,
144      maxConnections: this.config.max,
145      isConnected: this.isConnected
146    };
147  }
148
149  // Close all connections
150  async close() {
151    if (this.pool) {
152      await this.pool.end();
153      console.log('🔌 PostgreSQL connection pool closed');
154      this.isConnected = false;
155    }
156  }
157
158  // Health check
159  async healthCheck() {
160    try {
161      const result = await this.query('SELECT 1 as health_check');
162      return {
163        status: 'healthy',
164        timestamp: new Date(),
165        pool: this.getPoolStatus()
166      };
167    } catch (error) {
168      return {
169        status: 'unhealthy',
170        error: error.message,
171        timestamp: new Date()
172      };
173    }
174  }
175}
176
177module.exports = new PostgreSQLConnection();

1-- Enable extensions
2CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
3CREATE EXTENSION IF NOT EXISTS "pgcrypto";
4CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- For similarity searches
5
6-- Create custom types
7CREATE TYPE user_role AS ENUM ('admin', 'user', 'moderator', 'premium');
8CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
9CREATE TYPE payment_status AS ENUM ('pending', 'completed', 'failed', 'refunded');
10
11-- Users table with comprehensive fields
12CREATE TABLE users (
13    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
14    email VARCHAR(255) UNIQUE NOT NULL,
15    password_hash VARCHAR(255) NOT NULL,
16    first_name VARCHAR(100) NOT NULL,
17    last_name VARCHAR(100) NOT NULL,
18    phone VARCHAR(20),
19    date_of_birth DATE,
20    role user_role DEFAULT 'user',
21    is_active BOOLEAN DEFAULT true,
22    is_email_verified BOOLEAN DEFAULT false,
23    avatar_url TEXT,
24
25    -- Address information (normalized)
26    address_line1 VARCHAR(255),
27    address_line2 VARCHAR(255),
28    city VARCHAR(100),
29    state VARCHAR(100),
30    postal_code VARCHAR(20),
31    country VARCHAR(100) DEFAULT 'United States',
32
33    -- Preferences as JSONB
34    preferences JSONB DEFAULT '{}',
35
36    -- Security fields
37    last_login TIMESTAMPTZ,
38    login_attempts INTEGER DEFAULT 0,
39    locked_until TIMESTAMPTZ,
40    password_reset_token VARCHAR(255),
41    password_reset_expires TIMESTAMPTZ,
42    email_verification_token VARCHAR(255),
43
44    -- Audit fields
45    created_at TIMESTAMPTZ DEFAULT NOW(),
46    updated_at TIMESTAMPTZ DEFAULT NOW(),
47    created_by UUID REFERENCES users(id),
48    updated_by UUID REFERENCES users(id)
49);
50
51-- Categories table with hierarchy support
52CREATE TABLE categories (
53    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
54    name VARCHAR(255) NOT NULL,
55    slug VARCHAR(255) UNIQUE NOT NULL,
56    description TEXT,
57    parent_id UUID REFERENCES categories(id),
58    image_url TEXT,
59    is_active BOOLEAN DEFAULT true,
60    sort_order INTEGER DEFAULT 0,
61
62    -- SEO fields
63    meta_title VARCHAR(255),
64    meta_description TEXT,
65
66    -- Hierarchy path for efficient queries
67    path TEXT, -- e.g., '/electronics/computers/laptops/'
68
69    created_at TIMESTAMPTZ DEFAULT NOW(),
70    updated_at TIMESTAMPTZ DEFAULT NOW()
71);
72
73-- Products table with advanced features
74CREATE TABLE products (
75    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
76    name VARCHAR(255) NOT NULL,
77    slug VARCHAR(255) UNIQUE NOT NULL,
78    description TEXT,
79    short_description VARCHAR(500),
80    sku VARCHAR(100) UNIQUE NOT NULL,
81    category_id UUID NOT NULL REFERENCES categories(id),
82
83    -- Pricing
84    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
85    compare_at_price DECIMAL(10,2) CHECK (compare_at_price >= price),
86    cost DECIMAL(10,2) CHECK (cost >= 0),
87
88    -- Inventory
89    track_inventory BOOLEAN DEFAULT true,
90    inventory_quantity INTEGER DEFAULT 0 CHECK (inventory_quantity >= 0),
91    allow_backorder BOOLEAN DEFAULT false,
92
93    -- Physical properties
94    weight DECIMAL(8,2), -- in kg
95    dimensions JSONB, -- {length, width, height}
96
97    -- Product attributes as JSONB
98    attributes JSONB DEFAULT '{}',
99
100    -- SEO and marketing
101    meta_title VARCHAR(255),
102    meta_description TEXT,
103    featured_image_url TEXT,
104    gallery_images JSONB DEFAULT '[]',
105
106    -- Status
107    is_active BOOLEAN DEFAULT true,
108    is_featured BOOLEAN DEFAULT false,
109    published_at TIMESTAMPTZ,
110
111    -- Audit
112    created_at TIMESTAMPTZ DEFAULT NOW(),
113    updated_at TIMESTAMPTZ DEFAULT NOW(),
114    created_by UUID REFERENCES users(id),
115    updated_by UUID REFERENCES users(id)
116);
117
118-- Product variants for different options (size, color, etc.)
119CREATE TABLE product_variants (
120    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
121    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
122    title VARCHAR(255) NOT NULL,
123    sku VARCHAR(100) UNIQUE NOT NULL,
124
125    -- Variant-specific pricing
126    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
127    compare_at_price DECIMAL(10,2) CHECK (compare_at_price >= price),
128    cost DECIMAL(10,2) CHECK (cost >= 0),
129
130    -- Variant-specific inventory
131    inventory_quantity INTEGER DEFAULT 0 CHECK (inventory_quantity >= 0),
132
133    -- Option values (e.g., {color: 'red', size: 'large'})
134    option_values JSONB NOT NULL DEFAULT '{}',
135
136    -- Physical properties
137    weight DECIMAL(8,2),
138    dimensions JSONB,
139
140    -- Status
141    is_active BOOLEAN DEFAULT true,
142
143    created_at TIMESTAMPTZ DEFAULT NOW(),
144    updated_at TIMESTAMPTZ DEFAULT NOW()
145);
146
147-- Orders table
148CREATE TABLE orders (
149    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
150    order_number VARCHAR(50) UNIQUE NOT NULL,
151    user_id UUID NOT NULL REFERENCES users(id),
152
153    -- Order totals
154    subtotal DECIMAL(10,2) NOT NULL CHECK (subtotal >= 0),
155    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),
156    shipping_amount DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (shipping_amount >= 0),
157    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (discount_amount >= 0),
158    total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
159
160    -- Status
161    status order_status DEFAULT 'pending',
162    payment_status payment_status DEFAULT 'pending',
163
164    -- Shipping address
165    shipping_address JSONB NOT NULL,
166    billing_address JSONB,
167
168    -- Tracking
169    tracking_number VARCHAR(255),
170    shipped_at TIMESTAMPTZ,
171    delivered_at TIMESTAMPTZ,
172
173    -- Notes
174    customer_notes TEXT,
175    admin_notes TEXT,
176
177    created_at TIMESTAMPTZ DEFAULT NOW(),
178    updated_at TIMESTAMPTZ DEFAULT NOW()
179);
180
181-- Order items
182CREATE TABLE order_items (
183    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
184    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
185    product_id UUID NOT NULL REFERENCES products(id),
186    product_variant_id UUID REFERENCES product_variants(id),
187
188    quantity INTEGER NOT NULL CHECK (quantity > 0),
189    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
190    total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
191
192    -- Snapshot of product details at time of order
193    product_snapshot JSONB NOT NULL,
194
195    created_at TIMESTAMPTZ DEFAULT NOW()
196);
197
198-- Reviews and ratings
199CREATE TABLE reviews (
200    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
201    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
202    user_id UUID NOT NULL REFERENCES users(id),
203    order_id UUID REFERENCES orders(id),
204
205    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
206    title VARCHAR(255),
207    content TEXT,
208
209    -- Verification
210    is_verified BOOLEAN DEFAULT false,
211
212    -- Moderation
213    is_approved BOOLEAN DEFAULT true,
214    moderated_by UUID REFERENCES users(id),
215    moderated_at TIMESTAMPTZ,
216
217    -- Helpfulness
218    helpful_count INTEGER DEFAULT 0,
219    total_votes INTEGER DEFAULT 0,
220
221    created_at TIMESTAMPTZ DEFAULT NOW(),
222    updated_at TIMESTAMPTZ DEFAULT NOW(),
223
224    -- Unique constraint to prevent duplicate reviews
225    CONSTRAINT unique_user_product_review UNIQUE (user_id, product_id)
226);
227
228-- Create indexes for performance
229CREATE INDEX idx_users_email ON users(email);
230CREATE INDEX idx_users_role_active ON users(role, is_active);
231CREATE INDEX idx_users_created_at ON users(created_at);
232CREATE INDEX idx_users_last_login ON users(last_login);
233
234CREATE INDEX idx_categories_parent_id ON categories(parent_id);
235CREATE INDEX idx_categories_slug ON categories(slug);
236CREATE INDEX idx_categories_active ON categories(is_active);
237
238CREATE INDEX idx_products_category_id ON products(category_id);
239CREATE INDEX idx_products_slug ON products(slug);
240CREATE INDEX idx_products_sku ON products(sku);
241CREATE INDEX idx_products_active_featured ON products(is_active, is_featured);
242CREATE INDEX idx_products_price ON products(price);
243CREATE INDEX idx_products_published_at ON products(published_at);
244
245-- GIN indexes for JSONB fields
246CREATE INDEX idx_products_attributes ON products USING GIN(attributes);
247CREATE INDEX idx_users_preferences ON users USING GIN(preferences);
248
249-- Full-text search indexes
250CREATE INDEX idx_products_search ON products USING GIN(to_tsvector('english', name || ' ' || COALESCE(description, '')));
251CREATE INDEX idx_users_search ON users USING GIN(to_tsvector('english', first_name || ' ' || last_name || ' ' || email));
252
253CREATE INDEX idx_variants_product_id ON product_variants(product_id);
254CREATE INDEX idx_variants_sku ON product_variants(sku);
255
256CREATE INDEX idx_orders_user_id ON orders(user_id);
257CREATE INDEX idx_orders_status ON orders(status);
258CREATE INDEX idx_orders_created_at ON orders(created_at);
259CREATE INDEX idx_orders_order_number ON orders(order_number);
260
261CREATE INDEX idx_order_items_order_id ON order_items(order_id);
262CREATE INDEX idx_order_items_product_id ON order_items(product_id);
263
264CREATE INDEX idx_reviews_product_id ON reviews(product_id);
265CREATE INDEX idx_reviews_user_id ON reviews(user_id);
266CREATE INDEX idx_reviews_rating ON reviews(rating);
267CREATE INDEX idx_reviews_approved ON reviews(is_approved);
268
269-- Trigger to update updated_at timestamps
270CREATE OR REPLACE FUNCTION update_updated_at_column()
271RETURNS TRIGGER AS $$
272BEGIN
273    NEW.updated_at = NOW();
274    RETURN NEW;
275END;
276$$ language 'plpgsql';
277
278-- Apply trigger to tables
279CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
280CREATE TRIGGER update_categories_updated_at BEFORE UPDATE ON categories FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
281CREATE TRIGGER update_products_updated_at BEFORE UPDATE ON products FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
282CREATE TRIGGER update_variants_updated_at BEFORE UPDATE ON product_variants FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
283CREATE TRIGGER update_orders_updated_at BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
284CREATE TRIGGER update_reviews_updated_at BEFORE UPDATE ON reviews FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

1const db = require('../config/database');
2
3class QueryService {
4  // Complex product search with filters and facets
5  async searchProducts(searchParams) {
6    const {
7      query = '',
8      categoryId = null,
9      minPrice = 0,
10      maxPrice = null,
11      inStock = null,
12      rating = null,
13      page = 1,
14      limit = 20,
15      sortBy = 'relevance',
16      sortOrder = 'desc'
17    } = searchParams;
18
19    try {
20      // Build dynamic WHERE conditions
21      const conditions = ['p.is_active = true'];
22      const params = [];
23      let paramIndex = 1;
24
25      // Full-text search
26      if (query) {
27        conditions.push(\`to_tsvector('english', p.name || ' ' || COALESCE(p.description, '')) @@ plainto_tsquery('english', $\${paramIndex})\`);
28        params.push(query);
29        paramIndex++;
30      }
31
32      // Category filter (including subcategories)
33      if (categoryId) {
34        conditions.push(\`(p.category_id = $\${paramIndex} OR p.category_id IN (
35          WITH RECURSIVE subcategories AS (
36            SELECT id FROM categories WHERE id = $\${paramIndex}
37            UNION ALL
38            SELECT c.id FROM categories c
39            INNER JOIN subcategories s ON c.parent_id = s.id
40          )
41          SELECT id FROM subcategories
42        ))\`);
43        params.push(categoryId);
44        paramIndex++;
45      }
46
47      // Price range filter
48      if (minPrice > 0) {
49        conditions.push(\`p.price >= $\${paramIndex}\`);
50        params.push(minPrice);
51        paramIndex++;
52      }
53
54      if (maxPrice) {
55        conditions.push(\`p.price <= $\${paramIndex}\`);
56        params.push(maxPrice);
57        paramIndex++;
58      }
59
60      // Stock filter
61      if (inStock !== null) {
62        if (inStock) {
63          conditions.push(\`(p.track_inventory = false OR p.inventory_quantity > 0)\`);
64        } else {
65          conditions.push(\`(p.track_inventory = true AND p.inventory_quantity = 0)\`);
66        }
67      }
68
69      // Rating filter
70      if (rating) {
71        conditions.push(\`avg_rating.rating >= $\${paramIndex}\`);
72        params.push(rating);
73        paramIndex++;
74      }
75
76      // Build ORDER BY clause
77      let orderBy = '';
78      switch (sortBy) {
79        case 'price':
80          orderBy = \`p.price \${sortOrder}\`;
81          break;
82        case 'name':
83          orderBy = \`p.name \${sortOrder}\`;
84          break;
85        case 'created':
86          orderBy = \`p.created_at \${sortOrder}\`;
87          break;
88        case 'rating':
89          orderBy = \`COALESCE(avg_rating.rating, 0) \${sortOrder}\`;
90          break;
91        case 'relevance':
92        default:
93          if (query) {
94            orderBy = \`ts_rank(to_tsvector('english', p.name || ' ' || COALESCE(p.description, '')), plainto_tsquery('english', $1)) DESC\`;
95          } else {
96            orderBy = 'p.created_at DESC';
97          }
98          break;
99      }
100
101      const offset = (page - 1) * limit;
102
103      const searchQuery = \`
104        WITH product_data AS (
105          SELECT
106            p.*,
107            c.name as category_name,
108            c.slug as category_slug,
109
110            -- Calculate average rating
111            COALESCE(avg_rating.rating, 0) as avg_rating,
112            COALESCE(avg_rating.review_count, 0) as review_count,
113
114            -- Calculate discount percentage
115            CASE
116              WHEN p.compare_at_price > p.price THEN
117                ROUND((1 - p.price / p.compare_at_price) * 100)
118              ELSE 0
119            END as discount_percentage,
120
121            -- Check stock status
122            CASE
123              WHEN p.track_inventory = false THEN 'in_stock'
124              WHEN p.inventory_quantity > 10 THEN 'in_stock'
125              WHEN p.inventory_quantity > 0 THEN 'low_stock'
126              ELSE 'out_of_stock'
127            END as stock_status,
128
129            -- Search ranking (only when searching)
130            \${query ? "ts_rank(to_tsvector('english', p.name || ' ' || COALESCE(p.description, '')), plainto_tsquery('english', $1)) as search_rank" : '0 as search_rank'}
131
132          FROM products p
133          INNER JOIN categories c ON p.category_id = c.id
134          LEFT JOIN (
135            SELECT
136              product_id,
137              ROUND(AVG(rating)::numeric, 1) as rating,
138              COUNT(*) as review_count
139            FROM reviews
140            WHERE is_approved = true
141            GROUP BY product_id
142          ) avg_rating ON p.id = avg_rating.product_id
143
144          WHERE \${conditions.join(' AND ')}
145        ),
146
147        total_count AS (
148          SELECT COUNT(*) as total FROM product_data
149        )
150
151        SELECT
152          pd.*,
153          tc.total
154        FROM product_data pd
155        CROSS JOIN total_count tc
156        ORDER BY \${orderBy}
157        LIMIT $\${paramIndex} OFFSET $\${paramIndex + 1}
158      \`;
159
160      params.push(limit, offset);
161
162      const result = await db.query(searchQuery, params);
163
164      // Get facet data for filtering
165      const facetsQuery = \`
166        SELECT
167          'categories' as facet_type,
168          c.id as value,
169          c.name as label,
170          COUNT(p.id) as count
171        FROM products p
172        INNER JOIN categories c ON p.category_id = c.id
173        WHERE p.is_active = true
174        GROUP BY c.id, c.name
175        HAVING COUNT(p.id) > 0
176
177        UNION ALL
178
179        SELECT
180          'price_ranges' as facet_type,
181          CASE
182            WHEN p.price < 25 THEN '0-25'
183            WHEN p.price < 50 THEN '25-50'
184            WHEN p.price < 100 THEN '50-100'
185            WHEN p.price < 250 THEN '100-250'
186            ELSE '250+'
187          END as value,
188          CASE
189            WHEN p.price < 25 THEN 'Under $25'
190            WHEN p.price < 50 THEN '$25 - $50'
191            WHEN p.price < 100 THEN '$50 - $100'
192            WHEN p.price < 250 THEN '$100 - $250'
193            ELSE '$250+'
194          END as label,
195          COUNT(*) as count
196        FROM products p
197        WHERE p.is_active = true
198        GROUP BY value, label
199
200        ORDER BY facet_type, count DESC
201      \`;
202
203      const facetsResult = await db.query(facetsQuery);
204
205      // Format facets
206      const facets = {};
207      facetsResult.rows.forEach(row => {
208        if (!facets[row.facet_type]) {
209          facets[row.facet_type] = [];
210        }
211        facets[row.facet_type].push({
212          value: row.value,
213          label: row.label,
214          count: parseInt(row.count)
215        });
216      });
217
218      const products = result.rows;
219      const total = products.length > 0 ? parseInt(products[0].total) : 0;
220      const totalPages = Math.ceil(total / limit);
221
222      return {
223        success: true,
224        products: products.map(p => ({
225          ...p,
226          total: undefined // Remove total from individual products
227        })),
228        pagination: {
229          currentPage: page,
230          totalPages,
231          totalItems: total,
232          itemsPerPage: limit,
233          hasNextPage: page < totalPages,
234          hasPrevPage: page > 1
235        },
236        facets,
237        searchParams
238      };
239
240    } catch (error) {
241      throw new Error(\`Product search failed: \${error.message}\`);
242    }
243  }
244
245  // Advanced user analytics with window functions
246  async getUserAnalytics(timeframe = '30 days') {
247    try {
248      const query = \`
249        WITH user_metrics AS (
250          SELECT
251            u.id,
252            u.first_name,
253            u.last_name,
254            u.email,
255            u.role,
256            u.created_at,
257            u.last_login,
258
259            -- Order metrics
260            COUNT(DISTINCT o.id) as total_orders,
261            COALESCE(SUM(o.total_amount), 0) as total_spent,
262            COALESCE(AVG(o.total_amount), 0) as avg_order_value,
263            MAX(o.created_at) as last_order_date,
264
265            -- Review metrics
266            COUNT(DISTINCT r.id) as total_reviews,
267            COALESCE(AVG(r.rating), 0) as avg_rating_given,
268
269            -- Calculate customer lifetime value
270            COALESCE(SUM(o.total_amount), 0) as lifetime_value,
271
272            -- Calculate days since last activity
273            EXTRACT(DAY FROM NOW() - GREATEST(u.last_login, MAX(o.created_at))) as days_since_activity,
274
275            -- Window functions for ranking
276            ROW_NUMBER() OVER (ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) as value_rank,
277            ROW_NUMBER() OVER (ORDER BY COUNT(DISTINCT o.id) DESC) as order_rank,
278
279            -- Percentile calculations
280            PERCENT_RANK() OVER (ORDER BY COALESCE(SUM(o.total_amount), 0)) as value_percentile
281
282          FROM users u
283          LEFT JOIN orders o ON u.id = o.user_id
284            AND o.created_at >= NOW() - INTERVAL \${timeframe === '30 days' ? "'30 days'" : timeframe === '90 days' ? "'90 days'" : "'1 year'"}
285            AND o.status IN ('delivered', 'shipped')
286          LEFT JOIN reviews r ON u.id = r.user_id
287            AND r.created_at >= NOW() - INTERVAL \${timeframe === '30 days' ? "'30 days'" : timeframe === '90 days' ? "'90 days'" : "'1 year'"}
288
289          WHERE u.is_active = true
290          GROUP BY u.id, u.first_name, u.last_name, u.email, u.role, u.created_at, u.last_login
291        ),
292
293        customer_segments AS (
294          SELECT
295            *,
296            -- Customer segmentation based on RFM analysis
297            CASE
298              WHEN lifetime_value >= 1000 AND days_since_activity <= 30 THEN 'VIP'
299              WHEN lifetime_value >= 500 AND days_since_activity <= 60 THEN 'High Value'
300              WHEN lifetime_value >= 100 AND days_since_activity <= 90 THEN 'Regular'
301              WHEN total_orders > 0 AND days_since_activity <= 180 THEN 'At Risk'
302              WHEN total_orders > 0 THEN 'Lost'
303              ELSE 'New'
304            END as customer_segment,
305
306            -- Customer lifecycle stage
307            CASE
308              WHEN total_orders = 0 THEN 'Prospect'
309              WHEN total_orders = 1 THEN 'New Customer'
310              WHEN total_orders <= 5 THEN 'Developing Customer'
311              WHEN total_orders <= 10 THEN 'Established Customer'
312              ELSE 'Loyal Customer'
313            END as lifecycle_stage
314
315          FROM user_metrics
316        )
317
318        SELECT
319          *,
320          -- Calculate quartiles for value segmentation
321          NTILE(4) OVER (ORDER BY lifetime_value) as value_quartile
322        FROM customer_segments
323        ORDER BY lifetime_value DESC, total_orders DESC
324      \`;
325
326      const result = await db.query(query);
327
328      // Calculate summary statistics
329      const summaryQuery = \`
330        WITH metrics AS (
331          SELECT
332            COUNT(*) as total_users,
333            COUNT(CASE WHEN total_orders > 0 THEN 1 END) as customers,
334            AVG(total_spent) as avg_customer_value,
335            SUM(total_spent) as total_revenue,
336            COUNT(CASE WHEN customer_segment = 'VIP' THEN 1 END) as vip_customers,
337            COUNT(CASE WHEN customer_segment = 'At Risk' THEN 1 END) as at_risk_customers,
338            COUNT(CASE WHEN customer_segment = 'Lost' THEN 1 END) as lost_customers
339          FROM (\${query.replace('ORDER BY lifetime_value DESC, total_orders DESC', '')}) segmented_users
340        )
341        SELECT * FROM metrics
342      \`;
343
344      const summaryResult = await db.query(summaryQuery);
345
346      return {
347        success: true,
348        users: result.rows,
349        summary: summaryResult.rows[0],
350        timeframe,
351        generatedAt: new Date()
352      };
353
354    } catch (error) {
355      throw new Error(\`User analytics failed: \${error.message}\`);
356    }
357  }
358
359  // Product performance analysis with sales data
360  async getProductPerformanceAnalysis(categoryId = null) {
361    try {
362      const categoryFilter = categoryId ? 'AND p.category_id = $1' : '';
363      const params = categoryId ? [categoryId] : [];
364
365      const query = \`
366        WITH product_performance AS (
367          SELECT
368            p.id,
369            p.name,
370            p.sku,
371            p.price,
372            p.inventory_quantity,
373            p.created_at,
374            c.name as category_name,
375
376            -- Sales metrics
377            COALESCE(sales.total_sold, 0) as units_sold,
378            COALESCE(sales.total_revenue, 0) as total_revenue,
379            COALESCE(sales.avg_order_quantity, 0) as avg_order_quantity,
380
381            -- Review metrics
382            COALESCE(reviews.avg_rating, 0) as avg_rating,
383            COALESCE(reviews.review_count, 0) as review_count,
384
385            -- Calculate inventory turnover
386            CASE
387              WHEN p.inventory_quantity > 0 THEN
388                COALESCE(sales.total_sold, 0)::DECIMAL / p.inventory_quantity
389              ELSE 0
390            END as inventory_turnover,
391
392            -- Calculate revenue per unit
393            CASE
394              WHEN COALESCE(sales.total_sold, 0) > 0 THEN
395                COALESCE(sales.total_revenue, 0) / COALESCE(sales.total_sold, 0)
396              ELSE 0
397            END as revenue_per_unit,
398
399            -- Days since launch
400            EXTRACT(DAY FROM NOW() - p.created_at) as days_active,
401
402            -- Stock status
403            CASE
404              WHEN p.track_inventory = false THEN 'unlimited'
405              WHEN p.inventory_quantity > 50 THEN 'high_stock'
406              WHEN p.inventory_quantity > 10 THEN 'medium_stock'
407              WHEN p.inventory_quantity > 0 THEN 'low_stock'
408              ELSE 'out_of_stock'
409            END as stock_status
410
411          FROM products p
412          INNER JOIN categories c ON p.category_id = c.id
413
414          LEFT JOIN (
415            SELECT
416              oi.product_id,
417              SUM(oi.quantity) as total_sold,
418              SUM(oi.total) as total_revenue,
419              AVG(oi.quantity) as avg_order_quantity,
420              COUNT(DISTINCT oi.order_id) as order_count
421            FROM order_items oi
422            INNER JOIN orders o ON oi.order_id = o.id
423            WHERE o.status IN ('delivered', 'shipped')
424              AND o.created_at >= NOW() - INTERVAL '90 days'
425            GROUP BY oi.product_id
426          ) sales ON p.id = sales.product_id
427
428          LEFT JOIN (
429            SELECT
430              product_id,
431              AVG(rating) as avg_rating,
432              COUNT(*) as review_count
433            FROM reviews
434            WHERE is_approved = true
435            GROUP BY product_id
436          ) reviews ON p.id = reviews.product_id
437
438          WHERE p.is_active = true \${categoryFilter}
439        ),
440
441        ranked_products AS (
442          SELECT
443            *,
444            -- Performance rankings
445            ROW_NUMBER() OVER (ORDER BY total_revenue DESC) as revenue_rank,
446            ROW_NUMBER() OVER (ORDER BY units_sold DESC) as volume_rank,
447            ROW_NUMBER() OVER (ORDER BY avg_rating DESC, review_count DESC) as rating_rank,
448            ROW_NUMBER() OVER (ORDER BY inventory_turnover DESC) as turnover_rank,
449
450            -- Performance scores (0-100)
451            ROUND(
452              (PERCENT_RANK() OVER (ORDER BY total_revenue) * 30 +
453               PERCENT_RANK() OVER (ORDER BY units_sold) * 25 +
454               PERCENT_RANK() OVER (ORDER BY avg_rating) * 25 +
455               PERCENT_RANK() OVER (ORDER BY inventory_turnover) * 20) * 100
456            ) as performance_score,
457
458            -- Category rankings
459            ROW_NUMBER() OVER (PARTITION BY category_name ORDER BY total_revenue DESC) as category_revenue_rank
460
461          FROM product_performance
462        ),
463
464        performance_categories AS (
465          SELECT
466            *,
467            -- Performance categories
468            CASE
469              WHEN performance_score >= 80 THEN 'Top Performer'
470              WHEN performance_score >= 60 THEN 'Good Performer'
471              WHEN performance_score >= 40 THEN 'Average Performer'
472              WHEN performance_score >= 20 THEN 'Poor Performer'
473              ELSE 'Underperformer'
474            END as performance_category,
475
476            -- Action recommendations
477            CASE
478              WHEN stock_status = 'out_of_stock' AND units_sold > 10 THEN 'Restock Immediately'
479              WHEN stock_status = 'low_stock' AND inventory_turnover > 2 THEN 'Increase Inventory'
480              WHEN stock_status = 'high_stock' AND inventory_turnover < 0.5 THEN 'Reduce Inventory'
481              WHEN avg_rating < 3 AND review_count > 5 THEN 'Product Quality Review'
482              WHEN units_sold = 0 AND days_active > 30 THEN 'Marketing Review'
483              WHEN performance_score >= 80 THEN 'Feature Product'
484              ELSE 'Monitor'
485            END as recommended_action
486
487          FROM ranked_products
488        )
489
490        SELECT * FROM performance_categories
491        ORDER BY performance_score DESC, total_revenue DESC
492      \`;
493
494      const result = await db.query(query, params);
495
496      // Get category summary if no specific category filter
497      let categorySummary = null;
498      if (!categoryId) {
499        const categoryQuery = \`
500          SELECT
501            category_name,
502            COUNT(*) as product_count,
503            SUM(total_revenue) as category_revenue,
504            SUM(units_sold) as category_units_sold,
505            AVG(avg_rating) as avg_category_rating,
506            AVG(performance_score) as avg_performance_score
507          FROM (\${query.replace('ORDER BY performance_score DESC, total_revenue DESC', '')}) products
508          GROUP BY category_name
509          ORDER BY category_revenue DESC
510        \`;
511
512        const categoryResult = await db.query(categoryQuery, params);
513        categorySummary = categoryResult.rows;
514      }
515
516      return {
517        success: true,
518        products: result.rows,
519        categorySummary,
520        categoryFilter: categoryId,
521        generatedAt: new Date()
522      };
523
524    } catch (error) {
525      throw new Error(\`Product performance analysis failed: \${error.message}\`);
526    }
527  }
528}
529
530module.exports = new QueryService();