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


const createSalesReturn = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.userId;

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    const {
      return_no,
      quantity_returned,
      reason,
      quality_status,
      sales_order_id,
      product_id,
      customer_code,
      status
    } = req.body;

    // 🔹 Basic Validation
    if (!return_no || !quantity_returned || !product_id) {
      return res.status(400).json({
        message: "Missing required fields",
      });
    }

    await client.query("BEGIN");

    const insertQuery = `
      INSERT INTO sales_returns (
        return_no,
        quantity_returned,
        reason,
        quality_status,
        sales_order_id,
        product_id,
        org_id,
        customer_code,
        status,
        created_by,
        created_at,
        updated_at
      )
      VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,NOW(),NOW()
      )
      RETURNING *;
    `;

    const values = [
      return_no,
      quantity_returned,
      reason || null,
      quality_status || null,
      sales_order_id || null,
      product_id,
      orgId,
      customer_code || null,
      status || "ACTIVE",
      userId
    ];

    const result = await client.query(insertQuery, values);

    await client.query("COMMIT");

    return res.status(201).json({
      message: "Sales return created successfully",
      data: result.rows[0],
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("CREATE SALES RETURN ERROR:", error);

    return res.status(500).json({
      message: "Internal server error",
    });
  } finally {
    client.release();
  }
};


const getSalesReturns = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    //  Pagination
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 10;
    const offset = (page - 1) * limit;

    //  Filters
    const {
      search,          // return_no / customer_code
      status,
      product_id,
      sales_order_id,
      from_date,
      to_date
    } = req.query;

    let whereClause = `WHERE org_id = $1`;
    let values = [orgId];
    let index = 2;

    //  Dynamic Filters
    if (search) {
      whereClause += ` AND (return_no ILIKE $${index} OR customer_code ILIKE $${index})`;
      values.push(`%${search}%`);
      index++;
    }

    if (status) {
      whereClause += ` AND status = $${index}`;
      values.push(status);
      index++;
    }

    if (product_id) {
      whereClause += ` AND product_id = $${index}`;
      values.push(product_id);
      index++;
    }

    if (sales_order_id) {
      whereClause += ` AND sales_order_id = $${index}`;
      values.push(sales_order_id);
      index++;
    }

    if (from_date) {
      whereClause += ` AND created_at >= $${index}`;
      values.push(from_date);
      index++;
    }

    if (to_date) {
      whereClause += ` AND created_at <= $${index}`;
      values.push(to_date);
      index++;
    }

    //  Main Query
    const dataQuery = `
      SELECT *
      FROM sales_returns
      ${whereClause}
      ORDER BY created_at DESC
      LIMIT $${index} OFFSET $${index + 1};
    `;

    values.push(limit, offset);

    //  Count Query
    const countQuery = `
      SELECT COUNT(*) 
      FROM sales_returns
      ${whereClause};
    `;

    const [dataResult, countResult] = await Promise.all([
      client.query(dataQuery, values),
      client.query(countQuery, values.slice(0, index - 1))
    ]);

    const total = parseInt(countResult.rows[0].count);

    return res.status(200).json({
      message: "Sales returns fetched successfully",
      data: dataResult.rows,
      pagination: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    });

  } catch (error) {
    console.error("GET SALES RETURNS ERROR:", error);

    return res.status(500).json({
      message: "Internal server error",
    });
  } finally {
    client.release();
  }
};


const getSalesReturnById = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;
    const { id } = req.params;

    // Auth check
    if (!orgId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    //  Validate ID
    if (!id || isNaN(id)) {
      return res.status(400).json({
        message: "Invalid sales return ID",
      });
    }

    const query = `
      SELECT *
      FROM sales_returns
      WHERE sales_return_id = $1
        AND org_id = $2
      LIMIT 1;
    `;

    const values = [id, orgId];

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

    //  Not Found
    if (result.rows.length === 0) {
      return res.status(404).json({
        message: "Sales return not found",
      });
    }

    return res.status(200).json({
      message: "Sales return fetched successfully",
      data: result.rows[0],
    });

  } catch (error) {
    console.error("GET SALES RETURN BY ID ERROR:", error);

    return res.status(500).json({
      message: "Internal server error",
    });
  } finally {
    client.release();
  }
};

