const pool = require('../config/database');

// Get all media
const getAllMedia = async (req, res) => {
  try {
    const query = `
      SELECT 
        m.*,
        p.local_name as plant_name,
        l.village_name
      FROM entry_media m
      LEFT JOIN survey_entries se ON m.entry_id = se.entry_id
      LEFT JOIN plants p ON se.plant_id = p.plant_id
      LEFT JOIN locations l ON se.location_id = l.location_id
      ORDER BY m.media_id DESC
    `;
    
    const [rows] = await pool.execute(query);
    
    res.json({
      success: true,
      data: rows,
      count: rows.length
    });
  } catch (error) {
    console.error('Error fetching media:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch media'
    });
  }
};

// Get media by type
const getMediaByType = async (req, res) => {
  try {
    const { type } = req.params;
    const validTypes = ['Photo', 'Audio', 'Logo', 'Home']; // Added Home type
    
    if (!validTypes.includes(type)) {
      return res.status(400).json({
        success: false,
        error: 'Invalid media type. Valid types: Photo, Audio, Logo, Home'
      });
    }

    let query;
    if (type === 'Logo' || type === 'Home') {
      // For Logo and Home, we might not have entry_id linked to plants
      query = `
        SELECT m.*
        FROM entry_media m
        WHERE m.media_type = ?
        ORDER BY m.media_id DESC
      `;
    } else {
      query = `
        SELECT 
          m.*,
          p.local_name as plant_name,
          l.village_name
        FROM entry_media m
        LEFT JOIN survey_entries se ON m.entry_id = se.entry_id
        LEFT JOIN plants p ON se.plant_id = p.plant_id
        LEFT JOIN locations l ON se.location_id = l.location_id
        WHERE m.media_type = ?
        ORDER BY m.media_id DESC
      `;
    }
    
    const [rows] = await pool.execute(query, [type]);
    
    res.json({
      success: true,
      data: rows,
      count: rows.length,
      type: type
    });
  } catch (error) {
    console.error('Error fetching media by type:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch media by type'
    });
  }
};

// Get media by entry ID
const getMediaByEntry = async (req, res) => {
  try {
    const { entryId } = req.params;
    
    const query = `
      SELECT 
        m.*,
        p.local_name as plant_name,
        l.village_name
      FROM entry_media m
      LEFT JOIN survey_entries se ON m.entry_id = se.entry_id
      LEFT JOIN plants p ON se.plant_id = p.plant_id
      LEFT JOIN locations l ON se.location_id = l.location_id
      WHERE m.entry_id = ?
      ORDER BY m.media_type, m.media_id
    `;
    
    const [rows] = await pool.execute(query, [entryId]);
    
    // Group by media type
    const groupedMedia = {
      Photo: [],
      Audio: []
    };
    
    rows.forEach(media => {
      if (groupedMedia[media.media_type]) {
        groupedMedia[media.media_type].push(media);
      }
    });
    
    res.json({
      success: true,
      data: groupedMedia,
      total: rows.length,
      entry_id: entryId
    });
  } catch (error) {
    console.error('Error fetching media by entry:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch media by entry'
    });
  }
};

// Get logo media
const getLogoMedia = async (req, res) => {
  try {
    const query = `
      SELECT * FROM entry_media 
      WHERE media_type = 'Logo' 
      ORDER BY media_id DESC 
      LIMIT 1
    `;
    
    const [rows] = await pool.execute(query);
    
    res.json({
      success: true,
      data: rows[0] || null
    });
  } catch (error) {
    console.error('Error fetching logo:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch logo'
    });
  }
};

// Get homepage media (use first few photos as carousel)
const getHomepageMedia = async (req, res) => {
  try {
    const query = `
      SELECT 
        m.*,
        p.local_name as plant_name,
        l.village_name
      FROM entry_media m
      LEFT JOIN survey_entries se ON m.entry_id = se.entry_id
      LEFT JOIN plants p ON se.plant_id = p.plant_id
      LEFT JOIN locations l ON se.location_id = l.location_id
      WHERE m.media_type = 'Photo' 
      ORDER BY m.media_id DESC
      LIMIT 5
    `;
    
    const [rows] = await pool.execute(query);
    
    res.json({
      success: true,
      data: rows,
      count: rows.length
    });
  } catch (error) {
    console.error('Error fetching homepage media:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to fetch homepage media'
    });
  }
};

// Create media entry
const createMedia = async (req, res) => {
  try {
    const { entry_id, media_type, file_url } = req.body;
    
    const validTypes = ['Photo', 'Audio']; // Updated to match schema
    if (!validTypes.includes(media_type)) {
      return res.status(400).json({
        success: false,
        error: 'Invalid media type. Valid types: Photo, Audio'
      });
    }

    if (!file_url) {
      return res.status(400).json({
        success: false,
        error: 'File URL is required'
      });
    }

    const query = `
      INSERT INTO entry_media (entry_id, media_type, file_url)
      VALUES (?, ?, ?)
    `;
    
    const [result] = await pool.execute(query, [entry_id, media_type, file_url]);
    
    // Fetch the created media
    const [newMedia] = await pool.execute(
      'SELECT * FROM entry_media WHERE media_id = ?',
      [result.insertId]
    );
    
    res.status(201).json({
      success: true,
      data: newMedia[0],
      message: 'Media created successfully'
    });
  } catch (error) {
    console.error('Error creating media:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to create media'
    });
  }
};

// Delete media
const deleteMedia = async (req, res) => {
  try {
    const { id } = req.params;
    
    const query = 'DELETE FROM entry_media WHERE media_id = ?';
    const [result] = await pool.execute(query, [id]);
    
    if (result.affectedRows === 0) {
      return res.status(404).json({
        success: false,
        error: 'Media not found'
      });
    }
    
    res.json({
      success: true,
      message: 'Media deleted successfully'
    });
  } catch (error) {
    console.error('Error deleting media:', error);
    res.status(500).json({
      success: false,
      error: 'Failed to delete media'
    });
  }
};

module.exports = {
  getAllMedia,
  getMediaByType,
  getMediaByEntry,
  getLogoMedia,
  getHomepageMedia,
  createMedia,
  deleteMedia
};