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

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

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

const {
  isSubmissionEditable
} = require(
  "../utils/submissionState"
);

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

const allowedDocumentTypes =
  new Set([
    "academic_certificate",
    "professional_certificate",
    "appointment_letter",
    "promotion_letter",
    "other"
  ]);

const removeStoredFile =
  async (absoluteFilePath) => {
    if (!absoluteFilePath) {
      return;
    }

    try {
      await fs.unlink(
        absoluteFilePath
      );
    } catch (error) {
      if (
        error.code !== "ENOENT"
      ) {
        console.error(
          "Unable to remove stored document:",
          error
        );
      }
    }
  };

const fileSignatureMatches =
  async (
    absoluteFilePath,
    mimeType
  ) => {
    const fileHandle =
      await fs.open(
        absoluteFilePath,
        "r"
      );

    try {
      const signatureBuffer =
        Buffer.alloc(8);

      const {
        bytesRead
      } =
        await fileHandle.read(
          signatureBuffer,
          0,
          signatureBuffer.length,
          0
        );

      const signature =
        signatureBuffer.subarray(
          0,
          bytesRead
        );

      if (
        mimeType ===
        "application/pdf"
      ) {
        return (
          signature
            .subarray(0, 5)
            .toString("ascii") ===
          "%PDF-"
        );
      }

      if (
        mimeType ===
        "image/jpeg"
      ) {
        return (
          signature[0] === 0xff &&
          signature[1] === 0xd8 &&
          signature[2] === 0xff
        );
      }

      if (
        mimeType ===
        "image/png"
      ) {
        const pngSignature =
          Buffer.from([
            0x89,
            0x50,
            0x4e,
            0x47,
            0x0d,
            0x0a,
            0x1a,
            0x0a
          ]);

        return signature.equals(
          pngSignature
        );
      }

      return false;
    } finally {
      await fileHandle.close();
    }
  };

const mapDocumentRecord = (
  document
) => ({
  id:
    document.id,

  document_type:
    document.document_type,

  document_title:
    document.document_title,

  original_file_name:
    document.original_file_name,

  mime_type:
    document.mime_type,

  file_size_bytes:
    document.file_size_bytes,

  created_at:
    document.created_at,

  updated_at:
    document.updated_at
});


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

const allowedEducationLevels =
  new Set([
    "masters_degree",
    "first_degree",
    "diploma",
    "other"
  ]);

const allowedCertificationCodes =
  new Set([
    "ICAG",
    "ACCA",
    "CIMA",
    "CIHRM",
    "CPA",
    "OTHER"
  ]);

const normalizeNullableBoolean = (
  value
) => {
  if (
    value === true ||
    value === 1 ||
    value === "1" ||
    value === "true" ||
    value === "yes"
  ) {
    return true;
  }

  if (
    value === false ||
    value === 0 ||
    value === "0" ||
    value === "false" ||
    value === "no"
  ) {
    return false;
  }

  return null;
};



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

const normalizeText = (
  value,
  maximumLength = 255
) => {
  if (
    value === null ||
    value === undefined
  ) {
    return null;
  }

  const normalizedValue =
    String(value).trim();

  if (!normalizedValue) {
    return null;
  }

  return normalizedValue.slice(
    0,
    maximumLength
  );
};

const normalizeRequiredText = (
  value,
  maximumLength = 255
) => {
  const normalizedValue =
    normalizeText(
      value,
      maximumLength
    );

  return normalizedValue || "";
};

const normalizeEmail = (value) => {
  const email =
    normalizeText(value, 150);

  if (!email) {
    return null;
  }

  return email.toLowerCase();
};

const normalizePhone = (value) => {
  const phone =
    normalizeText(value, 30);

  if (!phone) {
    return null;
  }

  return phone.replace(
    /[^\d+]/g,
    ""
  );
};

const normalizeRegionId = (value) => {
  if (
    value === null ||
    value === undefined ||
    value === ""
  ) {
    return null;
  }

  const regionId =
    Number(value);

  if (
    !Number.isInteger(regionId) ||
    regionId < 1
  ) {
    return null;
  }

  return regionId;
};

const isValidEmail = (value) => {
  if (!value) {
    return true;
  }

  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(
    value
  );
};

const isValidPhone = (value) => {
  if (!value) {
    return true;
  }

  return /^\+?\d{9,15}$/.test(
    value
  );
};

const isValidGhanaCardNumber = (
  value
) => {
  if (!value) {
    return true;
  }

  return /^GHA-\d{9}-\d$/i.test(
    value
  );
};

const getSubmissionContext = (
  request
) => {
  return {
    submissionId:
      request.staffSubmission
        ?.submission_id,

    staffId:
      request.staffSubmission
        ?.staff_id,

    campaignId:
      request.staffSubmission
        ?.campaign_id
  };
};

const validateSubmissionSession =
  async ({
    submissionId,
    staffId,
    campaignId,
    connection = pool
  }) => {
    if (
      !submissionId ||
      !staffId ||
      !campaignId
    ) {
      return null;
    }

    const [rows] =
      await connection.query(
        `
          SELECT
            ss.id,
            ss.staff_id,
            ss.campaign_id,
            ss.submission_reference,
            ss.submission_status,

            ruc.status
              AS campaign_status,

            ruc.start_date,
            ruc.end_date

          FROM staff_submissions
            AS ss

          INNER JOIN record_update_campaigns
            AS ruc
            ON ruc.id =
              ss.campaign_id

          WHERE
            ss.id = ?

            AND ss.staff_id = ?

            AND ss.campaign_id = ?

          LIMIT 1
        `,
        [
          submissionId,
          staffId,
          campaignId
        ]
      );

    const submission =
      rows[0];

    if (!submission) {
      return null;
    }

    return {
      ...submission,

      editable:
        isSubmissionEditable(
          submission
            .submission_status
        )
    };
  };

/* =========================================================
   UPDATE SECTION PROGRESS
   ========================================================= */

