import openpyxl
import copy
import shutil
import os

def update_timeline():
    excel_path = "PDF Editor Timeline.xlsx"
    backup_path = "PDF Editor Timeline.xlsx.bak"
    
    if os.path.exists(excel_path):
        shutil.copyfile(excel_path, backup_path)
        print(f"Created backup at {backup_path}")
    else:
        print(f"Error: {excel_path} not found.")
        return

    wb = openpyxl.load_workbook(excel_path)
    
    tasks_data = {
        "FreeType + HarfBuzz wrappers + LRU glyph cache": [
            ("FreeType Integration", "1 Day", "22-05-26", "22-05-26", "FreeType library initialized; FT_Face successfully loaded from font buffer", "", "Base integration for the entire typography engine.", "Wk 2", "Wk 2", "Critical"),
            ("HarfBuzz Integration", "1 Day", "22-05-26", "22-05-26", "HarfBuzz library linked; hb_font and hb_buffer created from FT_Face", "", "Required for shaping Unicode and layout engine.", "Wk 2", "Wk 2", "Critical"),
            ("FontFace Architecture", "1-2 Days", "22-05-26", "22-05-26", "RAII wrapper for FT_Face and hb_font_t; clean memory management", "", "Encapsulates font face lifecycle in C++.", "Wk 2", "Wk 2", "High"),
            ("FontLoader System", "1 Day", "22-05-26", "22-05-26", "Loads raw font binary data (TTF/OTF) from memory buffer into FontFace", "", "Supports loading from system or custom paths.", "Wk 2", "Wk 2", "High"),
            ("FontResolver System", "1-2 Days", "22-05-26", "22-05-26", "Resolves font requests (family, weight, italic) to best matching local font", "", "Determines which font to load dynamically.", "Wk 2", "Wk 2", "High"),
            ("Font Metrics Engine", "1 Day", "22-05-26", "23-05-26", "Calculates ascender, descender, line gap, and scale factors", "", "Ensures vertical text alignment is correct.", "Wk 2", "Wk 3", "High"),
            ("Advance Width Calculation", "1 Day", "22-05-26", "23-05-26", "Retrieves horizontal advances for individual glyphs via FreeType / HarfBuzz", "", "Needed to correctly advance cursor during text rendering.", "Wk 2", "Wk 3", "High"),
            ("Glyph Lookup System", "1 Day", "22-05-26", "23-05-26", "Maps unicode character points to glyph indices accurately", "", "Core of character representation.", "Wk 2", "Wk 3", "High"),
            ("Missing Glyph Detection", "1 Day", "23-05-26", "23-05-26", "Identifies when a font lacks a glyph for a codepoint; triggers fallback", "", "Prevents rendering blank spaces / boxes (tofu).", "Wk 3", "Wk 3", "Med"),
            ("LRU Glyph Cache", "1-2 Days", "22-05-26", "23-05-26", "Caches rendered glyph bitmaps; cache hits >90% on text corpus", "", "Crucial for text rendering performance.", "Wk 2", "Wk 3", "Critical"),
            ("Runtime Font Cache", "1 Day", "22-05-26", "23-05-26", "Stores loaded FontFace instances globally to prevent reloading same font resource", "", "Speeds up multi-page renders.", "Wk 2", "Wk 3", "High"),
            ("Thread-Safe Font Engine", "1 Day", "22-05-26", "23-05-26", "All font engine components are thread-safe; concurrent access protected by mutexes", "", "Supports background parsing and parallel rasterization.", "Wk 2", "Wk 3", "High"),
            ("Memory Management & RAII", "1 Day", "22-05-26", "22-05-26", "Zero memory leaks under ASan when loading and unloading fonts", "", "Strict enforcement of ownership semantics.", "Wk 2", "Wk 2", "Critical"),
            ("HarfBuzz Ligature & Kerning Support", "1 Day", "22-05-26", "23-05-26", "Applies advanced open type features (liga, kern) correctly during text shaping", "", "Ensures professional typesetting look.", "Wk 2", "Wk 3", "High"),
            ("Unicode Text Shaping Pipeline", "2 Days", "22-05-26", "23-05-26", "Shapes incoming UTF-8 string into shaped glyph run with correct advance and offsets", "", "Translates abstract string to concrete spatial coordinates.", "Wk 2", "Wk 3", "Critical")
        ],
        "PDF font resource loader (Type1/TrueType/CID)": [
            ("PDF Font Resource Loader", "2 Days", "23-05-26", "25-05-26", "Loads font resource streams directly from PDF dictionaries", "", "Essential for rendering documents with embedded fonts.", "Wk 3", "Wk 3", "Critical"),
            ("TrueType Font Support", "1 Day", "23-05-26", "24-05-26", "Parses and renders standard TrueType (.ttf) fonts", "", "Handles glyph outline rendering for TrueType format.", "Wk 3", "Wk 3", "High"),
            ("OpenType Font Support", "1 Day", "24-05-26", "24-05-26", "Parses and renders standard OpenType (.otf) fonts", "", "Handles CFF outlines and advanced OpenType tables.", "Wk 3", "Wk 3", "High"),
            ("Type1 Font Support", "1 Day", "24-05-26", "25-05-26", "Parses and renders legacy Type 1 postscript fonts", "", "Needed for legacy PDF documents.", "Wk 3", "Wk 3", "High"),
            ("CIDFont Support", "2 Days", "25-05-26", "26-05-26", "Correctly maps character IDs to glyph indices for CIDFonts", "", "Crucial for large character sets (CJK).", "Wk 3", "Wk 4", "Critical"),
            ("Embedded Font Loading", "1 Day", "23-05-26", "24-05-26", "Extracts and loads embedded font file streams (FontFile, FontFile2, FontFile3)", "", "Prevents font missing errors.", "Wk 3", "Wk 3", "Critical"),
            ("Subset Font Loading", "1-2 Days", "24-05-26", "26-05-26", "Successfully loads fonts containing only a subset of glyphs", "", "Common optimization in PDFs; requires robust glyph-index mapping.", "Wk 3", "Wk 4", "High"),
            ("Font Metadata Extraction", "1 Day", "23-05-26", "24-05-26", "Parses font names, flags, subtypes, and panose data", "", "Useful for debugging and font substitution matching.", "Wk 3", "Wk 3", "Med"),
            ("CJK (Chinese/Japanese/Korean) Support", "2 Days", "24-05-26", "26-05-26", "Successfully renders double-byte character sets and vertical writing parameters", "", "Crucial for international document rendering.", "Wk 3", "Wk 4", "High"),
            ("PDFium ↔ Font Engine Integration", "2 Days", "24-05-26", "26-05-26", "Synchronizes PDFium's font extraction with our FreeType/HarfBuzz pipeline", "", "Binds the layout interpreter to our rendering path.", "Wk 3", "Wk 4", "Critical"),
            ("Lazy Font Loading", "1 Day", "24-05-26", "26-05-26", "Loads font resource streams only when a glyph from that font needs to be rendered", "", "Optimizes document open times and memory consumption.", "Wk 3", "Wk 4", "Med")
        ],
        "Font substitution system (Liberation fonts)": [
            ("Font Fallback System", "1-2 Days", "25-05-26", "27-05-26", "Dynamically selects a fallback font when primary font lacks glyph", "", "Ensures all characters render.", "Wk 4", "Wk 4", "High"),
            ("Font Substitution System (Liberation Fonts)", "2 Days", "25-05-26", "01-06-26", "Substitutes missing system fonts (e.g. Arial) with metric-compatible Liberation fonts", "", "Keeps layout intact.", "Wk 4", "Wk 4", "High")
        ]
    }

    def copy_cell_style(src_cell, dest_cell):
        if src_cell.has_style:
            dest_cell.font = copy.copy(src_cell.font)
            dest_cell.fill = copy.copy(src_cell.fill)
            dest_cell.border = copy.copy(src_cell.border)
            dest_cell.alignment = copy.copy(src_cell.alignment)
            dest_cell.number_format = copy.copy(src_cell.number_format)
            dest_cell.protection = copy.copy(src_cell.protection)

    ws = wb['📋 Master Timeline']
    
    headers = [ws.cell(row=6, column=col).value for col in range(1, ws.max_column + 1)]
    has_dates = "Start Date" in headers
    
    if not has_dates:
        print("Inserting Start Date and End Date columns in Master Timeline...")
        ws.insert_cols(11, 2)
        ws.cell(row=6, column=11).value = "Start Date"
        ws.cell(row=6, column=12).value = "End Date"
        headers = [ws.cell(row=6, column=col).value for col in range(1, ws.max_column + 1)]
        copy_cell_style(ws.cell(row=6, column=10), ws.cell(row=6, column=11))
        copy_cell_style(ws.cell(row=6, column=10), ws.cell(row=6, column=12))

    parent_rows = {}
    for r in range(7, ws.max_row + 1):
        task_name = ws.cell(row=r, column=3).value
        if task_name in tasks_data:
            parent_rows[task_name] = r

    sorted_parents = sorted(parent_rows.items(), key=lambda x: x[1], reverse=True)
    
    total_inserted = 0
    for parent_name, row_idx in sorted_parents:
        subtasks = tasks_data[parent_name]
        num_sub = len(subtasks)
        print(f"Found parent '{parent_name}' at row {row_idx}. Inserting {num_sub} subtasks below it.")
        
        ws.insert_rows(row_idx + 1, num_sub)
        
        for i, sub in enumerate(subtasks):
            curr_row = row_idx + 1 + i
            task_t, dur, sd, ed, sc, dep, notes, wk_s, wk_e, pri = sub
            
            ws.cell(row=curr_row, column=1).value = "P1"
            ws.cell(row=curr_row, column=2).value = "Phase 1"
            ws.cell(row=curr_row, column=3).value = f"  - {task_t}"
            ws.cell(row=curr_row, column=4).value = "Dev 3"
            ws.cell(row=curr_row, column=5).value = wk_s
            ws.cell(row=curr_row, column=6).value = wk_e
            ws.cell(row=curr_row, column=7).value = dur
            ws.cell(row=curr_row, column=8).value = "Done"
            ws.cell(row=curr_row, column=9).value = pri
            ws.cell(row=curr_row, column=10).value = 1.0
            ws.cell(row=curr_row, column=11).value = sd
            ws.cell(row=curr_row, column=12).value = ed
            ws.cell(row=curr_row, column=13).value = sc
            ws.cell(row=curr_row, column=14).value = dep if dep else None
            ws.cell(row=curr_row, column=15).value = notes
            
            for col in range(1, 16):
                copy_cell_style(ws.cell(row=row_idx, column=col), ws.cell(row=curr_row, column=col))
                
        total_inserted += num_sub

    summary_row_idx = None
    for r in range(7, ws.max_row + 1):
        val = ws.cell(row=r, column=1).value
        if val and isinstance(val, str) and "=COUNTIF" in val:
            summary_row_idx = r
            break
            
    if summary_row_idx:
        print(f"Summary row found at {summary_row_idx}. Updating formulas to include new rows.")
        old_end_row = 69
        new_end_row = 69 + total_inserted
        
        
        f_a = ws.cell(row=summary_row_idx, column=1).value
        ws.cell(row=summary_row_idx, column=1).value = f_a.replace(f"D{old_end_row}", f"D{new_end_row}")
        
        f_d = ws.cell(row=summary_row_idx, column=4).value
        ws.cell(row=summary_row_idx, column=4).value = f_d.replace(f"I{old_end_row}", f"I{new_end_row}")
        
        f_h = ws.cell(row=summary_row_idx, column=8).value
        ws.cell(row=summary_row_idx, column=8).value = f_h.replace(f"H{old_end_row}", f"H{new_end_row}")
        
        f_k = ws.cell(row=summary_row_idx, column=13).value
        if f_k and "J7:J69" in f_k:
            ws.cell(row=summary_row_idx, column=13).value = f_k.replace(f"J{old_end_row}", f"J{new_end_row}")
        
        print("Master Timeline formulas updated successfully.")

    if '🔍 Phase Detail' in wb.sheetnames:
        ws_det = wb['🔍 Phase Detail']
        parent_rows_det = {}
        for r in range(7, ws_det.max_row + 1):
            task_name = ws_det.cell(row=r, column=3).value
            if task_name in tasks_data:
                parent_rows_det[task_name] = r
                
        sorted_parents_det = sorted(parent_rows_det.items(), key=lambda x: x[1], reverse=True)
        for parent_name, row_idx in sorted_parents_det:
            subtasks = tasks_data[parent_name]
            num_sub = len(subtasks)
            ws_det.insert_rows(row_idx + 1, num_sub)
            for i, sub in enumerate(subtasks):
                curr_row = row_idx + 1 + i
                task_t, dur, sd, ed, sc, dep, notes, wk_s, wk_e, pri = sub
                ws_det.cell(row=curr_row, column=1).value = "P1"
                ws_det.cell(row=curr_row, column=2).value = "Phase 1"
                ws_det.cell(row=curr_row, column=3).value = f"  - {task_t}"
                ws_det.cell(row=curr_row, column=4).value = "Dev 3"
                ws_det.cell(row=curr_row, column=5).value = wk_s
                ws_det.cell(row=curr_row, column=6).value = wk_e
                ws_det.cell(row=curr_row, column=7).value = dur
                ws_det.cell(row=curr_row, column=8).value = "Done"
                ws_det.cell(row=curr_row, column=9).value = pri
                ws_det.cell(row=curr_row, column=10).value = 1.0
                ws_det.cell(row=curr_row, column=11).value = sc
                
                for col in range(1, 12):
                    copy_cell_style(ws_det.cell(row=row_idx, column=col), ws_det.cell(row=curr_row, column=col))
        print("Phase Detail sheet updated successfully.")

    if '📅 Gantt' in wb.sheetnames:
        ws_gantt = wb['📅 Gantt']
        parent_rows_g = {}
        for r in range(5, ws_gantt.max_row + 1):
            task_name = ws_gantt.cell(row=r, column=2).value
            if task_name in tasks_data:
                parent_rows_g[task_name] = r
                
        sorted_parents_g = sorted(parent_rows_g.items(), key=lambda x: x[1], reverse=True)
        for parent_name, row_idx in sorted_parents_g:
            subtasks = tasks_data[parent_name]
            num_sub = len(subtasks)
            ws_gantt.insert_rows(row_idx + 1, num_sub)
            for i, sub in enumerate(subtasks):
                curr_row = row_idx + 1 + i
                task_t, dur, sd, ed, sc, dep, notes, wk_s, wk_e, pri = sub
                ws_gantt.cell(row=curr_row, column=1).value = "P1"
                ws_gantt.cell(row=curr_row, column=2).value = f"  - {task_t}"
                ws_gantt.cell(row=curr_row, column=3).value = "Dev 3"
                ws_gantt.cell(row=curr_row, column=4).value = dur
                
                wk_s_num = int(wk_s.split()[1])
                wk_e_num = int(wk_e.split()[1])
                for wk in range(1, 21):
                    col_idx = 4 + wk
                    if wk_s_num <= wk <= wk_e_num:
                        ws_gantt.cell(row=curr_row, column=col_idx).value = "▶"
                    else:
                        ws_gantt.cell(row=curr_row, column=col_idx).value = None
                
                for col in range(1, 25):
                    copy_cell_style(ws_gantt.cell(row=row_idx, column=col), ws_gantt.cell(row=curr_row, column=col))
        print("Gantt sheet updated successfully.")

    wb.save(excel_path)
    print(f"Successfully saved updated timeline to {excel_path}!")

if __name__ == "__main__":
    update_timeline()