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

/* =========================================================
   HELPERS
   ========================================================= */

const toNumber = (
  value
) => {
  const number =
    Number(value);

  return Number.isFinite(number)
    ? number
    : 0;
};

const mapStatusCounts = (
  summary
) => ({
  total_submissions:
    toNumber(
      summary.total_submissions
    ),

  awaiting_review:
    toNumber(
      summary.awaiting_review
    ),

  under_review:
    toNumber(
      summary.under_review
    ),

  approved:
    toNumber(
      summary.approved
    ),

  returned:
    toNumber(
      summary.returned
    ),

  rejected:
    toNumber(
      summary.rejected
    ),

  submitted_today:
    toNumber(
      summary.submitted_today
    )
});

/* =========================================================
   GET ADMIN DASHBOARD
   GET /api/admin/dashboard
   ========================================================= */

const getDashboard = async (
  request,
  response
) => {
  try {
    const [summaryRows] =
      await pool.query(
        `
          SELECT
            COUNT(*) AS total_submissions,

            SUM(
              submission_status = 'submitted'
            ) AS awaiting_review,

            SUM(
              submission_status = 'under_review'
            ) AS under_review,

            SUM(
              submission_status = 'approved'
            ) AS approved,

            SUM(
              submission_status = 'returned'
            ) AS returned,

            SUM(
              submission_status = 'rejected'
            ) AS rejected,

            SUM(
              DATE(submitted_at) =
              CURDATE()
            ) AS submitted_today

          FROM staff_submissions

          WHERE submission_status
            <> 'draft'
        `
      );

    const [recentSubmissionRows] =
      await pool.query(
        `
          SELECT
            ss.id,
            ss.submission_reference,
            ss.submission_status,
            ss.submitted_at,
            ss.updated_at,

            s.staff_number,

            oldr.full_name,

            sr.review_status,
            sr.assigned_to,

            assigned_admin.full_name
              AS assigned_to_name

          FROM staff_submissions ss

          INNER JOIN staff s
            ON s.id =
              ss.staff_id

          INNER JOIN old_record oldr
            ON oldr.id =
              ss.old_record_id

          LEFT JOIN submission_reviews sr
            ON sr.submission_id =
              ss.id

          LEFT JOIN admin_users
            AS assigned_admin
            ON assigned_admin.id =
              sr.assigned_to

          WHERE ss.submission_status
            <> 'draft'

          ORDER BY
            COALESCE(
              ss.submitted_at,
              ss.updated_at
            ) DESC

          LIMIT 8
        `
      );

    const [reviewWorkloadRows] =
      await pool.query(
        `
          SELECT
            au.id,
            au.full_name,
            au.role,

            COUNT(sr.id)
              AS total_assigned,

            SUM(
              sr.review_status =
              'under_review'
            ) AS currently_reviewing,

            SUM(
              sr.review_status =
              'approved'
            ) AS approved_reviews,

            SUM(
              sr.review_status =
              'returned'
            ) AS returned_reviews

          FROM admin_users au

          LEFT JOIN submission_reviews sr
            ON sr.assigned_to =
              au.id

          WHERE
            au.status = 'active'

            AND au.role IN (
              'super_admin',
              'hr_admin',
              'hr_reviewer'
            )

          GROUP BY
            au.id,
            au.full_name,
            au.role

          ORDER BY
            total_assigned DESC,
            au.full_name ASC

          LIMIT 6
        `
      );

    return response.status(200).json({
      success: true,

      data: {
        summary:
          mapStatusCounts(
            summaryRows[0] || {}
          ),

        recent_submissions:
          recentSubmissionRows.map(
            (submission) => ({
              ...submission,

              id:
                toNumber(
                  submission.id
                ),

              assigned_to:
                submission.assigned_to
                  ? toNumber(
                      submission
                        .assigned_to
                    )
                  : null
            })
          ),

        reviewer_workload:
          reviewWorkloadRows.map(
            (reviewer) => ({
              id:
                toNumber(
                  reviewer.id
                ),

              full_name:
                reviewer.full_name,

              role:
                reviewer.role,

              total_assigned:
                toNumber(
                  reviewer
                    .total_assigned
                ),

              currently_reviewing:
                toNumber(
                  reviewer
                    .currently_reviewing
                ),

              approved_reviews:
                toNumber(
                  reviewer
                    .approved_reviews
                ),

              returned_reviews:
                toNumber(
                  reviewer
                    .returned_reviews
                )
            })
          )
      }
    });
  } catch (error) {
    console.error(
      "Get admin dashboard error:",
      error
    );

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

      message:
        process.env.NODE_ENV ===
        "development"
          ? error.sqlMessage ||
            error.message ||
            "Unable to load the administrator dashboard."
          : "Unable to load the administrator dashboard."
    });
  }
};

module.exports = {
  getDashboard
};