import { dataset, location, cluster, cyclicRecordingPattern, file, fileMetadata } from "../../db/schema";

import { dataset, location, cluster, cyclicRecordingPattern, file, fileMetadata, mothMetadata } from "../../db/schema";

    // Fetch metadata for these files if available

    // Fetch both regular metadata and moth metadata for these files if available

    let mothMetadataMap: Record<string, {
      gain: string | null;
      batteryV: number | null;
      tempC: number | null;
    }> = {};

        // Fetch regular file metadata

        // Fetch moth metadata in a separate query
        const mothMetadataResults = await db
          .select({
            fileId: mothMetadata.fileId,
            gain: mothMetadata.gain,
            batteryV: mothMetadata.batteryV,
            tempC: mothMetadata.tempC
          })
          .from(mothMetadata)
          .where(sqlExpr`${mothMetadata.fileId} IN (${sqlExpr.raw(fileIdsQuoted)})`);
          

        
        // Create a map of file ID to moth metadata
        mothMetadataMap = mothMetadataResults.reduce((acc, item) => {
          acc[item.fileId] = {
            gain: item.gain,
            batteryV: item.batteryV !== null ? Number(item.batteryV) : null,
            tempC: item.tempC !== null ? Number(item.tempC) : null
          };
          return acc;
        }, {} as Record<string, {
          gain: string | null;
          batteryV: number | null;
          tempC: number | null;
        }>);

        console.error("Error fetching file metadata:", metadataError);

        console.error("Error fetching metadata:", metadataError);

    // Enrich file data with metadata

    // Enrich file data with both standard metadata and moth metadata

      let mothData = null;

        // Process regular metadata

        
        // Get moth metadata if available
        mothData = mothMetadataMap[fileData.id] || null;

        mothData = null;

        metadata

        metadata,
        mothMetadata: mothData

}
interface MothMetadata {
  gain: string | null;
  batteryV: number | null;
  tempC: number | null;

  mothMetadata?: MothMetadata | null;

  const [hasMothMetadata, setHasMothMetadata] = useState<boolean>(false);

  const formatDateTime = (isoString: string): string => {

  // Keep these formatters commented out for future use
  // const formatDateTime = (isoString: string): string => {
  //   try {
  //     const date = new Date(isoString);
  //     return new Intl.DateTimeFormat('en-GB', {
  //       year: 'numeric',
  //       month: 'short',
  //       day: '2-digit',
  //       hour: '2-digit',
  //       minute: '2-digit',
  //       hour12: false
  //     }).format(date);
  //   } catch {
  //     return isoString;
  //   }
  // };
  
  // const formatSampleRate = (rate: number): string => {
  //   return `${(rate / 1000).toFixed(1)} kHz`;
  // };
  
  const formatMoonPhase = (phase: number | null | undefined): string => {
    if (phase === null || phase === undefined) return "—";

      const date = new Date(isoString);
      return new Intl.DateTimeFormat('en-GB', {
        year: 'numeric',
        month: 'short',
        day: '2-digit',
        hour: '2-digit',
        minute: '2-digit',
        hour12: false
      }).format(date);

      return Number(phase).toFixed(2);

      return isoString;

      return "—";

  const formatSampleRate = (rate: number): string => {
    return `${(rate / 1000).toFixed(1)} kHz`;

  const formatBatteryVoltage = (volts: number | null | undefined): string => {
    if (volts === null || volts === undefined) return "—";
    try {
      return Number(volts).toFixed(1) + 'V';
    } catch {
      return "—";
    }

  const formatMoonPhase = (phase: number | null | undefined): string => {
    if (phase === null || phase === undefined) return "—";

  const formatTemperature = (temp: number | null | undefined): string => {
    if (temp === null || temp === undefined) return "—";

      return Number(phase).toFixed(2);

      return Number(temp).toFixed(1) + '°C';

        
        // Check if any files have moth metadata
        const validMothMetadata = data.data.some(file => 
          file.mothMetadata && 
          (file.mothMetadata.gain !== null || 
           file.mothMetadata.batteryV !== null || 
           file.mothMetadata.tempC !== null)
        );
        

        setHasMothMetadata(validMothMetadata);

                  <TableHead className="py-3 font-bold text-sm uppercase">Timestamp</TableHead>

                  <TableHead className="py-3 font-bold text-sm uppercase">Sample Rate</TableHead>

                  {hasMothMetadata && (
                    <>
                      <TableHead className="py-3 font-bold text-sm uppercase">Gain</TableHead>
                      <TableHead className="py-3 font-bold text-sm uppercase">Battery</TableHead>
                      <TableHead className="py-3 font-bold text-sm uppercase">Temp</TableHead>
                    </>
                  )}

                    <TableCell className="whitespace-normal break-words">{formatDateTime(file.timestampLocal)}</TableCell>

                    <TableCell className="whitespace-normal break-words">{formatSampleRate(file.sampleRate)}</TableCell>

                    {hasMothMetadata && (
                      <>
                        <TableCell className="whitespace-normal break-words">
                          {file.mothMetadata?.gain || "—"}
                        </TableCell>
                        <TableCell className="whitespace-normal break-words">
                          {formatBatteryVoltage(file.mothMetadata?.batteryV)}
                        </TableCell>
                        <TableCell className="whitespace-normal break-words">
                          {formatTemperature(file.mothMetadata?.tempC)}
                        </TableCell>
                      </>
                    )}