# output_writer.py
import pandas as pd
from datetime import datetime

def save_with_instructions(df: pd.DataFrame, output_path: str, min_qty: int = 50, weights: dict = None):
    """
    Save the priority results to Excel with an instructions sheet
    """
    if weights is None:
        weights = {"AGE_WEIGHT": 60, "COMPONENT_WEIGHT": 30, "MANUAL_WEIGHT": 10}
        
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        # Save main results
        df.to_excel(writer, sheet_name='Priority_Results', index=False)
        
        # Create instructions sheet
        instructions_data = [
            ['Manufacturing Priority Decision Results'],
            [''],
            ['METHODOLOGY:'],
            [f'Age Weight: {weights["AGE_WEIGHT"]}%'],
            [f'Component Simplicity Weight: {weights["COMPONENT_WEIGHT"]}%'],
            [f'Manual Priority Weight: {weights["MANUAL_WEIGHT"]}%'],
            [f'Minimum Quantity Threshold: {min_qty}'],
            [''],
            ['COLUMNS EXPLANATION:'],
            ['OrderAgeDays: Days since oldest product required'],
            ['ComponentCount: Number of unique components needed'],
            ['QtyThresholdOK: Whether quantity meets minimum threshold'],
            ['AgeScore: Weighted age contribution to priority'],
            ['SimplicityScore: Weighted simplicity contribution to priority'],
            ['ManualScore: Weighted manual priority contribution'],
            ['PriorityScore: Final calculated priority (0-1 scale)'],
            [''],
            ['Higher PriorityScore = Higher Manufacturing Priority'],
            [f'Generated on: {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}']
        ]
        
        instructions_df = pd.DataFrame(instructions_data, columns=['Instructions'])
        instructions_df.to_excel(writer, sheet_name='Instructions', index=False)