import { query } from '../utils/database.js';
import path from 'path';
import fs from 'fs';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// Helper to calculate timeout date
const calculateTimeoutDate = (matchDate, timeoutMinutes) => {
  const date = new Date(matchDate);
  date.setMinutes(date.getMinutes() + timeoutMinutes);
  return date;
};

// Helper to convert BigInt to Number
const convertBigIntToNumber = (obj) => {
  if (obj === null || obj === undefined) return obj;
  if (typeof obj === 'bigint') return Number(obj);
  if (Array.isArray(obj)) return obj.map(convertBigIntToNumber);
  if (typeof obj === 'object') {
    const result = {};
    for (const key in obj) {
      result[key] = convertBigIntToNumber(obj[key]);
    }
    return result;
  }
  return obj;
};

// Helper to extract seat info from filename
const extractSeatInfoFromFilename = (filename) => {
  // Look for 3 to 5 uppercase letters, followed by an underscore, then numbers
  const regex = /^([A-Z]{3,5})_(\d+)\.pdf$/; // Updated regex to match DIRECTION_NUMBER.pdf format
  const match = filename.match(regex);
  if (match && match[1] && match[2]) {
    return {
      direction: match[1],
      extractedSeatNumber: parseInt(match[2], 10)
    };
  } else {
    return { direction: null, extractedSeatNumber: null };
  }
};

