// Report Generator Service
// Handles data fetching, transformation, chart generation, and output formatting

import { prisma } from '@/lib/db';
import type { ReportColumn, ChartConfig } from './report-definitions';

export interface ReportData {
  rows: Record<string, unknown>[];
  totals?: Record<string, number>;
  chartData?: ChartDataPoint[];
  metadata: {
    generatedAt: Date;
    rowCount: number;
    executionTime: number;
    filters: Record<string, unknown>;
  };
}

export interface ChartDataPoint {
  name: string;
  [key: string]: unknown;
}

export interface GenerateReportOptions {
  reportId: string;
  filters?: Record<string, unknown>;
  sortBy?: string;
  sortOrder?: 'asc' | 'desc';
  page?: number;
  pageSize?: number;
  format?: 'json' | 'html' | 'pdf' | 'csv' | 'excel';
}

// Currency formatter
const currencyFormatter = new Intl.NumberFormat('en-US', {
  style: 'currency',
  currency: 'USD',
});

// Percentage formatter
const percentFormatter = new Intl.NumberFormat('en-US', {
  style: 'percent',
  minimumFractionDigits: 1,
  maximumFractionDigits: 1,
});

// Date formatter
const dateFormatter = new Intl.DateTimeFormat('en-US', {
  year: 'numeric',
  month: 'short',
  day: 'numeric',
});

// Format cell value based on column type
export function formatCellValue(value: unknown, columnType: ReportColumn['type']): string {
  if (value === null || value === undefined) return '-';
  
  switch (columnType) {
    case 'currency':
      return currencyFormatter.format(Number(value));
    case 'percentage':
      return percentFormatter.format(Number(value) / 100);
    case 'date':
      return value instanceof Date ? dateFormatter.format(value) : dateFormatter.format(new Date(String(value)));
    case 'number':
      return Number(value).toLocaleString();
    case 'boolean':
      return value ? 'Yes' : 'No';
    case 'status':
      return String(value).replace(/_/g, ' ').replace(/\b\w/g, c => c.toUpperCase());
    default:
      return String(value);
  }
}

// Calculate aging bucket for a date
export function calculateAgingBucket(dueDate: Date, referenceDate: Date = new Date()): string {
  const diffDays = Math.floor((referenceDate.getTime() - dueDate.getTime()) / (1000 * 60 * 60 * 24));
  
  if (diffDays <= 0) return 'current';
  if (diffDays <= 30) return 'days1_30';
  if (diffDays <= 60) return 'days31_60';
  if (diffDays <= 90) return 'days61_90';
  return 'over90';
}

// Generate report data
export async function generateReport(options: GenerateReportOptions): Promise<ReportData> {
  const startTime = Date.now();
  
  // Fetch report definition
  const report = await prisma.reportDefinition.findUnique({
    where: { id: options.reportId },
  });
  
  if (!report) {
    throw new Error(`Report not found: ${options.reportId}`);
  }
  
  const filters = { ...(report.defaultFilters as Record<string, unknown> || {}), ...options.filters };
  const columns = (report.columns as unknown) as ReportColumn[];
  
  // Fetch data based on data source
  let rows: Record<string, unknown>[] = [];
  
  try {
    rows = await fetchReportData(report.dataSource, report.queryConfig as Record<string, unknown>, filters);
  } catch (error) {
    console.error('Error fetching report data:', error);
    rows = [];
  }
  
  // Apply sorting
  if (options.sortBy) {
    rows.sort((a, b) => {
      const aVal = String(a[options.sortBy as string] ?? '');
      const bVal = String(b[options.sortBy as string] ?? '');
      const comparison = aVal < bVal ? -1 : aVal > bVal ? 1 : 0;
      return options.sortOrder === 'desc' ? -comparison : comparison;
    });
  }
  
  // Calculate totals for aggregatable columns
  const totals: Record<string, number> = {};
  for (const column of columns) {
    if (column.aggregation) {
      totals[column.key] = calculateAggregation(rows, column.key, column.aggregation);
    }
  }
  
  // Generate chart data if applicable
  let chartData: ChartDataPoint[] | undefined;
  if (report.chartConfig) {
    chartData = generateChartData(rows, (report.chartConfig as unknown) as ChartConfig);
  }
  
  // Apply pagination
  const totalRows = rows.length;
  if (options.page !== undefined && options.pageSize !== undefined) {
    const start = (options.page - 1) * options.pageSize;
    rows = rows.slice(start, start + options.pageSize);
  }
  
  return {
    rows,
    totals,
    chartData,
    metadata: {
      generatedAt: new Date(),
      rowCount: totalRows,
      executionTime: Date.now() - startTime,
      filters,
    },
  };
}

