vtorio/app/actions/imports.ts

'use server';

import { prisma } from '@/lib/prisma';
import { revalidatePath } from 'next/cache';
import { z } from 'zod';
import { ImportProcessor } from '@/app/lib/excel-import/import-processor';

// Validation schemas
const createImportSchema = z.object({
  name: z.string().min(1, 'Import name is required'),
  layoutId: z.number().int().positive('Layout configuration is required'),
  fileId: z.string().optional(),
});

const updateImportSchema = z.object({
  id: z.number().int().positive(),
  name: z.string().min(1, 'Import name is required'),
  fileId: z.string().optional(),
});

// Create a new import
export async function createImport(data: {
  name: string;
  layoutId: number;
  fileId?: string;
}) {
  try {
    const validatedData = createImportSchema.parse(data);

    const importRecord = await prisma.import.create({
      data: {
        name: validatedData.name,
        layoutId: validatedData.layoutId,
        importDate: new Date(),
        ...(validatedData.fileId && { fileId: validatedData.fileId }),
      },
      include: {
        layout: true,
      },
    });

    revalidatePath('/imports');
    return { success: true, data: importRecord };
  } catch (error) {
    console.error('Error creating import:', error);
    return { success: false, error: 'Failed to create import' };
  }
}

// Get all imports for the current user
export async function getImports(userId?: string, layoutId?: number) {
  try {
    const whereClause: any = {};

    if (userId) {
      whereClause.fileId = {
        not: null,
      };
    }

    if (typeof layoutId === 'number') {
      whereClause.layoutId = layoutId;
    }

    const imports = await prisma.import.findMany({
      where: whereClause,
      include: {
        layout: true,
      },
      orderBy: {
        importDate: 'desc',
      },
    });

    // Filter by userId manually since Prisma doesn't have direct relation
    let filteredImports = imports;
    if (userId) {
      const fileIds = await prisma.file.findMany({
        where: {
          userId: userId,
        },
        select: {
          id: true,
        },
      });

      const userFileIds = new Set(fileIds.map(f => f.id));
      filteredImports = imports.filter(imp =>
        imp.fileId && userFileIds.has(imp.fileId)
      );
    }

    return { success: true, data: filteredImports };
  } catch (error) {
    console.error('Error fetching imports:', error);
    return { success: false, error: 'Failed to fetch imports' };
  }
}

// Get a single import by ID
export async function getImportById(id: number) {
  try {
    const importRecord = await prisma.import.findUnique({
      where: { id },
      include: {
        layout: {
          include: {
            sections: {
              include: {
                fields: true,
              },
            },
          },
        },
      },
    });

    if (!importRecord) {
      return { success: false, error: 'Import not found' };
    }

    // Fetch file separately if fileId exists
    let file = undefined;
    if (importRecord.fileId) {
      file = await prisma.file.findUnique({
        where: { id: importRecord.fileId },
      });
    }

    return { success: true, data: { ...importRecord, file } };
  } catch (error) {
    console.error('Error fetching import:', error);
    return { success: false, error: 'Failed to fetch import' };
  }
}

// Update an import
export async function updateImport(data: {
  id: number;
  name: string;
  fileId?: string;
}) {
  try {
    const validatedData = updateImportSchema.parse(data);

    const importRecord = await prisma.import.update({
      where: { id: validatedData.id },
      data: {
        name: validatedData.name,
        ...(validatedData.fileId !== undefined && { fileId: validatedData.fileId }),
      },
      include: {
        layout: true,
      },
    });

    revalidatePath('/imports');
    return { success: true, data: importRecord };
  } catch (error) {
    console.error('Error updating import:', error);
    return { success: false, error: 'Failed to update import' };
  }
}

// Delete an import
export async function deleteImport(id: number) {
  try {
    await prisma.import.delete({
      where: { id },
    });

    revalidatePath('/imports');
    return { success: true };
  } catch (error) {
    console.error('Error deleting import:', error);
    return { success: false, error: 'Failed to delete import' };
  }
}