const updateSectionProgress =
  async ({
    connection,
    submissionId,
    sectionKey,
    sectionName,
    sectionOrder,
    status
  }) => {
    await connection.query(
      `
        INSERT INTO submission_section_progress (
          submission_id,
          section_key,
          section_name,
          section_order,
          status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          ?,
          CASE
            WHEN ? = 'completed'
            THEN NOW()
            ELSE NULL
          END
        )

        ON DUPLICATE KEY UPDATE
          section_name =
            VALUES(
              section_name
            ),

          section_order =
            VALUES(
              section_order
            ),

          status =
            VALUES(
              status
            ),

          completed_at =
            CASE
              WHEN VALUES(
                status
              ) = 'completed'
              THEN NOW()
              ELSE NULL
            END,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        submissionId,
        sectionKey,
        sectionName,
        sectionOrder,
        status,
        status
      ]
    );
  };
/* =========================================================
   GET LOOKUP DATA
   GET /api/public/form/lookups
   ========================================================= */

const getFormLookups = async (
  request,
  response
) => {
  try {
    const [regions] =
      await pool.query(
        `
          SELECT
            id,
            name,
            code
          FROM regions
          ORDER BY name ASC
        `
      );

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

        genders: [
          {
            value: "male",
            label: "Male"
          },
          {
            value: "female",
            label: "Female"
          }
        ],

        marital_statuses: [
          {
            value: "single",
            label: "Single"
          },
          {
            value: "married",
            label: "Married"
          },
          {
            value: "divorced",
            label: "Divorced"
          },
          {
            value: "widowed",
            label: "Widowed"
          },
          {
            value: "separated",
            label: "Separated"
          }
        ]
      }
    });
  } catch (error) {
    console.error(
      "Get form lookups error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to load the form options."
    });
  }
};

/* =========================================================
   GET PERSONAL DETAILS
   GET /api/public/form/personal-details
   ========================================================= */

const getPersonalDetails = async (
  request,
  response
) => {
  try {
    const context =
      getSubmissionContext(request);

    const submission =
      await validateSubmissionSession(
        context
      );

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

    const [savedRows] =
      await pool.query(
        `
          SELECT
            spd.id,
            spd.submission_id,
            spd.staff_id,
            spd.surname,
            spd.first_name,
            spd.middle_name,
            spd.gender,
            spd.marital_status,
            spd.nationality,
            spd.ghana_card_number,
            spd.ssnit_number,
            spd.home_town,
            spd.home_region_id,
            r.name AS home_region_name,
            spd.residential_address,
            spd.gps_address,
            spd.primary_mobile,
            spd.secondary_mobile,
            spd.official_email,
            spd.personal_email,
            spd.completion_status,
            spd.completed_at,
            spd.updated_at
          FROM submission_personal_details spd
          LEFT JOIN regions r
            ON r.id =
              spd.home_region_id
          WHERE spd.submission_id = ?
          LIMIT 1
        `,
        [context.submissionId]
      );

    if (savedRows.length > 0) {
      return response.status(200).json({
        success: true,
        data: {
          source:
            "submission_draft",

          editable:
            submission.editable,

          personal_details:
            savedRows[0]
        }
      });
    }

    const [staffRows] =
      await pool.query(
        `
          SELECT
            s.id AS staff_id,
            s.surname,
            s.first_name,
            s.middle_name,
            s.gender,
            s.marital_status,
            s.nationality,
            s.ghana_card_number,
            s.ssnit_number,
            s.home_town,
            s.home_region_id,
            r.name AS home_region_name,
            s.residential_address,
            s.gps_address,
            s.primary_mobile,
            s.secondary_mobile,
            s.official_email,
            s.personal_email
          FROM staff s
          LEFT JOIN regions r
            ON r.id =
              s.home_region_id
          WHERE s.id = ?
          LIMIT 1
        `,
        [context.staffId]
      );

    const staff =
      staffRows[0];

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

    return response.status(200).json({
      success: true,
      data: {
        source:
          "official_staff_record",

        editable:
          submission.editable,

        personal_details: {
          ...staff,
          submission_id:
            context.submissionId,

          completion_status:
            "not_started",

          completed_at:
            null,

          updated_at:
            null
        }
      }
    });
  } catch (error) {
    console.error(
      "Get personal details error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to load the personal details."
    });
  }
};

/* =========================================================
   SAVE PERSONAL DETAILS
   PUT /api/public/form/personal-details
   ========================================================= */

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

  try {
    const context =
      getSubmissionContext(request);

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const submission =
      await validateSubmissionSession({
        ...context,
        connection
      });

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

      transactionStarted = false;

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

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

      transactionStarted = false;

      return response.status(409).json({
        success: false,
        message:
          "This submission can no longer be edited because it has already been submitted for review."
      });
    }

    const surname =
      normalizeRequiredText(
        request.body?.surname,
        100
      );

    const firstName =
      normalizeRequiredText(
        request.body?.first_name,
        100
      );

    const middleName =
      normalizeText(
        request.body?.middle_name,
        100
      );

    const gender =
      normalizeRequiredText(
        request.body?.gender,
        20
      ).toLowerCase();

    const maritalStatus =
      normalizeRequiredText(
        request.body?.marital_status,
        30
      ).toLowerCase();

    const nationality =
      normalizeRequiredText(
        request.body?.nationality,
        100
      );

    const ghanaCardNumber =
      normalizeText(
        request.body?.ghana_card_number,
        30
      )?.toUpperCase() || null;

    const ssnitNumber =
      normalizeText(
        request.body?.ssnit_number,
        30
      );

    const homeTown =
      normalizeText(
        request.body?.home_town,
        150
      );

    const homeRegionId =
      normalizeRegionId(
        request.body?.home_region_id
      );

    const residentialAddress =
      normalizeText(
        request.body
          ?.residential_address,
        2000
      );

    const gpsAddress =
      normalizeText(
        request.body?.gps_address,
        30
      )?.toUpperCase() || null;

    const primaryMobile =
      normalizePhone(
        request.body?.primary_mobile
      );

    const secondaryMobile =
      normalizePhone(
        request.body?.secondary_mobile
      );

    const officialEmail =
      normalizeEmail(
        request.body?.official_email
      );

    const personalEmail =
      normalizeEmail(
        request.body?.personal_email
      );

    const markCompleted =
      request.body?.mark_completed ===
      true;

    const allowedGenders = [
      "male",
      "female"
    ];

    const allowedMaritalStatuses = [
      "single",
      "married",
      "divorced",
      "widowed",
      "separated"
    ];

    const validationErrors = {};

    if (!surname) {
      validationErrors.surname =
        "Surname is required.";
    }

    if (!firstName) {
      validationErrors.first_name =
        "First name is required.";
    }

    if (
      !allowedGenders.includes(
        gender
      )
    ) {
      validationErrors.gender =
        "Select a valid gender.";
    }

    if (
      !allowedMaritalStatuses.includes(
        maritalStatus
      )
    ) {
      validationErrors.marital_status =
        "Select a valid marital status.";
    }

    if (!nationality) {
      validationErrors.nationality =
        "Nationality is required.";
    }

    if (
      ghanaCardNumber &&
      !isValidGhanaCardNumber(
        ghanaCardNumber
      )
    ) {
      validationErrors
        .ghana_card_number =
        "Enter the Ghana Card number in the format GHA-123456789-0.";
    }

    if (
      !isValidPhone(
        primaryMobile
      )
    ) {
      validationErrors.primary_mobile =
        "Enter a valid primary mobile number.";
    }

    if (
      !isValidPhone(
        secondaryMobile
      )
    ) {
      validationErrors.secondary_mobile =
        "Enter a valid secondary mobile number.";
    }

    if (
      !isValidEmail(
        officialEmail
      )
    ) {
      validationErrors.official_email =
        "Enter a valid official email address.";
    }

    if (
      !isValidEmail(
        personalEmail
      )
    ) {
      validationErrors.personal_email =
        "Enter a valid personal email address.";
    }

    if (homeRegionId) {
      const [regionRows] =
        await connection.query(
          `
            SELECT id
            FROM regions
            WHERE id = ?
            LIMIT 1
          `,
          [homeRegionId]
        );

      if (regionRows.length === 0) {
        validationErrors.home_region_id =
          "Select a valid region.";
      }
    }

    if (markCompleted) {
      if (!homeTown) {
        validationErrors.home_town =
          "Home town is required.";
      }

      if (!homeRegionId) {
        validationErrors.home_region_id =
          "Home region is required.";
      }

      if (!residentialAddress) {
        validationErrors
          .residential_address =
          "Residential address is required.";
      }

      if (!gpsAddress) {
        validationErrors.gps_address =
          "GPS address is required.";
      }

      if (!primaryMobile) {
        validationErrors.primary_mobile =
          "Primary mobile number is required.";
      }

      if (!officialEmail) {
        validationErrors.official_email =
          "Official email address is required.";
      }

      if (!ghanaCardNumber) {
        validationErrors
          .ghana_card_number =
          "Ghana Card number is required.";
      }
    }

    if (
      Object.keys(
        validationErrors
      ).length > 0
    ) {
      await connection.rollback();

      transactionStarted = false;

      return response.status(422).json({
        success: false,
        message:
          "Some personal details require correction.",

        errors:
          validationErrors
      });
    }

    const completionStatus =
      markCompleted
        ? "completed"
        : "in_progress";

    await connection.query(
      `
        INSERT INTO submission_personal_details (
          submission_id,
          staff_id,
          surname,
          first_name,
          middle_name,
          gender,
          marital_status,
          nationality,
          ghana_card_number,
          ssnit_number,
          home_town,
          home_region_id,
          residential_address,
          gps_address,
          primary_mobile,
          secondary_mobile,
          official_email,
          personal_email,
          completion_status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          CASE
            WHEN ? = 'completed'
            THEN NOW()
            ELSE NULL
          END
        )
        ON DUPLICATE KEY UPDATE
          surname =
            VALUES(surname),

          first_name =
            VALUES(first_name),

          middle_name =
            VALUES(middle_name),

          gender =
            VALUES(gender),

          marital_status =
            VALUES(marital_status),

          nationality =
            VALUES(nationality),

          ghana_card_number =
            VALUES(ghana_card_number),

          ssnit_number =
            VALUES(ssnit_number),

          home_town =
            VALUES(home_town),

          home_region_id =
            VALUES(home_region_id),

          residential_address =
            VALUES(residential_address),

          gps_address =
            VALUES(gps_address),

          primary_mobile =
            VALUES(primary_mobile),

          secondary_mobile =
            VALUES(secondary_mobile),

          official_email =
            VALUES(official_email),

          personal_email =
            VALUES(personal_email),

          completion_status =
            VALUES(completion_status),

          completed_at =
            CASE
              WHEN VALUES(
                completion_status
              ) = 'completed'
              THEN NOW()
              ELSE NULL
            END,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        context.staffId,
        surname,
        firstName,
        middleName,
        gender,
        maritalStatus,
        nationality,
        ghanaCardNumber,
        ssnitNumber,
        homeTown,
        homeRegionId,
        residentialAddress,
        gpsAddress,
        primaryMobile,
        secondaryMobile,
        officialEmail,
        personalEmail,
        completionStatus,
        completionStatus
      ]
    );

    await updateSectionProgress({
      connection,

      submissionId:
        context.submissionId,

      sectionKey:
        "personal_details",

      sectionName:
        "Personal Details",

      sectionOrder:
        1,

      status:
        completionStatus
    });

    await connection.query(
      `
        UPDATE staff_submissions
        SET updated_at =
          CURRENT_TIMESTAMP
        WHERE id = ?
      `,
      [context.submissionId]
    );

    await connection.commit();

    transactionStarted = false;

    const [savedRows] =
      await pool.query(
        `
          SELECT
            spd.*,
            r.name AS home_region_name
          FROM submission_personal_details spd
          LEFT JOIN regions r
            ON r.id =
              spd.home_region_id
          WHERE spd.submission_id = ?
          LIMIT 1
        `,
        [context.submissionId]
      );

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

      message:
        completionStatus ===
        "completed"
          ? "Personal details completed successfully."
          : "Personal details saved as a draft.",

      data: {
        personal_details:
          savedRows[0],

        next_section:
          completionStatus ===
          "completed"
            ? "employment_details"
            : "personal_details"
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Save personal details rollback error:",
          rollbackError
        );
      }
    }

    console.error(
      "Save personal details error:",
      error
    );

    if (
      error.code ===
      "ER_DATA_TOO_LONG"
    ) {
      return response.status(422).json({
        success: false,
        message:
          "One or more values are longer than the permitted length."
      });
    }

    return response.status(500).json({
      success: false,
      message:
        "Unable to save the personal details."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

/* =========================================================
   GET FORM PROGRESS
   GET /api/public/form/progress
   ========================================================= */

const getFormProgress = async (
  request,
  response
) => {
  try {
    const context =
      getSubmissionContext(request);

    const submission =
      await validateSubmissionSession(
        context
      );

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

    const sectionDefinitions = [
      {
        section_key:
          "personal_details",
        section_name:
          "Personal Details",
        section_order:
          1
      },
      {
        section_key:
          "employment_details",
        section_name:
          "Employment Details",
        section_order:
          2
      },
      {
        section_key:
          "next_of_kin",
        section_name:
          "Next of Kin",
        section_order:
          3
      },
      {
        section_key:
          "dependents",
        section_name:
          "Dependents",
        section_order:
          4
      },
      {
        section_key:
          "emergency_contact",
        section_name:
          "Emergency Contact",
        section_order:
          5
      },
      {
        section_key:
          "qualifications",
        section_name:
          "Qualifications",
        section_order:
          6
      },
      {
        section_key:
          "documents",
        section_name:
          "Supporting Documents",
        section_order:
          7
      },
      {
        section_key:
          "declaration",
        section_name:
          "Review and Declaration",
        section_order:
          8
      }
    ];

    const [savedProgress] =
      await pool.query(
        `
          SELECT
            section_key,
            section_name,
            section_order,
            status,
            completed_at,
            updated_at
          FROM submission_section_progress
          WHERE submission_id = ?
          ORDER BY section_order ASC
        `,
        [context.submissionId]
      );

    const savedProgressMap =
      new Map(
        savedProgress.map(
          (section) => [
            section.section_key,
            section
          ]
        )
      );

    const sections =
      sectionDefinitions.map(
        (definition) => {
          const saved =
            savedProgressMap.get(
              definition.section_key
            );

          return {
            ...definition,
            status:
              saved?.status ||
              "not_started",

            completed_at:
              saved?.completed_at ||
              null,

            updated_at:
              saved?.updated_at ||
              null
          };
        }
      );

    const completedSections =
      sections.filter(
        (section) =>
          section.status ===
          "completed"
      ).length;

    const totalSections =
      sections.length;

    const percentage =
      Math.round(
        (
          completedSections /
          totalSections
        ) * 100
      );

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

      data: {
        submission_reference:
          submission
            .submission_reference,

        submission_status:
          submission
            .submission_status,

        editable:
          submission.editable,

        completed_sections:
          completedSections,

        total_sections:
          totalSections,

        percentage,

        sections
      }
    });
  } catch (error) {
    console.error(
      "Get form progress error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to retrieve the form progress."
    });
  }
};


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

const normalizeDate = (value) => {
  if (
    value === null ||
    value === undefined ||
    value === ""
  ) {
    return null;
  }

  const normalizedValue =
    String(value).trim();

  if (
    !/^\d{4}-\d{2}-\d{2}$/.test(
      normalizedValue
    )
  ) {
    return null;
  }

  const parsedDate =
    new Date(
      `${normalizedValue}T00:00:00`
    );

  if (
    Number.isNaN(
      parsedDate.getTime()
    )
  ) {
    return null;
  }

  return normalizedValue;
};

const isFutureDate = (value) => {
  if (!value) {
    return false;
  }

  const selectedDate =
    new Date(
      `${value}T00:00:00`
    );

  const today =
    new Date();

  today.setHours(
    0,
    0,
    0,
    0
  );

  return selectedDate > today;
};

/* =========================================================
   GET EMPLOYMENT DETAILS
   GET /api/public/form/employment-details
   ========================================================= */

