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

// 1. Get Logged-in Merchant's Businesses
export async function getMyBusiness(req, res) {
  try {
    const ownerId = req.user.id;
    const query = 'SELECT * FROM listings WHERE owner_id = ? ORDER BY created_at DESC';
    const [rows] = await pool.query(query, [ownerId]);

    // Ensure internal admin remarks are NEVER leaked to the merchant/user
    const sanitizedListings = rows.map(r => {
      const { remarks, ...rest } = r;
      return rest;
    });

    return res.json({ listings: sanitizedListings });
  } catch (err) {
    console.error("getMyBusiness error:", err);
    return res.status(500).json({ error: "Internal server error fetching your registered businesses." });
  }
}

// Dynamic helper to check available columns in the listings table
let cachedListingColumns = null;
export async function getListingColumns() {
  if (!cachedListingColumns) {
    try {
      const [cols] = await pool.query('SHOW COLUMNS FROM listings');
      cachedListingColumns = new Set(cols.map(c => c.Field));
      
      const newCols = [
        { name: 'remarks', sql: 'ALTER TABLE listings ADD COLUMN remarks TEXT NULL' },
        { name: 'admin_note_to_user', sql: 'ALTER TABLE listings ADD COLUMN admin_note_to_user TEXT NULL' },
        { name: 'pending_update', sql: 'ALTER TABLE listings ADD COLUMN pending_update JSON NULL' },
        { name: 'pending_update_note', sql: 'ALTER TABLE listings ADD COLUMN pending_update_note TEXT NULL' },
        { name: 'edit_status', sql: "ALTER TABLE listings ADD COLUMN edit_status VARCHAR(50) DEFAULT 'none'" },
        { name: 'edit_submitted_at', sql: 'ALTER TABLE listings ADD COLUMN edit_submitted_at TIMESTAMP NULL' },
        { name: 'documents', sql: 'ALTER TABLE listings ADD COLUMN documents JSON NULL' }
      ];

      for (const col of newCols) {
        if (!cachedListingColumns.has(col.name)) {
          await pool.query(col.sql).catch(() => {});
          cachedListingColumns.add(col.name);
        }
      }

      // Ensure listing_edit_history table exists for complete chronological audit trail
      await pool.query(`
        CREATE TABLE IF NOT EXISTS listing_edit_history (
          id INT AUTO_INCREMENT PRIMARY KEY,
          listing_id INT NOT NULL,
          editor_id INT NULL,
          editor_name VARCHAR(150) NULL,
          editor_role VARCHAR(50) NOT NULL,
          action_type VARCHAR(50) NOT NULL,
          note TEXT NULL,
          data_snapshot JSON NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          INDEX idx_leh_listing (listing_id),
          INDEX idx_leh_created (created_at)
        ) ENGINE=InnoDB;
      `).catch(() => {});
    } catch (err) {
      console.error("Listing schema check error:", err);
      cachedListingColumns = new Set();
    }
  }
  return cachedListingColumns;
}

// Chronological listing edit history logger
export async function logListingHistory(listingId, user, actionType, note = null, snapshot = null) {
  try {
    await getListingColumns();
    const editorName = user ? [user.first_name, user.last_name].filter(Boolean).join(' ') || user.email || 'User' : 'System';
    const editorRole = user?.role || 'business_owner';
    const snapshotStr = snapshot ? (typeof snapshot === 'string' ? snapshot : JSON.stringify(snapshot)) : null;

    await pool.query(
      `INSERT INTO listing_edit_history (listing_id, editor_id, editor_name, editor_role, action_type, note, data_snapshot)
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [listingId, user?.id || null, editorName, editorRole, actionType, note || null, snapshotStr]
    );
  } catch (err) {
    console.error("logListingHistory error:", err.message);
  }
}

// Helper to record administrative audit events in audit_logs table
async function logAuditEvent(adminUserId, eventType, description, severity = 'Info', ipAddress = null) {
  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS audit_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        admin_user_id INT,
        event_type VARCHAR(100) NOT NULL,
        description TEXT,
        severity VARCHAR(20) DEFAULT 'Info',
        ip_address VARCHAR(45),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (admin_user_id) REFERENCES users(id) ON DELETE SET NULL
      ) ENGINE=InnoDB
    `).catch(() => {});

    await pool.query(
      'INSERT INTO audit_logs (admin_user_id, event_type, description, severity, ip_address) VALUES (?, ?, ?, ?, ?)',
      [adminUserId || null, eventType, description, severity, ipAddress || null]
    );
  } catch (err) {
    console.error("Audit log error:", err);
  }
}

