const fs = require("fs");
const path = require("path");

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

const {
  resolvePrivateFile
} = require(
  "../config/storage"
);

const {
  writeAdminAuditLog
} = require(
  "../services/adminAuditService"
);

/* =========================================================
   CONSTANTS
   ========================================================= */

const reviewRoles = [
  "super_admin",
  "hr_admin",
  "hr_reviewer"
];

const managerRoles = [
  "super_admin",
  "hr_admin"
];

const optionalSectionTables = {
  personal_details: [
    "submission_personal_details",
    "submission_personal_detail"
  ],

  employment_details: [
    "submission_employment_details",
    "submission_employment"
  ],

  next_of_kin: [
    "submission_next_of_kin"
  ],

  dependents: [
    "submission_dependents",
    "submission_dependent"
  ],

  emergency_contact: [
    "submission_emergency_contact"
  ],

  qualifications: [
    "submission_qualifications",
    "submission_qualification"
  ]
};

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

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

  if (
    !Number.isInteger(number) ||
    number < 1
  ) {
    return 0;
  }

  return number;
};

const normalizeNote = (
  value,
  maximumLength = 3000
) => {
  return String(value || "")
    .trim()
    .slice(
      0,
      maximumLength
    );
};

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

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

const getRequestIp = (
  request
) => {
  return (
    request.ip ||
    request.socket
      ?.remoteAddress ||
    null
  );
};

const getUserAgent = (
  request
) => {
  const userAgent =
    request.get(
      "user-agent"
    );

  return userAgent
    ? userAgent.slice(
        0,
        500
      )
    : null;
};

const isManager = (
  admin
) => {
  return managerRoles.includes(
    admin?.role
  );
};

const canReview = (
  admin
) => {
  return reviewRoles.includes(
    admin?.role
  );
};

const sendDevelopmentError = (
  response,
  error,
  fallbackMessage
) => {
  const isDevelopment =
    process.env.NODE_ENV !==
    "production";

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

    message:
      isDevelopment
        ? error.sqlMessage ||
          error.message ||
          fallbackMessage
        : fallbackMessage
  });
};

/* =========================================================
   DATABASE TABLE HELPERS
   ========================================================= */

const findExistingTable =
  async (
    connection,
    candidates
  ) => {
    if (
      !Array.isArray(candidates) ||
      candidates.length === 0
    ) {
      return null;
    }

    const placeholders =
      candidates
        .map(() => "?")
        .join(", ");

    const [rows] =
      await connection.query(
        `
          SELECT
            TABLE_NAME AS table_name
          FROM information_schema.TABLES
          WHERE
            TABLE_SCHEMA =
              DATABASE()

            AND TABLE_NAME IN (
              ${placeholders}
            )
        `,
        candidates
      );

    const availableTables =
      new Set(
        rows.map(
          (row) =>
            row.table_name
        )
      );

    return (
      candidates.find(
        (tableName) =>
          availableTables.has(
            tableName
          )
      ) || null
    );
  };

const loadOptionalSection =
  async ({
    connection,
    tableCandidates,
    submissionId,
    multiple = false
  }) => {
    const tableName =
      await findExistingTable(
        connection,
        tableCandidates
      );

    if (!tableName) {
      return multiple
        ? []
        : null;
    }

    /*
     * tableName is taken only from the
     * internal allowlist above.
     */
    const [rows] =
      await connection.query(
        `
          SELECT *
          FROM \`${tableName}\`
          WHERE submission_id = ?
          ORDER BY id ASC
        `,
        [
          submissionId
        ]
      );

    return multiple
      ? rows
      : rows[0] || null;
  };

/* =========================================================
   ACCESS CONTROL
   ========================================================= */

const verifySubmissionAccess =
  ({
    request,
    submission
  }) => {
    if (
      request.admin.role ===
      "hr_reviewer"
    ) {
      if (
        Number(
          submission.assigned_to
        ) !==
        Number(
          request.admin.id
        )
      ) {
        return {
          allowed: false,

          status: 403,

          message:
            "This submission is not assigned to you."
        };
      }
    }

    return {
      allowed: true
    };
  };

/* =========================================================
   LOAD BASE SUBMISSION
   ========================================================= */

