const jwt = require("jsonwebtoken");

require("dotenv").config();

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

const {
  createSubmissionReference
} = require(
  "../utils/submissionReference"
);

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

const normalizeText = (value) => {
  if (typeof value !== "string") {
    return "";
  }

  return value.trim();
};

const normalizeStaffId = (value) => {
  return normalizeText(value)
    .replace(/\D/g, "");
};

const normalizeGhanaCard = (value) => {
  return normalizeText(value)
    .toUpperCase()
    .replace(/[\s-]/g, "");
};

const formatGhanaCard = (
  normalizedValue
) => {
  if (
    !/^GHA\d{10}$/.test(
      normalizedValue
    )
  ) {
    return normalizedValue;
  }

  return [
    normalizedValue.slice(0, 3),
    normalizedValue.slice(3, 12),
    normalizedValue.slice(12)
  ].join("-");
};

const isValidGhanaCard = (value) => {
  return /^GHA\d{10}$/.test(value);
};

const maskName = (fullName) => {
  const names =
    normalizeText(fullName)
      .split(/\s+/)
      .filter(Boolean);

  return names
    .map((name) => {
      if (name.length <= 2) {
        return `${name[0] || ""}*`;
      }

      return `${name[0]}${"*".repeat(
        Math.min(name.length - 1, 5)
      )}`;
    })
    .join(" ");
};

const getActiveCampaign = async (
  connection = pool
) => {
  const [rows] =
    await connection.query(
      `
        SELECT
          id,
          campaign_name,
          campaign_year,
          description,
          start_date,
          end_date,
          status,
          allow_multiple_submissions
        FROM record_update_campaigns
        WHERE
          status = 'active'
          AND start_date <= NOW()
          AND end_date >= NOW()
        ORDER BY start_date DESC
        LIMIT 1
      `
    );

  return rows[0] || null;
};

const buildLegacyNameParts = (
  fullName
) => {
  const parts =
    normalizeText(fullName)
      .split(/\s+/)
      .filter(Boolean);

  if (parts.length === 0) {
    return {
      firstName: "Unknown",
      middleName: null,
      surname: "Unknown"
    };
  }

  if (parts.length === 1) {
    return {
      firstName: parts[0],
      middleName: null,
      surname: parts[0]
    };
  }

  return {
    firstName: parts[0],

    middleName:
      parts.length > 2
        ? parts
            .slice(1, -1)
            .join(" ")
        : null,

    surname:
      parts[parts.length - 1]
  };
};

const createGhanaCardToken = ({
  oldRecordId,
  campaignId,
  normalizedGhanaCard
}) => {
  return jwt.sign(
    {
      token_type:
        "ghana_card_access",

      old_record_id:
        oldRecordId,

      campaign_id:
        campaignId,

      ghana_card_number:
        normalizedGhanaCard
    },
    process.env.PORTAL_JWT_SECRET,
    {
      expiresIn:
        process.env
          .PORTAL_ACCESS_TOKEN_EXPIRES_IN ||
        "30m"
    }
  );
};

const createSubmissionToken = ({
  submissionId,
  staffId,
  oldRecordId,
  campaignId,
  submissionReference
}) => {
  return jwt.sign(
    {
      token_type:
        "staff_submission",

      submission_id:
        submissionId,

      staff_id:
        staffId,

      old_record_id:
        oldRecordId,

      campaign_id:
        campaignId,

      submission_reference:
        submissionReference
    },
    process.env.PORTAL_JWT_SECRET,
    {
      expiresIn:
        process.env
          .PORTAL_SUBMISSION_TOKEN_EXPIRES_IN ||
        "2h"
    }
  );
};

/* =========================================================
   GET ACTIVE CAMPAIGN
   GET /api/public/campaign
   ========================================================= */

const getCurrentCampaign = async (
  request,
  response
) => {
  try {
    const campaign =
      await getActiveCampaign();

    if (!campaign) {
      return response.status(404).json({
        success: false,
        message:
          "There is currently no active staff records update exercise."
      });
    }

    return response.status(200).json({
      success: true,
      data: {
        campaign
      }
    });
  } catch (error) {
    console.error(
      "Get campaign error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to retrieve the current staff records update exercise."
    });
  }
};

/* =========================================================
   VALIDATE GHANA CARD
   POST /api/public/validate-ghana-card
   ========================================================= */