// 2. Create Listing linked to Logged-in Merchant
export async function createListing(req, res) {
  const { 
    name, category, description, phone, email, address, location, hours, image, price,
    established_year, business_type, muslim_owned, sell_halal, sell_alcohol, sell_pork,
    sell_cigarettes, halal_certified, customer_restroom, prayer_space, offer_catering,
    presence, delivery, website, latitude, longitude,
    specialties, promo_title, promo_description, reservation_url
  } = req.body;
  const ownerId = req.user.id;

  if (!name || !category || !address || !location) {
    return res.status(400).json({ error: "Missing required listing fields: name, category, address, or location." });
  }

  try {
    const cols = await getListingColumns();
    const listingImage = image || null;

    // Base fields
    const fieldNames = [
      'name', 'category', 'description', 'phone', 'email', 'address', 'location', 'hours', 'image', 'price', 'owner_id', 'status',
      'established_year', 'business_type', 'muslim_owned', 'sell_halal', 'sell_alcohol', 'sell_pork', 'sell_cigarettes',
      'halal_certified', 'customer_restroom', 'prayer_space', 'offer_catering', 'presence', 'delivery', 'website', 'latitude', 'longitude'
    ];
    const fieldValues = [
      name, category, description || '', phone || '', email || '', address, location,
      hours || null, listingImage, price || null, ownerId, 'pending',
      established_year || null, business_type || '', muslim_owned || '', sell_halal || '',
      sell_alcohol || '', sell_pork || '', sell_cigarettes || '', halal_certified || '',
      customer_restroom || '', prayer_space || '', offer_catering || '', presence || '',
      delivery || '', website || '',
      latitude !== undefined ? latitude : null,
      longitude !== undefined ? longitude : null
    ];

    // Conditionally append new profile layout fields if columns exist
    if (cols.has('specialties')) {
      fieldNames.push('specialties');
      fieldValues.push(specialties || '');
    }
    if (cols.has('promo_title')) {
      fieldNames.push('promo_title');
      fieldValues.push(promo_title || '');
    }
    if (cols.has('promo_description')) {
      fieldNames.push('promo_description');
      fieldValues.push(promo_description || '');
    }
    if (cols.has('reservation_url')) {
      fieldNames.push('reservation_url');
      fieldValues.push(reservation_url || '');
    }
    if (cols.has('documents') && req.body.documents) {
      try {
        const docs = Array.isArray(req.body.documents)
          ? req.body.documents
          : (typeof req.body.documents === 'string' ? JSON.parse(req.body.documents) : []);
        fieldNames.push('documents');
        fieldValues.push(JSON.stringify(docs.slice(0, 10)));
      } catch (e) {
        // ignore parse error
      }
    }

    const placeholders = fieldNames.map(() => '?').join(', ');
    const insertQuery = `INSERT INTO listings (${fieldNames.join(', ')}) VALUES (${placeholders})`;

    const [insertResult] = await pool.query(insertQuery, fieldValues);
    const newId = insertResult.insertId;

    await logListingHistory(
      newId,
      req.user,
      'created',
      req.body.owner_note || 'Initial business listing submitted for review.',
      req.body
    );

    return res.status(201).json({ message: "Listing submitted for verification successfully.", id: newId });
  } catch (err) {
    console.error("createListing error:", err);
    return res.status(500).json({ error: "Internal server error creating listings profile." });
  }
}

