import path from "path";
import { fileURLToPath } from "url";
import { pool } from "../config/db.js";
import { ApiError } from "../utils/ApiError.js";
import {
  getJwtUserId,
  getNormalizedRole
} from "../middlewares/authMiddleware.js";
import {
  assertCurrentUserIsAssessmentExecutor,
  assertCurrentUserIsAssessmentVerifier
} from "../utils/oracAssessmentRoleRules.js";
import { isOracFormArchived } from "../utils/oracFormVersioning.js";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

/**
 * Migration 001 adds `orac_assessments.form_id`.
 * Do not cache: a stale `true` after DB restore / env switch caused SELECT to use `a.form_id`
 * when the column was missing → "Unknown column 'form_id' in 'field list'".
 */
async function oracAssessmentsFormIdColumnExists() {
  try {
    const [rows] = await pool.query(
      "SHOW COLUMNS FROM `orac_assessments` LIKE 'form_id'"
    );
    return rows.length > 0;
  } catch {
    return false;
  }
}

/** Assign form / persist `form_id` requires migration 001 (`orac_assessments.form_id`). */
async function assertOracAssessmentsHasFormIdColumn() {
  if (!(await oracAssessmentsFormIdColumnExists())) {
    throw new ApiError(
      500,
      "Database column orac_assessments.form_id is missing. " +
        "Apply migrations/001_add_orac_assessments_form_id.sql, or from Risk_assessment_backend run: node scripts/apply-form-id-migration.js"
    );
  }
}

/**
 * Effective ORAC form for listing: per-assessment `form_id` when the column exists, else MIN(form) for location.
 */
async function sqlFragmentOracFormIdAsColumn() {
  if (await oracAssessmentsFormIdColumnExists()) {
    return `COALESCE(
          a.form_id,
          (
            SELECT MIN(lf.form_id)
            FROM orac_location_forms_details lf
            WHERE lf.location_id = a.location_id
          )
        ) AS orac_form_id`;
  }
  return `(
          SELECT MIN(lf.form_id)
          FROM orac_location_forms_details lf
          WHERE lf.location_id = a.location_id
        ) AS orac_form_id`;
}

/**
 * Validates `form_id` for assignment: must exist; if the assessment has a location with
 * at least one linked form, the selection must be one of those forms (otherwise any form is allowed).
 */
async function validateAssignmentFormId(assessmentId, formIdRaw) {
  const id = Number(formIdRaw);
  if (!Number.isInteger(id) || id <= 0) {
    throw new ApiError(400, "form_id is required and must be a positive integer");
  }
  const [formRows] = await pool.query(
    "SELECT 1 AS ok FROM `orac_form_details` WHERE `form_id` = ? LIMIT 1",
    [id]
  );
  if (!formRows.length) {
    throw new ApiError(404, "Form not found");
  }
  if (await isOracFormArchived(pool, id)) {
    throw new ApiError(
      400,
      "This form version is archived; choose the current form linked to the location for new audits."
    );
  }
  const [assRows] = await pool.query(
    "SELECT `location_id` FROM `orac_assessments` WHERE `assessment_id` = ? LIMIT 1",
    [assessmentId]
  );
  const locId = assRows[0]?.location_id;
  if (locId == null || locId === "") {
    return id;
  }
  const [mapped] = await pool.query(
    "SELECT `form_id` FROM `orac_location_forms_details` WHERE `location_id` = ?",
    [locId]
  );
  if (!mapped.length) {
    return id;
  }
  const ok = mapped.some((r) => Number(r.form_id) === id);
  if (!ok) {
    throw new ApiError(
      400,
      "Selected form is not linked to this assessment's location. Map the form to the location in admin, or choose a listed form."
    );
  }
  return id;
}

function isFormIdProvided(body) {
  const v = body?.form_id;
  return v != null && v !== "";
}

