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

/* ===========================
   CREATE SALES CUSTOMER PRICE
=========================== */
const createSalesCustomerPrice = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const {
      product_id,
      customer_id,
      base_price,
      freight_included,
      from_date,
      to_date,
      // org_id,
    } = req.body;

    if (
      !product_id ||
      !customer_id ||
      !base_price ||
      !from_date ||
      !to_date
    ) {
      return res.status(400).json({
        success: false,
        message: "Required fields are missing",
      });
    }

    const query = `
      INSERT INTO sales_price (
        product_id,
        customer_id,
        base_price,
        freight_included,
        from_date,
        to_date,
        org_id,
        created_by
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7, $8)
      RETURNING *;
    `;

    const values = [
      product_id,
      customer_id,
      base_price,
      freight_included === "true" || freight_included === true,
      from_date,
      to_date,
      org_id || null,
      userId,
    ];

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

    res.status(201).json({
      success: true,
      message: "Sales customer price created successfully ✅",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("CREATE SALES CUSTOMER PRICE ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Create failed",
    });
  }
};

/* ===========================
   GET ALL SALES CUSTOMER PRICES
=========================== */
const getSalesCustomerPrices = async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        scp.*,
        p.product_name,
        c.company_name
      FROM sales_price scp
      JOIN sales_products p ON scp.product_id = p.id
      JOIN customers c ON scp.customer_id = c.id
      WHERE scp.is_deleted = false
      ORDER BY scp.created_at DESC
    `);

    res.json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error("FETCH SALES CUSTOMER PRICE ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

/* ===========================
   ✅ GET BY ID
=========================== */
// const getSalesCustomerPriceById = async (req, res) => {
//   try {
//     const { id } = req.params;

//     const result = await pool.query(
//       `
//       SELECT * FROM sales_customer_price
//       WHERE id = $1
//         AND is_deleted = false
//       `,
//       [id]
//     );

//     if (!result.rows.length) {
//       return res.status(404).json({
//         success: false,
//         message: "Record not found",
//       });
//     }

//     res.json({
//       success: true,
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("GET BY ID ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: "Fetch failed",
//     });
//   }
// };
const getSalesCustomerPriceById = async (req, res) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `
      SELECT 
        scp.id,
        scp.product_id,
        scp.customer_id,
        scp.base_price,
        scp.freight_included,
        scp.from_date,
        scp.to_date,
        scp.created_by,
        scp.updated_by,
        scp.created_at,
        scp.updated_at,
        p.product_name,
        c.company_name
      FROM sales_price scp
      LEFT JOIN sales_products p 
        ON scp.product_id = p.id
      LEFT JOIN customers c 
        ON scp.customer_id = c.id
      WHERE scp.id = $1
        AND scp.is_deleted = false
      `,
      [id]
    );

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

    res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("GET BY ID ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

/* ===========================
   ✅ UPDATE
=========================== */
const updateSalesCustomerPrice = async (req, res) => {
  try {
    const { id } = req.params;

    const {
      product_id,
      customer_id,
      base_price,
      freight_included,
      from_date,
      to_date,
    } = req.body;

    const query = `
      UPDATE sales_price SET
        product_id = COALESCE($1, product_id),
        customer_id = COALESCE($2, customer_id),
        base_price = COALESCE($3, base_price),
        freight_included = COALESCE($4, freight_included),
        from_date = COALESCE($5, from_date),
        to_date = COALESCE($6, to_date),
        updated_at = NOW()
      WHERE id = $7
        AND is_deleted = false
      RETURNING *;
    `;

    const values = [
      product_id || null,
      customer_id || null,
      base_price || null,
      freight_included === "true" || freight_included === true,
      from_date || null,
      to_date || null,
      id,
    ];

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

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

    res.json({
      success: true,
      message: "Updated successfully ✅",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("UPDATE ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Update failed",
    });
  }
};

/* ===========================
   ✅ DELETE (SOFT)
=========================== */
const deleteSalesCustomerPrice = async (req, res) => {
  try {
    const { id } = req.params;

    await pool.query(
      `
      UPDATE sales_price
      SET is_deleted = true,
          updated_at = NOW()
      WHERE id = $1
      `,
      [id]
    );

    res.json({
      success: true,
      message: "Deleted successfully ✅",
    });
  } catch (err) {
    console.error("DELETE ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Delete failed",
    });
  }
};

module.exports = {
  createSalesCustomerPrice,
  getSalesCustomerPrices,
  getSalesCustomerPriceById,
  updateSalesCustomerPrice,
  deleteSalesCustomerPrice,
};