const getEmploymentDetails = async (
  request,
  response
) => {
  try {
    const context =
      getSubmissionContext(request);

    const submission =
      await validateSubmissionSession(
        context
      );

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

    const [savedRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            current_designation,
            grade,
            organisation_structure,
            date_first_appointment,
            date_last_promotion,
            current_duty_station,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_employment_details
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    if (
      savedRows.length > 0
    ) {
      return response.status(200).json({
        success: true,

        data: {
          source:
            "submission_draft",

          editable:
            submission.editable,

          employment_details:
            savedRows[0]
        }
      });
    }

    const [employmentRows] =
      await pool.query(
        `
          SELECT
            ed.id,
            ed.staff_id,
            ed.current_designation,
            ed.grade,

            CONCAT_WS(
              ' / ',
              NULLIF(ed.mda_name, ''),
              NULLIF(ed.mmda_name, ''),
              NULLIF(r.name, ''),
              NULLIF(d.name, ''),
              NULLIF(dv.name, ''),
              NULLIF(u.name, '')
            ) AS organisation_structure,

            ed.date_first_appointment,
            ed.date_last_promotion,
            ed.current_duty_station

          FROM employment_details ed

          LEFT JOIN regions r
            ON r.id =
              ed.region_id

          LEFT JOIN directorates d
            ON d.id =
              ed.directorate_id

          LEFT JOIN divisions dv
            ON dv.id =
              ed.division_id

          LEFT JOIN units u
            ON u.id =
              ed.unit_id

          WHERE ed.staff_id = ?
          LIMIT 1
        `,
        [
          context.staffId
        ]
      );

    const officialEmployment =
      employmentRows[0];

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

        data: {
          source:
            "empty_record",

          editable:
            submission.editable,

          employment_details: {
            submission_id:
              context.submissionId,

            staff_id:
              context.staffId,

            current_designation:
              "",

            grade:
              "",

            organisation_structure:
              "",

            date_first_appointment:
              null,

            date_last_promotion:
              null,

            current_duty_station:
              "",

            completion_status:
              "not_started",

            completed_at:
              null,

            updated_at:
              null
          }
        }
      });
    }

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

      data: {
        source:
          "official_employment_record",

        editable:
          submission.editable,

        employment_details: {
          ...officialEmployment,

          submission_id:
            context.submissionId,

          completion_status:
            "not_started",

          completed_at:
            null,

          updated_at:
            null
        }
      }
    });
  } catch (error) {
    console.error(
      "Get employment details error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to load the employment details."
    });
  }
};