export async function listOracAssessments(req, res, next) {
  try {
    const role = getNormalizedRole(req.user);
    const executorRoleName = (process.env.JWT_EXECUTOR_ROLE || "executer/varifier")
      .trim()
      .toLowerCase()
      .replace(/\s*\/\s*/g, "/");
    const adminRoleName = (process.env.JWT_ADMIN_ROLE || "admin")
      .trim()
      .toLowerCase()
      .replace(/\s*\/\s*/g, "/");
    const userRoleName = (process.env.JWT_USER_ROLE || "user")
      .trim()
      .toLowerCase()
      .replace(/\s*\/\s*/g, "/");

    let filters = { ...(req.query || {}) };

    if (role === executorRoleName) {
      const uid = getJwtUserId(req.user);
      if (!uid) {
        throw new ApiError(401, "Could not resolve user id from token");
      }
      const assignRole = String(req.query.assign_role || "executor")
        .trim()
        .toLowerCase();
      if (assignRole === "verifier") {
        filters = { verifier_id: uid };
      } else {
        filters = { executor_id: uid };
      }
    } else if (role === userRoleName) {
      const uid = getJwtUserId(req.user);
      if (!uid) {
        throw new ApiError(401, "Could not resolve user id from token");
      }
      // End-user uploads only — ignore query params that could scope another user's rows
      filters = { uploader_user_id: uid };
    } else if (
      filters.executor_id !== undefined &&
      filters.executor_id !== null &&
      filters.executor_id !== ""
    ) {
      if (role !== adminRoleName) {
        throw new ApiError(403, "Not allowed to filter assessments by executor");
      }
    }

    const whereParts = [];
    const values = [];

    Object.entries(filters).forEach(([key, value]) => {
      if (key === "assign_role") return;
      if (value === undefined || value === null || value === "") return;
      if (key.endsWith("_like")) {
        const column = key.slice(0, -5);
        whereParts.push(`a.\`${column}\` LIKE ?`);
        values.push(`%${value}%`);
      } else {
        whereParts.push(`a.\`${key}\` = ?`);
        values.push(value);
      }
    });

    const oracFormIdCol = await sqlFragmentOracFormIdAsColumn();

    let sql = `
      SELECT
        a.*,
        s.assessment_status_name,
        l.Location_name  AS location_name,
        d.department_name AS department_name,
        ${oracFormIdCol},
        (
          SELECT u.employee_name FROM users u
          WHERE u.user_id = a.uploader_user_id LIMIT 1
        ) AS uploaded_by_name,
        (
          SELECT u.employee_name FROM users u
          WHERE u.user_id = a.executor_id LIMIT 1
        ) AS executor_name,
        (
          SELECT u.employee_name FROM users u
          WHERE u.user_id = a.verifier_id LIMIT 1
        ) AS verifier_name,
        (
          SELECT COALESCE(MAX(vf.\`occurrence\`), 0)
          FROM orac_verification_flow vf
          WHERE vf.assessment_id = a.assessment_id
        ) AS vf_max_occurrence,
        (
          SELECT vf_rf.\`returned_for\`
          FROM orac_verification_flow vf_rf
          WHERE vf_rf.assessment_id = a.assessment_id
          ORDER BY vf_rf.\`occurrence\` DESC
          LIMIT 1
        ) AS vf_latest_returned_for,
        (
          SELECT IFNULL(
            (
              SELECT JSON_ARRAYAGG(
                JSON_OBJECT(
                  'assessment_id', op.assessment_id,
                  'participant_user_id', op.participant_user_id,
                  'participants_name', op.participants_name,
                  'participant_name', (
                    SELECT u.employee_name
                    FROM users u
                    WHERE u.user_id = op.participant_user_id
                    LIMIT 1
                  )
                )
              )
              FROM orac_participants op
              WHERE op.assessment_id = a.assessment_id
            ),
            JSON_ARRAY()
          )
        ) AS participants_json
      FROM orac_assessments a
      LEFT JOIN orac_assessments_status s ON a.assessment_status_id = s.assessment_status_id
      LEFT JOIN locations l               ON a.location_id          = l.location_id
      LEFT JOIN departments d             ON a.department_id        = d.department_id
    `;

    if (whereParts.length) {
      sql += ` WHERE ${whereParts.join(" AND ")}`;
    }

    sql += ` ORDER BY a.assessment_id DESC`;

    const [rows] = await pool.query(sql, values);
    res.json(rows);
  } catch (err) {
    next(err);
  }
}