const loadSubmissionContext =
  async (
    connection,
    submissionId,
    lockRecord = false
  ) => {
    const lockClause =
      lockRecord
        ? "FOR UPDATE"
        : "";

    const [rows] =
      await connection.query(
        `
          SELECT
            ss.id,
            ss.staff_id,
            ss.old_record_id,
            ss.submission_reference,
            ss.submission_status,
            ss.correction_status,
            ss.return_count,
            ss.last_returned_at,
            ss.last_returned_by,
            ss.resubmitted_at,
            ss.submitted_at,
            ss.created_at,
            ss.updated_at,
            s.staff_number,

            sr.id AS review_id,
            sr.assigned_to,
            sr.assigned_by,
            sr.review_status,
            sr.review_notes,
            sr.return_reason,
            sr.rejection_reason,
            sr.review_started_at,
            sr.reviewed_at,
            sr.completed_at,

            assigned_admin.full_name
              AS assigned_to_name,

            assigned_admin.email
              AS assigned_to_email,

            assigning_admin.full_name
              AS assigned_by_name

          FROM staff_submissions ss

          INNER JOIN staff s
            ON s.id =
              ss.staff_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

          LEFT JOIN admin_users
            AS assigning_admin
            ON assigning_admin.id =
              sr.assigned_by

          WHERE ss.id = ?

          LIMIT 1

          ${lockClause}
        `,
        [
          submissionId
        ]
      );

    return rows[0] || null;
  };

/* =========================================================
   PERMISSIONS
   ========================================================= */

const buildSubmissionPermissions =
  (
    request,
    submission
  ) => {
    const status =
      submission
        .submission_status;

    const completed =
      [
        "approved",
        "rejected"
      ].includes(status);

    const assignedToCurrentAdmin =
      Number(
        submission.assigned_to
      ) ===
      Number(
        request.admin.id
      );

    const reviewerHasAccess =
      request.admin.role !==
        "hr_reviewer" ||
      assignedToCurrentAdmin;

    return {
      can_assign:
        isManager(
          request.admin
        ) &&
        !completed,

      can_start_review:
        canReview(
          request.admin
        ) &&
        reviewerHasAccess &&
        status ===
          "submitted",

      can_approve:
        canReview(
          request.admin
        ) &&
        reviewerHasAccess &&
        [
          "submitted",
          "under_review"
        ].includes(status),

      can_return:
        canReview(
          request.admin
        ) &&
        reviewerHasAccess &&
        [
          "submitted",
          "under_review"
        ].includes(status),

      can_reject:
        canReview(
          request.admin
        ) &&
        reviewerHasAccess &&
        [
          "submitted",
          "under_review"
        ].includes(status),

      can_download_documents:
        true
    };
  };

/* =========================================================
   GET SUBMISSION DETAIL
   GET /api/admin/submissions/:submissionId
   ========================================================= */