const validateGhanaCard = async (
  request,
  response
) => {
  try {
    const normalizedGhanaCard =
      normalizeGhanaCard(
        request.body
          ?.ghana_card_number
      );

    if (!normalizedGhanaCard) {
      return response.status(400).json({
        success: false,
        message:
          "Ghana Card number is required."
      });
    }

    if (
      !isValidGhanaCard(
        normalizedGhanaCard
      )
    ) {
      return response.status(400).json({
        success: false,
        message:
          "Enter a valid Ghana Card number, for example GHA-123456789-0."
      });
    }

    const campaign =
      await getActiveCampaign();

    if (!campaign) {
      return response.status(403).json({
        success: false,
        message:
          "The staff records update exercise is currently closed."
      });
    }

    const [rows] =
      await pool.query(
        `
          SELECT
            id,
            employee_number,
            full_name,
            nia_number,
            assignment_status
          FROM old_record
          WHERE
            REPLACE(
              REPLACE(
                UPPER(nia_number),
                '-',
                ''
              ),
              ' ',
              ''
            ) = ?
          LIMIT 1
        `,
        [normalizedGhanaCard]
      );

    const oldRecord =
      rows[0];

    if (!oldRecord) {
      return response.status(404).json({
        success: false,
        message:
          "The Ghana Card number could not be found in the existing staff record. Please verify the number or contact the Human Resource Directorate."
      });
    }

    const verificationToken =
      createGhanaCardToken({
        oldRecordId:
          oldRecord.id,

        campaignId:
          campaign.id,

        normalizedGhanaCard
      });

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

      message:
        "Ghana Card number validated successfully.",

      data: {
        verification_token:
          verificationToken,

        expires_in:
          process.env
            .PORTAL_ACCESS_TOKEN_EXPIRES_IN ||
          "30m",

        staff_hint: {
          masked_name:
            maskName(
              oldRecord.full_name
            )
        },

        campaign: {
          id:
            campaign.id,

          campaign_name:
            campaign.campaign_name,

          campaign_year:
            campaign.campaign_year,

          end_date:
            campaign.end_date
        }
      }
    });
  } catch (error) {
    console.error(
      "Validate Ghana Card error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to validate the Ghana Card number at this time."
    });
  }
};