/* =========================================================
   SAVE EMPLOYMENT DETAILS
   PUT /api/public/form/employment-details
   ========================================================= */

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

  try {
    const context =
      getSubmissionContext(request);

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const submission =
      await validateSubmissionSession({
        ...context,
        connection
      });

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

      transactionStarted = false;

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

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

      transactionStarted = false;

      return response.status(409).json({
        success: false,
        message:
          "This submission can no longer be edited because it has already been submitted for review."
      });
    }

    const currentDesignation =
      normalizeText(
        request.body
          ?.current_designation,
        150
      );

    const grade =
      normalizeText(
        request.body?.grade,
        100
      );

    const organisationStructure =
      normalizeText(
        request.body
          ?.organisation_structure,
        500
      );

    const rawFirstAppointment =
      request.body
        ?.date_first_appointment;

    const rawLastPromotion =
      request.body
        ?.date_last_promotion;

    const dateFirstAppointment =
      normalizeDate(
        rawFirstAppointment
      );

    const dateLastPromotion =
      normalizeDate(
        rawLastPromotion
      );

    const currentDutyStation =
      normalizeText(
        request.body
          ?.current_duty_station,
        180
      );

    const markCompleted =
      request.body
        ?.mark_completed === true;

    const validationErrors = {};

    if (
      rawFirstAppointment &&
      !dateFirstAppointment
    ) {
      validationErrors
        .date_first_appointment =
        "Enter a valid date of first appointment.";
    }

    if (
      rawLastPromotion &&
      !dateLastPromotion
    ) {
      validationErrors
        .date_last_promotion =
        "Enter a valid date of last promotion.";
    }

    if (
      dateFirstAppointment &&
      isFutureDate(
        dateFirstAppointment
      )
    ) {
      validationErrors
        .date_first_appointment =
        "Date of first appointment cannot be in the future.";
    }

    if (
      dateLastPromotion &&
      isFutureDate(
        dateLastPromotion
      )
    ) {
      validationErrors
        .date_last_promotion =
        "Date of last promotion cannot be in the future.";
    }

    if (
      dateFirstAppointment &&
      dateLastPromotion &&
      new Date(
        `${dateLastPromotion}T00:00:00`
      ) <
        new Date(
          `${dateFirstAppointment}T00:00:00`
        )
    ) {
      validationErrors
        .date_last_promotion =
        "Date of last promotion cannot be earlier than the date of first appointment.";
    }

    if (markCompleted) {
      if (!currentDesignation) {
        validationErrors
          .current_designation =
          "Current designation is required.";
      }

      if (!grade) {
        validationErrors.grade =
          "Grade is required.";
      }

      if (
        !organisationStructure
      ) {
        validationErrors
          .organisation_structure =
          "Region, MDA, MMDA, Directorate, Division or Unit is required.";
      }

      if (
        !dateFirstAppointment
      ) {
        validationErrors
          .date_first_appointment =
          "Date of first appointment is required.";
      }

      if (
        !currentDutyStation
      ) {
        validationErrors
          .current_duty_station =
          "Current duty station is required.";
      }
    }

    if (
      Object.keys(
        validationErrors
      ).length > 0
    ) {
      await connection.rollback();

      transactionStarted = false;

      return response.status(422).json({
        success: false,
        message:
          "Some employment details require correction.",
        errors:
          validationErrors
      });
    }

    const completionStatus =
      markCompleted
        ? "completed"
        : "in_progress";

    await connection.query(
      `
        INSERT INTO submission_employment_details (
          submission_id,
          staff_id,
          current_designation,
          grade,
          organisation_structure,
          date_first_appointment,
          date_last_promotion,
          current_duty_station,
          completion_status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          CASE
            WHEN ? = 'completed'
            THEN NOW()
            ELSE NULL
          END
        )

        ON DUPLICATE KEY UPDATE
          current_designation =
            VALUES(
              current_designation
            ),

          grade =
            VALUES(grade),

          organisation_structure =
            VALUES(
              organisation_structure
            ),

          date_first_appointment =
            VALUES(
              date_first_appointment
            ),

          date_last_promotion =
            VALUES(
              date_last_promotion
            ),

          current_duty_station =
            VALUES(
              current_duty_station
            ),

          completion_status =
            VALUES(
              completion_status
            ),

          completed_at =
            CASE
              WHEN VALUES(
                completion_status
              ) = 'completed'
              THEN NOW()
              ELSE NULL
            END,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        context.staffId,
        currentDesignation,
        grade,
        organisationStructure,
        dateFirstAppointment,
        dateLastPromotion,
        currentDutyStation,
        completionStatus,
        completionStatus
      ]
    );

    await updateSectionProgress({
      connection,

      submissionId:
        context.submissionId,

      sectionKey:
        "employment_details",

      sectionName:
        "Employment Details",

      sectionOrder:
        2,

      status:
        completionStatus
    });

    await connection.query(
      `
        UPDATE staff_submissions
        SET updated_at =
          CURRENT_TIMESTAMP
        WHERE id = ?
      `,
      [
        context.submissionId
      ]
    );

    await connection.commit();

    transactionStarted = false;

    const [savedRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            current_designation,
            grade,
            organisation_structure,
            date_first_appointment,
            date_last_promotion,
            current_duty_station,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_employment_details
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

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

      message:
        completionStatus ===
        "completed"
          ? "Employment details completed successfully."
          : "Employment details saved as a draft.",

      data: {
        employment_details:
          savedRows[0],

        next_section:
          completionStatus ===
          "completed"
            ? "next_of_kin"
            : "employment_details"
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Save employment details rollback error:",
          rollbackError
        );
      }
    }

    console.error(
      "Save employment details error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to save the employment details."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};


/* =========================================================
   GET NEXT OF KIN
   GET /api/public/form/next-of-kin
   ========================================================= */

const getNextOfKin = async (
  request,
  response
) => {
  try {
    const context =
      getSubmissionContext(request);

    const submission =
      await validateSubmissionSession(
        context
      );

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

    const [savedRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            full_name,
            relationship,
            residential_address,
            telephone_number,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_next_of_kin
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    if (savedRows.length > 0) {
      return response.status(200).json({
        success: true,

        data: {
          source:
            "submission_draft",

          editable:
            submission.editable,

          next_of_kin:
            savedRows[0]
        }
      });
    }

    const [officialRows] =
      await pool.query(
        `
          SELECT
            id,
            staff_id,
            full_name,
            relationship,
            residential_address,
            telephone_number
          FROM next_of_kin
          WHERE
            staff_id = ?
            AND is_primary = TRUE
          ORDER BY id ASC
          LIMIT 1
        `,
        [
          context.staffId
        ]
      );

    const officialRecord =
      officialRows[0];

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

        data: {
          source:
            "empty_record",

          editable:
            submission.editable,

          next_of_kin: {
            submission_id:
              context.submissionId,

            staff_id:
              context.staffId,

            full_name:
              "",

            relationship:
              "",

            residential_address:
              "",

            telephone_number:
              "",

            completion_status:
              "not_started",

            completed_at:
              null,

            updated_at:
              null
          }
        }
      });
    }

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

      data: {
        source:
          "official_next_of_kin_record",

        editable:
          submission.editable,

        next_of_kin: {
          ...officialRecord,

          submission_id:
            context.submissionId,

          completion_status:
            "not_started",

          completed_at:
            null,

          updated_at:
            null
        }
      }
    });
  } catch (error) {
    console.error(
      "Get next of kin error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to load the next-of-kin details."
    });
  }
};

/* =========================================================
   SAVE NEXT OF KIN
   PUT /api/public/form/next-of-kin
   ========================================================= */

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

  try {
    const context =
      getSubmissionContext(request);

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const submission =
      await validateSubmissionSession({
        ...context,
        connection
      });

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

      transactionStarted = false;

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

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

      transactionStarted = false;

      return response.status(409).json({
        success: false,
        message:
          "This submission can no longer be edited because it has already been submitted for review."
      });
    }

    const fullName =
      normalizeText(
        request.body?.full_name,
        150
      );

    const relationship =
      normalizeText(
        request.body?.relationship,
        100
      );

    const residentialAddress =
      normalizeText(
        request.body
          ?.residential_address,
        2000
      );

    const telephoneNumber =
      normalizePhone(
        request.body
          ?.telephone_number
      );

    const markCompleted =
      request.body
        ?.mark_completed === true;

    const validationErrors = {};

    if (
      telephoneNumber &&
      !isValidPhone(
        telephoneNumber
      )
    ) {
      validationErrors.telephone_number =
        "Enter a valid telephone number.";
    }

    if (markCompleted) {
      if (!fullName) {
        validationErrors.full_name =
          "Next-of-kin name is required.";
      }

      if (!relationship) {
        validationErrors.relationship =
          "Relationship is required.";
      }

      if (!residentialAddress) {
        validationErrors
          .residential_address =
          "Residential address is required.";
      }

      if (!telephoneNumber) {
        validationErrors
          .telephone_number =
          "Telephone number is required.";
      }
    }

    if (
      Object.keys(
        validationErrors
      ).length > 0
    ) {
      await connection.rollback();

      transactionStarted = false;

      return response.status(422).json({
        success: false,
        message:
          "Some next-of-kin details require correction.",

        errors:
          validationErrors
      });
    }

    const completionStatus =
      markCompleted
        ? "completed"
        : "in_progress";

    await connection.query(
      `
        INSERT INTO submission_next_of_kin (
          submission_id,
          staff_id,
          full_name,
          relationship,
          residential_address,
          telephone_number,
          completion_status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          CASE
            WHEN ? = 'completed'
            THEN NOW()
            ELSE NULL
          END
        )

        ON DUPLICATE KEY UPDATE
          full_name =
            VALUES(full_name),

          relationship =
            VALUES(relationship),

          residential_address =
            VALUES(residential_address),

          telephone_number =
            VALUES(telephone_number),

          completion_status =
            VALUES(completion_status),

          completed_at =
            CASE
              WHEN VALUES(
                completion_status
              ) = 'completed'
              THEN NOW()
              ELSE NULL
            END,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        context.staffId,
        fullName,
        relationship,
        residentialAddress,
        telephoneNumber,
        completionStatus,
        completionStatus
      ]
    );

    await updateSectionProgress({
      connection,

      submissionId:
        context.submissionId,

      sectionKey:
        "next_of_kin",

      sectionName:
        "Next of Kin",

      sectionOrder:
        3,

      status:
        completionStatus
    });

    await connection.query(
      `
        UPDATE staff_submissions
        SET updated_at =
          CURRENT_TIMESTAMP
        WHERE id = ?
      `,
      [
        context.submissionId
      ]
    );

    await connection.commit();

    transactionStarted = false;

    const [savedRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            full_name,
            relationship,
            residential_address,
            telephone_number,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_next_of_kin
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

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

      message:
        completionStatus ===
        "completed"
          ? "Next-of-kin details completed successfully."
          : "Next-of-kin details saved as a draft.",

      data: {
        next_of_kin:
          savedRows[0],

        next_section:
          completionStatus ===
          "completed"
            ? "dependents"
            : "next_of_kin"
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Save next of kin rollback error:",
          rollbackError
        );
      }
    }

    console.error(
      "Save next of kin error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to save the next-of-kin details."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

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

const normalizeBoolean = (value) => {
  return (
    value === true ||
    value === 1 ||
    value === "1" ||
    value === "true"
  );
};

const validateDateValue = (value) => {
  if (!value) {
    return null;
  }

  const normalizedValue =
    String(value).trim();

  if (
    !/^\d{4}-\d{2}-\d{2}$/.test(
      normalizedValue
    )
  ) {
    return null;
  }

  const date =
    new Date(
      `${normalizedValue}T00:00:00`
    );

  if (
    Number.isNaN(
      date.getTime()
    )
  ) {
    return null;
  }

  return normalizedValue;
};

const isDateAfterToday = (value) => {
  if (!value) {
    return false;
  }

  const selectedDate =
    new Date(
      `${value}T00:00:00`
    );

  const today =
    new Date();

  today.setHours(
    0,
    0,
    0,
    0
  );

  return selectedDate > today;
};

/* =========================================================
   GET DEPENDENTS
   GET /api/public/form/dependents
   ========================================================= */

const getDependents = async (
  request,
  response
) => {
  try {
    const context =
      getSubmissionContext(request);

    const submission =
      await validateSubmissionSession(
        context
      );

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

    const [summaryRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            has_dependents,
            number_of_dependents,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_dependents_summary
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    const [savedDependents] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            full_name,
            date_of_birth,
            relationship,
            gender,
            occupation_or_school,
            created_at,
            updated_at
          FROM submission_dependents
          WHERE submission_id = ?
          ORDER BY id ASC
        `,
        [
          context.submissionId
        ]
      );

    if (
      summaryRows.length > 0 ||
      savedDependents.length > 0
    ) {
      const summary =
        summaryRows[0] || {
          has_dependents:
            savedDependents.length > 0,

          number_of_dependents:
            savedDependents.length,

          completion_status:
            savedDependents.length > 0
              ? "in_progress"
              : "not_started",

          completed_at:
            null
        };

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

        data: {
          source:
            "submission_draft",

          editable:
            submission.editable,

          summary,

          dependents:
            savedDependents
        }
      });
    }

    const [officialDependents] =
      await pool.query(
        `
          SELECT
            id,
            staff_id,
            full_name,
            date_of_birth,
            relationship,
            gender,
            occupation_or_school
          FROM dependents
          WHERE staff_id = ?
          ORDER BY id ASC
        `,
        [
          context.staffId
        ]
      );

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

      data: {
        source:
          officialDependents.length > 0
            ? "official_dependents_record"
            : "empty_record",

        editable:
          submission.editable,

        summary: {
          submission_id:
            context.submissionId,

          staff_id:
            context.staffId,

          has_dependents:
            officialDependents.length > 0,

          number_of_dependents:
            officialDependents.length,

          completion_status:
            "not_started",

          completed_at:
            null
        },

        dependents:
          officialDependents
      }
    });
  } catch (error) {
    console.error(
      "Get dependents error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to load the dependents details."
    });
  }
};

/* =========================================================
   SAVE DEPENDENTS
   PUT /api/public/form/dependents
   ========================================================= */

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

  try {
    const context =
      getSubmissionContext(request);

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const submission =
      await validateSubmissionSession({
        ...context,
        connection
      });

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

      transactionStarted = false;

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

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

      transactionStarted = false;

      return response.status(409).json({
        success: false,
        message:
          "This submission can no longer be edited because it has already been submitted for review."
      });
    }

    const hasDependents =
      normalizeBoolean(
        request.body?.has_dependents
      );

    const markCompleted =
      request.body?.mark_completed ===
      true;

    const submittedDependents =
      Array.isArray(
        request.body?.dependents
      )
        ? request.body.dependents
        : [];

    const validationErrors = {};
    const cleanedDependents = [];

    if (
      submittedDependents.length > 20
    ) {
      validationErrors.dependents =
        "A maximum of 20 dependents may be entered.";
    }

    submittedDependents.forEach(
      (
        dependent,
        index
      ) => {
        const rowNumber =
          index + 1;

        const fullName =
          normalizeText(
            dependent?.full_name,
            150
          );

        const relationship =
          normalizeText(
            dependent?.relationship,
            100
          );

        const gender =
          normalizeText(
            dependent?.gender,
            20
          )?.toLowerCase() || null;

        const rawDateOfBirth =
          dependent?.date_of_birth;

        const dateOfBirth =
          validateDateValue(
            rawDateOfBirth
          );

        const occupationOrSchool =
          normalizeText(
            dependent
              ?.occupation_or_school,
            180
          );

        const rowErrors = {};

        if (!fullName) {
          rowErrors.full_name =
            "Full name is required.";
        }

        if (!relationship) {
          rowErrors.relationship =
            "Relationship is required.";
        }

        if (
          rawDateOfBirth &&
          !dateOfBirth
        ) {
          rowErrors.date_of_birth =
            "Enter a valid date of birth.";
        }

        if (
          dateOfBirth &&
          isDateAfterToday(
            dateOfBirth
          )
        ) {
          rowErrors.date_of_birth =
            "Date of birth cannot be in the future.";
        }

        if (
          gender &&
          ![
            "male",
            "female"
          ].includes(gender)
        ) {
          rowErrors.gender =
            "Select a valid gender.";
        }

        if (
          Object.keys(
            rowErrors
          ).length > 0
        ) {
          validationErrors[
            `dependent_${rowNumber}`
          ] = rowErrors;
        }

        cleanedDependents.push({
          full_name:
            fullName,

          relationship,

          date_of_birth:
            dateOfBirth,

          gender,

          occupation_or_school:
            occupationOrSchool
        });
      }
    );

    if (
      hasDependents &&
      markCompleted &&
      cleanedDependents.length === 0
    ) {
      validationErrors.dependents =
        "Add at least one dependent or select that you have no dependents.";
    }

    if (
      !hasDependents &&
      cleanedDependents.length > 0
    ) {
      validationErrors.dependents =
        "Remove the dependent entries or select that you have dependents.";
    }

    if (
      Object.keys(
        validationErrors
      ).length > 0
    ) {
      await connection.rollback();

      transactionStarted = false;

      return response.status(422).json({
        success: false,
        message:
          "Some dependent details require correction.",

        errors:
          validationErrors
      });
    }

    const completionStatus =
      markCompleted
        ? "completed"
        : "in_progress";

    /*
     * Replace the current dependent draft with
     * the latest submitted list.
     */

    await connection.query(
      `
        DELETE FROM submission_dependents
        WHERE submission_id = ?
      `,
      [
        context.submissionId
      ]
    );

    for (
      const dependent of
      cleanedDependents
    ) {
      await connection.query(
        `
          INSERT INTO submission_dependents (
            submission_id,
            staff_id,
            full_name,
            date_of_birth,
            relationship,
            gender,
            occupation_or_school
          )
          VALUES (
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?
          )
        `,
        [
          context.submissionId,
          context.staffId,
          dependent.full_name,
          dependent.date_of_birth,
          dependent.relationship,
          dependent.gender,
          dependent
            .occupation_or_school
        ]
      );
    }

    await connection.query(
      `
        INSERT INTO submission_dependents_summary (
          submission_id,
          staff_id,
          has_dependents,
          number_of_dependents,
          completion_status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          ?,
          CASE
            WHEN ? = 'completed'
            THEN NOW()
            ELSE NULL
          END
        )

        ON DUPLICATE KEY UPDATE
          has_dependents =
            VALUES(has_dependents),

          number_of_dependents =
            VALUES(
              number_of_dependents
            ),

          completion_status =
            VALUES(
              completion_status
            ),

          completed_at =
            CASE
              WHEN VALUES(
                completion_status
              ) = 'completed'
              THEN NOW()
              ELSE NULL
            END,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        context.staffId,
        hasDependents,
        cleanedDependents.length,
        completionStatus,
        completionStatus
      ]
    );

    await updateSectionProgress({
      connection,

      submissionId:
        context.submissionId,

      sectionKey:
        "dependents",

      sectionName:
        "Dependents",

      sectionOrder:
        4,

      status:
        completionStatus
    });

    await connection.query(
      `
        UPDATE staff_submissions
        SET updated_at =
          CURRENT_TIMESTAMP
        WHERE id = ?
      `,
      [
        context.submissionId
      ]
    );

    await connection.commit();

    transactionStarted = false;

    const [savedRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            full_name,
            date_of_birth,
            relationship,
            gender,
            occupation_or_school,
            created_at,
            updated_at
          FROM submission_dependents
          WHERE submission_id = ?
          ORDER BY id ASC
        `,
        [
          context.submissionId
        ]
      );

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

      message:
        completionStatus ===
        "completed"
          ? "Dependents section completed successfully."
          : "Dependents details saved as a draft.",

      data: {
        summary: {
          has_dependents:
            hasDependents,

          number_of_dependents:
            savedRows.length,

          completion_status:
            completionStatus
        },

        dependents:
          savedRows,

        next_section:
          completionStatus ===
          "completed"
            ? "emergency_contact"
            : "dependents"
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Save dependents rollback error:",
          rollbackError
        );
      }
    }

    console.error(
      "Save dependents error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to save the dependents details."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};


/* =========================================================
   GET EMERGENCY CONTACT
   GET /api/public/form/emergency-contact
   ========================================================= */

const getEmergencyContact = async (
  request,
  response
) => {
  try {
    const context =
      getSubmissionContext(request);

    const submission =
      await validateSubmissionSession(
        context
      );

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

    const [savedRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            full_name,
            relationship,
            residential_address,
            telephone_number,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_emergency_contact
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    if (savedRows.length > 0) {
      return response.status(200).json({
        success: true,

        data: {
          source:
            "submission_draft",

          editable:
            submission.editable,

          emergency_contact:
            savedRows[0]
        }
      });
    }

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

      data: {
        source:
          "empty_record",

        editable:
          submission.editable,

        emergency_contact: {
          submission_id:
            context.submissionId,

          staff_id:
            context.staffId,

          full_name:
            "",

          relationship:
            "",

          residential_address:
            "",

          telephone_number:
            "",

          completion_status:
            "not_started",

          completed_at:
            null,

          created_at:
            null,

          updated_at:
            null
        }
      }
    });
  } catch (error) {
    console.error(
      "Get emergency contact error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to load the emergency-contact details."
    });
  }
};

/* =========================================================
   SAVE EMERGENCY CONTACT
   PUT /api/public/form/emergency-contact
   ========================================================= */

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

  try {
    const context =
      getSubmissionContext(request);

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const submission =
      await validateSubmissionSession({
        ...context,
        connection
      });

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

      transactionStarted = false;

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

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

      transactionStarted = false;

      return response.status(409).json({
        success: false,
        message:
          "This submission can no longer be edited because it has already been submitted for review."
      });
    }

    const fullName =
      normalizeText(
        request.body?.full_name,
        150
      );

    const relationship =
      normalizeText(
        request.body?.relationship,
        100
      );

    const residentialAddress =
      normalizeText(
        request.body
          ?.residential_address,
        2000
      );

    const telephoneNumber =
      normalizePhone(
        request.body
          ?.telephone_number
      );

    const markCompleted =
      request.body
        ?.mark_completed === true;

    const validationErrors = {};

    if (
      telephoneNumber &&
      !isValidPhone(
        telephoneNumber
      )
    ) {
      validationErrors.telephone_number =
        "Enter a valid telephone number.";
    }

    if (markCompleted) {
      if (!fullName) {
        validationErrors.full_name =
          "Emergency-contact name is required.";
      }

      if (!relationship) {
        validationErrors.relationship =
          "Relationship is required.";
      }

      if (!residentialAddress) {
        validationErrors
          .residential_address =
          "Residential address is required.";
      }

      if (!telephoneNumber) {
        validationErrors
          .telephone_number =
          "Telephone number is required.";
      }
    }

    if (
      Object.keys(
        validationErrors
      ).length > 0
    ) {
      await connection.rollback();

      transactionStarted = false;

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

        message:
          "Some emergency-contact details require correction.",

        errors:
          validationErrors
      });
    }

    const completionStatus =
      markCompleted
        ? "completed"
        : "in_progress";

    await connection.query(
      `
        INSERT INTO submission_emergency_contact (
          submission_id,
          staff_id,
          full_name,
          relationship,
          residential_address,
          telephone_number,
          completion_status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          CASE
            WHEN ? = 'completed'
            THEN NOW()
            ELSE NULL
          END
        )

        ON DUPLICATE KEY UPDATE
          full_name =
            VALUES(full_name),

          relationship =
            VALUES(relationship),

          residential_address =
            VALUES(
              residential_address
            ),

          telephone_number =
            VALUES(
              telephone_number
            ),

          completion_status =
            VALUES(
              completion_status
            ),

          completed_at =
            CASE
              WHEN VALUES(
                completion_status
              ) = 'completed'
              THEN NOW()
              ELSE NULL
            END,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        context.staffId,
        fullName,
        relationship,
        residentialAddress,
        telephoneNumber,
        completionStatus,
        completionStatus
      ]
    );

    await updateSectionProgress({
      connection,

      submissionId:
        context.submissionId,

      sectionKey:
        "emergency_contact",

      sectionName:
        "Emergency Contact",

      sectionOrder:
        5,

      status:
        completionStatus
    });

    await connection.query(
      `
        UPDATE staff_submissions
        SET updated_at =
          CURRENT_TIMESTAMP
        WHERE id = ?
      `,
      [
        context.submissionId
      ]
    );

    await connection.commit();

    transactionStarted = false;

    const [savedRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            full_name,
            relationship,
            residential_address,
            telephone_number,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_emergency_contact
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

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

      message:
        completionStatus ===
        "completed"
          ? "Emergency-contact details completed successfully."
          : "Emergency-contact details saved as a draft.",

      data: {
        emergency_contact:
          savedRows[0],

        next_section:
          completionStatus ===
          "completed"
            ? "qualifications"
            : "emergency_contact"
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Save emergency contact rollback error:",
          rollbackError
        );
      }
    }

    console.error(
      "Save emergency contact error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to save the emergency-contact details."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};