const getSubmissionDetail =
  async (
    request,
    response
  ) => {
    let connection = null;

    try {
      const submissionId =
        normalizePositiveInteger(
          request.params
            .submissionId
        );

      if (!submissionId) {
        return response.status(422).json({
          success: false,
          message:
            "A valid submission is required."
        });
      }

      connection =
        await pool.getConnection();

      const submission =
        await loadSubmissionContext(
          connection,
          submissionId
        );

      if (!submission) {
        return response.status(404).json({
          success: false,
          message:
            "The staff submission could not be found."
        });
      }

      const access =
        verifySubmissionAccess({
          request,
          submission
        });

      if (!access.allowed) {
        return response
          .status(access.status)
          .json({
            success: false,
            message:
              access.message
          });
      }

      const [
        staffRows
      ] = await connection.query(
        `
          SELECT *
          FROM staff
          WHERE id = ?
          LIMIT 1
        `,
        [
          submission.staff_id
        ]
      );

      const [
        oldRecordRows
      ] = await connection.query(
        `
          SELECT *
          FROM old_record
          WHERE id = ?
          LIMIT 1
        `,
        [
          submission
            .old_record_id
        ]
      );

      const [
        newRecordRows
      ] = await connection.query(
        `
          SELECT *
          FROM new_record
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          submissionId
        ]
      );

      const personalDetails =
        await loadOptionalSection({
          connection,

          tableCandidates:
            optionalSectionTables
              .personal_details,

          submissionId
        });

      const employmentDetails =
        await loadOptionalSection({
          connection,

          tableCandidates:
            optionalSectionTables
              .employment_details,

          submissionId
        });

      const nextOfKin =
        await loadOptionalSection({
          connection,

          tableCandidates:
            optionalSectionTables
              .next_of_kin,

          submissionId
        });

      const dependents =
        await loadOptionalSection({
          connection,

          tableCandidates:
            optionalSectionTables
              .dependents,

          submissionId,

          multiple: true
        });

      const emergencyContact =
        await loadOptionalSection({
          connection,

          tableCandidates:
            optionalSectionTables
              .emergency_contact,

          submissionId
        });

      const qualifications =
        await loadOptionalSection({
          connection,

          tableCandidates:
            optionalSectionTables
              .qualifications,

          submissionId,

          multiple: true
        });

      const [
        documentRows
      ] = await connection.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            document_type,
            document_title,
            original_file_name,
            mime_type,
            file_size_bytes,
            created_at,
            updated_at
          FROM submission_documents
          WHERE submission_id = ?
          ORDER BY
            created_at ASC,
            id ASC
        `,
        [
          submissionId
        ]
      );

      const [
        declarationRows
      ] = await connection.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            declaration_accepted,
            declared_full_name,
            declaration_date,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_declaration
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          submissionId
        ]
      );

      const [
        historyRows
      ] = await connection.query(
        `
          SELECT
            action_record.id,
            action_record.action,
            action_record.previous_status,
            action_record.new_status,
            action_record.action_note,
            action_record.created_at,

            admin_user.id
              AS admin_user_id,

            admin_user.full_name
              AS admin_user_name,

            admin_user.role
              AS admin_user_role

          FROM submission_review_actions
            AS action_record

          INNER JOIN admin_users
            AS admin_user
            ON admin_user.id =
              action_record.admin_user_id

          WHERE
            action_record.submission_id = ?

          ORDER BY
            action_record.created_at DESC,
            action_record.id DESC
        `,
        [
          submissionId
        ]
      );

      await writeAdminAuditLog({
        adminUserId:
          request.admin.id,

        action:
          "SUBMISSION_VIEWED",

        entityType:
          "staff_submission",

        entityId:
          submissionId,

        description:
          `Administrator viewed submission ${submission.submission_reference}.`,

        ipAddress:
          getRequestIp(
            request
          ),

        userAgent:
          getUserAgent(
            request
          )
      });

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

        data: {
          submission: {
            ...submission,

            id:
              toNumber(
                submission.id
              ),

            staff_id:
              toNumber(
                submission.staff_id
              ),

            old_record_id:
              toNumber(
                submission
                  .old_record_id
              ),

            review_id:
              submission.review_id
                ? toNumber(
                    submission
                      .review_id
                  )
                : null,

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

          staff:
            staffRows[0] ||
            null,

          old_record:
            oldRecordRows[0] ||
            null,

          new_record:
            newRecordRows[0] ||
            null,

          sections: {
            personal_details:
              personalDetails,

            employment_details:
              employmentDetails,

            next_of_kin:
              nextOfKin,

            dependents,

            emergency_contact:
              emergencyContact,

            qualifications,

            documents:
              documentRows.map(
                (document) => ({
                  ...document,

                  id:
                    toNumber(
                      document.id
                    ),

                  file_size_bytes:
                    toNumber(
                      document
                        .file_size_bytes
                    )
                })
              ),

            declaration:
              declarationRows[0] ||
              null
          },

          history:
            historyRows,

          permissions:
            buildSubmissionPermissions(
              request,
              submission
            )
        }
      });
    } catch (error) {
      console.error(
        "Get submission detail error:",
        error
      );

      return sendDevelopmentError(
        response,
        error,
        "Unable to load the staff submission."
      );
    } finally {
      if (connection) {
        connection.release();
      }
    }
  };

/* =========================================================
   START REVIEW
   PATCH /api/admin/submissions/:submissionId/start-review
   ========================================================= */

