generate_report.py

"""
generate_report.py  –  Fill Rate Report Generator
===================================================

RULES
-----
1. SOF documents (Doc. Code starts with "SOF …") are ignored entirely.
2. Only EOF rows from February 2026 onward appear in the report.
   (Bulk production lots from before Feb 2026 are still used as a lookup
    so that FGs filled in Feb+ can be linked back to older bulk batches.)
3. Only rows that are NEITHER bulk-production EOF NOR linked FG go to
   the "Other & Intermediates" sheet — i.e. colorantes, bases, solvents,
   and other non-standard article codes.  FG rows that are successfully
   linked to a bulk batch are shown ONLY in the Fill Rate Report sheet.

LOGIC
-----
  • Bulk PRODUCTION rows  : Article matches XXX-XXXX  AND  Doc. Code starts with EOF
  • FG rows               : Article matches XXX-XXXX-XXX AND Doc. Code starts with EOF
  • Link chain            :
        FG lot  →  bulk production lot (same lot, article base must match)
        e.g.  206-0003-020  lot 26FEB…  →  bulk 206-0003  lot 26FEB…

  Anything else (colorantes, bases, solvents — article codes that do not
  match either pattern) goes to the Other & Intermediates sheet.
"""

import io
import json
import os
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from datetime import datetime


# ── Manual MO overrides ───────────────────────────────────────────────────────
# Loaded once at import time from mo_overrides.json (same directory as this file).
# Keys = FG MO (str), values = {"bulk_mo": "XXXXXXX" | null, "note": "..."}
def _load_overrides() -> dict:
    path = os.path.join(os.path.dirname(__file__), 'mo_overrides.json')
    try:
        with open(path) as f:
            data = json.load(f)
        return {k: v for k, v in data.get('overrides', {}).items() if v.get('bulk_mo')}
    except FileNotFoundError:
        return {}

MO_OVERRIDES = _load_overrides()   # {fg_mo_str: {bulk_mo: str, note: str}}


def _load_comments() -> dict:
    """Load comments.json → {bulk_mo_str: comment_text}"""
    path = os.path.join(os.path.dirname(__file__), 'comments.json')
    try:
        with open(path) as f:
            data = json.load(f)
        return {k: v for k, v in data.get('comments', {}).items() if v}
    except FileNotFoundError:
        return {}

MO_COMMENTS = _load_comments()    # {bulk_mo_str: comment_text}


def _load_article_aliases() -> dict:
    """Load article_aliases.json → {raw_code: canonical_code}"""
    path = os.path.join(os.path.dirname(__file__), 'article_aliases.json')
    try:
        with open(path) as f:
            data = json.load(f)
        return {k: v for k, v in data.get('aliases', {}).items() if k and v}
    except FileNotFoundError:
        return {}

ARTICLE_ALIASES = _load_article_aliases()  # {raw_code: canonical_code}


def _load_excluded_mos() -> set:
    """Load excluded_mos.json → set of MO ints to skip entirely."""
    path = os.path.join(os.path.dirname(__file__), 'excluded_mos.json')
    try:
        with open(path) as f:
            data = json.load(f)
        return {int(entry['mo']) for entry in data.get('excluded', []) if entry.get('mo')}
    except FileNotFoundError:
        return set()

EXCLUDED_MOS = _load_excluded_mos()  # {mo_int, ...}


# ── Size / suffix mappings ────────────────────────────────────────────────────
SUFFIX_MAP = {
    '500': '500ML',
    '750': '750ML',
    '001': '1L',
    '005': '5L',
    '020': '20L',
    '025': '25KG',
    '030': '30KG',
}

SIZE_LITRES = {
    '500ML': 0.5,
    '750ML': 0.75,
    '1L':    1.0,
    '5L':    5.0,
    '20L':   20.0,
    '25KG':  25.0,
    '30KG':  30.0,
}

PACK_COLS = ['500ML', '750ML', '1L', '5L', '20L', '25KG', '30KG']

REQUIRED_COLS = {
    'Date', 'Manufacturing Order', 'Article',
    'Description', 'Lot', 'Qty.', 'Doc. Code',
}


# ── Classify rows ─────────────────────────────────────────────────────────────
def _classify(df: pd.DataFrame) -> pd.DataFrame:
    art = df['Article'].astype(str).str.strip()
    doc = df['Doc. Code'].astype(str).str.strip()
    df = df.copy()
    df['_is_fg']     = art.str.match(r'^\d{3}-\d{4}-\d{3}$')
    df['_is_bulk']   = art.str.match(r'^\d{3}-\d{4}$')
    df['_doc_eof']   = doc.str.upper().str.startswith('EOF')
    df['_bulk_prod'] = df['_is_bulk'] & df['_doc_eof']
    df['_art_base']  = art.str[:8]   # e.g. "206-0003"
    return df


