26Y03a1

"""
step6_excel_builder.py — Partition-Aware FinOps Master Governance Report Builder (Cluster Isolated)
"""
import os
import argparse
import boto3
from botocore.exceptions import ClientError
import pandas as pd
from datetime import datetime, timedelta, timezone
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.drawing.image import Image
from openpyxl.formatting.rule import DataBarRule

# ─── 📂 경로 및 타임존 표준화 ─────────────────────────────────────────────
BASE_DATA_DIR = Path("./data")
OUTPUT_DIR    = BASE_DATA_DIR / "output"
BASE_PLOT_DIR = OUTPUT_DIR / "plots"

KST = timezone(timedelta(hours=9))

# ─── 🎨 엔터프라이즈 Muted Blue 테마 디자인 시트 스타일 정의 ──────────────────
C = dict(
    hdr_dark="1F4E79", hdr_mid="2E75B6", hdr_light="BDD7EE",
    accent="ED7D31", red="C00000", green="70AD47",
    yellow="FFC000", gray_row="F2F2F2", white="FFFFFF",
    border="9DC3E6", summary_bg="EBF3FB"
)

def ft(bold=False, size=10, color="000000", name="Arial"):
    return Font(name=name, bold=bold, size=size, color=color)
def fill(hex_color):
    return PatternFill("solid", fgColor=hex_color)
def thin_border():
    t = Side(style="thin", color=C["border"])
    return Border(left=t, right=t, top=t, bottom=t)
def center(wrap=False):
    return Alignment(horizontal="center", vertical="center", wrap_text=wrap)
def left(wrap=False):
    return Alignment(horizontal="left", vertical="center", wrap_text=wrap)

def parse_arguments():
    parser = argparse.ArgumentParser(description="FinOps Master Excel Sheet Builder")
    # 💡 INTEGRATED를 배제하고 무조건 명확한 대상 클러스터를 요구하도록 고정
    parser.add_argument("--cluster", type=str, required=True, choices=["COMPUTE", "STORAGE"], help="Target cluster type")
    return parser.parse_args()

def get_minio_client():
    return boto3.client(
        "s3", endpoint_url=os.getenv("MINIO_ENDPOINT"), aws_access_key_id=os.getenv("MINIO_ACCESS_KEY"),
        aws_secret_access_key=os.getenv("MINIO_SECRET_KEY"), region_name="us-east-1",
        config=boto3.session.Config(signature_version="s3v4")
    )