// 3. Update Merchant's Listing with Staging & Strict Review Guards
export async function updateMyBusiness(req, res) {
  const listingId = req.params.id;
  const ownerId = req.user.id;
  const { 
    name, category, description, phone, email, address, location, hours, image, price,
    established_year, business_type, muslim_owned, sell_halal, sell_alcohol, sell_pork,
    sell_cigarettes, halal_certified, customer_restroom, prayer_space, offer_catering,
    presence, delivery, website, latitude, longitude,
    specialties, promo_title, promo_description, reservation_url, owner_note
  } = req.body;

  if (!name || !category || !address || !location) {
    return res.status(400).json({ error: "Missing required listing updates: name, category, address, or location." });
  }

  try {
    const isElevated = req.user.role === 'super_admin' || req.user.role === 'moderator';
    let current;
    if (isElevated) {
      [current] = await pool.query('SELECT * FROM listings WHERE id = ?', [listingId]);
    } else {
      [current] = await pool.query('SELECT * FROM listings WHERE id = ? AND owner_id = ?', [listingId, ownerId]);
    }
    if (current.length === 0) {
      return res.status(404).json({ error: "Listing profile not found." });
    }

    const currentRecord = current[0];

    // ==========================================
    // BUSINESS OWNER EDITING GUARDS
    // ==========================================
    if (!isElevated) {
      // RULE 1: Cannot edit once business is in review
      if (currentRecord.status === 'reviewing') {
        return res.status(403).json({
          error: "This business is currently under review by our moderation team. Editing is locked until review completes."
        });
      }

      // RULE 2: If an update is already submitted and pending or in review, cannot submit another edit
      if (currentRecord.edit_status === 'pending' || currentRecord.edit_status === 'reviewing') {
        return res.status(403).json({
          error: "An update for this business is already under administrative review. Editing is locked until review completes."
        });
      }

      // RULE 3: If business is already approved (live on directory), STAGE the update!
      // Live listing remains active with existing data. Staged update goes to admin for review.
      if (currentRecord.status === 'approved') {
        const updatePayload = {
          name, category, description: description || '', phone: phone || '', email: email || '',
          address, location, hours: hours || '', image: image || '', price: price || '',
          established_year: established_year || null, business_type: business_type || '',
          muslim_owned: muslim_owned || '', sell_halal: sell_halal || '',
          sell_alcohol: sell_alcohol || '', sell_pork: sell_pork || '',
          sell_cigarettes: sell_cigarettes || '', halal_certified: halal_certified || '',
          customer_restroom: customer_restroom || '', prayer_space: prayer_space || '',
          offer_catering: offer_catering || '', presence: presence || '',
          delivery: delivery || '', website: website || '',
          latitude: latitude !== undefined ? latitude : null,
          longitude: longitude !== undefined ? longitude : null,
          specialties: specialties || '', promo_title: promo_title || '',
          promo_description: promo_description || '', reservation_url: reservation_url || ''
        };

        let docsArray = [];
        if (req.body.documents !== undefined) {
          try {
            docsArray = Array.isArray(req.body.documents)
              ? req.body.documents
              : (typeof req.body.documents === 'string' ? JSON.parse(req.body.documents) : []);
          } catch (e) {}
        } else if (currentRecord.documents) {
          try {
            docsArray = typeof currentRecord.documents === 'string' ? JSON.parse(currentRecord.documents) : currentRecord.documents;
          } catch (e) {}
        }
        updatePayload.documents = docsArray.slice(0, 10);

        await pool.query(`
          UPDATE listings
          SET pending_update = ?,
              pending_update_note = ?,
              edit_status = 'pending',
              edit_submitted_at = CURRENT_TIMESTAMP
          WHERE id = ? AND owner_id = ?
        `, [JSON.stringify(updatePayload), owner_note || null, listingId, ownerId]);

        await logListingHistory(
          listingId,
          req.user,
          'submitted_update',
          owner_note || 'Submitted update for live verified business.',
          updatePayload
        );

        return res.json({
          message: "Update submitted for administrative review. Your verified listing remains live with your existing data until approved.",
          status: 'approved',
          edit_status: 'pending'
        });
      }
    }

    // ==========================================
    // DIRECT UPDATE (For Pending, Rejected, or Admin edits)
    // ==========================================
    const newStatus = isElevated
      ? (req.body.status || currentRecord.status)
      : 'pending'; // When owner edits a rejected or pending listing, status is 'pending'

    const cols = await getListingColumns();
    const setClauses = [
      'name = ?', 'category = ?', 'description = ?', 'phone = ?', 'email = ?', 'address = ?', 'location = ?', 
      'hours = ?', 'image = ?', 'price = ?', 'status = ?',
      'established_year = ?', 'business_type = ?', 'muslim_owned = ?', 'sell_halal = ?', 'sell_alcohol = ?', 'sell_pork = ?',
      'sell_cigarettes = ?', 'halal_certified = ?', 'customer_restroom = ?', 'prayer_space = ?', 'offer_catering = ?',
      'presence = ?', 'delivery = ?', 'website = ?', 'latitude = ?', 'longitude = ?',
      'pending_update = NULL', 'pending_update_note = NULL', 'edit_status = "none"'
    ];
    const updateValues = [
      name, category, description, phone, email, address, location, hours, image, price, newStatus,
      established_year || null, business_type || '', muslim_owned || '', sell_halal || '',
      sell_alcohol || '', sell_pork || '', sell_cigarettes || '', halal_certified || '',
      customer_restroom || '', prayer_space || '', offer_catering || '', presence || '',
      delivery || '', website || '',
      latitude !== undefined ? latitude : null,
      longitude !== undefined ? longitude : null
    ];

    if (cols.has('specialties')) {
      setClauses.push('specialties = ?');
      updateValues.push(specialties || '');
    }
    if (cols.has('promo_title')) {
      setClauses.push('promo_title = ?');
      updateValues.push(promo_title || '');
    }
    if (cols.has('promo_description')) {
      setClauses.push('promo_description = ?');
      updateValues.push(promo_description || '');
    }
    if (cols.has('reservation_url')) {
      setClauses.push('reservation_url = ?');
      updateValues.push(reservation_url || '');
    }
    if (cols.has('documents') && req.body.documents !== undefined) {
      try {
        const docs = Array.isArray(req.body.documents)
          ? req.body.documents
          : (typeof req.body.documents === 'string' ? JSON.parse(req.body.documents) : []);
        setClauses.push('documents = ?');
        updateValues.push(JSON.stringify(docs.slice(0, 10)));
      } catch (e) {
        setClauses.push('documents = ?');
        updateValues.push(null);
      }
    }
    if (currentRecord.status === 'rejected' && !isElevated) {
      setClauses.push('remarks = NULL');
    }

    if (isElevated) {
      updateValues.push(listingId);
    } else {
      updateValues.push(listingId, ownerId);
    }

    const updateQuery = `
      UPDATE listings 
      SET ${setClauses.join(', ')}
      WHERE id = ? ${isElevated ? '' : 'AND owner_id = ?'}
    `;

    await pool.query(updateQuery, updateValues);

    const historyAction = isElevated 
      ? 'admin_edit' 
      : (currentRecord.status === 'rejected' ? 'resubmitted' : 'edited_pending');
    const historyNote = owner_note || (
      isElevated 
        ? `Administrator edited business details.` 
        : (currentRecord.status === 'rejected' ? 'Resubmitted listing after rejection.' : 'Updated pending listing details.')
    );

    await logListingHistory(
      listingId,
      req.user,
      historyAction,
      historyNote,
      req.body
    );

    if (isElevated) {
      const prevStatus = currentRecord.status;
      const adminName = [req.user.first_name, req.user.last_name].filter(Boolean).join(' ') || req.user.email || 'Administrator';
      await logAuditEvent(
        req.user.id,
        'Listing Edited',
        `Admin ${adminName} edited business #${listingId} ("${name}") while status was "${prevStatus}".`,
        prevStatus !== 'approved' ? 'Warning' : 'Info',
        req.ip
      );
    }

    return res.json({
      message: "Listing profile successfully updated.",
      status: newStatus,
      edit_status: 'none'
    });
  } catch (err) {
    console.error("updateMyBusiness error:", err);
    return res.status(500).json({ error: "Internal server error updating listing details." });
  }
}

// 4. Get Single Listing by ID (public)
export async function getListingById(req, res) {
  const listingId = req.params.id;

  try {
    const query = `
      SELECT l.*, u.first_name as owner_first, u.last_name as owner_last 
      FROM listings l
      LEFT JOIN users u ON l.owner_id = u.id
      WHERE l.id = ?
    `;
    const [rows] = await pool.query(query, [listingId]);

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

    return res.json({ listing: rows[0] });
  } catch (err) {
    console.error("getListingById error:", err);
    return res.status(500).json({ error: "Internal server error fetching listing." });
  }
}