/* =========================================================
   GET QUALIFICATIONS
   GET /api/public/form/qualifications
   ========================================================= */

const getQualifications = async (
  request,
  response
) => {
  try {
    const context =
      getSubmissionContext(request);

    const submission =
      await validateSubmissionSession(
        context
      );

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

    const [qualificationRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            highest_education_level,
            other_education_level,
            has_professional_certification,
            other_certification,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_qualifications
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    const [certificationRows] =
      await pool.query(
        `
          SELECT
            certification_code
          FROM submission_professional_certifications
          WHERE submission_id = ?
          ORDER BY id ASC
        `,
        [
          context.submissionId
        ]
      );

    if (qualificationRows.length > 0) {
      return response.status(200).json({
        success: true,

        data: {
          source:
            "submission_draft",

          editable:
            submission.editable,

          qualifications:
            qualificationRows[0],

          professional_certifications:
            certificationRows.map(
              (certification) =>
                certification
                  .certification_code
            )
        }
      });
    }

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

      data: {
        source:
          "empty_record",

        editable:
          submission.editable,

        qualifications: {
          submission_id:
            context.submissionId,

          staff_id:
            context.staffId,

          highest_education_level:
            null,

          other_education_level:
            "",

          has_professional_certification:
            null,

          other_certification:
            "",

          completion_status:
            "not_started",

          completed_at:
            null,

          created_at:
            null,

          updated_at:
            null
        },

        professional_certifications: []
      }
    });
  } catch (error) {
    console.error(
      "Get qualifications error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to load the academic and professional qualifications."
    });
  }
};