// Fetch data from different sources
async function fetchReportData(
  dataSource: string,
  queryConfig: Record<string, unknown>,
  filters: Record<string, unknown>
): Promise<Record<string, unknown>[]> {
  // Handle custom data sources
  if (dataSource.startsWith('custom:')) {
    return fetchCustomReportData(dataSource.replace('custom:', ''), filters);
  }
  
  // Handle Prisma model data sources
  const modelName = dataSource.toLowerCase();
  const model = (prisma as unknown as Record<string, { findMany: (args: Record<string, unknown>) => Promise<Record<string, unknown>[]> }>)[modelName];
  
  if (!model) {
    console.warn(`Model not found: ${modelName}`);
    return [];
  }
  
  // Build query
  const query: Record<string, unknown> = {};
  
  if (queryConfig.include) {
    query.include = queryConfig.include;
  }
  
  if (queryConfig.where) {
    query.where = { ...queryConfig.where as Record<string, unknown> };
  }
  
  if (queryConfig.orderBy) {
    query.orderBy = queryConfig.orderBy;
  }
  
  if (queryConfig.take) {
    query.take = queryConfig.take;
  }
  
  // Apply filters
  if (filters.dateRange && typeof filters.dateRange === 'object') {
    const dateRange = filters.dateRange as { start?: string; end?: string };
    if (!query.where) query.where = {};
    (query.where as Record<string, unknown>).createdAt = {
      gte: dateRange.start ? new Date(dateRange.start) : undefined,
      lte: dateRange.end ? new Date(dateRange.end) : undefined,
    };
  }
  
  try {
    return await model.findMany(query);
  } catch (error) {
    console.error(`Error querying ${modelName}:`, error);
    return [];
  }
}

// Fetch custom report data
async function fetchCustomReportData(
  reportType: string,
  filters: Record<string, unknown>
): Promise<Record<string, unknown>[]> {
  switch (reportType) {
    case 'executive_kpis':
      return fetchExecutiveKPIs(filters);
    case 'financial_summary':
      return fetchFinancialSummary(filters);
    case 'ops_health':
      return fetchOpsHealth(filters);
    case 'anomalies':
      return fetchAnomalies(filters);
    case 'data_retention':
      return fetchDataRetention();
    default:
      return [];
  }
}

// Executive KPIs
async function fetchExecutiveKPIs(filters: Record<string, unknown>): Promise<Record<string, unknown>[]> {
  // Aggregate key metrics
  const [taskCount, agentCount, workflowCount] = await Promise.all([
    prisma.task.count({ where: { status: 'completed' } }),
    prisma.agent.count({ where: { status: 'active' } }),
    prisma.workflow.count({ where: { status: 'active' } }),
  ]);
  
  return [
    { metric: 'Tasks Completed', current: taskCount, prior: 0, target: 1000, variance: 0 },
    { metric: 'Active Agents', current: agentCount, prior: 0, target: 10, variance: 0 },
    { metric: 'Active Workflows', current: workflowCount, prior: 0, target: 20, variance: 0 },
  ];
}

// Financial Summary
async function fetchFinancialSummary(filters: Record<string, unknown>): Promise<Record<string, unknown>[]> {
  return [
    { category: 'Revenue', actual: 0, comparison: 0, variance: 0, variancePercent: 0 },
    { category: 'Expenses', actual: 0, comparison: 0, variance: 0, variancePercent: 0 },
    { category: 'Net Income', actual: 0, comparison: 0, variance: 0, variancePercent: 0 },
  ];
}

// Ops Health
async function fetchOpsHealth(filters: Record<string, unknown>): Promise<Record<string, unknown>[]> {
  const healthMetrics = await prisma.healthMetric.findMany({
    orderBy: { timestamp: 'desc' },
    take: 10,
  });
  
  return healthMetrics.map((m: any) => ({
    metric: m.component,
    value: m.status,
    trend: m.missedBeats === 0 ? 'stable' : 'declining',
    status: m.status,
  }));
}

// Anomalies
async function fetchAnomalies(filters: Record<string, unknown>): Promise<Record<string, unknown>[]> {
  const events = await prisma.securityEvent.findMany({
    orderBy: { timestamp: 'desc' },
    take: 50,
  });
  
  return events.map((e: any) => ({
    detectedAt: e.timestamp,
    category: e.type,
    description: e.description,
    severity: e.severity,
    status: e.resolvedAt ? 'resolved' : 'open',
    resolvedBy: e.resolvedBy,
  }));
}