// 5. Get Public Approved Listings
export async function getPublicListings(req, res) {
  try {
    const query = `
      SELECT l.*, 
             COALESCE(rev.cnt, 0) as reviews_count,
             COALESCE(rev.avg_r, 0.00) as rating,
             u.first_name as owner_first, u.last_name as owner_last 
      FROM listings l
      LEFT JOIN (
        SELECT listing_id, COUNT(*) as cnt, ROUND(AVG(rating), 2) as avg_r
        FROM reviews
        GROUP BY listing_id
      ) rev ON l.id = rev.listing_id
      LEFT JOIN users u ON l.owner_id = u.id
      WHERE l.status = 'approved'
      ORDER BY rating DESC, reviews_count DESC
    `;
    const [rows] = await pool.query(query);
    return res.json({ listings: rows });
  } catch (err) {
    console.error("getPublicListings error:", err);
    return res.status(500).json({ error: "Internal server error fetching directory listings." });
  }
}

// 6. Get All Listings (Admin / Moderator) with server-side filters & pagination
export async function getAllListingsAdmin(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to view all listings." });
    }

    const { page, limit, search, category, city, zipcode, state, status, export: isExport } = req.query;

    const whereClauses = ['1=1'];
    const queryParams = [];

    if (search && search.trim()) {
      const term = `%${search.trim()}%`;
      whereClauses.push('(l.name LIKE ? OR l.description LIKE ? OR l.address LIKE ? OR l.location LIKE ? OR u.first_name LIKE ? OR u.last_name LIKE ? OR u.email LIKE ?)');
      queryParams.push(term, term, term, term, term, term, term);
    }

    if (category && category.trim() && category !== 'All Entries') {
      whereClauses.push('(l.category = ? OR l.category LIKE ?)');
      queryParams.push(category.trim(), `%${category.trim()}%`);
    }

    if (city && city.trim()) {
      whereClauses.push('(l.location LIKE ? OR l.address LIKE ?)');
      queryParams.push(`%${city.trim()}%`, `%${city.trim()}%`);
    }

    if (zipcode && zipcode.trim()) {
      whereClauses.push('l.address LIKE ?');
      queryParams.push(`%${zipcode.trim()}%`);
    }

    if (state && state.trim()) {
      whereClauses.push('(l.location LIKE ? OR l.address LIKE ?)');
      queryParams.push(`%${state.trim()}%`, `%${state.trim()}%`);
    }

    if (status && status.trim() && status !== 'all') {
      if (status === 'has_update') {
        whereClauses.push("(l.pending_update IS NOT NULL OR l.edit_status = 'pending' OR l.edit_status = 'reviewing')");
      } else {
        whereClauses.push('l.status = ?');
        queryParams.push(status.trim());
      }
    }

    const whereSql = whereClauses.join(' AND ');

    // If export is requested, return all matched rows without pagination
    if (isExport === 'true' || isExport === true) {
      const exportQuery = `
        SELECT l.*, u.first_name as owner_first, u.last_name as owner_last, u.email as owner_email 
        FROM listings l
        LEFT JOIN users u ON l.owner_id = u.id
        WHERE ${whereSql}
        ORDER BY l.created_at DESC
      `;
      const [rows] = await pool.query(exportQuery, queryParams);
      return res.json({ listings: rows, total: rows.length });
    }

    // Get total matching count
    const countQuery = `
      SELECT COUNT(*) as total
      FROM listings l
      LEFT JOIN users u ON l.owner_id = u.id
      WHERE ${whereSql}
    `;
    const [countResult] = await pool.query(countQuery, queryParams);
    const total = countResult[0]?.total || 0;

    const pageNum = Math.max(1, parseInt(page) || 1);
    const limitNum = Math.max(1, parseInt(limit) || 10);
    const offset = (pageNum - 1) * limitNum;

    const listQuery = `
      SELECT l.*, u.first_name as owner_first, u.last_name as owner_last, u.email as owner_email 
      FROM listings l
      LEFT JOIN users u ON l.owner_id = u.id
      WHERE ${whereSql}
      ORDER BY l.created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    const listParams = [...queryParams, limitNum, offset];
    const [rows] = await pool.query(listQuery, listParams);

    return res.json({
      listings: rows,
      pagination: {
        total,
        page: pageNum,
        limit: limitNum,
        totalPages: Math.ceil(total / limitNum) || 1
      }
    });
  } catch (err) {
    console.error("getAllListingsAdmin error:", err);
    return res.status(500).json({ error: "Internal server error fetching admin audit listings." });
  }
}

// 7. Admin Stats Aggregate
export async function getAdminStats(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied." });
    }

    const [listingStats] = await pool.query(`
      SELECT 
        COUNT(*) as total_listings,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
        SUM(CASE WHEN status = 'reviewing' THEN 1 ELSE 0 END) as reviewing_count,
        SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved_count,
        SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected_count
      FROM listings
    `);

    const [userStats] = await pool.query('SELECT COUNT(*) as total_users FROM users');
    const [reviewStats] = await pool.query('SELECT COUNT(*) as total_reviews FROM reviews');

    // Recent submissions (last 10)
    const [recentListings] = await pool.query(`
      SELECT l.id, l.name, l.category, l.status, l.created_at, 
             u.first_name as owner_first, u.last_name as owner_last
      FROM listings l
      LEFT JOIN users u ON l.owner_id = u.id
      ORDER BY l.created_at DESC
      LIMIT 10
    `);

    return res.json({
      stats: {
        total_listings: listingStats[0].total_listings || 0,
        pending_count: listingStats[0].pending_count || 0,
        reviewing_count: listingStats[0].reviewing_count || 0,
        approved_count: listingStats[0].approved_count || 0,
        rejected_count: listingStats[0].rejected_count || 0,
        total_users: userStats[0].total_users || 0,
        total_reviews: reviewStats[0].total_reviews || 0
      },
      recent_listings: recentListings
    });
  } catch (err) {
    console.error("getAdminStats error:", err);
    return res.status(500).json({ error: "Internal server error fetching admin stats." });
  }
}

// 8. Approve Listing (Admin / Moderator)
export async function approveListing(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to moderate listings." });
    }

    const listingId = req.params.id;
    const adminNote = req.body?.admin_note || 'Approved by administrator.';
    const [result] = await pool.query("UPDATE listings SET status = 'approved', admin_note_to_user = NULL, edit_status = 'none' WHERE id = ?", [listingId]);

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Listing not found." });
    }

    const adminName = [req.user.first_name, req.user.last_name].filter(Boolean).join(' ') || req.user.email || 'Administrator';
    await logListingHistory(listingId, req.user, 'approved', adminNote);
    await logAuditEvent(
      req.user.id,
      'Listing Approved',
      `Admin ${adminName} approved business listing #${listingId}. Note: ${adminNote}`,
      'Info',
      req.ip
    );

    return res.json({ message: "Listing status successfully updated to approved." });
  } catch (err) {
    console.error("approveListing error:", err);
    return res.status(500).json({ error: "Internal server error updating listing audit status." });
  }
}