// Calculate Cintas summaries for an import
export async function calculateCintasSummaries(importId: number) {
  try {
    // This would typically call a stored procedure or perform calculations
    // For now, we'll simulate the calculation

    // In a real implementation, you might call:
    // await prisma.$executeRaw`CALL cintas_calculate_summary(${importId})`;

    // For demo purposes, we'll create some sample data
    const summaries = [
      {
        importId,
        week: '2024-W01',
        trrTotal: 100,
        fourWkAverages: 95,
        trrPlus4Wk: 195,
        powerAdds: 25,
        weekId: 1,
      },
      {
        importId,
        week: '2024-W02',
        trrTotal: 110,
        fourWkAverages: 100,
        trrPlus4Wk: 210,
        powerAdds: 30,
        weekId: 2,
      },
    ];

    // Clear existing summaries for this import
    await prisma.cintasSummary.deleteMany({
      where: { importId },
    });

    // Create new summaries
    const createdSummaries = await Promise.all(
      summaries.map(summary =>
        prisma.cintasSummary.create({
          data: summary,
        })
      )
    );

    return { success: true, data: createdSummaries };
  } catch (error) {
    console.error('Error calculating Cintas summaries:', error);
    return { success: false, error: 'Failed to calculate summaries' };
  }
}

// Get available layout configurations
export async function getLayoutConfigurations() {
  try {
    const layouts = await prisma.layoutConfiguration.findMany({
      include: {
        sections: {
          include: {
            fields: true,
          },
        },
      },
      orderBy: {
        name: 'asc',
      },
    });

    return { success: true, data: layouts };
  } catch (error) {
    console.error('Error fetching layout configurations:', error);
    return { success: false, error: 'Failed to fetch layout configurations' };
  }
}

// Get import summary (GET /api/imports/[id]/summary)
export async function getImportSummary(importId: number) {
  try {
    if (!importId || isNaN(importId)) {
      return { success: false, error: 'Invalid import ID' };
    }

    // Check if import exists
    const importRecord = await prisma.import.findUnique({
      where: { id: importId }
    });

    if (!importRecord) {
      return { success: false, error: 'Import not found' };
    }

    // Get basic summary data
    const totalRecords = await prisma.cintasInstallCalendar.count({
      where: { importId }
    });

    const cintasSummaries = await prisma.cintasSummary.findMany({
      where: { importId },
      orderBy: { weekId: 'desc' }
    });

    // Get file info
    const file = importRecord.fileId ? await prisma.file.findUnique({
      where: { id: importRecord.fileId }
    }) : null;

    const summary = {
      totalRecords,
      totalWeeks: cintasSummaries.length,
      cintasSummaries: cintasSummaries.map((summary: any) => ({
        id: summary.id,
        week: summary.week,
        trrTotal: summary.trrTotal,
        fourWkAverages: summary.fourWkAverages,
        trrPlus4Wk: summary.trrPlus4Wk,
        powerAdds: summary.powerAdds,
        weekId: summary.weekId
      }))
    };

    return {
      success: true,
      data: {
        importId,
        fileName: file?.filename || 'Unknown',
        uploadDate: importRecord.createdAt,
        summary,
        summaryExists: cintasSummaries.length > 0
      }
    };

  } catch (error) {
    console.error('Error fetching import summary:', error);
    return { success: false, error: 'Failed to fetch import summary' };
  }
}

