/**
 * Provider Controller - Admin Oversight of Service Providers (PostgreSQL)
 */
const { getPool } = require('../db');
const { eventBus, EVENTS } = require('../services/eventBus');

/**
 * List all providers with filters
 */
async function list(req, res) {
  const { provider_type, verification_status, district, limit = 50, offset = 0 } = req.query;
  const pool = getPool();
  
  if (!pool) {
    console.error('[Provider List Error] No database pool available');
    return res.status(500).json({ error: 'Database not connected' });
  }
  
  try {
    // Explicit selection to avoid ambiguity and handle missing location data
    let query = `
      SELECT 
        p.id, 
        p.user_id, 
        p.provider_type, 
        p.bio, 
        p.verification_status, 
        p.created_at,
        u.name as user_name, 
        u.email as user_email, 
        COALESCE(up.city, 'Jhargram') as district 
      FROM provider_profiles p 
      INNER JOIN users u ON p.user_id = u.id 
      LEFT JOIN user_profiles up ON u.id = up.user_id
      WHERE 1=1
    `;
    
    const params = [];
    let paramCounter = 1;
    
    if (provider_type) {
      query += ` AND p.provider_type = $${paramCounter++}`;
      params.push(provider_type);
    }
    
    if (verification_status) {
      query += ` AND p.verification_status = $${paramCounter++}`;
      params.push(verification_status);
    }
    
    if (district) {
      // Filter by city in profile or mention in bio
      query += ` AND (up.city ILIKE $${paramCounter++} OR p.bio ILIKE $${paramCounter++})`;
      params.push(`%${district}%`, `%${district}%`);
    }
    
    query += ` ORDER BY p.created_at DESC LIMIT $${paramCounter++} OFFSET $${paramCounter++}`;
    params.push(parseInt(limit), parseInt(offset));
    
    const result = await pool.query(query, params);
    
    res.json({ 
      success: true, 
      count: result.rows.length, 
      data: result.rows 
    });
  } catch (error) {
    console.error('[Provider List Query Error]', {
      message: error.message,
      stack: error.stack,
      params: { provider_type, verification_status, district, limit, offset }
    });
    
    res.status(500).json({ 
      success: false, 
      error: 'Internal Server Error',
      message: error.message 
    });
  }
}

/**
 * Verify or Update Provider Status
 */
async function verify(req, res) {
  const { id } = req.params;
  const { verification_status, comments } = req.body;
  const pool = getPool();
  if (!pool) return res.status(500).json({ error: 'Database not connected' });
  
  try {
    const query = 'UPDATE provider_profiles SET verification_status = $2, updated_at = NOW() WHERE id = $1';
    await pool.query(query, [id, verification_status]);
    
    // Emit event
    if (verification_status === 'verified') {
      eventBus.emit(EVENTS.PROVIDER_VERIFIED, { provider_id: id });
    }
    
    res.json({ success: true, message: `Provider status updated to ${verification_status}` });
  } catch (error) {
    console.error('[Provider Verify Error]', error.message);
    res.status(500).json({ error: 'Failed to update provider status' });
  }
}

/**
 * Create Provider Profile (admin)
 */
async function create(req, res) {
  const { user_id, provider_type, bio } = req.body;
  const pool = getPool();
  if (!pool) return res.status(500).json({ error: 'Database not connected' });

  if (!user_id || !provider_type) {
    return res.status(400).json({ error: 'user_id and provider_type are required' });
  }

  try {
    const result = await pool.query(
      `INSERT INTO provider_profiles (user_id, provider_type, bio, verification_status) 
       VALUES ($1, $2, $3, 'pending') 
       RETURNING *`,
      [user_id, provider_type, bio || '']
    );
    res.status(201).json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error('[Provider Create Error]', error.message);
    res.status(500).json({ error: 'Failed to create provider' });
  }
}

/**
 * Update Provider Profile
 */
async function update(req, res) {
  const { id } = req.params;
  const { provider_type, bio, verification_status } = req.body;
  const pool = getPool();
  if (!pool) return res.status(500).json({ error: 'Database not connected' });

  try {
    const updates = [];
    const params = [id];
    let paramCounter = 2;

    if (provider_type !== undefined) {
      updates.push(`provider_type = $${paramCounter++}`);
      params.push(provider_type);
    }
    if (bio !== undefined) {
      updates.push(`bio = $${paramCounter++}`);
      params.push(bio);
    }
    if (verification_status !== undefined) {
      const validStatuses = ['pending', 'verified', 'rejected'];
      if (!validStatuses.includes(verification_status)) {
        return res.status(400).json({ error: `Invalid verification_status. Must be one of: ${validStatuses.join(', ')}` });
      }
      updates.push(`verification_status = $${paramCounter++}`);
      params.push(verification_status);
    }

    if (updates.length === 0) {
      return res.status(400).json({ error: 'No fields to update' });
    }

    updates.push('updated_at = NOW()');
    const query = `UPDATE provider_profiles SET ${updates.join(', ')} WHERE id = $1 RETURNING *`;
    const result = await pool.query(query, params);

    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Provider not found' });
    }

    res.json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error('[Provider Update Error]', error.message);
    res.status(500).json({ error: 'Failed to update provider' });
  }
}

/**
 * Delete Provider Profile
 */
async function remove(req, res) {
  const { id } = req.params;
  const pool = getPool();
  if (!pool) return res.status(500).json({ error: 'Database not connected' });
  
  try {
    await pool.query('DELETE FROM provider_profiles WHERE id = $1', [id]);
    
    res.json({ success: true, message: 'Provider deleted' });
  } catch (error) {
    console.error('[Provider Delete Error]', error.message);
    res.status(500).json({ error: 'Failed to delete provider' });
  }
}

module.exports = { list, verify, create, update, remove };