# generate_xlsx.py
import json
import datetime
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter

RESULTS_FILE = "results.json"
OUTPUT_FILE  = "security_map_райони.xlsx"
TODAY        = datetime.date.today()

# ── Зареди резултатите ─────────────────────────────────────────
with open(RESULTS_FILE, encoding="utf-8") as f:
    results = json.load(f)

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Райони"

# ── Хедър ─────────────────────────────────────────────────────
HEADERS = [
    "Район", "Домейн", "IP", "Score", "Статус",
    "Изтича", "Дни",
    "HTTPS_наложен", "HSTS", "TLS",
    "X-Frame-Options", "Permissions-Policy", "Referrer-Policy",
    "X-Content-Type", "Бисквитки Secure", "Без Server", "robots.txt",
    "SPF", "DMARC", "DNSSEC", "Zone transfer", "DMARC политика",
]

header_fill = PatternFill("solid", fgColor="1F4E79")
header_font = Font(bold=True, color="FFFFFF", size=10)
thin = Side(style="thin", color="CCCCCC")
border = Border(left=thin, right=thin, top=thin, bottom=thin)

for col, h in enumerate(HEADERS, 1):
    cell = ws.cell(row=1, column=col, value=h)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    cell.border = border

ws.row_dimensions[1].height = 30

# ── Цветове по статус ─────────────────────────────────────────
STATUS_COLORS = {
    "good":     "C6EFCE",   # зелено
    "warning":  "FFEB9C",   # жълто
    "poor":     "FCBA73",   # оранжево
    "critical": "FFC7CE",   # червено
}

TRUE_FILL  = PatternFill("solid", fgColor="C6EFCE")
FALSE_FILL = PatternFill("solid", fgColor="FFC7CE")

def bool_cell(ws, row, col, value):
    cell = ws.cell(row=row, column=col, value="TRUE" if value else "FALSE")
    cell.fill = TRUE_FILL if value else FALSE_FILL
    cell.alignment = Alignment(horizontal="center")
    cell.border = border
    return cell

# ── Запълни редовете ──────────────────────────────────────────
for i, r in enumerate(results, 2):
    c = r["checks"]

    # Изчисли дни до изтичане
    expiry_str = c.get("cert_expiry")
    expiry_date = None
    days_left = None
    if expiry_str:
        try:
            expiry_date = datetime.datetime.strptime(expiry_str, "%Y-%m-%d").date()
            days_left = (expiry_date - TODAY).days
        except Exception:
            pass

    row_fill = PatternFill("solid", fgColor=STATUS_COLORS.get(r["status"], "FFFFFF"))

    def tc(col, value, center=True):
        cell = ws.cell(row=i, column=col, value=value)
        cell.fill = row_fill
        cell.border = border
        if center:
            cell.alignment = Alignment(horizontal="center")
        return cell

    tc(1, r["name"], center=False)
    tc(2, r["domain"], center=False)
    tc(3, c.get("ip") or "")
    tc(4, r["score"])
    tc(5, r["status"])

    # Изтича / Дни
    if expiry_date:
        cell_exp = ws.cell(row=i, column=6, value=expiry_date.strftime("%d.%m.%Y"))
        cell_exp.border = border
        cell_exp.alignment = Alignment(horizontal="center")
        cell_exp.fill = row_fill
    else:
        tc(6, "")

    cell_days = ws.cell(row=i, column=7, value=days_left)
    cell_days.border = border
    cell_days.alignment = Alignment(horizontal="center")
    # Оцвети дните: < 30 → червено, < 90 → жълто
    if days_left is not None:
        if days_left < 30:
            cell_days.fill = PatternFill("solid", fgColor="FFC7CE")
        elif days_left < 90:
            cell_days.fill = PatternFill("solid", fgColor="FFEB9C")
        else:
            cell_days.fill = row_fill

    # Boolean колони
    bool_cols = [
        (8,  c.get("https_enforced")),
        (9,  c.get("hsts")),
        (10, c.get("tls_version")),
        (11, c.get("x_frame_options")),
        (12, c.get("permissions_policy")),
        (13, c.get("referrer_policy")),
        (14, c.get("x_content_type")),
        (15, c.get("cookies_secure_httponly")),
        (16, c.get("no_server_banner")),
        (17, c.get("robots_txt")),
        (18, c.get("spf")),
        (19, c.get("dmarc")),
        (20, c.get("dnssec")),
        (21, c.get("no_zone_transfer")),
    ]
    for col, val in bool_cols:
        bool_cell(ws, i, col, bool(val))

    # DMARC политика
    cell_pol = ws.cell(row=i, column=22, value=c.get("dmarc_policy") or "")
    cell_pol.border = border
    cell_pol.alignment = Alignment(horizontal="center")
    cell_pol.fill = row_fill

# ── Ширина на колони ──────────────────────────────────────────
col_widths = [22, 28, 16, 7, 10, 12, 6,
              14, 7, 7,
              16, 18, 16, 16, 18, 12, 12,
              7, 7, 7, 7, 16]

for col, width in enumerate(col_widths, 1):
    ws.column_dimensions[get_column_letter(col)].width = width

# ── Замрази хедъра ────────────────────────────────────────────
ws.freeze_panes = "A2"

# ── Автофилтър ───────────────────────────────────────────────
ws.auto_filter.ref = f"A1:{get_column_letter(len(HEADERS))}1"

wb.save(OUTPUT_FILE)
print(f"✅ Файлът е записан: {OUTPUT_FILE} ({len(results)} района)")

input("\nНатисни Enter за изход...")