// Generate import summary (POST /api/imports/[id]/summary)
export async function generateImportSummary(importId: number) {
  try {
    if (!importId || isNaN(importId)) {
      return { success: false, error: 'Invalid import ID' };
    }

    // Check if import exists
    const importRecord = await prisma.import.findUnique({
      where: { id: importId }
    });

    if (!importRecord) {
      return { success: false, error: 'Import not found' };
    }

    // Check if summary already exists
    const existingSummaries = await prisma.cintasSummary.count({
      where: { importId }
    });

    if (existingSummaries > 0) {
      // Return existing summary
      const cintasSummaries = await prisma.cintasSummary.findMany({
        where: { importId },
        orderBy: { weekId: 'desc' }
      });

      return {
        success: true,
        data: {
          importId,
          summaryGenerated: false,
          message: 'Summary already exists',
          summary: cintasSummaries.map((summary: any) => ({
            id: summary.id,
            week: summary.week,
            trrTotal: summary.trrTotal,
            fourWkAverages: summary.fourWkAverages,
            trrPlus4Wk: summary.trrPlus4Wk,
            powerAdds: summary.powerAdds,
            weekId: summary.weekId
          }))
        }
      };
    }

    // Remove accidental header rows that can break stored procedure casts
    await prisma.cintasInstallCalendar.deleteMany({
      where: {
        importId,
        OR: [
          { installDate: { equals: 'Date of Install', mode: 'insensitive' } },
          { opportunityStatus: { equals: 'Opportunity Status', mode: 'insensitive' } },
          { trr: { equals: 'TRR', mode: 'insensitive' } },
        ]
      }
    });

    // Generate new summary using stored procedure
    await prisma.$executeRaw`CALL cintas_calculate_summary(${importId})`;

    // Fetch the newly generated summary
    const cintasSummaries = await prisma.cintasSummary.findMany({
      where: { importId },
      orderBy: { weekId: 'desc' }
    });

    return {
      success: true,
      data: {
        importId,
        summaryGenerated: true,
        message: 'Summary generated successfully',
        summary: cintasSummaries.map((summary: any) => ({
          id: summary.id,
          week: summary.week,
          trrTotal: summary.trrTotal,
          fourWkAverages: summary.fourWkAverages,
          trrPlus4Wk: summary.trrPlus4Wk,
          powerAdds: summary.powerAdds,
          weekId: summary.weekId
        }))
      }
    };

  } catch (error) {
    console.error('Error generating summary:', error);
    return { success: false, error: 'Failed to generate summary' };
  }
}

// Get import progress (GET /api/imports/[id]/progress)
export async function getImportProgress(importId: number) {
  try {
    if (!importId || isNaN(importId)) {
      return { success: false, error: 'Invalid import ID' };
    }

    // Check if import exists
    const importRecord = await prisma.import.findUnique({
      where: { id: importId }
    });

    if (!importRecord) {
      return { success: false, error: 'Import not found' };
    }

    // Check all possible tables for records
    const [cintasCount, gowDataCount, gowFacCount, gowCorpCount] = await Promise.all([
      prisma.cintasInstallCalendar.count({ where: { importId } }),
      prisma.gowData.count({ where: { importId } }),
      prisma.gowFacId.count({ where: { importId } }),
      prisma.gowCorpRef.count({ where: { importId } }),
    ]);

    const totalRecords = cintasCount + gowDataCount + gowFacCount + gowCorpCount;
    // Since we don't have status fields, we'll use record count as proxy
    const hasRecords = totalRecords > 0;
    return {
      success: true,
      data: {
        importId,
        status: hasRecords ? 'completed' : 'pending',
        progress: hasRecords ? 100 : 0,
        processedRecords: totalRecords,
        totalRecords: totalRecords,
        errorMessage: null,
        lastUpdated: importRecord.updatedAt,
        timestamp: new Date().toISOString()
      }
    };

  } catch (error) {
    console.error('Error fetching import progress:', error);
    return { success: false, error: 'Failed to fetch import progress' };
  }
}