export async function listUserAssessmentsGroupedByStatus(req, res, next) {
  try {
    const { user_id } = req.params;

    if (!user_id) {
      throw new ApiError(400, "user_id is required");
    }

    const oracFormIdCol = await sqlFragmentOracFormIdAsColumn();

    const sql = `
      SELECT
        a.*,
        s.assessment_status_name,
        l.Location_name  AS location_name,
        d.department_name AS department_name,
        (
          SELECT u.employee_name FROM users u
          WHERE u.user_id = a.uploader_user_id LIMIT 1
        ) AS uploaded_by_name,
        (
          SELECT u.employee_name FROM users u
          WHERE u.user_id = a.executor_id LIMIT 1
        ) AS executor_name,
        (
          SELECT u.employee_name FROM users u
          WHERE u.user_id = a.verifier_id LIMIT 1
        ) AS verifier_name,
        ${oracFormIdCol}
      FROM orac_assessments a
      LEFT JOIN orac_assessments_status s ON a.assessment_status_id = s.assessment_status_id
      LEFT JOIN locations l               ON a.location_id          = l.location_id
      LEFT JOIN departments d             ON a.department_id        = d.department_id
      WHERE a.uploader_user_id = ?
      ORDER BY a.assessment_status_id ASC, a.assessment_id DESC
    `;

    const [rows] = await pool.query(sql, [user_id]);

    const groupedMap = rows.reduce((acc, assessment) => {
      const statusId = assessment.assessment_status_id;
      const statusName =
        assessment.assessment_status_name || `Status ${statusId ?? "Unknown"}`;
      const groupKey = String(statusId ?? "unknown");

      if (!acc[groupKey]) {
        acc[groupKey] = {
          assessment_status_id: statusId ?? null,
          assessment_status_name: statusName,
          count: 0,
          assessments: []
        };
      }

      acc[groupKey].assessments.push(assessment);
      acc[groupKey].count += 1;
      return acc;
    }, {});

    const grouped_assessments = Object.values(groupedMap);

    res.json({
      user_id: Number(user_id) || user_id,
      total_assessments: rows.length,
      grouped_assessments
    });
  } catch (err) {
    next(err);
  }
}

