this seems to work but is not parsing all the figures, some  figures might have text element too thats why they may have gotten skipped ::# proto2_figures.py

import os
import re
import logging
import json
from datetime import datetime
from dotenv import load_dotenv
import psycopg2
from psycopg2 import sql
import fitz  # PyMuPDF
from pathlib import Path

# Load environment variables from .env file
load_dotenv()

# Configure logging
logging.basicConfig(
    filename='figure_extraction.log',
    filemode='a',
    level=logging.DEBUG,  # Set to DEBUG for detailed logs
    format='%(asctime)s:%(levelname)s:%(message)s'
)

# Database connection parameters from environment variables
DB_PARAMS = {
    'dbname': os.getenv('DB_NAME'),
    'user': os.getenv('DB_USER'),
    'password': os.getenv('DB_PASSWORD'),
    'host': os.getenv('DB_HOST'),
    'port': os.getenv('DB_PORT')
}

# Paths from environment variables
DATA_DIR = Path(os.getenv('DATA_DIR', './data'))
OUTPUT_DIR = Path(os.getenv('FIGURES_OUTPUT_DIR', './figures_output'))

# Ensure output directory exists
OUTPUT_DIR.mkdir(parents=True, exist_ok=True)

def connect_db(db_params):
    """
    Establish a connection to the PostgreSQL database.
    
    Args:
        db_params (dict): Database connection parameters.
    
    Returns:
        connection: psycopg2 connection object.
    """
    try:
        conn = psycopg2.connect(**db_params)
        logging.info("Database connection established successfully.")
        return conn
    except psycopg2.Error as e:
        logging.error(f"Database connection failed: {e}")
        raise

def find_pak_fak_pdfs(data_dir):
    """
    Find all PAK and FAK PDF files in the specified directory.
    
    Args:
        data_dir (Path): Path to the data directory containing PDFs.
    
    Returns:
        list of tuples: Each tuple contains (exam_code, source, pdf_path)
    """
    pak_files = sorted(data_dir.glob('PAK-*.pdf'))
    fak_files = sorted(data_dir.glob('FAK-*.pdf'))

    pdf_pairs = []

    # Extract exam_codes
    pak_exam_codes = {pak.stem[4:]: pak for pak in pak_files}  # Remove 'PAK-' prefix
    fak_exam_codes = {fak.stem[4:]: fak for fak in fak_files}  # Remove 'FAK-' prefix

    # Combine PAK and FAK files
    for exam_code, pak_pdf in pak_exam_codes.items():
        if exam_code in fak_exam_codes:
            fak_pdf = fak_exam_codes[exam_code]
            pdf_pairs.append((exam_code, 'PAK', pak_pdf))
            pdf_pairs.append((exam_code, 'FAK', fak_pdf))
            logging.info(f"Found PAK and FAK PDFs for exam_code: {exam_code}")
        else:
            logging.warning(f"No corresponding FAK PDF found for PAK exam_code: {exam_code}")

    for exam_code, fak_pdf in fak_exam_codes.items():
        if exam_code not in pak_exam_codes:
            logging.warning(f"No corresponding PAK PDF found for FAK exam_code: {exam_code}")

    if not pdf_pairs:
        logging.warning("No PAK and FAK PDF pairs found in the data directory.")

    return pdf_pairs

