// Increase timeout in Supabase client
const supabase = createClient(url, key, {
  db: {
    schema: 'public',
  },
  global: {
    headers: {
      'X-Client-Info': 'smart-shelf',
    },
  },
  // Increase timeout
  realtime: {
    params: {
      timeout: 30000,
    },
  },
})

// Add retry logic
async function withRetry<T>(
  operation: () => Promise<T>,
  maxRetries: number = 3
): Promise<T> {
  for (let i = 0; i < maxRetries; i++) {
    try {
      return await operation()
    } catch (error) {
      if (i === maxRetries - 1) throw error
      await new Promise(resolve => setTimeout(resolve, 1000 * Math.pow(2, i)))
    }
  }
  throw new Error('Max retries exceeded')
}

// Configure connection pooling
const supabase = createClient(url, key, {
  db: {
    schema: 'public',
  },
  // Limit concurrent connections
  realtime: {
    params: {
      eventsPerSecond: 10,
    },
  },
})

// Use connection pooling middleware
export const poolConfig = {
  max: 20, // Maximum number of connections
  min: 2,  // Minimum number of connections
  acquire: 30000, // Maximum time to wait for connection
  idle: 10000,    // Maximum time connection can be idle
}

-- Add indexes for frequently queried columns
CREATE INDEX CONCURRENTLY idx_products_name ON products(name);
CREATE INDEX CONCURRENTLY idx_inventory_product_warehouse ON inventory(product_id, warehouse_id);
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at);

-- Optimize queries with EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM products WHERE name ILIKE '%search%';

-- Use proper LIMIT and OFFSET for pagination
SELECT * FROM products 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 0;

-- Avoid N+1 queries with proper joins
SELECT 
  p.*,
  i.quantity_on_hand
FROM products p
LEFT JOIN inventory i ON p.id = i.product_id
WHERE p.is_active = true;

// Use specific column selection
const { data } = await supabase
  .from('products')
  .select(`
    id,
    name,
    sku,
    inventory!inner(quantity_on_hand)
  `)
  .eq('is_active', true)
  .limit(20)

// Batch operations instead of individual queries
const batchInsert = await supabase
  .from('products')
  .insert([
    { name: 'Product 1', sku: 'SKU1' },
    { name: 'Product 2', sku: 'SKU2' },
    { name: 'Product 3', sku: 'SKU3' },
  ])

// Use proper filtering
const { data } = await supabase
  .from('products')
  .select('*')
  .gte('created_at', '2024-01-01')
  .lt('created_at', '2024-12-31')
  .order('created_at', { ascending: false })

-- Check existing RLS policies
SELECT 
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies 
WHERE tablename = 'products';

-- Create proper RLS policies
CREATE POLICY "Users can view products" ON products
  FOR SELECT USING (true);

CREATE POLICY "Users can insert products" ON products
  FOR INSERT WITH CHECK (auth.role() = 'authenticated');

CREATE POLICY "Users can update own products" ON products
  FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "Admins can delete products" ON products
  FOR DELETE USING (
    auth.jwt() ->> 'role' = 'admin'
  );

-- Enable RLS on table
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Temporarily disable RLS for debugging (not recommended for production)
ALTER TABLE products DISABLE ROW LEVEL SECURITY;

# Check migration status
supabase migration list

# Reset database migrations (development only)
supabase db reset

# Apply specific migration
supabase db migrate up --file 20240101000000_initial_schema.sql

# Generate new migration
supabase migration new add_products_table

# Rollback migration
supabase migration down --file 20240101000000_initial_schema.sql

-- Always backup before migrations
pg_dump -h your-host -U your-user your-database > backup.sql

-- Use transactions for complex migrations
BEGIN;

-- Add new column with default value
ALTER TABLE products ADD COLUMN category_id INTEGER DEFAULT 1;

-- Update existing data
UPDATE products SET category_id = 2 WHERE type = 'electronics';

-- Add constraints
ALTER TABLE products ADD CONSTRAINT fk_category 
  FOREIGN KEY (category_id) REFERENCES categories(id);

COMMIT;

-- Handle large table migrations
-- Create new table
CREATE TABLE products_new (LIKE products INCLUDING ALL);

-- Add new columns
ALTER TABLE products_new ADD COLUMN new_field VARCHAR(255);

-- Copy data in batches
INSERT INTO products_new SELECT *, 'default_value' 
FROM products WHERE id BETWEEN 1 AND 10000;

-- Swap tables
BEGIN;
DROP TABLE products;
ALTER TABLE products_new RENAME TO products;
COMMIT;

-- Monitor active queries
SELECT 
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity 
WHERE state = 'active'
ORDER BY duration DESC;

-- Check table sizes
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Analyze query performance
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

-- Find missing indexes
SELECT 
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND n_distinct > 100;

-- Monitor index usage
SELECT 
  indexrelname,
  idx_tup_read,
  idx_tup_fetch,
  idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Remove unused indexes
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

# Connect to database
psql -h your-host -U your-user -d your-database

# Check database status
\conninfo
\l  # List databases
\dt # List tables
\di # List indexes

# Analyze table
ANALYZE products;

# Vacuum table
VACUUM ANALYZE products;

# Check for locks
SELECT * FROM pg_locks WHERE NOT granted;

// Add query logging
const supabase = createClient(url, key, {
  global: {
    headers: {
      'X-Client-Info': 'your-app',
    },
  },
})

// Monitor Supabase queries
supabase.realtime.setAuth(token)

const subscription = supabase
  .channel('db-changes')
  .on('postgres_changes', 
    { event: '*', schema: 'public' },
    (payload) => {
      console.log('Database change:', payload)
    }
  )
  .subscribe()