const startSubmissionReview =
  async (
    request,
    response
  ) => {
    let connection = null;
    let transactionStarted =
      false;

    try {
      const submissionId =
        normalizePositiveInteger(
          request.params
            .submissionId
        );

      const reviewNote =
        normalizeNote(
          request.body
            ?.review_note
        );

      if (!submissionId) {
        return response.status(422).json({
          success: false,
          message:
            "A valid submission is required."
        });
      }

      connection =
        await pool.getConnection();

      await connection.beginTransaction();

      transactionStarted = true;

      const submission =
        await loadSubmissionContext(
          connection,
          submissionId,
          true
        );

      if (!submission) {
        await connection.rollback();

        transactionStarted = false;

        return response.status(404).json({
          success: false,
          message:
            "The staff submission could not be found."
        });
      }

      if (
        submission
          .submission_status !==
        "submitted"
      ) {
        await connection.rollback();

        transactionStarted = false;

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

          message:
            submission
              .submission_status ===
              "under_review"
              ? "This submission is already under review."
              : "This submission cannot currently be moved into review."
        });
      }

      const access =
        verifySubmissionAccess({
          request,
          submission
        });

      if (!access.allowed) {
        await connection.rollback();

        transactionStarted = false;

        return response
          .status(access.status)
          .json({
            success: false,
            message:
              access.message
          });
      }

      let assignedTo =
        submission.assigned_to;

      /*
       * Managers may start an unassigned
       * submission. It is automatically
       * assigned to the manager.
       */
      if (
        !assignedTo &&
        isManager(
          request.admin
        )
      ) {
        assignedTo =
          request.admin.id;
      }

      if (!assignedTo) {
        await connection.rollback();

        transactionStarted = false;

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

          message:
            "Assign this submission to a reviewer before starting the review."
        });
      }

      await connection.query(
        `
          INSERT INTO submission_reviews (
            submission_id,
            assigned_to,
            assigned_by,
            review_status,
            review_started_at,
            review_notes
          )
          VALUES (
            ?,
            ?,
            ?,
            'under_review',
            NOW(),
            ?
          )

          ON DUPLICATE KEY UPDATE
            assigned_to =
              COALESCE(
                assigned_to,
                VALUES(
                  assigned_to
                )
              ),

            assigned_by =
              COALESCE(
                assigned_by,
                VALUES(
                  assigned_by
                )
              ),

            review_status =
              'under_review',

            review_started_at =
              COALESCE(
                review_started_at,
                NOW()
              ),

            review_notes =
              CASE
                WHEN VALUES(
                  review_notes
                ) IS NULL
                  OR VALUES(
                    review_notes
                  ) = ''
                  THEN review_notes

                ELSE VALUES(
                  review_notes
                )
              END,

            updated_at =
              CURRENT_TIMESTAMP
        `,
        [
          submissionId,
          assignedTo,
          request.admin.id,
          reviewNote || null
        ]
      );

      await connection.query(
        `
          UPDATE staff_submissions
          SET submission_status =
            'under_review'
          WHERE id = ?
        `,
        [
          submissionId
        ]
      );

      await connection.query(
        `
          UPDATE new_record
          SET record_status =
            'under_review'
          WHERE submission_id = ?
        `,
        [
          submissionId
        ]
      );

      const [
        reviewRows
      ] = await connection.query(
        `
          SELECT id
          FROM submission_reviews
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          submissionId
        ]
      );

      const reviewId =
        reviewRows[0]?.id;

      await connection.query(
        `
          INSERT INTO submission_review_actions (
            submission_id,
            review_id,
            admin_user_id,
            action,
            previous_status,
            new_status,
            action_note,
            ip_address,
            user_agent
          )
          VALUES (
            ?,
            ?,
            ?,
            'review_started',
            'submitted',
            'under_review',
            ?,
            ?,
            ?
          )
        `,
        [
          submissionId,
          reviewId,
          request.admin.id,

          reviewNote ||
            "HR review started.",

          getRequestIp(
            request
          ),

          getUserAgent(
            request
          )
        ]
      );

      await writeAdminAuditLog({
        connection,

        adminUserId:
          request.admin.id,

        action:
          "SUBMISSION_REVIEW_STARTED",

        entityType:
          "staff_submission",

        entityId:
          submissionId,

        description:
          `Review started for submission ${submission.submission_reference}.`,

        metadata: {
          assigned_to:
            assignedTo,

          review_note:
            reviewNote ||
            null
        },

        ipAddress:
          getRequestIp(
            request
          ),

        userAgent:
          getUserAgent(
            request
          )
      });

      await connection.commit();

      transactionStarted = false;

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

        message:
          "The submission review has started successfully."
      });
    } catch (error) {
      if (
        connection &&
        transactionStarted
      ) {
        try {
          await connection.rollback();
        } catch (
          rollbackError
        ) {
          console.error(
            "Start review rollback error:",
            rollbackError
          );
        }
      }

      console.error(
        "Start submission review error:",
        error
      );

      return sendDevelopmentError(
        response,
        error,
        "Unable to start the submission review."
      );
    } finally {
      if (connection) {
        connection.release();
      }
    }
  };

/* =========================================================
   COMPLETE REVIEW ACTION
   ========================================================= */

const completeReviewAction =
  ({
    action,
    newStatus,
    noteField,
    noteRequired,
    successMessage
  }) =>
  async (
    request,
    response
  ) => {
    let connection = null;
    let transactionStarted =
      false;

    try {
      const submissionId =
        normalizePositiveInteger(
          request.params
            .submissionId
        );

      const actionNote =
        normalizeNote(
          request.body?.[
            noteField
          ] ||
          request.body
            ?.review_note ||
          request.body?.note
        );

      if (!submissionId) {
        return response.status(422).json({
          success: false,
          message:
            "A valid submission is required."
        });
      }

      if (
        noteRequired &&
        actionNote.length < 10
      ) {
        return response.status(422).json({
          success: false,

          message:
            "Provide a clear reason before completing this action.",

          errors: {
            [noteField]:
              "Enter at least 10 characters."
          }
        });
      }

      connection =
        await pool.getConnection();

      await connection.beginTransaction();

      transactionStarted = true;

      const submission =
        await loadSubmissionContext(
          connection,
          submissionId,
          true
        );

      if (!submission) {
        await connection.rollback();

        transactionStarted = false;

        return response.status(404).json({
          success: false,
          message:
            "The staff submission could not be found."
        });
      }

      const access =
        verifySubmissionAccess({
          request,
          submission
        });

      if (!access.allowed) {
        await connection.rollback();

        transactionStarted = false;

        return response
          .status(access.status)
          .json({
            success: false,
            message:
              access.message
          });
      }

      if (
        ![
          "submitted",
          "under_review"
        ].includes(
          submission
            .submission_status
        )
      ) {
        await connection.rollback();

        transactionStarted = false;

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

          message:
            "This submission has already been completed or returned."
        });
      }

      await connection.query(
        `
          INSERT INTO submission_reviews (
            submission_id,
            assigned_to,
            assigned_by,
            review_status
          )
          VALUES (
            ?,
            ?,
            ?,
            'pending'
          )

          ON DUPLICATE KEY UPDATE
            submission_id =
              VALUES(
                submission_id
              )
        `,
        [
          submissionId,
          submission.assigned_to ||
            request.admin.id,
          request.admin.id
        ]
      );

      if (
        newStatus ===
        "returned"
      ) {
        await connection.query(
          `
            UPDATE submission_reviews
            SET
              review_status =
                'returned',

              review_notes = ?,
              return_reason = ?,
              rejection_reason = NULL,

              reviewed_at = NOW(),
              completed_at = NULL,

              updated_at =
                CURRENT_TIMESTAMP

            WHERE submission_id = ?
          `,
          [
            actionNote,
            actionNote,
            submissionId
          ]
        );
      } else if (
        newStatus ===
        "rejected"
      ) {
        await connection.query(
          `
            UPDATE submission_reviews
            SET
              review_status =
                'rejected',

              review_notes = ?,
              return_reason = NULL,
              rejection_reason = ?,

              reviewed_at = NOW(),
              completed_at = NOW(),

              updated_at =
                CURRENT_TIMESTAMP

            WHERE submission_id = ?
          `,
          [
            actionNote ||
              "Submission rejected.",

            actionNote ||
              "Submission rejected.",

            submissionId
          ]
        );
      } else {
        await connection.query(
          `
            UPDATE submission_reviews
            SET
              review_status =
                'approved',

              review_notes = ?,
              return_reason = NULL,
              rejection_reason = NULL,

              reviewed_at = NOW(),
              completed_at = NOW(),

              updated_at =
                CURRENT_TIMESTAMP

            WHERE submission_id = ?
          `,
          [
            actionNote ||
              "Submission approved.",

            submissionId
          ]
        );
      }

      await connection.query(
        `
          UPDATE staff_submissions
          SET submission_status = ?
          WHERE id = ?
        `,
        [
          newStatus,
          submissionId
        ]
      );

      await connection.query(
        `
          UPDATE new_record
          SET record_status = ?
          WHERE submission_id = ?
        `,
        [
          newStatus,
          submissionId
        ]
      );


      /*
 * Create a new correction cycle when HR
 * returns a submission to the staff member.
 */
if (newStatus === "returned") {
  const correctionCycleNumber =
    Number(
      submission.return_count || 0
    ) + 1;

  const [
    correctionInsertResult
  ] = await connection.query(
    `
      INSERT INTO submission_correction_cycles (
        submission_id,
        cycle_number,
        returned_by,
        return_reason,
        correction_status,
        returned_at
      )
      VALUES (
        ?,
        ?,
        ?,
        ?,
        'awaiting_correction',
        NOW()
      )
    `,
    [
      submissionId,
      correctionCycleNumber,
      request.admin.id,
      actionNote
    ]
  );

  await connection.query(
    `
      UPDATE staff_submissions
      SET
        correction_status =
          'awaiting_correction',

        return_count = ?,

        last_returned_at =
          NOW(),

        last_returned_by = ?,

        resubmitted_at =
          NULL

      WHERE id = ?
    `,
    [
      correctionCycleNumber,
      request.admin.id,
      submissionId
    ]
  );

  await connection.query(
    `
      INSERT INTO submission_correction_events (
        correction_cycle_id,
        submission_id,
        actor_type,
        admin_user_id,
        action,
        action_note,
        ip_address,
        user_agent
      )
      VALUES (
        ?,
        ?,
        'admin',
        ?,
        'returned',
        ?,
        ?,
        ?
      )
    `,
    [
      correctionInsertResult.insertId,
      submissionId,
      request.admin.id,
      actionNote,
      getRequestIp(request),
      getUserAgent(request)
    ]
  );
}

if (
  newStatus === "approved" ||
  newStatus === "rejected"
) {
  await connection.query(
    `
      UPDATE staff_submissions
      SET correction_status =
        'not_required'
      WHERE id = ?
    `,
    [
      submissionId
    ]
  );
}

      const [
        reviewRows
      ] = await connection.query(
        `
          SELECT id
          FROM submission_reviews
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          submissionId
        ]
      );

      const reviewId =
        reviewRows[0]?.id;

      await connection.query(
        `
          INSERT INTO submission_review_actions (
            submission_id,
            review_id,
            admin_user_id,
            action,
            previous_status,
            new_status,
            action_note,
            ip_address,
            user_agent
          )
          VALUES (
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?
          )
        `,
        [
          submissionId,
          reviewId,
          request.admin.id,
          action,

          submission
            .submission_status,

          newStatus,

          actionNote ||
            successMessage,

          getRequestIp(
            request
          ),

          getUserAgent(
            request
          )
        ]
      );

      await writeAdminAuditLog({
        connection,

        adminUserId:
          request.admin.id,

        action:
          `SUBMISSION_${action.toUpperCase()}`,

        entityType:
          "staff_submission",

        entityId:
          submissionId,

        description:
          `${successMessage} Reference: ${submission.submission_reference}.`,

        metadata: {
          previous_status:
            submission
              .submission_status,

          new_status:
            newStatus,

          action_note:
            actionNote ||
            null
        },

        ipAddress:
          getRequestIp(
            request
          ),

        userAgent:
          getUserAgent(
            request
          )
      });

      await connection.commit();

      transactionStarted = false;

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

        data: {
          submission_id:
            submissionId,

          submission_status:
            newStatus
        }
      });
    } catch (error) {
      if (
        connection &&
        transactionStarted
      ) {
        try {
          await connection.rollback();
        } catch (
          rollbackError
        ) {
          console.error(
            "Review action rollback error:",
            rollbackError
          );
        }
      }

      console.error(
        `${action} submission error:`,
        error
      );

      return sendDevelopmentError(
        response,
        error,
        `Unable to ${action} the submission.`
      );
    } finally {
      if (connection) {
        connection.release();
      }
    }
  };

