import { pool } from "../config/db.js";
import { ApiError } from "../utils/ApiError.js";
import { snapshotUserLog } from "../utils/auditLogs.js";
import {
  normalizeBlobForPersist,
  bufferFromDb,
  sniffContentType
} from "../utils/binary.js";

/** Writable columns on `users` (PK + created_at are managed by DB/flow). */
const USER_COLUMNS = [
  "employee_id",
  "status_id",
  "employee_name",
  "job_title_id",
  "email_id",
  "phonenumber",
  "department_id",
  "location_id",
  "photo",
  "password"
];

/** Non-blob columns, safe for JSON list/detail responses. */
const USER_PUBLIC_COLUMNS = USER_COLUMNS.filter((c) => c !== "photo");

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;
}

/** Collect role_ids from body (`role_ids: []` or single `role_id`). */
function extractRoleIds(body) {
  const ids = [];
  const raw = body?.role_ids != null ? body.role_ids : body?.role_id;
  if (raw == null || raw === "") return ids;
  const list = Array.isArray(raw) ? raw : [raw];
  for (const x of list) {
    const n = Number(x);
    if (Number.isInteger(n) && n > 0) ids.push(n);
  }
  return [...new Set(ids)];
}

/** Build the column/value pairs for a users write from body + multipart file. */
function buildUserWrite(req, { partial }) {
  const body = req.body || {};
  const out = {};
  for (const col of USER_COLUMNS) {
    if (Object.prototype.hasOwnProperty.call(body, col) && body[col] !== undefined) {
      out[col] = col === "photo" ? normalizeBlobForPersist(body[col]) : body[col];
    }
  }
  // multipart upload wins over a body-provided photo string
  if (req.file && req.file.buffer) {
    out.photo = req.file.buffer;
  }
  if (!partial) {
    // Minimal required field for provisioning
    if (out.employee_name == null || String(out.employee_name).trim() === "") {
      throw new ApiError(400, "employee_name is required");
    }
  }
  return out;
}