/* =========================================================
   VERIFY STAFF ID
   POST /api/public/identify
   ========================================================= */

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

  try {
    const staffNumber =
      normalizeStaffId(
        request.body?.staff_number
      );

    if (!staffNumber) {
      return response.status(400).json({
        success: false,
        message:
          "Staff ID is required."
      });
    }

    if (
      staffNumber.length < 4 ||
      staffNumber.length > 20
    ) {
      return response.status(400).json({
        success: false,
        message:
          "Enter a valid Staff ID."
      });
    }

    const oldRecordId =
      request.ghanaCardAccess
        ?.old_record_id;

    const campaignId =
      request.ghanaCardAccess
        ?.campaign_id;

    if (
      !oldRecordId ||
      !campaignId
    ) {
      return response.status(401).json({
        success: false,
        message:
          "Your Ghana Card verification session is invalid. Please start again."
      });
    }

    connection =
      await pool.getConnection();

    await connection.beginTransaction();

    transactionStarted = true;

    const [campaignRows] =
      await connection.query(
        `
          SELECT
            id,
            campaign_name,
            campaign_year,
            allow_multiple_submissions
          FROM record_update_campaigns
          WHERE
            id = ?
            AND status = 'active'
            AND start_date <= NOW()
            AND end_date >= NOW()
          LIMIT 1
          FOR UPDATE
        `,
        [campaignId]
      );

    const campaign =
      campaignRows[0];

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

      return response.status(403).json({
        success: false,
        message:
          "The staff records update exercise is no longer active."
      });
    }

    const [oldRecordRows] =
      await connection.query(
        `
          SELECT
            id,
            employee_number,
            full_name,
            sex,
            nia_number,
            date_of_birth,
            hire_date,
            mgt_unit,
            job_name,
            grade,
            social_security_number,
            division,
            region,
            district,
            location,
            assignment_status
          FROM old_record
          WHERE
            id = ?
            AND employee_number = ?
          LIMIT 1
          FOR UPDATE
        `,
        [
          oldRecordId,
          staffNumber
        ]
      );

    const oldRecord =
      oldRecordRows[0];

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

      return response.status(404).json({
        success: false,
        message:
          "The Staff ID does not match the employee record associated with the Ghana Card number."
      });
    }

    /*
     * The current form implementation still references
     * the staff table. Create or update a compatibility
     * staff row from the verified old record.
     */

    const nameParts =
      buildLegacyNameParts(
        oldRecord.full_name
      );

    const gender =
      String(
        oldRecord.sex || ""
      ).toLowerCase() === "female"
        ? "female"
        : "male";

    const [existingStaffRows] =
      await connection.query(
        `
          SELECT id
          FROM staff
          WHERE staff_number = ?
          LIMIT 1
          FOR UPDATE
        `,
        [staffNumber]
      );

    let staffId =
      existingStaffRows[0]?.id;

    if (!staffId) {
      const [staffInsert] =
        await connection.query(
          `
            INSERT INTO staff (
              staff_number,
              surname,
              first_name,
              middle_name,
              gender,
              marital_status,
              nationality,
              ghana_card_number,
              ssnit_number,
              home_region_id,
              primary_mobile,
              official_email,
              record_status
            )
            VALUES (
              ?,
              ?,
              ?,
              ?,
              ?,
              'single',
              'Ghanaian',
              ?,
              ?,
              NULL,
              NULL,
              NULL,
              'draft'
            )
          `,
          [
            staffNumber,
            nameParts.surname,
            nameParts.firstName,
            nameParts.middleName,
            gender,
            formatGhanaCard(
              normalizeGhanaCard(
                oldRecord.nia_number
              )
            ),
            oldRecord
              .social_security_number ||
              null
          ]
        );

      staffId =
        staffInsert.insertId;
    } else {
      await connection.query(
        `
          UPDATE staff
          SET
            surname = ?,
            first_name = ?,
            middle_name = ?,
            gender = ?,
            ghana_card_number = ?,
            ssnit_number = ?,
            updated_at =
              CURRENT_TIMESTAMP
          WHERE id = ?
        `,
        [
          nameParts.surname,
          nameParts.firstName,
          nameParts.middleName,
          gender,
          formatGhanaCard(
            normalizeGhanaCard(
              oldRecord.nia_number
            )
          ),
          oldRecord
            .social_security_number ||
            null,
          staffId
        ]
      );
    }

    /*
     * Pre-create the new-record shell.
     * It remains draft until the full form is submitted.
     */

    await connection.query(
      `
        INSERT INTO new_record (
          old_record_id,
          staff_id,
          ghana_card_number,
          full_name,
          gender,
          date_of_birth,
          current_designation,
          grade,
          organisation_structure,
          date_first_appointment,
          current_duty_station,
          ssnit_number,
          record_status
        )
        VALUES (
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          ?,
          'draft'
        )
        ON DUPLICATE KEY UPDATE
          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 =
            COALESCE(
              current_designation,
              VALUES(
                current_designation
              )
            ),

          grade =
            COALESCE(
              grade,
              VALUES(grade)
            ),

          organisation_structure =
            COALESCE(
              organisation_structure,
              VALUES(
                organisation_structure
              )
            ),

          date_first_appointment =
            COALESCE(
              date_first_appointment,
              VALUES(
                date_first_appointment
              )
            ),

          current_duty_station =
            COALESCE(
              current_duty_station,
              VALUES(
                current_duty_station
              )
            ),

          ssnit_number =
            COALESCE(
              ssnit_number,
              VALUES(ssnit_number)
            ),

          updated_at =
            CURRENT_TIMESTAMP
      `,
      [
        oldRecord.id,
        staffNumber,
        formatGhanaCard(
          normalizeGhanaCard(
            oldRecord.nia_number
          )
        ),
        oldRecord.full_name,
        gender,
        oldRecord.date_of_birth ||
          null,
        oldRecord.job_name ||
          null,
        oldRecord.grade ||
          null,
        [
          oldRecord.region,
          oldRecord.mgt_unit,
          oldRecord.division
        ]
          .filter(Boolean)
          .join(" / ") || null,
        oldRecord.hire_date ||
          null,
        oldRecord.location ||
          null,
        oldRecord
          .social_security_number ||
          null
      ]
    );

    const [submissionRows] =
      await connection.query(
        `
          SELECT
            id,
            submission_reference,
            submission_status,
            submitted_at
          FROM staff_submissions
          WHERE
            campaign_id = ?
            AND old_record_id = ?
          ORDER BY created_at DESC
          LIMIT 1
          FOR UPDATE
        `,
        [
          campaign.id,
          oldRecord.id
        ]
      );

    let submission =
      submissionRows[0] || null;

    const blockedStatuses = [
      "submitted",
      "under_review",
      "approved"
    ];

    if (
      submission &&
      !Boolean(
        campaign
          .allow_multiple_submissions
      ) &&
      blockedStatuses.includes(
        submission.submission_status
      )
    ) {
      await connection.rollback();
      transactionStarted = false;

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

        code:
          "SUBMISSION_ALREADY_EXISTS",

        message:
          "A staff records update has already been submitted for this employee.",

        data: {
          submission_reference:
            submission
              .submission_reference,

          submission_status:
            submission
              .submission_status,

          submitted_at:
            submission.submitted_at
        }
      });
    }

    if (!submission) {
      let submissionReference = "";
      let referenceExists = true;
      let attempts = 0;

      while (
        referenceExists &&
        attempts < 10
      ) {
        attempts += 1;

        submissionReference =
          createSubmissionReference(
            campaign.campaign_year
          );

        const [referenceRows] =
          await connection.query(
            `
              SELECT id
              FROM staff_submissions
              WHERE submission_reference = ?
              LIMIT 1
            `,
            [submissionReference]
          );

        referenceExists =
          referenceRows.length > 0;
      }

      if (referenceExists) {
        throw new Error(
          "Unable to generate a unique submission reference."
        );
      }

      const [insertResult] =
        await connection.query(
          `
            INSERT INTO staff_submissions (
              campaign_id,
              old_record_id,
              submission_reference,
              staff_id,
              submitted_staff_number,
              submitted_payroll_number,
              submitted_official_email,
              submitted_mobile_number,
              submission_status,
              verification_status,
              verification_method,
              access_ip_address,
              access_user_agent
            )
            VALUES (
              ?,
              ?,
              ?,
              ?,
              ?,
              NULL,
              NULL,
              NULL,
              'draft',
              'verified',
              'staff_number',
              ?,
              ?
            )
          `,
          [
            campaign.id,
            oldRecord.id,
            submissionReference,
            staffId,
            staffNumber,
            request.ip || null,
            request.get(
              "user-agent"
            ) || null
          ]
        );

      submission = {
        id:
          insertResult.insertId,

        submission_reference:
          submissionReference,

        submission_status:
          "draft"
      };
    }

    await connection.commit();
    transactionStarted = false;

    const submissionToken =
      createSubmissionToken({
        submissionId:
          submission.id,

        staffId,

        oldRecordId:
          oldRecord.id,

        campaignId:
          campaign.id,

        submissionReference:
          submission
            .submission_reference
      });

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

      message:
        "Staff identity verified successfully.",

      data: {
        submission_token:
          submissionToken,

        expires_in:
          process.env
            .PORTAL_SUBMISSION_TOKEN_EXPIRES_IN ||
          "2h",

        submission: {
          id:
            submission.id,

          submission_reference:
            submission
              .submission_reference,

          submission_status:
            submission
              .submission_status
        },

        staff: {
          old_record_id:
            oldRecord.id,

          staff_number:
            staffNumber,

          full_name:
            oldRecord.full_name
        },

        campaign: {
          id:
            campaign.id,

          campaign_name:
            campaign.campaign_name,

          campaign_year:
            campaign.campaign_year
        }
      }
    });
  } catch (error) {
    if (
      connection &&
      transactionStarted
    ) {
      try {
        await connection.rollback();
      } catch (rollbackError) {
        console.error(
          "Identity rollback error:",
          rollbackError
        );
      }
    }

    console.error(
      "Staff identification error:",
      error
    );

    return response.status(500).json({
      success: false,
      message:
        "Unable to verify the Staff ID at this time."
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
};

/* =========================================================
   GET SUBMISSION SESSION
   GET /api/public/submission/session
   ========================================================= */

const getSubmissionSession = async (
  request,
  response
) => {
  try {
    const submissionId =
      request.staffSubmission
        ?.submission_id;

    const oldRecordId =
      request.staffSubmission
        ?.old_record_id;

    const campaignId =
      request.staffSubmission
        ?.campaign_id;

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

            oldr.id AS old_record_id,
            oldr.employee_number,
            oldr.full_name,

            ruc.id AS campaign_id,
            ruc.campaign_name,
            ruc.campaign_year,
            ruc.start_date,
            ruc.end_date,
            ruc.status AS campaign_status

          FROM staff_submissions ss

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

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

          WHERE
            ss.id = ?
            AND ss.old_record_id = ?
            AND ss.campaign_id = ?

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

    const submission =
      rows[0];

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

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

      data: {
        submission: {
          id:
            submission.id,

          submission_reference:
            submission
              .submission_reference,

          submission_status:
            submission
              .submission_status,

          verification_status:
            submission
              .verification_status,

          submitted_at:
            submission.submitted_at,

          created_at:
            submission.created_at,

          updated_at:
            submission.updated_at
        },

        staff: {
          old_record_id:
            submission.old_record_id,

          staff_number:
            submission.employee_number,

          full_name:
            submission.full_name
        },

        campaign: {
          id:
            submission.campaign_id,

          campaign_name:
            submission.campaign_name,

          campaign_year:
            submission.campaign_year,

          start_date:
            submission.start_date,

          end_date:
            submission.end_date,

          status:
            submission.campaign_status
        }
      }
    });
  } catch (error) {
    console.error(
      "Submission session error:",
      error
    );

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

module.exports = {
  getCurrentCampaign,
  validateGhanaCard,
  identifyStaff,
  getSubmissionSession
};