from __future__ import annotations

# ---------------------------------------------------------------------------
# WideWorldImportersDW — read-only MSSQL queries
#
# Column names in this DW use spaces and require bracket notation.
# Each list contains fallback variants tried in order.
# ---------------------------------------------------------------------------

# Daily sales from Fact.Sale joined to Dimension.Date
WWI_DAILY_SALES: list[str] = [
    """
    SELECT
      d.[Date]                            AS sale_date,
      SUM(s.[Total Excluding Tax])        AS revenue,
      SUM(s.[Total Excluding Tax] - s.[Profit]) AS cost,
      SUM(CAST(s.[Quantity] AS FLOAT))   AS quantity,
      COUNT_BIG(*)                        AS orders
    FROM [Fact].[Sale] AS s
    INNER JOIN [Dimension].[Date] AS d
      ON d.[Date Key] = s.[Delivery Date Key]
    GROUP BY d.[Date]
    ORDER BY d.[Date];
    """,
    # Fallback: use Invoice Date Key if Delivery Date Key is missing
    """
    SELECT
      d.[Date]                            AS sale_date,
      SUM(s.[Total Excluding Tax])        AS revenue,
      SUM(s.[Total Excluding Tax] - s.[Profit]) AS cost,
      SUM(CAST(s.[Quantity] AS FLOAT))   AS quantity,
      COUNT_BIG(*)                        AS orders
    FROM [Fact].[Sale] AS s
    INNER JOIN [Dimension].[Date] AS d
      ON d.[Date Key] = s.[Invoice Date Key]
    GROUP BY d.[Date]
    ORDER BY d.[Date];
    """,
]

# Current stock levels per stock item (net movement quantity)
WWI_STOCK_LEVELS: list[str] = [
    """
    SELECT
      si.[Stock Item Key]          AS stock_item_key,
      si.[Stock Item]              AS stock_item_name,
      si.[Unit Price]              AS unit_price,
      si.[Lead Time Days]          AS lead_time_days,
      SUM(CAST(m.[Quantity] AS FLOAT)) AS current_stock
    FROM [Dimension].[Stock Item] AS si
    LEFT JOIN [Fact].[Movement] AS m
      ON m.[Stock Item Key] = si.[Stock Item Key]
    WHERE si.[Stock Item Key] <> 0
    GROUP BY
      si.[Stock Item Key],
      si.[Stock Item],
      si.[Unit Price],
      si.[Lead Time Days];
    """,
    # Fallback: without movement (returns 0 stock)
    """
    SELECT
      si.[Stock Item Key]          AS stock_item_key,
      si.[Stock Item]              AS stock_item_name,
      si.[Unit Price]              AS unit_price,
      si.[Lead Time Days]          AS lead_time_days,
      CAST(0 AS FLOAT)             AS current_stock
    FROM [Dimension].[Stock Item] AS si
    WHERE si.[Stock Item Key] <> 0;
    """,
]

# 90-day demand velocity per stock item from Fact.Sale
WWI_DEMAND_VELOCITY: list[str] = [
    """
    SELECT
      s.[Stock Item Key]                   AS stock_item_key,
      SUM(CAST(s.[Quantity] AS FLOAT))    AS qty_sold_90d,
      COUNT_BIG(DISTINCT s.[WWI Invoice ID]) AS invoice_count_90d
    FROM [Fact].[Sale] AS s
    INNER JOIN [Dimension].[Date] AS d
      ON d.[Date Key] = s.[Delivery Date Key]
    WHERE d.[Date] >= DATEADD(day, -90, GETDATE())
      AND s.[Stock Item Key] <> 0
    GROUP BY s.[Stock Item Key];
    """,
    """
    SELECT
      s.[Stock Item Key]                   AS stock_item_key,
      SUM(CAST(s.[Quantity] AS FLOAT))    AS qty_sold_90d,
      COUNT_BIG(DISTINCT s.[WWI Invoice ID]) AS invoice_count_90d
    FROM [Fact].[Sale] AS s
    INNER JOIN [Dimension].[Date] AS d
      ON d.[Date Key] = s.[Invoice Date Key]
    WHERE d.[Date] >= DATEADD(day, -90, GETDATE())
      AND s.[Stock Item Key] <> 0
    GROUP BY s.[Stock Item Key];
    """,
]

# Supplier reliability data from Fact.Purchase
WWI_SUPPLIER_PERFORMANCE: list[str] = [
    """
    SELECT
      sup.[Supplier Key]                              AS supplier_key,
      sup.[Supplier]                                  AS supplier_name,
      sup.[Category]                                  AS category,
      COUNT_BIG(*)                                    AS total_orders,
      SUM(CAST(p.[Ordered Outers] AS FLOAT))         AS total_ordered_outers,
      SUM(CAST(p.[Received Outers] AS FLOAT))        AS total_received_outers,
      SUM(CASE WHEN p.[Is Order Finalized] = 1 THEN 1 ELSE 0 END) AS finalized_orders
    FROM [Dimension].[Supplier] AS sup
    INNER JOIN [Fact].[Purchase] AS p
      ON p.[Supplier Key] = sup.[Supplier Key]
    WHERE sup.[Supplier Key] <> 0
    GROUP BY
      sup.[Supplier Key],
      sup.[Supplier],
      sup.[Category]
    ORDER BY total_orders DESC;
    """,
    # Fallback: without Is Order Finalized
    """
    SELECT
      sup.[Supplier Key]                              AS supplier_key,
      sup.[Supplier]                                  AS supplier_name,
      sup.[Category]                                  AS category,
      COUNT_BIG(*)                                    AS total_orders,
      SUM(CAST(p.[Ordered Outers] AS FLOAT))         AS total_ordered_outers,
      SUM(CAST(p.[Received Outers] AS FLOAT))        AS total_received_outers,
      COUNT_BIG(*)                                    AS finalized_orders
    FROM [Dimension].[Supplier] AS sup
    INNER JOIN [Fact].[Purchase] AS p
      ON p.[Supplier Key] = sup.[Supplier Key]
    WHERE sup.[Supplier Key] <> 0
    GROUP BY
      sup.[Supplier Key],
      sup.[Supplier],
      sup.[Category]
    ORDER BY total_orders DESC;
    """,
]

# Single stock item detail for what-if scenario computation
WWI_STOCK_ITEM_DETAIL = """
SELECT
  si.[Stock Item Key]          AS stock_item_key,
  si.[Stock Item]              AS stock_item_name,
  si.[Unit Price]              AS unit_price,
  si.[Lead Time Days]          AS lead_time_days,
  COALESCE(SUM(CAST(m.[Quantity] AS FLOAT)), 0) AS current_stock
FROM [Dimension].[Stock Item] AS si
LEFT JOIN [Fact].[Movement] AS m
  ON m.[Stock Item Key] = si.[Stock Item Key]
WHERE si.[Stock Item Key] = :stock_item_key
GROUP BY
  si.[Stock Item Key],
  si.[Stock Item],
  si.[Unit Price],
  si.[Lead Time Days];
"""

WWI_STOCK_ITEM_DEMAND = """
SELECT
  SUM(CAST(s.[Quantity] AS FLOAT)) / NULLIF(90.0, 0) AS avg_daily_demand
FROM [Fact].[Sale] AS s
INNER JOIN [Dimension].[Date] AS d
  ON d.[Date Key] = s.[Delivery Date Key]
WHERE s.[Stock Item Key] = :stock_item_key
  AND d.[Date] >= DATEADD(day, -90, GETDATE());
"""