import { pool } from "../config/db.js";
import { ApiError } from "../utils/ApiError.js";
import { getJwtUserId, getNormalizedRole } from "../middlewares/authMiddleware.js";
import {
  assertCurrentUserIsAssessmentExecutor,
  assertExecutorNotBlockedByPendingVerification,
  assertUserCanViewAssessmentSnapsFromRow
} from "../utils/oracAssessmentRoleRules.js";

function buildWhereFromParamsLocal(primaryKeys, params) {
  const whereParts = [];
  const values = [];
  primaryKeys.forEach((pk) => {
    if (!(pk in params)) {
      throw new ApiError(400, `Missing primary key param: ${pk}`);
    }
    whereParts.push(`\`${pk}\` = ?`);
    values.push(params[pk]);
  });
  return { where: whereParts.join(" AND "), values };
}

const TABLE = "orac_snaps";
const PRIMARY_KEYS = ["snap_id", "assessment_id"];

/** Only these columns may be written on POST — avoids stray JSON keys and accidental `snap_id` overrides. */
const SNAPS_CREATE_COLUMNS = ["assessment_id", "snap", "snap_timing"];

/** Columns allowed on PUT (PK comes from URL params, not body). */
const SNAPS_UPDATE_COLUMNS = ["snap", "snap_timing"];

/** True if buffer is already raw JPEG/PNG/GIF/WebP/BMP bytes (not base64 text). */
function hasBinaryImageMagic(buf) {
  if (!buf || buf.length < 3) return false;
  const b0 = buf[0];
  const b1 = buf[1];
  const b2 = buf[2];
  const b3 = buf[3];
  if (b0 === 0xff && b1 === 0xd8 && b2 === 0xff) return true;
  if (b0 === 0x89 && b1 === 0x50 && b2 === 0x4e && b3 === 0x47) return true;
  if (b0 === 0x47 && b1 === 0x49 && b2 === 0x46) return true;
  if (
    buf.length >= 12 &&
    b0 === 0x52 &&
    b1 === 0x49 &&
    b2 === 0x46 &&
    b3 === 0x46 &&
    buf[8] === 0x57 &&
    buf[9] === 0x45 &&
    buf[10] === 0x42 &&
    buf[11] === 0x50
  ) {
    return true;
  }
  if (b0 === 0x42 && b1 === 0x4d) return true;
  return false;
}

/**
 * Client sends `snap` as base64 (see submitSnapObservation). If inserted as a JSON string,
 * MySQL stores UTF-8 of the ASCII base64 — not image bytes — and browsers cannot decode it.
 * Decode to Buffer on write; on read, detect legacy rows and decode for display.
 */
function tryDecodeBase64StringToImageBuffer(str) {
  if (typeof str !== "string" || str.length < 20) return null;
  const cleaned = str.replace(/\s+/g, "").trim();
  if (!/^[A-Za-z0-9+/]+=*$/.test(cleaned)) return null;
  try {
    const out = Buffer.from(cleaned, "base64");
    if (out.length === 0) return null;
    if (hasBinaryImageMagic(out)) return out;
    return null;
  } catch {
    return null;
  }
}

function normalizeSnapForPersist(value) {
  if (value == null) return value;
  if (Buffer.isBuffer(value)) {
    if (hasBinaryImageMagic(value)) return value;
    const decoded = tryDecodeBase64StringToImageBuffer(value.toString("utf8"));
    return decoded || value;
  }
  if (typeof value === "string") {
    let t = value.trim();
    if (t.startsWith("data:")) {
      const i = t.indexOf(",");
      t = i >= 0 ? t.slice(i + 1) : t;
    }
    const decoded = tryDecodeBase64StringToImageBuffer(t);
    if (decoded) return decoded;
    try {
      const b = Buffer.from(t, "base64");
      if (b.length > 0 && hasBinaryImageMagic(b)) return b;
    } catch {
      /* ignore */
    }
  }
  return value;
}

