"""
Create an Excel evaluation template for RGB harvest date predictions.
Parses field names and dates directly from RGB image filenames.
"""
import os
import glob
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import re
from datetime import datetime

# Configuration
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
RGB_DIR = os.path.join(BASE_DIR, "laravel_app", "storage", "app", "angata", "RGB")
OUTPUT_PATH = os.path.join(BASE_DIR, "laravel_app", "storage", "app", "angata", "RGB_Evaluation_Template.xlsx")

# Evaluators
EVALUATORS = ["Joey", "Daniel", "Nik", "Dimitra", "Timon"]

def parse_rgb_filenames():
    """
    Parse field names and harvest dates from RGB image filenames.
    Expected format: field_{field_id or name}_{YYYYMMDD}_harvest_xlsx_harvest_rgb.png
    """
    fields_data = []
    
    # Find all RGB images
    rgb_files = glob.glob(os.path.join(RGB_DIR, "field_*.png"))
    
    for filepath in sorted(rgb_files):
        filename = os.path.basename(filepath)
        # Pattern: field_{field_id}_{YYYYMMDD}_harvest_xlsx_harvest_rgb.png
        match = re.match(r"field_(.+?)_(\d{8})_harvest_xlsx_harvest_rgb\.png", filename)
        
        if match:
            field_id = match.group(1)  # e.g., "1000" or "91&92"
            date_str = match.group(2)  # e.g., "20250814"
            
            # Format date as YYYY-MM-DD
            try:
                harvest_date = datetime.strptime(date_str, "%Y%m%d").strftime("%Y-%m-%d")
            except ValueError:
                harvest_date = date_str
            
            fields_data.append({
                "field_id": field_id,
                "harvest_date": harvest_date,
                "filename": filename
            })
    
    # Sort by field_id (treating numeric parts as integers where possible)
    fields_data = sorted(fields_data, key=lambda x: (x["field_id"].replace("&92", ""), ))
    
    return fields_data

