import { pool } from "../config/db.js";
import { SWI_DOCUMENT_STATUS, canAdminAssignDocument } from "../constants/swiDocumentStatus.js";
import { ApiError } from "../utils/ApiError.js";

/** Columns on `swi_document` (see sql/swi.sql). */
const SWI_DOCUMENT_COLUMNS = [
  "document_id",
  "document_number",
  "document_name",
  "document_status_id",
  "assigned_to_user_id",
  "previous_document_id",
  "location_id",
  "department_id",
  "job_area_id",
  "process_name",
  "page_count",
  "issued_at",
  "assigned_at",
  "completed_at",
];

const CREATE_REQUIRED = ["document_id", "document_number", "document_name"];

function pickAllowedFields(body, { allowPk = false } = {}) {
  if (!body || typeof body !== "object") {
    throw new ApiError(400, "Body must be a JSON object");
  }
  const out = {};
  for (const key of SWI_DOCUMENT_COLUMNS) {
    if (!allowPk && (key === "document_id" || key === "document_number")) {
      continue;
    }
    if (Object.prototype.hasOwnProperty.call(body, key)) {
      out[key] = body[key];
    }
  }
  return out;
}

function normalizeRowPayload(fields) {
  const normalized = { ...fields };
  for (const key of Object.keys(normalized)) {
    if (normalized[key] === "") {
      normalized[key] = null;
    }
  }
  return normalized;
}

async function fetchByPk(documentId, documentNumber) {
  const [rows] = await pool.query(
    `SELECT * FROM \`swi_document\`
     WHERE \`document_id\` = ? AND \`document_number\` = ?
     LIMIT 1`,
    [documentId, documentNumber]
  );
  return rows[0] || null;
}

export async function listSwiDocuments(req, res, next) {
  try {
    const filters = req.query || {};
    const whereParts = [];
    const values = [];

    for (const [key, value] of Object.entries(filters)) {
      if (value === undefined || value === null || value === "") continue;
      if (!SWI_DOCUMENT_COLUMNS.includes(key)) continue;
      if (key.endsWith("_like")) continue;
      whereParts.push(`\`${key}\` = ?`);
      values.push(value);
    }

    let sql = "SELECT * FROM `swi_document`";
    if (whereParts.length) {
      sql += ` WHERE ${whereParts.join(" AND ")}`;
    }
    sql += " ORDER BY `document_id` DESC, `document_number` DESC";

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

export async function getSwiDocument(req, res, next) {
  try {
    const documentId = Number(req.params.document_id);
    const documentNumber = Number(req.params.document_number);
    if (!Number.isInteger(documentId) || !Number.isInteger(documentNumber)) {
      throw new ApiError(400, "Invalid document_id or document_number");
    }
    const row = await fetchByPk(documentId, documentNumber);
    if (!row) {
      throw new ApiError(404, "swi_document record not found");
    }
    res.json(row);
  } catch (err) {
    next(err);
  }
}

export async function createSwiDocument(req, res, next) {
  try {
    const body = normalizeRowPayload(pickAllowedFields(req.body, { allowPk: true }));

    for (const key of CREATE_REQUIRED) {
      if (body[key] === undefined || body[key] === null || body[key] === "") {
        throw new ApiError(400, `${key} is required`);
      }
    }

    const documentId = Number(body.document_id);
    const documentNumber = Number(body.document_number);
    if (!Number.isInteger(documentId) || documentId < 1) {
      throw new ApiError(400, "document_id must be a positive integer");
    }
    if (!Number.isInteger(documentNumber) || documentNumber < 1) {
      throw new ApiError(400, "document_number must be a positive integer");
    }

    const existing = await fetchByPk(documentId, documentNumber);
    if (existing) {
      throw new ApiError(
        409,
        `Document already exists for document_id=${documentId}, document_number=${documentNumber}`
      );
    }

    if (!body.issued_at) {
      body.issued_at = new Date();
    }

    if (body.document_status_id == null) {
      body.document_status_id = body.assigned_to_user_id
        ? SWI_DOCUMENT_STATUS.ASSIGNED
        : SWI_DOCUMENT_STATUS.OPENED;
    }

    const columns = Object.keys(body);
    const values = columns.map((c) => body[c]);
    const placeholders = columns.map(() => "?").join(",");
    await pool.query(
      `INSERT INTO \`swi_document\` (${columns.map((c) => `\`${c}\``).join(",")})
       VALUES (${placeholders})`,
      values
    );

    const created = await fetchByPk(documentId, documentNumber);
    res.status(201).json(created);
  } catch (err) {
    if (err?.code === "ER_NO_REFERENCED_ROW_2") {
      return next(
        new ApiError(
          400,
          "Invalid foreign key — check user, location, department, status, or job area exists",
          err.sqlMessage
        )
      );
    }
    next(err);
  }
}


export async function updateSwiDocument(req, res, next) {
  try {
    const documentId = Number(req.params.document_id);
    const documentNumber = Number(req.params.document_number);
    if (!Number.isInteger(documentId) || !Number.isInteger(documentNumber)) {
      throw new ApiError(400, "Invalid document_id or document_number");
    }

    const existing = await fetchByPk(documentId, documentNumber);
    if (!existing) {
      throw new ApiError(404, "swi_document record not found");
    }

    const body = normalizeRowPayload(pickAllowedFields(req.body));
    const columns = Object.keys(body);
    if (!columns.length) {
      throw new ApiError(400, "Body cannot be empty");
    }

    if (Object.prototype.hasOwnProperty.call(body, "assigned_to_user_id")) {
      const nextAssignee = body.assigned_to_user_id;
      if (nextAssignee != null && !canAdminAssignDocument(existing)) {
        throw new ApiError(
          409,
          "This document is already assigned and cannot be reassigned"
        );
      }
    }

    const setClause = columns.map((c) => `\`${c}\` = ?`).join(", ");
    const values = columns.map((c) => body[c]);
    await pool.query(
      `UPDATE \`swi_document\` SET ${setClause}
       WHERE \`document_id\` = ? AND \`document_number\` = ?`,
      [...values, documentId, documentNumber]
    );

    const updated = await fetchByPk(documentId, documentNumber);
    res.json(updated);
  } catch (err) {
    if (err?.code === "ER_NO_REFERENCED_ROW_2") {
      return next(
        new ApiError(
          400,
          "Invalid foreign key — check user, location, department, status, or job area exists",
          err.sqlMessage
        )
      );
    }
    next(err);
  }
}

export async function deleteSwiDocument(req, res, next) {
  try {
    const documentId = Number(req.params.document_id);
    const documentNumber = Number(req.params.document_number);
    const [result] = await pool.query(
      "DELETE FROM `swi_document` WHERE `document_id` = ? AND `document_number` = ?",
      [documentId, documentNumber]
    );
    res.json({ affectedRows: result.affectedRows });
  } catch (err) {
    next(err);
  }
}
