import React, { useState, useEffect, useMemo } from 'react';
import { useSearchParams } from 'react-router-dom';
import { useQuery, keepPreviousData } from '@tanstack/react-query';
import { motion } from 'framer-motion';
import { Settings2 } from 'lucide-react';
import { Button } from "@/components/ui/button";
import { Switch } from "@/components/ui/switch";
import { Label } from "@/components/ui/label";
import { 
  DropdownMenu, 
  DropdownMenuContent, 
  DropdownMenuTrigger,
  DropdownMenuLabel,
  DropdownMenuSeparator,
  DropdownMenuCheckboxItem
} from "@/components/ui/dropdown-menu";
import {
  Pagination,
  PaginationContent,
  PaginationEllipsis,
  PaginationItem,
  PaginationLink,
  PaginationNext,
  PaginationPrevious,
} from "@/components/ui/pagination";
import { ProductMetric, ProductMetricColumnKey, ActiveFilterValue } from "@/types/products";
import { ProductTable } from "@/components/products/ProductTable";
import { ProductFilters } from "@/components/products/ProductFilters";
import { ProductDetail } from "@/components/products/ProductDetail";
import { ProductViews } from "@/components/products/ProductViews";
import { ProductTableSkeleton } from "@/components/products/ProductTableSkeleton";
import { useToast } from "@/hooks/use-toast";

// Column definition type
interface ColumnDef {
  key: ProductMetricColumnKey;
  label: string;
  group: string;
  noLabel?: boolean;
  width?: string;
  format?: (value: any, product?: ProductMetric) => React.ReactNode;
}

