from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.utils import get_column_letter
def create_estimate_template(output_path: str, project_name: str):
    """Create construction cost estimate template."""
    wb = Workbook()
    ws = wb.active
    ws.title = "Cost Estimate"
    # Styles
    header_font = Font(bold=True, size=12)
    currency_format = '$#,##0.00'
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    # Project header
    ws['A1'] = "CONSTRUCTION COST ESTIMATE"
    ws['A1'].font = Font(bold=True, size=16)
    ws['A2'] = f"Project: {project_name}"
    ws['A3'] = "Date:"
    ws['B3'] = "=TODAY()"
    # Column headers (row 5)
    headers = ['CSI Code', 'Description', 'Quantity', 'Unit', 'Unit Cost',
               'Labor', 'Material', 'Equipment', 'Total']
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=5, column=col, value=header)
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = Alignment(horizontal='center')
    # Set column widths
    widths = [12, 40, 10, 8, 12, 12, 12, 12, 14]
    for i, width in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = width
    # Sample data rows with formulas
    for row in range(6, 26):  # 20 empty rows
        # Total formula: Labor + Material + Equipment
        ws.cell(row=row, column=9,
                value=f"=SUM(F{row}:H{row})")
        # Apply borders
        for col in range(1, 10):
            ws.cell(row=row, column=col).border = thin_border
    # Currency formatting
    for col in [5, 6, 7, 8, 9]:  # Cost columns
        for row in range(6, 26):
            ws.cell(row=row, column=col).number_format = currency_format
    # Subtotals section
    ws['G27'] = "SUBTOTAL"
    ws['I27'] = "=SUM(I6:I25)"
    ws['I27'].number_format = currency_format
    ws['G28'] = "Contingency (10%)"
    ws['I28'] = "=I27*0.10"
    ws['G29'] = "TOTAL"
    ws['I29'] = "=I27+I28"
    ws['I29'].font = Font(bold=True, size=14)
    wb.save(output_path)
    return output_path

def create_schedule_tracker(output_path: str, tasks: list):
    """Create construction schedule tracking spreadsheet."""
    wb = Workbook()
    ws = wb.active
    ws.title = "Schedule"
    # Headers
    headers = ['ID', 'Task', 'Start Date', 'End Date', 'Duration',
               'Progress', 'Status', 'Predecessor', 'Notes']
    # Status dropdown options
    from openpyxl.worksheet.datavalidation import DataValidation
    status_dv = DataValidation(
        type="list",
        formula1='"Not Started,In Progress,Complete,On Hold,Delayed"',
        allow_blank=True
    )
    ws.add_data_validation(status_dv)
    # Header row
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=header)
        cell.font = Font(bold=True)
    # Add tasks with formulas
    for i, task in enumerate(tasks, 2):
        ws.cell(row=i, column=1, value=task.get('id', i-1))
        ws.cell(row=i, column=2, value=task.get('name', ''))
        ws.cell(row=i, column=3, value=task.get('start', ''))
        ws.cell(row=i, column=4, value=task.get('end', ''))
        # Duration formula
        ws.cell(row=i, column=5, value=f"=D{i}-C{i}")
        # Progress bar (0-100%)
        ws.cell(row=i, column=6, value=task.get('progress', 0))
        ws.cell(row=i, column=6).number_format = '0%'
        # Status with validation
        status_cell = ws.cell(row=i, column=7, value=task.get('status', 'Not Started'))
        status_dv.add(status_cell)
    wb.save(output_path)
    return output_path

def create_rfi_log(output_path: str):
    """Create RFI tracking log."""
    wb = Workbook()
    ws = wb.active
    ws.title = "RFI Log"
    headers = [
        'RFI #', 'Date Submitted', 'Subject', 'Spec Section',
        'Drawing Ref', 'Submitted By', 'Assigned To',
        'Response Due', 'Date Responded', 'Status', 'Days Open'
    ]
    for col, header in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=header)
    # Days Open formula (in column K)
    for row in range(2, 100):
        ws.cell(row=row, column=11,
                value=f'=IF(I{row}="",TODAY()-B{row},I{row}-B{row})')
    # Conditional formatting for overdue items
    from openpyxl.formatting.rule import FormulaRule
    from openpyxl.styles import PatternFill
    red_fill = PatternFill(bgColor="FFC7CE")
    ws.conditional_formatting.add(
        'K2:K100',
        FormulaRule(formula=['K2>7'], fill=red_fill)
    )
    wb.save(output_path)
    return output_path

import pandas as pd
def process_qto_from_bim(bim_xlsx: str, output_path: str):
    """Process BIM export for quantity takeoff."""
    # Read BIM export
    df = pd.read_excel(bim_xlsx)
    # Group by category and type
    qto = df.groupby(['Category', 'Type']).agg({
        'Volume': 'sum',
        'Area': 'sum',
        'Length': 'sum',
        'Count': 'count'
    }).reset_index()
    # Rename columns for clarity
    qto.columns = ['Category', 'Type', 'Total Volume (m³)',
                   'Total Area (m²)', 'Total Length (m)', 'Element Count']
    # Create workbook with formatting
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        qto.to_excel(writer, sheet_name='QTO Summary', index=False)
        # Get workbook to apply formatting
        wb = writer.book
        ws = wb['QTO Summary']
        # Format numeric columns
        for col in ['C', 'D', 'E']:
            for row in range(2, len(qto) + 2):
                ws[f'{col}{row}'].number_format = '#,##0.00'
    return output_path

# Add error handling to formulas
formula_with_error_check = "=IFERROR(B5/C5, 0)"

# Convert BIM export to estimate
from ddc_toolkit import RevitExporter, CWICRSemanticSearch
# Export BIM data
exporter = RevitExporter()
bim_data = exporter.read_elements("project.xlsx")
# Process QTO
qto = process_qto_from_bim("project.xlsx", "qto_output.xlsx")
# Match to cost database
search = CWICRSemanticSearch()
for category in bim_data['Category'].unique():
    prices = search.search_work_items(category)
    # Apply prices to QTO...

pip install openpyxl pandas xlrd