/* =========================================================
   SAVE QUALIFICATIONS
   PUT /api/public/form/qualifications
   ========================================================= */

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

  try {
    const context =
      getSubmissionContext(request);

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const submission =
      await validateSubmissionSession({
        ...context,
        connection
      });

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

      transactionStarted = false;

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

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

      transactionStarted = false;

      return response.status(409).json({
        success: false,
        message:
          "This submission can no longer be edited because it has already been submitted for review."
      });
    }

    const highestEducationLevel =
      normalizeText(
        request.body
          ?.highest_education_level,
        50
      )?.toLowerCase() || null;

    let otherEducationLevel =
      normalizeText(
        request.body
          ?.other_education_level,
        150
      );

    const rawCertificationStatus =
      request.body
        ?.has_professional_certification;

    const hasProfessionalCertification =
      normalizeNullableBoolean(
        rawCertificationStatus
      );

    let otherCertification =
      normalizeText(
        request.body
          ?.other_certification,
        180
      );

    const markCompleted =
      request.body
        ?.mark_completed === true;

    const submittedCertifications =
      Array.isArray(
        request.body
          ?.professional_certifications
      )
        ? request.body
            .professional_certifications
        : [];

    const normalizedCertifications =
      [
        ...new Set(
          submittedCertifications
            .map((certification) => {
              const normalized =
                normalizeText(
                  certification,
                  30
                );

              return normalized
                ? normalized.toUpperCase()
                : null;
            })
            .filter(Boolean)
        )
      ];

    const validationErrors = {};

    if (
      highestEducationLevel &&
      !allowedEducationLevels.has(
        highestEducationLevel
      )
    ) {
      validationErrors
        .highest_education_level =
        "Select a valid highest level of education.";
    }

    if (
      rawCertificationStatus !==
        undefined &&
      rawCertificationStatus !==
        null &&
      rawCertificationStatus !==
        "" &&
      hasProfessionalCertification ===
        null
    ) {
      validationErrors
        .has_professional_certification =
        "Select whether you have a professional certification.";
    }

    const invalidCertifications =
      normalizedCertifications.filter(
        (certification) =>
          !allowedCertificationCodes.has(
            certification
          )
      );

    if (
      invalidCertifications.length > 0
    ) {
      validationErrors
        .professional_certifications =
        "One or more selected professional certifications are invalid.";
    }

    /*
     * Clear conditional fields when they are
     * not applicable.
     */

    if (
      highestEducationLevel !==
      "other"
    ) {
      otherEducationLevel = null;
    }

    let cleanedCertifications =
      normalizedCertifications.filter(
        (certification) =>
          allowedCertificationCodes.has(
            certification
          )
      );

    if (
      hasProfessionalCertification ===
      false
    ) {
      cleanedCertifications = [];
      otherCertification = null;
    }

    if (
      !cleanedCertifications.includes(
        "OTHER"
      )
    ) {
      otherCertification = null;
    }

    if (markCompleted) {
      if (!highestEducationLevel) {
        validationErrors
          .highest_education_level =
          "Highest level of education is required.";
      }

      if (
        highestEducationLevel ===
          "other" &&
        !otherEducationLevel
      ) {
        validationErrors
          .other_education_level =
          "State your highest level of education.";
      }

      if (
        hasProfessionalCertification ===
        null
      ) {
        validationErrors
          .has_professional_certification =
          "Select whether you have a professional certification.";
      }

      if (
        hasProfessionalCertification ===
          true &&
        cleanedCertifications.length ===
          0
      ) {
        validationErrors
          .professional_certifications =
          "Select at least one professional certification.";
      }

      if (
        cleanedCertifications.includes(
          "OTHER"
        ) &&
        !otherCertification
      ) {
        validationErrors
          .other_certification =
          "Specify the other professional certification.";
      }
    }

    if (
      Object.keys(
        validationErrors
      ).length > 0
    ) {
      await connection.rollback();

      transactionStarted = false;

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

        message:
          "Some qualification details require correction.",

        errors:
          validationErrors
      });
    }

    const completionStatus =
      markCompleted
        ? "completed"
        : "in_progress";

    await connection.query(
      `
        INSERT INTO submission_qualifications (
          submission_id,
          staff_id,
          highest_education_level,
          other_education_level,
          has_professional_certification,
          other_certification,
          completion_status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          CASE
            WHEN ? = 'completed'
            THEN NOW()
            ELSE NULL
          END
        )

        ON DUPLICATE KEY UPDATE
          highest_education_level =
            VALUES(
              highest_education_level
            ),

          other_education_level =
            VALUES(
              other_education_level
            ),

          has_professional_certification =
            VALUES(
              has_professional_certification
            ),

          other_certification =
            VALUES(
              other_certification
            ),

          completion_status =
            VALUES(
              completion_status
            ),

          completed_at =
            CASE
              WHEN VALUES(
                completion_status
              ) = 'completed'
              THEN NOW()
              ELSE NULL
            END,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        context.staffId,
        highestEducationLevel,
        otherEducationLevel,
        hasProfessionalCertification,
        otherCertification,
        completionStatus,
        completionStatus
      ]
    );

    /*
     * Replace the previously selected certification
     * list with the latest submitted list.
     */

    await connection.query(
      `
        DELETE FROM
          submission_professional_certifications
        WHERE submission_id = ?
      `,
      [
        context.submissionId
      ]
    );

    for (
      const certificationCode of
      cleanedCertifications
    ) {
      await connection.query(
        `
          INSERT INTO
            submission_professional_certifications (
              submission_id,
              staff_id,
              certification_code
            )
          VALUES (
            ?,
            ?,
            ?
          )
        `,
        [
          context.submissionId,
          context.staffId,
          certificationCode
        ]
      );
    }

    await updateSectionProgress({
      connection,

      submissionId:
        context.submissionId,

      sectionKey:
        "qualifications",

      sectionName:
        "Academic and Professional Qualifications",

      sectionOrder:
        6,

      status:
        completionStatus
    });

    await connection.query(
      `
        UPDATE staff_submissions
        SET updated_at =
          CURRENT_TIMESTAMP
        WHERE id = ?
      `,
      [
        context.submissionId
      ]
    );

    await connection.commit();

    transactionStarted = false;

    const [savedQualificationRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            highest_education_level,
            other_education_level,
            has_professional_certification,
            other_certification,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_qualifications
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

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

      message:
        completionStatus ===
        "completed"
          ? "Qualification details completed successfully."
          : "Qualification details saved as a draft.",

      data: {
        qualifications:
          savedQualificationRows[0],

        professional_certifications:
          cleanedCertifications,

        next_section:
          completionStatus ===
          "completed"
            ? "documents"
            : "qualifications"
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Save qualifications rollback error:",
          rollbackError
        );
      }
    }

    console.error(
      "Save qualifications error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to save the academic and professional qualifications."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

/* =========================================================
   GET SUPPORTING DOCUMENTS
   GET /api/public/form/documents
   ========================================================= */

const getDocuments = async (
  request,
  response
) => {
  try {
    const context =
      getSubmissionContext(request);

    const submission =
      await validateSubmissionSession(
        context
      );

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

    const [summaryRows] =
      await pool.query(
        `
          SELECT
            id,
            submission_id,
            staff_id,
            has_new_documents,
            number_of_documents,
            completion_status,
            completed_at,
            created_at,
            updated_at
          FROM submission_documents_summary
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    const [documentRows] =
      await pool.query(
        `
          SELECT
            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
        `,
        [
          context.submissionId
        ]
      );

    const summary =
      summaryRows[0] || {
        submission_id:
          context.submissionId,

        staff_id:
          context.staffId,

        has_new_documents:
          null,

        number_of_documents:
          documentRows.length,

        completion_status:
          "not_started",

        completed_at:
          null,

        created_at:
          null,

        updated_at:
          null
      };

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

      data: {
        editable:
          submission.editable,

        summary,

        documents:
          documentRows.map(
            mapDocumentRecord
          )
      }
    });
  } catch (error) {
    console.error(
      "Get documents error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to load the supporting documents."
    });
  }
};

/* =========================================================
   UPLOAD SUPPORTING DOCUMENT
   POST /api/public/form/documents
   ========================================================= */

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

  const uploadedAbsolutePath =
    request.file?.path || null;

  try {
    const context =
      getSubmissionContext(request);

    if (!request.file) {
      return response.status(400).json({
        success: false,

        message:
          "Select a document to upload.",

        errors: {
          document:
            "A document is required."
        }
      });
    }

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const submission =
      await validateSubmissionSession({
        ...context,
        connection
      });

    if (!submission) {
      await connection.rollback();
      transactionStarted = false;

      await removeStoredFile(
        uploadedAbsolutePath
      );

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

    if (!submission.editable) {
      await connection.rollback();
      transactionStarted = false;

      await removeStoredFile(
        uploadedAbsolutePath
      );

      return response.status(409).json({
        success: false,
        message:
          "This submission can no longer be edited."
      });
    }

    const documentType =
      normalizeText(
        request.body
          ?.document_type,
        50
      )?.toLowerCase();

    const documentTitle =
      normalizeText(
        request.body
          ?.document_title,
        180
      );

    const validationErrors = {};

    if (
      !documentType ||
      !allowedDocumentTypes.has(
        documentType
      )
    ) {
      validationErrors.document_type =
        "Select a valid document type.";
    }

    if (
      documentType === "other" &&
      !documentTitle
    ) {
      validationErrors.document_title =
        "Enter a title for the other supporting document.";
    }

    if (
      Object.keys(
        validationErrors
      ).length > 0
    ) {
      await connection.rollback();
      transactionStarted = false;

      await removeStoredFile(
        uploadedAbsolutePath
      );

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

        message:
          "Some document details require correction.",

        errors:
          validationErrors
      });
    }

    const validSignature =
      await fileSignatureMatches(
        uploadedAbsolutePath,
        request.file.mimetype
      );

    if (!validSignature) {
      await connection.rollback();
      transactionStarted = false;

      await removeStoredFile(
        uploadedAbsolutePath
      );

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

        message:
          "The selected file content does not match its file type.",

        errors: {
          document:
            "Select a genuine PDF, JPG, JPEG or PNG file."
        }
      });
    }

    const relativeStoragePath =
      path
        .relative(
          privateUploadDirectory,
          uploadedAbsolutePath
        )
        .replace(/\\/g, "/");

    const [insertResult] =
      await connection.query(
        `
          INSERT INTO submission_documents (
            submission_id,
            staff_id,
            document_type,
            document_title,
            original_file_name,
            stored_file_name,
            storage_path,
            mime_type,
            file_size_bytes
          )
          VALUES (
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?,
            ?
          )
        `,
        [
          context.submissionId,
          context.staffId,
          documentType,
          documentTitle,
          request.file.originalname,
          request.file.filename,
          relativeStoragePath,
          request.file.mimetype,
          request.file.size
        ]
      );

    const [countRows] =
      await connection.query(
        `
          SELECT
            COUNT(*) AS total
          FROM submission_documents
          WHERE submission_id = ?
        `,
        [
          context.submissionId
        ]
      );

    const documentCount =
      Number(
        countRows[0]?.total
      ) || 0;

    await connection.query(
      `
        INSERT INTO submission_documents_summary (
          submission_id,
          staff_id,
          has_new_documents,
          number_of_documents,
          completion_status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          1,
          ?,
          'in_progress',
          NULL
        )

        ON DUPLICATE KEY UPDATE
          has_new_documents = 1,

          number_of_documents =
            VALUES(
              number_of_documents
            ),

          completion_status =
            'in_progress',

          completed_at = NULL,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        context.staffId,
        documentCount
      ]
    );

    await updateSectionProgress({
      connection,

      submissionId:
        context.submissionId,

      sectionKey:
        "documents",

      sectionName:
        "Supporting Documents",

      sectionOrder:
        7,

      status:
        "in_progress"
    });

    await connection.commit();

    transactionStarted = false;

    const [documentRows] =
      await pool.query(
        `
          SELECT
            id,
            document_type,
            document_title,
            original_file_name,
            mime_type,
            file_size_bytes,
            created_at,
            updated_at
          FROM submission_documents
          WHERE id = ?
          LIMIT 1
        `,
        [
          insertResult.insertId
        ]
      );

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

      message:
        "Document uploaded successfully.",

      data: {
        document:
          mapDocumentRecord(
            documentRows[0]
          ),

        summary: {
          has_new_documents:
            true,

          number_of_documents:
            documentCount,

          completion_status:
            "in_progress"
        }
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Document upload rollback error:",
          rollbackError
        );
      }
    }

    await removeStoredFile(
      uploadedAbsolutePath
    );

    console.error(
      "Upload document error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to upload the supporting document."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

/* =========================================================
   DELETE SUPPORTING DOCUMENT
   DELETE /api/public/form/documents/:documentId
   ========================================================= */

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

  try {
    const context =
      getSubmissionContext(request);

    const documentId =
      Number(
        request.params
          ?.documentId
      );

    if (
      !Number.isInteger(
        documentId
      ) ||
      documentId < 1
    ) {
      return response.status(400).json({
        success: false,
        message:
          "A valid document ID is required."
      });
    }

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const submission =
      await validateSubmissionSession({
        ...context,
        connection
      });

    if (!submission) {
      await connection.rollback();
      transactionStarted = false;

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

    if (!submission.editable) {
      await connection.rollback();
      transactionStarted = false;

      return response.status(409).json({
        success: false,
        message:
          "This submission can no longer be edited."
      });
    }

    const [documentRows] =
      await connection.query(
        `
          SELECT
            id,
            storage_path
          FROM submission_documents
          WHERE
            id = ?
            AND submission_id = ?
          LIMIT 1
          FOR UPDATE
        `,
        [
          documentId,
          context.submissionId
        ]
      );

    const document =
      documentRows[0];

    if (!document) {
      await connection.rollback();
      transactionStarted = false;

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

    absoluteFilePath =
      resolvePrivateFile(
        document.storage_path
      );

    await connection.query(
      `
        DELETE FROM submission_documents
        WHERE
          id = ?
          AND submission_id = ?
      `,
      [
        documentId,
        context.submissionId
      ]
    );

    const [countRows] =
      await connection.query(
        `
          SELECT
            COUNT(*) AS total
          FROM submission_documents
          WHERE submission_id = ?
        `,
        [
          context.submissionId
        ]
      );

    const remainingCount =
      Number(
        countRows[0]?.total
      ) || 0;

    await connection.query(
      `
        INSERT INTO submission_documents_summary (
          submission_id,
          staff_id,
          has_new_documents,
          number_of_documents,
          completion_status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          'in_progress',
          NULL
        )

        ON DUPLICATE KEY UPDATE
          number_of_documents =
            VALUES(
              number_of_documents
            ),

          completion_status =
            'in_progress',

          completed_at = NULL,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        context.staffId,
        remainingCount > 0
          ? 1
          : null,
        remainingCount
      ]
    );

    await updateSectionProgress({
      connection,

      submissionId:
        context.submissionId,

      sectionKey:
        "documents",

      sectionName:
        "Supporting Documents",

      sectionOrder:
        7,

      status:
        "in_progress"
    });

    await connection.commit();

    transactionStarted = false;

    await removeStoredFile(
      absoluteFilePath
    );

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

      message:
        "Document removed successfully.",

      data: {
        number_of_documents:
          remainingCount
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Delete document rollback error:",
          rollbackError
        );
      }
    }

    console.error(
      "Delete document error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to remove the supporting document."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

/* =========================================================
   DOWNLOAD SUPPORTING DOCUMENT
   GET /api/public/form/documents/:documentId/download
   ========================================================= */

const downloadDocument = async (
  request,
  response
) => {
  try {
    const context =
      getSubmissionContext(request);

    const submission =
      await validateSubmissionSession(
        context
      );

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

    const documentId =
      Number(
        request.params
          ?.documentId
      );

    if (
      !Number.isInteger(
        documentId
      ) ||
      documentId < 1
    ) {
      return response.status(400).json({
        success: false,
        message:
          "A valid document ID is required."
      });
    }

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

    const document =
      documentRows[0];

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

    const absoluteFilePath =
      resolvePrivateFile(
        document.storage_path
      );

    try {
      await fs.access(
        absoluteFilePath
      );
    } catch (error) {
      return response.status(404).json({
        success: false,
        message:
          "The stored document file could not be found."
      });
    }

    response.type(
      document.mime_type
    );

    return response.download(
      absoluteFilePath,
      document.original_file_name
    );
  } catch (error) {
    console.error(
      "Download document error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to download the supporting document."
    });
  }
};

