import pool from '../config/database.js';
import path from 'path';
import { fileURLToPath } from 'url';
import { dirname } from 'path';
import fs from 'fs';

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

export const getAllCategories = async (req, res) => {
  try {
    const [categories] = await pool.query(
      'SELECT * FROM job_categories ORDER BY created_at DESC'
    );
    res.json({ categories });
  } catch (error) {
    console.error('Error fetching categories:', error);
    res.status(500).json({ error: 'Failed to fetch categories' });
  }
};

export const getCategoryById = async (req, res) => {
  try {
    const [categories] = await pool.query(
      'SELECT * FROM job_categories WHERE id = ?',
      [req.params.id]
    );

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

    res.json({ category: categories[0] });
  } catch (error) {
    console.error('Error fetching category:', error);
    res.status(500).json({ error: 'Failed to fetch category' });
  }
};

export const createCategory = async (req, res) => {
  try {
    const { title, description, job_count = 0, is_active = true, display_order = 0 } = req.body;

    if (!title) {
      return res.status(400).json({ error: 'Title is required' });
    }

    let imageUrl = null;
    if (req.file) {
      imageUrl = `/uploads/${req.file.filename}`;
    }

    const [result] = await pool.query(
      `INSERT INTO job_categories (title, description, image_url, job_count, is_active, display_order)
       VALUES (?, ?, ?, ?, ?, ?)`,
      [title, description, imageUrl, job_count, is_active, display_order]
    );

    const [newCategory] = await pool.query(
      'SELECT * FROM job_categories WHERE id = ?',
      [result.insertId]
    );

    res.status(201).json({ category: newCategory[0] });
  } catch (error) {
    console.error('Error creating category:', error);
    res.status(500).json({ error: 'Failed to create category' });
  }
};

export const updateCategory = async (req, res) => {
  try {
    const { id } = req.params;
    const { title, description, job_count, is_active, display_order } = req.body;

    const [existing] = await pool.query(
      'SELECT * FROM job_categories WHERE id = ?',
      [id]
    );

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

    let imageUrl = existing[0].image_url;
    if (req.file) {
      if (existing[0].image_url) {
        const oldImagePath = path.join(__dirname, '..', existing[0].image_url);
        if (fs.existsSync(oldImagePath)) {
          fs.unlinkSync(oldImagePath);
        }
      }
      imageUrl = `/uploads/${req.file.filename}`;
    }

    await pool.query(
      `UPDATE job_categories
       SET title = ?, description = ?, image_url = ?, job_count = ?, is_active = ?, display_order = ?
       WHERE id = ?`,
      [title, description, imageUrl, job_count, is_active, display_order, id]
    );

    const [updatedCategory] = await pool.query(
      'SELECT * FROM job_categories WHERE id = ?',
      [id]
    );

    res.json({ category: updatedCategory[0] });
  } catch (error) {
    console.error('Error updating category:', error);
    res.status(500).json({ error: 'Failed to update category' });
  }
};

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

    const [existing] = await pool.query(
      'SELECT * FROM job_categories WHERE id = ?',
      [id]
    );

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

    if (existing[0].image_url) {
      const imagePath = path.join(__dirname, '..', existing[0].image_url);
      if (fs.existsSync(imagePath)) {
        fs.unlinkSync(imagePath);
      }
    }

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

    res.json({ message: 'Category deleted successfully' });
  } catch (error) {
    console.error('Error deleting category:', error);
    res.status(500).json({ error: 'Failed to delete category' });
  }
};