def main():
    args = parse_arguments()
    cluster_target = args.cluster.upper()
    
    print(f"🚀 [Step6] Executing Excel Master Builder for Target Cluster: {cluster_target}...")

    # ─── 🗂️ [적응형 매핑 주입 1] 4단계 거버넌스 산출 파일 바인딩 ───
    risk_file  = OUTPUT_DIR / f"gov_risk_oom_{cluster_target}.parquet"
    waste_file = OUTPUT_DIR / f"gov_waste_candidates_{cluster_target}.parquet"
    viol_file  = OUTPUT_DIR / f"gov_violations_{cluster_target}.parquet"
    
    # ─── 🎨 [적응형 매핑 주입 2] 3단계 차트 보관 디렉토리 격리 바인딩 ───
    PLOT_DIR = BASE_PLOT_DIR / cluster_target

    if not risk_file.exists():
        print(f"❌ Error: Required governance data for '{cluster_target}' not found. Please run step4 first.")
        return

    # 데이터 로드
    df_risk  = pd.read_parquet(risk_file)
    df_waste = pd.read_parquet(waste_file)
    df_viol  = pd.read_parquet(viol_file)

    wb = Workbook()
    
    # -------------------------------------------------------------------------
    # 시트 1: 0. 전사종합요약 & 격리 대시보드 차트 탑재
    # -------------------------------------------------------------------------
    ws_dash = wb.active
    ws_dash.title = "0. 종합 대시보드"
    ws_dash.sheet_view.showGridLines = False
    
    ws_dash.merge_cells("A1:N2")
    title_cell = ws_dash["A1"]
    title_cell.value = f"FinOps Infrastructure Governance Executive Summary [{cluster_target} CLUSTER]"
    title_cell.font = ft(bold=True, size=14, color=C["white"])
    title_cell.fill = fill(C["hdr_dark"])
    title_cell.alignment = center()
    
    # 💡 격리된 폴더 경로에서 이미지 차트 서칭 및 임베딩
    chart_map = {
        "B4": "chart6_status_donut.png",
        "H4": "chart1_cpu_req_vs_usage_by_workload.png" if cluster_target == "COMPUTE" else "chart2_mem_req_vs_usage_by_workload.png",
        "B20": "chart5_pareto_ns_waste.png",
        "H20": "chart4_cpu_efficiency_heatmap.png" if cluster_target == "COMPUTE" else "chart18_mem_waste_heatmap.png"
    }
    
    print("🎨 Embedding cluster-isolated analytical charts into Dashboard sheet...")
    for cell_loc, chart_name in chart_map.items():
        img_path = PLOT_DIR / chart_name
        if img_path.exists():
            img = Image(str(img_path))
            # 대시보드 레이아웃 균형을 위해 차트 해상도 스케일링 조율
            img.width, img.height = 420, 260
            ws_dash.add_image(img, cell_loc)
        else:
            print(f"  ⚠️ Missing chart asset: {img_path.name} (Placeholder will be used in Excel view)")

    # -------------------------------------------------------------------------
    # 시트 2: 4. 자원부족및OOM장애군 (Step4 산출물 매핑)
    # -------------------------------------------------------------------------
    ws_risk = wb.create_sheet("1. 자원부족 및 OOM 장애군")
    ws_risk.sheet_view.showGridLines = False
    
    headers_risk = ["Date", "Namespace", "Workload Type", "Pod Name", "Container", "Minutes Running", "Max Request", "Max Limit", "P95 Usage", "OOM Count", "Status", "Recommendation"]
    
    # 헤더 렌더링
    for col_idx, h in enumerate(headers_risk, 1):
        cell = ws_risk.cell(row=1, column=col_idx, value=h)
        cell.font = ft(bold=True, color=C["white"])
        cell.fill = fill(C["hdr_mid"])
        cell.alignment = center()
        cell.border = thin_border()
        
    # 데이터 쓰기
    for r_idx, row in enumerate(df_risk.itertuples(index=False), 2):
        bg_color = C["gray_row"] if r_idx % 2 == 1 else C["white"]
        for c_idx, val in enumerate(row, 1):
            cell = ws_risk.cell(row=r_idx, column=c_idx, value=val)
            cell.font = ft(size=9)
            cell.fill = fill(bg_color)
            cell.border = thin_border()
            if c_idx in [1, 2, 3, 11]: cell.alignment = center()
            if c_idx in [7, 8, 9]: cell.number_format = '#,##0.2f'

    # -------------------------------------------------------------------------
    # 시트 3: 2. CPU_Memory_과다할당 권고군 (Step4 산출물 매핑)
    # -------------------------------------------------------------------------
    ws_waste = wb.create_sheet("2. 과다할당 권고군")
    ws_waste.sheet_view.showGridLines = False
    
    headers_waste = ["Date", "Namespace", "Workload Type", "Pod Name", "Container", "Minutes Running", "Max Request", "Max Limit", "P95 Usage", "Throttled Max", "Allocated Hours", "Usage Hours", "Waste Hours", "Status"]
    
    for col_idx, h in enumerate(headers_waste, 1):
        cell = ws_waste.cell(row=1, column=col_idx, value=h)
        cell.font = ft(bold=True, color=C["white"])
        cell.fill = fill(C["hdr_mid"])
        cell.alignment = center()
        cell.border = thin_border()

    for r_idx, row in enumerate(df_waste.itertuples(index=False), 2):
        bg_color = C["gray_row"] if r_idx % 2 == 1 else C["white"]
        for c_idx, val in enumerate(row, 1):
            cell = ws_waste.cell(row=r_idx, column=c_idx, value=val)
            cell.font = ft(size=9)
            cell.fill = fill(bg_color)
            cell.border = thin_border()
            if c_idx in [1, 2, 3, 14]: cell.alignment = center()
            if c_idx in [7, 8, 9, 11, 12, 13]: cell.number_format = '#,##0.1f'

    # 📊 낭비 자산 가시성을 높이기 위한 데이터바 조건부 서식 부여 (Waste Hours 컬럼 저격)
    if len(df_waste) > 0:
        ws_waste.conditional_formatting.add(
            f"M2:M{len(df_waste)+1}", 
            DataBarRule(start_type="num", start_value=0, end_type="max", color="F2DCDB", showValue=True)
        )

    # -------------------------------------------------------------------------
    # 시트 4: 5. 리소스 미설정 위반군 (Step4 산출물 매핑)
    # -------------------------------------------------------------------------
    ws_viol = wb.create_sheet("3. 스펙 미설정 위반군")
    ws_viol.sheet_view.showGridLines = False
    
    for col_idx, h in enumerate(headers_risk, 1): # 위험군과 데이터 구조가 동일하므로 헤더 재사용
        cell = ws_viol.cell(row=1, column=col_idx, value=h)
        cell.font = ft(bold=True, color=C["white"])
        cell.fill = fill(C["hdr_dark"])
        cell.alignment = center()
        cell.border = thin_border()

    for r_idx, row in enumerate(df_viol.itertuples(index=False), 2):
        bg_color = C["gray_row"] if r_idx % 2 == 1 else C["white"]
        for c_idx, val in enumerate(row, 1):
            cell = ws_viol.cell(row=r_idx, column=c_idx, value=val)
            cell.font = ft(size=9)
            cell.fill = fill(bg_color)
            cell.border = thin_border()
            if c_idx in [1, 2, 3, 11]: cell.alignment = center()

    # 📐 전 시트 스캔 및 글로벌 열 너비 최적화 자동화 구문
    print("📐 Executing column auto-fit layout optimizer...")
    for ws in wb.worksheets:
        if ws.title == "0. 종합 대시보드": continue
        ws.freeze_panes = "A2"
        ws.auto_filter.ref = ws.dimensions
        for col in ws.columns:
            max_len = max(len(str(cell.value or '')) for cell in col)
            col_letter = get_column_letter(col[0].column)
            ws.column_dimensions[col_letter].width = max(max_len + 3, 12)

    # 💾 [최종 마감 및 S3 슈팅]
    target_date_str = datetime.now(KST).strftime("%Y%m%d")
    excel_filename = f"res_usage_report_{cluster_target.lower()}_{target_date_str}.xlsx"
    local_excel_path = OUTPUT_DIR / excel_filename
    
    wb.save(local_excel_path)
    print(f"✅ Local report compilation completed: {local_excel_path.name}")

    # AIStor 원격 업로드 집행
    s3_client = get_minio_client()
    bucket_name = os.getenv("MINIO_REPORT_BUCKET", "devops-test")
    object_key = f"reports/{cluster_target.lower()}/{excel_filename}"
    
    print(f"📤 Uploading final executive ledger to AIStor Tables Ecosystem...")
    try:
        s3_client.upload_file(str(local_excel_path), bucket_name, object_key)
        print(f"🏁 [FinOps 완수] Report securely stored ➡️ S3://{bucket_name}/{object_key}")
    except Exception as e:
        print(f"❌ S3 Upload failed due to network anomaly: {str(e)}")

if __name__ == "__main__":
    main()