import * as React from "react";
import {
  BestSellerIcon,
  PurchaseRateIcon,
  MostViewedIcon,
} from "@/components/Sections/RealTimeAnalytics/Icons";
import type { SxProps } from "@mui/material/styles";

const commonSvgIconSx: SxProps = {
  width: "100%",
  height: "140px",
};

export type TimeRangeLabelType =
  | "last1day"
  | "last7days"
  | "last12hours"
  | "last6hours"
  | "last30days";

export type CardItemType = {
  id: string;
  title: string;
  icon: React.ReactNode;
  sql: string;
  defaultTimerange?: TimeRangeLabelType;
  defaultTimeranges?: TimeRangeLabelType[];
};

export const timerangeLabelMap: Record<TimeRangeLabelType, string> = {
  last30days: "Last 30 Days",
  last7days: "Last 7 Days",
  last1day: "Last 1 Day",
  last12hours: "Last 12 Hours",
  last6hours: "Last 6 Hours",
};

export const cards: CardItemType[] = [
  {
    id: "best-sellers",
    title: "Best Sellers",
    defaultTimerange: "last7days",
    defaultTimeranges: ["last30days", "last7days", "last1day", "last12hours"],
    icon: (
      <BestSellerIcon
        fontSize="inherit"
        sx={{
          ...commonSvgIconSx,
        }}
      />
    ),
    sql: `WITH top_sellers AS (
      SELECT 
            product_id, 
            SUM(price) AS sold
      FROM events
      WHERE event_type = 'purchase'
            AND event_time >= '\${start_time}'
            AND event_time <= '\${end_time}'
      GROUP BY product_id
      ORDER BY sold DESC
      LIMIT \${top_n}
)
SELECT 
      products.product_id, 
      products.product_name, 
      top_sellers.sold
FROM top_sellers
JOIN products ON top_sellers.product_id = products.product_id
ORDER BY sold DESC`,
  },
  {
    id: "purchase-rate",
    title: "Purchase Rate",
    defaultTimerange: "last1day",
    icon: (
      <PurchaseRateIcon
        fontSize="inherit"
        sx={{
          ...commonSvgIconSx,
        }}
      />
    ),
    sql: `SELECT (
      COUNT(CASE WHEN event_type = 'purchase' THEN 1 ELSE NULL END) /
      COUNT(CASE WHEN event_type = 'view' THEN 1 ELSE NULL END)
  ) AS bought_rate
FROM events
WHERE event_time >= '\${start_time}'
    AND event_time <= '\${end_time}'`,
  },
  {
    id: "most-viewed",
    title: "Most Viewed",
    defaultTimerange: "last1day",
    icon: (
      <MostViewedIcon
        fontSize="inherit"
        sx={{
          ...commonSvgIconSx,
        }}
      />
    ),
    sql: `WITH top_merchandises AS (
      SELECT 
          product_id, 
          COUNT(*) AS viewed
      FROM events
      WHERE event_type = 'view'
          AND event_time >= '\${start_time}'
          AND event_time <= '\${end_time}'
      GROUP BY product_id
      ORDER BY viewed DESC
      LIMIT \${top_n}
)
SELECT 
      products.product_id, 
      products.product_name, 
      top_merchandises.viewed
FROM top_merchandises
JOIN products ON top_merchandises.product_id = products.product_id
ORDER BY viewed DESC`,
  },
];


export const SQLComment = `-- It is a particularly resource-intensive analytical query.
-- There is an event table with 109,950,743 rows of the same data in both MySQL and TiDB instances. \n`;