#!/usr/bin/env python3
"""Fetch GitHub project data and store it in a local SQLite database.

Fetches repository metadata, the latest 5 releases, and the latest 10
issues for real projects via the GitHub REST API (no token required),
and generates mock data for the fictional "openclaw" project.

Stage 4.3 (schema unfreeze): the script is now incremental-friendly.
- Tables are created with CREATE TABLE IF NOT EXISTS (never DROPped).
- Rows are written with INSERT OR REPLACE keyed on UNIQUE indexes:
  projects.full_name, releases(project_id, tag_name),
  issues(project_id, html_url).
- releases/issues carry a `fetched_at` TEXT column (ISO-8601 UTC).
- --incremental: skip repos whose latest fetched_at is fresher than
  the repo's GitHub `updated_at` (no new upstream activity).
- --project <name>: only refresh a single project (by `name` or
  `full_name`).

Usage:
    python3 fetch_data.py                          # full refresh, no drops
    python3 fetch_data.py --incremental            # skip up-to-date repos
    python3 fetch_data.py --project opencode       # single project
    python3 fetch_data.py --incremental --project opencode
"""

import argparse
import logging
import os
import sqlite3
import sys
from datetime import datetime, timezone

import requests

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s",
)
logger = logging.getLogger(__name__)

GITHUB_API = "https://api.github.com"
HEADERS = {
    "Accept": "application/vnd.github+json",
    "User-Agent": "opencode-blog-showcase-fetcher",
}

# Paths are relative to this script's location so the script is portable.
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.normpath(
    os.path.join(SCRIPT_DIR, "..", "..", "data", "projects.db")
)

REAL_REPOS = ["NousResearch/hermes-agent", "sst/opencode"]
RELEASES_LIMIT = 5
ISSUES_LIMIT = 10

# ---------------------------------------------------------------------------
# Mock data for the fictional "openclaw" project.
# ---------------------------------------------------------------------------
MOCK_PROJECT = {
    "name": "openclaw",
    "full_name": "openclaw/openclaw",
    "description": "OpenClaw - a mock open-source CLI agent harness used for demo purposes.",
    "stars": 1284,
    "forks": 96,
    "language": "Python",
    "html_url": "https://github.com/openclaw/openclaw",
    "created_at": "2025-01-15T10:00:00Z",
    "updated_at": "2026-08-10T08:30:00Z",
    "source": "mock",
}

MOCK_RELEASES = [
    {
        "tag_name": "v0.3.0",
        "name": "v0.3.0 - Streaming tool calls",
        "body": "Adds streaming support for tool calls, improved retry logic, "
                "and a new `--dry-run` flag for the agent runner.",
        "published_at": "2026-07-20T12:00:00Z",
        "html_url": "https://github.com/openclaw/openclaw/releases/tag/v0.3.0",
    },
    {
        "tag_name": "v0.2.1",
        "name": "v0.2.1 - Bugfix release",
        "body": "Fixes a race condition in the scheduler and corrects "
                "token-count reporting for long sessions.",
        "published_at": "2026-06-05T09:00:00Z",
        "html_url": "https://github.com/openclaw/openclaw/releases/tag/v0.2.1",
    },
    {
        "tag_name": "v0.2.0",
        "name": "v0.2.0 - Plugin system",
        "body": "Introduces the plugin system with sandboxed execution and "
                "a declarative manifest format.",
        "published_at": "2026-05-01T14:30:00Z",
        "html_url": "https://github.com/openclaw/openclaw/releases/tag/v0.2.0",
    },
    {
        "tag_name": "v0.1.0",
        "name": "v0.1.0 - Initial public release",
        "body": "First public release of OpenClaw with basic agent loop, "
                "file editing tools, and shell execution.",
        "published_at": "2026-03-12T16:00:00Z",
        "html_url": "https://github.com/openclaw/openclaw/releases/tag/v0.1.0",
    },
]

