const { pool } = require("../../config/db");

/**
 * Generate unique ID
 */
const generateId = () => {
  return Date.now().toString(36) + Math.random().toString(36).slice(2);
};

/**
 * Format date to DD/MM/YYYY HH:MM
 */
const formatDateTime = (date) => {
  if (!date) return null;
  const d = new Date(date);
  if (isNaN(d.getTime())) return null;
  const day = String(d.getDate()).padStart(2, "0");
  const month = String(d.getMonth() + 1).padStart(2, "0");
  const year = d.getFullYear();
  const hours = String(d.getHours()).padStart(2, "0");
  const minutes = String(d.getMinutes()).padStart(2, "0");
  return `${day}/${month}/${year} ${hours}:${minutes}`;
};

/**
 * Format date to YYYY-MM-DD for DB
 */
const formatDateForDB = (date) => {
  if (!date) return null;
  const d = new Date(date);
  if (isNaN(d.getTime())) return null;
  return d.toISOString().split("T")[0];
};

/**
 * GET /budget-master/budgets
 * Get all budgets for an organization
 */
const getAllBudgets = async (req, res) => {
  try {
    const { page = 1, limit = 20, search = "" } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    let searchCondition = "";
    const queryParams = [orgId];
    let paramIndex = 2;

    if (search) {
      searchCondition += ` AND (b.name ILIKE $${paramIndex} OR b.month_label ILIKE $${paramIndex})`;
      queryParams.push(`%${search}%`);
      paramIndex++;
    }

    const query = `
      WITH dept_totals AS (
        SELECT 
          d.budget_id,
          COALESCE(SUM(d.allocated_amount), 0) AS total_dept_allocated,
          COUNT(d.id) AS dept_count
        FROM acc_departments d
        GROUP BY d.budget_id
      )
      SELECT 
        b.id,
        b.name,
        b.total_amount,
        b.month,
        b.year,
        b.month_label,
        b.created_at,
        b.updated_at,
        COALESCE(dt.total_dept_allocated, 0) AS total_allocated,
        COALESCE(dt.dept_count, 0) AS dept_count,
        CASE 
          WHEN b.total_amount > 0 THEN 
            ROUND((COALESCE(dt.total_dept_allocated, 0) / b.total_amount) * 100, 2)
          ELSE 0
        END AS utilization_percentage
      FROM acc_budgets b
      LEFT JOIN dept_totals dt ON dt.budget_id = b.id
      WHERE b.org_id = $1
        AND b.is_deleted = false
        ${searchCondition}
      ORDER BY b.created_at DESC
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
    `;

    queryParams.push(parseInt(limit), offset);
    const result = await pool.query(query, queryParams);

    // Count query
    const countQuery = `
      SELECT COUNT(*) AS total
      FROM acc_budgets b
      WHERE b.org_id = $1 AND b.is_deleted = false
      ${searchCondition.replace(/b\./g, 'b.')}
    `;
    const countParams = [orgId];
    if (search) countParams.push(`%${search}%`);
    const countResult = await pool.query(countQuery, countParams);
    const totalCount = parseInt(countResult.rows[0].total);

    const budgets = result.rows.map(b => ({
      id: b.id,
      name: b.name,
      total: parseFloat(b.total_amount),
      month: b.month,
      year: b.year,
      monthLabel: b.month_label,
      createdAt: formatDateTime(b.created_at),
      updatedAt: formatDateTime(b.updated_at),
      totalAllocated: parseFloat(b.total_allocated),
      deptCount: parseInt(b.dept_count),
      utilizationPercentage: parseFloat(b.utilization_percentage)
    }));

    return res.status(200).json({
      success: true,
      data: budgets,
      pagination: {
        total: totalCount,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(totalCount / parseInt(limit)),
      },
    });
  } catch (error) {
    console.error("Get All Budgets Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch budgets",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/**
 * GET /budget-master/budgets/:id
 * Get single budget with all departments and sub-departments
 */
const getBudgetById = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    // Get budget details
    const budgetQuery = `
      SELECT 
        b.id, b.name, b.total_amount, b.month, b.year, 
        b.month_label, b.created_at, b.updated_at
      FROM acc_budgets b
      WHERE b.id = $1 AND b.org_id = $2 AND b.is_deleted = false
    `;
    const budgetResult = await pool.query(budgetQuery, [id, orgId]);

    if (budgetResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Budget not found" });
    }

    const budget = budgetResult.rows[0];

    // Get departments with their sub-departments
    const deptsQuery = `
      SELECT 
        d.id, d.name, d.allocated_amount, d.created_at, d.updated_at,
        COALESCE(
          (SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
              'id', sd.id,
              'name', sd.name,
              'amount', sd.allocated_amount
            )
            ORDER BY sd.created_at
          ) FROM acc_sub_departments sd WHERE sd.department_id = d.id),
          '[]'::json
        ) AS sub_departments
      FROM acc_departments d
      WHERE d.budget_id = $1
      ORDER BY d.created_at
    `;
    const deptsResult = await pool.query(deptsQuery, [budget.id]);

    const departments = deptsResult.rows.map(d => ({
      id: d.id,
      name: d.name,
      amount: d.allocated_amount ? parseFloat(d.allocated_amount).toString() : "",
      subs: d.sub_departments.map(sd => ({
        id: sd.id,
        name: sd.name,
        amount: sd.amount ? parseFloat(sd.amount).toString() : ""
      }))
    }));

    const totalAllocated = departments.reduce((sum, d) => sum + (parseFloat(d.amount) || 0), 0);

    return res.status(200).json({
      success: true,
      data: {
        id: budget.id,
        name: budget.name,
        total: parseFloat(budget.total_amount),
        month: budget.month,
        year: budget.year,
        monthLabel: budget.month_label,
        createdAt: formatDateTime(budget.created_at),
        updatedAt: formatDateTime(budget.updated_at),
        totalAllocated: totalAllocated,
        departments: departments
      },
    });
  } catch (error) {
    console.error("Get Budget By ID Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch budget details",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/**
 * POST /budget-master/budgets
 * Create a new budget with default departments and sub-departments
 */
const createBudget = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { name, total, month, year, monthLabel } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.id;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    if (!name || !total || month === undefined || !year) {
      return res.status(400).json({ success: false, message: "Missing required fields: name, total, month, year" });
    }

    const budgetId = generateId();
    const now = new Date();

    // Begin transaction
    await client.query('BEGIN');

    // Insert budget
    await client.query(
      `INSERT INTO acc_budgets (id, org_id, name, total_amount, month, year, month_label, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
      [budgetId, orgId, name, total, month, year, monthLabel, now, now]
    );

    // Default departments and sub-departments (hardcoded)
    const defaultDepartments = [
      { name: "Manufacturing", subDepts: ["Assembly Line", "Quality Control", "Packaging"] },
      { name: "HR & Admin", subDepts: ["Recruitment", "Payroll", "Training"] },
      { name: "R&D", subDepts: ["Product Design", "Testing Lab", "Patents"] },
      { name: "Sales & Marketing", subDepts: ["Digital Marketing", "Field Sales", "PR"] },
      { name: "IT & Infrastructure", subDepts: ["Hardware", "Software Licenses", "Cloud & Hosting"] }
    ];

    // Insert departments and sub-departments
    for (const dept of defaultDepartments) {
      const deptId = generateId();
      
      await client.query(
        `INSERT INTO acc_departments (id, budget_id, name, allocated_amount, created_at, updated_at)
         VALUES ($1, $2, $3, $4, $5, $6)`,
        [deptId, budgetId, dept.name, 0, now, now]
      );

      for (const subDeptName of dept.subDepts) {
        const subDeptId = generateId();
        await client.query(
          `INSERT INTO acc_sub_departments (id, department_id, name, allocated_amount, created_at, updated_at)
           VALUES ($1, $2, $3, $4, $5, $6)`,
          [subDeptId, deptId, subDeptName, 0, now, now]
        );
      }
    }

    await client.query('COMMIT');

    // Fetch created budget with departments
    const fetchQuery = `
      SELECT 
        d.id, d.name, d.allocated_amount,
        COALESCE(
          (SELECT JSON_AGG(
            JSON_BUILD_OBJECT('id', sd.id, 'name', sd.name, 'amount', sd.allocated_amount)
          ) FROM acc_sub_departments sd WHERE sd.department_id = d.id),
          '[]'::json
        ) AS sub_departments
      FROM acc_departments d
      WHERE d.budget_id = $1 AND d.is_deleted = false
      ORDER BY d.created_at
    `;
    const deptsResult = await client.query(fetchQuery, [budgetId]);

    const departments = deptsResult.rows.map(d => ({
      id: d.id,
      name: d.name,
      amount: d.allocated_amount ? parseFloat(d.allocated_amount).toString() : "",
      subs: d.sub_departments.map(sd => ({
        id: sd.id,
        name: sd.name,
        amount: sd.amount ? parseFloat(sd.amount).toString() : ""
      }))
    }));

    return res.status(201).json({
      success: true,
      message: "Budget created successfully",
      data: {
        id: budgetId,
        name: name,
        total: parseFloat(total),
        month: month,
        year: year,
        monthLabel: monthLabel,
        createdAt: formatDateTime(now),
        departments: departments
      },
    });
  } catch (error) {
    await client.query('ROLLBACK');
    console.error("Create Budget Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to create budget",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  } finally {
    client.release();
  }
};

/**
 * PUT /budget-master/budgets/:id
 * Update budget basic info (name, total amount, month, year)
 */
const updateBudget = async (req, res) => {
  try {
    const { id } = req.params;
    const { name, total, month, year, monthLabel } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.id;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    // Check if budget exists
    const checkQuery = `SELECT id FROM acc_budgets WHERE id = $1 AND org_id = $2 AND is_deleted = false`;
    const checkResult = await pool.query(checkQuery, [id, orgId]);
    if (checkResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Budget not found" });
    }

    const updates = [];
    const values = [];
    let paramIndex = 1;

    if (name !== undefined) {
      updates.push(`name = $${paramIndex++}`);
      values.push(name);
    }
    if (total !== undefined) {
      updates.push(`total_amount = $${paramIndex++}`);
      values.push(total);
    }
    if (month !== undefined) {
      updates.push(`month = $${paramIndex++}`);
      values.push(month);
    }
    if (year !== undefined) {
      updates.push(`year = $${paramIndex++}`);
      values.push(year);
    }
    if (monthLabel !== undefined) {
      updates.push(`month_label = $${paramIndex++}`);
      values.push(monthLabel);
    }

    if (updates.length === 0) {
      return res.status(400).json({ success: false, message: "No fields to update" });
    }

    updates.push(`updated_at = NOW()`);
    values.push(id, orgId);

    const query = `
      UPDATE acc_budgets 
      SET ${updates.join(", ")}
      WHERE id = $${paramIndex++} AND org_id = $${paramIndex++} AND is_deleted = false
      RETURNING id, name, total_amount, month, year, month_label, updated_at
    `;

    const result = await pool.query(query, values);

    return res.status(200).json({
      success: true,
      message: "Budget updated successfully",
      data: {
        id: result.rows[0].id,
        name: result.rows[0].name,
        total: parseFloat(result.rows[0].total_amount),
        month: result.rows[0].month,
        year: result.rows[0].year,
        monthLabel: result.rows[0].month_label,
        updatedAt: formatDateTime(result.rows[0].updated_at),
      },
    });
  } catch (error) {
    console.error("Update Budget Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to update budget",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/**
 * PUT /budget-master/budgets/:budgetId/departments/:deptId
 * Update department allocation amount
 */
const updateDepartmentAllocation = async (req, res) => {
  try {
    const { budgetId, deptId } = req.params;
    const { amount } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.id;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    // Check if budget exists and belongs to org
    const budgetCheck = await pool.query(
      `SELECT id, total_amount FROM acc_budgets WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [budgetId, orgId]
    );
    if (budgetCheck.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Budget not found" });
    }

    const totalBudget = parseFloat(budgetCheck.rows[0].total_amount);

    // Get current department allocation and other departments total
    const deptCheck = await pool.query(
      `SELECT allocated_amount FROM acc_departments WHERE id = $1 AND budget_id = $2`,
      [deptId, budgetId]
    );
    if (deptCheck.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Department not found" });
    }

    const otherDeptsTotal = await pool.query(
      `SELECT COALESCE(SUM(allocated_amount), 0) as total 
       FROM acc_departments 
       WHERE budget_id = $1 AND id != $2`,
      [budgetId, deptId]
    );

    const newAmount = parseFloat(amount) || 0;
    const maxAllowed = totalBudget - parseFloat(otherDeptsTotal.rows[0].total);

    if (newAmount > maxAllowed) {
      return res.status(400).json({
        success: false,
        message: `Amount exceeds available budget. Max allowed: ${maxAllowed}`,
        maxAllowed: maxAllowed
      });
    }

    const result = await pool.query(
      `UPDATE acc_departments 
       SET allocated_amount = $1, updated_at = NOW()
       WHERE id = $2 AND budget_id = $3
       RETURNING id, name, allocated_amount`,
      [newAmount, deptId, budgetId]
    );

    // Insert into allocation log
    await pool.query(
      `INSERT INTO acc_budget_allocations (id, budget_id, department_id, amount, allocation_type, previous_amount, new_amount, action_by, created_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW())`,
      [generateId(), budgetId, deptId, newAmount, 'department', parseFloat(deptCheck.rows[0].allocated_amount) || 0, newAmount, userId]
    );

    return res.status(200).json({
      success: true,
      message: "Department allocation updated",
      data: {
        id: result.rows[0].id,
        name: result.rows[0].name,
        amount: parseFloat(result.rows[0].allocated_amount).toString()
      }
    });
  } catch (error) {
    console.error("Update Department Allocation Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to update department allocation",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/**
 * PUT /budget-master/budgets/:budgetId/departments/:deptId/sub-departments/:subDeptId
 * Update sub-department allocation amount
 */
const updateSubDepartmentAllocation = async (req, res) => {
  try {
    const { budgetId, deptId, subDeptId } = req.params;
    const { amount } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.id;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    // Check if budget exists
    const budgetCheck = await pool.query(
      `SELECT id FROM acc_budgets WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [budgetId, orgId]
    );
    if (budgetCheck.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Budget not found" });
    }

    // Get department allocated amount
    const deptResult = await pool.query(
      `SELECT allocated_amount FROM acc_departments WHERE id = $1 AND budget_id = $2`,
      [deptId, budgetId]
    );
    if (deptResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Department not found" });
    }

    const deptAmount = parseFloat(deptResult.rows[0].allocated_amount);

    // Get current sub-department allocation
    const subDeptResult = await pool.query(
      `SELECT allocated_amount FROM acc_sub_departments WHERE id = $1 AND department_id = $2`,
      [subDeptId, deptId]
    );
    if (subDeptResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Sub-department not found" });
    }

    const otherSubDeptsTotal = await pool.query(
      `SELECT COALESCE(SUM(allocated_amount), 0) as total 
       FROM acc_sub_departments 
       WHERE department_id = $1 AND id != $2`,
      [deptId, subDeptId]
    );

    const newAmount = parseFloat(amount) || 0;
    const maxAllowed = deptAmount - parseFloat(otherSubDeptsTotal.rows[0].total);

    if (newAmount > maxAllowed) {
      return res.status(400).json({
        success: false,
        message: `Amount exceeds department budget. Max allowed: ${maxAllowed}`,
        maxAllowed: maxAllowed
      });
    }

    const result = await pool.query(
      `UPDATE acc_sub_departments 
       SET allocated_amount = $1, updated_at = NOW()
       WHERE id = $2 AND department_id = $3
       RETURNING id, name, allocated_amount`,
      [newAmount, subDeptId, deptId]
    );

    // Insert into allocation log
    await pool.query(
      `INSERT INTO acc_budget_allocations (id, budget_id, department_id, sub_department_id, amount, allocation_type, previous_amount, new_amount, action_by, created_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW())`,
      [generateId(), budgetId, deptId, subDeptId, newAmount, 'sub_department', parseFloat(subDeptResult.rows[0].allocated_amount) || 0, newAmount, userId]
    );

    return res.status(200).json({
      success: true,
      message: "Sub-department allocation updated",
      data: {
        id: result.rows[0].id,
        name: result.rows[0].name,
        amount: parseFloat(result.rows[0].allocated_amount).toString()
      }
    });
  } catch (error) {
    console.error("Update Sub-Department Allocation Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to update sub-department allocation",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/**
 * POST /budget-master/budgets/:budgetId/departments
 * Add a new department to a budget
 */
const addDepartment = async (req, res) => {
  try {
    const { budgetId } = req.params;
    const { name } = req.body;
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    if (!name || !name.trim()) {
      return res.status(400).json({ success: false, message: "Department name is required" });
    }

    // Check if budget exists
    const budgetCheck = await pool.query(
      `SELECT id FROM acc_budgets WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [budgetId, orgId]
    );
    if (budgetCheck.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Budget not found" });
    }

    // Check if department already exists
    const deptCheck = await pool.query(
      `SELECT id FROM acc_departments WHERE budget_id = $1 AND name = $2`,
      [budgetId, name.trim()]
    );
    if (deptCheck.rows.length > 0) {
      return res.status(400).json({ success: false, message: "Department already exists in this budget" });
    }

    const deptId = generateId();
    const now = new Date();

    const result = await pool.query(
      `INSERT INTO acc_departments (id, budget_id, name, allocated_amount, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6)
       RETURNING id, name, allocated_amount`,
      [deptId, budgetId, name.trim(), 0, now, now]
    );

    return res.status(201).json({
      success: true,
      message: "Department added successfully",
      data: {
        id: result.rows[0].id,
        name: result.rows[0].name,
        amount: "0",
        subs: []
      }
    });
  } catch (error) {
    console.error("Add Department Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to add department",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/**
 * POST /budget-master/budgets/:budgetId/departments/:deptId/sub-departments
 * Add a new sub-department to a department
 */
const addSubDepartment = async (req, res) => {
  try {
    const { budgetId, deptId } = req.params;
    const { name } = req.body;
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    if (!name || !name.trim()) {
      return res.status(400).json({ success: false, message: "Sub-department name is required" });
    }

    // Check if department exists and belongs to budget
    const deptCheck = await pool.query(
      `SELECT d.id FROM acc_departments d
       JOIN acc_budgets b ON b.id = d.budget_id
       WHERE d.id = $1 AND d.budget_id = $2 AND b.org_id = $3 AND b.is_deleted = false`,
      [deptId, budgetId, orgId]
    );
    if (deptCheck.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Department not found" });
    }

    // Check if sub-department already exists
    const subCheck = await pool.query(
      `SELECT id FROM acc_sub_departments WHERE department_id = $1 AND name = $2`,
      [deptId, name.trim()]
    );
    if (subCheck.rows.length > 0) {
      return res.status(400).json({ success: false, message: "Sub-department already exists in this department" });
    }

    const subDeptId = generateId();
    const now = new Date();

    const result = await pool.query(
      `INSERT INTO acc_sub_departments (id, department_id, name, allocated_amount, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6)
       RETURNING id, name, allocated_amount`,
      [subDeptId, deptId, name.trim(), 0, now, now]
    );

    return res.status(201).json({
      success: true,
      message: "Sub-department added successfully",
      data: {
        id: result.rows[0].id,
        name: result.rows[0].name,
        amount: "0"
      }
    });
  } catch (error) {
    console.error("Add Sub-Department Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to add sub-department",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/**
 * DELETE /budget-master/budgets/:budgetId/departments/:deptId
 * Delete a department (soft delete)
 */
const deleteDepartment = async (req, res) => {
  try {
    const { budgetId, deptId } = req.params;
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const result = await pool.query(
      `UPDATE acc_departments 
       SET is_deleted = true, updated_at = NOW()
       WHERE id = $1 AND budget_id = $2
       AND EXISTS (SELECT 1 FROM acc_budgets WHERE id = $2 AND org_id = $3 AND is_deleted = false)
       RETURNING id`,
      [deptId, budgetId, orgId]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Department not found" });
    }

    return res.status(200).json({
      success: true,
      message: "Department deleted successfully"
    });
  } catch (error) {
    console.error("Delete Department Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to delete department",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/**
 * DELETE /budget-master/budgets/:budgetId/departments/:deptId/sub-departments/:subDeptId
 * Delete a sub-department (soft delete)
 */
const deleteSubDepartment = async (req, res) => {
  try {
    const { budgetId, deptId, subDeptId } = req.params;
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const result = await pool.query(
      `UPDATE acc_sub_departments 
       SET is_deleted = true, updated_at = NOW()
       WHERE id = $1 AND department_id = $2
       AND EXISTS (
         SELECT 1 FROM acc_departments d
         JOIN acc_budgets b ON b.id = d.budget_id
         WHERE d.id = $2 AND d.budget_id = $3 AND b.org_id = $4 AND b.is_deleted = false
       )
       RETURNING id`,
      [subDeptId, deptId, budgetId, orgId]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Sub-department not found" });
    }

    return res.status(200).json({
      success: true,
      message: "Sub-department deleted successfully"
    });
  } catch (error) {
    console.error("Delete Sub-Department Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to delete sub-department",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/**
 * DELETE /budget-master/budgets/:id
 * Delete a budget (soft delete)
 */
const deleteBudget = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const userId = req.user?.id;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const result = await pool.query(
      `UPDATE acc_budgets 
       SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id, name`,
      [userId, id, orgId]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Budget not found" });
    }

    return res.status(200).json({
      success: true,
      message: "Budget deleted successfully",
      data: {
        id: result.rows[0].id,
        name: result.rows[0].name
      }
    });
  } catch (error) {
    console.error("Delete Budget Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to delete budget",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

/**
 * POST /budget-master/budgets/:id/clone
 * Clone a budget to next month
 */
const cloneBudget = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { id } = req.params;
    const { name, total, month, year, monthLabel } = req.body;
    const orgId = req.user?.orgId;
    const userId = req.user?.id;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    // Get source budget with all departments and sub-departments
    const sourceQuery = `
      SELECT 
        b.id, b.name, b.total_amount,
        d.id AS dept_id, d.name AS dept_name, d.allocated_amount AS dept_amount,
        sd.id AS sub_id, sd.name AS sub_name, sd.allocated_amount AS sub_amount
      FROM acc_budgets b
      LEFT JOIN acc_departments d ON d.budget_id = b.id AND d.is_deleted = false
      LEFT JOIN acc_sub_departments sd ON sd.department_id = d.id AND sd.is_deleted = false
      WHERE b.id = $1 AND b.org_id = $2 AND b.is_deleted = false
    `;
    const sourceResult = await client.query(sourceQuery, [id, orgId]);

    if (sourceResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Source budget not found" });
    }

    await client.query('BEGIN');

    // Create new budget
    const newBudgetId = generateId();
    const now = new Date();

    await client.query(
      `INSERT INTO acc_budgets (id, org_id, name, total_amount, month, year, month_label, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
      [newBudgetId, orgId, name, total, month, year, monthLabel, now, now]
    );

    // Map to store old dept id -> new dept id
    const deptIdMap = new Map();

    // Get unique departments from source
    const deptMap = new Map();
    for (const row of sourceResult.rows) {
      if (row.dept_id && !deptMap.has(row.dept_id)) {
        deptMap.set(row.dept_id, {
          name: row.dept_name,
          amount: row.dept_amount || 0
        });
      }
    }

    // Clone departments
    for (const [oldDeptId, deptData] of deptMap.entries()) {
      const newDeptId = generateId();
      deptIdMap.set(oldDeptId, newDeptId);

      await client.query(
        `INSERT INTO acc_departments (id, budget_id, name, allocated_amount, created_at, updated_at)
         VALUES ($1, $2, $3, $4, $5, $6)`,
        [newDeptId, newBudgetId, deptData.name, 0, now, now]
      );
    }

    // Clone sub-departments
    for (const row of sourceResult.rows) {
      if (row.sub_id && row.dept_id && deptIdMap.has(row.dept_id)) {
        const newDeptId = deptIdMap.get(row.dept_id);
        const newSubId = generateId();

        await client.query(
          `INSERT INTO acc_sub_departments (id, department_id, name, allocated_amount, created_at, updated_at)
           VALUES ($1, $2, $3, $4, $5, $6)`,
          [newSubId, newDeptId, row.sub_name, 0, now, now]
        );
      }
    }

    await client.query('COMMIT');

    // Fetch created budget
    const fetchQuery = `
      SELECT 
        d.id, d.name, d.allocated_amount,
        COALESCE(
          (SELECT JSON_AGG(
            JSON_BUILD_OBJECT('id', sd.id, 'name', sd.name, 'amount', sd.allocated_amount)
          ) FROM acc_sub_departments sd WHERE sd.department_id = d.id),
          '[]'::json
        ) AS sub_departments
      FROM acc_departments d
      WHERE d.budget_id = $1
      ORDER BY d.created_at
    `;
    const deptsResult = await client.query(fetchQuery, [newBudgetId]);

    const departments = deptsResult.rows.map(d => ({
      id: d.id,
      name: d.name,
      amount: d.allocated_amount ? parseFloat(d.allocated_amount).toString() : "",
      subs: d.sub_departments.map(sd => ({
        id: sd.id,
        name: sd.name,
        amount: sd.amount ? parseFloat(sd.amount).toString() : ""
      }))
    }));

    return res.status(201).json({
      success: true,
      message: "Budget cloned successfully",
      data: {
        id: newBudgetId,
        name: name,
        total: parseFloat(total),
        month: month,
        year: year,
        monthLabel: monthLabel,
        createdAt: formatDateTime(now),
        departments: departments
      },
    });
  } catch (error) {
    await client.query('ROLLBACK');
    console.error("Clone Budget Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to clone budget",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  } finally {
    client.release();
  }
};

/**
 * GET /budget-master/summary
 * Get budget summary statistics
 */
const getBudgetSummary = async (req, res) => {
  try {
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const query = `
      WITH dept_totals AS (
        SELECT 
          d.budget_id,
          COALESCE(SUM(d.allocated_amount), 0) AS total_allocated,
          COUNT(DISTINCT d.id) AS dept_count
        FROM acc_departments d
        GROUP BY d.budget_id
      ),
      sub_dept_totals AS (
        SELECT 
          d.budget_id,
          COUNT(DISTINCT sd.id) AS sub_dept_count
        FROM acc_sub_departments sd
        JOIN acc_departments d ON d.id = sd.department_id
        GROUP BY d.budget_id
      )
      SELECT 
        COUNT(b.id) AS total_budgets,
        COALESCE(SUM(b.total_amount), 0) AS total_budgeted,
        COALESCE(SUM(dt.total_allocated), 0) AS total_allocated,
        COALESCE(SUM(dt.dept_count), 0) AS total_departments,
        COALESCE(SUM(sdt.sub_dept_count), 0) AS total_sub_departments,
        COUNT(CASE WHEN b.month = EXTRACT(MONTH FROM CURRENT_DATE) AND b.year = EXTRACT(YEAR FROM CURRENT_DATE) THEN 1 END) AS current_month_budgets
      FROM acc_budgets b
      LEFT JOIN dept_totals dt ON dt.budget_id = b.id
      LEFT JOIN sub_dept_totals sdt ON sdt.budget_id = b.id
      WHERE b.org_id = $1 AND b.is_deleted = false
    `;

    const result = await pool.query(query, [orgId]);
    const summary = result.rows[0];

    return res.status(200).json({
      success: true,
      data: {
        totalBudgets: parseInt(summary.total_budgets) || 0,
        totalBudgeted: parseFloat(summary.total_budgeted) || 0,
        totalAllocated: parseFloat(summary.total_allocated) || 0,
        totalDepartments: parseInt(summary.total_departments) || 0,
        totalSubDepartments: parseInt(summary.total_sub_departments) || 0,
        currentMonthBudgets: parseInt(summary.current_month_budgets) || 0,
        utilizationPercentage: summary.total_budgeted > 0 
          ? Math.round((parseFloat(summary.total_allocated) / parseFloat(summary.total_budgeted)) * 100)
          : 0
      }
    });
  } catch (error) {
    console.error("Get Budget Summary Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch budget summary",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

module.exports = {
  getAllBudgets,
  getBudgetById,
  createBudget,
  updateBudget,
  updateDepartmentAllocation,
  updateSubDepartmentAllocation,
  addDepartment,
  addSubDepartment,
  deleteDepartment,
  deleteSubDepartment,
  deleteBudget,
  cloneBudget,
  getBudgetSummary
};