// Define available columns with their groups
const AVAILABLE_COLUMNS: ColumnDef[] = [
  // Identity & Basic Info
  { key: 'imageUrl', label: 'Image', group: 'Product Identity', noLabel: true, width: 'w-[60px]' },
  { key: 'title', label: 'Name', group: 'Product Identity'},
  { key: 'sku', label: 'Item Number', group: 'Product Identity' },
  { key: 'barcode', label: 'UPC', group: 'Product Identity' },
  { key: 'brand', label: 'Company', group: 'Product Identity' },
  { key: 'line', label: 'Line', group: 'Product Identity' },
  { key: 'subline', label: 'Subline', group: 'Product Identity' },
  { key: 'artist', label: 'Artist', group: 'Product Identity' },
  { key: 'isVisible', label: 'Visible', group: 'Product Identity' },
  { key: 'isReplenishable', label: 'Replenishable', group: 'Product Identity' },
  { key: 'abcClass', label: 'ABC Class', group: 'Product Identity' },
  { key: 'status', label: 'Status', group: 'Product Identity' },
  { key: 'dateCreated', label: 'Created', group: 'Dates' },
  
  // Supply Chain
  { key: 'vendor', label: 'Supplier', group: 'Supply Chain' },
  { key: 'vendorReference', label: 'Supplier #', group: 'Supply Chain' },
  { key: 'notionsReference', label: 'Notions #', group: 'Supply Chain' },
  { key: 'harmonizedTariffCode', label: 'Tariff Code', group: 'Supply Chain' },
  { key: 'countryOfOrigin', label: 'Country', group: 'Supply Chain' },
  { key: 'location', label: 'Location', group: 'Supply Chain' },
  { key: 'moq', label: 'MOQ', group: 'Supply Chain', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },

  // Physical Properties
  { key: 'weight', label: 'Weight', group: 'Physical', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'length', label: 'Length', group: 'Physical', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'width', label: 'Width', group: 'Physical', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'height', label: 'Height', group: 'Physical', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'dimensions', label: 'Dimensions', group: 'Physical', format: (_, product) => {
    // Handle dimensions as separate length, width, height fields
    const length = product?.length;
    const width = product?.width; 
    const height = product?.height;
    if (length && width && height) {
      return `${length}×${width}×${height}`;
    }
    return '-';
  }},
  
  // Customer Engagement
  { key: 'rating', label: 'Rating', group: 'Customer', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'reviews', label: 'Reviews', group: 'Customer', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'baskets', label: 'Basket Adds', group: 'Customer', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'notifies', label: 'Stock Alerts', group: 'Customer', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  
  // Inventory & Stock
  { key: 'currentStock', label: 'Current Stock', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'preorderCount', label: 'Preorders', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'notionsInvCount', label: 'Notions Inv.', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'configSafetyStock', label: 'Safety Stock', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'replenishmentUnits', label: 'Replenish Units', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'stockCoverInDays', label: 'Stock Cover (Days)', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'sellsOutInDays', label: 'Sells Out In (Days)', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'onOrderQty', label: 'On Order', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'earliestExpectedDate', label: 'Expected Date', group: 'Inventory' },
  { key: 'isOldStock', label: 'Old Stock', group: 'Inventory' },
  { key: 'overstockedUnits', label: 'Overstock Qty', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'stockoutDays30d', label: 'Stockout Days (30d)', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'stockoutRate30d', label: 'Stockout Rate %', group: 'Inventory', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'avgStockUnits30d', label: 'Avg Stock Units (30d)', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'receivedQty30d', label: 'Received Qty (30d)', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'poCoverInDays', label: 'PO Cover (Days)', group: 'Inventory', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  
  // Pricing & Costs
  { key: 'currentPrice', label: 'Price', group: 'Pricing', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'currentRegularPrice', label: 'Regular Price', group: 'Pricing', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'currentCostPrice', label: 'Cost', group: 'Pricing', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'currentLandingCostPrice', label: 'Landing Cost', group: 'Pricing', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'currentStockCost', label: 'Stock Cost', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'currentStockRetail', label: 'Stock Retail', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'currentStockGross', label: 'Stock Gross', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'onOrderCost', label: 'On Order Cost', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'onOrderRetail', label: 'On Order Retail', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'overstockedCost', label: 'Overstock Cost', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'overstockedRetail', label: 'Overstock Retail', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'avgStockCost30d', label: 'Avg Stock Cost (30d)', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'avgStockRetail30d', label: 'Avg Stock Retail (30d)', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'avgStockGross30d', label: 'Avg Stock Gross (30d)', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'receivedCost30d', label: 'Received Cost (30d)', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'replenishmentCost', label: 'Replenishment Cost', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'replenishmentRetail', label: 'Replenishment Retail', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'replenishmentProfit', label: 'Replenishment Profit', group: 'Valuation', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  
  // Dates & Timing
  { key: 'dateFirstReceived', label: 'First Received', group: 'Dates' },
  { key: 'dateLastReceived', label: 'Last Received', group: 'Dates' },
  { key: 'dateFirstSold', label: 'First Sold', group: 'Dates' },
  { key: 'dateLastSold', label: 'Last Sold', group: 'Dates' },
  { key: 'ageDays', label: 'Age (Days)', group: 'Dates', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'avgLeadTimeDays', label: 'Avg Lead Time', group: 'Dates', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'replenishDate', label: 'Replenish Date', group: 'Dates' },
  { key: 'planningPeriodDays', label: 'Planning Period (Days)', group: 'Dates', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  
  // Sales & Revenue
  { key: 'salesVelocityDaily', label: 'Daily Velocity', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'yesterdaySales', label: 'Yesterday Sales', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'sales7d', label: 'Sales (7d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'revenue7d', label: 'Revenue (7d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'sales14d', label: 'Sales (14d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'revenue14d', label: 'Revenue (14d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'sales30d', label: 'Sales (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'revenue30d', label: 'Revenue (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'sales365d', label: 'Sales (365d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'revenue365d', label: 'Revenue (365d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'avgSalesPerDay30d', label: 'Avg Sales/Day (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'avgSalesPerMonth30d', label: 'Avg Sales/Month (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'asp30d', label: 'ASP (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'acp30d', label: 'ACP (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'avgRos30d', label: 'Avg ROS (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'returnsUnits30d', label: 'Returns Units (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'returnsRevenue30d', label: 'Returns Revenue (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'discounts30d', label: 'Discounts (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'grossRevenue30d', label: 'Gross Revenue (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'grossRegularRevenue30d', label: 'Gross Regular Revenue (30d)', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'lifetimeSales', label: 'Lifetime Sales', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'lifetimeRevenue', label: 'Lifetime Revenue', group: 'Sales', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  
  // Financial Performance
  { key: 'cogs30d', label: 'COGS (30d)', group: 'Financial', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'profit30d', label: 'Profit (30d)', group: 'Financial', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'margin30d', label: 'Margin %', group: 'Financial', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'markup30d', label: 'Markup %', group: 'Financial', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'gmroi30d', label: 'GMROI', group: 'Financial', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'stockturn30d', label: 'Stock Turn', group: 'Financial', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'sellThrough30d', label: 'Sell Through %', group: 'Financial', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'returnRate30d', label: 'Return Rate %', group: 'Financial', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'discountRate30d', label: 'Discount Rate %', group: 'Financial', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'markdown30d', label: 'Markdown (30d)', group: 'Financial', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'markdownRate30d', label: 'Markdown Rate %', group: 'Financial', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  
  // Forecasting
  { key: 'leadTimeForecastUnits', label: 'Lead Time Forecast Units', group: 'Forecasting', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'daysOfStockForecastUnits', label: 'Days of Stock Forecast Units', group: 'Forecasting', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'planningPeriodForecastUnits', label: 'Planning Period Forecast Units', group: 'Forecasting', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'leadTimeClosingStock', label: 'Lead Time Closing Stock', group: 'Forecasting', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'daysOfStockClosingStock', label: 'Days of Stock Closing Stock', group: 'Forecasting', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'replenishmentNeededRaw', label: 'Replenishment Needed Raw', group: 'Forecasting', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'forecastLostSalesUnits', label: 'Forecast Lost Sales Units', group: 'Forecasting', format: (v) => v === 0 ? '0' : v ? v.toFixed(1) : '-' },
  { key: 'forecastLostRevenue', label: 'Forecast Lost Revenue', group: 'Forecasting', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  
  // First Period Performance
  { key: 'first7DaysSales', label: 'First 7 Days Sales', group: 'First Period', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'first7DaysRevenue', label: 'First 7 Days Revenue', group: 'First Period', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'first30DaysSales', label: 'First 30 Days Sales', group: 'First Period', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'first30DaysRevenue', label: 'First 30 Days Revenue', group: 'First Period', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'first60DaysSales', label: 'First 60 Days Sales', group: 'First Period', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'first60DaysRevenue', label: 'First 60 Days Revenue', group: 'First Period', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'first90DaysSales', label: 'First 90 Days Sales', group: 'First Period', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'first90DaysRevenue', label: 'First 90 Days Revenue', group: 'First Period', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  
  // Growth Metrics
  { key: 'salesGrowth30dVsPrev', label: 'Sales Growth % (30d vs Prev)', group: 'Growth Analysis', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'revenueGrowth30dVsPrev', label: 'Revenue Growth % (30d vs Prev)', group: 'Growth Analysis', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'salesGrowthYoy', label: 'Sales Growth % YoY', group: 'Growth Analysis', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'revenueGrowthYoy', label: 'Revenue Growth % YoY', group: 'Growth Analysis', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  
  // Demand Variability Metrics
  { key: 'salesVariance30d', label: 'Sales Variance (30d)', group: 'Demand Variability', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'salesStdDev30d', label: 'Sales Std Dev (30d)', group: 'Demand Variability', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'salesCv30d', label: 'Sales CV % (30d)', group: 'Demand Variability', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'demandPattern', label: 'Demand Pattern', group: 'Demand Variability' },
  
  // Service Level Metrics
  { key: 'fillRate30d', label: 'Fill Rate % (30d)', group: 'Service Level', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'stockoutIncidents30d', label: 'Stockout Incidents (30d)', group: 'Service Level', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  { key: 'serviceLevel30d', label: 'Service Level % (30d)', group: 'Service Level', format: (v) => v === 0 ? '0%' : v ? `${v.toFixed(1)}%` : '-' },
  { key: 'lostSalesIncidents30d', label: 'Lost Sales Incidents (30d)', group: 'Service Level', format: (v) => v === 0 ? '0' : v ? v.toString() : '-' },
  
  // Seasonality Metrics
  { key: 'seasonalityIndex', label: 'Seasonality Index', group: 'Seasonality', format: (v) => v === 0 ? '0' : v ? v.toFixed(2) : '-' },
  { key: 'seasonalPattern', label: 'Seasonal Pattern', group: 'Seasonality' },
  { key: 'peakSeason', label: 'Peak Season', group: 'Seasonality' },
  
  // Quality Indicators
  { key: 'lifetimeRevenueQuality', label: 'Lifetime Revenue Quality', group: 'Data Quality' },
];

// Define default columns for each view
const VIEW_COLUMNS: Record<string, ProductMetricColumnKey[]> = {
  all: [
    'imageUrl',
    'title',
    'brand',
    'status',
    'currentStock',
    'currentPrice',
    'salesVelocityDaily',
    'sales30d',
    'revenue30d',
    'profit30d',
    'stockCoverInDays',
    'currentStockCost',
    'salesGrowth30dVsPrev'
  ],
  critical: [
    'status',
    'imageUrl',
    'title',
    'currentStock',
    'configSafetyStock',
    'replenishmentUnits',
    'salesVelocityDaily',
    'sales7d',
    'sales30d',
    'onOrderQty',
    'earliestExpectedDate',
    'vendor',
    'dateLastReceived',
    'avgLeadTimeDays',
    'serviceLevel30d',
    'stockoutIncidents30d'
  ],
  reorder: [
    'status',
    'imageUrl',
    'title',
    'currentStock',
    'configSafetyStock',
    'replenishmentUnits',
    'salesVelocityDaily',
    'sellsOutInDays',
    'currentCostPrice',
    'sales30d',
    'vendor',
    'avgLeadTimeDays',
    'dateLastReceived',
    'demandPattern'
  ],
  overstocked: [
    'status',
    'imageUrl',
    'title',
    'currentStock',
    'overstockedUnits',
    'sales7d',
    'sales30d',
    'salesVelocityDaily',
    'stockCoverInDays',
    'stockturn30d',
    'currentStockCost',
    'overstockedCost',
    'dateLastSold',
    'salesVariance30d'
  ],
  'at-risk': [
    'status',
    'imageUrl',
    'title',
    'currentStock',
    'configSafetyStock',
    'salesVelocityDaily',
    'sales7d',
    'sales30d',
    'stockCoverInDays',
    'sellsOutInDays',
    'dateLastSold',
    'avgLeadTimeDays',
    'profit30d',
    'fillRate30d',
    'salesGrowth30dVsPrev'
  ],
  new: [
    'status',
    'imageUrl',
    'title',
    'currentStock',
    'salesVelocityDaily',
    'sales7d',
    'vendor',
    'brand',
    'currentPrice',
    'currentCostPrice',
    'dateFirstReceived',
    'ageDays',
    'abcClass',
    'first7DaysSales',
    'first30DaysSales'
  ],
  healthy: [
    'status',
    'imageUrl',
    'title',
    'currentStock',
    'stockCoverInDays',
    'salesVelocityDaily',
    'sales30d',
    'revenue30d',
    'profit30d',
    'margin30d',
    'gmroi30d',
    'stockturn30d',
    'salesGrowth30dVsPrev',
    'serviceLevel30d'
  ],
};

export function Products() {
  const [searchParams, setSearchParams] = useSearchParams();
  const [filters, setFilters] = useState<Record<string, ActiveFilterValue>>({});
  const [sortColumn, setSortColumn] = useState<ProductMetricColumnKey>('title');
  const [sortDirection, setSortDirection] = useState<'asc' | 'desc'>('asc');
  // Track last sort direction for each column
  const [columnSortDirections, setColumnSortDirections] = useState<Record<string, 'asc' | 'desc'>>({
    'title': 'asc' // Initialize with default sort column and direction
  });
  const [currentPage, setCurrentPage] = useState(1);
  const [activeView, setActiveView] = useState(searchParams.get('view') || "all");
  const [pageSize] = useState(50);
  const [showNonReplenishable, setShowNonReplenishable] = useState(false);
  const [selectedProductId, setSelectedProductId] = useState<number | null>(null);
  const [, setIsLoading] = useState(false);
  const { toast } = useToast();
  
  // Store visible columns and order for each view
  const [viewColumns, setViewColumns] = useState<Record<string, Set<ProductMetricColumnKey>>>(() => {
    const initialColumns: Record<string, Set<ProductMetricColumnKey>> = {};
    Object.entries(VIEW_COLUMNS).forEach(([view, columns]) => {
      initialColumns[view] = new Set(columns);
    });
    return initialColumns;
  });
  
  const [viewColumnOrder, setViewColumnOrder] = useState<Record<string, ProductMetricColumnKey[]>>(() => {
    const initialOrder: Record<string, ProductMetricColumnKey[]> = {};
    Object.entries(VIEW_COLUMNS).forEach(([view, defaultColumns]) => {
      initialOrder[view] = [
        ...defaultColumns,
        ...AVAILABLE_COLUMNS.map(col => col.key).filter(key => !defaultColumns.includes(key))
      ];
    });
    return initialOrder;
  });

  // Get current view's columns
  const visibleColumns = useMemo(() => {
    const columns = new Set(viewColumns[activeView] || VIEW_COLUMNS.all);
    
    // Add isReplenishable column when showing non-replenishable products for better visibility
    if (showNonReplenishable) {
      columns.add('isReplenishable');
    }
    
    return columns;
  }, [viewColumns, activeView, showNonReplenishable]);

  const columnOrder = viewColumnOrder[activeView] || viewColumnOrder.all;

  // Handle column visibility changes
  const handleColumnVisibilityChange = (column: ProductMetricColumnKey, isVisible: boolean) => {
    setViewColumns(prev => ({
      ...prev,
      [activeView]: isVisible 
        ? new Set([...prev[activeView], column])
        : new Set([...prev[activeView]].filter(col => col !== column))
    }));
  };

  // Handle column order changes
  const handleColumnOrderChange = (newOrder: ProductMetricColumnKey[]) => {
    setViewColumnOrder(prev => ({
      ...prev,
      [activeView]: newOrder
    }));
  };

  // Reset columns to default for current view
  const resetColumnsToDefault = () => {
    setViewColumns(prev => ({
      ...prev,
      [activeView]: new Set(VIEW_COLUMNS[activeView] || VIEW_COLUMNS.all)
    }));
    setViewColumnOrder(prev => ({
      ...prev,
      [activeView]: [
        ...(VIEW_COLUMNS[activeView] || VIEW_COLUMNS.all),
        ...AVAILABLE_COLUMNS.map(col => col.key)
          .filter(key => !(VIEW_COLUMNS[activeView] || VIEW_COLUMNS.all).includes(key))
      ]
    }));
  };

  // Function to fetch products data
  const transformFilters = (filters: Record<string, any>) => {
    const transformedFilters: Record<string, any> = {};
    
    Object.entries(filters).forEach(([key, value]) => {
      if (typeof value === 'object' && 'operator' in value) {
        // Convert the operator format to match what the backend expects
        // Backend expects keys like "sales30d_gt" instead of separate operator parameters
        const operatorSuffix = value.operator === '=' ? 'eq' : 
                              value.operator === '>' ? 'gt' :
                              value.operator === '>=' ? 'gte' :
                              value.operator === '<' ? 'lt' :
                              value.operator === '<=' ? 'lte' : 
                              value.operator === 'between' ? 'between' : 'eq';
        
        // Create a key with the correct suffix format: key_operator
        transformedFilters[`${key}_${operatorSuffix}`] = value.value;
      } else {
        // Simple values are passed as-is
        transformedFilters[key] = value;
      }
    });
    
    return transformedFilters;
  };

  const fetchProducts = async () => {
    setIsLoading(true);
    try {
      const params = new URLSearchParams();
      params.append('page', currentPage.toString());
      params.append('limit', pageSize.toString());
      
      if (sortColumn) {
        // Don't convert camelCase to snake_case - use the column name directly
        // as defined in the backend's COLUMN_MAP
        console.log(`Sorting: ${sortColumn} (${sortDirection})`);
        params.append('sort', sortColumn);
        params.append('order', sortDirection);
      }
      
      if (activeView && activeView !== 'all') {
        const stockStatus = activeView === 'at-risk' ? 'At Risk' : 
                           activeView === 'reorder' ? 'Reorder Soon' : 
                           activeView === 'overstocked' ? 'Overstock' : 
                           activeView === 'new' ? 'New' :
                           activeView.charAt(0).toUpperCase() + activeView.slice(1);
        
        console.log(`View: ${activeView} → Stock Status: ${stockStatus}`);
        params.append('stock_status', stockStatus);
      }

      // Transform filters to match API expectations
      const transformedFilters = transformFilters(filters);
      Object.entries(transformedFilters).forEach(([key, value]) => {
        if (value !== undefined && value !== null && value !== '') {
          // Don't convert camelCase to snake_case - use the filter name directly
          if (Array.isArray(value)) {
            params.append(key, JSON.stringify(value));
          } else {
            params.append(key, value.toString());
          }
        }
      });

      if (!showNonReplenishable) {
        params.append('showNonReplenishable', 'false');
      }

      // Log the final query parameters for debugging
      console.log('API Query:', params.toString());

      const response = await fetch(`/api/metrics?${params.toString()}`);
      if (!response.ok) throw new Error('Failed to fetch products');
      
      const data = await response.json();
      
      // Transform snake_case keys to camelCase and convert string numbers to actual numbers
      const transformedProducts = data.metrics?.map((product: any) => {
        const transformed: any = {};
        
        // Process all keys to convert from snake_case to camelCase
        Object.entries(product).forEach(([key, value]) => {
          // Better handling of snake_case to camelCase conversion
          let camelKey = key;
          
          // First handle cases like sales_7d -> sales7d
          camelKey = camelKey.replace(/_(\d+[a-z])/g, '$1');
          
          // Then handle regular snake_case -> camelCase
          camelKey = camelKey.replace(/_([a-z])/g, (_, p1) => p1.toUpperCase());
          
          // Convert numeric strings to actual numbers, but handle empty strings properly
          if (typeof value === 'string' && value !== '' && !isNaN(Number(value)) && 
              !key.toLowerCase().includes('date') && key !== 'sku' && key !== 'title' && 
              key !== 'brand' && key !== 'vendor') {
            transformed[camelKey] = Number(value);
          } else {
            transformed[camelKey] = value;
          }
        });
        
        // Ensure pid is a number
        transformed.pid = typeof transformed.pid === 'string' ? 
          parseInt(transformed.pid, 10) : transformed.pid;
        
        return transformed;
      }) || [];
      
      // Debug: Log the first item to check field mapping
      if (transformedProducts.length > 0) {
        console.log('Sample product after transformation:');
        console.log('sales7d:', transformedProducts[0].sales7d);
        console.log('sales30d:', transformedProducts[0].sales30d);
        console.log('revenue30d:', transformedProducts[0].revenue30d);
        console.log('margin30d:', transformedProducts[0].margin30d);
        console.log('markup30d:', transformedProducts[0].markup30d);
        
        // Debug specific fields with issues
        console.log('configSafetyStock:', transformedProducts[0].configSafetyStock);
        console.log('length:', transformedProducts[0].length);
        console.log('width:', transformedProducts[0].width);
        console.log('height:', transformedProducts[0].height);
        console.log('first7DaysSales:', transformedProducts[0].first7DaysSales);
        console.log('first30DaysSales:', transformedProducts[0].first30DaysSales);
        console.log('first7DaysRevenue:', transformedProducts[0].first7DaysRevenue);
        console.log('first30DaysRevenue:', transformedProducts[0].first30DaysRevenue);
      }
      
      // Transform the metrics response to match our expected format
      return {
        products: transformedProducts,
        pagination: data.pagination || { 
          total: 0, 
          pages: 0,
          currentPage: 1, 
          limit: pageSize 
        },
        filters: data.appliedQuery?.filters || {}
      };
    } catch (error) {
      console.error('Error fetching products:', error);
      toast({
        title: "Error",
        description: "Failed to fetch products. Please try again.",
        variant: "destructive",
      });
      return null;
    } finally {
      setIsLoading(false);
    }
  };

  // Query for filter options
  const { data: filterOptionsData, isLoading: isLoadingFilterOptions } = useQuery({
    queryKey: ['filterOptions'],
    queryFn: async () => {
      try {
        const response = await fetch('/api/metrics/filter-options');
        if (!response.ok) throw new Error('Failed to fetch filter options');
        const data = await response.json();
        
        // Ensure we have the expected structure with correct casing
        return {
          vendors: data.vendors || [],
          brands: data.brands || [],
          abcClasses: data.abc_classes || data.abcClasses || []
        };
      } catch (error) {
        console.error('Error fetching filter options:', error);
        return { vendors: [], brands: [], abcClasses: [] };
      }
    },
    staleTime: 5 * 60 * 1000, // Cache for 5 minutes
  });

  // Query for products data
  const { data, isFetching } = useQuery({
    queryKey: ['products', currentPage, pageSize, sortColumn, sortDirection, activeView, filters, showNonReplenishable],
    queryFn: fetchProducts,
    placeholderData: keepPreviousData,
  });

  // Update current page if it exceeds the total pages
  useEffect(() => {
    if (data?.pagination.pages && currentPage > data.pagination.pages) {
      setCurrentPage(1);
    }
  }, [currentPage, data?.pagination.pages]);

  // Handle sort column change with improved column-specific direction memory
  const handleSort = (column: ProductMetricColumnKey) => {
    let nextDirection: 'asc' | 'desc';
    
    if (sortColumn === column) {
      // If clicking the same column, toggle direction
      nextDirection = sortDirection === 'asc' ? 'desc' : 'asc';
    } else {
      // If clicking a different column:
      // 1. If this column has been sorted before, use the stored direction
      // 2. Otherwise use a sensible default (asc for text, desc for numeric columns)
      const prevDirection = columnSortDirections[column];
      
      if (prevDirection) {
        // Use the stored direction
        nextDirection = prevDirection;
      } else {
        // Determine sensible default based on column type
        const columnDef = AVAILABLE_COLUMNS.find(c => c.key === column);
        const isNumeric = columnDef?.group === 'Sales' || 
                        columnDef?.group === 'Financial' || 
                        columnDef?.group === 'Stock' || 
                        ['currentPrice', 'currentRegularPrice', 'currentCostPrice', 'currentStock'].includes(column);
        
        // Start with descending for numeric columns (to see highest values first)
        // Start with ascending for text columns (alphabetical order)
        nextDirection = isNumeric ? 'desc' : 'asc';
      }
    }
    
    // Update the current sort state
    setSortDirection(nextDirection);
    setSortColumn(column);
    
    // Remember this column's sort direction for next time
    setColumnSortDirections(prev => ({
      ...prev,
      [column]: nextDirection
    }));
  };

  // Handle filter changes
  const handleFilterChange = (newFilters: Record<string, ActiveFilterValue>) => {
    setFilters(newFilters);
    setCurrentPage(1);
  };

  const handleClearFilters = () => {
    setFilters({});
    setCurrentPage(1);
  };

  // Function to handle page changes
  const handlePageChange = (page: number) => {
    setCurrentPage(page);
    window.scrollTo({ top: 0, behavior: 'smooth' });
  };

  // Group columns by their group property
  const columnsByGroup = AVAILABLE_COLUMNS.reduce((acc, col) => {
    if (!acc[col.group]) {
      acc[col.group] = [];
    }
    acc[col.group].push(col);
    return acc;
  }, {} as Record<string, typeof AVAILABLE_COLUMNS>);

  const renderColumnToggle = () => {
    const [open, setOpen] = React.useState(false);
    
    return (
      <DropdownMenu open={open} onOpenChange={setOpen} modal={false}>
        <DropdownMenuTrigger asChild>
          <Button variant="outline" className="ml-auto">
            <Settings2 className="mr-2 h-4 w-4" />
            Columns
          </Button>
        </DropdownMenuTrigger>
        <DropdownMenuContent
          align="end"
          className="w-[600px] max-h-[calc(100vh-16rem)] overflow-y-auto"
          onCloseAutoFocus={(e) => e.preventDefault()}
          onPointerDownOutside={(e) => {
            // Only close if clicking outside the dropdown
            if (!(e.target as HTMLElement).closest('[role="dialog"]')) {
              setOpen(false);
            }
          }}
          onInteractOutside={(e) => {
            // Prevent closing when interacting with checkboxes
            if ((e.target as HTMLElement).closest('[role="dialog"]')) {
              e.preventDefault();
            }
          }}
        >
          <div className="sticky top-0 bg-background z-10 flex items-center justify-between">
            <DropdownMenuLabel>Toggle columns</DropdownMenuLabel>
            <Button 
              variant="secondary" 
              size="sm"
              onClick={(e) => {
                resetColumnsToDefault();
                // Prevent closing by stopping propagation
                e.stopPropagation();
              }}
            >
              Reset to Default
            </Button>
          </div>
          <DropdownMenuSeparator className="sticky top-[29px] bg-background z-10" />
          <div style={{ columnCount: 3, columnGap: '2rem' }} className="p-2">
            {Object.entries(columnsByGroup).map(([group, columns]) => (
              <div key={group} style={{ breakInside: 'avoid' }} className="mb-4">
                <DropdownMenuLabel className="text-xs font-semibold text-muted-foreground mb-2">
                  {group}
                </DropdownMenuLabel>
                <div className="flex flex-col gap-1">
                  {columns.map((column) => (
                    <DropdownMenuCheckboxItem
                      key={column.key}
                      className="capitalize"
                      checked={visibleColumns.has(column.key)}
                      onCheckedChange={(checked) => {
                        handleColumnVisibilityChange(column.key, checked);
                      }}
                      onSelect={(e) => {
                        e.preventDefault();
                      }}
                    >
                      {column.label}
                    </DropdownMenuCheckboxItem>
                  ))}
                </div>
              </div>
            ))}
          </div>
        </DropdownMenuContent>
      </DropdownMenu>
    );
  };

  // Calculate pagination numbers
  const totalPages = data?.pagination.pages || 1;
  const showEllipsis = totalPages > 7;
  const pageNumbers = showEllipsis
    ? currentPage <= 4
      ? [1, 2, 3, 4, 5]
      : currentPage >= totalPages - 3
        ? [totalPages - 4, totalPages - 3, totalPages - 2, totalPages - 1, totalPages]
        : [currentPage - 2, currentPage - 1, currentPage, currentPage + 1, currentPage + 2]
    : Array.from({ length: totalPages }, (_, i) => i + 1);

  // Update URL when view changes
  const handleViewChange = (view: string) => {
    setActiveView(view);
    setCurrentPage(1);
    setSearchParams(prev => {
      const newParams = new URLSearchParams(prev);
      newParams.set('view', view);
      return newParams;
    });
  };

  // Sync with URL params when they change
  useEffect(() => {
    const viewParam = searchParams.get('view');
    if (viewParam && viewParam !== activeView) {
      setActiveView(viewParam);
    }
  }, [searchParams]);

  return (
    <motion.div
      initial={{ opacity: 0 }}
      animate={{ opacity: 1 }}
      transition={{ duration: 0.2 }}
      className="container mx-auto py-6 space-y-4"
    >
      <div className="flex items-center justify-between">
        <h1 className="text-3xl font-bold tracking-tight">Products</h1>
      </div>

      <ProductViews 
        activeView={activeView} 
        onViewChange={handleViewChange}
      />

      <div>
        <div className="flex items-center justify-between mb-4">
          <ProductFilters
            filterOptions={{
              vendors: filterOptionsData?.vendors ?? [],
              brands: filterOptionsData?.brands ?? [],
              abcClasses: filterOptionsData?.abcClasses ?? []
            }}
            isLoadingOptions={isLoadingFilterOptions}
            onFilterChange={handleFilterChange}
            onClearFilters={handleClearFilters}
            activeFilters={filters}
          />
          <div className="flex items-center gap-4">
            <div className="flex items-center space-x-2">
              <Switch
                id="show-non-replenishable"
                checked={showNonReplenishable}
                onCheckedChange={(checked) => {
                  setShowNonReplenishable(checked);
                  setCurrentPage(1);
                }}
              />
              <Label htmlFor="show-non-replenishable">Show Non-Replenishable</Label>
            </div>
            {data?.pagination?.total !== undefined && (
              <div className="text-sm text-muted-foreground">
                {data.pagination.total.toLocaleString()} products
              </div>
            )}
            {renderColumnToggle()}
          </div>
        </div>

        {isFetching ? (
          <ProductTableSkeleton />
        ) : (
          <div className="space-y-4">
            <ProductTable
              products={data?.products?.map((product: ProductMetric) => ({
                ...product,
                // No need to calculate status anymore since it comes from the backend
                status: product.status || 'Healthy' // Fallback only if status is null
              })) || []}
              onSort={handleSort}
              sortColumn={sortColumn}
              sortDirection={sortDirection}
              visibleColumns={visibleColumns}
              columnDefs={AVAILABLE_COLUMNS}
              columnOrder={columnOrder}
              onColumnOrderChange={handleColumnOrderChange}
              onViewProduct={(productId) => setSelectedProductId(productId)}
            />
            
            {totalPages > 1 && (
              <Pagination>
                <PaginationContent>
                  <PaginationItem>
                    <PaginationPrevious 
                      href="#"
                      onClick={(e) => {
                        e.preventDefault();
                        handlePageChange(Math.max(1, currentPage - 1));
                      }}
                      aria-disabled={currentPage === 1}
                    />
                  </PaginationItem>
                  
                  {showEllipsis && currentPage > 4 && (
                    <>
                      <PaginationItem>
                        <PaginationLink
                          href="#"
                          onClick={(e) => {
                            e.preventDefault();
                            handlePageChange(1);
                          }}
                        >
                          1
                        </PaginationLink>
                      </PaginationItem>
                      <PaginationItem>
                        <PaginationEllipsis />
                      </PaginationItem>
                    </>
                  )}
                  
                  {pageNumbers.map((page) => (
                    <PaginationItem key={page}>
                      <PaginationLink
                        href="#"
                        isActive={currentPage === page}
                        onClick={(e) => {
                          e.preventDefault();
                          handlePageChange(page);
                        }}
                      >
                        {page}
                      </PaginationLink>
                    </PaginationItem>
                  ))}
                  
                  {showEllipsis && currentPage < totalPages - 3 && (
                    <>
                      <PaginationItem>
                        <PaginationEllipsis />
                      </PaginationItem>
                      <PaginationItem>
                        <PaginationLink
                          href="#"
                          onClick={(e) => {
                            e.preventDefault();
                            handlePageChange(totalPages);
                          }}
                        >
                          {totalPages}
                        </PaginationLink>
                      </PaginationItem>
                    </>
                  )}
                  
                  <PaginationItem>
                    <PaginationNext
                      href="#"
                      onClick={(e) => {
                        e.preventDefault();
                        handlePageChange(Math.min(totalPages, currentPage + 1));
                      }}
                      aria-disabled={currentPage === totalPages}
                    />
                  </PaginationItem>
                </PaginationContent>
              </Pagination>
            )}
          </div>
        )}
      </div>

      <ProductDetail 
        productId={selectedProductId} 
        onClose={() => setSelectedProductId(null)} 
      />
    </motion.div>
  );
} 