MOCK_ISSUES = [
    {
        "title": "Agent loop hangs when tool output exceeds context window",
        "state": "open",
        "created_at": "2026-08-01T11:20:00Z",
        "html_url": "https://github.com/openclaw/openclaw/issues/42",
        "user": "mock-user-alice",
    },
    {
        "title": "Add support for custom system prompts per project",
        "state": "open",
        "created_at": "2026-07-18T08:45:00Z",
        "html_url": "https://github.com/openclaw/openclaw/issues/38",
        "user": "mock-user-bob",
    },
    {
        "title": "Scheduler race condition under parallel subagents",
        "state": "closed",
        "created_at": "2026-06-02T15:10:00Z",
        "html_url": "https://github.com/openclaw/openclaw/issues/31",
        "user": "mock-user-carol",
    },
    {
        "title": "Documentation: missing example for plugin manifest",
        "state": "closed",
        "created_at": "2026-05-20T10:05:00Z",
        "html_url": "https://github.com/openclaw/openclaw/issues/25",
        "user": "mock-user-dave",
    },
]


# ---------------------------------------------------------------------------
# GitHub API helpers
# ---------------------------------------------------------------------------
def github_get(path, params=None):
    """GET a GitHub API path, returning parsed JSON or None on failure."""
    url = f"{GITHUB_API}{path}"
    try:
        resp = requests.get(url, headers=HEADERS, params=params, timeout=20)
        if resp.status_code == 403 and "rate limit" in resp.text.lower():
            logger.error("GitHub rate limit exceeded for %s", url)
            return None
        resp.raise_for_status()
        return resp.json()
    except requests.RequestException as exc:
        logger.error("Request failed for %s: %s", url, exc)
        return None


def fetch_repo(full_name):
    """Fetch normalized metadata for one repository."""
    data = github_get(f"/repos/{full_name}")
    if not data:
        return None
    return {
        "name": data.get("name", full_name.split("/")[-1]),
        "full_name": data.get("full_name", full_name),
        "description": data.get("description") or "",
        "stars": data.get("stargazers_count", 0),
        "forks": data.get("forks_count", 0),
        "language": data.get("language") or "",
        "html_url": data.get("html_url", ""),
        "created_at": data.get("created_at", ""),
        "updated_at": data.get("updated_at", ""),
        "source": "real",
    }


def fetch_releases(full_name, limit=RELEASES_LIMIT):
    """Fetch the latest `limit` releases for a repository."""
    data = github_get(f"/repos/{full_name}/releases", params={"per_page": limit})
    if not data:
        return []
    releases = []
    for rel in data[:limit]:
        releases.append(
            {
                "tag_name": rel.get("tag_name", ""),
                "name": rel.get("name") or rel.get("tag_name", ""),
                "body": rel.get("body") or "",
                "published_at": rel.get("published_at") or rel.get("created_at", ""),
                "html_url": rel.get("html_url", ""),
            }
        )
    return releases


def fetch_issues(full_name, limit=ISSUES_LIMIT):
    """Fetch the latest `limit` issues (excluding pull requests)."""
    data = github_get(
        f"/repos/{full_name}/issues",
        params={"state": "all", "per_page": limit * 2, "sort": "created",
                "direction": "desc"},
    )
    if not data:
        return []
    issues = []
    for issue in data:
        # The issues endpoint also returns pull requests; skip them.
        if "pull_request" in issue:
            continue
        issues.append(
            {
                "title": issue.get("title", ""),
                "state": issue.get("state", ""),
                "created_at": issue.get("created_at", ""),
                "html_url": issue.get("html_url", ""),
                "user": (issue.get("user") or {}).get("login", ""),
            }
        )
        if len(issues) >= limit:
            break
    return issues


