import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font
import sys
import os
import io
from pathlib import Path

# Fix Windows console encoding
if sys.platform == 'win32':
    sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')

def post_process_file(target_file, mapping_file):
    print(f"🎨 Starting Post-Processing for: {target_file}")
    
    # 1. Load Mappings
    if not os.path.exists(mapping_file):
        print(f"❌ Mapping file not found: {mapping_file}")
        return
    
    map_df = pd.read_excel(mapping_file)
    # columns: code, Type, tag
    
    omission_codes = set(map_df[map_df['Type'].str.lower() == 'omission']['code'].astype(str).tolist())
    reverse_codes = set(map_df[map_df['tag'].str.lower() == 'reverse-scoring item']['code'].astype(str).tolist())
    
    print(f"📊 Mapping loaded: {len(omission_codes)} Omission items, {len(reverse_codes)} Reverse items")

    # 2. Load Target Workbook
    if not os.path.exists(target_file):
        print(f"❌ Target file not found: {target_file}")
        return
        
    wb = load_workbook(target_file)
    ws = wb.active

    # Define Styles (Text Color)
    green_font = Font(color="008000") # Dark Green text
    red_font = Font(color="FF0000")   # Bright Red text

    # 3. Process Columns
    # header row is 1
    headers = [cell.value for cell in ws[1]]
    
    modified_cols = 0
    for col_idx, header in enumerate(headers, start=1):
        if not header:
            continue
            
        header_str = str(header).strip()
        
        target_font = None
        
        # Priority: Red (Reverse) > Green (Omission)
        if header_str in reverse_codes:
            target_font = red_font
            print(f"   🚩 Marking header {header_str} text as RED (Reverse)")
        elif header_str in omission_codes:
            target_font = green_font
            print(f"   🟢 Marking header {header_str} text as GREEN (Omission)")
            
        if target_font:
            # Apply ONLY to the header cell (row 1)
            ws.cell(row=1, column=col_idx).font = target_font
            modified_cols += 1

    # Clear any existing column fills from previous runs (Clean up)
    for col in range(1, ws.max_column + 1):
        for row in range(2, ws.max_row + 1):
            ws.cell(row=row, column=col).fill = PatternFill(fill_type=None)

    # 4. Save
    wb.save(target_file)
    print(f"✅ Success: {modified_cols} columns formatted and file saved.")

if __name__ == "__main__":
    # Default paths for the current task
    DEFAULT_TARGET = r"C:\work\CP_Automation\Personality_14-17.xlsx"
    DEFAULT_MAPPING = r"C:\work\CP_Automation\Simulated_Assessment_Engine\data\AllQuestions.xlsx"
    
    # Allow command line overrides
    target = sys.argv[1] if len(sys.argv) > 1 else DEFAULT_TARGET
    mapping = sys.argv[2] if len(sys.argv) > 2 else DEFAULT_MAPPING
    
    post_process_file(target, mapping)