# ── Build report tables ───────────────────────────────────────────────────────
def build_report_data(df: pd.DataFrame):
    """
    Parameters
    ----------
    df : raw dataframe straight from pd.read_excel — no pre-filtering.

    Returns
    -------
    (report_df, other_df)
    """
    # ── RULE 1: Drop all SOF documents ───────────────────────────────────────
    df = df[~df['Doc. Code'].astype(str).str.upper().str.startswith('SOF')].copy()

    # ── Apply article aliases (e.g. "0066-0040" → "066-0040") ────────────────
    if ARTICLE_ALIASES:
        df['Article'] = df['Article'].astype(str).str.strip().replace(ARTICLE_ALIASES)

    # ── Drop excluded MOs entirely ────────────────────────────────────────────
    if EXCLUDED_MOS:
        mo_col = pd.to_numeric(df['Manufacturing Order'], errors='coerce')
        df = df[~mo_col.isin(EXCLUDED_MOS)]

    df = _classify(df)

    # ── Bulk production lookup — ALL dates so older lots can link to Feb+ FGs ─
    # Group by Manufacturing Order (not Lot) — this ensures we never lose a bulk
    # record when two different articles share the same lot number.
    bulk_prod  = df[df['_bulk_prod']].copy()
    bulk_by_lot = (
        bulk_prod
        .groupby('Manufacturing Order', sort=False)
        .agg(
            Lot=('Lot', 'first'),
            Bulk_Article=('Article', 'first'),
            Bulk_Description=('Description', 'first'),
            Bulk_Qty=('Qty.', 'sum'),
            Date=('Date', 'first'),
        )
        .reset_index()
        .rename(columns={'Manufacturing Order': 'Bulk_MO'})
    )

    # ── RULE 2: FG rows restricted to Feb 2026+ ───────────────────────────────
    fg_all = df[df['_is_fg']].copy()
    fg = fg_all[pd.to_datetime(fg_all['Date']) >= '2026-02-01'].copy()
    fg['suffix']   = fg['Article'].astype(str).str[-3:]
    fg['pack_col'] = fg['suffix'].map(SUFFIX_MAP)

    # ── Link: FG lot + article base → correct bulk MO ────────────────────────
    # bulk_by_lot now has one row per MO. Build a (Lot, base_article) → Bulk_MO
    # lookup so that when two bulk articles share the same lot we pick the right one.
    bulk_lot_base = bulk_by_lot[['Lot', 'Bulk_Article', 'Bulk_MO']].copy()
    bulk_lot_base['base'] = bulk_lot_base['Bulk_Article'].astype(str).str[:8]

    fg_full = fg.merge(
        bulk_lot_base[['Lot', 'base', 'Bulk_MO']].rename(columns={'base': '_match_base'}),
        left_on=['Lot', '_art_base'],
        right_on=['Lot', '_match_base'],
        how='left',
    ).drop(columns=['_match_base'], errors='ignore')

    # Attach the full bulk row metadata via Bulk_MO
    fg_full = fg_full.merge(
        bulk_by_lot[['Bulk_MO', 'Bulk_Article', 'Bulk_Description', 'Bulk_Qty', 'Date']]
            .rename(columns={'Date': 'Date_b', 'Bulk_Article': 'Bulk_Article_b'}),
        on='Bulk_MO',
        how='left',
    )

    # ── Apply manual MO overrides for rows that still have no Bulk_MO ─────────
    bulk_by_mo = bulk_by_lot.set_index('Bulk_MO')
    unlinked_mask = fg_full['Bulk_MO'].isna()
    if unlinked_mask.any() and MO_OVERRIDES:
        for idx in fg_full[unlinked_mask].index:
            fg_mo_str = str(int(fg_full.at[idx, 'Manufacturing Order']))
            override  = MO_OVERRIDES.get(fg_mo_str)
            if override:
                bulk_mo_float = float(int(override['bulk_mo']))
                if bulk_mo_float in bulk_by_mo.index:
                    brow = bulk_by_mo.loc[bulk_mo_float]
                    fg_full.at[idx, 'Bulk_MO']           = bulk_mo_float
                    fg_full.at[idx, 'Bulk_Article_b']     = brow['Bulk_Article']
                    fg_full.at[idx, 'Bulk_Description']   = brow['Bulk_Description']
                    fg_full.at[idx, 'Bulk_Qty']           = brow['Bulk_Qty']
                    fg_full.at[idx, 'Date_b']             = brow['Date']
                    fg_full.at[idx, 'Lot']                = brow['Lot']

    fg_linked_valid = fg_full.dropna(subset=['Bulk_MO'])

    # ── Aggregate FG pack sizes per bulk MO ───────────────────────────────────
    # Group by Bulk_MO (not Lot) so override rows — which may carry a different
    # lot than the one originally recorded on the FG — land in the right bucket.
    fg_agg = (
        fg_linked_valid
        .groupby(['Bulk_MO', 'pack_col'])['Qty.']
        .sum()
        .unstack(fill_value=0)
        .reset_index()
    )

    fg_mos = (
        fg_linked_valid
        .groupby('Bulk_MO')['Manufacturing Order']
        .apply(lambda x: ', '.join(sorted({str(int(v)) for v in x.dropna()})))
        .reset_index()
        .rename(columns={'Manufacturing Order': 'FG_MOs'})
    )

    # ── Build the report: bulk MOs from Feb+ OR linked to Feb+ FGs ───────────
    bulk_feb_mos  = set(bulk_prod[pd.to_datetime(bulk_prod['Date']) >= '2026-02-01']['Manufacturing Order'])
    linked_mos    = set(fg_linked_valid['Bulk_MO'].unique())
    report_mos    = bulk_feb_mos | linked_mos

    report = bulk_by_lot[bulk_by_lot['Bulk_MO'].isin(report_mos)].copy()
    report = report.merge(fg_agg, on='Bulk_MO', how='left')
    report = report.merge(fg_mos, on='Bulk_MO', how='left')

    for c in PACK_COLS:
        report[c] = report.get(c, pd.Series(0.0, index=report.index)).fillna(0.0).astype(float)

    report['FG_Litres'] = sum(report[c] * SIZE_LITRES[c] for c in PACK_COLS)
    report['Fill_Rate'] = report.apply(
        lambda r: r['FG_Litres'] / r['Bulk_Qty'] if r['Bulk_Qty'] > 0 else 0.0, axis=1
    )
    report['Bulk_MO'] = report['Bulk_MO'].astype(float).astype(int)
    report = report.sort_values(['Date', 'Bulk_MO']).reset_index(drop=True)

    # ── RULE 3: Other = only non-bulk, non-FG rows (colorantes, bases, etc.) ──
    # FG rows (linked or not) never appear in Other.
    other_mask = ~df['_bulk_prod'] & ~df['_is_fg']
    other_combined = (
        df[other_mask & (pd.to_datetime(df['Date']) >= '2026-02-01')]
        [['Date', 'Manufacturing Order', 'Article', 'Description', 'Lot', 'Qty.', 'Doc. Code']]
        .sort_values('Manufacturing Order')
        .reset_index(drop=True)
    )

    # ── Unlinked FG rows — merge into report sorted by date ──────────────────
    # Build stub rows shaped like the report so the single rendering loop
    # handles them with no special-casing. Bulk columns are NaN/blank;
    # the Comment column will read "No bulk found".
    linked_fg_mos = set()
    for v in report['FG_MOs'].dropna():
        for mo in str(v).split(', '):
            linked_fg_mos.add(mo.strip())

    fg_feb = df[df['_is_fg'] & (pd.to_datetime(df['Date']) >= '2026-02-01')].copy()
    fg_feb['_mo_str'] = fg_feb['Manufacturing Order'].astype(int).astype(str)
    unlinked_fg_raw = fg_feb[~fg_feb['_mo_str'].isin(linked_fg_mos)].copy()

    if len(unlinked_fg_raw):
        stubs = pd.DataFrame({
            'Date':             unlinked_fg_raw['Date'].values,
            'Bulk_MO':          unlinked_fg_raw['Manufacturing Order'].astype(int).values,
            'Lot':              unlinked_fg_raw['Lot'].values,
            'Bulk_Article':     unlinked_fg_raw['Article'].values,
            'Bulk_Description': unlinked_fg_raw['Description'].values,
            'Bulk_Qty':         unlinked_fg_raw['Qty.'].values,
            'FG_MOs':           pd.NA,
            '_unlinked_fg':     True,
        })
        for c in PACK_COLS:
            stubs[c] = 0.0
        stubs['FG_Litres'] = 0.0
        stubs['Fill_Rate'] = float('nan')
        report['_unlinked_fg'] = False
        report = pd.concat([report, stubs], ignore_index=True)

    report = report.sort_values(['Date', 'Bulk_MO']).reset_index(drop=True)
    if '_unlinked_fg' not in report.columns:
        report['_unlinked_fg'] = False

    # ── Unlinked bulk MOs (Feb+, no FG rows linked) ───────────────────────────
    unlinked_bulk = (
        report[~report['_unlinked_fg'] & (report['FG_MOs'].isna() | (report['FG_MOs'] == ''))]
        [['Bulk_MO', 'Bulk_Article', 'Bulk_Description', 'Lot', 'Bulk_Qty', 'Date']]
        .sort_values('Bulk_MO')
        .reset_index(drop=True)
    )

    # Legacy unlinked_fg df for stats
    unlinked_fg = (
        unlinked_fg_raw
        [['Date', 'Manufacturing Order', 'Article', 'Description', 'Lot', 'Qty.']]
        .sort_values('Manufacturing Order')
        .reset_index(drop=True)
    ) if len(unlinked_fg_raw) else pd.DataFrame()

    return report, other_combined, unlinked_fg, unlinked_bulk


