// Sales Dashboard Stats API

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

const getSalesDashboardStats = async (req, res) => {
  try {

    const orgId = req.session.user.orgId;

    // Dashboard Stats Query
    const dashboardQuery = `
      SELECT

        /* Total Sales Orders */
        (
          SELECT COUNT(*)
          FROM sales_orders
          WHERE is_deleted = false
          AND org_id = $1
        ) AS sales_orders,

        /* Total Quotations */
        (
          SELECT COUNT(*)
          FROM sales_quotations
          WHERE is_deleted = false
          AND org_id = $1
        ) AS quotations,

        /* Total Sales Enquiries */
        (
          SELECT COUNT(*)
          FROM sales_enquiry
          WHERE is_deleted = false
          AND org_id = $1
        ) AS enquiries,

        /* Total Invoices */
        (
          SELECT COUNT(*)
          FROM sales_invoices
          WHERE is_deleted = false
          AND org_id = $1
        ) AS invoices,

        /* Revenue Pipeline */
        (
          SELECT COALESCE(SUM(total_amount), 0)
          FROM sales_orders
          WHERE is_deleted = false
          AND org_id = $1
        ) AS revenue_pipeline,

        /* Dispatch MTD */
        (
          SELECT COALESCE(SUM(total_amount), 0)
          FROM sales_invoices
          WHERE is_deleted = false
          AND org_id = $1
          AND DATE_TRUNC(
            'month',
            invoice_date
          ) = DATE_TRUNC(
            'month',
            CURRENT_DATE
          )
        ) AS dispatch_mtd
    `;

    // Sales Performance Query
    const salesPerformanceQuery = `
      SELECT

        TO_CHAR(
          invoice_date,
          'Dy'
        ) AS day,

        COALESCE(
          SUM(total_amount),
          0
        ) AS total_sales

      FROM sales_invoices

      WHERE is_deleted = false
      AND org_id = $1

      AND invoice_date >=
        CURRENT_DATE - INTERVAL '6 days'

      GROUP BY invoice_date

      ORDER BY invoice_date
    `;

    // Execute Queries
    const [
      dashboardResult,
      salesPerformanceResult
    ] = await Promise.all([
      pool.query(
        dashboardQuery,
        [orgId]
      ),

      pool.query(
        salesPerformanceQuery,
        [orgId]
      ),
    ]);

    const stats =
      dashboardResult.rows[0];

    // Response
    return res.status(200).json({
      success: true,

      message:
        "Sales dashboard stats fetched successfully",

      data: {

        sales_orders:
          Number(
            stats.sales_orders
          ),

        quotations:
          Number(
            stats.quotations
          ),

        enquiries:
          Number(
            stats.enquiries
          ),

        invoices:
          Number(
            stats.invoices
          ),

        revenue_pipeline:
          Number(
            stats.revenue_pipeline
          ),

        dispatch_mtd:
          Number(
            stats.dispatch_mtd
          ),

        sales_performance:
          salesPerformanceResult.rows.map(
            (item) => ({
              day: item.day.trim(),
              total_sales:
                Number(item.total_sales),
            })
          ),
      },
    });

  } catch (error) {

    console.error(
      "Sales Dashboard Stats Error:",
      error
    );

    return res.status(500).json({
      success: false,

      message:
        "Failed to fetch sales dashboard stats",

      error: error.message,
    });

  }
};

module.exports = {
  getSalesDashboardStats,
};