function normalizeSnapFromDb(raw) {
  if (raw == null) return raw;
  const buf = Buffer.isBuffer(raw) ? raw : Buffer.from(raw);
  if (hasBinaryImageMagic(buf)) return buf;
  const decoded = tryDecodeBase64StringToImageBuffer(buf.toString("utf8"));
  return decoded || buf;
}

/** JSON-safe list rows (avoids BigInt / Buffer breaking res.json; normalizes snap_timing). */
function normalizeSnapTimingForJson(raw) {
  if (raw == null) return null;
  if (typeof raw === "object" && !Buffer.isBuffer(raw) && !Array.isArray(raw)) {
    return raw;
  }
  if (Buffer.isBuffer(raw)) {
    try {
      return JSON.parse(raw.toString("utf8"));
    } catch {
      return null;
    }
  }
  if (typeof raw === "string") {
    try {
      return JSON.parse(raw);
    } catch {
      return null;
    }
  }
  return raw;
}

function serializeSnapListRow(r) {
  const sid = Number(r.snap_id);
  const aid = Number(r.assessment_id);
  const hs = Number(r.has_snap);
  return {
    snap_id: Number.isFinite(sid) ? sid : r.snap_id,
    assessment_id: Number.isFinite(aid) ? aid : r.assessment_id,
    snap_timing: normalizeSnapTimingForJson(r.snap_timing),
    has_snap: Number.isFinite(hs) && hs > 0 ? 1 : 0
  };
}

function adminRoleName() {
  return (process.env.JWT_ADMIN_ROLE || "admin").trim().toLowerCase();
}

async function loadAssessmentRowForSnapAccess(assessmentId) {
  const id = Number(assessmentId);
  if (!Number.isInteger(id) || id <= 0) {
    return null;
  }
  const [rows] = await pool.query(
    "SELECT `assessment_id`, `uploader_user_id`, `executor_id`, `verifier_id`, `assessment_status_id` FROM `orac_assessments` WHERE `assessment_id` = ? LIMIT 1",
    [id]
  );
  return rows[0] ?? null;
}

/**
 * GET /api/orac_snaps
 * Lists snap rows **without** the LONGBLOB `snap` column (JSON-safe). Use `has_snap` + `/image` for bytes.
 */
export async function listOracSnaps(req, res, next) {
  try {
    const filters = { ...(req.query || {}) };
    const isAdmin = getNormalizedRole(req.user) === adminRoleName();

    if (!isAdmin) {
      const aid = filters.assessment_id;
      if (aid === undefined || aid === null || aid === "") {
        throw new ApiError(400, "assessment_id query parameter is required");
      }
      const row = await loadAssessmentRowForSnapAccess(aid);
      assertUserCanViewAssessmentSnapsFromRow(req, row);
    }

    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(`\`${column}\` LIKE ?`);
        values.push(`%${value}%`);
      } else {
        whereParts.push(`\`${key}\` = ?`);
        values.push(value);
      }
    });

    let sql = `SELECT \`snap_id\`, \`assessment_id\`, \`snap_timing\`,
      (CASE WHEN \`snap\` IS NOT NULL AND LENGTH(\`snap\`) > 0 THEN 1 ELSE 0 END) AS \`has_snap\`
      FROM \`${TABLE}\``;
    if (whereParts.length) {
      sql += ` WHERE ${whereParts.join(" AND ")}`;
    }
    sql += ` ORDER BY \`assessment_id\` ASC, \`snap_id\` ASC`;

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

/**
 * GET /api/orac_snaps/:snap_id/:assessment_id
 * Single row metadata without `snap` BLOB.
 */