/* =========================================================
   SAVE DOCUMENT SECTION
   PUT /api/public/form/documents/section
   ========================================================= */

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

    try {
      const context =
        getSubmissionContext(request);

      connection =
        await pool.getConnection();

      await connection.beginTransaction();

      transactionStarted = true;

      const submission =
        await validateSubmissionSession({
          ...context,
          connection
        });

      if (!submission) {
        await connection.rollback();
        transactionStarted = false;

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

      if (!submission.editable) {
        await connection.rollback();
        transactionStarted = false;

        return response.status(409).json({
          success: false,
          message:
            "This submission can no longer be edited."
        });
      }

      const hasNewDocuments =
        normalizeNullableBoolean(
          request.body
            ?.has_new_documents
        );

      const markCompleted =
        request.body
          ?.mark_completed === true;

      const [countRows] =
        await connection.query(
          `
            SELECT
              COUNT(*) AS total
            FROM submission_documents
            WHERE submission_id = ?
          `,
          [
            context.submissionId
          ]
        );

      const documentCount =
        Number(
          countRows[0]?.total
        ) || 0;

      const validationErrors = {};

      if (
        markCompleted &&
        hasNewDocuments === null
      ) {
        validationErrors
          .has_new_documents =
          "Select whether you have new supporting documents.";
      }

      if (
        markCompleted &&
        hasNewDocuments === true &&
        documentCount === 0
      ) {
        validationErrors.documents =
          "Upload at least one document or select that you have no new documents.";
      }

      if (
        hasNewDocuments === false &&
        documentCount > 0
      ) {
        validationErrors.documents =
          "Remove the uploaded documents or select that you have new supporting documents.";
      }

      if (
        Object.keys(
          validationErrors
        ).length > 0
      ) {
        await connection.rollback();
        transactionStarted = false;

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

          message:
            "Some supporting-document details require correction.",

          errors:
            validationErrors
        });
      }

      const completionStatus =
        markCompleted
          ? "completed"
          : "in_progress";

      await connection.query(
        `
          INSERT INTO submission_documents_summary (
            submission_id,
            staff_id,
            has_new_documents,
            number_of_documents,
            completion_status,
            completed_at
          )
          VALUES (
            ?,
            ?,
            ?,
            ?,
            ?,
            CASE
              WHEN ? = 'completed'
              THEN NOW()
              ELSE NULL
            END
          )

          ON DUPLICATE KEY UPDATE
            has_new_documents =
              VALUES(
                has_new_documents
              ),

            number_of_documents =
              VALUES(
                number_of_documents
              ),

            completion_status =
              VALUES(
                completion_status
              ),

            completed_at =
              CASE
                WHEN VALUES(
                  completion_status
                ) = 'completed'
                THEN NOW()
                ELSE NULL
              END,

            updated_at =
              CURRENT_TIMESTAMP
        `,
        [
          context.submissionId,
          context.staffId,
          hasNewDocuments,
          documentCount,
          completionStatus,
          completionStatus
        ]
      );

      await updateSectionProgress({
        connection,

        submissionId:
          context.submissionId,

        sectionKey:
          "documents",

        sectionName:
          "Supporting Documents",

        sectionOrder:
          7,

        status:
          completionStatus
      });

      await connection.query(
        `
          UPDATE staff_submissions
          SET updated_at =
            CURRENT_TIMESTAMP
          WHERE id = ?
        `,
        [
          context.submissionId
        ]
      );

      await connection.commit();

      transactionStarted = false;

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

        message:
          completionStatus ===
          "completed"
            ? "Supporting documents section completed successfully."
            : "Supporting documents section saved as a draft.",

        data: {
          summary: {
            has_new_documents:
              hasNewDocuments,

            number_of_documents:
              documentCount,

            completion_status:
              completionStatus
          },

          next_section:
            completionStatus ===
            "completed"
              ? "declaration"
              : "documents"
        }
      });
    } catch (error) {
      if (
        connection &&
        transactionStarted
      ) {
        try {
          await connection.rollback();
        } catch (rollbackError) {
          console.error(
            "Save document section rollback error:",
            rollbackError
          );
        }
      }

      console.error(
        "Save document section error:",
        error
      );

      return response.status(500).json({
        success: false,
        message:
          "Unable to save the supporting-documents section."
      });
    } finally {
      if (connection) {
        connection.release();
      }
    }
  };


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

const requiredFinalSections = [
  {
    key: "personal_details",
    label: "Personal Details"
  },
  {
    key: "employment_details",
    label: "Employment Details"
  },
  {
    key: "next_of_kin",
    label: "Next of Kin"
  },
  {
    key: "dependents",
    label: "Dependents"
  },
  {
    key: "emergency_contact",
    label: "Emergency Contact"
  },
  {
    key: "qualifications",
    label:
      "Academic and Professional Qualifications"
  },
  {
    key: "documents",
    label: "Supporting Documents"
  }
];

const normalizeComparableName = (
  value
) => {
  return String(value || "")
    .trim()
    .toUpperCase()
    .replace(/[^A-Z0-9]/g, "");
};

/* =========================================================
   GET DECLARATION
   GET /api/public/form/declaration
   ========================================================= */