export async function listUsers(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")) {
        whereParts.push(`\`${key.slice(0, -5)}\` LIKE ?`);
        values.push(`%${value}%`);
      } else if (USER_PUBLIC_COLUMNS.includes(key) || key === "user_id") {
        whereParts.push(`\`${key}\` = ?`);
        values.push(value);
      }
    });

    let sql = `SELECT user_id, ${USER_PUBLIC_COLUMNS.map((c) => `\`${c}\``).join(", ")},
        created_at,
        (CASE WHEN photo IS NOT NULL AND LENGTH(photo) > 0 THEN 1 ELSE 0 END) AS has_photo
      FROM users`;
    if (whereParts.length) sql += ` WHERE ${whereParts.join(" AND ")}`;
    sql += " ORDER BY user_id ASC";

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

export async function getUser(req, res, next) {
  try {
    const userId = toPositiveInt(req.params.user_id, "user_id");
    const [rows] = await pool.query(
      `SELECT user_id, ${USER_PUBLIC_COLUMNS.map((c) => `\`${c}\``).join(", ")},
          created_at,
          (CASE WHEN photo IS NOT NULL AND LENGTH(photo) > 0 THEN 1 ELSE 0 END) AS has_photo
        FROM users WHERE user_id = ? LIMIT 1`,
      [userId]
    );
    if (!rows.length) throw new ApiError(404, "users record not found");
    const [roleRows] = await pool.query(
      "SELECT role_id FROM user_role WHERE user_id = ?",
      [userId]
    );
    res.json({ ...rows[0], role_ids: roleRows.map((r) => Number(r.role_id)) });
  } catch (err) {
    next(err);
  }
}

export async function serveUserPhoto(req, res, next) {
  try {
    const userId = toPositiveInt(req.params.user_id, "user_id");
    const [rows] = await pool.query(
      "SELECT photo FROM users WHERE user_id = ? LIMIT 1",
      [userId]
    );
    if (!rows.length || !rows[0].photo) {
      return res.status(404).json({ message: "Photo not found" });
    }
    const buf = bufferFromDb(rows[0].photo);
    res.set("Content-Type", sniffContentType(buf));
    res.set("Cache-Control", "public, max-age=86400");
    res.send(buf);
  } catch (err) {
    next(err);
  }
}

export async function createUser(req, res, next) {
  const conn = await pool.getConnection();
  try {
    const write = buildUserWrite(req, { partial: false });
    const roleIds = extractRoleIds(req.body || {});

    const columns = Object.keys(write);
    if (!columns.length) throw new ApiError(400, "Body cannot be empty");

    await conn.beginTransaction();

    const placeholders = columns.map(() => "?").join(",");
    const [result] = await conn.query(
      `INSERT INTO users (${columns.map((c) => `\`${c}\``).join(",")}) VALUES (${placeholders})`,
      columns.map((c) => write[c])
    );
    const userId = Number(result.insertId);

    for (const roleId of roleIds) {
      await conn.query(
        "INSERT INTO user_role (role_id, user_id) VALUES (?, ?)",
        [roleId, userId]
      );
    }

    await conn.commit();
    res.status(201).json({ insertedId: userId, affectedRows: result.affectedRows });
  } catch (err) {
    await conn.rollback().catch(() => {});
    next(err);
  } finally {
    conn.release();
  }
}

export async function updateUser(req, res, next) {
  const conn = await pool.getConnection();
  try {
    const userId = toPositiveInt(req.params.user_id, "user_id");
    const write = buildUserWrite(req, { partial: true });
    const hasRoleIds =
      req.body &&
      (Object.prototype.hasOwnProperty.call(req.body, "role_ids") ||
        Object.prototype.hasOwnProperty.call(req.body, "role_id"));
    const roleIds = extractRoleIds(req.body || {});

    const columns = Object.keys(write);
    if (!columns.length && !hasRoleIds) {
      throw new ApiError(400, "Body cannot be empty");
    }

    await conn.beginTransaction();

    // Snapshot prior state BEFORE mutation (same transaction).
    await snapshotUserLog(conn, userId, "UPDATE");

    let affectedRows = 0;
    if (columns.length) {
      const setClause = columns.map((c) => `\`${c}\` = ?`).join(", ");
      const [result] = await conn.query(
        `UPDATE users SET ${setClause} WHERE user_id = ?`,
        [...columns.map((c) => write[c]), userId]
      );
      affectedRows = result.affectedRows;
    }

    if (hasRoleIds) {
      await conn.query("DELETE FROM user_role WHERE user_id = ?", [userId]);
      for (const roleId of roleIds) {
        await conn.query(
          "INSERT INTO user_role (role_id, user_id) VALUES (?, ?)",
          [roleId, userId]
        );
      }
    }

    await conn.commit();
    res.json({ affectedRows });
  } catch (err) {
    await conn.rollback().catch(() => {});
    next(err);
  } finally {
    conn.release();
  }
}

export async function deleteUser(req, res, next) {
  const conn = await pool.getConnection();
  try {
    const userId = toPositiveInt(req.params.user_id, "user_id");

    await conn.beginTransaction();

    // Snapshot prior state BEFORE delete.
    await snapshotUserLog(conn, userId, "DELETE");

    // Remove access mappings first to satisfy FK constraints.
    await conn.query("DELETE FROM user_role WHERE user_id = ?", [userId]);
    await conn.query(
      "DELETE FROM admin_level_user_multi_city_map WHERE user_id = ?",
      [userId]
    );

    const [result] = await conn.query("DELETE FROM users WHERE user_id = ?", [
      userId
    ]);

    await conn.commit();
    res.json({ affectedRows: result.affectedRows });
  } catch (err) {
    await conn.rollback().catch(() => {});
    next(err);
  } finally {
    conn.release();
  }
}