// Trigger import process (POST /api/imports/[id]/trigger)
export async function triggerImportProcess(importId: number) {
  try {
    if (!importId || isNaN(importId)) {
      console.error('[TRIGGER_IMPORT] ❌ Invalid import ID:', importId);
      return { success: false, error: 'Invalid import ID' };
    }

    console.log('[TRIGGER_IMPORT] 📥 Starting triggerImportProcess for ID:', importId);

    // Validate import exists
    const importRecord = await prisma.import.findUnique({
      where: { id: importId }
    });

    console.log('[TRIGGER_IMPORT] 📄 Import record found:', { id: importRecord?.id, hasFileId: !!importRecord?.fileId, layoutId: importRecord?.layoutId });

    if (!importRecord) {
      console.error('[TRIGGER_IMPORT] ❌ Import not found for ID:', importId);
      return { success: false, error: 'Import not found' };
    }

    if (!importRecord.fileId) {
      console.error('[TRIGGER_IMPORT] ❌ No file attached to import:', importId);
      return { success: false, error: 'No file attached to import' };
    }

    // Check if layout exists
    const layout = await prisma.layoutConfiguration.findUnique({
      where: { id: importRecord.layoutId }
    });

    console.log('[TRIGGER_IMPORT] 📋 Layout found:', { id: layout?.id, name: layout?.name });

    if (!layout) {
      console.error('[TRIGGER_IMPORT] ❌ No layout configuration found for ID:', importRecord.layoutId);
      return { success: false, error: 'No layout configuration found' };
    }

    // Check if data already exists for this import
    const existingRecords = await prisma.cintasInstallCalendar.count({
      where: { importId }
    });

    console.log('[TRIGGER_IMPORT] 📊 Existing records count:', existingRecords);

    if (existingRecords > 0) {
      console.log('[TRIGGER_IMPORT] ✅ Import already processed, returning early');
      return {
        success: true,
        message: 'Import already processed',
        importId,
        existingRecords
      };
    }

    // Use the ImportProcessor to actually process the import
    console.log('[TRIGGER_IMPORT] 🔄 Starting ImportProcessor for import ID:', importId);
    const processor = new ImportProcessor();
    const result = await processor.processImport(importId);

    console.log('[TRIGGER_IMPORT] 📈 ImportProcessor result:', { success: result.success, totalInserted: result.totalInserted, sectionsCount: result.sections?.length, errors: result.errors });

    if (result.success) {
      console.log('[TRIGGER_IMPORT] ✅ Import completed successfully');
      revalidatePath('/imports');
      return {
        success: true,
        message: 'Import process completed successfully',
        importId,
        totalInserted: result.totalInserted
      };
    } else {
      console.error('[TRIGGER_IMPORT] ❌ Import processing failed:', result.errors);
      return {
        success: false,
        error: 'Import processing failed',
        errors: result.errors
      };
    }

  } catch (error) {
    console.error('[TRIGGER_IMPORT] ❌ Error triggering import:', error);
    return { success: false, error: 'Failed to trigger import' };
  }
}

// Update import progress (for internal use by ImportProcessor)
export async function updateImportProgress(
  importId: number,
  progress: {
    processedRecords: number;
    totalRecords: number;
    status: string;
    errorMessage?: string;
  }
) {
  try {
    // Since the Import model doesn't have these fields, we'll just return success
    // In a real implementation, you would need to add these fields to the schema
    console.log(`Import ${importId} progress: ${progress.processedRecords}/${progress.totalRecords} (${progress.status})`);

    return { success: true };
  } catch (error) {
    console.error('Error updating import progress:', error);
    return { success: false, error: 'Failed to update progress' };
  }
}

// Get imports filtered by layout configuration name
export async function getImportsByLayoutName(layoutName: string) {
  try {
    // First find the layout by name
    const layout = await prisma.layoutConfiguration.findFirst({
      where: { name: layoutName },
    });

    if (!layout) {
      return { success: true, data: [] };
    }

    const imports = await prisma.import.findMany({
      where: {
        layoutId: layout.id,
      },
      include: {
        layout: true,
      },
      orderBy: {
        importDate: 'desc',
      },
    });

    return { success: true, data: imports };
  } catch (error) {
    console.error('Error fetching imports by layout:', error);
    return { success: false, error: 'Failed to fetch imports' };
  }
}

