const { db } = require("../shared");

/**
 * Save image metadata to the database.
 * Uses UPSERT pattern to handle re-uploads.
 *
 * @param {string} s3Key - Final S3 key (without staging/ prefix)
 * @param {Object} metadata - Extracted metadata
 * @returns {Promise<string>} The inserted/updated record ID
 */
async function saveImageMetadata(s3Key, metadata) {
  const query = `
    INSERT INTO "ImageMetadata" (
      id, "s3Key", latitude, longitude,
      "cameraMake", "cameraModel", "cameraSoftware",
      "dateTaken", width, height, orientation, "fileSize",
      "processingStatus", "createdAt", "updatedAt"
    ) VALUES (
      gen_random_uuid(), $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, NOW(), NOW()
    )
    ON CONFLICT ("s3Key") DO UPDATE SET
      latitude = EXCLUDED.latitude,
      longitude = EXCLUDED.longitude,
      "cameraMake" = EXCLUDED."cameraMake",
      "cameraModel" = EXCLUDED."cameraModel",
      "cameraSoftware" = EXCLUDED."cameraSoftware",
      "dateTaken" = EXCLUDED."dateTaken",
      width = EXCLUDED.width,
      height = EXCLUDED.height,
      orientation = EXCLUDED.orientation,
      "fileSize" = EXCLUDED."fileSize",
      "processingStatus" = EXCLUDED."processingStatus",
      "updatedAt" = NOW()
    RETURNING id
  `;

  const values = [
    s3Key,
    metadata.latitude,
    metadata.longitude,
    metadata.cameraMake,
    metadata.cameraModel,
    metadata.cameraSoftware,
    metadata.dateTaken,
    metadata.width,
    metadata.height,
    metadata.orientation,
    metadata.fileSize,
    "processing",
  ];

  const result = await db.query(query, values);
  return result.rows[0].id;
}

/**
 * Update processing status after completion.
 *
 * @param {string} s3Key - S3 key
 * @param {string} status - 'completed' or 'failed'
 * @param {string} errorMessage - Error message if failed
 */
async function updateProcessingStatus(s3Key, status, errorMessage = null) {
  const query = `
    UPDATE "ImageMetadata"
    SET
      "processingStatus" = $2::"enum_ImageMetadata_processingStatus",
      "processedAt" = CASE WHEN $2 = 'completed' THEN NOW() ELSE "processedAt" END,
      "errorMessage" = $3,
      "updatedAt" = NOW()
    WHERE "s3Key" = $1
  `;

  await db.query(query, [s3Key, status, errorMessage]);
}

module.exports = {
  saveImageMetadata,
  updateProcessingStatus,
};