export async function uploadOracAssessment(req, res, next) {
  try {
    if (!req.file) {
      throw new ApiError(400, "Video file is required");
    }

    const {
      uploader_user_id,
      // from user.txt – mapped to actual DB columns
      location_id, // "location"
      department_id, // "work area"
      contract_details,
      video_title, // "video titles"
      video_capture_date,
      video_capture_time,
      country_id,
      remarks
    } = req.body || {};
    const contractDetailsValue =
      contract_details != null && contract_details !== ""
        ? contract_details
        : req.body?.contact_details;

    if (!uploader_user_id) {
      throw new ApiError(400, "uploader_user_id is required");
    }

    // Workflow step 1 — user upload: Not Started (orac_assessments_status.assessment_status_id = 1)
    const assessment_status_id = 1;

    const videoRelativePath = path.relative(
      path.join(__dirname, "..", ".."),
      req.file.path
    );

    const record = {
      uploader_user_id,
      assessment_status_id,
      country_id,
      location_id,
      department_id,
      contract_details: contractDetailsValue,
      video_title,
      video_capture_date,
      video_capture_time,
      remarks,
      video: videoRelativePath
    };

    const columns = Object.keys(record).filter(
      (key) => record[key] !== undefined
    );

    if (!columns.length) {
      throw new ApiError(400, "No valid fields provided for assessment");
    }

    const values = columns.map((key) => record[key]);
    const placeholders = columns.map(() => "?").join(",");
    const sql = `INSERT INTO \`orac_assessments\` (${columns
      .map((c) => `\`${c}\``)
      .join(",")}) VALUES (${placeholders})`;

    const [result] = await pool.query(sql, values);

    res.status(201).json({
      assessment_id: result.insertId ?? null,
      uploader_user_id,
      assessment_status_id,
      video_path: videoRelativePath
    });
  } catch (err) {
    next(err);
  }
}

export async function assignExecutor(req, res, next) {
  try {
    const { assessment_id } = req.params;
    const { executor_id, form_id } = req.body || {};

    if (!assessment_id) {
      throw new ApiError(400, "assessment_id is required");
    }

    if (!executor_id) {
      throw new ApiError(400, "executor_id is required");
    }

    const shouldSetForm = isFormIdProvided(req.body);
    let validatedFormId = null;
    if (shouldSetForm) {
      validatedFormId = await validateAssignmentFormId(assessment_id, form_id);
    }

    const [rows] = await pool.query(
      "SELECT `assessment_id` FROM `orac_assessments` WHERE `assessment_id` = ?",
      [assessment_id]
    );

    if (!rows.length) {
      throw new ApiError(404, "Assessment not found");
    }

    // Step 2 — admin assigns executor: InProgress (3). Preserve 4/5/2 if admin reassigns mid workflow.
    const [beforeExec] = await pool.query(
      "SELECT `assessment_status_id` FROM `orac_assessments` WHERE `assessment_id` = ? LIMIT 1",
      [assessment_id]
    );
    const prevStatus = Number(beforeExec[0]?.assessment_status_id);
    const nextStatus = prevStatus === 1 ? 3 : prevStatus;
    if (shouldSetForm && validatedFormId != null) {
      await assertOracAssessmentsHasFormIdColumn();
      await pool.query(
        "UPDATE `orac_assessments` SET `executor_id` = ?, `assessment_status_id` = ?, `form_id` = ? WHERE `assessment_id` = ?",
        [executor_id, nextStatus, validatedFormId, assessment_id]
      );
    } else {
      await pool.query(
        "UPDATE `orac_assessments` SET `executor_id` = ?, `assessment_status_id` = ? WHERE `assessment_id` = ?",
        [executor_id, nextStatus, assessment_id]
      );
    }

    res.status(200).json({
      message: "Executor assigned successfully",
      assessment_id: Number(assessment_id),
      executor_id,
      form_id: shouldSetForm ? validatedFormId : undefined
    });
  } catch (err) {
    next(err);
  }
}

export async function assignVerifier(req, res, next) {
  try {
    const { assessment_id } = req.params;
    const { verifier_id, form_id } = req.body || {};

    if (!assessment_id) {
      throw new ApiError(400, "assessment_id is required");
    }

    if (!verifier_id) {
      throw new ApiError(400, "verifier_id is required");
    }

    const shouldSetForm = isFormIdProvided(req.body);
    let validatedFormId = null;
    if (shouldSetForm) {
      validatedFormId = await validateAssignmentFormId(assessment_id, form_id);
    }

    const [rows] = await pool.query(
      "SELECT `assessment_id` FROM `orac_assessments` WHERE `assessment_id` = ?",
      [assessment_id]
    );

    if (!rows.length) {
      throw new ApiError(404, "Assessment not found");
    }

    // Only set verifier (+ optional form); do not change assessment_status_id (avoids resetting 4/5/2 during verification loop).
    if (shouldSetForm && validatedFormId != null) {
      await assertOracAssessmentsHasFormIdColumn();
      await pool.query(
        "UPDATE `orac_assessments` SET `verifier_id` = ?, `form_id` = ? WHERE `assessment_id` = ?",
        [verifier_id, validatedFormId, assessment_id]
      );
    } else {
      await pool.query(
        "UPDATE `orac_assessments` SET `verifier_id` = ? WHERE `assessment_id` = ?",
        [verifier_id, assessment_id]
      );
    }

    res.status(200).json({
      message: "Verifier assigned successfully",
      assessment_id: Number(assessment_id),
      verifier_id,
      form_id: shouldSetForm ? validatedFormId : undefined
    });
  } catch (err) {
    next(err);
  }
}

/**
 * POST /orac_assessments/:assessment_id/assign_form — assigned executor (or admin) sets the ORAC form for this assessment.
 */
export async function assignAssessmentForm(req, res, next) {
  try {
    const { assessment_id } = req.params;
    const { form_id } = req.body || {};
    const uid = getJwtUserId(req.user);

    if (!assessment_id) {
      throw new ApiError(400, "assessment_id is required");
    }

    const [rows] = await pool.query(
      "SELECT `executor_id` FROM `orac_assessments` WHERE `assessment_id` = ? LIMIT 1",
      [assessment_id]
    );
    if (!rows.length) {
      throw new ApiError(404, "Assessment not found");
    }
    const { executor_id: execRow } = rows[0] || {};
    if (execRow == null || execRow === "") {
      throw new ApiError(
        400,
        "An executor must be assigned before an ORAC form can be chosen for this assessment."
      );
    }
    assertCurrentUserIsAssessmentExecutor(uid, execRow, req);

    const validatedFormId = await validateAssignmentFormId(assessment_id, form_id);

    await assertOracAssessmentsHasFormIdColumn();

    await pool.query(
      "UPDATE `orac_assessments` SET `form_id` = ? WHERE `assessment_id` = ?",
      [validatedFormId, assessment_id]
    );

    res.status(200).json({
      message: "ORAC form assigned to assessment",
      assessment_id: Number(assessment_id),
      form_id: validatedFormId
    });
  } catch (err) {
    next(err);
  }
}

/**
 * Resolves status rows by name (DB may spell "Verification" as "Varification").
 * Step 4 — return to executor: Returned (4). Step 6 — verifier done: Complete (2).
 */
async function resolveAssessmentStatusId(pool, kind) {
  const [rows] = await pool.query(
    "SELECT `assessment_status_id`, `assessment_status_name` FROM `orac_assessments_status` ORDER BY `assessment_status_id` ASC"
  );
  if (!rows.length) {
    throw new ApiError(500, "orac_assessments_status is empty — cannot resolve workflow status.");
  }
  const lowered = rows.map((r) => ({
    id: r.assessment_status_id,
    n: (r.assessment_status_name || "").toLowerCase()
  }));
  if (kind === "verifier_complete") {
    const hit = lowered.find(
      (r) =>
        r.n.includes("complete") ||
        r.n.includes("verified") ||
        (r.n.includes("done") && !r.n.includes("not"))
    );
    if (hit) return hit.id;
  }
  if (kind === "return_to_executor") {
    const hit = lowered.find(
      (r) =>
        (r.n.includes("return") && r.n.includes("executor")) ||
        r.n.includes("returned")
    );
    if (hit) return hit.id;
  }
  if (kind === "verification_required") {
    const hit = lowered.find(
      (r) =>
        r.n.includes("varific") ||
        (r.n.includes("verif") && r.n.includes("requir"))
    );
    if (hit) return hit.id;
  }
  throw new ApiError(
    500,
    `Could not resolve a "${kind}" row in orac_assessments_status — add a matching assessment_status_name.`
  );
}

/**
 * POST /api/orac_assessments/:assessment_id/executor_submit_for_verification
 * Executor finishes review: moves status from In progress (3) or Returned (4) to Verification required (5).
 */
export async function executorSubmitForVerification(req, res, next) {
  try {
    const { assessment_id } = req.params;
    const aid = Number(assessment_id);
    if (!Number.isInteger(aid) || aid <= 0) {
      throw new ApiError(400, "assessment_id must be a positive integer");
    }
    const uid = getJwtUserId(req.user);

    const [rows] = await pool.query(
      "SELECT `executor_id`, `assessment_status_id` FROM `orac_assessments` WHERE `assessment_id` = ? LIMIT 1",
      [aid]
    );
    if (!rows.length) {
      throw new ApiError(404, "Assessment not found");
    }
    const { executor_id: execRow, assessment_status_id: curStatus } = rows[0] || {};
    assertCurrentUserIsAssessmentExecutor(uid, execRow, req);

    const st = Number(curStatus);
    if (st !== 3 && st !== 4) {
      throw new ApiError(
        400,
        "Submit for verification is only available while the assessment is in progress or returned from the verifier."
      );
    }

    const [[snapCount]] = await pool.query(
      "SELECT COUNT(*) AS cnt FROM `orac_snaps` WHERE `assessment_id` = ?",
      [aid]
    );
    const cnt = Number(snapCount?.cnt) || 0;
    if (cnt < 1) {
      throw new ApiError(
        400,
        "Save at least one observation snapshot to the database before submitting for verification."
      );
    }

    const statusId = await resolveAssessmentStatusId(pool, "verification_required");
    await pool.query(
      "UPDATE `orac_assessments` SET `assessment_status_id` = ? WHERE `assessment_id` = ?",
      [statusId, aid]
    );
    res.status(200).json({
      message: "Submitted for verifier review",
      assessment_id: aid,
      assessment_status_id: statusId
    });
  } catch (err) {
    next(err);
  }
}

/**
 * POST /api/orac_assessments/:assessment_id/verifier_complete
 * Marks verification finished (e.g. after generating the risk report).
 */
export async function completeVerifierReview(req, res, next) {
  try {
    const { assessment_id } = req.params;
    const aid = Number(assessment_id);
    if (!Number.isInteger(aid) || aid <= 0) {
      throw new ApiError(400, "assessment_id must be a positive integer");
    }
    const [rows] = await pool.query(
      "SELECT `verifier_id` FROM `orac_assessments` WHERE `assessment_id` = ? LIMIT 1",
      [aid]
    );
    if (!rows.length) {
      throw new ApiError(404, "Assessment not found");
    }
    assertCurrentUserIsAssessmentVerifier(
      getJwtUserId(req.user),
      rows[0].verifier_id,
      req
    );
    const statusId = await resolveAssessmentStatusId(pool, "verifier_complete");
    await pool.query(
      "UPDATE `orac_assessments` SET `assessment_status_id` = ? WHERE `assessment_id` = ?",
      [statusId, aid]
    );
    res.status(200).json({
      message: "Verification completed",
      assessment_id: aid,
      assessment_status_id: statusId
    });
  } catch (err) {
    next(err);
  }
}

/**
 * POST /api/orac_assessments/:assessment_id/verifier_return
 * Body: { returned_for?: string } — logs a row in orac_verification_flow and sends work back to the executor.
 */
export async function returnToExecutorFromVerifier(req, res, next) {
  try {
    const { assessment_id } = req.params;
    const aid = Number(assessment_id);
    if (!Number.isInteger(aid) || aid <= 0) {
      throw new ApiError(400, "assessment_id must be a positive integer");
    }
    const returnedFor =
      req.body && typeof req.body.returned_for === "string" ? req.body.returned_for.trim() : "";
    const [rows] = await pool.query(
      "SELECT `verifier_id` FROM `orac_assessments` WHERE `assessment_id` = ? LIMIT 1",
      [aid]
    );
    if (!rows.length) {
      throw new ApiError(404, "Assessment not found");
    }
    assertCurrentUserIsAssessmentVerifier(
      getJwtUserId(req.user),
      rows[0].verifier_id,
      req
    );
    const [occRows] = await pool.query(
      "SELECT COALESCE(MAX(`occurrence`), 0) + 1 AS `next` FROM `orac_verification_flow` WHERE `assessment_id` = ?",
      [aid]
    );
    const occurrence = Number(occRows[0]?.next) || 1;
    await pool.query(
      "INSERT INTO `orac_verification_flow` (`assessment_id`, `occurrence`, `returned_for`) VALUES (?, ?, ?)",
      [aid, occurrence, returnedFor || null]
    );
    const statusId = await resolveAssessmentStatusId(pool, "return_to_executor");
    await pool.query(
      "UPDATE `orac_assessments` SET `assessment_status_id` = ? WHERE `assessment_id` = ?",
      [statusId, aid]
    );
    res.status(200).json({
      message: "Returned to executor",
      assessment_id: aid,
      assessment_status_id: statusId,
      occurrence
    });
  } catch (err) {
    next(err);
  }
}
