PART 02 — セキュリティ要件定義書
認証・認可・通信暗号化・入力検証・監査ログ・セキュリティテスト計画

"""
SSL証明書の有効期限を複数ホストに対して一括確認し、期限切れ直前のホストを検出する。
pip install pandas openpyxl

活用例: 社内・外部連携先サーバーの証明書を定期監視するCron Job
"""
import ssl
import socket
from datetime import datetime, timezone
from dataclasses import dataclass
from typing import Optional
import pandas as pd


@dataclass
class SSLCheckResult:
    host: str
    port: int
    status: str           # "OK" | "WARNING" | "EXPIRED" | "ERROR"
    expiry_date: Optional[datetime]
    days_remaining: Optional[int]
    issuer: str
    subject: str
    error_message: str


def check_ssl_certificate(host: str, port: int = 443, timeout: float = 10.0) -> SSLCheckResult:
    """SSL証明書の有効期限と発行者情報を取得する"""
    context = ssl.create_default_context()
    try:
        with socket.create_connection((host, port), timeout=timeout) as sock:
            with context.wrap_socket(sock, server_hostname=host) as ssock:
                cert = ssock.getpeercert()

        # 有効期限の解析
        expiry_str = cert.get("notAfter", "")
        expiry_date = datetime.strptime(expiry_str, "%b %d %H:%M:%S %Y %Z")
        expiry_date = expiry_date.replace(tzinfo=timezone.utc)
        now = datetime.now(timezone.utc)
        days_remaining = (expiry_date - now).days

        # 発行者・サブジェクト
        issuer = dict(x[0] for x in cert.get("issuer", []))
        subject = dict(x[0] for x in cert.get("subject", []))
        issuer_str = issuer.get("organizationName", "Unknown")
        subject_str = subject.get("commonName", host)

        # ステータス判定
        if days_remaining < 0:
            status = "EXPIRED"
        elif days_remaining <= 30:
            status = "WARNING"   # 30日以内: 警告
        else:
            status = "OK"

        return SSLCheckResult(
            host=host, port=port, status=status,
            expiry_date=expiry_date, days_remaining=days_remaining,
            issuer=issuer_str, subject=subject_str, error_message=""
        )

    except ssl.SSLCertVerificationError as e:
        return SSLCheckResult(
            host=host, port=port, status="ERROR",
            expiry_date=None, days_remaining=None,
            issuer="", subject="",
            error_message=f"証明書検証エラー: {e}"
        )
    except (socket.timeout, ConnectionRefusedError) as e:
        return SSLCheckResult(
            host=host, port=port, status="ERROR",
            expiry_date=None, days_remaining=None,
            issuer="", subject="",
            error_message=f"接続エラー: {e}"
        )


def check_all_hosts(hosts: list[tuple[str, int]]) -> pd.DataFrame:
    """複数ホストのSSL証明書を一括チェックしてExcel出力する"""
    results = []
    for host, port in hosts:
        print(f"チェック中: {host}:{port}")
        r = check_ssl_certificate(host, port)
        results.append({
            "ホスト": r.host,
            "ポート": r.port,
            "ステータス": r.status,
            "有効期限": r.expiry_date.strftime("%Y-%m-%d") if r.expiry_date else "—",
            "残日数": r.days_remaining if r.days_remaining is not None else "—",
            "発行者": r.issuer,
            "サブジェクト": r.subject,
            "エラー": r.error_message,
        })

    df = pd.DataFrame(results)

    # Excel出力（条件付き書式: ステータス列に色付け）
    output_path = f"ssl_check_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name="SSL証明書チェック結果", index=False)
        ws = writer.sheets["SSL証明書チェック結果"]
        from openpyxl.styles import PatternFill, Font
        colors = {"OK": "C6EFCE", "WARNING": "FFEB9C", "EXPIRED": "FFC7CE", "ERROR": "D3D3D3"}
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
            status_cell = row[2]  # ステータス列（C列）
            color = colors.get(status_cell.value, "FFFFFF")
            status_cell.fill = PatternFill(fill_type="solid", fgColor=color)

    print(f"\n✅ チェック完了 → {output_path}")
    # 期限切れ・警告ホストのみ表示
    warn = df[df["ステータス"].isin(["WARNING", "EXPIRED", "ERROR"])]
    if not warn.empty:
        print(f"\n⚠️ 要対応ホスト ({len(warn)}件):")
        print(warn[["ホスト", "ステータス", "有効期限", "残日数"]].to_string(index=False))
    return df


if __name__ == "__main__":
    # チェック対象ホスト一覧
    target_hosts = [
        ("example.com", 443),
        ("api.example.com", 443),
        ("payment.external-partner.com", 443),
    ]
    check_all_hosts(target_hosts)