# ---------------------------------------------------------------------------
# Database helpers
# ---------------------------------------------------------------------------
SCHEMA = """
CREATE TABLE IF NOT EXISTS projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    full_name TEXT NOT NULL UNIQUE,
    description TEXT,
    stars INTEGER DEFAULT 0,
    forks INTEGER DEFAULT 0,
    language TEXT,
    html_url TEXT,
    created_at TEXT,
    updated_at TEXT,
    source TEXT NOT NULL DEFAULT 'real',
    starred INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS releases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER NOT NULL,
    tag_name TEXT,
    name TEXT,
    body TEXT,
    published_at TEXT,
    html_url TEXT,
    fetched_at TEXT,
    FOREIGN KEY (project_id) REFERENCES projects (id)
);

CREATE TABLE IF NOT EXISTS issues (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    state TEXT,
    created_at TEXT,
    html_url TEXT,
    user TEXT,
    fetched_at TEXT,
    FOREIGN KEY (project_id) REFERENCES projects (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_releases_project_tag
    ON releases(project_id, tag_name);

CREATE UNIQUE INDEX IF NOT EXISTS idx_issues_project_url
    ON issues(project_id, html_url);
"""

# Columns added in Stage 4.3 via ALTER TABLE when upgrading an existing DB.
_MIGRATIONS = (
    ("projects", "starred", "ALTER TABLE projects ADD COLUMN starred INTEGER DEFAULT 0"),
    ("releases", "fetched_at", "ALTER TABLE releases ADD COLUMN fetched_at TEXT"),
    ("issues", "fetched_at", "ALTER TABLE issues ADD COLUMN fetched_at TEXT"),
)


def _column_exists(conn, table, column):
    rows = conn.execute(f"PRAGMA table_info({table})").fetchall()
    return any(r[1] == column for r in rows)


def init_db(db_path):
    """Open (or create) the database, apply schema + migrations, return conn.

    Never drops tables. Existing rows are preserved; missing columns are
    added via ALTER TABLE (Stage 4.3 schema unfreeze).
    """
    os.makedirs(os.path.dirname(db_path), exist_ok=True)
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA foreign_keys = ON;")
    conn.executescript(SCHEMA)
    for table, column, ddl in _MIGRATIONS:
        if not _column_exists(conn, table, column):
            logger.info("Migrating: %s", ddl)
            conn.execute(ddl)
    conn.commit()
    logger.info("Initialized database at %s", db_path)
    return conn


def _utcnow_iso():
    return datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")