# ── Excel styling ─────────────────────────────────────────────────────────────
FONT      = 'Times New Roman'
FONT_SIZE = 12

def _border():
    s = Side(style='thin', color='000000')
    return Border(left=s, right=s, top=s, bottom=s)

def _font(bold=False):
    return Font(name=FONT, size=FONT_SIZE, bold=bold)

def _fill(hex_):
    return PatternFill('solid', fgColor=hex_)

def _aln(h='left', wrap=False):
    return Alignment(horizontal=h, vertical='center', wrap_text=wrap)


# ── Build the Excel workbook ──────────────────────────────────────────────────
def build_excel(report: pd.DataFrame, other_combined: pd.DataFrame,
                unlinked_fg: pd.DataFrame, unlinked_bulk: pd.DataFrame,
                raw_df: pd.DataFrame, mo_df: pd.DataFrame = None) -> io.BytesIO:
    wb  = Workbook()
    bdr = _border()

    # ════════════════════════════════════════════════════════════════════════
    # Sheet 1 – Fill Rate Report  (header on row 1, data from row 2)
    # ════════════════════════════════════════════════════════════════════════
    ws = wb.active
    ws.title = 'Fill Rate Report'

    headers    = [
        'DATE', 'BULK MFG ORDER', 'FG MFG ORDER(S)',
        'BULK LOT', 'BULK ARTICLE', 'BULK DESCRIPTION',
        'BULK QTY (L/KG)',
        'ACTUAL QTY PRODUCED',
        '500ML', '750ML', '1L', '5L', '20L', '25KG', '30KG',
        'FILL RATE', 'COMMENT',
    ]
    col_widths = [14, 16, 30, 28, 14, 38, 14, 16, 9, 9, 9, 9, 9, 9, 9, 12, 36]

    # Column letters for formula references (1-based → letter)
    # G = Bulk Qty, H = Actual Qty Produced, I..O = pack sizes, P = Fill Rate
    COL_BULK_QTY   = 'G'   # col 7
    COL_ACTUAL_QTY = 'H'   # col 8
    COL_FILL_RATE  = 'P'   # col 16
    # Pack size columns I(9)=500ML, J(10)=750ML, K(11)=1L, L(12)=5L,
    #                   M(13)=20L,  N(14)=25KG,  O(15)=30KG
    PACK_MULTIPLIERS = [0.5, 0.75, 1, 5, 20, 25, 30]
    PACK_LETTERS     = ['I', 'J', 'K', 'L', 'M', 'N', 'O']

    # Header row (row 1)
    for i, (h, w) in enumerate(zip(headers, col_widths), 1):
        c = ws.cell(row=1, column=i, value=h)
        c.font      = _font(bold=True)
        c.fill      = _fill('1F4E79')
        c.font      = Font(name=FONT, size=FONT_SIZE, bold=True, color='FFFFFF')
        c.alignment = _aln('center', wrap=True)
        c.border    = bdr
        ws.column_dimensions[get_column_letter(i)].width = w
    ws.row_dimensions[1].height = 30

    # Data rows (start at row 2) — blank separator + month label between months
    num_cols   = len(headers)
    current_month = None
    excel_row  = 2   # tracks actual Excel row as we insert separators

    for ridx, row in report.iterrows():
        row_month = pd.to_datetime(row['Date']).strftime('%B %Y') if pd.notnull(row['Date']) else None

        if row_month != current_month:
            if current_month is not None:
                # blank spacer row (no borders, no content)
                excel_row += 1

            # Month label row — merged across all columns
            ws.merge_cells(start_row=excel_row, start_column=1,
                           end_row=excel_row, end_column=num_cols)
            mc = ws.cell(row=excel_row, column=1, value=row_month or '')
            mc.font      = Font(name=FONT, size=FONT_SIZE, bold=True, color='1F4E79')
            mc.alignment = _aln('left')
            excel_row   += 1
            current_month = row_month

        fr  = row['Fill_Rate']
        is_unlinked = bool(row.get('_unlinked_fg', False))

        if is_unlinked:
            fr_fill = None
        else:
            fr_fill = (
                _fill('FFC7CE') if pd.isna(fr) or fr == 0 else
                _fill('FFEB9C') if fr < 0.85 else
                _fill('C6EFCE')
            )

        # Actual qty produced formula: sum of (pack col * litre multiplier)
        actual_qty_formula = '+'.join(
            f'{l}{excel_row}*{m}' for l, m in zip(PACK_LETTERS, PACK_MULTIPLIERS)
        )

        date_val = row['Date'].date() if pd.notnull(row['Date']) else ''
        comment  = MO_COMMENTS.get(str(int(row['Bulk_MO'])), '')
        if is_unlinked and not comment:
            comment = 'No bulk found'

        if is_unlinked:
            fg_qty        = float(row['Bulk_Qty']) if pd.notnull(row['Bulk_Qty']) else 0.0
            bulk_qty_val  = fg_qty                          # G — FG qty used as bulk qty
            actual_qty_val = f'={actual_qty_formula}'       # H — formula (pack sizes are 0, so = 0)
            # Fill rate formula still works: H/G — but since pack sizes are 0
            # we write qty directly into G and set actual = same value via formula.
            # Simpler: just put the qty in both G and H so H/G = 100%
            actual_qty_val = fg_qty                         # H — same value → 100% fill rate
            fill_rate_val  = (f'=IF({COL_BULK_QTY}{excel_row}>0,'
                              f'{COL_ACTUAL_QTY}{excel_row}/{COL_BULK_QTY}{excel_row},0)')
            fg_mos_val     = str(int(row['Bulk_MO']))       # C — FG MO number
            bulk_mo_val    = '—'                            # B — no bulk MO
        else:
            bulk_qty_val   = float(row['Bulk_Qty'])
            actual_qty_val = f'={actual_qty_formula}'
            fill_rate_val  = (f'=IF({COL_BULK_QTY}{excel_row}>0,'
                              f'{COL_ACTUAL_QTY}{excel_row}/{COL_BULK_QTY}{excel_row},0)')
            fg_mos_val     = str(row['FG_MOs']) if pd.notnull(row.get('FG_MOs')) else '—'
            bulk_mo_val    = int(row['Bulk_MO'])

        vals = [
            date_val,          # A
            bulk_mo_val,       # B
            fg_mos_val,        # C
            str(row['Lot']),   # D
            str(row['Bulk_Article']),     # E
            str(row['Bulk_Description']), # F
            bulk_qty_val,      # G
            actual_qty_val,    # H
            float(row['500ML']), float(row['750ML']), float(row['1L']),  # I J K
            float(row['5L']),    float(row['20L']),                      # L M
            float(row['25KG']),  float(row['30KG']),                     # N O
            fill_rate_val,     # P
            comment,           # Q
        ]
        fmts = [
            'DD/MM/YYYY', '0', '@', '@', '@', '@',
            '#,##0.00',
            '#,##0.00',
            '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
            '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
            '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
            '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
            '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
            '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
            '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
            '0.0%', '@',
        ]
        alns = [
            'center', 'center', 'left', 'left', 'center', 'left',
            'right', 'right',
            'right', 'right', 'right', 'right', 'right', 'right', 'right',
            'center', 'left',
        ]
        for ci, (v, fmt, aln) in enumerate(zip(vals, fmts, alns), 1):
            c = ws.cell(excel_row, ci, v)
            c.font          = _font()
            c.alignment     = _aln(aln)
            c.border        = bdr
            c.number_format = fmt
            if not is_unlinked and ci == num_cols - 1:   # Fill Rate column (P) on normal rows only
                c.fill = fr_fill
        excel_row += 1

    last_data_row = excel_row - 1
    ws.freeze_panes = 'A2'
    ws.auto_filter.ref = f'A1:Q{last_data_row}'

    # ════════════════════════════════════════════════════════════════════════
    # Sheet 2 – Other & Intermediates  (header on row 1, data from row 2)
    # ════════════════════════════════════════════════════════════════════════
    ws2 = wb.create_sheet('Other & Intermediates')

    h2 = ['DATE', 'MFG ORDER', 'ARTICLE', 'DESCRIPTION', 'LOT', 'QTY', 'DOC CODE']
    w2 = [14, 14, 18, 46, 28, 12, 18]
    for i, (h, w) in enumerate(zip(h2, w2), 1):
        c = ws2.cell(1, i, h)
        c.font      = Font(name=FONT, size=FONT_SIZE, bold=True, color='FFFFFF')
        c.fill      = _fill('1F4E79')
        c.alignment = _aln('center', wrap=True)
        c.border    = bdr
        ws2.column_dimensions[get_column_letter(i)].width = w
    ws2.row_dimensions[1].height = 24

    for ridx, row in other_combined.iterrows():
        er   = ridx + 2
        mo_v = int(row['Manufacturing Order']) if pd.notnull(row['Manufacturing Order']) else ''
        dv   = row['Date'].date() if pd.notnull(row['Date']) else ''
        vals = [
            dv, mo_v,
            str(row['Article']), str(row['Description']),
            str(row['Lot']), float(row['Qty.']), str(row['Doc. Code']),
        ]
        fmts = ['DD/MM/YYYY', '0', '@', '@', '@', '#,##0.00', '@']
        alns = ['center', 'center', 'left', 'left', 'left', 'right', 'left']
        for ci, (v, fmt, aln) in enumerate(zip(vals, fmts, alns), 1):
            c = ws2.cell(er, ci, v)
            c.font          = _font()
            c.border        = bdr
            c.alignment     = _aln(aln)
            c.number_format = fmt

    ws2.freeze_panes = 'A2'
    ws2.auto_filter.ref = f'A1:G{len(other_combined) + 1}'

    # ════════════════════════════════════════════════════════════════════════
    # Sheet 3 – Raw Data (every row from the uploaded file)
    # ════════════════════════════════════════════════════════════════════════
    ws4 = wb.create_sheet('Raw Data')

    raw_cols = ['Date', 'Manufacturing Order', 'Doc. Code', 'Article',
                'Description', 'Warehouse', 'Lot', 'Qty.']
    raw_widths = [16, 16, 18, 18, 46, 14, 28, 10]
    raw_headers = ['DATE', 'MFG ORDER', 'DOC CODE', 'ARTICLE',
                   'DESCRIPTION', 'WAREHOUSE', 'LOT', 'QTY']

    for i, (h, w) in enumerate(zip(raw_headers, raw_widths), 1):
        c = ws4.cell(1, i, h)
        c.font      = Font(name=FONT, size=FONT_SIZE, bold=True, color='FFFFFF')
        c.fill      = _fill('1F4E79')
        c.alignment = _aln('center', wrap=True)
        c.border    = bdr
        ws4.column_dimensions[get_column_letter(i)].width = w
    ws4.row_dimensions[1].height = 24

    for ridx, row in raw_df[raw_cols].iterrows():
        er   = ridx + 2
        dv   = row['Date'].date() if pd.notnull(row['Date']) else ''
        mo_v = int(row['Manufacturing Order']) if pd.notnull(row['Manufacturing Order']) else ''
        vals = [dv, mo_v, str(row['Doc. Code']), str(row['Article']),
                str(row['Description']), str(row['Warehouse']), str(row['Lot']),
                float(row['Qty.']) if pd.notnull(row['Qty.']) else 0.0]
        fmts = ['DD/MM/YYYY', '0', '@', '@', '@', '@', '@', '#,##0.00']
        alns = ['center', 'center', 'left', 'left', 'left', 'center', 'left', 'right']
        for ci, (v, fmt, aln) in enumerate(zip(vals, fmts, alns), 1):
            c = ws4.cell(er, ci, v)
            c.font          = _font()
            c.border        = bdr
            c.alignment     = _aln(aln)
            c.number_format = fmt

    ws4.freeze_panes = 'A2'
    ws4.auto_filter.ref = f'A1:H{len(raw_df) + 1}'

    # ── Sheet 2: All Manufacturing Orders (only when MO file is provided) ──────
    if mo_df is not None:
        status_df = build_mo_status(mo_df, report)
        _write_mo_status_sheet(wb, status_df)
        # Move to position 2 (index 1), right after Fill Rate Report
        wb.move_sheet('All Manufacturing Orders', offset=-(len(wb.sheetnames) - 2))

    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return buf


