import pool from '../config/db.js';

let reviewColsChecked = false;
async function ensureReviewColumns() {
  if (reviewColsChecked) return;
  try {
    const [cols] = await pool.query('SHOW COLUMNS FROM reviews');
    const set = new Set(cols.map(c => c.Field));
    if (!set.has('images')) {
      await pool.query('ALTER TABLE reviews ADD COLUMN images JSON NULL').catch(() => {});
    }
    reviewColsChecked = true;
  } catch (e) {}
}

// 1. Submit a Review (authenticated general users only, requires OTP verification)
export async function createReview(req, res) {
  await ensureReviewColumns();
  const listing_id = req.body.listing_id || req.params.listingId;
  const { rating, comment, images } = req.body;
  const userId = req.user.id;
  const userRole = req.user.role;

  // Restriction: Only general users can review
  if (userRole !== 'user') {
    return res.status(403).json({
      error: "Reviews can only be submitted by general consumers. Business owners, moderators, and administrators are restricted to ensure objective feedback."
    });
  }

  if (!listing_id || !rating) {
    return res.status(400).json({ error: "Missing required fields: listing_id and rating." });
  }

  if (rating < 1 || rating > 5) {
    return res.status(400).json({ error: "Rating must be between 1 and 5." });
  }

  try {
    // Check if user is OTP verified
    const [userRows] = await pool.query('SELECT is_verified FROM users WHERE id = ?', [userId]);
    if (userRows.length === 0 || !userRows[0].is_verified) {
      return res.status(403).json({
        error: "Account verification required to post reviews. Please go to your profile and verify your account with OTP.",
        requires_verification: true
      });
    }

    // Check if listing exists
    const [listing] = await pool.query('SELECT id FROM listings WHERE id = ?', [listing_id]);
    if (listing.length === 0) {
      return res.status(404).json({ error: "Listing not found." });
    }

    // Check if user already reviewed this listing
    const [existingReview] = await pool.query(
      'SELECT id FROM reviews WHERE listing_id = ? AND user_id = ?',
      [listing_id, userId]
    );
    if (existingReview.length > 0) {
      return res.status(409).json({ error: "You have already submitted a review for this listing." });
    }

    // Optional photos (up to 3 images)
    let imagesJson = null;
    if (Array.isArray(images) && images.length > 0) {
      imagesJson = JSON.stringify(images.slice(0, 3));
    }

    // Insert review
    await pool.query(
      'INSERT INTO reviews (listing_id, user_id, rating, comment, images) VALUES (?, ?, ?, ?, ?)',
      [listing_id, userId, rating, comment || '', imagesJson]
    );

    // Recalculate average rating and review count for the listing
    const [stats] = await pool.query(
      'SELECT AVG(rating) as avg_rating, COUNT(*) as review_count FROM reviews WHERE listing_id = ?',
      [listing_id]
    );

    const avgRating = parseFloat(stats[0].avg_rating).toFixed(2);
    const reviewCount = stats[0].review_count;

    await pool.query(
      'UPDATE listings SET rating = ?, reviews_count = ? WHERE id = ?',
      [avgRating, reviewCount, listing_id]
    );

    return res.status(201).json({
      message: "Review submitted successfully.",
      rating: avgRating,
      reviews_count: reviewCount
    });
  } catch (err) {
    console.error("createReview error:", err);
    return res.status(500).json({ error: "Internal server error submitting review." });
  }
}

// 2. Get All Reviews for a Listing (public)
export async function getReviewsByListing(req, res) {
  await ensureReviewColumns();
  const listingId = req.params.listingId;

  try {
    const query = `
      SELECT r.id, r.rating, r.comment, r.images, r.created_at,
             u.first_name, u.last_name, u.id as user_id, u.avatar_url
      FROM reviews r
      LEFT JOIN users u ON r.user_id = u.id
      WHERE r.listing_id = ?
      ORDER BY r.created_at DESC
    `;
    const [rows] = await pool.query(query, [listingId]);

    const formattedReviews = rows.map(r => {
      let parsedImages = [];
      if (r.images) {
        try {
          parsedImages = typeof r.images === 'string' ? JSON.parse(r.images) : r.images;
        } catch (e) {
          parsedImages = [];
        }
      }
      return {
        ...r,
        images: Array.isArray(parsedImages) ? parsedImages : []
      };
    });

    // Also get aggregate stats
    const [stats] = await pool.query(
      'SELECT AVG(rating) as avg_rating, COUNT(*) as review_count FROM reviews WHERE listing_id = ?',
      [listingId]
    );

    return res.json({
      reviews: formattedReviews,
      avg_rating: stats[0].avg_rating ? parseFloat(stats[0].avg_rating).toFixed(2) : "0.00",
      review_count: stats[0].review_count || 0
    });
  } catch (err) {
    console.error("getReviewsByListing error:", err);
    return res.status(500).json({ error: "Internal server error fetching reviews." });
  }
}

// 3. Delete a Review (admin only)
export async function deleteReview(req, res) {
  const reviewId = req.params.id;
  const userRole = req.user.role;

  if (userRole !== 'super_admin' && userRole !== 'moderator') {
    return res.status(403).json({ error: "Access denied. Only administrators or moderators can delete reviews." });
  }

  try {
    const [review] = await pool.query('SELECT listing_id FROM reviews WHERE id = ?', [reviewId]);
    if (review.length === 0) {
      return res.status(404).json({ error: "Review not found." });
    }

    const listingId = review[0].listing_id;

    await pool.query('DELETE FROM reviews WHERE id = ?', [reviewId]);

    // Recalculate
    const [stats] = await pool.query(
      'SELECT AVG(rating) as avg_rating, COUNT(*) as review_count FROM reviews WHERE listing_id = ?',
      [listingId]
    );

    const avgRating = stats[0].avg_rating ? parseFloat(stats[0].avg_rating).toFixed(2) : "0.00";
    const reviewCount = stats[0].review_count || 0;

    await pool.query(
      'UPDATE listings SET rating = ?, reviews_count = ? WHERE id = ?',
      [avgRating, reviewCount, listingId]
    );

    return res.json({
      message: "Review deleted successfully.",
      rating: avgRating,
      reviews_count: reviewCount
    });
  } catch (err) {
    console.error("deleteReview error:", err);
    return res.status(500).json({ error: "Internal server error deleting review." });
  }
}
