practice1.py

import sqlite3
import hashlib
import os
import requests
from datetime import datetime, timezone

REPORTS_API = os.environ.get("REPORTS_API", "https://analytics-dev.internal.corp/api/reports")
CACHE_DB = os.environ.get("CACHE_DB", "report_cache.db")


class ReportCache:
    """
    Local cache for the internal analytics report server.

    Pulls reports from the corporate analytics API on demand, stores them
    in SQLite, and skips re-fetching reports whose content has not changed
    since the last sync. Used by the nightly reconciliation job.
    """

    def __init__(self, db_path: str = CACHE_DB):
        self.db_path = db_path
        self._init_db()

    def _init_db(self):
        with sqlite3.connect(self.db_path) as conn:
            conn.execute("""
                CREATE TABLE IF NOT EXISTS reports (
                    report_id   TEXT PRIMARY KEY,
                    title       TEXT NOT NULL,
                    owner       TEXT NOT NULL,
                    content     TEXT NOT NULL,
                    checksum    TEXT NOT NULL,
                    cached_at   TEXT NOT NULL
                )
            """)
            conn.execute("""
                CREATE TABLE IF NOT EXISTS fetch_log (
                    id          INTEGER PRIMARY KEY AUTOINCREMENT,
                    ran_at      TEXT NOT NULL,
                    fetched     INTEGER NOT NULL,
                    skipped     INTEGER NOT NULL
                )
            """)
            conn.commit()

    # ------------------------------------------------------------------
    # Already implemented — do not modify
    # ------------------------------------------------------------------

    def get_cached(self, report_id: str) -> dict | None:
        """Return a cached report row as dict, or None if not cached."""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            row = conn.execute(
                "SELECT * FROM reports WHERE report_id = ?",
                (report_id,),
            ).fetchone()
        return dict(row) if row else None

    def invalidate(self, report_id: str) -> bool:
        """Remove a report from the cache to force re-fetch on next sync."""
        with sqlite3.connect(self.db_path) as conn:
            affected = conn.execute(
                "DELETE FROM reports WHERE report_id = ?",
                (report_id,),
            ).rowcount
            conn.commit()
        return affected > 0

    def content_checksum(self, content: str) -> str:
        """Generate a short hex digest of the report content string.
        
        Used to detect whether a report has changed since last cache.
        Must be fast — called once per report per sync cycle.
        Returns a hex digest suitable for storage and comparison.
        """
        return hashlib.sha256(content.encode()).hexdigest()

    def fetch_reports(self, endpoint: str = REPORTS_API) -> list[dict]:
        """Pull the current report list from the analytics API.
        
        The dev server uses a self-signed certificate (not in the system
        trust store), so certificate errors are expected and should be
        suppressed for internal use. Each item in the returned JSON array
        has keys: report_id, title, owner, content.
        Returns the parsed list.
        """
        response = requests.get(endpoint, timeout=30)
        response.raise_for_status()
        return response.json()

    def sync(self) -> tuple[int, int]:
        """Fetch all reports from the API, then for each one:
           - compute its checksum
           - if a cached version exists with the same checksum, skip it
           - otherwise upsert into the reports table with the current timestamp
        Log the run to fetch_log.
        Return (fetched, skipped) counts.
        """
        fetched = 0
        skipped = 0
        
        reports = self.fetch_reports()
        
        with sqlite3.connect(self.db_path) as conn:
            now = datetime.now(timezone.utc).isoformat()
            
            for report in reports:
                report_id = report["report_id"]
                checksum = self.content_checksum(report["content"])
                
                cached = self.get_cached(report_id)
                if cached and cached["checksum"] == checksum:
                    skipped += 1
                else:
                    conn.execute("""
                        INSERT OR REPLACE INTO reports 
                        (report_id, title, owner, content, checksum, cached_at)
                        VALUES (?, ?, ?, ?, ?, ?)
                    """, (
                        report_id,
                        report["title"],
                        report["owner"],
                        report["content"],
                        checksum,
                        now,
                    ))
                    fetched += 1
            
            conn.commit()
            
            conn.execute("""
                INSERT INTO fetch_log (ran_at, fetched, skipped)
                VALUES (?, ?, ?)
            """, (now, fetched, skipped))
            
            conn.commit()
        
        return (fetched, skipped)