export async function getOracSnapRecord(req, res, next) {
  try {
    const { snap_id, assessment_id } = req.params;
    const row = await loadAssessmentRowForSnapAccess(assessment_id);
    assertUserCanViewAssessmentSnapsFromRow(req, row);
    const [rows] = await pool.query(
      `SELECT \`snap_id\`, \`assessment_id\`, \`snap_timing\`,
        (CASE WHEN \`snap\` IS NOT NULL AND LENGTH(\`snap\`) > 0 THEN 1 ELSE 0 END) AS \`has_snap\`
       FROM \`${TABLE}\` WHERE \`snap_id\` = ? AND \`assessment_id\` = ? LIMIT 1`,
      [snap_id, assessment_id]
    );
    if (!rows.length) {
      throw new ApiError(404, "Snap not found");
    }
    res.json(serializeSnapListRow(rows[0]));
  } catch (err) {
    next(err);
  }
}

async function assertSnapUserIsExecutorForAssessment(req, assessmentId) {
  const id = Number(assessmentId);
  if (!Number.isInteger(id) || id <= 0) {
    throw new ApiError(400, "assessment_id must be a positive integer");
  }
  const [rows] = await pool.query(
    "SELECT `executor_id`, `assessment_status_id` FROM `orac_assessments` WHERE `assessment_id` = ? LIMIT 1",
    [id]
  );
  if (!rows.length) {
    throw new ApiError(404, "Assessment not found");
  }
  const aRow = rows[0];
  assertCurrentUserIsAssessmentExecutor(
    getJwtUserId(req.user),
    aRow?.executor_id,
    req
  );
  assertExecutorNotBlockedByPendingVerification(req, aRow);
}

export async function createOracSnap(req, res, next) {
  try {
    const raw = req.body;
    if (!raw || typeof raw !== "object") {
      throw new ApiError(400, "Body must be JSON object");
    }
    const body = {};
    for (const key of SNAPS_CREATE_COLUMNS) {
      if (Object.prototype.hasOwnProperty.call(raw, key) && raw[key] !== undefined) {
        body[key] = raw[key];
      }
    }
    if (!Object.prototype.hasOwnProperty.call(body, "assessment_id") || body.assessment_id == null || body.assessment_id === "") {
      throw new ApiError(400, "assessment_id is required");
    }
    if (Object.prototype.hasOwnProperty.call(body, "snap")) {
      body.snap = normalizeSnapForPersist(body.snap);
    }
    const columns = SNAPS_CREATE_COLUMNS.filter((c) => Object.prototype.hasOwnProperty.call(body, c));
    const values = columns.map((c) => body[c]);
    if (!columns.length) {
      throw new ApiError(400, "Body cannot be empty");
    }
    await assertSnapUserIsExecutorForAssessment(req, body.assessment_id);
    const placeholders = columns.map(() => "?").join(",");
    const sql = `INSERT INTO \`${TABLE}\` (${columns
      .map((c) => `\`${c}\``)
      .join(",")}) VALUES (${placeholders})`;
    const [result] = await pool.query(sql, values);
    res.status(201).json({
      insertedId: result.insertId ?? null,
      affectedRows: result.affectedRows
    });
  } catch (err) {
    next(err);
  }
}

export async function updateOracSnap(req, res, next) {
  try {
    const raw = req.body;
    if (!raw || typeof raw !== "object") {
      throw new ApiError(400, "Body must be JSON object");
    }
    const body = {};
    for (const key of SNAPS_UPDATE_COLUMNS) {
      if (Object.prototype.hasOwnProperty.call(raw, key) && raw[key] !== undefined) {
        body[key] = raw[key];
      }
    }
    if (Object.prototype.hasOwnProperty.call(body, "snap")) {
      body.snap = normalizeSnapForPersist(body.snap);
    }
    const columns = SNAPS_UPDATE_COLUMNS.filter((c) => Object.prototype.hasOwnProperty.call(body, c));
    const colValues = columns.map((c) => body[c]);
    if (!columns.length) {
      throw new ApiError(400, "Body cannot be empty");
    }
    await assertSnapUserIsExecutorForAssessment(req, req.params.assessment_id);
    const setClause = columns.map((c) => `\`${c}\` = ?`).join(", ");
    const { where, values: whereValues } = buildWhereFromParamsLocal(
      PRIMARY_KEYS,
      req.params
    );
    const sql = `UPDATE \`${TABLE}\` SET ${setClause} WHERE ${where}`;
    const [result] = await pool.query(sql, [...colValues, ...whereValues]);
    res.json({ affectedRows: result.affectedRows });
  } catch (err) {
    next(err);
  }
}