def create_evaluation_template():
    """Create the Excel evaluation template."""
    print("Loading field data from RGB images...")
    fields_data = parse_rgb_filenames()
    
    if not fields_data:
        print("ERROR: No RGB images found in", RGB_DIR)
        return
    
    print(f"Found {len(fields_data)} RGB images")
    
    # Create workbook
    wb = Workbook()
    
    # === SHEET 1: Evaluation Form ===
    ws_eval = wb.active
    ws_eval.title = "Evaluation"
    
    # Define styles
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_font = Font(bold=True, color="FFFFFF", size=11)
    border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    center_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
    left_align = Alignment(horizontal="left", vertical="center", wrap_text=True)
    
    # Column headers
    headers = ["Field ID", "Predicted Harvest Date"] + EVALUATORS
    for col_idx, header in enumerate(headers, start=1):
        cell = ws_eval.cell(row=1, column=col_idx, value=header)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = center_align
        cell.border = border
    
    # Set column widths
    ws_eval.column_dimensions['A'].width = 15
    ws_eval.column_dimensions['B'].width = 20
    for col_idx in range(3, 3 + len(EVALUATORS)):
        ws_eval.column_dimensions[get_column_letter(col_idx)].width = 12
    
    # Add data rows
    for row_idx, field in enumerate(fields_data, start=2):
        ws_eval.cell(row=row_idx, column=1, value=field["field_id"])
        ws_eval.cell(row=row_idx, column=2, value=field["harvest_date"])
        
        # Add empty cells for evaluator responses
        for col_idx in range(3, 3 + len(EVALUATORS)):
            cell = ws_eval.cell(row=row_idx, column=col_idx)
            cell.alignment = center_align
            cell.border = border
        
        # Light alternating row color
        if row_idx % 2 == 0:
            for col_idx in range(1, 3 + len(EVALUATORS)):
                ws_eval.cell(row=row_idx, column=col_idx).fill = PatternFill(
                    start_color="D9E8F5", end_color="D9E8F5", fill_type="solid"
                )
        
        # Apply borders to all data cells
        for col_idx in range(1, 3 + len(EVALUATORS)):
            ws_eval.cell(row=row_idx, column=col_idx).border = border
            if col_idx == 1 or col_idx == 2:
                ws_eval.cell(row=row_idx, column=col_idx).alignment = left_align
    
    # Freeze panes
    ws_eval.freeze_panes = "C2"
    
    # === SHEET 2: Instructions ===
    ws_instr = wb.create_sheet("Instructions")
    
    instr_content = [
        ["RGB Evaluation Instructions", ""],
        ["", ""],
        ["Overview:", ""],
        ["The generated RGB images visualize the predicted harvest dates for each field.", ""],
        ["The images are 3x3 grids showing satellite imagery from different dates", ""],
        ["centered on the predicted harvest date (the center/red-box image).", ""],
        ["", ""],
        ["What to Evaluate:", ""],
        ["For each field, determine if the predicted harvest date is CORRECT:", ""],
        ["", ""],
        ["Instructions for Reviewing:", ""],
        ["1. Look at the CENTER image (red box) - this is the predicted harvest date", ""],
        ["2. Compare to surrounding dates (before and after)", ""],
        ["3. Look for change in field color/status:", ""],
        ["   • BEFORE: Field appears GREEN (growing/healthy crop)", ""],
        ["   • AT PREDICTED DATE: Field shows BROWN/YELLOW (soil visible, ripe for harvest)", ""],
        ["   • AFTER: Field continues BROWN (post-harvest or dormant)", ""],
        ["", ""],
        ["How to Enter Your Assessment:", ""],
        ["Enter one of the following in your evaluator column for each field:", ""],
        ["  • YES  = Predicted date is CORRECT (brown/harvest-ready at center date)", ""],
        ["  • NO   = Predicted date is INCORRECT (not ready or already harvested)", ""],
        ["  • ? or MAYBE = Uncertain (cloudy images, unclear field status)", ""],
        ["", ""],
        ["Workflow Options:", ""],
        ["Option A (Divide Work): Assign 2-3 fields per evaluator (rows divided by column)", ""],
        ["Option B (Full Review):  Each evaluator reviews all fields (everyone fills all rows)", ""],
        ["Option C (Spot Check):  Each evaluator samples 5-10 random fields", ""],
        ["", ""],
        ["Image Location:", ""],
        ["All RGB images are in: /laravel_app/storage/app/angata/RGB/", ""],
        ["Format: field_{FIELD_ID}_{YYYY-MM-DD}_harvest_xlsx_harvest_rgb.png", ""],
        ["", ""],
        ["Notes:", ""],
        ["• Cloud cover may obscure ground truth - use best judgment", ""],
        ["• Fields with multiple bands or irregular shapes: focus on dominant area", ""],
        ["• Use context from previous/next dates to validate your assessment", ""],
    ]
    
    # Add instructions text
    for row_idx, row_data in enumerate(instr_content, start=1):
        for col_idx, value in enumerate(row_data, start=1):
            cell = ws_instr.cell(row=row_idx, column=col_idx, value=value)
            if row_idx == 1:
                cell.font = Font(bold=True, size=14)
            elif any(keyword in str(value) for keyword in ["Overview:", "Instructions", "Workflow", "Image Location", "Notes"]):
                cell.font = Font(bold=True, size=11)
            cell.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True)
    
    ws_instr.column_dimensions['A'].width = 50
    ws_instr.column_dimensions['B'].width = 80
    
    # Save workbook
    wb.save(OUTPUT_PATH)
    print(f"✓ Evaluation template created: {OUTPUT_PATH}")
    print(f"✓ {len(fields_data)} fields added to evaluation form")
    print(f"✓ Evaluators: {', '.join(EVALUATORS)}")

if __name__ == "__main__":
    create_evaluation_template()