const updateSalesReturn = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.userId;
    const { id } = req.params;

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    if (!id || isNaN(id)) {
      return res.status(400).json({
        message: "Invalid sales return ID",
      });
    }

    const {
      return_no,
      quantity_returned,
      reason,
      quality_status,
      sales_order_id,
      product_id,
      customer_code,
      status
    } = req.body;

    // 🔹 Build dynamic update query
    let updateFields = [];
    let values = [];
    let index = 1;

    if (return_no !== undefined) {
      updateFields.push(`return_no = $${index++}`);
      values.push(return_no);
    }

    if (quantity_returned !== undefined) {
      updateFields.push(`quantity_returned = $${index++}`);
      values.push(quantity_returned);
    }

    if (reason !== undefined) {
      updateFields.push(`reason = $${index++}`);
      values.push(reason || null);
    }

    if (quality_status !== undefined) {
      updateFields.push(`quality_status = $${index++}`);
      values.push(quality_status || null);
    }

    if (sales_order_id !== undefined) {
      updateFields.push(`sales_order_id = $${index++}`);
      values.push(sales_order_id || null);
    }

    if (product_id !== undefined) {
      updateFields.push(`product_id = $${index++}`);
      values.push(product_id);
    }

    if (customer_code !== undefined) {
      updateFields.push(`customer_code = $${index++}`);
      values.push(customer_code || null);
    }

    if (status !== undefined) {
      updateFields.push(`status = $${index++}`);
      values.push(status);
    }

    //  Ensure something to update
    if (updateFields.length === 0) {
      return res.status(400).json({
        message: "No fields provided for update",
      });
    }

    //  Add audit fields
    updateFields.push(`updated_by = $${index++}`);
    values.push(userId);

    updateFields.push(`updated_at = NOW()`);

    //  WHERE condition
    values.push(id);
    values.push(orgId);

    const updateQuery = `
      UPDATE sales_returns
      SET ${updateFields.join(", ")}
      WHERE sales_return_id = $${index++}
        AND org_id = $${index}
      RETURNING *;
    `;

    await client.query("BEGIN");

    const updateResult = await client.query(updateQuery, values);

    if (updateResult.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({
        message: "Sales return not found or not accessible",
      });
    }

    //  Fetch enriched data with joins (users, product optional)
    const fetchQuery = `
      SELECT 
        sr.*,

        u1.user_name AS created_by_name,
        u2.user_name AS updated_by_name

      FROM sales_returns sr

      LEFT JOIN users u1 ON u1.user_id = sr.created_by
      LEFT JOIN users u2 ON u2.user_id = sr.updated_by

      WHERE sr.sales_return_id = $1
        AND sr.org_id = $2
      LIMIT 1;
    `;

    const fetchResult = await client.query(fetchQuery, [id, orgId]);

    await client.query("COMMIT");

    return res.status(200).json({
      message: "Sales return updated successfully",
      data: fetchResult.rows[0],
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("UPDATE SALES RETURN ERROR:", error);

    return res.status(500).json({
      message: "Internal server error",
    });
  } finally {
    client.release();
  }
};

const deleteSalesReturn = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.userId;
    const { id } = req.params;

    //  Auth check
    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    //  Validate ID
    if (!id || isNaN(id)) {
      return res.status(400).json({
        message: "Invalid sales return ID",
      });
    }

    await client.query("BEGIN");

    //  Soft delete (mark as DELETED instead of removing)
    const deleteQuery = `
      UPDATE sales_returns
      SET 
        status = 'DELETED',
        updated_by = $1,
        updated_at = NOW()
      WHERE sales_return_id = $2
        AND org_id = $3
        AND status != 'DELETED'
      RETURNING *;
    `;

    const result = await client.query(deleteQuery, [
      userId,
      id,
      orgId,
    ]);

    //  Not found / already deleted
    if (result.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({
        message: "Sales return not found or already deleted",
      });
    }

    await client.query("COMMIT");

    return res.status(200).json({
      message: "Sales return deleted successfully",
      data: result.rows[0],
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("DELETE SALES RETURN ERROR:", error);

    return res.status(500).json({
      message: "Internal server error",
    });
  } finally {
    client.release();
  }
};

module.exports = {
    createSalesReturn,
    getSalesReturns,
    getSalesReturnById,
    updateSalesReturn,
    deleteSalesReturn,
}