export const addMatch = async (req, res) => {
  try {
    const { name, date, location, totalSeats, price, timeoutDate } = req.body;
    const uploadedFiles = req.files; // Access uploaded files via multer
    // Parse the seatInfo array sent as a JSON string(s) from the frontend
    const seatInfoArray = req.body.seatInfo ? (Array.isArray(req.body.seatInfo) ? req.body.seatInfo.map(info => JSON.parse(info)) : [JSON.parse(req.body.seatInfo)]) : [];

    console.log('Uploaded files:', uploadedFiles); // Log uploaded files
    console.log('Received seatInfoArray:', seatInfoArray); // Log received seat info array

    // Basic validation
    if (!name || !date || !location || !totalSeats || !timeoutDate) {
      return res.status(400).json({ message: 'Missing required fields' });
    }

    // Ensure totalSeats is a number
    const parsedTotalSeats = parseInt(totalSeats, 10);
    const parsedPrice = parseFloat(price || 0);

    if (isNaN(parsedTotalSeats) || parsedTotalSeats < 0 || isNaN(parsedPrice) || parsedPrice < 0) {
      return res.status(400).json({ message: 'Invalid number format for seats or price' });
    }

    // Optional: Validate number of uploaded files vs totalSeats
    if (uploadedFiles && uploadedFiles.length > 0 && uploadedFiles.length !== parsedTotalSeats) {
      console.warn(`Number of uploaded files (${uploadedFiles.length}) does not match total seats (${parsedTotalSeats}). Seat info may not be fully populated.`);
    }

    // Start a transaction
    await query('START TRANSACTION');

    try {
      // Insert match
      const matchResult = await query(
        `INSERT INTO matches (
          name, date, location, totalSeats, availableSeats, 
          price, timeoutDate
        ) VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [
          name,
          date,
          location,
          parsedTotalSeats,
          parsedTotalSeats,
          parsedPrice,
          timeoutDate
        ]
      );

      const matchId = matchResult.insertId;

      // Create seats for the match and store extracted/manual info
      const seatValues = Array.from({ length: parsedTotalSeats }, (_, i) => {
        // Ensure we process files up to the number of uploaded files or totalSeats, whichever is smaller
        if (i >= uploadedFiles.length && i >= seatInfoArray.length) {
             return null; // No file or seat info for this seat index
        }

        const seatNumberDefault = i + 1; // Sequential seat number as default fallback
        const file = uploadedFiles && uploadedFiles[i]; // Corresponding uploaded file
        const frontendSeatInfo = seatInfoArray[i]; // Get seat info by index (assuming order matches files)

        console.log(`Processing seat ${i + 1}: File`, file ? file.originalname : 'No file', 'Frontend Info:', frontendSeatInfo); // Log processing details

        let seatNumberToSave = seatNumberDefault; // Use a new variable name for the final seat number
        let uploadedPdfPath = null;
        let sourceOfDirection = null; // Keep track of where direction came from

        // Determine seatDirection and seatNumberToSave based on available data, prioritizing frontend
        const determinedSeatInfo = (() => {
            if (frontendSeatInfo) {
                return {
                    direction: frontendSeatInfo.direction,
                    seatNumber: frontendSeatInfo.manualSeatNumber !== null ? frontendSeatInfo.manualSeatNumber : (frontendSeatInfo.extractedSeatNumber !== null ? frontendSeatInfo.extractedSeatNumber : seatNumberDefault),
                    source: 'frontend'
                };
            } else if (file) {
                // Fallback to backend extraction if no frontend info
                const extractedInfo = extractSeatInfoFromFilename(file.originalname);
                return {
                    direction: extractedInfo.direction,
                    seatNumber: extractedInfo.extractedSeatNumber !== null ? extractedInfo.extractedSeatNumber : seatNumberDefault,
                    source: 'backend_extraction'
                };
            } else {
                // Default if no frontend info and no file
                return {
                    direction: null,
                    seatNumber: seatNumberDefault,
                    source: 'default'
                };
            }
        })();

        const seatDirection = determinedSeatInfo.direction; // Assign determined direction
        seatNumberToSave = determinedSeatInfo.seatNumber; // Assign determined seat number
        sourceOfDirection = determinedSeatInfo.source; // Assign source

        if (file) {
             // Construct the path assuming multer saves to /public/uploads
             uploadedPdfPath = `/uploads/${file.filename}`; // Use file.filename provided by multer
        }

        // Log values after PDF path assignment, before returning for insertion
        console.log(`Seat ${i + 1} - Values after PDF path assignment: Direction = ${seatDirection}, Seat Number = ${seatNumberToSave}, PDF Path = ${uploadedPdfPath}, Source = ${sourceOfDirection}`);

        // Log values right before returning for insertion
        console.log(`Seat ${i + 1} - Values before return for insertion: Direction = ${seatDirection}, Seat Number = ${seatNumberToSave}, PDF Path = ${uploadedPdfPath}, Source = ${sourceOfDirection}`);

        // Return the values in the order expected by the INSERT query
        return [matchId, seatNumberToSave, seatDirection, uploadedPdfPath];
      }).filter(value => value !== null); // Filter out null values if totalSeats was higher than uploaded files/seatInfo

      const seatPlaceholders = seatValues.map(() => '(?, ?, ?, ?)').join(', ');

      if (seatValues.length > 0) {
        await query(
          `INSERT INTO seats (matchId, seatNumber, direction, uploadedPdfPath) VALUES ${seatPlaceholders}`,
          seatValues.flat()
        );
      } else if (parsedTotalSeats > 0 && (uploadedFiles.length === 0 || seatInfoArray.length === 0)) {
        // If totalSeats > 0 but no files uploaded or seatInfo processed, create seats with default info
        const defaultSeatValues = Array.from({ length: parsedTotalSeats }, (_, i) => [matchId, i + 1, null, null]);
        const defaultPlaceholders = defaultSeatValues.map(() => '(?, ?, ?, ?)').join(', ');
        await query(
          `INSERT INTO seats (matchId, seatNumber, direction, uploadedPdfPath) VALUES ${defaultPlaceholders}`,
          defaultSeatValues.flat()
        );
      }

      // Commit transaction
      await query('COMMIT');

      // Convert BigInt to Number before sending response
      const response = convertBigIntToNumber({
        message: 'Match added successfully',
        matchId: matchId
      });

      res.status(201).json(response);
    } catch (error) {
      // Rollback transaction on error
      await query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    console.error('Error adding match:', error);
    res.status(500).json({ 
      message: 'Failed to add match', 
      error: error.message 
    });
  }
};

export const getMatchSeats = async (req, res) => {
  try {
    const { matchId } = req.params;

    const seats = await query(`
      SELECT s.*, t.name as bookedBy, t.email as bookedByEmail
      FROM seats s
      LEFT JOIN tickets t ON s.ticketId = t.id
      WHERE s.matchId = ?
      ORDER BY s.seatNumber
    `, [matchId]);

    res.status(200).json(convertBigIntToNumber(seats));
  } catch (error) {
    console.error('Error fetching seats:', error);
    res.status(500).json({ 
      message: 'Failed to fetch seats', 
      error: error.message 
    });
  }
};

export const deleteMatch = async (req, res) => {
  try {
    const { id } = req.params;

    if (!id) {
      return res.status(400).json({ message: 'Match ID is required' });
    }

    // Start a transaction
    await query('START TRANSACTION');

    try {
      // Get file paths of associated PDFs before deleting records
      const seatPdfs = await query('SELECT uploadedPdfPath FROM seats WHERE matchId = ? AND uploadedPdfPath IS NOT NULL', [id]);
      const ticketPdfs = await query('SELECT pdfFile FROM tickets WHERE matchId = ? AND pdfFile IS NOT NULL', [id]);

      // Delete associated seats
      await query('DELETE FROM seats WHERE matchId = ?', [id]);

      // Delete associated tickets
      await query('DELETE FROM tickets WHERE matchId = ?', [id]);

      // Delete the match
      const result = await query('DELETE FROM matches WHERE id = ?', [id]);

      // Commit transaction
      await query('COMMIT');

      // Delete physical PDF files after successful database deletion
      const filesToDelete = [
        ...seatPdfs.map(row => row.uploadedPdfPath),
        ...ticketPdfs.map(row => row.pdfFile)
      ];

      const publicDir = path.join(__dirname, '..', '..' , 'public');

      for (const filePath of filesToDelete) {
        if (filePath) {
          const absolutePath = path.join(publicDir, filePath);
          fs.unlink(absolutePath, (err) => {
            if (err) {
              console.error(`Error deleting file ${absolutePath}:`, err);
            } else {
              console.log(`Deleted file: ${absolutePath}`);
            }
          });
        }
      }

      if (result.affectedRows === 0) {
        return res.status(404).json({ message: 'Match not found' });
      }

      res.status(200).json({ message: 'Match deleted successfully' });

    } catch (error) {
      // Rollback transaction on error
      await query('ROLLBACK');
      console.error('Error deleting match:', error);
      res.status(500).json({ 
        message: 'Failed to delete match', 
        error: error.message 
      });
    }

  } catch (error) {
    console.error('Error processing delete request:', error);
    res.status(500).json({ message: 'Failed to delete match', error: error.message });
  }
};

// Add other admin controller functions here later (e.g., for status page)