// 9. Reject Listing (Admin / Moderator)
export async function rejectListing(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to moderate listings." });
    }

    const listingId = req.params.id;
    const rejectionNote = req.body?.admin_note || req.body?.admin_note_to_user || 'Listing rejected by administrator.';
    const [result] = await pool.query("UPDATE listings SET status = 'rejected', admin_note_to_user = ?, edit_status = 'none' WHERE id = ?", [rejectionNote, listingId]);

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Listing not found." });
    }

    const adminName = [req.user.first_name, req.user.last_name].filter(Boolean).join(' ') || req.user.email || 'Administrator';
    await logListingHistory(listingId, req.user, 'rejected', rejectionNote);
    await logAuditEvent(
      req.user.id,
      'Listing Rejected',
      `Admin ${adminName} rejected business listing #${listingId}. Reason: ${rejectionNote}`,
      'Warning',
      req.ip
    );

    return res.json({ message: "Listing status successfully updated to rejected." });
  } catch (err) {
    console.error("rejectListing error:", err);
    return res.status(500).json({ error: "Internal server error updating listing audit status." });
  }
}

// 10. Update Listing Status (Admin / Moderator) - Supports 'pending', 'reviewing', 'approved', 'rejected'
export async function updateListingStatusAdmin(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to moderate listings." });
    }

    const listingId = req.params.id;
    const { status, admin_note } = req.body;
    const validStatuses = ['pending', 'reviewing', 'approved', 'rejected'];

    if (!status || !validStatuses.includes(status)) {
      return res.status(400).json({ error: `Invalid status. Must be one of: ${validStatuses.join(', ')}` });
    }

    let updateSql = "UPDATE listings SET status = ? WHERE id = ?";
    let params = [status, listingId];

    if (status === 'rejected') {
      updateSql = "UPDATE listings SET status = ?, admin_note_to_user = ? WHERE id = ?";
      params = [status, admin_note || 'Flagged by administrator.', listingId];
    } else if (status === 'approved') {
      updateSql = "UPDATE listings SET status = ?, admin_note_to_user = NULL, edit_status = 'none' WHERE id = ?";
      params = [status, listingId];
    }

    const [result] = await pool.query(updateSql, params);

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Listing not found." });
    }

    const actionType = status === 'reviewing' ? 'moved_to_review' : status;
    await logListingHistory(listingId, req.user, actionType, admin_note || `Status updated to ${status}.`);

    const adminName = [req.user.first_name, req.user.last_name].filter(Boolean).join(' ') || req.user.email || 'Administrator';
    await logAuditEvent(
      req.user.id,
      `Listing ${status.charAt(0).toUpperCase() + status.slice(1)}`,
      `Admin ${adminName} changed business listing #${listingId} status to "${status}".`,
      status === 'rejected' ? 'Warning' : 'Info',
      req.ip
    );

    return res.json({ message: `Listing status successfully updated to ${status}.`, status });
  } catch (err) {
    console.error("updateListingStatusAdmin error:", err);
    return res.status(500).json({ error: "Internal server error updating listing audit status." });
  }
}

