Maverick9876/document-extraction-workbench-cloud

1import asyncio2import io3import json4import os5import re6from datetime import datetime7from pathlib import Path8from typing import Dict, Tuple9 10import pandas as pd11from PIL import Image, ImageEnhance, ImageFilter12from tqdm import tqdm13from playwright.async_api import async_playwright, TimeoutError as PlaywrightTimeoutError14from ai_provider import get_ai_provider_label, request_vision_json15from workbench_env import load_app_env16 17try:18    import tkinter as tk19    from tkinter import filedialog20except ImportError:  # pragma: no cover21    tk = None22    filedialog = None23 24try:25    import pytesseract26except ImportError:27    pytesseract = None28 29# ---------- CONFIG ----------30load_app_env()31 32TIMEOUT_MS = 3000033HEADLESS = True  # Keep True to run without opening the browser window34SLOW_MO_MS = 035RETRIES = 236RETRY_BACKOFF_SEC = 0.837CHECKPOINT_EVERY = int(os.getenv("CHECKPOINT_EVERY", "10"))38MAX_CONCURRENCY = int(os.getenv("MAX_CONCURRENCY", "3"))39CONTENT_WAIT_MS = 1500040TESSERACT_CANDIDATE_PATHS = (41    r"C:\Program Files\Tesseract-OCR\tesseract.exe",42    r"C:\Program Files (x86)\Tesseract-OCR\tesseract.exe",43    str(Path.home() / "AppData" / "Local" / "Programs" / "Tesseract-OCR" / "tesseract.exe"),44)45 46NOT_FOUND_DETAILS = {47    "certificate_name": "NOT FOUND",48    "learner_name": "NOT FOUND",49    "issued_date": "NOT FOUND",50}51 52GENERIC_TITLES = {53    "accredible • certificates, badges and blockchain",54    "accredible - certificates, badges and blockchain",55}56 57EXTRACT_JS = """58() => {59    const getMeta = (selector) => {60        const el = document.querySelector(selector);61        return (el && el.content) ? el.content.trim() : "";62    };63 64    const pickBySelectors = (selectors) => {65        for (const selector of selectors) {66            const el = document.querySelector(selector);67            if (el && el.textContent) {68                const value = el.textContent.trim();69                if (value) return value;70            }71        }72        return "";73    };74 75    const findAfterLabel = (labels) => {76        const pageText = (document.body?.innerText || "").replace(/\\s+/g, " ");77        for (const label of labels) {78            const escaped = label.replace(/[.*+?^${}()|[\\]\\\\]/g, "\\\\$&");79            const rx = new RegExp(escaped + "\\\\s*[:\\-]?\\\\s*([^\\n|,;]+)", "i");80            const m = pageText.match(rx);81            if (m && m[1]) {82                const value = m[1].trim();83                if (value) return value;84            }85        }86        return "";87    };88 89    const bodyText = (document.body?.innerText || "").replace(/\\u00A0/g, " ");90    const lines = bodyText91        .split(/\\n+/)92        .map(line => line.replace(/\\s+/g, " ").trim())93        .filter(Boolean);94 95    const extractDateFromText = (text) => {96        if (!text) return "";97        const patterns = [98            /\\b(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:t(?:ember)?)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?)\\s+\\d{1,2},\\s+\\d{4}\\b/i,99            /\\b\\d{1,2}\\s+(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:t(?:ember)?)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?)\\s+\\d{4}\\b/i,100            /\\b\\d{4}-\\d{2}-\\d{2}\\b/,101            /\\b\\d{1,2}[/-]\\d{1,2}[/-]\\d{4}\\b/,102        ];103        for (const pattern of patterns) {104            const match = text.match(pattern);105            if (match) return match[0].trim();106        }107        return "";108    };109 110    const findStandaloneDateLine = () => {111        for (const line of lines) {112            if (line.length > 40) continue;113            const date = extractDateFromText(line);114            if (date && line.toLowerCase() === date.toLowerCase()) {115                return date;116            }117        }118        return "";119    };120 121    const findDateNearKeywords = (keywords) => {122        for (let i = 0; i < lines.length; i++) {123            const line = lines[i].toLowerCase();124            if (!keywords.some(keyword => line.includes(keyword))) {125                continue;126            }127 128            for (let offset = -3; offset <= 3; offset++) {129                const candidate = lines[i + offset];130                const date = extractDateFromText(candidate || "");131                if (date) return date;132            }133        }134        return "";135    };136 137    const certName =138        getMeta('meta[property="og:title"]') ||139        getMeta('meta[name="twitter:title"]') ||140        pickBySelectors([141            "[data-testid='certificate-title']",142            "[data-certificate-title]",143            "h1",144            ".certificate-title",145            ".cert-title",146            ".title"147        ]) ||148        (document.title ? document.title.trim() : "");149 150    const parseFromTitle = (titleText) => {151        if (!titleText) return { cert: "", learner: "" };152 153        // Normalize common bullet separators, including mojibake variants.154        const normalized = titleText.replace(155            /\\u2022|\\u00B7|\\u00E2\\u20AC\\u00A2|\\u00C3\\u00A2\\u00E2\\u201A\\u00AC\\u00C2\\u00A2/g,156            "|"157        );158        const segments = normalized.split("|").map(s => s.trim()).filter(Boolean);159 160        if (segments.length >= 3) {161            // Common credential pattern: "<certificate> | <learner> | <issuer>"162            return { cert: segments[0], learner: segments[segments.length - 2] };163        }164        return { cert: titleText.trim(), learner: "" };165    };166 167    const parsedTitle = parseFromTitle(certName);168 169    let learnerName =170        getMeta('meta[property="profile:username"]') ||171        pickBySelectors([172            "[data-testid='learner-name']",173            "[data-learner-name]",174            ".learner-name",175            ".recipient-name",176            ".student-name",177            "#learner-name",178            "#recipient-name"179        ]) ||180        findAfterLabel([181            "Learner Name",182            "Recipient Name",183            "Student Name",184            "Issued to",185            "Awarded to"186        ]);187 188    // Avoid generic site labels being mistaken as learner names.189    if (!learnerName || /credential\\.net|upgrad|iiit-b/i.test(learnerName)) {190        learnerName = parsedTitle.learner || "";191    }192 193    let finalCertName = parsedTitle.cert || certName || "";194    if (!finalCertName && certName) {195        finalCertName = certName;196    }197 198    const visibleCertificateDate =199        findDateNearKeywords([200            "completion date",201            "date of completion",202            "completed on",203            "completion",204            "programme",205            "program",206            "specialization",207            "specialisation",208            "certificate",209            "end date"210        ]) ||211        findStandaloneDateLine() ||212        pickBySelectors([213            "[data-testid='issue-date']",214            "[data-issued-date]",215            ".issue-date",216            ".issued-date",217            ".date-issued",218            "#issue-date"219        ]) ||220        findAfterLabel([221            "Completion Date",222            "Date of Completion",223            "Completed On",224            "End Date",225            "Certificate Date",226            "Date Issued",227            "Issued On",228            "Award Date",229            "Date"230        ]);231 232    const metadataDate =233        getMeta('meta[property="article:published_time"]') ||234        getMeta('meta[itemprop="datePublished"]') ||235        getMeta('meta[name="date"]') ||236        "";237 238    const issuedDate = visibleCertificateDate || metadataDate || "";239 240    return {241        certificate_name: finalCertName || "NOT FOUND",242        learner_name: learnerName || "NOT FOUND",243        visible_certificate_date: visibleCertificateDate || "",244        metadata_date: metadataDate || "",245        issued_date: issuedDate || "NOT FOUND"246    };247}248"""249def pick_input_csv() -> str:250    if tk is None or filedialog is None:251        raise RuntimeError("Tkinter is not available in this environment.")252    root = tk.Tk()253    root.withdraw()254    input_path = filedialog.askopenfilename(255        title="Select CSV file (links in first column, no header)",256        filetypes=[("CSV Files", "*.csv")],257    )258    if not input_path:259        raise SystemExit("No file selected. Exiting.")260    return input_path261 262 263def write_csv_safely(df: pd.DataFrame, target_path: str, is_checkpoint: bool) -> str:264    try:265        df.to_csv(target_path, index=False, header=False)266        return target_path267    except PermissionError:268        base = Path(target_path)269        suffix = datetime.now().strftime("%Y%m%d_%H%M%S")270        fallback = str(base.with_name(f"{base.stem}_autosave_{suffix}.csv"))271        df.to_csv(fallback, index=False, header=False)272        kind = "Checkpoint" if is_checkpoint else "Final save"273        print(f"\n{kind} path was locked. Saved to fallback file: {fallback}")274        return fallback275 276 277def parse_json_from_text(text: str):278    if not text:279        return None280 281    cleaned = text.strip()282    cleaned = re.sub(r"^```json\s*|\s*```$", "", cleaned, flags=re.IGNORECASE | re.DOTALL).strip()283    try:284        return json.loads(cleaned)285    except Exception:286        pass287 288    match = re.search(r"\{[\s\S]*\}", cleaned)289    if not match:290        return None291 292    try:293        return json.loads(match.group())294    except Exception:295        return None296 297 298def resolve_tesseract_path() -> str:299    if pytesseract is None:300        return ""301 302    configured = getattr(pytesseract.pytesseract, "tesseract_cmd", "") or ""303    if configured and Path(configured).exists():304        return configured305 306    for candidate in TESSERACT_CANDIDATE_PATHS:307        if Path(candidate).exists():308            pytesseract.pytesseract.tesseract_cmd = candidate309            return candidate310 311    return ""312 313 314DATE_PATTERNS = (315    r"\b(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:t(?:ember)?)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?)\s+\d{1,2},\s+\d{4}\b",316    r"\b\d{1,2}\s+(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:t(?:ember)?)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?)\s+\d{4}\b",317    r"\b\d{4}-\d{2}-\d{2}\b",318    r"\b\d{1,2}[/-]\d{1,2}[/-]\d{4}\b",319)320 321 322def extract_date_candidates(text: str):323    matches = []324    for pattern in DATE_PATTERNS:325        matches.extend(re.findall(pattern, text or "", flags=re.IGNORECASE))326    deduped = []327    seen = set()328    for item in matches:329        key = item.strip().lower()330        if key and key not in seen:331            deduped.append(item.strip())332            seen.add(key)333    return deduped334 335 336def normalize_date(value: str) -> str:337    if not value:338        return ""339 340    text = re.sub(r"\s+", " ", str(value)).strip()341    if not text:342        return ""343 344    formats = (345        "%B %d, %Y",346        "%b %d, %Y",347        "%d %B %Y",348        "%d %b %Y",349        "%Y-%m-%d",350        "%d/%m/%Y",351        "%d-%m-%Y",352        "%m/%d/%Y",353        "%m-%d-%Y",354    )355    for fmt in formats:356        try:357            return datetime.strptime(text, fmt).strftime("%B %d, %Y")358        except ValueError:359            continue360    return text361 362 363def candidate_score(text: str) -> int:364    lowered = text.lower()365    score = 0366    if any(word in lowered for word in ("completion", "completed", "programme", "program", "specialization", "specialisation", "certificate")):367        score += 3368    if re.fullmatch(r"\s*" + DATE_PATTERNS[0] + r"\s*", text, flags=re.IGNORECASE):369        score += 2370    if len(text.strip()) <= 28:371        score += 1372    return score373 374 375def select_best_date_from_text(text: str) -> str:376    if not text:377        return ""378 379    lines = [re.sub(r"\s+", " ", line).strip() for line in text.splitlines() if line.strip()]380    best = ""381    best_score = -1382 383    for line in lines:384        for candidate in extract_date_candidates(line):385            score = candidate_score(line)386            if score > best_score:387                best = candidate388                best_score = score389 390    if best:391        return normalize_date(best)392 393    candidates = extract_date_candidates(text)394    return normalize_date(candidates[0]) if candidates else ""395 396 397def build_ocr_image_variants(image_bytes: bytes):398    base = Image.open(io.BytesIO(image_bytes)).convert("RGB")399    width, height = base.size400 401    crops = [402        ("full", base),403        ("center", base.crop((int(width * 0.08), int(height * 0.18), int(width * 0.92), int(height * 0.86)))),404        ("date_band", base.crop((int(width * 0.18), int(height * 0.42), int(width * 0.82), int(height * 0.74)))),405    ]406 407    variants = []408    for label, image in crops:409        grayscale = image.convert("L")410        enlarged = grayscale.resize((grayscale.width * 2, grayscale.height * 2))411        enhanced = ImageEnhance.Contrast(enlarged).enhance(2.0)412        sharpened = enhanced.filter(ImageFilter.SHARPEN)413        variants.append((label, image))414        variants.append((f"{label}_ocr", sharpened))415    return variants416 417 418def pil_image_to_png_bytes(image: Image.Image) -> bytes:419    buffer = io.BytesIO()420    image.save(buffer, format="PNG")421    return buffer.getvalue()422 423 424def extract_date_with_tesseract(image_bytes: bytes) -> str:425    tesseract_cmd = resolve_tesseract_path()426    if not tesseract_cmd or pytesseract is None:427        return ""428 429    collected_text = []430    for _label, image in build_ocr_image_variants(image_bytes):431        try:432            text = pytesseract.image_to_string(433                image,434                config="--oem 3 --psm 6",435            )436        except Exception:437            continue438        if text:439            collected_text.append(text)440 441    return select_best_date_from_text("\n".join(collected_text))442 443 444def extract_date_with_openai(image_bytes: bytes, api_key: str = None) -> str:445    if not (api_key or os.getenv("OPENAI_API_KEY", "").strip() or os.getenv("GEMINI_API_KEY", "").strip()):446        return ""447 448    variants = dict(build_ocr_image_variants(image_bytes))449    focus_image = variants.get("date_band") or variants.get("center") or variants.get("full")450    focus_bytes = pil_image_to_png_bytes(focus_image)451    prompt = (452        "Read this certificate image and extract the visible completion or end date shown on the certificate body. "453        "Do not use metadata. Return strict JSON only like {\"date\": \"...\"}. "454        "If no visible date is readable, return {\"date\": null}."455    )456 457    schema = {458        "type": "object",459        "properties": {460            "date": {461                "type": ["string", "null"],462                "description": "The visible completion or end date shown on the certificate body"463            }464        },465        "required": ["date"],466        "additionalProperties": False467    }468 469    try:470        raw_text = request_vision_json(471            prompt=prompt,472            image_bytes=focus_bytes,473            mime_type="image/png",474            api_key=api_key,475            timeout_sec=90,476            temperature=0,477            max_output_tokens=120,478            response_schema=schema,479        )480        data = parse_json_from_text(raw_text)481        return normalize_date((data or {}).get("date") or "")482    except Exception:483        return ""484 485 486async def capture_certificate_screenshot(page) -> bytes:487    return await page.screenshot(type="png", full_page=True)488 489 490async def extract_visible_date_with_ocr(491    page,492    status_callback=None,493    use_openai_ocr: bool = True,494    openai_api_key: str = None,495) -> str:496    screenshot_bytes = await capture_certificate_screenshot(page)497 498    local_ocr_date = extract_date_with_tesseract(screenshot_bytes)499    if local_ocr_date:500        if status_callback:501            status_callback(f"OCR found visible certificate date with Tesseract: {local_ocr_date}")502        return local_ocr_date503 504    if use_openai_ocr:505        openai_ocr_date = extract_date_with_openai(screenshot_bytes, api_key=openai_api_key)506        if openai_ocr_date:507            if status_callback:508                status_callback(f"OCR found visible certificate date with {get_ai_provider_label()}: {openai_ocr_date}")509            return openai_ocr_date510 511    return ""512 513 514async def extract_details_with_retries(515    page,516    url: str,517    status_callback=None,518    use_openai_ocr: bool = True,519    openai_api_key: str = None,520) -> Dict[str, str]:521    if not isinstance(url, str):522        return dict(NOT_FOUND_DETAILS)523 524    clean_url = url.strip()525    if not clean_url.lower().startswith(("http://", "https://")):526        return dict(NOT_FOUND_DETAILS)527 528    for attempt in range(RETRIES + 1):529        try:530            await page.goto(clean_url, timeout=TIMEOUT_MS, wait_until="domcontentloaded")531            try:532                await page.wait_for_load_state("networkidle", timeout=CONTENT_WAIT_MS)533            except Exception:534                pass535 536            # credential.net title is generic at first render; wait until actual certificate title appears.537            try:538                await page.wait_for_function(539                    """540                    () => {541                        const t = (document.title || "").trim().toLowerCase();542                        return t && ![543                            "accredible • certificates, badges and blockchain",544                            "accredible - certificates, badges and blockchain"545                        ].includes(t);546                    }547                    """,548                    timeout=CONTENT_WAIT_MS,549                )550            except Exception:551                pass552 553            details = await page.evaluate(EXTRACT_JS)554            cert_lower = str(details.get("certificate_name", "")).strip().lower()555            visible_date = normalize_date(str(details.get("visible_certificate_date", "")).strip())556            metadata_date = normalize_date(str(details.get("metadata_date", "")).strip())557 558            if not visible_date:559                ocr_date = await extract_visible_date_with_ocr(560                    page,561                    status_callback=status_callback,562                    use_openai_ocr=use_openai_ocr,563                    openai_api_key=openai_api_key,564                )565                if ocr_date:566                    visible_date = ocr_date567 568            final_date = visible_date or metadata_date or "NOT FOUND"569            details["visible_certificate_date"] = visible_date570            details["metadata_date"] = metadata_date571            details["issued_date"] = final_date572 573            # If still generic, retry.574            if cert_lower in GENERIC_TITLES and attempt < RETRIES:575                await asyncio.sleep(RETRY_BACKOFF_SEC * (attempt + 1))576                continue577 578            return details579        except PlaywrightTimeoutError:580            if attempt < RETRIES:581                await asyncio.sleep(RETRY_BACKOFF_SEC * (attempt + 1))582            else:583                return dict(NOT_FOUND_DETAILS)584        except Exception:585            if attempt < RETRIES:586                await asyncio.sleep(RETRY_BACKOFF_SEC * (attempt + 1))587            else:588                return dict(NOT_FOUND_DETAILS)589    return dict(NOT_FOUND_DETAILS)590 591 592async def process_one(593    context,594    idx: int,595    url: str,596    status_callback=None,597    use_openai_ocr: bool = True,598    openai_api_key: str = None,599) -> Tuple[int, Dict[str, str]]:600    page = await context.new_page()601    try:602        details = await extract_details_with_retries(603            page,604            url,605            status_callback=status_callback,606            use_openai_ocr=use_openai_ocr,607            openai_api_key=openai_api_key,608        )609        return idx, details610    finally:611        try:612            await page.close()613        except Exception:614            pass615 616 617async def launch_browser_instance(playwright, headless: bool, status_callback=None):618    launch_attempts = [619        {"label": "Playwright Chromium", "kwargs": {"headless": headless, "slow_mo": SLOW_MO_MS}},620        {"label": "Microsoft Edge", "kwargs": {"headless": headless, "slow_mo": SLOW_MO_MS, "channel": "msedge"}},621    ]622 623    last_error = None624    for attempt in launch_attempts:625        try:626            if status_callback:627                status_callback(f"Launching {attempt['label']}...")628            return await playwright.chromium.launch(**attempt["kwargs"])629        except Exception as exc:630            last_error = exc631            if status_callback:632                status_callback(f"{attempt['label']} launch failed: {exc}")633 634    if last_error:635        raise last_error636    raise RuntimeError("Unable to launch a browser for certificate extraction.")637 638 639async def run_extraction(640    df: pd.DataFrame,641    output_csv: str,642    progress_callback=None,643    status_callback=None,644    headless: bool = HEADLESS,645    use_openai_ocr: bool = True,646    openai_api_key: str = None,647) -> str:648    current_output_csv = output_csv649    async with async_playwright() as p:650        browser = await launch_browser_instance(p, headless=headless, status_callback=status_callback)651        context = await browser.new_context()652 653        # Keep images enabled because certificate date text may be rendered in the visible page body.654        async def route_handler(route):655            if route.request.resource_type in {"media", "font"}:656                await route.abort()657            else:658                await route.continue_()659 660        await context.route("**/*", route_handler)661 662        sem = asyncio.Semaphore(MAX_CONCURRENCY)663 664        async def worker(idx: int, url: str):665            async with sem:666                try:667                    return await process_one(668                        context,669                        idx,670                        url,671                        status_callback=status_callback,672                        use_openai_ocr=use_openai_ocr,673                        openai_api_key=openai_api_key,674                    )675                except Exception:676                    return idx, dict(NOT_FOUND_DETAILS)677 678        tasks = [asyncio.create_task(worker(idx, url)) for idx, url in df[0].items()]679 680        processed = 0681        with tqdm(total=len(tasks), desc="Reading certificates") as pbar:682            for coro in asyncio.as_completed(tasks):683                idx, details = await coro684                df.at[idx, 1] = details.get("certificate_name", "NOT FOUND")685                df.at[idx, 2] = details.get("learner_name", "NOT FOUND")686                df.at[idx, 3] = details.get("issued_date", "NOT FOUND")687                processed += 1688                pbar.update(1)689 690                if progress_callback:691                    progress_callback(processed, len(tasks))692 693                if processed % CHECKPOINT_EVERY == 0:694                    current_output_csv = write_csv_safely(695                        df, current_output_csv, is_checkpoint=True696                    )697                    if status_callback:698                        status_callback(f"Checkpoint saved: {current_output_csv}")699 700        await context.close()701        await browser.close()702    return current_output_csv703 704 705def run_certificate_extraction(706    input_csv: str,707    output_csv: str = None,708    progress_callback=None,709    status_callback=None,710    headless: bool = HEADLESS,711    use_openai_ocr: bool = True,712    openai_api_key: str = None,713) -> str:714    if status_callback:715        status_callback(f"Reading CSV: {input_csv}")716 717    final_output = output_csv or str(Path(input_csv).with_name("output_with_certificate_details.csv"))718    df = pd.read_csv(input_csv, header=None, dtype=str).fillna("")719    if len(df) > 0 and str(df.iloc[0, 0]).strip().lower() == "link":720        df = df.iloc[1:].reset_index(drop=True)721 722    df[1] = "NOT FOUND"723    df[2] = "NOT FOUND"724    df[3] = "NOT FOUND"725 726    if progress_callback:727        progress_callback(0, len(df))728    if status_callback:729        status_callback(f"Loaded {len(df)} links")730        if use_openai_ocr:731            status_callback(732                f"Certificate AI fallback is enabled when a saved {get_ai_provider_label()} key is available."733            )734        else:735            status_callback("Certificate AI fallback is disabled for this run.")736 737    final_output = asyncio.run(738        run_extraction(739            df,740            final_output,741            progress_callback=progress_callback,742            status_callback=status_callback,743            headless=headless,744            use_openai_ocr=use_openai_ocr,745            openai_api_key=openai_api_key,746        )747    )748    final_output = write_csv_safely(df, final_output, is_checkpoint=False)749 750    if status_callback:751        status_callback(f"Certificate extraction complete. Output saved to: {final_output}")752 753    return final_output754 755 756def main() -> None:757    input_csv = pick_input_csv()758    output_csv = run_certificate_extraction(input_csv=input_csv)759    print("\nDONE!")760    print("Output saved at:", output_csv)761 762 763if __name__ == "__main__":764    main()765