# ── Public entry point ────────────────────────────────────────────────────────
def generate(file_stream, mo_file_stream=None) -> tuple[io.BytesIO, dict]:
    """
    Parameters
    ----------
    file_stream : file-like object
        The uploaded Production Records Excel file.
    mo_file_stream : file-like object, optional
        The uploaded Manufacturing Orders Excel file.

    Returns
    -------
    (BytesIO, stats_dict)
    """
    def _to_bytesio(stream):
        """Wrap any file-like object in BytesIO so openpyxl/pandas can seek it."""
        if isinstance(stream, io.BytesIO):
            return stream
        return io.BytesIO(stream.read())

    df = pd.read_excel(_to_bytesio(file_stream))

    missing = REQUIRED_COLS - set(df.columns)
    if missing:
        raise ValueError(f"Missing columns: {', '.join(sorted(missing))}")

    # Load optional MO file
    mo_df = None
    if mo_file_stream is not None:
        mo_df = pd.read_excel(_to_bytesio(mo_file_stream))
        mo_missing = MO_REQUIRED_COLS - set(mo_df.columns)
        if mo_missing:
            raise ValueError(f"Missing columns in MO file: {', '.join(sorted(mo_missing))}")

    report, other_combined, unlinked_fg, unlinked_bulk = build_report_data(df)
    buf = build_excel(report, other_combined, unlinked_fg, unlinked_bulk, df, mo_df)

    avg_fr = float(report['Fill_Rate'].mean()) if len(report) else 0.0
    stats  = {
        'bulk_orders':    len(report),
        'fg_orders':      int(df['Article'].astype(str).str.match(r'^\d{3}-\d{4}-\d{3}$').sum()),
        'fill_rate_avg':  round(avg_fr * 100, 1),
        'other_rows':     len(other_combined),
        'unlinked_fg':    len(unlinked_fg),
        'unlinked_bulk':  len(unlinked_bulk),
        'total_rows':     len(df),
        'mo_status':      True if mo_df is not None else False,
    }
    return buf, stats