export async function deleteOracSnap(req, res, next) {
  try {
    await assertSnapUserIsExecutorForAssessment(req, req.params.assessment_id);
    const snapId = Number(req.params.snap_id);
    const assessmentId = Number(req.params.assessment_id);
    if (!Number.isInteger(snapId) || snapId <= 0 || !Number.isInteger(assessmentId) || assessmentId <= 0) {
      throw new ApiError(400, "Invalid snap_id or assessment_id");
    }

    const conn = await pool.getConnection();
    try {
      await conn.beginTransaction();

      const [subs] = await conn.query(
        "SELECT `submission_id` FROM `orac_submissions` WHERE `assessment_id` = ? AND `snap_id` = ?",
        [assessmentId, snapId]
      );
    const submissionIds = (subs || []).map((r) => r.submission_id).filter((id) => id != null);
    if (submissionIds.length) {
      const ph = submissionIds.map(() => "?").join(",");
      await conn.query(
        `DELETE FROM \`orac_answers\` WHERE \`submission_id\` IN (${ph})`,
        submissionIds
      );
    }
      await conn.query(
        "DELETE FROM `orac_submissions` WHERE `assessment_id` = ? AND `snap_id` = ?",
        [assessmentId, snapId]
      );

      const [result] = await conn.query(
        "DELETE FROM `orac_snaps` WHERE `snap_id` = ? AND `assessment_id` = ?",
        [snapId, assessmentId]
      );

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

/** First bytes → MIME (snaps may be JPEG from camera or PNG from canvas). */
function sniffImageContentType(buf) {
  if (!buf || buf.length < 4) return 'image/jpeg';
  const b0 = buf[0];
  const b1 = buf[1];
  const b2 = buf[2];
  const b3 = buf[3];
  if (b0 === 0xff && b1 === 0xd8 && b2 === 0xff) return 'image/jpeg';
  if (b0 === 0x89 && b1 === 0x50 && b2 === 0x4e && b3 === 0x47) return 'image/png';
  if (b0 === 0x47 && b1 === 0x49 && b2 === 0x46) return 'image/gif';
  if (
    buf.length >= 12 &&
    b0 === 0x52 &&
    b1 === 0x49 &&
    b2 === 0x46 &&
    b3 === 0x46 &&
    buf[8] === 0x57 &&
    buf[9] === 0x45 &&
    buf[10] === 0x42 &&
    buf[11] === 0x50
  ) {
    return 'image/webp';
  }
  if (b0 === 0x42 && b1 === 0x4d) return 'image/bmp';
  /* `<img>` + blob URLs decode more reliably as jpeg than octet-stream */
  return 'image/jpeg';
}

/**
 * GET /api/orac_snaps/:snap_id/:assessment_id/image
 * Serves the snap LONGBLOB with a detected Content-Type.
 * This avoids JSON Buffer serialisation issues altogether.
 */
export async function serveSnapImage(req, res, next) {
  try {
    const { snap_id, assessment_id } = req.params;
    const aRow = await loadAssessmentRowForSnapAccess(assessment_id);
    assertUserCanViewAssessmentSnapsFromRow(req, aRow);
    const [rows] = await pool.query(
      'SELECT `snap` FROM `orac_snaps` WHERE `snap_id` = ? AND `assessment_id` = ? LIMIT 1',
      [snap_id, assessment_id]
    );
    if (!rows.length || !rows[0].snap) {
      return res.status(404).json({ message: 'Snap image not found' });
    }
    const blob = normalizeSnapFromDb(rows[0].snap); // Buffer from MySQL2; legacy base64-as-text decoded here
    const ct = sniffImageContentType(blob);
    res.set('Content-Type', ct);
    res.set('Cache-Control', 'public, max-age=86400');
    res.send(blob);
  } catch (err) {
    next(err);
  }
}