const approveSubmission =
  completeReviewAction({
    action: "approved",
    newStatus: "approved",
    noteField: "review_note",
    noteRequired: false,

    successMessage:
      "The staff submission has been approved."
  });

const returnSubmission =
  completeReviewAction({
    action: "returned",
    newStatus: "returned",
    noteField: "return_reason",
    noteRequired: true,

    successMessage:
      "The submission has been returned to the staff member for correction."
  });

const rejectSubmission =
  completeReviewAction({
    action: "rejected",
    newStatus: "rejected",
    noteField: "rejection_reason",
    noteRequired: true,

    successMessage:
      "The staff submission has been rejected."
  });

/* =========================================================
   DOCUMENT DOWNLOAD
   GET /api/admin/submissions/:submissionId/documents/:documentId/download
   ========================================================= */

const downloadSubmissionDocument =
  async (
    request,
    response
  ) => {
    try {
      const submissionId =
        normalizePositiveInteger(
          request.params
            .submissionId
        );

      const documentId =
        normalizePositiveInteger(
          request.params
            .documentId
        );

      if (
        !submissionId ||
        !documentId
      ) {
        return response.status(422).json({
          success: false,
          message:
            "A valid document is required."
        });
      }

      const submission =
        await loadSubmissionContext(
          pool,
          submissionId
        );

      if (!submission) {
        return response.status(404).json({
          success: false,
          message:
            "The staff submission could not be found."
        });
      }

      const access =
        verifySubmissionAccess({
          request,
          submission
        });

      if (!access.allowed) {
        return response
          .status(access.status)
          .json({
            success: false,
            message:
              access.message
          });
      }

      const [
        documentRows
      ] = await pool.query(
        `
          SELECT
            id,
            submission_id,
            original_file_name,
            stored_file_name,
            storage_path,
            mime_type
          FROM submission_documents
          WHERE
            id = ?
            AND submission_id = ?
          LIMIT 1
        `,
        [
          documentId,
          submissionId
        ]
      );

      const document =
        documentRows[0];

      if (!document) {
        return response.status(404).json({
          success: false,
          message:
            "The supporting document could not be found."
        });
      }

            /* =====================================================
         RESOLVE THE STORED DOCUMENT

         Use the same storage resolver used when documents
         are uploaded through the public portal.
         ===================================================== */

      const storedPathCandidates = [
        document.storage_path,

        document.stored_file_name
          ? (
              `submission_${submissionId}/` +
              document.stored_file_name
            )
          : null,

        document.stored_file_name
      ].filter(Boolean);

      let filePath =
        null;

      for (
        const storedPath of
        storedPathCandidates
      ) {
        try {
          const resolvedPath =
            resolvePrivateFile(
              storedPath
            );

          if (
            resolvedPath &&
            fs.existsSync(
              resolvedPath
            )
          ) {
            filePath =
              resolvedPath;

            break;
          }
        } catch (
          pathError
        ) {
          if (
            process.env.NODE_ENV !==
            "production"
          ) {
            console.warn(
              "Unable to resolve document path:",
              {
                storedPath,
                message:
                  pathError.message
              }
            );
          }
        }
      }

      if (!filePath) {
        console.error(
          "Supporting document file was not found:",
          {
            submissionId,
            documentId,

            storagePath:
              document.storage_path,

            storedFileName:
              document.stored_file_name,

            attemptedPaths:
              storedPathCandidates
          }
        );

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

            message:
              "The document file is missing from storage."
          });
      }
      
      await writeAdminAuditLog({
        adminUserId:
          request.admin.id,

        action:
          "SUBMISSION_DOCUMENT_DOWNLOADED",

        entityType:
          "submission_document",

        entityId:
          documentId,

        description:
          `Supporting document downloaded from submission ${submission.submission_reference}.`,

        metadata: {
          submission_id:
            submissionId,

          original_file_name:
            document
              .original_file_name
        },

        ipAddress:
          getRequestIp(
            request
          ),

        userAgent:
          getUserAgent(
            request
          )
      });

      response.setHeader(
        "Cache-Control",
        "private, no-store"
      );

      if (
        document.mime_type
      ) {
        response.type(
          document.mime_type
        );
      }

      return response.download(
        filePath,

        document.original_file_name ||
          document.stored_file_name ||
          "supporting-document"
      );
    } catch (error) {
      console.error(
        "Download submission document error:",
        error
      );

      return sendDevelopmentError(
        response,
        error,
        "Unable to download the supporting document."
      );
    }
  };

module.exports = {
  getSubmissionDetail,
  startSubmissionReview,
  approveSubmission,
  returnSubmission,
  rejectSubmission,
  downloadSubmissionDocument
};