def filename_for_report(as_at_date=None) -> str:
    """Return the standard download filename, e.g.
       'Fill Rate Report as at 05.03.2026.xlsx'
    """
    d = as_at_date or datetime.now()
    return f"Fill Rate Report as at {d.strftime('%d.%m.%Y')}.xlsx"


# ── MO Status builder ─────────────────────────────────────────────────────────
MO_REQUIRED_COLS = {'Manufacturing Order', 'Article', 'Item Description', 'Date', 'Confirmada', 'Qty.'}

def build_mo_status(mo_df: pd.DataFrame, report: pd.DataFrame) -> pd.DataFrame:
    """
    Compare the full MO list against what was produced in the Fill Rate report.

    Returns a DataFrame with one row per MO (bulk + FG only), with columns:
      MO, Date, Article, Description, Planned_Qty, Status,
      Produced_Qty, Fill_Rate, FG_MOs  (last three only for bulk rows)
    """
    mo_df = mo_df.copy()
    mo_df['_is_bulk'] = mo_df['Article'].astype(str).str.match(r'^\d{3}-\d{4}$')
    mo_df['_is_fg']   = mo_df['Article'].astype(str).str.match(r'^\d{3}-\d{4}-\d{3}$')

    # Only bulk and FG rows are relevant (same scope as main report)
    mo_relevant = mo_df[mo_df['_is_bulk'] | mo_df['_is_fg']].copy()
    mo_relevant['MO'] = mo_relevant['Manufacturing Order'].astype(float).astype(int)

    # Set of MOs that appear in production records (i.e. have been produced)
    produced_bulk_mos = set(report[~report['_unlinked_fg']]['Bulk_MO'].unique())
    # For FG MOs: flatten all FG_MOs strings
    produced_fg_mos = set()
    for v in report['FG_MOs'].dropna():
        for mo in str(v).split(', '):
            s = mo.strip()
            if s and s != 'nan':
                produced_fg_mos.add(int(s))

    rows = []
    for _, mo_row in mo_relevant.iterrows():
        mo_int      = int(mo_row['MO'])
        is_bulk     = bool(mo_row['_is_bulk'])
        confirmed   = bool(mo_row['Confirmada'])

        if is_bulk:
            bulk_match = report[report['Bulk_MO'] == mo_int]
            if not bulk_match.empty:
                status       = 'Produced'
                produced_qty = float(bulk_match.iloc[0]['Bulk_Qty'])
                fill_rate    = bulk_match.iloc[0]['Fill_Rate']
                fg_mos       = str(bulk_match.iloc[0]['FG_MOs']) if pd.notnull(bulk_match.iloc[0]['FG_MOs']) else '—'
            else:
                status       = 'In Progress' if confirmed else 'Planned'
                produced_qty = 0.0
                fill_rate    = float('nan')
                fg_mos       = '—'
        else:
            if mo_int in produced_fg_mos:
                status       = 'Produced'
                produced_qty = float(mo_row['Qty.'])
                fill_rate    = float('nan')
                fg_mos       = '—'
            else:
                status       = 'In Progress' if confirmed else 'Planned'
                produced_qty = 0.0
                fill_rate    = float('nan')
                fg_mos       = '—'

        rows.append({
            'MO':           mo_int,
            'Date':         mo_row['Date'],
            'Article':      str(mo_row['Article']),
            'Description':  str(mo_row['Item Description']),
            'Planned_Qty':  float(mo_row['Qty.']),
            'Type':         'Bulk' if is_bulk else 'FG',
            'Status':       status,
            'Produced_Qty': produced_qty,
            'Fill_Rate':    fill_rate,
            'FG_MOs':       fg_mos,
        })

    status_df = pd.DataFrame(rows)
    status_df = status_df.sort_values(['Date', 'MO']).reset_index(drop=True)
    return status_df