// 11. Approve Staged Business Update (Admin / Moderator)
export async function approveListingUpdate(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to moderate listings." });
    }

    const listingId = req.params.id;
    const [rows] = await pool.query('SELECT * FROM listings WHERE id = ?', [listingId]);
    if (rows.length === 0) {
      return res.status(404).json({ error: "Listing not found." });
    }

    const listing = rows[0];
    if (!listing.pending_update) {
      return res.status(400).json({ error: "No pending update found for this listing." });
    }

    let updateData = {};
    try {
      updateData = typeof listing.pending_update === 'string' ? JSON.parse(listing.pending_update) : listing.pending_update;
    } catch (e) {
      return res.status(500).json({ error: "Failed to parse staged update data." });
    }

    const cols = await getListingColumns();
    const setClauses = [
      'name = ?', 'category = ?', 'description = ?', 'phone = ?', 'email = ?', 'address = ?', 'location = ?',
      'hours = ?', 'image = ?', 'price = ?',
      'established_year = ?', 'business_type = ?', 'muslim_owned = ?', 'sell_halal = ?', 'sell_alcohol = ?', 'sell_pork = ?',
      'sell_cigarettes = ?', 'halal_certified = ?', 'customer_restroom = ?', 'prayer_space = ?', 'offer_catering = ?',
      'presence = ?', 'delivery = ?', 'website = ?', 'latitude = ?', 'longitude = ?',
      'pending_update = NULL', 'pending_update_note = NULL', 'edit_status = "none"', 'admin_note_to_user = NULL'
    ];
    const updateValues = [
      updateData.name || listing.name,
      updateData.category || listing.category,
      updateData.description || '',
      updateData.phone || '',
      updateData.email || '',
      updateData.address || listing.address,
      updateData.location || listing.location,
      updateData.hours || '',
      updateData.image || '',
      updateData.price !== undefined ? (updateData.price || null) : (listing.price || null),
      updateData.established_year || null,
      updateData.business_type || '',
      updateData.muslim_owned || '',
      updateData.sell_halal || '',
      updateData.sell_alcohol || '',
      updateData.sell_pork || '',
      updateData.sell_cigarettes || '',
      updateData.halal_certified || '',
      updateData.customer_restroom || '',
      updateData.prayer_space || '',
      updateData.offer_catering || '',
      updateData.presence || '',
      updateData.delivery || '',
      updateData.website || '',
      updateData.latitude !== undefined ? updateData.latitude : null,
      updateData.longitude !== undefined ? updateData.longitude : null
    ];

    if (cols.has('specialties')) {
      setClauses.push('specialties = ?');
      updateValues.push(updateData.specialties || '');
    }
    if (cols.has('promo_title')) {
      setClauses.push('promo_title = ?');
      updateValues.push(updateData.promo_title || '');
    }
    if (cols.has('promo_description')) {
      setClauses.push('promo_description = ?');
      updateValues.push(updateData.promo_description || '');
    }
    if (cols.has('reservation_url')) {
      setClauses.push('reservation_url = ?');
      updateValues.push(updateData.reservation_url || '');
    }
    if (cols.has('documents') && updateData.documents !== undefined) {
      setClauses.push('documents = ?');
      updateValues.push(updateData.documents ? JSON.stringify(updateData.documents) : null);
    }

    updateValues.push(listingId);
    await pool.query(`UPDATE listings SET ${setClauses.join(', ')} WHERE id = ?`, updateValues);

    const adminName = [req.user.first_name, req.user.last_name].filter(Boolean).join(' ') || req.user.email || 'Administrator';
    const note = req.body?.admin_note || 'Approved staged business update. Live directory data updated.';

    await logListingHistory(listingId, req.user, 'update_approved', note, updateData);
    await logAuditEvent(req.user.id, 'Listing Update Approved', `Admin ${adminName} approved staged update for listing #${listingId}.`, 'Info', req.ip);

    return res.json({ message: "Listing update approved and live directory record updated successfully." });
  } catch (err) {
    console.error("approveListingUpdate error:", err);
    return res.status(500).json({ error: "Internal server error approving listing update." });
  }
}

// 12. Reject Staged Business Update (Admin / Moderator)
export async function rejectListingUpdate(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to moderate listings." });
    }

    const listingId = req.params.id;
    const rejectionNote = req.body?.admin_note || req.body?.admin_note_to_user || 'Staged update rejected by administrator.';

    const [result] = await pool.query(
      "UPDATE listings SET pending_update = NULL, pending_update_note = NULL, edit_status = 'none', admin_note_to_user = ? WHERE id = ?",
      [rejectionNote, listingId]
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Listing not found." });
    }

    const adminName = [req.user.first_name, req.user.last_name].filter(Boolean).join(' ') || req.user.email || 'Administrator';
    await logListingHistory(listingId, req.user, 'update_rejected', rejectionNote);
    await logAuditEvent(req.user.id, 'Listing Update Rejected', `Admin ${adminName} rejected staged update for listing #${listingId}.`, 'Warning', req.ip);

    return res.json({ message: "Staged update rejected. Live listing data was preserved." });
  } catch (err) {
    console.error("rejectListingUpdate error:", err);
    return res.status(500).json({ error: "Internal server error rejecting listing update." });
  }
}

// 13. Move Staged Business Update to Reviewing (Admin / Moderator)
export async function setListingUpdateReviewing(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to moderate listings." });
    }

    const listingId = req.params.id;
    const note = req.body?.admin_note || 'Administrator started review of proposed update.';

    const [result] = await pool.query("UPDATE listings SET edit_status = 'reviewing' WHERE id = ?", [listingId]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Listing not found." });
    }

    await logListingHistory(listingId, req.user, 'update_in_review', note);

    return res.json({ message: "Listing update moved to reviewing status.", edit_status: 'reviewing' });
  } catch (err) {
    console.error("setListingUpdateReviewing error:", err);
    return res.status(500).json({ error: "Internal server error updating review status." });
  }
}

// 14. Get Chronological Listing History (Admin / Moderator)
export async function getListingHistoryAdmin(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to view listing history." });
    }

    const listingId = req.params.id;
    await getListingColumns();

    const [rows] = await pool.query(
      "SELECT * FROM listing_edit_history WHERE listing_id = ? ORDER BY created_at DESC",
      [listingId]
    );

    return res.json({ history: rows });
  } catch (err) {
    console.error("getListingHistoryAdmin error:", err);
    return res.status(500).json({ error: "Internal server error fetching chronological listing history." });
  }
}

