import crypto from "node:crypto";
import { query } from "../db/db";
import { LoggerFactory } from "../util/logger";
import { DatabaseError, NotFoundError } from "../types/errortypes";
import { getCachedItem, setCacheItem } from "../api/cache/cache.utils";
import { getRedisClient, isRedisAvailable } from "../clients/redis.client";

const logger = LoggerFactory("ProjectFilesService");

const GET_FILES_QUERY = `SELECT * FROM xyz."fn_GetProjectFileList"($1)`;
const GET_FILE_QUERY = `SELECT * FROM xyz."fn_GetProjectFile"($1, $2)`;
const GET_BIGGEST_FILE_QUERY = `SELECT * FROM xyz."fn_GetBiggestFile"($1)`;
const INSERT_FILE_QUERY = `CALL xyz."usp_InsertProjectFile"($1, $2, $3, $4, $5, $6, $7)`;
const UPDATE_FILE_SIZE_QUERY = `CALL xyz."usp_UpdateProjectFileSizeBytes"($1, $2, $3, $4)`;

const FILE_HASH_CACHE_TTL_SEC = 30 * 60 * 60;
const HASH_SKIPPED_SENTINEL = "HASH_SKIPPED";

function getFileHashCacheKey(fileReferenceId: string): string {
  return `papi:filehash:${fileReferenceId}`;
}

export async function accumulateFileHash(
  fileReferenceId: string,
  chunkHash: string,
  isFirstChunk: boolean,
): Promise<void> {
  const cacheKey = getFileHashCacheKey(fileReferenceId);

  if (isFirstChunk) {
    await setCacheItem(cacheKey, chunkHash, FILE_HASH_CACHE_TTL_SEC);
    return;
  }

  const existing = (await getCachedItem(cacheKey)) as string | undefined;
  if (!existing || existing === HASH_SKIPPED_SENTINEL) {
    // Prior chunk hash was never stored — abandon tracking; a partial hash is worse than none
    await setCacheItem(cacheKey, HASH_SKIPPED_SENTINEL, FILE_HASH_CACHE_TTL_SEC);
    return;
  }

  const combined = crypto.createHash("sha256").update(`${existing}${chunkHash}`).digest("hex");
  await setCacheItem(cacheKey, combined, FILE_HASH_CACHE_TTL_SEC);
}

export async function popFileHash(fileReferenceId: string): Promise<string | null> {
  const cacheKey = getFileHashCacheKey(fileReferenceId);
  const hash = (await getCachedItem(cacheKey)) as string | null | undefined;
  if (hash != null && isRedisAvailable()) {
    try {
      const client = getRedisClient();
      if (client) await client.del(cacheKey);
    } catch {
      // best-effort delete
    }
  }
  if (!hash || hash === HASH_SKIPPED_SENTINEL) return null;
  return hash;
}

export async function getProjectFiles(
  projectId: string,
): Promise<Record<string, unknown>[]> {
  logger.info(`Getting project files for projectId: ${projectId}`);
  try {
    const { rows } = await query(GET_FILES_QUERY, [projectId]);
    return rows;
  } catch (err) {
    if (err instanceof NotFoundError) throw err;
    logger.error(`Error fetching project files for projectId: ${projectId}`, err);
    throw new DatabaseError(
      `Error fetching project files for projectId: ${projectId}`,
    );
  }
}

export async function getProjectFile(
  projectId: string,
  fileReferenceId: string,
): Promise<Record<string, unknown>> {
  logger.info(
    `Getting project file for projectId: ${projectId}, fileReferenceId: ${fileReferenceId}`,
  );
  try {
    const { rows } = await query(GET_FILE_QUERY, [projectId, fileReferenceId]);
    if (rows.length === 0) {
      throw new NotFoundError(
        `File with id '${fileReferenceId}' not found in project '${projectId}'.`,
      );
    }
    return rows[0];
  } catch (err) {
    if (err instanceof NotFoundError) throw err;
    logger.error(
      `Error fetching project file for projectId: ${projectId}`,
      err,
    );
    throw new DatabaseError(
      `Error fetching project file for projectId: ${projectId}`,
    );
  }
}

export async function getBiggestProjectFile(
  projectId: string,
): Promise<Record<string, unknown> | null> {
  logger.info(`Getting biggest project file for projectId: ${projectId}`);
  try {
    const { rows } = await query(GET_BIGGEST_FILE_QUERY, [projectId]);
    if (rows.length === 0) {
      return null;
    }
    return rows[0];
  } catch (err) {
    if (
      err instanceof Error &&
      err.message.includes("No project found for ProjectId")
    ) {
      throw new NotFoundError(
        `Project with ProjectId: ${projectId} not found.`,
      );
    }
    logger.error(
      `Error fetching biggest project file for projectId: ${projectId}`,
      err,
    );
    throw new DatabaseError(
      `Error fetching biggest project file for projectId: ${projectId}`,
    );
  }
}

export async function createProjectFile(
  projectId: string,
  fileReferenceId: string,
  fileName: string,
  fileExtension: string,
  cloudStoragePath: string,
  uploadedBy: string,
  xyzDisplayName: string
): Promise<void> {
  logger.info(
    `Creating project file for projectId: ${projectId}, fileReferenceId: ${fileReferenceId}`,
  );
  try {
    await query(INSERT_FILE_QUERY, [
      projectId,
      fileReferenceId,
      fileName,
      fileExtension,
      cloudStoragePath,
      uploadedBy,
      xyzDisplayName
    ]);
  } catch (err) {
    if (
      err instanceof Error &&
      err.message.includes("No project found for ProjectId")
    ) {
      throw new NotFoundError(
        `Project with ProjectId: ${projectId} not found.`,
      );
    }
    logger.error(
      `Error creating project file for projectId: ${projectId}`,
      err,
    );
    throw new DatabaseError(
      `Error creating project file for projectId: ${projectId}`,
    );
  }
}

export async function updateProjectFileSize(
  projectId: string,
  fileReferenceId: string,
  fileSizeBytes: number,
  fileHash: string | null,
): Promise<void> {
  logger.info(
    `Updating filesize for fileReferenceId: ${fileReferenceId}`,
  );
  try {
    await query(UPDATE_FILE_SIZE_QUERY, [
      projectId,
      fileReferenceId,
      fileSizeBytes,
      fileHash,
    ]);
  } catch (err) {
    if (
      err instanceof Error &&
      err.message.includes("No project found for ProjectId")
    ) {
      throw new NotFoundError(
        `Project with ProjectId: ${projectId} not found.`,
      );
    }
    logger.error(
      `Error updating project file size for projectId: ${projectId}`,
      err,
    );
    throw new DatabaseError(
      `Error updating project file size for projectId: ${projectId}`,
    );
  }
}