CLProject/src/services/reportService.js

import { query } from '../db/pool.js';
import { parseJsonColumn } from '../utils/json.js';

/*
 * The report service handles server-side storage of submitted reports.
 * Images are stored as BLOBs in the report_images table alongside metadata.
 */

export async function submitReport(report) {
  /* Strip image dataUrls from answers before storing in JSON column */
  const answersForJson = stripImagesFromAnswers(report.answers);

  await query(
    `
      INSERT INTO reports (report_uuid, report_number, template_code, template_version, status, answers_json, submitted_at)
      VALUES (?, ?, ?, ?, ?, ?, NOW())
      ON DUPLICATE KEY UPDATE
        status = VALUES(status),
        answers_json = VALUES(answers_json),
        submitted_at = VALUES(submitted_at),
        updated_at = NOW()
    `,
    [
      report.id,
      report.reportNumber,
      report.templateCode,
      report.templateVersion,
      report.status,
      JSON.stringify(answersForJson)
    ]
  );

  /* Store images as BLOBs in DB */
  if (report.answers?.records) {
    await storeReportImages(report.id, report.answers.records);
  }

  return getReport(report.id);
}

export async function getReport(reportUuid) {
  const rows = await query(
    `
      SELECT
        report_uuid AS reportUuid,
        report_number AS reportNumber,
        template_code AS templateCode,
        template_version AS templateVersion,
        status,
        answers_json AS answersJson,
        submitted_at AS submittedAt,
        created_at AS createdAt,
        updated_at AS updatedAt
      FROM reports
      WHERE report_uuid = ?
      LIMIT 1
    `,
    [reportUuid]
  );

  return rows.length ? mapReportRow(rows[0]) : null;
}

export async function listReports({ status, templateCode, limit = 100, offset = 0 } = {}) {
  let sql = `
    SELECT
      report_uuid AS reportUuid,
      report_number AS reportNumber,
      template_code AS templateCode,
      template_version AS templateVersion,
      status,
      answers_json AS answersJson,
      submitted_at AS submittedAt,
      created_at AS createdAt,
      updated_at AS updatedAt
    FROM reports
  `;
  const params = [];
  const clauses = [];

  if (status) {
    clauses.push('status = ?');
    params.push(status);
  }

  if (templateCode) {
    clauses.push('template_code = ?');
    params.push(templateCode);
  }

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

  sql += ' ORDER BY updated_at DESC LIMIT ? OFFSET ?';
  params.push(limit, offset);

  const rows = await query(sql, params);
  return rows.map(mapReportRow);
}

function mapReportRow(row) {
  return {
    id: row.reportUuid,
    reportNumber: row.reportNumber,
    templateCode: row.templateCode,
    templateVersion: row.templateVersion,
    status: row.status,
    answers: parseJsonColumn(row.answersJson, {}),
    submittedAt: row.submittedAt,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt
  };
}

/* ═══════════════════════════════════════════════════════════════════════════
 * Image storage helpers
 * ═══════════════════════════════════════════════════════════════════════════ */

/**
 * Strips dataUrl from image objects in answers so the JSON column stays lean.
 * The full image data is stored separately in report_images.
 */
function stripImagesFromAnswers(answers) {
  if (!answers?.records) return answers;
  const clean = { ...answers, records: {} };
  for (const [recId, rd] of Object.entries(answers.records)) {
    clean.records[recId] = {
      ...rd,
      images: (rd.images || []).map(img => ({
        name: img.name,
        size: img.size,
        width: img.width,
        height: img.height,
        exif: img.exif || null
      }))
    };
  }
  return clean;
}

/**
 * Stores image binary data as BLOBs in the report_images table.
 * Replaces existing images for the report on re-submit.
 */
async function storeReportImages(reportUuid, records) {
  /* Clear existing images for this report to avoid duplicates */
  await query('DELETE FROM report_images WHERE report_uuid = ?', [reportUuid]);

  for (const [recId, rd] of Object.entries(records)) {
    if (!rd.images?.length) continue;
    for (let i = 0; i < rd.images.length; i++) {
      const img = rd.images[i];
      if (!img.dataUrl) continue;

      /* Convert base64 dataUrl to Buffer */
      const matches = img.dataUrl.match(/^data:([^;]+);base64,(.+)$/);
      if (!matches) continue;
      const mimeType = matches[1];
      const buffer = Buffer.from(matches[2], 'base64');

      const fileName = img.name || `image_${i}.jpg`;

      await query(
        `INSERT INTO report_images (report_uuid, record_id, image_index, file_name, file_size, mime_type, width_px, height_px, exif_json, image_data)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          reportUuid,
          recId,
          i,
          fileName,
          img.size || buffer.length,
          mimeType,
          img.width || null,
          img.height || null,
          img.exif ? JSON.stringify(img.exif) : null,
          buffer
        ]
      );
    }
  }
}

/**
 * Retrieves all images for a given report, grouped by record ID.
 * Returns base64 dataUrls constructed from the stored BLOBs.
 */
export async function getReportImages(reportUuid) {
  const rows = await query(
    `SELECT record_id AS recordId, image_index AS imageIndex, file_name AS fileName,
            file_size AS fileSize, mime_type AS mimeType, width_px AS widthPx,
            height_px AS heightPx, exif_json AS exifJson, image_data AS imageData
     FROM report_images
     WHERE report_uuid = ?
     ORDER BY record_id, image_index`,
    [reportUuid]
  );

  const grouped = {};
  for (const row of rows) {
    if (!grouped[row.recordId]) grouped[row.recordId] = [];
    const base64 = row.imageData.toString('base64');
    grouped[row.recordId].push({
      index: row.imageIndex,
      name: row.fileName,
      size: row.fileSize,
      mimeType: row.mimeType,
      width: row.widthPx,
      height: row.heightPx,
      exif: parseJsonColumn(row.exifJson, null),
      dataUrl: `data:${row.mimeType};base64,${base64}`
    });
  }
  return grouped;
}

/**
 * Deletes a report and all its associated images from DB.
 */
export async function deleteReport(reportUuid) {
  /* CASCADE will remove report_images rows automatically */
  await query('DELETE FROM reports WHERE report_uuid = ?', [reportUuid]);
}

/**
 * Deletes a specific image for a record in a report.
 */
export async function deleteReportImage(reportUuid, recordId, fileName) {
  await query(
    'DELETE FROM report_images WHERE report_uuid = ? AND record_id = ? AND file_name = ?',
    [reportUuid, recordId, fileName]
  );
}