import { pool } from "../config/db.js";
import { ApiError } from "../utils/ApiError.js";
import { getJwtUserId } from "../middlewares/authMiddleware.js";
import { ensurePermitStatuses } from "../utils/permitStatus.js";

const PERMIT_COLUMNS = [
  "permit_number",
  "uploader_user_id",
  "permit_status_id",
  "country_id",
  "location_id",
  "department_id"
];

function toPositiveInt(value, fieldName) {
  const n = Number(value);
  if (!Number.isInteger(n) || n <= 0) {
    throw new ApiError(400, `${fieldName} must be a positive integer`);
  }
  return n;
}

function actorId(req) {
  const raw = getJwtUserId(req.user);
  const n = raw != null ? Number(raw) : NaN;
  return Number.isInteger(n) && n > 0 ? n : null;
}

export async function listPermits(req, res, next) {
  try {
    const filters = req.query || {};
    const whereParts = [];
    const values = [];
    Object.entries(filters).forEach(([key, value]) => {
      if (value === undefined || value === null || value === "") return;
      if (key.endsWith("_like")) {
        const column = key.slice(0, -5);
        whereParts.push(`p.\`${column}\` LIKE ?`);
        values.push(`%${value}%`);
      } else {
        whereParts.push(`p.\`${key}\` = ?`);
        values.push(value);
      }
    });
    let sql = `
      SELECT p.*, loc.location_name, loc.country_id AS location_country_id
      FROM ptw_permits p
      LEFT JOIN locations loc ON loc.location_id = p.location_id`;
    if (whereParts.length) sql += ` WHERE ${whereParts.join(" AND ")}`;
    sql += " ORDER BY p.permit_id DESC";
    const [rows] = await pool.query(sql, values);
    res.json(rows);
  } catch (err) {
    next(err);
  }
}

export async function getPermit(req, res, next) {
  try {
    const permitId = toPositiveInt(req.params.permit_id, "permit_id");
    const [rows] = await pool.query(
      "SELECT * FROM ptw_permits WHERE permit_id = ? LIMIT 1",
      [permitId]
    );
    if (!rows.length) throw new ApiError(404, "ptw_permits record not found");
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
}

/** Create (initialize) a permit. uploader defaults to the JWT user; permit_number auto-generated if absent. */
export async function createPermit(req, res, next) {
  try {
    const body = req.body || {};
    const write = {};
    for (const col of PERMIT_COLUMNS) {
      if (Object.prototype.hasOwnProperty.call(body, col) && body[col] !== undefined) {
        write[col] = body[col];
      }
    }
    if (write.uploader_user_id == null) {
      write.uploader_user_id = actorId(req);
    }
    if (write.permit_number == null || String(write.permit_number).trim() === "") {
      write.permit_number = `PTW-${Date.now()}`;
    }

    const columns = Object.keys(write);
    if (!columns.length) throw new ApiError(400, "Body cannot be empty");
    if (write.permit_status_id != null) {
      await ensurePermitStatuses(pool);
    }
    const placeholders = columns.map(() => "?").join(",");
    const [result] = await pool.query(
      `INSERT INTO ptw_permits (${columns.map((c) => `\`${c}\``).join(",")}) VALUES (${placeholders})`,
      columns.map((c) => write[c])
    );
    res.status(201).json({
      insertedId: Number(result.insertId),
      permit_number: write.permit_number,
      affectedRows: result.affectedRows
    });
  } catch (err) {
    next(err);
  }
}

/** Update a permit (commonly a status transition). */
export async function updatePermit(req, res, next) {
  try {
    const permitId = toPositiveInt(req.params.permit_id, "permit_id");
    const body = req.body || {};
    const write = {};
    for (const col of PERMIT_COLUMNS) {
      if (Object.prototype.hasOwnProperty.call(body, col) && body[col] !== undefined) {
        write[col] = body[col];
      }
    }
    const columns = Object.keys(write);
    if (!columns.length) throw new ApiError(400, "Body cannot be empty");

    if (write.permit_status_id != null) {
      const statusId = Number(write.permit_status_id);
      if (!Number.isInteger(statusId) || statusId <= 0) {
        throw new ApiError(400, "permit_status_id must be a positive integer");
      }
      await ensurePermitStatuses(pool);
      const [statusRows] = await pool.query(
        "SELECT permit_status_id FROM ptw_permit_status WHERE permit_status_id = ? LIMIT 1",
        [statusId]
      );
      if (!statusRows.length) {
        throw new ApiError(
          400,
          `Unknown permit_status_id ${statusId}. Seed ptw_permit_status or choose a valid status.`
        );
      }
    }

    const setClause = columns.map((c) => `\`${c}\` = ?`).join(", ");
    const [result] = await pool.query(
      `UPDATE ptw_permits SET ${setClause} WHERE permit_id = ?`,
      [...columns.map((c) => write[c]), permitId]
    );
    if (!result.affectedRows) {
      throw new ApiError(404, "ptw_permits record not found");
    }
    res.json({ affectedRows: result.affectedRows });
  } catch (err) {
    if (err?.code === "ER_NO_REFERENCED_ROW_2") {
      next(new ApiError(400, "Invalid reference in permit update (check permit_status_id and related IDs)"));
      return;
    }
    next(err);
  }
}

export async function deletePermit(req, res, next) {
  const conn = await pool.getConnection();
  try {
    const permitId = toPositiveInt(req.params.permit_id, "permit_id");
    await conn.beginTransaction();
    await conn.query("DELETE FROM ptw_opening_closing_map WHERE permit_id = ?", [permitId]);
    const [result] = await conn.query(
      "DELETE FROM ptw_permits WHERE permit_id = ?",
      [permitId]
    );
    await conn.commit();
    res.json({ affectedRows: result.affectedRows });
  } catch (err) {
    await conn.rollback().catch(() => {});
    next(err);
  } finally {
    conn.release();
  }
}
