/**
 * Destinations Controller - READ-ONLY (PostgreSQL)
 */
const { getPool } = require('../db');
const transportService = require('../services/transport_service');

function safeJson(value, fallback) {
  if (value === null || value === undefined) return fallback;
  if (typeof value === 'object') return value;
  if (typeof value === 'string') {
    try {
      return JSON.parse(value);
    } catch {
      return fallback;
    }
  }
  return fallback;
}

function normalizeDestination(row) {
  return {
    ...row,
    highlights: safeJson(row.highlights, []),
    media: safeJson(row.media, []),
    nearby_attractions: safeJson(row.nearby_attractions, []),
    ai_data: safeJson(row.ai_data, {})
  };
}

async function list(req, res) {
  const pool = getPool();
  if (!pool) {
    return res.status(500).json({ error: 'Database connection not established' });
  }

  try {
    const { category, district, search, limit = 100, offset = 0 } = req.query;

    let query = 'SELECT * FROM destinations WHERE is_active = true';
    const params = [];
    let paramCounter = 1;

    if (category) {
      query += ` AND category = $${paramCounter++}`;
      params.push(category);
    }
    if (district) {
      query += ` AND district ILIKE $${paramCounter++}`;
      params.push(`%${district}%`);
    }
    if (search) {
      query += ` AND (LOWER(name) LIKE $${paramCounter++} OR LOWER(description) LIKE $${paramCounter++})`;
      const term = `%${String(search).toLowerCase()}%`;
      params.push(term, term);
    }

    query += ` ORDER BY rating DESC NULLS LAST LIMIT $${paramCounter++} OFFSET $${paramCounter++}`;
    params.push(parseInt(limit, 10), parseInt(offset, 10));

    const result = await pool.query(query, params);
    const destinations = result.rows.map(normalizeDestination);

    res.json({ success: true, count: destinations.length, data: destinations });
  } catch (error) {
    console.error('[Destination Controller] list error:', {
      message: error.message,
      stack: error.stack,
      query: req.query
    });
    res.status(500).json({ 
      error: 'Failed to fetch destinations from database',
      details: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
}

async function show(req, res) {
  const pool = getPool();
  if (!pool) {
    return res.status(500).json({ error: 'Database connection not established' });
  }

  try {
    const slug = req.params.slug;
    const normalizedSlug = slug.replace(/-/g, '_');
    
    const result = await pool.query(
      'SELECT * FROM destinations WHERE (slug = $1 OR slug = $2 OR LOWER(slug) = LOWER($1)) AND is_active = true',
      [slug, normalizedSlug]
    );

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

    res.json({ success: true, data: normalizeDestination(result.rows[0]) });
  } catch (error) {
    console.error('[Destination Controller] show error:', {
      slug: req.params.slug,
      message: error.message,
      stack: error.stack
    });
    res.status(500).json({ 
      error: 'Failed to fetch destination from database',
      details: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
}

async function districtStats(req, res) {
  const pool = getPool();
  if (!pool) {
    return res.status(500).json({ error: 'Database connection not established' });
  }

  try {
    const result = await pool.query(`
      SELECT 
        district, 
        COUNT(*)::integer as count, 
        ROUND(COALESCE(AVG(rating), 0)::numeric, 2)::float as avg_rating
      FROM destinations 
      WHERE is_active = true AND latitude IS NOT NULL
      GROUP BY district 
      ORDER BY count DESC
    `);

    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error('[Destination Controller] districtStats error:', {
      message: error.message,
      stack: error.stack
    });
    res.status(500).json({ 
      error: 'Failed to fetch district stats from database',
      details: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
}

async function districtDestinations(req, res) {
  const pool = getPool();
  if (!pool) {
    return res.status(500).json({ error: 'Database connection not established' });
  }

  try {
    const { district } = req.params;
    const { limit = 3 } = req.query;

    const result = await pool.query(
      `SELECT id, name, slug, media, rating, category
       FROM destinations
       WHERE district = $1 AND is_active = true
       ORDER BY rating DESC NULLS LAST
       LIMIT $2`,
      [district, parseInt(limit, 10)]
    );

    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error('[Destination Controller] districtDestinations error:', {
      district: req.params.district,
      message: error.message,
      stack: error.stack
    });
    res.status(500).json({ 
      error: 'Failed to fetch district destinations from database',
      details: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
}

async function transportHubs(req, res) {
  try {
    const { district } = req.query;
    const hubs = await transportService.getNearbyHubs(district);
    res.json({ success: true, count: hubs.length, data: hubs });
  } catch (error) {
    console.error('[Transport Error]', error.message);
    res.status(500).json({ error: 'Failed to fetch transport hubs' });
  }
}

module.exports = {
  list,
  show,
  districtStats,
  districtDestinations,
  transportHubs
};