def upsert_project(conn, project):
    """INSERT OR REPLACE a project row keyed on UNIQUE(full_name).

    Returns the project id. Preserves the existing id (and any columns not
    in the INSERT, like `starred`) by reusing the current row when present.
    """
    cur = conn.cursor()
    existing = cur.execute(
        "SELECT id FROM projects WHERE full_name = ?", (project["full_name"],)
    ).fetchone()
    if existing:
        cur.execute(
            """
            UPDATE projects
               SET name = ?, description = ?, stars = ?, forks = ?,
                   language = ?, html_url = ?, created_at = ?, updated_at = ?,
                   source = ?
             WHERE full_name = ?
            """,
            (
                project["name"], project["description"], project["stars"],
                project["forks"], project["language"], project["html_url"],
                project["created_at"], project["updated_at"], project["source"],
                project["full_name"],
            ),
        )
        return existing[0]
    cur.execute(
        """
        INSERT OR REPLACE INTO projects
            (name, full_name, description, stars, forks, language,
             html_url, created_at, updated_at, source)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        (
            project["name"], project["full_name"], project["description"],
            project["stars"], project["forks"], project["language"],
            project["html_url"], project["created_at"], project["updated_at"],
            project["source"],
        ),
    )
    return cur.lastrowid


def upsert_releases(conn, project_id, releases, fetched_at):
    """INSERT OR REPLACE releases keyed on UNIQUE(project_id, tag_name)."""
    conn.executemany(
        """
        INSERT OR REPLACE INTO releases
            (project_id, tag_name, name, body, published_at, html_url, fetched_at)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        """,
        [
            (project_id, r["tag_name"], r["name"], r["body"],
             r["published_at"], r["html_url"], fetched_at)
            for r in releases
        ],
    )


def upsert_issues(conn, project_id, issues, fetched_at):
    """INSERT OR REPLACE issues keyed on UNIQUE(project_id, html_url)."""
    conn.executemany(
        """
        INSERT OR REPLACE INTO issues
            (project_id, title, state, created_at, html_url, user, fetched_at)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        """,
        [
            (project_id, i["title"], i["state"], i["created_at"],
             i["html_url"], i["user"], fetched_at)
            for i in issues
        ],
    )


def get_last_fetched_at(conn, full_name):
    """Most recent fetched_at across releases+issues for a project, or None."""
    row = conn.execute(
        """
        SELECT MAX(fetched_at) FROM (
            SELECT r.fetched_at AS fetched_at
              FROM releases r
              JOIN projects p ON p.id = r.project_id
             WHERE p.full_name = ?
            UNION ALL
            SELECT i.fetched_at AS fetched_at
              FROM issues i
              JOIN projects p ON p.id = i.project_id
             WHERE p.full_name = ?
        )
        """,
        (full_name, full_name),
    ).fetchone()
    return row[0] if row else None


# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------
def _resolve_targets(project_filter):
    """Return list of (kind, payload) tuples to process.

    kind == "real"  -> payload is a GitHub "owner/repo" full_name
    kind == "mock"  -> payload is the mock project dict
    """
    if project_filter is None:
        return ([("real", r) for r in REAL_REPOS]
                + [("mock", MOCK_PROJECT)])

    needle = project_filter.lower()
    targets = []
    for full_name in REAL_REPOS:
        short = full_name.split("/")[-1].lower()
        if needle in (full_name.lower(), short):
            targets.append(("real", full_name))
    if needle in (MOCK_PROJECT["full_name"].lower(), MOCK_PROJECT["name"].lower()):
        targets.append(("mock", MOCK_PROJECT))
    if not targets:
        logger.error("No project matches --project %r", project_filter)
        sys.exit(2)
    return targets


def _should_skip_incremental(conn, full_name, project):
    """Incremental mode: skip when upstream hasn't moved since last fetch.

    GitHub's `updated_at` on the repo is bumped by pushes/releases, so if
    our last fetched_at is newer, there is nothing new to pull.
    """
    last = get_last_fetched_at(conn, full_name)
    if not last:
        return False
    upstream = project.get("updated_at") or ""
    if upstream and last >= upstream:
        logger.info(
            "  -> skip %s (last fetched %s >= upstream updated_at %s)",
            full_name, last, upstream,
        )
        return True
    return False


def main(argv=None):
    parser = argparse.ArgumentParser(description=__doc__.splitlines()[0])
    parser.add_argument(
        "--incremental",
        action="store_true",
        help="Skip repos whose last fetched_at is newer than GitHub updated_at.",
    )
    parser.add_argument(
        "--project",
        metavar="NAME",
        default=None,
        help="Only refresh the named project (short name or owner/repo).",
    )
    args = parser.parse_args(argv)

    conn = init_db(DB_PATH)
    fetched_at = _utcnow_iso()
    try:
        for kind, payload in _resolve_targets(args.project):
            if kind == "mock":
                logger.info("Upserting mock project: %s", payload["name"])
                pid = upsert_project(conn, payload)
                upsert_releases(conn, pid, MOCK_RELEASES, fetched_at)
                upsert_issues(conn, pid, MOCK_ISSUES, fetched_at)
                continue

            full_name = payload
            logger.info("Fetching repo: %s", full_name)
            project = fetch_repo(full_name)
            if project is None:
                logger.error("Skipping %s (fetch failed)", full_name)
                continue
            if args.incremental and _should_skip_incremental(
                conn, full_name, project
            ):
                continue
            releases = fetch_releases(full_name)
            issues = fetch_issues(full_name)
            logger.info(
                "  -> %d releases, %d issues", len(releases), len(issues)
            )
            pid = upsert_project(conn, project)
            upsert_releases(conn, pid, releases, fetched_at)
            upsert_issues(conn, pid, issues, fetched_at)

        conn.commit()

        # Summary.
        cur = conn.cursor()
        for table in ("projects", "releases", "issues"):
            count = cur.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
            logger.info("Table %-10s: %d rows", table, count)
    except sqlite3.Error as exc:
        conn.rollback()
        logger.error("Database error: %s", exc)
        sys.exit(1)
    finally:
        conn.close()

    logger.info("Done. Database written to %s", DB_PATH)


if __name__ == "__main__":
    main()