"""SQL-only data access for the supply module.

Same layering rules as DemandRepository: parameterised SQL only, returns
plain list[dict], no business metrics, no FastAPI imports.

Data sources:
    erp_stock_current  → current stock per (product, store)
    dim_stores         → is_warehouse + country split
    incoming_supply    → planned inbound deliveries by (sku, year, week)
    supply_master      → lead time + MOQ per (sku, supplier)
    v_sales_weekly_full     → fallback demand signal when forecasts is empty
    forecasts          → primary demand signal once seeded
    on_top_inputs      → KAM/CM commitments to subtract on exclusion
"""
from __future__ import annotations

from typing import Optional

from sqlalchemy import text

from backend.repositories.base import BaseRepository


class SupplyRepository(BaseRepository):

    # ------------------------------------------------------------------
    # Stock summary — dashboard headline
    # ------------------------------------------------------------------

    def get_stock_summary(self) -> dict:
        """Aggregates across erp_stock_current. Returns headline totals
        plus a per-(country, is_warehouse) breakdown for the dashboard bar.

        `total_purchase_value` and `total_retail_value` are derived live as
        `stock_qty × erp_costs.cost_price` (and × erp_prices.avg_sell_price)
        because `erp_stock_current.purchase_value` / `.retail_value` are
        currently empty in the ERP feed. Same formula Executive and Finance
        use, so the three pages agree on inventory €."""
        total_sql = """
            WITH lc AS (
                SELECT DISTINCT ON (product_id) product_id, cost_price::float AS cost_price
                FROM erp_costs ORDER BY product_id, valid_from DESC, id DESC
            ),
            lp AS (
                SELECT DISTINCT ON (product_id) product_id, avg_sell_price::float AS sell_price
                FROM erp_prices ORDER BY product_id, valid_from DESC, id DESC
            )
            SELECT
                COALESCE(SUM(esc.stock_qty), 0)::float AS total_units,
                COALESCE(SUM(esc.stock_qty * COALESCE(lc.cost_price, 0)), 0)::float
                    AS total_purchase_value,
                COALESCE(SUM(esc.stock_qty * COALESCE(lp.sell_price, 0)), 0)::float
                    AS total_retail_value,
                COALESCE(SUM(CASE WHEN s.is_warehouse
                                   THEN esc.stock_qty ELSE 0 END), 0)::float
                    AS warehouse_units,
                COALESCE(SUM(CASE WHEN NOT s.is_warehouse
                                   THEN esc.stock_qty ELSE 0 END), 0)::float
                    AS store_units,
                COUNT(DISTINCT esc.product_id) AS n_products,
                MAX(esc.updated_at)            AS updated_at
            FROM erp_stock_current esc
            LEFT JOIN dim_stores s ON s.id = esc.store_id
            LEFT JOIN lc           ON lc.product_id = esc.product_id
            LEFT JOIN lp           ON lp.product_id = esc.product_id
        """
        total = self.db.execute(text(total_sql)).mappings().first() or {}

        by_loc_sql = """
            WITH lc AS (
                SELECT DISTINCT ON (product_id) product_id, cost_price::float AS cost_price
                FROM erp_costs ORDER BY product_id, valid_from DESC, id DESC
            ),
            lp AS (
                SELECT DISTINCT ON (product_id) product_id, avg_sell_price::float AS sell_price
                FROM erp_prices ORDER BY product_id, valid_from DESC, id DESC
            )
            SELECT
                COALESCE(s.country, '—') AS country,
                COALESCE(s.is_warehouse, false) AS is_warehouse,
                COUNT(DISTINCT esc.store_id) AS n_stores,
                COALESCE(SUM(esc.stock_qty), 0)::float AS units,
                COALESCE(SUM(esc.stock_qty * COALESCE(lc.cost_price, 0)), 0)::float
                    AS purchase_value,
                COALESCE(SUM(esc.stock_qty * COALESCE(lp.sell_price, 0)), 0)::float
                    AS retail_value
            FROM erp_stock_current esc
            LEFT JOIN dim_stores s ON s.id = esc.store_id
            LEFT JOIN lc           ON lc.product_id = esc.product_id
            LEFT JOIN lp           ON lp.product_id = esc.product_id
            GROUP BY s.country, s.is_warehouse
            ORDER BY s.is_warehouse DESC, s.country
        """
        by_loc = [dict(r) for r in self.db.execute(text(by_loc_sql)).mappings().all()]
        return {**dict(total), "by_location": by_loc}

    # ------------------------------------------------------------------
    # Stock + demand + incoming per SKU — the projection grid source
    # ------------------------------------------------------------------

    def get_horizon_weeks(self, *, n_weeks: int = 13) -> list[tuple[int, int]]:
        """Return the next `n_weeks` (year, week) pairs.

        Start week is `MAX(year*100+week)+1` from sales data, BUT capped
        at the current ISO week so a stray future-dated row in
        sales_clean.csv (data error, test data, future-dated booking)
        can't push the projection horizon out beyond the forecast window.
        Falls back to current ISO week if v_sales_weekly_full is empty.
        """
        now_row = self.db.execute(text(
            "SELECT EXTRACT(ISOYEAR FROM now())::int AS y, "
            "EXTRACT(WEEK FROM now())::int AS w"
        )).mappings().first()
        current_yw = (int(now_row["y"]) * 100 + int(now_row["w"])) if now_row else 202620

        max_row = self.db.execute(text(
            "SELECT MAX(year * 100 + week) AS m FROM v_sales_weekly_full "
            "WHERE (year * 100 + week) <= :now_yw"
        ), {"now_yw": current_yw}).mappings().first()

        if max_row and max_row["m"] is not None:
            start_yw = int(max_row["m"]) + 1
        else:
            start_yw = current_yw
        return _step_weeks(start_yw, n_weeks)

    def get_stock_by_product(
        self,
        *,
        category: Optional[list[str]] = None,
        tier: Optional[list[str]] = None,
        xyz: Optional[list[str]] = None,
        sku: Optional[list[str]] = None,
        planned_only: bool = False,
        warehouse_only: bool = False,
    ) -> list[dict]:
        """Per-SKU current stock (summed across locations) joined with planning
        metadata. Filters mirror the demand module so the projection page
        shares the same FilterBar shape.

        planned_only=True restricts to SKUs that appear in sku_planning (~500
        SKUs with Gold/Silver/Bronze tier). This is the right default for
        planning tools (coverage workbook, alerts, order entry) — unplanned
        SKUs lack the demand-variability profile needed for safety-stock
        math. Reporting tools (inventory health, dashboard) leave this off.

        warehouse_only=True restricts the stock sum to warehouse locations
        (dim_stores.is_warehouse). Coverage Workbook + Order Proposal use
        this — store stock is already at the customer-facing endpoint and
        can't be redirected to fulfil another store's demand. Dashboard
        and Inventory Health keep both (total picture).
        """
        # Join shape changes based on `planned_only`. Inner join when the
        # filter is on so planned-only callers don't pay the cost of LEFT
        # JOIN + NULL filter; LEFT JOIN otherwise so unplanned SKUs survive.
        sp_join = (
            "JOIN sku_planning sp  ON sp.product_id = p.id"
            if planned_only else
            "LEFT JOIN sku_planning sp  ON sp.product_id = p.id"
        )
        where: list[str] = ["p.active IS NOT FALSE"]
        params: dict = {}
        if tier:
            clauses = []
            for i, t in enumerate(tier):
                k = f"sp_tier_{i}"
                clauses.append(f"sp.tier ILIKE :{k}")
                params[k] = f"%{t}%"
            where.append("(" + " OR ".join(clauses) + ")")
        if xyz:
            where.append("sp.total_xyz = ANY(:sp_xyz)")
            params["sp_xyz"] = xyz
        if category:
            where.append("c.name = ANY(:sp_cat)")
            params["sp_cat"] = category
        if sku:
            where.append("p.sku = ANY(:sp_sku)")
            params["sp_sku"] = sku

        # When `planned_only` is off, keep the "must have stock OR be planned"
        # constraint that already kept ghost SKUs out of the projection.
        extra_filter = (
            "" if planned_only else
            " AND (st.stock_qty IS NOT NULL OR sp.product_id IS NOT NULL)"
        )

        # WH-only filter joins dim_stores so SUM picks up only warehouse
        # locations (is_warehouse=TRUE). When off, sum across all locations
        # AND split into warehouse vs store buckets so callers can show the
        # breakdown without re-querying.
        stock_cte = (
            """SELECT esc.product_id,
                      COALESCE(SUM(esc.stock_qty), 0)::float AS stock_qty,
                      COALESCE(SUM(esc.stock_qty), 0)::float AS warehouse_stock,
                      0::float                               AS store_stock
               FROM erp_stock_current esc
               JOIN dim_stores ds ON ds.id = esc.store_id
               WHERE ds.is_warehouse
               GROUP BY esc.product_id"""
            if warehouse_only else
            """SELECT esc.product_id,
                      COALESCE(SUM(esc.stock_qty), 0)::float                                AS stock_qty,
                      COALESCE(SUM(esc.stock_qty) FILTER (WHERE ds.is_warehouse),     0)::float AS warehouse_stock,
                      COALESCE(SUM(esc.stock_qty) FILTER (WHERE NOT ds.is_warehouse), 0)::float AS store_stock
               FROM erp_stock_current esc
               JOIN dim_stores ds ON ds.id = esc.store_id
               GROUP BY esc.product_id"""
        )
        sql = f"""
            WITH stock AS (
                {stock_cte}
            )
            SELECT
                p.id AS product_id, p.sku, p.name,
                c.name AS category,
                sp.tier,
                sp.total_xyz AS xyz,
                sp.total_cv  AS total_cv,
                (sp.product_id IS NOT NULL) AS is_planned,
                COALESCE(st.stock_qty,       0)::float AS current_stock,
                COALESCE(st.warehouse_stock, 0)::float AS warehouse_stock,
                COALESCE(st.store_stock,     0)::float AS store_stock
            FROM dim_products p
            LEFT JOIN dim_categories c ON p.category_id = c.id
            {sp_join}
            LEFT JOIN stock st         ON st.product_id = p.id
            WHERE {' AND '.join(where)}{extra_filter}
            ORDER BY p.sku
        """
        rows = self.db.execute(text(sql), params).mappings().all()
        return [dict(r) for r in rows]

    # ------------------------------------------------------------------
    # Run rate — non-promo trailing average. Demand fallback for unplanned
    # SKUs and the sigma source for the store-overstock page.
    # ------------------------------------------------------------------

    def get_run_rates(
        self, *, product_ids: Optional[list[int]] = None, weeks: int = 13,
    ) -> dict[int, dict]:
        """Per-product avg non-promo weekly qty over the last `weeks` weeks
        of v_sales_weekly_full, with weeks flagged in erp_promo_weeks dropped.

        Returns {product_id: {avg_weekly, sigma_weekly, weeks_with_sales}}.
        `avg_weekly` is forced to 0 when fewer than 4 non-promo weeks exist
        (matches the store-overstock 'insufficient_history' rule). Sigma is
        the per-product std-dev of weekly qty across the non-promo weeks
        (matches the store-overstock σ source).
        """
        max_row = self.db.execute(
            text("SELECT MAX(year * 100 + week) AS m FROM v_sales_weekly_full")
        ).mappings().first()
        if not max_row or max_row["m"] is None:
            return {}
        max_yw = int(max_row["m"])
        cutoff_y = max_yw // 100
        cutoff_w = max_yw % 100 - (weeks - 1)
        while cutoff_w <= 0:
            cutoff_y -= 1
            cutoff_w += 52
        cutoff_yw = cutoff_y * 100 + cutoff_w

        where_pid = "" if product_ids is None else " AND v.product_id = ANY(:pids)"
        params: dict = {"cutoff": cutoff_yw, "max_yw": max_yw}
        if product_ids is not None:
            params["pids"] = product_ids

        # AVG and STDDEV over the non-promo weeks only. STDDEV_SAMP is NULL
        # for groups with one row — coalesce to 0 to match pandas behaviour.
        sql = f"""
            WITH promo AS (
                SELECT product_id, year, week
                FROM erp_promo_weeks
                WHERE is_erp_promo = true
            ),
            non_promo_weeks AS (
                SELECT v.product_id, v.year, v.week, v.qty_total::float AS qty
                FROM v_sales_weekly_full v
                LEFT JOIN promo p
                  ON p.product_id = v.product_id
                 AND p.year = v.year
                 AND p.week = v.week
                WHERE p.product_id IS NULL
                  AND v.year * 100 + v.week BETWEEN :cutoff AND :max_yw
                  {where_pid}
            )
            SELECT
                product_id,
                AVG(qty)::float                  AS avg_weekly,
                COALESCE(STDDEV_SAMP(qty), 0)::float AS sigma_weekly,
                COUNT(*)                         AS weeks_with_sales
            FROM non_promo_weeks
            GROUP BY product_id
        """
        rows = self.db.execute(text(sql), params).mappings().all()
        out: dict[int, dict] = {}
        for r in rows:
            n = int(r["weeks_with_sales"] or 0)
            # < 4 non-promo weeks → insufficient signal. Mirror Streamlit's
            # store-overstock MIN_WEEKS rule by zeroing avg, but keep the
            # row so callers can still see the count.
            if n < 4:
                out[int(r["product_id"])] = {
                    "avg_weekly": 0.0,
                    "sigma_weekly": 0.0,
                    "weeks_with_sales": n,
                }
                continue
            out[int(r["product_id"])] = {
                "avg_weekly":   float(r["avg_weekly"] or 0),
                "sigma_weekly": float(r["sigma_weekly"] or 0),
                "weeks_with_sales": n,
            }
        return out

    # ------------------------------------------------------------------
    # Per-SKU backtest FA — used by inventory health to drive safety stock
    # ------------------------------------------------------------------

    def get_per_sku_backtest_fa(
        self, *, min_weeks: int = 6,
    ) -> dict[int, dict]:
        """Per-SKU forecast accuracy from backtest_results using the
        totals-approach (sumF, sumA over the SKU). Returns FA as a fraction
        (e.g. 0.84) clipped to [0.30, 0.95] when n_weeks >= min_weeks. Each
        entry: {fa, n_weeks}. SKUs not in backtest_results are absent."""
        sql = """
            SELECT
                product_id,
                COUNT(*) AS n_weeks,
                SUM(forecast)::float AS sum_f,
                SUM(actual)::float   AS sum_a
            FROM backtest_results
            WHERE actual IS NOT NULL AND actual > 0
            GROUP BY product_id
        """
        rows = self.db.execute(text(sql)).mappings().all()
        out: dict[int, dict] = {}
        for r in rows:
            n = int(r["n_weeks"] or 0)
            if n < min_weeks:
                continue
            a = float(r["sum_a"] or 0)
            f = float(r["sum_f"] or 0)
            if a <= 0:
                continue
            fa_raw = max(0.0, 1 - abs(f - a) / a)
            fa_clipped = max(0.30, min(0.95, fa_raw))
            out[int(r["product_id"])] = {"fa": fa_clipped, "n_weeks": n}
        return out

    # ------------------------------------------------------------------
    # Per-product cost / retail-value lookup — used for inventory-health
    # excess-€ valuation. Returns latest erp_costs.cost_price + a fallback
    # to erp_prices.avg_sell_price (matches the Streamlit cost vs sell
    # downgrade path).
    # ------------------------------------------------------------------

    def get_unit_values(
        self, *, product_ids: Optional[list[int]] = None,
    ) -> dict[int, dict]:
        where = "" if product_ids is None else " WHERE product_id = ANY(:pids)"
        params = {} if product_ids is None else {"pids": product_ids}
        sql = f"""
            WITH lc AS (
                SELECT DISTINCT ON (product_id)
                    product_id, cost_price::float AS cost_price
                FROM erp_costs
                {where}
                ORDER BY product_id, valid_from DESC, id DESC
            ),
            lp AS (
                SELECT DISTINCT ON (product_id)
                    product_id, avg_sell_price::float AS avg_sell_price
                FROM erp_prices
                {where}
                ORDER BY product_id, valid_from DESC, id DESC
            )
            SELECT
                COALESCE(lc.product_id, lp.product_id) AS product_id,
                lc.cost_price,
                lp.avg_sell_price
            FROM lc
            FULL OUTER JOIN lp ON lc.product_id = lp.product_id
        """
        rows = self.db.execute(text(sql), params).mappings().all()
        return {
            int(r["product_id"]): {
                "cost_price":     float(r["cost_price"])     if r.get("cost_price")     is not None else None,
                "avg_sell_price": float(r["avg_sell_price"]) if r.get("avg_sell_price") is not None else None,
            }
            for r in rows if r.get("product_id") is not None
        }

    # ------------------------------------------------------------------
    # Per-store, per-SKU sales — used by store overstock for σ and weeks
    # ------------------------------------------------------------------

    def get_store_sku_non_promo_stats(
        self, *, country: str = "HR",
    ) -> list[dict]:
        """Per (product, store) avg + σ + non-promo week count over the
        full sales history. Filters to HR retail (RCM channel via store
        country). Promo weeks are dropped using the same join as
        get_run_rates. Used only by the store overstock page."""
        sql = """
            WITH promo AS (
                SELECT product_id, year, week
                FROM erp_promo_weeks
                WHERE is_erp_promo = true
            ),
            -- Weekly aggregate per (product, store) — derived from
            -- erp_transactions because v_sales_weekly_full is product-level only.
            -- Channel filter goes through erp_transactions.channel_map_id
            -- → lookup_channel_map.channel = 'retail' (the doc_type column
            -- isn't joined directly here; the FK + channel='retail' captures
            -- the same set of receipts as Streamlit's tip_dok='RCM' filter).
            weekly AS (
                SELECT
                    t.product_id,
                    t.store_id,
                    EXTRACT(ISOYEAR FROM t.transaction_date)::int AS year,
                    EXTRACT(WEEK    FROM t.transaction_date)::int AS week,
                    SUM(t.quantity)::float AS qty
                FROM erp_transactions t
                JOIN dim_stores s ON s.id = t.store_id
                JOIN lookup_channel_map cm ON cm.id = t.channel_map_id
                WHERE s.country = :country
                  AND s.is_warehouse = false
                  AND cm.channel = 'retail'
                GROUP BY t.product_id, t.store_id,
                         EXTRACT(ISOYEAR FROM t.transaction_date)::int,
                         EXTRACT(WEEK    FROM t.transaction_date)::int
            ),
            non_promo AS (
                SELECT w.product_id, w.store_id, w.year, w.week, w.qty
                FROM weekly w
                LEFT JOIN promo p
                  ON p.product_id = w.product_id
                 AND p.year = w.year
                 AND p.week = w.week
                WHERE p.product_id IS NULL
            )
            SELECT
                product_id, store_id,
                AVG(qty)::float                  AS avg_weekly,
                COALESCE(STDDEV_SAMP(qty), 0)::float AS sigma,
                COUNT(*)                         AS weeks_with_sales
            FROM non_promo
            GROUP BY product_id, store_id
        """
        rows = self.db.execute(text(sql), {"country": country}).mappings().all()
        return [dict(r) for r in rows]

    def get_store_stock(
        self, *, country: str = "HR",
    ) -> list[dict]:
        """Per (product, store) on-hand from erp_stock_current. Used by the
        store overstock page to pair stock with the σ stats. HR stores only,
        warehouses excluded."""
        sql = """
            SELECT
                esc.product_id,
                esc.store_id,
                s.unit_code AS store_code,
                COALESCE(s.name, s.unit_code) AS store_name,
                SUM(esc.stock_qty)::float AS on_hand
            FROM erp_stock_current esc
            JOIN dim_stores s ON s.id = esc.store_id
            WHERE s.country = :country AND s.is_warehouse = false
            GROUP BY esc.product_id, esc.store_id, s.unit_code, s.name
        """
        rows = self.db.execute(text(sql), {"country": country}).mappings().all()
        return [dict(r) for r in rows]

    def get_avg_weekly_demand(
        self, *, product_ids: list[int], weeks_back: int = 13,
    ) -> dict[int, float]:
        """Recent weekly avg of qty_total per product — **non-promo run rate**
        over the last `weeks_back` ISO weeks (default 13). Aligns with the
        rest of the app: Finance, Supply Inventory Health, and Executive all
        use the same 13w-non-promo fallback for SKUs without a live forecast.

        Promo weeks are excluded via `erp_promo_weeks.is_erp_promo`. If a SKU
        has zero non-promo weeks in the window the value falls through to the
        unfiltered avg so it doesn't collapse to zero."""
        if not product_ids:
            return {}
        max_row = self.db.execute(
            text("SELECT MAX(year * 100 + week) AS m FROM v_sales_weekly_full")
        ).mappings().first()
        if not max_row or max_row["m"] is None:
            return {}
        max_yw = int(max_row["m"])
        cutoff_y = max_yw // 100
        cutoff_w = max_yw % 100 - (weeks_back - 1)
        while cutoff_w <= 0:
            cutoff_y -= 1
            cutoff_w += 52
        cutoff_yw = cutoff_y * 100 + cutoff_w
        sql = """
            WITH win AS (
                SELECT v.product_id, v.qty_total,
                       COALESCE(epw.is_erp_promo, FALSE) AS is_promo
                FROM v_sales_weekly_full v
                LEFT JOIN erp_promo_weeks epw
                    ON epw.product_id = v.product_id
                   AND epw.year       = v.year
                   AND epw.week       = v.week
                WHERE v.product_id = ANY(:pids)
                  AND v.year*100 + v.week BETWEEN :cutoff AND :max_yw
            ),
            non_promo AS (
                SELECT product_id, AVG(qty_total)::float AS avg_np,
                       COUNT(*)::int AS n_np
                FROM win WHERE NOT is_promo
                GROUP BY product_id
            ),
            all_weeks AS (
                SELECT product_id, AVG(qty_total)::float AS avg_all
                FROM win GROUP BY product_id
            )
            SELECT a.product_id,
                   COALESCE(np.avg_np, a.avg_all)::float AS avg_qty
            FROM all_weeks a
            LEFT JOIN non_promo np ON np.product_id = a.product_id
        """
        rows = self.db.execute(
            text(sql),
            {"pids": product_ids, "cutoff": cutoff_yw, "max_yw": max_yw},
        ).mappings().all()
        return {int(r["product_id"]): float(r["avg_qty"] or 0) for r in rows}

    def get_forecast_demand_horizon(
        self, *, product_ids: list[int], horizon: list[tuple[int, int]],
    ) -> dict[tuple[int, int, int], float]:
        """Demand per (product, year, week) from forecasts.total for the
        latest run. Returns {} when forecasts is empty so callers can fall
        back to the recent-avg signal."""
        if not product_ids or not horizon:
            return {}
        yws = [y * 100 + w for (y, w) in horizon]
        sql = """
            SELECT product_id, year, week, total::float AS demand
            FROM forecasts
            WHERE run_id = (SELECT MAX(run_id) FROM forecasts)
              AND product_id = ANY(:pids)
              AND year * 100 + week = ANY(:yws)
        """
        rows = self.db.execute(
            text(sql), {"pids": product_ids, "yws": yws},
        ).mappings().all()
        return {
            (int(r["product_id"]), int(r["year"]), int(r["week"])): float(r["demand"] or 0)
            for r in rows
        }

    def get_incoming_horizon(
        self, *, product_ids: list[int], horizon: list[tuple[int, int]],
    ) -> dict[tuple[int, int, int], float]:
        """Sum incoming_supply.quantity per (product, year, week) for the
        horizon. Status filter excludes 'cancelled' rows (defensive — the
        column is optional and may be NULL in dev data)."""
        if not product_ids or not horizon:
            return {}
        yws = [y * 100 + w for (y, w) in horizon]
        sql = """
            SELECT product_id, year, week,
                   SUM(quantity)::float AS incoming
            FROM incoming_supply
            WHERE product_id = ANY(:pids)
              AND year * 100 + week = ANY(:yws)
              AND COALESCE(LOWER(status), '') <> 'cancelled'
            GROUP BY product_id, year, week
        """
        rows = self.db.execute(
            text(sql), {"pids": product_ids, "yws": yws},
        ).mappings().all()
        return {
            (int(r["product_id"]), int(r["year"]), int(r["week"])): float(r["incoming"] or 0)
            for r in rows
        }

    # ------------------------------------------------------------------
    # Supply master — lead times + MOQ + supplier name per SKU
    # ------------------------------------------------------------------

    def get_supply_master(
        self, *, product_ids: Optional[list[int]] = None,
    ) -> dict[int, dict]:
        """Latest supply_master row per product. The table doesn't have a
        validity timestamp so we use max(id) as the recency proxy. Joined to
        dim_suppliers for the display name. Returns {} when scoped to a
        product_ids list that has no matches."""
        params: dict = {}
        where_sql = ""
        if product_ids:
            where_sql = "WHERE sm.product_id = ANY(:pids)"
            params["pids"] = product_ids
        sql = f"""
            SELECT DISTINCT ON (sm.product_id)
                sm.product_id,
                sm.lead_time_weeks::float AS lead_time_weeks,
                sm.moq::float             AS moq,
                s.name                    AS supplier
            FROM supply_master sm
            LEFT JOIN dim_suppliers s ON s.id = sm.supplier_id
            {where_sql}
            ORDER BY sm.product_id, sm.id DESC
        """
        rows = self.db.execute(text(sql), params).mappings().all()
        return {int(r["product_id"]): dict(r) for r in rows}

    # ------------------------------------------------------------------
    # Order proposals — bulk insert
    # ------------------------------------------------------------------

    def create_order_proposals(
        self, *, proposals: list[dict],
    ) -> list[dict]:
        """Bulk insert into order_proposals. Each dict needs product_id,
        year_week, proposed_qty, status. Returns one dict per input row
        with `sku` echoed for the frontend's per-row feedback. Errors are
        caught per-row so a single bad row doesn't kill the batch."""
        out: list[dict] = []
        for p in proposals:
            try:
                row = self.db.execute(
                    text("""
                        INSERT INTO order_proposals
                            (product_id, year_week, proposed_qty, status)
                        VALUES
                            (:pid, :yw, :qty, :status)
                        RETURNING id
                    """),
                    {
                        "pid":    p["product_id"],
                        "yw":     p["year_week"],
                        "qty":    p["proposed_qty"],
                        "status": p.get("status", "draft"),
                    },
                ).mappings().first()
                out.append({
                    "sku":       p.get("sku"),
                    "year_week": p["year_week"],
                    "id":        int(row["id"]) if row else None,
                    "status":    "created",
                    "message":   None,
                })
            except Exception as e:
                self.db.rollback()
                out.append({
                    "sku":       p.get("sku"),
                    "year_week": p["year_week"],
                    "id":        None,
                    "status":    "error",
                    "message":   str(e)[:200],
                })
        self.db.commit()
        return out

    def resolve_skus_to_ids(self, skus: list[str]) -> dict[str, int]:
        """Map SKU codes → product_id, skipping unknowns. Used by the
        order-proposal POST handler so the API surface stays sku-based."""
        if not skus:
            return {}
        sql = "SELECT sku, id FROM dim_products WHERE sku = ANY(:s)"
        rows = self.db.execute(text(sql), {"s": skus}).mappings().all()
        return {r["sku"]: int(r["id"]) for r in rows}

    # ------------------------------------------------------------------
    # On-top adjustments — used by the KAM exclusion logic
    # ------------------------------------------------------------------

    def get_on_top_by_kam(self) -> list[dict]:
        """Per-submitter rollup with the list of distinct buyer names they
        committed for. Empty when on_top_inputs is empty (current state)."""
        sql = """
            SELECT
                ot.submitted_by_id,
                COALESCE(NULLIF(u.display_name, ''), u.username, '(unassigned)') AS person,
                u.role,
                LOWER(COALESCE(ot.channel, '')) AS channel,
                COUNT(*)             AS n_sku_weeks,
                SUM(ot.quantity)::float AS qty_total,
                COALESCE(ARRAY_AGG(DISTINCT ot.buyer) FILTER (WHERE ot.buyer IS NOT NULL), ARRAY[]::varchar[]) AS buyers
            FROM on_top_inputs ot
            LEFT JOIN users u ON u.id = ot.submitted_by_id
            GROUP BY ot.submitted_by_id, person, u.role, LOWER(COALESCE(ot.channel, ''))
            ORDER BY qty_total DESC
        """
        rows = self.db.execute(text(sql)).mappings().all()
        return [dict(r) for r in rows]

    def get_on_top_quantities(
        self,
        *,
        product_ids: list[int],
        horizon: list[tuple[int, int]],
        excluded_kams: Optional[list[str]] = None,
        excluded_buyers: Optional[list[str]] = None,
    ) -> dict[tuple[int, int, int], float]:
        """Sum on-top quantities per (product, year, week) belonging to the
        excluded KAM/buyer combinations — so the service can subtract them
        from demand. Returns {} when on_top_inputs is empty or nothing was
        excluded."""
        if not (excluded_kams or excluded_buyers) or not product_ids or not horizon:
            return {}
        yws = [y * 100 + w for (y, w) in horizon]
        where: list[str] = [
            "ot.product_id = ANY(:pids)",
            "ot.year_week = ANY(:yws)",
        ]
        params: dict = {"pids": product_ids, "yws": yws}
        person_clauses: list[str] = []
        if excluded_kams:
            person_clauses.append(
                "COALESCE(NULLIF(u.display_name, ''), u.username) = ANY(:ex_kams)"
            )
            params["ex_kams"] = excluded_kams
        if excluded_buyers:
            person_clauses.append("ot.buyer = ANY(:ex_buyers)")
            params["ex_buyers"] = excluded_buyers
        where.append("(" + " OR ".join(person_clauses) + ")")

        sql = f"""
            SELECT ot.product_id,
                   (ot.year_week / 100) AS year,
                   (ot.year_week % 100) AS week,
                   SUM(ot.quantity)::float AS qty
            FROM on_top_inputs ot
            LEFT JOIN users u ON u.id = ot.submitted_by_id
            WHERE {' AND '.join(where)}
            GROUP BY ot.product_id, ot.year_week
        """
        rows = self.db.execute(text(sql), params).mappings().all()
        return {
            (int(r["product_id"]), int(r["year"]), int(r["week"])): float(r["qty"] or 0)
            for r in rows
        }


    # ------------------------------------------------------------------
    # MOQ Analysis — planned SKUs with supply_master + run rates
    # ------------------------------------------------------------------

    def get_moq_analysis(
        self,
        *,
        tier: Optional[list[str]] = None,
        category: Optional[list[str]] = None,
    ) -> list[dict]:
        """Per-SKU MOQ data for planned SKUs. Joins supply_master with
        13-week non-promo run rates and current stock. weeks_per_moq
        and cover_now are computed in SQL so sorting works correctly."""
        where_clauses: list[str] = []
        params: dict = {}
        if tier:
            where_clauses.append("UPPER(sp.tier) LIKE ANY(:tier_pats)")
            params["tier_pats"] = [f"%{t.upper()}%" for t in tier]
        if category:
            where_clauses.append("dc.name = ANY(:cats)")
            params["cats"] = category
        where_sql = ("WHERE " + " AND ".join(where_clauses)) if where_clauses else ""

        sql = f"""
            WITH max_yw AS (
                SELECT MAX(year * 100 + week) AS m FROM v_sales_weekly_full
            ),
            run_rates AS (
                SELECT
                    vsw.product_id,
                    COUNT(*) FILTER (WHERE NOT COALESCE(epw.is_erp_promo, false)) AS np_weeks,
                    COALESCE(
                        CASE
                            WHEN COUNT(*) FILTER (WHERE NOT COALESCE(epw.is_erp_promo, false)) >= 4
                            THEN AVG(vsw.qty_total) FILTER (WHERE NOT COALESCE(epw.is_erp_promo, false))
                            ELSE 0
                        END, 0
                    )::float AS avg_weekly
                FROM v_sales_weekly_full vsw
                CROSS JOIN max_yw mx
                LEFT JOIN erp_promo_weeks epw
                    ON epw.product_id = vsw.product_id
                    AND epw.year = vsw.year AND epw.week = vsw.week
                WHERE vsw.year * 100 + vsw.week > mx.m - 13
                GROUP BY vsw.product_id
            ),
            stock AS (
                SELECT product_id, COALESCE(SUM(stock_qty), 0)::float AS on_hand
                FROM erp_stock_current
                GROUP BY product_id
            )
            SELECT
                p.sku,
                p.name,
                sp.tier,
                dc.name                                          AS category,
                ds.name                                          AS supplier,
                sm.lead_time_weeks::float,
                sm.moq::float,
                COALESCE(rr.avg_weekly, 0)::float               AS avg_weekly,
                COALESCE(st.on_hand, 0)::float                  AS on_hand,
                CASE WHEN COALESCE(rr.avg_weekly, 0) > 0
                     THEN COALESCE(st.on_hand, 0) / rr.avg_weekly
                     ELSE NULL END::float                        AS cover_now,
                CASE WHEN sm.moq IS NOT NULL AND COALESCE(rr.avg_weekly, 0) > 0
                     THEN sm.moq::float / rr.avg_weekly
                     ELSE NULL END::float                        AS weeks_per_moq,
                CASE WHEN sm.moq IS NOT NULL AND ec.cost_price IS NOT NULL
                     THEN sm.moq::float * ec.cost_price::float
                     ELSE NULL END::float                        AS moq_eur_cost
            FROM sku_planning sp
            JOIN dim_products p   ON p.id = sp.product_id
            LEFT JOIN dim_categories dc ON dc.id = p.category_id
            LEFT JOIN supply_master sm  ON sm.product_id = sp.product_id
            LEFT JOIN dim_suppliers ds  ON ds.id = sm.supplier_id
            LEFT JOIN run_rates rr      ON rr.product_id = sp.product_id
            LEFT JOIN stock st          ON st.product_id = sp.product_id
            LEFT JOIN erp_costs ec      ON ec.product_id = sp.product_id
            {where_sql}
            ORDER BY sp.tier,
                     CASE WHEN sm.moq IS NOT NULL AND COALESCE(rr.avg_weekly, 0) > 0
                          THEN sm.moq::float / rr.avg_weekly ELSE NULL END ASC NULLS LAST,
                     sm.moq DESC NULLS LAST
        """
        rows = self.db.execute(text(sql), params).mappings().all()
        return [dict(r) for r in rows]

    # ------------------------------------------------------------------
    # Logistics — incoming supply timeline by supplier
    # ------------------------------------------------------------------

    def get_logistics(self, *, min_year_week: Optional[int] = None) -> list[dict]:
        """All incoming_supply rows joined with product + supplier data.
        Optionally filtered to year_week >= min_year_week so past receipts
        can be excluded."""
        params: dict = {}
        yw_clause = ""
        if min_year_week is not None:
            yw_clause = "WHERE ins.year * 100 + ins.week >= :min_yw"
            params["min_yw"] = min_year_week

        sql = f"""
            SELECT
                p.sku,
                p.name,
                sp.tier,
                COALESCE(ds.name, p_sup.name, '— unknown —') AS supplier,
                ins.year,
                ins.week,
                ins.year * 100 + ins.week                    AS year_week,
                ins.quantity::float,
                ins.status,
                CASE WHEN ec.cost_price IS NOT NULL
                     THEN (ins.quantity * ec.cost_price)::float
                     ELSE NULL END                            AS purchase_value
            FROM incoming_supply ins
            JOIN dim_products p       ON p.id = ins.product_id
            LEFT JOIN sku_planning sp ON sp.product_id = ins.product_id
            LEFT JOIN supply_master sm ON sm.product_id = ins.product_id
            LEFT JOIN dim_suppliers ds ON ds.id = sm.supplier_id
            LEFT JOIN dim_suppliers p_sup ON p_sup.id = p.supplier_id
            LEFT JOIN erp_costs ec    ON ec.product_id = ins.product_id
            {yw_clause}
            ORDER BY ins.year, ins.week, supplier, p.sku
        """
        rows = self.db.execute(text(sql), params).mappings().all()
        return [dict(r) for r in rows]

    # ------------------------------------------------------------------
    # Costs — erp_costs + erp_prices per product
    # ------------------------------------------------------------------

    def get_costs(
        self,
        *,
        tier: Optional[list[str]] = None,
        category: Optional[list[str]] = None,
        planned_only: bool = False,
    ) -> list[dict]:
        """Per-SKU cost + price + margin data. Includes current on-hand
        so the planner can see total capital tied up. planned_only=True
        restricts to sku_planning rows (Gold/Silver/Bronze)."""
        where_clauses: list[str] = ["(ec.cost_price IS NOT NULL OR ep.avg_sell_price IS NOT NULL)"]
        join_type = "INNER" if planned_only else "LEFT"
        params: dict = {}
        if tier:
            where_clauses.append("UPPER(sp.tier) LIKE ANY(:tier_pats)")
            params["tier_pats"] = [f"%{t.upper()}%" for t in tier]
        if category:
            where_clauses.append("dc.name = ANY(:cats)")
            params["cats"] = category
        where_sql = "WHERE " + " AND ".join(where_clauses)

        sql = f"""
            WITH stock AS (
                SELECT product_id, COALESCE(SUM(stock_qty), 0)::float AS on_hand
                FROM erp_stock_current
                GROUP BY product_id
            )
            SELECT
                p.sku,
                p.name,
                dc.name                          AS category,
                sp.tier,
                (sp.product_id IS NOT NULL)      AS is_planned,
                ds.name                          AS supplier,
                ec.cost_price::float,
                ep.avg_sell_price::float,
                CASE WHEN ep.avg_sell_price > 0 AND ec.cost_price IS NOT NULL
                     THEN ((ep.avg_sell_price - ec.cost_price) / ep.avg_sell_price * 100)::float
                     ELSE NULL END               AS margin_pct,
                COALESCE(st.on_hand, 0)::float   AS on_hand,
                CASE WHEN ec.cost_price IS NOT NULL
                     THEN (COALESCE(st.on_hand, 0) * ec.cost_price)::float
                     ELSE NULL END               AS stock_value_cost,
                CASE WHEN ep.avg_sell_price IS NOT NULL
                     THEN (COALESCE(st.on_hand, 0) * ep.avg_sell_price)::float
                     ELSE NULL END               AS stock_value_sell
            FROM dim_products p
            {join_type} JOIN sku_planning sp ON sp.product_id = p.id
            LEFT JOIN dim_categories dc ON dc.id = p.category_id
            LEFT JOIN dim_suppliers ds  ON ds.id = p.supplier_id
            LEFT JOIN erp_costs ec      ON ec.product_id = p.id
            LEFT JOIN erp_prices ep     ON ep.product_id = p.id
            LEFT JOIN stock st          ON st.product_id = p.id
            {where_sql}
            ORDER BY dc.name NULLS LAST, sp.tier NULLS LAST, p.sku
        """
        rows = self.db.execute(text(sql), params).mappings().all()
        return [dict(r) for r in rows]

    # ------------------------------------------------------------------
    # Settings — read supply_master for planned SKUs
    # ------------------------------------------------------------------

    def get_settings(self) -> list[dict]:
        """All planned SKUs with their supply_master values (or NULL when
        no supply_master row exists yet). Ordered by tier + sku."""
        sql = """
            SELECT
                p.sku,
                p.name,
                dc.name     AS category,
                sp.tier,
                ds.name     AS supplier,
                ds.code     AS supplier_code,
                sm.id       AS supply_master_id,
                sm.lead_time_weeks::float,
                sm.moq::float
            FROM sku_planning sp
            JOIN dim_products p   ON p.id = sp.product_id
            LEFT JOIN dim_categories dc ON dc.id = p.category_id
            LEFT JOIN supply_master sm  ON sm.product_id = sp.product_id
            LEFT JOIN dim_suppliers ds  ON ds.id = sm.supplier_id
            ORDER BY sp.tier, p.sku
        """
        rows = self.db.execute(text(sql)).mappings().all()
        return [dict(r) for r in rows]

    def upsert_supply_master(self, updates: list[dict]) -> dict:
        """Upsert lead_time_weeks and moq in supply_master for each SKU.
        Returns counts of updated / inserted / errored rows."""
        n_updated = n_inserted = n_errors = 0
        errors: list[str] = []
        for item in updates:
            sku = item.get("sku", "")
            lt  = item.get("lead_time_weeks")
            moq = item.get("moq")
            if lt is None and moq is None:
                continue
            try:
                pid_row = self.db.execute(
                    text("SELECT id FROM dim_products WHERE sku = :sku"),
                    {"sku": sku},
                ).mappings().first()
                if pid_row is None:
                    errors.append(f"{sku}: SKU not found")
                    n_errors += 1
                    continue
                pid = int(pid_row["id"])
                existing = self.db.execute(
                    text("SELECT id FROM supply_master WHERE product_id = :pid"),
                    {"pid": pid},
                ).mappings().first()
                if existing:
                    sets: list[str] = []
                    params: dict = {"pid": pid}
                    if lt is not None:
                        sets.append("lead_time_weeks = :lt")
                        params["lt"] = lt
                    if moq is not None:
                        sets.append("moq = :moq")
                        params["moq"] = moq
                    self.db.execute(
                        text(f"UPDATE supply_master SET {', '.join(sets)} WHERE product_id = :pid"),
                        params,
                    )
                    n_updated += 1
                else:
                    self.db.execute(
                        text("""
                            INSERT INTO supply_master (product_id, lead_time_weeks, moq)
                            VALUES (:pid, :lt, :moq)
                        """),
                        {"pid": pid, "lt": lt, "moq": moq},
                    )
                    n_inserted += 1
            except Exception as exc:
                errors.append(f"{sku}: {exc}")
                n_errors += 1
        if n_updated + n_inserted > 0:
            self.db.commit()
        return {
            "n_updated": n_updated,
            "n_inserted": n_inserted,
            "n_errors": n_errors,
            "errors": errors,
        }


    # ------------------------------------------------------------------
    # Order Proposal — supplier-scoped (s, S) ordering
    # ------------------------------------------------------------------

    def get_suppliers_for_proposal(self) -> list[dict]:
        """Suppliers with at least one **planned** SKU + a populated lead_time.
        Used to populate the Order Proposal dropdown."""
        sql = """
            SELECT ds.id              AS supplier_id,
                   ds.name            AS supplier_name,
                   COUNT(DISTINCT sp.product_id) AS n_planned_skus,
                   AVG(sm.lead_time_weeks)::float AS avg_lead_time_weeks
            FROM dim_suppliers ds
            JOIN supply_master sm  ON sm.supplier_id = ds.id
            JOIN sku_planning  sp  ON sp.product_id  = sm.product_id
            WHERE ds.name IS NOT NULL
              AND ds.name <> ''
              AND sm.lead_time_weeks IS NOT NULL
              -- Postgres NUMERIC allows literal NaN values (different from NULL).
              -- They compare > 0 so the standard > 0 check doesn't catch them.
              AND sm.lead_time_weeks::text <> 'NaN'
              AND sm.lead_time_weeks > 0
            GROUP BY ds.id, ds.name
            ORDER BY n_planned_skus DESC, ds.name
        """
        rows = self.db.execute(text(sql)).mappings().all()
        return [dict(r) for r in rows]

    def get_supplier_planned_skus(
        self, *, supplier_id: int,
    ) -> list[dict]:
        """Planned-SKU rows for one supplier — everything the order-proposal
        service needs in one query: SKU, name, tier, category, lead_time,
        MOQ (from supply_master), cost_price, current WH stock.

        Returns [] if the supplier has no planned SKUs."""
        sql = """
            WITH wh_stock AS (
                SELECT esc.product_id,
                       COALESCE(SUM(esc.stock_qty), 0)::float AS wh_qty
                FROM erp_stock_current esc
                JOIN dim_stores ds ON ds.id = esc.store_id
                WHERE ds.is_warehouse
                GROUP BY esc.product_id
            )
            SELECT p.id                            AS product_id,
                   p.sku,
                   p.name,
                   sp.tier,
                   dc.name                         AS category,
                   sm.lead_time_weeks::float       AS lead_time_weeks,
                   sm.moq::float                   AS moq,
                   ec.cost_price::float            AS cost_price,
                   COALESCE(ws.wh_qty, 0)::float   AS current_wh_stock
            FROM sku_planning sp
            JOIN dim_products p     ON p.id = sp.product_id
            JOIN supply_master sm   ON sm.product_id = sp.product_id
            LEFT JOIN dim_categories dc ON dc.id = p.category_id
            LEFT JOIN erp_costs ec  ON ec.product_id = sp.product_id
            LEFT JOIN wh_stock  ws  ON ws.product_id = sp.product_id
            WHERE sm.supplier_id = :sid
              AND sm.lead_time_weeks IS NOT NULL
              AND sm.lead_time_weeks::text <> 'NaN'
              AND sm.lead_time_weeks > 0
            ORDER BY p.sku
        """
        rows = self.db.execute(text(sql), {"sid": supplier_id}).mappings().all()
        return [dict(r) for r in rows]


# ---------------------------------------------------------------------------
# Helpers (module-level so tests can poke at them without instantiating the
# repo — week-stepping logic doesn't need a DB session).
# ---------------------------------------------------------------------------


def _step_weeks(start_yw: int, n: int) -> list[tuple[int, int]]:
    """Generate `n` consecutive (year, ISO-week) pairs starting at
    start_yw = year*100+week. Week 53 is preserved on years that have it
    in the source data — but for simplicity we wrap at 52, which matches
    Streamlit's behaviour on the existing pipeline (no W53 in current data).
    """
    out: list[tuple[int, int]] = []
    y, w = start_yw // 100, start_yw % 100
    for _ in range(n):
        out.append((y, w))
        w += 1
        if w > 52:
            w = 1
            y += 1
    return out