// 11. Update Listing Internal Remarks (Admin / Moderator)
export async function updateListingRemarksAdmin(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to update remarks." });
    }

    const listingId = req.params.id;
    const { remarks } = req.body;

    await getListingColumns();
    const [result] = await pool.query("UPDATE listings SET remarks = ? WHERE id = ?", [remarks || '', listingId]);

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Listing not found." });
    }

    const adminName = [req.user.first_name, req.user.last_name].filter(Boolean).join(' ') || req.user.email || 'Administrator';
    await logAuditEvent(
      req.user.id,
      'Remarks Updated',
      `Admin ${adminName} updated internal remarks for business listing #${listingId}.`,
      'Info',
      req.ip
    );

    return res.json({ message: "Remarks saved successfully.", remarks: remarks || '' });
  } catch (err) {
    console.error("updateListingRemarksAdmin error:", err);
    return res.status(500).json({ error: "Internal server error updating remarks." });
  }
}

// 12. Delete Listing (Admin / Moderator)
export async function deleteListingAdmin(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to delete listings." });
    }

    const listingId = req.params.id;

    // Clean up dependent reviews if any exist
    await pool.query("DELETE FROM reviews WHERE listing_id = ?", [listingId]).catch(() => {});

    const [result] = await pool.query("DELETE FROM listings WHERE id = ?", [listingId]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Listing not found." });
    }

    const adminName = [req.user.first_name, req.user.last_name].filter(Boolean).join(' ') || req.user.email || 'Administrator';
    await logAuditEvent(
      req.user.id,
      'Listing Deleted',
      `Admin ${adminName} permanently deleted listing #${listingId}.`,
      'Warning',
      req.ip
    );

    return res.json({ message: "Listing permanently deleted successfully." });
  } catch (err) {
    console.error("deleteListingAdmin error:", err);
    return res.status(500).json({ error: "Internal server error deleting listing." });
  }
}

// 13. Get Audit Logs (Admin / Moderator) with Server-Side Filtering, Pagination & Live KPI Stats
export async function getAuditLogsAdmin(req, res) {
  try {
    const role = (req.user?.role || '').toLowerCase();
    if (!['super_admin', 'admin', 'moderator'].includes(role)) {
      return res.status(403).json({ error: "Access denied." });
    }

    await pool.query(`
      CREATE TABLE IF NOT EXISTS audit_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        admin_user_id INT,
        event_type VARCHAR(100) NOT NULL,
        description TEXT,
        severity VARCHAR(20) DEFAULT 'Info',
        ip_address VARCHAR(45),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (admin_user_id) REFERENCES users(id) ON DELETE SET NULL
      ) ENGINE=InnoDB
    `).catch(() => {});

    // Parse filters
    const page = Math.max(1, parseInt(req.query.page || 1, 10));
    const limit = Math.max(1, Math.min(100, parseInt(req.query.limit || 50, 10)));
    const search = (req.query.search || '').trim();
    const severity = (req.query.severity || '').trim();
    const event_type = (req.query.event_type || '').trim();
    const timeframe = (req.query.timeframe || '').trim();

    const conditions = [];
    const params = [];

    // Severity filter
    if (severity && severity !== 'All Severities') {
      conditions.push('a.severity = ?');
      params.push(severity);
    }

    // Event Type filter
    if (event_type && event_type !== 'All Events') {
      if (event_type === 'Tasks') {
        conditions.push("a.event_type LIKE 'TASK_%'");
      } else if (event_type === 'Listings') {
        conditions.push("(a.event_type LIKE '%LISTING%' OR a.event_type LIKE '%BUSINESS%')");
      } else if (event_type === 'User Admin') {
        conditions.push("(a.event_type LIKE '%USER%' OR a.event_type LIKE '%ROLE%' OR a.event_type LIKE '%ACCOUNT%')");
      } else if (event_type === 'Security') {
        conditions.push("(a.event_type LIKE '%AUTH%' OR a.event_type LIKE '%LOGIN%' OR a.event_type LIKE '%PASSWORD%' OR a.event_type LIKE '%SECURITY%')");
      }
    }

    // Timeframe filter
    if (timeframe === 'today') {
      conditions.push("DATE(a.created_at) = CURDATE()");
    } else if (timeframe === '7d') {
      conditions.push("a.created_at >= NOW() - INTERVAL 7 DAY");
    } else if (timeframe === '30d') {
      conditions.push("a.created_at >= NOW() - INTERVAL 30 DAY");
    }

    // Search filter
    if (search) {
      const term = `%${search}%`;
      conditions.push("(a.description LIKE ? OR a.event_type LIKE ? OR u.first_name LIKE ? OR u.last_name LIKE ? OR u.email LIKE ?)");
      params.push(term, term, term, term, term);
    }

    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

    // Total count matching filters
    const [countRows] = await pool.query(
      `SELECT COUNT(*) AS total FROM audit_logs a LEFT JOIN users u ON a.admin_user_id = u.id ${whereClause}`,
      params
    );
    const totalCount = countRows[0]?.total || 0;

    // Paginated records
    const offset = (page - 1) * limit;
    const [rows] = await pool.query(
      `SELECT a.*, u.first_name, u.last_name, u.email
       FROM audit_logs a
       LEFT JOIN users u ON a.admin_user_id = u.id
       ${whereClause}
       ORDER BY a.created_at DESC
       LIMIT ? OFFSET ?`,
      [...params, limit, offset]
    );

    // Live KPI Stats
    const [[{ total_24h = 0 } = {}]] = await pool.query(
      `SELECT COUNT(*) as total_24h FROM audit_logs WHERE created_at >= NOW() - INTERVAL 24 HOUR`
    ).catch(() => [[{ total_24h: 0 }]]);

    const [[{ total_prev_24h = 0 } = {}]] = await pool.query(
      `SELECT COUNT(*) as total_prev_24h FROM audit_logs WHERE created_at >= NOW() - INTERVAL 48 HOUR AND created_at < NOW() - INTERVAL 24 HOUR`
    ).catch(() => [[{ total_prev_24h: 0 }]]);

    const [[{ critical_count = 0 } = {}]] = await pool.query(
      `SELECT COUNT(*) as critical_count FROM audit_logs WHERE severity IN ('Critical', 'Warning') AND created_at >= NOW() - INTERVAL 24 HOUR`
    ).catch(() => [[{ critical_count: 0 }]]);

    const [[{ active_admins = 0 } = {}]] = await pool.query(
      `SELECT COUNT(DISTINCT admin_user_id) as active_admins FROM audit_logs WHERE created_at >= NOW() - INTERVAL 24 HOUR AND admin_user_id IS NOT NULL`
    ).catch(() => [[{ active_admins: 0 }]]);

    const [[{ auth_attempts = 0 } = {}]] = await pool.query(
      `SELECT COUNT(*) as auth_attempts FROM audit_logs WHERE (event_type LIKE '%AUTH%' OR event_type LIKE '%LOGIN%' OR event_type LIKE '%SECURITY%' OR event_type LIKE '%USER%') AND created_at >= NOW() - INTERVAL 24 HOUR`
    ).catch(() => [[{ auth_attempts: 0 }]]);

    let growthStr = "+0%";
    if (total_prev_24h > 0) {
      const pct = Math.round(((total_24h - total_prev_24h) / total_prev_24h) * 100);
      growthStr = `${pct >= 0 ? '+' : ''}${pct}%`;
    } else if (total_24h > 0) {
      growthStr = "+100%";
    }

    return res.json({
      logs: rows,
      pagination: {
        currentPage: page,
        totalPages: Math.max(1, Math.ceil(totalCount / limit)),
        totalCount,
        pageSize: limit
      },
      stats: {
        total_24h,
        growth_24h: growthStr,
        critical_count,
        active_admins: Math.max(1, active_admins), // at least the current admin
        auth_attempts
      }
    });
  } catch (err) {
    console.error("getAuditLogsAdmin error:", err);
    return res.status(500).json({ error: "Internal server error fetching audit logs." });
  }
}

