#!/usr/bin/env python3
"""Create the Excel template for job description analysis."""

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

def create_template():
    wb = Workbook()
    ws = wb.active
    ws.title = "Tasks & Responsibilities"
    
    # Headers
    headers = [
        'Role/Position',
        'Department/Area',
        'Key Tasks',
        'Key Responsibilities',
        'Decision-Making Authority',
        'Reports To',
        'Direct Reports',
        'Source Document',
        'Notes'
    ]
    
    # Style for headers
    header_fill = PatternFill(start_color="366092", end_color="366092", 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')
    )
    
    # Write headers
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.value = header
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        cell.border = border
    
    # Add example row
    example_row = [
        'Example Role',
        'Example Department',
        'Task 1\nTask 2\nTask 3',
        'Responsibility 1\nResponsibility 2',
        'Decision 1\nDecision 2',
        'Manager Name',
        'Staff Member 1, Staff Member 2',
        'example_job_description.docx',
        'Additional notes or comments'
    ]
    
    for col_num, value in enumerate(example_row, 1):
        cell = ws.cell(row=2, column=col_num)
        cell.value = value
        cell.border = border
        cell.alignment = Alignment(vertical='top', wrap_text=True)
        if col_num == 2:  # Department column
            cell.font = Font(italic=True, color="808080")
    
    # Adjust column widths
    ws.column_dimensions['A'].width = 25
    ws.column_dimensions['B'].width = 20
    ws.column_dimensions['C'].width = 40
    ws.column_dimensions['D'].width = 40
    ws.column_dimensions['E'].width = 40
    ws.column_dimensions['F'].width = 25
    ws.column_dimensions['G'].width = 25
    ws.column_dimensions['H'].width = 30
    ws.column_dimensions['I'].width = 30
    
    # Set row heights
    ws.row_dimensions[2].height = 100
    
    # Freeze header row
    ws.freeze_panes = 'A2'
    
    # Add instructions sheet
    ws2 = wb.create_sheet("Instructions")
    instructions = [
        "TASK, RESPONSIBILITY & DECISION OVERVIEW TEMPLATE",
        "",
        "PURPOSE:",
        "This template helps organize tasks, responsibilities, and decision-making authority",
        "from job descriptions to support management structure reorganization.",
        "",
        "HOW TO USE:",
        "1. Run the analysis script: python analyze_job_descriptions.py",
        "2. The script will automatically populate this template with extracted data",
        "3. Review and refine the extracted information",
        "4. Fill in Department/Area column to group roles",
        "5. Add Notes column for additional context or reorganization suggestions",
        "",
        "COLUMN DESCRIPTIONS:",
        "• Role/Position: Job title or position name",
        "• Department/Area: Which department or area this role belongs to",
        "• Key Tasks: Specific tasks this role performs",
        "• Key Responsibilities: Main responsibilities and accountabilities",
        "• Decision-Making Authority: What decisions this role can make independently",
        "• Reports To: Who this role reports to (line manager)",
        "• Direct Reports: Who reports to this role",
        "• Source Document: Original job description file name",
        "• Notes: Additional comments, reorganization suggestions, or observations",
        "",
        "TIPS FOR REORGANIZATION:",
        "• Group roles by Department/Area to see current structure",
        "• Review Decision-Making Authority to identify decision bottlenecks",
        "• Check Reports To column to map current reporting structure",
        "• Use Notes column to document proposed changes",
        "• Look for overlapping responsibilities that could be consolidated",
    ]
    
    for row_num, instruction in enumerate(instructions, 1):
        cell = ws2.cell(row=row_num, column=1)
        cell.value = instruction
        if row_num == 1:
            cell.font = Font(bold=True, size=14)
        elif instruction.startswith("•"):
            cell.alignment = Alignment(indent=2)
    
    ws2.column_dimensions['A'].width = 80
    
    # Save
    output_path = "job_descriptions/TASK_RESPONSIBILITY_DECISION_TEMPLATE.xlsx"
    wb.save(output_path)
    print(f"Template created: {output_path}")

if __name__ == '__main__':
    create_template()