def extract_images_from_pdf(exam_code, source, pdf_path, conn):
    """
    Extract images from a PDF file and save them with a uniform naming scheme.
    Then, insert metadata into the database.
    
    Args:
        exam_code (str): The exam code.
        source (str): 'PAK' or 'FAK'.
        pdf_path (Path): Path to the PDF file.
        conn: psycopg2 database connection.
    """
    try:
        doc = fitz.open(pdf_path)
        logging.info(f"Opened PDF: {pdf_path}")
    except Exception as e:
        logging.error(f"Failed to open PDF {pdf_path}: {e}")
        return

    figure_counter = 1  # To keep track of figure numbers within the PDF

    for page_num in range(len(doc)):
        page = doc.load_page(page_num)
        image_list = page.get_images(full=True)

        if not image_list:
            logging.debug(f"No images found on page {page_num + 1} of {pdf_path}")
            continue

        logging.info(f"Found {len(image_list)} images on page {page_num + 1} of {pdf_path}")

        for img_index, img in enumerate(image_list, start=1):
            xref = img[0]
            try:
                base_image = doc.extract_image(xref)
                image_bytes = base_image["image"]
                image_ext = base_image["ext"]
                image = Image.open(io.BytesIO(image_bytes))

                # Define the filename
                figure_filename = f"{exam_code}_{source}_figure_{figure_counter}.{image_ext}"
                figure_path = OUTPUT_DIR / figure_filename

                # Save the image
                image.save(figure_path)
                logging.info(f"Saved figure: {figure_path}")

                # Insert metadata into the database
                insert_figure_metadata(conn, exam_code, source, figure_counter, figure_path, page_num + 1)

                figure_counter += 1

            except Exception as e:
                logging.error(f"Failed to extract/save image {img_index} on page {page_num + 1} of {pdf_path}: {e}")
                continue

    doc.close()

def insert_figure_metadata(conn, exam_code, source, figure_number, file_path, page_number):
    """
    Insert metadata of the extracted figure into the 'figures' table.
    
    Args:
        conn: psycopg2 database connection.
        exam_code (str): The exam code.
        source (str): 'PAK' or 'FAK'.
        figure_number (int): Sequential number of the figure.
        file_path (Path): Path where the figure image is saved.
        page_number (int): Page number in the PDF where the figure was found.
    """
    try:
        with conn.cursor() as cursor:
            # Retrieve exam_id from exams table using exam_code
            cursor.execute(
                "SELECT exam_id FROM exams WHERE exam_code = %s;",
                (exam_code,)
            )
            result = cursor.fetchone()
            if not result:
                logging.error(f"No exam_id found for exam_code: {exam_code}. Skipping metadata insertion.")
                return
            exam_id = result[0]

            # Insert figure metadata
            insert_query = """
                INSERT INTO figures (exam_id, source, figure_number, file_path, page_number)
                VALUES (%s, %s, %s, %s, %s);
            """
            cursor.execute(insert_query, (
                exam_id,
                source,
                figure_number,
                str(file_path),
                page_number
            ))
            conn.commit()
            logging.info(f"Inserted metadata for figure {figure_number} of exam_code {exam_code} into the database.")

    except Exception as e:
        logging.error(f"Failed to insert figure metadata for exam_code {exam_code}: {e}")
        conn.rollback()

def main():
    """
    Main function to orchestrate figure extraction from PDFs.
    """
    # Step 1: Connect to the Database
    try:
        conn = connect_db(DB_PARAMS)
    except Exception as e:
        print("Failed to connect to the database. Check logs for details.")
        return

    # Step 2: Find All PAK and FAK PDFs
    pdf_pairs = find_pak_fak_pdfs(DATA_DIR)
    if not pdf_pairs:
        print("No PAK and FAK PDF pairs found in the data directory. Exiting...")
        return

    # Step 3: Process Each PDF Pair
    for exam_code, source, pdf_path in pdf_pairs:
        print(f"\n--- Extracting Figures from {source} PDF for Exam Code: {exam_code} ---")
        logging.info(f"Starting figure extraction for exam_code: {exam_code}, source: {source}")
        extract_images_from_pdf(exam_code, source, pdf_path, conn)

    # Close the database connection
    conn.close()
    logging.info("Figure extraction process completed and database connection closed.")
    print("\nFigure extraction completed successfully.")

if __name__ == '__main__':
    import io
    from PIL import Image  # Ensure Pillow is installed

    # Check if Pillow is installed
    try:
        Image.OPEN
    except AttributeError:
        logging.error("Pillow library is not installed. Please install it using 'pip install Pillow'.")
        print("Error: Pillow library is not installed. Please install it using 'pip install Pillow'.")
        exit(1)

    main()