def _write_mo_status_sheet(wb, status_df: pd.DataFrame):
    """Write the All Manufacturing Orders sheet into an existing workbook."""
    bdr  = _border()
    ws   = wb.create_sheet('All Manufacturing Orders')

    # Filter to Feb 2026+ only (same window as main report)
    status_df = status_df[pd.to_datetime(status_df['Date']) >= '2026-02-01'].reset_index(drop=True)

    STATUS_FILL = {
        'Produced':    _fill('C6EFCE'),   # green
        'In Progress': _fill('FFEB9C'),   # yellow
        'Planned':     _fill('D9E1F2'),   # blue-grey
    }

    headers    = ['MO', 'DATE', 'TYPE', 'ARTICLE', 'DESCRIPTION',
                  'PLANNED QTY', 'STATUS', 'PRODUCED QTY', 'FILL RATE', 'FG MFG ORDER(S)']
    col_widths = [14, 14, 10, 16, 44, 12, 14, 13, 12, 30]

    for i, (h, w) in enumerate(zip(headers, col_widths), 1):
        c = ws.cell(row=1, column=i, value=h)
        c.font      = Font(name=FONT, size=FONT_SIZE, bold=True, color='FFFFFF')
        c.fill      = _fill('1F4E79')
        c.alignment = _aln('center', wrap=True)
        c.border    = bdr
        ws.column_dimensions[get_column_letter(i)].width = w
    ws.row_dimensions[1].height = 24

    current_month = None
    excel_row = 2

    for _, row in status_df.iterrows():
        row_month = pd.to_datetime(row['Date']).strftime('%B %Y') if pd.notnull(row['Date']) else None

        if row_month != current_month:
            if current_month is not None:
                excel_row += 1   # blank spacer
            ws.merge_cells(start_row=excel_row, start_column=1,
                           end_row=excel_row, end_column=len(headers))
            mc = ws.cell(row=excel_row, column=1, value=row_month or '')
            mc.font      = Font(name=FONT, size=FONT_SIZE, bold=True, color='1F4E79')
            mc.alignment = _aln('left')
            excel_row   += 1
            current_month = row_month

        sf   = STATUS_FILL.get(row['Status'])
        dv   = row['Date'].date() if pd.notnull(row['Date']) else ''
        fr   = row['Fill_Rate']
        pq   = row['Produced_Qty']

        vals = [
            int(row['MO']),
            dv,
            str(row['Type']),
            str(row['Article']),
            str(row['Description']),
            float(row['Planned_Qty']),
            str(row['Status']),
            pq if pq > 0 else '—',
            f'{fr:.1%}' if not pd.isna(fr) and row['Type'] == 'Bulk' else '—',
            str(row['FG_MOs']) if row['FG_MOs'] != '—' else '—',
        ]
        fmts = ['0', 'DD/MM/YYYY', '@', '@', '@', '#,##0.00', '@', '#,##0.00', '@', '@']
        alns = ['center','center','center','left','left','right','center','right','center','left']

        for ci, (v, fmt, aln) in enumerate(zip(vals, fmts, alns), 1):
            c = ws.cell(excel_row, ci, v)
            c.font          = _font()
            c.border        = bdr
            c.alignment     = _aln(aln)
            c.number_format = fmt
            if sf and ci == 7:   # colour only the Status cell
                c.fill = sf
        excel_row += 1

    ws.freeze_panes = 'A2'
    ws.auto_filter.ref = f'A1:J{excel_row - 1}'