// Data Retention
async function fetchDataRetention(): Promise<Record<string, unknown>[]> {
  // Placeholder - would analyze data retention policies
  return [
    { dataType: 'Audit Logs', retentionPolicy: '7 years', recordCount: 0, oldestRecord: null, nextCleanup: null, status: 'compliant' },
    { dataType: 'Session Data', retentionPolicy: '30 days', recordCount: 0, oldestRecord: null, nextCleanup: null, status: 'compliant' },
  ];
}

// Calculate aggregation
function calculateAggregation(
  rows: Record<string, unknown>[],
  key: string,
  type: 'sum' | 'avg' | 'count' | 'min' | 'max'
): number {
  const values = rows.map(r => Number(r[key]) || 0);
  
  switch (type) {
    case 'sum':
      return values.reduce((a, b) => a + b, 0);
    case 'avg':
      return values.length ? values.reduce((a, b) => a + b, 0) / values.length : 0;
    case 'count':
      return rows.length;
    case 'min':
      return Math.min(...values);
    case 'max':
      return Math.max(...values);
    default:
      return 0;
  }
}

// Generate chart data
function generateChartData(rows: Record<string, unknown>[], config: ChartConfig): ChartDataPoint[] {
  if (!config.xAxis) return [];
  
  // Group by xAxis if needed
  const grouped = new Map<string, Record<string, unknown>>();
  
  for (const row of rows) {
    const key = String(row[config.xAxis] || 'Other');
    if (!grouped.has(key)) {
      grouped.set(key, { name: key });
    }
    
    const entry = grouped.get(key)!;
    
    // Aggregate yAxis values
    if (config.yAxis) {
      const yKeys = Array.isArray(config.yAxis) ? config.yAxis : [config.yAxis];
      for (const yKey of yKeys) {
        entry[yKey] = (Number(entry[yKey]) || 0) + (Number(row[yKey]) || 0);
      }
    }
  }
  
  return Array.from(grouped.values()) as ChartDataPoint[];
}

// Generate HTML report
export function generateHTMLReport(
  reportName: string,
  columns: ReportColumn[],
  data: ReportData
): string {
  const tableRows = data.rows.map(row => {
    const cells = columns.map(col => {
      const value = formatCellValue(row[col.key], col.type);
      return `<td class="px-4 py-2 border-b border-gray-700">${value}</td>`;
    }).join('');
    return `<tr class="hover:bg-gray-800">${cells}</tr>`;
  }).join('');
  
  const headerCells = columns.map(col => 
    `<th class="px-4 py-3 text-left font-semibold text-gray-300 border-b border-gray-600">${col.label}</th>`
  ).join('');
  
  const totalRow = data.totals && Object.keys(data.totals).length > 0
    ? `<tr class="bg-gray-800 font-semibold">${columns.map(col => {
        const value = data.totals?.[col.key] !== undefined
          ? formatCellValue(data.totals[col.key], col.type)
          : '';
        return `<td class="px-4 py-2 border-t-2 border-gray-600">${col.key === columns[0].key ? 'Total' : value}</td>`;
      }).join('')}</tr>`
    : '';
  
  return `
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>${reportName}</title>
  <script src="https://cdn.tailwindcss.com"></script>
  <style>
    body { background-color: #0f172a; color: #e2e8f0; }
  </style>
</head>
<body class="p-8">
  <div class="max-w-7xl mx-auto">
    <div class="mb-8">
      <h1 class="text-3xl font-bold text-white mb-2">${reportName}</h1>
      <p class="text-gray-400">Generated: ${dateFormatter.format(data.metadata.generatedAt)} | Rows: ${data.metadata.rowCount}</p>
    </div>
    
    <div class="bg-gray-900 rounded-lg overflow-hidden border border-gray-700">
      <table class="w-full text-sm">
        <thead class="bg-gray-800">
          <tr>${headerCells}</tr>
        </thead>
        <tbody>
          ${tableRows}
          ${totalRow}
        </tbody>
      </table>
    </div>
    
    <div class="mt-6 text-sm text-gray-500">
      Execution time: ${data.metadata.executionTime}ms
    </div>
  </div>
</body>
</html>
  `.trim();
}

// Generate CSV
export function generateCSV(columns: ReportColumn[], data: ReportData): string {
  const headers = columns.map(c => `"${c.label}"`).join(',');
  const rows = data.rows.map(row => 
    columns.map(col => {
      const value = formatCellValue(row[col.key], col.type);
      return `"${value.replace(/"/g, '""')}"`;
    }).join(',')
  ).join('\n');
  
  return `${headers}\n${rows}`;
}
