tender_engine/generators/excel_generator.py

"""
Excel Generator
---------------
Generates two Excel outputs from TenderData:

1. BOQ Excel  (BOQ_541339.xlsx)
   Mirrors the layout of BOQ_541339_2.26 Less.xlsx:
   - Header block: tender ID, procuring entity, package no, work name
   - Summary row: lot no, work description, location, security, work time, quoted %
   - BOQ item rows: item no, code, description, qty, unit, bwdb rate, amount, quoted rate, amount, %
   - Totals row

2. Work Plan Excel  (Work_Plan_541339.xlsx)
   Mirrors 7. Work Plan_541339.xlsx:
   - Header: package no, tender ID, work name
   - Year/month header rows
   - Activity rows (blank Gantt cells for user to fill)
"""

from pathlib import Path
from typing import List
from ..models.tender_data import TenderData, BOQItem, WorkActivity


# ── BOQ Excel ────────────────────────────────────────────────────────────────

def generate_boq_excel(output_path: str, td: TenderData) -> str:
    """Generate BOQ comparison Excel."""
    try:
        import openpyxl
        from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
        from openpyxl.utils import get_column_letter
    except ImportError:
        raise ImportError("openpyxl is required. Run: pip install openpyxl")

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Quot"

    thin = Side(style="thin")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)
    bold = Font(bold=True)
    center = Alignment(horizontal="center", vertical="center", wrap_text=True)
    left = Alignment(horizontal="left", vertical="center", wrap_text=True)
    header_fill = PatternFill("solid", fgColor="BDD7EE")  # light blue
    yellow_fill = PatternFill("solid", fgColor="FFFF00")

    # ── Row 2: Tender ID + Procuring entity ─────────────────────────
    ws.merge_cells("B2:C2")
    _wc(ws, "B2", f"Tender ID: {td.tender_id}", bold, center, header_fill, border)
    ws.merge_cells("D2:H2")
    _wc(ws, "D2", td.procuring_entity, bold, center, header_fill, border)
    ws.merge_cells("I2:L2")
    _wc(ws, "I2", "Quoted Rate (%)", bold, center, header_fill, border)

    # ── Row 3: Departmental Estimation label ─────────────────────────
    ws.merge_cells("D3:H3")
    _wc(ws, "D3", "Departmental Estimation", bold, center, header_fill, border)

    # ── Row 5: Column headers ────────────────────────────────────────
    headers_row5 = ["", "Lot No.", "Lot No.", "Lot Description", "Location",
                    "Tender Security", "Tender Security", "Work Time",
                    "Quoted Rate (%)", "", "", ""]
    for col, val in enumerate(headers_row5, 1):
        cell = ws.cell(row=5, column=col, value=val)
        cell.font = bold
        cell.alignment = center
        cell.border = border

    # ── Row 6: Summary data row ──────────────────────────────────────
    work_period = f"{td.start_date}\nto \n{td.completion_date}\n"
    quoted_pct = td.quoted_rate_percent  # e.g. -0.02255
    row6 = ["", td.package_no, td.package_no, td.work_name, td.location,
            td.tender_security_amount, td.tender_security_amount,
            work_period, quoted_pct, quoted_pct, quoted_pct, quoted_pct]
    for col, val in enumerate(row6, 1):
        cell = ws.cell(row=6, column=col, value=val)
        cell.alignment = center
        cell.border = border

    # ── Row 8: BOQ section header ─────────────────────────────────────
    ws.merge_cells("B8:C8")
    _wc(ws, "B8", f"Tender ID: {td.tender_id}", bold, center, header_fill, border)
    ws.merge_cells("D8:H8")
    _wc(ws, "D8", f"Bill of Quantities (BOQ)_{td.rate_schedule_ref}", bold, center, header_fill, border)
    ws.merge_cells("I8:L8")
    _wc(ws, "I8", "Quoted Rate", bold, center, header_fill, border)

    # ── Row 9: BOQ column headers ─────────────────────────────────────
    boq_headers = ["", "Item No.", "Item Code", "Item Description", "Quantity", "Unit",
                   "BWDB\nRate", "BWDB\nAmount", "Quoted\nRate", "CFT Rate",
                   "Quoted\nAmount", "%"]
    for col, val in enumerate(boq_headers, 1):
        cell = ws.cell(row=9, column=col, value=val)
        cell.font = bold
        cell.alignment = center
        cell.fill = header_fill
        cell.border = border

    # ── BOQ data rows ─────────────────────────────────────────────────
    data_start = 10
    for i, item in enumerate(td.boq_items):
        r = data_start + i
        row_data = [
            "", item.item_no, item.item_code, item.description,
            item.quantity, item.unit, item.bwdb_rate, item.bwdb_amount,
            item.quoted_rate, "-", item.quoted_amount, item.percent_diff
        ]
        for col, val in enumerate(row_data, 1):
            cell = ws.cell(row=r, column=col, value=val)
            cell.border = border
            cell.alignment = left if col == 4 else center

    # ── Totals row ───────────────────────────────────────────────────
    last_r = data_start + len(td.boq_items)
    ws.cell(row=last_r, column=8, value=td.departmental_estimate).font = bold
    ws.cell(row=last_r, column=11, value=td.quoted_total).font = bold
    ws.cell(row=last_r, column=12, value=td.quoted_rate_percent).font = bold

    # ── Column widths ────────────────────────────────────────────────
    col_widths = [2, 12, 14, 55, 12, 8, 12, 16, 12, 12, 16, 10]
    for i, w in enumerate(col_widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w

    ws.row_dimensions[6].height = 60

    wb.save(output_path)
    print(f"  [OK] Generated: {Path(output_path).name}")
    return output_path


# ── Work Plan Excel ──────────────────────────────────────────────────────────

def generate_work_plan_excel(output_path: str, td: TenderData) -> str:
    """Generate Work Plan (Gantt) Excel."""
    try:
        import openpyxl
        from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
        from openpyxl.utils import get_column_letter
    except ImportError:
        raise ImportError("openpyxl is required. Run: pip install openpyxl")

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = td.tender_id

    thin = Side(style="thin")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)
    bold = Font(bold=True)
    center = Alignment(horizontal="center", vertical="center", wrap_text=True)
    left = Alignment(horizontal="left", vertical="center", wrap_text=True)
    header_fill = PatternFill("solid", fgColor="BDD7EE")

    # Determine month columns from td.work_months
    months = td.work_months if td.work_months else [
        "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec",
        "Jan", "Feb", "Mar", "Apr", "May", "Jun"
    ]
    n_months = len(months)

    # ── Row 2: Package + WORK SCHEDULE + Tender ID ───────────────────
    ws.merge_cells(f"A2:B2")
    _wc(ws, "A2", f"Package :-{td.package_no}", bold, center, header_fill, border)
    ws.merge_cells(f"C2:{get_column_letter(2 + n_months // 2)}2")
    _wc(ws, "C2", "WORK SCHEDULE", bold, center, header_fill, border)
    tender_id_col = get_column_letter(3 + n_months // 2)
    ws.merge_cells(f"{tender_id_col}2:{get_column_letter(2 + n_months)}2")
    _wc(ws, f"{tender_id_col}2", f"Tender ID: {td.tender_id}", bold, center, header_fill, border)

    # ── Row 3-4: Work name (merged across all cols) ───────────────────
    total_cols = 2 + n_months
    ws.merge_cells(f"A3:{get_column_letter(total_cols)}4")
    _wc(ws, "A3", f'Name of Work: "{td.work_name}"', bold, center, None, border)
    ws.row_dimensions[3].height = 50

    # ── Row 6: Year headers ───────────────────────────────────────────
    _wc(ws, "A6", "Sl. No.", bold, center, header_fill, border)
    _wc(ws, "B6", "Activity", bold, center, header_fill, border)
    # Determine year for each month
    start_year = td.work_start_year
    end_year = td.work_end_year
    year_for_month = _month_years(months, start_year, end_year)
    prev_year = None
    year_start_col = 3
    for m_idx, (month, year) in enumerate(zip(months, year_for_month)):
        col_letter = get_column_letter(3 + m_idx)
        ws.cell(row=6, column=3 + m_idx, value=year).font = bold
        ws.cell(row=6, column=3 + m_idx).alignment = center
        ws.cell(row=6, column=3 + m_idx).fill = header_fill
        ws.cell(row=6, column=3 + m_idx).border = border

    # ── Row 7: Month headers ──────────────────────────────────────────
    _wc(ws, "A7", "Sl. No.", bold, center, header_fill, border)
    _wc(ws, "B7", "Activity", bold, center, header_fill, border)
    for m_idx, month in enumerate(months):
        col_letter = get_column_letter(3 + m_idx)
        _wc(ws, f"{col_letter}7", month, bold, center, header_fill, border)

    # ── Activity rows ─────────────────────────────────────────────────
    activities = td.work_activities if td.work_activities else _default_activities()
    for a_idx, act in enumerate(activities):
        r = 8 + a_idx
        ws.cell(row=r, column=1, value=act.sl_no).border = border
        ws.cell(row=r, column=2, value=act.activity).alignment = left
        ws.cell(row=r, column=2).border = border
        ws.row_dimensions[r].height = 30
        for m_idx in range(n_months):
            ws.cell(row=r, column=3 + m_idx).border = border

    # ── Column widths ─────────────────────────────────────────────────
    ws.column_dimensions["A"].width = 8
    ws.column_dimensions["B"].width = 42
    for m_idx in range(n_months):
        ws.column_dimensions[get_column_letter(3 + m_idx)].width = 6

    wb.save(output_path)
    print(f"  [OK] Work Plan generated: {Path(output_path).name}")
    return output_path


# ── Helpers ──────────────────────────────────────────────────────────────────

def _wc(ws, cell_ref, value, font=None, alignment=None, fill=None, border=None):
    """Write cell with optional formatting."""
    c = ws[cell_ref]
    c.value = value
    if font:
        c.font = font
    if alignment:
        c.alignment = alignment
    if fill:
        c.fill = fill
    if border:
        c.border = border


def _month_years(months: List[str], start_year: int, end_year: int) -> List[int]:
    """Assign a year to each month label based on calendar order."""
    _order = ["Jan", "Feb", "Mar", "Apr", "May", "Jun",
              "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
    years = []
    current_year = start_year
    prev_idx = -1
    for m in months:
        idx = next((i for i, mo in enumerate(_order) if mo.lower() == m.lower()[:3]), 0)
        if idx < prev_idx:
            current_year += 1
        years.append(current_year)
        prev_idx = idx
    return years


def _default_activities() -> List[WorkActivity]:
    from ..models.tender_data import WorkActivity
    return [
        WorkActivity("i",    "Site Preparation & Mobilization."),
        WorkActivity("ii",   "Procurement of Man power, Equipment, Materials & etc."),
        WorkActivity("iii",  "E/W in excavation/re-excavation of khal & foundation trench etc."),
        WorkActivity("iv",   "Manufacturing of C.C. Blocks"),
        WorkActivity("v",    "Supply of Sand Filter, Geo-Textile Filter, Khoa filter etc."),
        WorkActivity("vi",   "Dumping & Placing C.C Blocks"),
        WorkActivity("vii",  "Construction Of Herring Bone Bond (HBB) Brick Road."),
        WorkActivity("viii", "Supplying, Filling & Laying Geo-Tube"),
        WorkActivity("ix",   "Fine dressing and close turfing of the slopes and the crest of embankment"),
        WorkActivity("x",    "De-mobilization & Site handover"),
    ]