"""書籍の一覧サイトから全商品の情報を集めて、Excelにまとめるツール（制作サンプル）。

対象：Books to Scrape（https://books.toscrape.com/）
      データ収集の練習用に公開されているサイトです。実際の案件では、対象サイトの
      利用規約と robots.txt を確認し、許可されている範囲でだけ収集します。

使い方:
    python collect_books.py books.xlsx

工夫:
    ・1ページごとに1秒あけてアクセスし、相手のサーバーに負担をかけない
    ・通信に失敗したページは3回までやり直し、それでもだめなら記録して続ける
    ・途中で止まっても、集めた分は保存する
"""

import re
import sys
import time
from collections import Counter, defaultdict
from urllib.parse import urljoin

import requests
from bs4 import BeautifulSoup
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, PatternFill

BASE = "https://books.toscrape.com/"
WAIT = 1.0
RATING = {"One": 1, "Two": 2, "Three": 3, "Four": 4, "Five": 5}
session = requests.Session()
session.headers["User-Agent"] = "portfolio-sample-collector/1.0 (contact via CrowdWorks: 12kkoei)"


def get(url: str) -> BeautifulSoup | None:
    for i in range(3):
        try:
            r = session.get(url, timeout=20)
            r.raise_for_status()
            r.encoding = "utf-8"
            return BeautifulSoup(r.text, "html.parser")
        except requests.RequestException as e:
            print(f"  再試行 {i + 1}/3: {url} ({e})")
            time.sleep(3 * (i + 1))
    return None


def categories() -> dict[str, str]:
    """カテゴリ名 → カテゴリのURL"""
    soup = get(BASE)
    links = soup.select("div.side_categories ul li ul li a")
    return {a.get_text(strip=True): urljoin(BASE, a["href"]) for a in links}


def collect() -> tuple[list[dict], list[str]]:
    books, errors = [], []
    for cat, url in categories().items():
        page = url
        while page:
            time.sleep(WAIT)
            soup = get(page)
            if soup is None:
                errors.append(page)
                break
            for art in soup.select("article.product_pod"):
                a = art.select_one("h3 a")
                price = art.select_one("p.price_color").get_text(strip=True)
                books.append({
                    "タイトル": a["title"],
                    "カテゴリ": cat,
                    "価格（ポンド）": float(re.sub(r"[^\d.]", "", price)),
                    "評価（星）": RATING.get(art.select_one("p.star-rating")["class"][1], 0),
                    "在庫": "あり" if "In stock" in art.select_one("p.availability").get_text() else "なし",
                    "URL": urljoin(page, a["href"]),
                })
            nxt = soup.select_one("li.next a")
            page = urljoin(page, nxt["href"]) if nxt else None
        print(f"{cat}: 累計 {len(books)} 件")
    return books, errors


def save(books: list[dict], errors: list[str], path: str) -> None:
    wb = Workbook()
    head = PatternFill("solid", fgColor="3B5BDB")

    ws = wb.active
    ws.title = "書籍一覧"
    cols = list(books[0].keys())
    ws.append(cols)
    for b in books:
        ws.append([b[c] for c in cols])
        ws.cell(ws.max_row, 6).hyperlink = b["URL"]
    for c in ws[1]:
        c.fill, c.font = head, Font(bold=True, color="FFFFFF")
    ws.freeze_panes, ws.auto_filter.ref = "A2", f"A1:F{ws.max_row}"
    for col, w in zip("ABCDEF", (60, 22, 14, 11, 8, 70)):
        ws.column_dimensions[col].width = w

    # カテゴリ別の集計（件数・平均価格・平均評価）
    st = wb.create_sheet("カテゴリ別集計", 0)
    st.append(["カテゴリ", "件数", "平均価格（ポンド）", "平均評価（星）", "星5の件数"])
    by = defaultdict(list)
    for b in books:
        by[b["カテゴリ"]].append(b)
    for cat, items in sorted(by.items(), key=lambda kv: -len(kv[1])):
        st.append([cat, len(items),
                   round(sum(i["価格（ポンド）"] for i in items) / len(items), 2),
                   round(sum(i["評価（星）"] for i in items) / len(items), 2),
                   sum(1 for i in items if i["評価（星）"] == 5)])
    for c in st[1]:
        c.fill, c.font = head, Font(bold=True, color="FFFFFF")
    for col, w in zip("ABCDE", (24, 8, 18, 15, 11)):
        st.column_dimensions[col].width = w
    ch = BarChart()
    ch.title, ch.height, ch.width = "件数の多いカテゴリ（上位10）", 8, 18
    ch.add_data(Reference(st, min_col=2, min_row=1, max_row=11), titles_from_data=True)
    ch.set_categories(Reference(st, min_col=1, min_row=2, max_row=11))
    st.add_chart(ch, "G2")

    log = wb.create_sheet("収集ログ")
    log.append(["収集件数", len(books)])
    log.append(["カテゴリ数", len(by)])
    log.append(["取得できなかったページ", len(errors)])
    for e in errors:
        log.append(["", e])
    wb.save(path)


if __name__ == "__main__":
    sys.stdout.reconfigure(encoding="utf-8")
    out = sys.argv[1] if len(sys.argv) > 1 else "books.xlsx"
    t = time.time()
    books, errors = collect()
    save(books, errors, out)
    stars = Counter(b["評価（星）"] for b in books)
    print(f"完了: {len(books)} 件（エラー {len(errors)} ページ）/ {time.time() - t:.0f} 秒 / 星5: {stars[5]} 件 → {out}")