const getDeclaration = async (
  request,
  response
) => {
  try {
    const context =
      getSubmissionContext(request);

    const submission =
      await validateSubmissionSession(
        context
      );

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

    const [staffRows] =
      await pool.query(
        `
          SELECT
            oldr.full_name,
            oldr.employee_number,
            ss.submission_reference,
            ss.submission_status
          FROM staff_submissions ss

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

          WHERE ss.id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    const staffRecord =
      staffRows[0];

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

    const [personalRows] =
      await pool.query(
        `
          SELECT
            surname,
            first_name,
            middle_name
          FROM submission_personal_details
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    const personalDetails =
      personalRows[0];

    const currentFullName =
      personalDetails
        ? [
            personalDetails.first_name,
            personalDetails.middle_name,
            personalDetails.surname
          ]
            .filter(Boolean)
            .join(" ")
        : staffRecord.full_name;

    const [declarationRows] =
      await pool.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
        `,
        [
          context.submissionId
        ]
      );

    const declaration =
      declarationRows[0] || {
        submission_id:
          context.submissionId,

        staff_id:
          context.staffId,

        declaration_accepted:
          null,

        declared_full_name:
          "",

        declaration_date:
          null,

        completion_status:
          "not_started",

        completed_at:
          null,

        created_at:
          null,

        updated_at:
          null
      };

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

      data: {
        editable:
          submission.editable,

        staff: {
          staff_number:
            staffRecord.employee_number,

          full_name:
            currentFullName
        },

        submission: {
          submission_reference:
            staffRecord
              .submission_reference,

          submission_status:
            staffRecord
              .submission_status
        },

        declaration
      }
    });
  } catch (error) {
    console.error(
      "Get declaration error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to load the declaration section."
    });
  }
};

/* =========================================================
   SAVE DECLARATION DRAFT
   PUT /api/public/form/declaration
   ========================================================= */

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

  try {
    const context =
      getSubmissionContext(request);

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const submission =
      await validateSubmissionSession({
        ...context,
        connection
      });

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

      transactionStarted = false;

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

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

      transactionStarted = false;

      return response.status(409).json({
        success: false,
        message:
          "This submission can no longer be edited."
      });
    }

    const declarationAccepted =
      normalizeNullableBoolean(
        request.body
          ?.declaration_accepted
      );

    const declaredFullName =
      normalizeText(
        request.body
          ?.declared_full_name,
        150
      );

    await connection.query(
      `
        INSERT INTO submission_declaration (
          submission_id,
          staff_id,
          declaration_accepted,
          declared_full_name,
          declaration_date,
          declaration_ip_address,
          declaration_user_agent,
          completion_status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          NULL,
          ?,
          ?,
          'in_progress',
          NULL
        )

        ON DUPLICATE KEY UPDATE
          declaration_accepted =
            VALUES(
              declaration_accepted
            ),

          declared_full_name =
            VALUES(
              declared_full_name
            ),

          declaration_date =
            NULL,

          declaration_ip_address =
            VALUES(
              declaration_ip_address
            ),

          declaration_user_agent =
            VALUES(
              declaration_user_agent
            ),

          completion_status =
            'in_progress',

          completed_at =
            NULL,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        context.staffId,
        declarationAccepted,
        declaredFullName,
        request.ip || null,
        request.get(
          "user-agent"
        ) || null
      ]
    );

    await updateSectionProgress({
      connection,

      submissionId:
        context.submissionId,

      sectionKey:
        "declaration",

      sectionName:
        "Declaration",

      sectionOrder:
        8,

      status:
        "in_progress"
    });

    await connection.query(
      `
        UPDATE staff_submissions
        SET updated_at =
          CURRENT_TIMESTAMP
        WHERE id = ?
      `,
      [
        context.submissionId
      ]
    );

    await connection.commit();

    transactionStarted = false;

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

      message:
        "Declaration saved as a draft.",

      data: {
        declaration: {
          declaration_accepted:
            declarationAccepted,

          declared_full_name:
            declaredFullName,

          completion_status:
            "in_progress"
        }
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Declaration draft rollback error:",
          rollbackError
        );
      }
    }

    console.error(
      "Save declaration draft error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to save the declaration draft."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

/* =========================================================
   FINAL SUBMISSION
   POST /api/public/form/declaration/submit
   ========================================================= */

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

  try {
    const context =
      getSubmissionContext(request);

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const submission =
      await validateSubmissionSession({
        ...context,
        connection
      });

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

      transactionStarted = false;

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

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

      transactionStarted = false;

      return response.status(409).json({
        success: false,
        message:
          "This submission has already been submitted and can no longer be edited."
      });
    }

    const declarationAccepted =
      normalizeNullableBoolean(
        request.body
          ?.declaration_accepted
      );

    const declaredFullName =
      normalizeText(
        request.body
          ?.declared_full_name,
        150
      );

    const validationErrors = {};

    if (
      declarationAccepted !== true
    ) {
      validationErrors
        .declaration_accepted =
        "You must accept the declaration before submitting.";
    }

    if (!declaredFullName) {
      validationErrors
        .declared_full_name =
        "Enter your full name as your electronic declaration.";
    }

    const [progressRows] =
      await connection.query(
        `
          SELECT
            section_key,
            status
          FROM submission_section_progress
          WHERE submission_id = ?
        `,
        [
          context.submissionId
        ]
      );

    const progressStatus =
      new Map(
        progressRows.map(
          (section) => [
            section.section_key,
            section.status
          ]
        )
      );

    const incompleteSections =
      requiredFinalSections.filter(
        (section) =>
          progressStatus.get(
            section.key
          ) !== "completed"
      );

    if (
      incompleteSections.length > 0
    ) {
      validationErrors.sections =
        incompleteSections.map(
          (section) =>
            section.label
        );
    }

    const [submissionRows] =
      await connection.query(
        `
          SELECT
            ss.id,
            ss.old_record_id,
            ss.submission_reference,
            ss.submission_status,

            s.staff_number,

            oldr.full_name AS old_full_name,
            oldr.nia_number,
            oldr.date_of_birth

          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

          WHERE ss.id = ?
          LIMIT 1
          FOR UPDATE
        `,
        [
          context.submissionId
        ]
      );

    const submissionRecord =
      submissionRows[0];

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

      transactionStarted = false;

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

    const [personalRows] =
      await connection.query(
        `
          SELECT
            spd.surname,
            spd.first_name,
            spd.middle_name,
            spd.gender,
            spd.marital_status,
            spd.nationality,
            spd.ghana_card_number,
            spd.ssnit_number,
            spd.home_town,
            r.name AS home_region,
            spd.residential_address,
            spd.gps_address,
            spd.primary_mobile,
            spd.secondary_mobile,
            spd.official_email,
            spd.personal_email

          FROM submission_personal_details spd

          LEFT JOIN regions r
            ON r.id =
              spd.home_region_id

          WHERE spd.submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    const personalDetails =
      personalRows[0];

    if (!personalDetails) {
      validationErrors.sections = [
        ...(
          validationErrors.sections ||
          []
        ),
        "Personal Details"
      ];
    }

    const submittedFullName =
      personalDetails
        ? [
            personalDetails.first_name,
            personalDetails.middle_name,
            personalDetails.surname
          ]
            .filter(Boolean)
            .join(" ")
        : submissionRecord.old_full_name;

    if (
      declaredFullName &&
      normalizeComparableName(
        declaredFullName
      ) !==
        normalizeComparableName(
          submittedFullName
        )
    ) {
      validationErrors
        .declared_full_name =
        `Enter your full name exactly as shown: ${submittedFullName}`;
    }

    if (
      Object.keys(
        validationErrors
      ).length > 0
    ) {
      await connection.rollback();

      transactionStarted = false;

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

        message:
          incompleteSections.length > 0
            ? "Complete all previous sections before submitting the form."
            : "Some declaration details require correction.",

        errors:
          validationErrors
      });
    }

    const [employmentRows] =
      await connection.query(
        `
          SELECT
            current_designation,
            grade,
            organisation_structure,
            date_first_appointment,
            date_last_promotion,
            current_duty_station
          FROM submission_employment_details
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    const employmentDetails =
      employmentRows[0];

    const [qualificationRows] =
      await connection.query(
        `
          SELECT
            highest_education_level,
            other_education_level,
            has_professional_certification,
            other_certification
          FROM submission_qualifications
          WHERE submission_id = ?
          LIMIT 1
        `,
        [
          context.submissionId
        ]
      );

    const qualificationDetails =
      qualificationRows[0] || {};

    const [certificationRows] =
      await connection.query(
        `
          SELECT
            certification_code
          FROM submission_professional_certifications
          WHERE submission_id = ?
          ORDER BY id ASC
        `,
        [
          context.submissionId
        ]
      );

    const professionalCertifications =
      certificationRows
        .map(
          (certification) =>
            certification
              .certification_code
        )
        .join(", ");

    await connection.query(
      `
        INSERT INTO submission_declaration (
          submission_id,
          staff_id,
          declaration_accepted,
          declared_full_name,
          declaration_date,
          declaration_ip_address,
          declaration_user_agent,
          completion_status,
          completed_at
        )
        VALUES (
          ?,
          ?,
          1,
          ?,
          CURDATE(),
          ?,
          ?,
          'completed',
          NOW()
        )

        ON DUPLICATE KEY UPDATE
          declaration_accepted =
            1,

          declared_full_name =
            VALUES(
              declared_full_name
            ),

          declaration_date =
            CURDATE(),

          declaration_ip_address =
            VALUES(
              declaration_ip_address
            ),

          declaration_user_agent =
            VALUES(
              declaration_user_agent
            ),

          completion_status =
            'completed',

          completed_at =
            NOW(),

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        context.staffId,
        declaredFullName,
        request.ip || null,
        request.get(
          "user-agent"
        ) || null
      ]
    );

    await updateSectionProgress({
      connection,

      submissionId:
        context.submissionId,

      sectionKey:
        "declaration",

      sectionName:
        "Declaration",

      sectionOrder:
        8,

      status:
        "completed"
    });

    await connection.query(
      `
        INSERT INTO new_record (
          submission_id,
          old_record_id,
          staff_id,
          ghana_card_number,
          full_name,
          gender,
          date_of_birth,

          current_designation,
          grade,
          organisation_structure,
          date_first_appointment,
          date_last_promotion,
          current_duty_station,

          nationality,
          marital_status,
          ssnit_number,
          home_town,
          home_region,
          residential_address,
          gps_address,
          primary_mobile,
          secondary_mobile,
          official_email,
          personal_email,

          highest_education_level,
          other_education_level,
          has_professional_certification,
          professional_certifications,
          other_certification,

          declaration_accepted,
          declaration_name,
          declaration_date,

          record_status,
          submitted_at
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,

          ?,
          ?,
          ?,
          ?,
          ?,
          ?,

          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,

          ?,
          ?,
          ?,
          ?,
          ?,

          1,
          ?,
          CURDATE(),

          'submitted',
          NOW()
        )

        ON DUPLICATE KEY UPDATE
          submission_id =
            VALUES(
              submission_id
            ),

          staff_id =
            VALUES(staff_id),

          ghana_card_number =
            VALUES(
              ghana_card_number
            ),

          full_name =
            VALUES(full_name),

          gender =
            VALUES(gender),

          date_of_birth =
            VALUES(
              date_of_birth
            ),

          current_designation =
            VALUES(
              current_designation
            ),

          grade =
            VALUES(grade),

          organisation_structure =
            VALUES(
              organisation_structure
            ),

          date_first_appointment =
            VALUES(
              date_first_appointment
            ),

          date_last_promotion =
            VALUES(
              date_last_promotion
            ),

          current_duty_station =
            VALUES(
              current_duty_station
            ),

          nationality =
            VALUES(nationality),

          marital_status =
            VALUES(
              marital_status
            ),

          ssnit_number =
            VALUES(
              ssnit_number
            ),

          home_town =
            VALUES(home_town),

          home_region =
            VALUES(home_region),

          residential_address =
            VALUES(
              residential_address
            ),

          gps_address =
            VALUES(gps_address),

          primary_mobile =
            VALUES(
              primary_mobile
            ),

          secondary_mobile =
            VALUES(
              secondary_mobile
            ),

          official_email =
            VALUES(
              official_email
            ),

          personal_email =
            VALUES(
              personal_email
            ),

          highest_education_level =
            VALUES(
              highest_education_level
            ),

          other_education_level =
            VALUES(
              other_education_level
            ),

          has_professional_certification =
            VALUES(
              has_professional_certification
            ),

          professional_certifications =
            VALUES(
              professional_certifications
            ),

          other_certification =
            VALUES(
              other_certification
            ),

          declaration_accepted =
            1,

          declaration_name =
            VALUES(
              declaration_name
            ),

          declaration_date =
            CURDATE(),

          record_status =
            'submitted',

          submitted_at =
            NOW(),

          approved_at =
            NULL,

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        context.submissionId,
        submissionRecord.old_record_id,
        submissionRecord.staff_number,

        personalDetails
          .ghana_card_number ||
          submissionRecord.nia_number,

        submittedFullName,
        personalDetails.gender,
        submissionRecord.date_of_birth,

        employmentDetails
          .current_designation,

        employmentDetails.grade,

        employmentDetails
          .organisation_structure,

        employmentDetails
          .date_first_appointment,

        employmentDetails
          .date_last_promotion,

        employmentDetails
          .current_duty_station,

        personalDetails.nationality,
        personalDetails.marital_status,
        personalDetails.ssnit_number,
        personalDetails.home_town,
        personalDetails.home_region,
        personalDetails
          .residential_address,
        personalDetails.gps_address,
        personalDetails.primary_mobile,
        personalDetails.secondary_mobile,
        personalDetails.official_email,
        personalDetails.personal_email,

        qualificationDetails
          .highest_education_level ||
          null,

        qualificationDetails
          .other_education_level ||
          null,

        qualificationDetails
          .has_professional_certification,

        professionalCertifications ||
          null,

        qualificationDetails
          .other_certification ||
          null,

        declaredFullName
      ]
    );

    await connection.query(
      `
        UPDATE staff_submissions
        SET
          submission_status =
            'submitted',

          submitted_at =
            NOW(),

          updated_at =
            CURRENT_TIMESTAMP

        WHERE id = ?
      `,
      [
        context.submissionId
      ]
    );

    await connection.commit();

    transactionStarted = false;

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

      message:
        "Your staff records update has been submitted successfully.",

      data: {
        submission: {
          submission_reference:
            submissionRecord
              .submission_reference,

          submission_status:
            "submitted",

          submitted_at:
            new Date().toISOString()
        },

        staff: {
          staff_number:
            submissionRecord
              .staff_number,

          full_name:
            submittedFullName
        }
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Final submission rollback error:",
          rollbackError
        );
      }
    }

    console.error(
      "Final submission error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to submit the staff records update."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

module.exports = {
  getFormLookups,

  getPersonalDetails,
  savePersonalDetails,

  getEmploymentDetails,
  saveEmploymentDetails,

  getNextOfKin,
  saveNextOfKin,

  getDependents,
  saveDependents,

  getEmergencyContact,
  saveEmergencyContact,

  getQualifications,
  saveQualifications,

  getDocuments,
  uploadDocument,
  deleteDocument,
  downloadDocument,
  saveDocumentsSection,

  getDeclaration,
  saveDeclarationDraft,
  submitFinalDeclaration,

  getFormProgress
};