// 14. Export Audit Logs to CSV
export async function exportAuditLogsCSV(req, res) {
  try {
    const role = (req.user?.role || '').toLowerCase();
    if (!['super_admin', 'admin', 'moderator'].includes(role)) {
      return res.status(403).json({ error: "Access denied." });
    }

    const search = (req.query.search || '').trim();
    const severity = (req.query.severity || '').trim();
    const event_type = (req.query.event_type || '').trim();
    const timeframe = (req.query.timeframe || '').trim();

    const conditions = [];
    const params = [];

    if (severity && severity !== 'All Severities') {
      conditions.push('a.severity = ?');
      params.push(severity);
    }

    if (event_type && event_type !== 'All Events') {
      if (event_type === 'Tasks') {
        conditions.push("a.event_type LIKE 'TASK_%'");
      } else if (event_type === 'Listings') {
        conditions.push("(a.event_type LIKE '%LISTING%' OR a.event_type LIKE '%BUSINESS%')");
      } else if (event_type === 'User Admin') {
        conditions.push("(a.event_type LIKE '%USER%' OR a.event_type LIKE '%ROLE%' OR a.event_type LIKE '%ACCOUNT%')");
      } else if (event_type === 'Security') {
        conditions.push("(a.event_type LIKE '%AUTH%' OR a.event_type LIKE '%LOGIN%' OR a.event_type LIKE '%PASSWORD%' OR a.event_type LIKE '%SECURITY%')");
      }
    }

    if (timeframe === 'today') {
      conditions.push("DATE(a.created_at) = CURDATE()");
    } else if (timeframe === '7d') {
      conditions.push("a.created_at >= NOW() - INTERVAL 7 DAY");
    } else if (timeframe === '30d') {
      conditions.push("a.created_at >= NOW() - INTERVAL 30 DAY");
    }

    if (search) {
      const term = `%${search}%`;
      conditions.push("(a.description LIKE ? OR a.event_type LIKE ? OR u.first_name LIKE ? OR u.last_name LIKE ? OR u.email LIKE ?)");
      params.push(term, term, term, term, term);
    }

    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

    const [rows] = await pool.query(
      `SELECT a.id, a.created_at, u.first_name, u.last_name, u.email, a.event_type, a.description, a.severity, a.ip_address
       FROM audit_logs a
       LEFT JOIN users u ON a.admin_user_id = u.id
       ${whereClause}
       ORDER BY a.created_at DESC
       LIMIT 2000`,
      params
    );

    // Build CSV
    const headers = ["Log ID", "Timestamp", "Admin Name", "Admin Email", "Event Type", "Description", "Severity", "IP Address"];
    const csvLines = [headers.join(',')];

    for (const r of rows) {
      const name = [r.first_name, r.last_name].filter(Boolean).join(' ') || 'Admin';
      const cleanDesc = (r.description || '').replace(/"/g, '""');
      csvLines.push([
        r.id,
        `"${new Date(r.created_at).toISOString()}"`,
        `"${name}"`,
        `"${r.email || ''}"`,
        `"${r.event_type || ''}"`,
        `"${cleanDesc}"`,
        `"${r.severity || 'Info'}"`,
        `"${r.ip_address || ''}"`
      ].join(','));
    }

    const csvContent = csvLines.join('\n');
    res.setHeader('Content-Type', 'text/csv');
    res.setHeader('Content-Disposition', `attachment; filename="system_audit_logs_${new Date().toISOString().slice(0, 10)}.csv"`);
    return res.send(csvContent);
  } catch (err) {
    console.error("exportAuditLogsCSV error:", err);
    return res.status(500).json({ error: "Failed to export CSV." });
  }
}