// TerraTech facility summary result type
export interface TerraTechSummaryRow {
  wellName: string | null;
  corpId: string | null;
  facilityId: string | null;
  gas: number | null;
  oil: number | null;
  water: number | null;
  state: string | null;
  county: string | null;
  daysQ1: number | null;
  daysQ2: number | null;
  daysQ3: number | null;
  daysQ4: number | null;
  daysQ1Pct: number | null;
  daysQ2Pct: number | null;
  daysQ3Pct: number | null;
  daysQ4Pct: number | null;
  gasCorr: number | null;
  oilCorr: number | null;
  waterCorr: number | null;
  isMissingFacilityId: string | null;
  isMissingCounty: string | null;
}

// Get TerraTech facility summary for an import
export async function getTerraTechFacilitySummary(importId: number) {
  try {
    if (!importId || isNaN(importId)) {
      return { success: false, error: 'Invalid import ID' };
    }

    // Check if import exists
    const importRecord = await prisma.import.findUnique({
      where: { id: importId },
      include: { layout: true }
    });

    if (!importRecord) {
      return { success: false, error: 'Import not found' };
    }

    // Execute the complex aggregation query using raw SQL
    const results = await prisma.$queryRaw<TerraTechSummaryRow[]>`
      SELECT
        s.well_name AS "wellName",
        s."corpId" AS "corpId",
        gfi.odeq_fac_id AS "facilityId",
        CAST(SUM(CAST(s.gas_production AS DECIMAL)) AS DECIMAL(10,2)) AS "gas",
        CAST(SUM(CAST(s.oil_production AS DECIMAL)) AS DECIMAL(10,2)) AS "oil",
        CAST(SUM(CAST(s.water_production AS DECIMAL)) AS DECIMAL(10,2)) AS "water",
        s.state AS "state",
        cr.county_parish AS "county",
        SUM(CASE WHEN q."Quarter" = 1 THEN q."DaysInMonth" ELSE 0 END) AS "daysQ1",
        SUM(CASE WHEN q."Quarter" = 2 THEN q."DaysInMonth" ELSE 0 END) AS "daysQ2",
        SUM(CASE WHEN q."Quarter" = 3 THEN q."DaysInMonth" ELSE 0 END) AS "daysQ3",
        SUM(CASE WHEN q."Quarter" = 4 THEN q."DaysInMonth" ELSE 0 END) AS "daysQ4",
        CAST(
          (SUM(CASE WHEN q."Quarter" = 1 THEN q."DaysInMonth" ELSE 0 END)::DECIMAL / 
           CASE WHEN (EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 4 = 0 
                      AND (EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 100 != 0 
                           OR EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 400 = 0)) 
                THEN 366 ELSE 365 END * 100) 
          AS DECIMAL(5,1)
        ) AS "daysQ1Pct",
        CAST(
          (SUM(CASE WHEN q."Quarter" = 2 THEN q."DaysInMonth" ELSE 0 END)::DECIMAL / 
           CASE WHEN (EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 4 = 0 
                      AND (EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 100 != 0 
                           OR EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 400 = 0)) 
                THEN 366 ELSE 365 END * 100) 
          AS DECIMAL(5,1)
        ) AS "daysQ2Pct",
        CAST(
          (SUM(CASE WHEN q."Quarter" = 3 THEN q."DaysInMonth" ELSE 0 END)::DECIMAL / 
           CASE WHEN (EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 4 = 0 
                      AND (EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 100 != 0 
                           OR EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 400 = 0)) 
                THEN 366 ELSE 365 END * 100) 
          AS DECIMAL(5,1)
        ) AS "daysQ3Pct",
        CAST(
          (SUM(CASE WHEN q."Quarter" = 4 THEN q."DaysInMonth" ELSE 0 END)::DECIMAL / 
           CASE WHEN (EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 4 = 0 
                      AND (EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 100 != 0 
                           OR EXTRACT(YEAR FROM NOW() AT TIME ZONE 'America/Detroit')::INT % 400 = 0)) 
                THEN 366 ELSE 365 END * 100) 
          AS DECIMAL(5,1)
        ) AS "daysQ4Pct",
        CAST(CASE WHEN SUM(CAST(s.gas_production AS DECIMAL)) < 0 THEN 0 ELSE SUM(CAST(s.gas_production AS DECIMAL)) END AS DECIMAL(10,2)) AS "gasCorr",
        CAST(CASE WHEN SUM(CAST(s.oil_production AS DECIMAL)) < 0 THEN 0 ELSE SUM(CAST(s.oil_production AS DECIMAL)) END AS DECIMAL(10,2)) AS "oilCorr",
        CAST(CASE WHEN SUM(CAST(s.water_production AS DECIMAL)) < 0 THEN 0 ELSE SUM(CAST(s.water_production AS DECIMAL)) END AS DECIMAL(10,2)) AS "waterCorr",
        CASE WHEN gfi.odeq_fac_id IS NULL THEN 'Yes' ELSE NULL END AS "isMissingFacilityId",
        CASE WHEN cr.county_parish IS NULL THEN 'Yes' ELSE NULL END AS "isMissingCounty"
      FROM gow_data s
      INNER JOIN LATERAL (
        SELECT
          DATE_PART('quarter', s.month::DATE) AS "Quarter",
          DATE_PART('days',
            DATE_TRUNC('month', s.month::DATE)
            + '1 MONTH'::INTERVAL
            - '1 DAY'::INTERVAL
          ) AS "DaysInMonth"
      ) q ON TRUE
      LEFT JOIN gow_fac_id gfi ON s."corpId" = gfi.corp_id AND gfi."importId" = s."importId"
      LEFT JOIN gow_corp_ref cr ON cr.corporate_id = s."corpId" AND cr."importId" = s."importId"
      WHERE s."importId" = ${importId}
      GROUP BY s.well_name, s."corpId", gfi.odeq_fac_id, s.state, cr.county_parish
      ORDER BY s.well_name
    `;

    // Serialize Decimal objects to plain numbers for client components
    const serializedRows = results.map(row => ({
      wellName: row.wellName,
      corpId: row.corpId,
      facilityId: row.facilityId,
      gas: row.gas !== null ? Number(row.gas) : null,
      oil: row.oil !== null ? Number(row.oil) : null,
      water: row.water !== null ? Number(row.water) : null,
      state: row.state,
      county: row.county,
      daysQ1: row.daysQ1 !== null ? Number(row.daysQ1) : null,
      daysQ2: row.daysQ2 !== null ? Number(row.daysQ2) : null,
      daysQ3: row.daysQ3 !== null ? Number(row.daysQ3) : null,
      daysQ4: row.daysQ4 !== null ? Number(row.daysQ4) : null,
      daysQ1Pct: row.daysQ1Pct !== null ? Number(row.daysQ1Pct) : null,
      daysQ2Pct: row.daysQ2Pct !== null ? Number(row.daysQ2Pct) : null,
      daysQ3Pct: row.daysQ3Pct !== null ? Number(row.daysQ3Pct) : null,
      daysQ4Pct: row.daysQ4Pct !== null ? Number(row.daysQ4Pct) : null,
      gasCorr: row.gasCorr !== null ? Number(row.gasCorr) : null,
      oilCorr: row.oilCorr !== null ? Number(row.oilCorr) : null,
      waterCorr: row.waterCorr !== null ? Number(row.waterCorr) : null,
      isMissingFacilityId: row.isMissingFacilityId,
      isMissingCounty: row.isMissingCounty,
    }));

    return {
      success: true,
      data: {
        importId,
        importName: importRecord.name,
        layoutName: importRecord.layout.name,
        rows: serializedRows
      }
    };
  } catch (error) {
    console.error('Error fetching TerraTech facility summary:', error);
    return { success: false, error: 'Failed to fetch facility summary' };
  }
}