// tdd-guard:skip — UI composition page; pure data-fetch + prop-pass, no unit logic here (logic lives in lib/reporting/aggregations.ts with full test coverage)
import { redirect } from 'next/navigation'
import { createClient } from '@/lib/supabase/server'
import { getUserSession, isAtLeastRole } from '@/lib/auth/permissions'
import { logger } from '@/lib/logger'
import AppHeader from '@/components/layout/app-header'
import dynamic from 'next/dynamic'
import { getProfile } from '@/config/profiles'

const ReportingDashboard = dynamic(
  () => import('@/components/reporting/reporting-dashboard'),
  {
    loading: () => (
      <div className="flex-1 flex items-center justify-center py-20 text-sm text-muted-foreground">
        Lade Reporting…
      </div>
    ),
  },
)

export default async function ReportingPage() {
  const session = await getUserSession()
  if (!session) redirect('/login')
  if (!isAtLeastRole(session, 'consultant')) redirect('/projektanlage')

  const supabase = await createClient()

  // Date range: last 90 days for assignments
  const today = new Date()
  const ninetyDaysAgo = new Date(today)
  ninetyDaysAgo.setDate(ninetyDaysAgo.getDate() - 90)
  const startDate = ninetyDaysAgo.toISOString().slice(0, 10)

  // O9 (KAR-704): the OEE overview shows the latest record per line. We bound the
  // query by a ~2-year window on `year` instead of a global row cap — a global
  // `.limit()` ordered by recency silently drops whole lines once other lines
  // accumulate more rows, hiding older projects from the dashboard.
  const OEE_REPORTING_WINDOW_YEARS = 2
  const oeeMinYear = today.getFullYear() - OEE_REPORTING_WINDOW_YEARS

  // KAR-832: Demo-Seeds liegen in den Produktions-Tabellen (KAR-795) und dürfen
  // nicht in die Kunden-Kennzahlen einfließen. NULL zählt als "kein Demo"
  // (Alt-Zeilen vor Einführung der Spalte).
  const NOT_DEMO = 'is_demo.is.null,is_demo.eq.false'

  const [
    { data: projects, error: projectsError },
    { data: oeeRecords },
    { data: assessments },
    // F-016: aggregate assessment responses at query level — join to assessments
    // to enable per-project filtering; still limited to completed assessments
    { data: assessmentResponses },
    { data: assessmentQuestions },
    { data: mainCategories },
    { data: consultants },
    { data: assignments },
    { data: workshopActions },
    { data: projectTypeAssignments },
    // NEW: LSC workshop progress
    { data: processStepsRaw },
    // NEW: LSC measures for Maßnahmenfortschritt
    { data: lscMeasures },
    // NEW: OEE capacity columns (installed_capacity, purchased_capacity, good_output)
    { data: oeeCapacity },
    // NEW: user_audit_log (last 10 entries for activity summary)
    { data: auditLogEntries },
    // NEW: supplier_master_data for Standort dimension
    { data: supplierMasterData },
    // F-002 (KAR-632): department_master_data for Abteilung dimension
    { data: departmentMasterData },
    // KAR-832: Demo-Projekt-IDs für den workshop_actions-Postfilter
    { data: demoProjects, error: demoProjectsError },
  ] = await Promise.all([
    // F-003 (KAR-632): filter to reporting_relevant=true (or NULL for rows that predate the column)
    supabase
      .from('projects')
      .select(`
        id,
        supplier_name,
        supplier_id,
        project_code,
        visit_date,
        kifag_area_id,
        plant_location,
        customer_takt_time_sec,
        target_cycle_time_sec,
        department_id,
        kifag_area:master_data_values!kifag_area_id(id, label),
        project_statuses ( id, code, label )
      `)
      .is('parent_project_id', null)
      .or('reporting_relevant.is.null,reporting_relevant.eq.true')
      .or(NOT_DEMO)
      .order('project_code', { ascending: false, nullsFirst: false }),

    supabase
      .from('oee_records')
      .select(
        'line_name, plant_name, oee_factor, availability_factor, performance_factor, quality_factor, calendar_week, year',
      )
      .gte('year', oeeMinYear)
      .or(NOT_DEMO)
      .order('year', { ascending: false })
      .order('calendar_week', { ascending: false }),

    supabase
      .from('assessments')
      .select('id, title, status, conducted_at, supplier_name, project_id')
      .or(NOT_DEMO)
      .order('conducted_at', { ascending: false })
      .limit(200),

    // F-016: limit responses — join assessment_id to filter; aggregate via assessment_questions join
    supabase
      .from('assessment_responses')
      .select('question_id, selected_rating, is_relevant, assessment_id')
      .or(NOT_DEMO)
      .limit(5000),

    supabase
      .from('assessment_questions')
      .select('id, main_category_id')
      .eq('is_active', true),

    supabase
      .from('assessment_main_categories')
      .select('id, label, sort_order')
      .eq('is_active', true)
      .order('sort_order'),

    supabase
      .from('consultants')
      .select('id, display_name, first_name, last_name')
      .eq('is_active', true)
      .order('last_name'),

    supabase.from('assignments').select('consultant_id, date').gte('date', startDate).or(NOT_DEMO),

    // workshop_actions hat keine is_demo-Spalte — Demo-Zeilen werden unten
    // über die Demo-Projekt-IDs herausgefiltert (KAR-832)
    supabase
      .from('workshop_actions')
      .select('project_id, cost_saving_eur, status')
      .not('cost_saving_eur', 'is', null),

    supabase.from('project_type_assignments').select('project_id, project_type_code'),

    // LSC: process_steps with embedded cycle_measurements (aggregated columns only)
    supabase
      .from('process_steps')
      .select(`
        id,
        project_id,
        station_name,
        cycle_measurements ( cycle_time_sec, is_outlier )
      `)
      .or(NOT_DEMO)
      .limit(2000),

    // LSC measures grouped by project + status for Maßnahmenfortschritt
    supabase
      .from('lsc_measures')
      .select('project_id, status')
      .or(NOT_DEMO)
      .limit(2000),

    // OEE capacity: installed vs purchased vs actual (good_output) per line
    supabase
      .from('oee_records')
      .select(
        'project_id, line_name, installed_capacity, purchased_capacity, good_output, calendar_week, year',
      )
      .not('installed_capacity', 'eq', 0)
      .or(NOT_DEMO)
      .order('year', { ascending: false })
      .order('calendar_week', { ascending: false })
      .limit(200),

    // Audit log: last 10 entries for activity summary card
    supabase
      .from('user_audit_log')
      .select('id, actor_id, action, created_at')
      .order('created_at', { ascending: false })
      .limit(10),

    // Supplier master data for Standort dimension
    supabase
      .from('supplier_master_data')
      .select('id, supplier_name, supplier_location, plant')
      .eq('is_active', true)
      .limit(500),

    // F-002 (KAR-632): Abteilung dimension
    supabase
      .from('department_master_data')
      .select('id, department_code, department_name, organization_area')
      .order('department_code'),

    // KAR-832: workshop_actions trägt kein is_demo — Demo-Projekt-IDs laden,
    // um diese Zeilen unten herauszufiltern. Parallel im selben Promise.all.
    supabase.from('projects').select('id').eq('is_demo', true),
  ])

  // FB-41 (P0 compliance, composition profile — ADR 013): whether the
  // "Berater-Auslastung (letzte 90 Tage)" section renders + exports.
  const consultantLoadReport = getProfile().features.consultantLoadReport

  if (projectsError) logger.error('reporting.projects.fetch_failed', projectsError)

  // KAR-832: fail-closed — kann die Demo-Projekt-Liste nicht geladen werden,
  // werden ALLE workshop_actions mit project_id ausgeblendet (lieber eine
  // Kennzahl zu leer als Demo-Einsparungen in der Kunden-Auswertung).
  if (demoProjectsError) logger.error('reporting.demo_projects.fetch_failed', demoProjectsError)
  const demoProjectIds = new Set((demoProjects ?? []).map((p) => p.id))
  const workshopActionsClean = (workshopActions ?? []).filter((w) =>
    demoProjectsError ? !w.project_id : !w.project_id || !demoProjectIds.has(w.project_id),
  )

  // Map junction rows to per-project arrays
  const typeAssignmentsByProjectId = new Map<string, { project_type_code: string }[]>()
  for (const assignment of projectTypeAssignments ?? []) {
    const list = typeAssignmentsByProjectId.get(assignment.project_id) ?? []
    list.push({ project_type_code: assignment.project_type_code })
    typeAssignmentsByProjectId.set(assignment.project_id, list)
  }

  // Build supplier location map for Standort dimension
  const supplierLocationMap = new Map<string, { location: string | null; plant: string | null }>()
  for (const s of supplierMasterData ?? []) {
    supplierLocationMap.set(s.id, {
      location: s.supplier_location ?? null,
      plant: s.plant ?? null,
    })
  }

  // F-002 (KAR-632): Build department lookup map for Abteilung dimension
  const departmentMap = new Map<string, { name: string; code: string }>()
  for (const d of departmentMasterData ?? []) {
    departmentMap.set(d.id, { name: d.department_name, code: d.department_code })
  }

  const projectsWithTypeAssignments = (projects ?? []).map((project) => {
    const supplierInfo = project.supplier_id
      ? supplierLocationMap.get(project.supplier_id) ?? null
      : null
    const deptInfo = project.department_id
      ? departmentMap.get(project.department_id) ?? null
      : null
    return {
      ...project,
      project_type_assignments: typeAssignmentsByProjectId.get(project.id) ?? [],
      supplier_location: supplierInfo?.location ?? null,
      supplier_plant: supplierInfo?.plant ?? null,
      // F-002 (KAR-632): resolved department name for reporting
      department_name: deptInfo?.name ?? null,
      department_code: deptInfo?.code ?? null,
    }
  })

  return (
    <>
      <AppHeader title="Reporting" backHref="/projektanlage" />
      <div className="flex-1 bg-background px-6 py-6 overflow-y-auto">
        <ReportingDashboard
          // eslint-disable-next-line @typescript-eslint/no-explicit-any
          projects={projectsWithTypeAssignments as any[]}
          oeeRecords={oeeRecords ?? []}
          // eslint-disable-next-line @typescript-eslint/no-explicit-any
          assessments={(assessments ?? []) as any[]}
          assessmentResponses={assessmentResponses ?? []}
          assessmentQuestions={assessmentQuestions ?? []}
          mainCategories={mainCategories ?? []}
          consultants={consultants ?? []}
          assignments={assignments ?? []}
          workshopActions={
            workshopActionsClean as {
              project_id: string
              cost_saving_eur: number | null
              status: string
            }[]
          }
          processSteps={processStepsRaw ?? []}
          lscMeasures={lscMeasures ?? []}
          oeeCapacity={oeeCapacity ?? []}
          auditLogEntries={auditLogEntries ?? []}
          consultantLoadReport={consultantLoadReport}
        />
      </div>
    </>
  )
}