I want to add the figures in the webpages whenever there is a figure in the question, Can you setup an ocr script apart from main one to just look out for figures and when they see it, they should crop it and save it with uniform naming scheme and if possible add it to sql database  . this is the main script :: #sceript 1 proto1.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 pdfminer.high_level import extract_text as pdfminer_extract_text
from pdfminer.pdfpage import PDFPage
from pdf2image import convert_from_path
import pytesseract
from PIL import Image
import fasttext  # Optional: Install via `pip install fasttext`
import pdfplumber
import pandas as pd
from pathlib import Path

# Load environment variables from .env file
load_dotenv()

# Configure logging
logging.basicConfig(
    filename='exam_data_import.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'))
POPPLER_PATH = os.getenv('POPPLER_PATH')
TESSERACT_CMD = os.getenv('TESSERACT_CMD')
OUTPUT_DIR = Path(os.getenv('OUTPUT_DIR', './output'))
FASTTEXT_MODEL_PATH = os.getenv('FASTTEXT_MODEL_PATH')  # e.g., 'models/lid.176.bin'
PAGES_CONFIG_PATH = Path('pages_config.json')  # Path to pages configuration

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

# Configure Tesseract OCR
pytesseract.pytesseract.tesseract_cmd = TESSERACT_CMD

# Optional: Initialize FastText model for language detection if improved accuracy is needed
if FASTTEXT_MODEL_PATH and Path(FASTTEXT_MODEL_PATH).exists():
    try:
        fasttext_model = fasttext.load_model(FASTTEXT_MODEL_PATH)
        logging.info("FastText language detection model loaded successfully.")
    except Exception as e:
        fasttext_model = None
        logging.error(f"Failed to load FastText model: {e}")
else:
    fasttext_model = None
    logging.warning("FastText model not found. Proceeding without FastText language detection.")


def verify_tesseract_languages():
    """
    Verify that Tesseract has the required languages installed.
    If Gujarati ('guj') is not installed, provide instructions.
    """
    try:
        langs = pytesseract.get_languages(config='')
        if 'guj' not in langs:
            logging.error("Gujarati language data ('guj') not found in Tesseract.")
            print("Error: Gujarati language data ('guj') not found in Tesseract.")
            print("Please install it by following the instructions below:")
            print("For Ubuntu/Debian:")
            print("  sudo apt-get install tesseract-ocr-guj")
            print("For Windows:")
            print("  Download 'guj.traineddata' from https://github.com/tesseract-ocr/tessdata and place it in your tessdata directory.")
            return False
        else:
            logging.info("Gujarati language data ('guj') is installed in Tesseract.")
            return True
    except Exception as e:
        logging.error(f"Failed to verify Tesseract languages: {e}")
        print(f"Failed to verify Tesseract languages: {e}")
        return False


def load_pages_config():
    """
    Load the pages configuration from pages_config.json.
    If the file does not exist, create an empty configuration.
    
    Returns:
        dict: Configuration mapping exam_code to PAK and FAK pages.
    """
    if PAGES_CONFIG_PATH.exists():
        try:
            with PAGES_CONFIG_PATH.open('r', encoding='utf-8') as f:
                config = json.load(f)
            logging.info(f"Loaded pages configuration from {PAGES_CONFIG_PATH}.")
            return config
        except Exception as e:
            logging.error(f"Failed to load pages configuration: {e}")
            print(f"Error: Failed to load pages configuration: {e}")
            return {}
    else:
        logging.info(f"No existing pages configuration found. A new one will be created at {PAGES_CONFIG_PATH}.")
        return {}


def save_pages_config(config):
    """
    Save the pages configuration to pages_config.json.
    
    Args:
        config (dict): Configuration mapping exam_code to PAK and FAK pages.
    """
    try:
        with PAGES_CONFIG_PATH.open('w', encoding='utf-8') as f:
            json.dump(config, f, indent=4)
        logging.info(f"Saved pages configuration to {PAGES_CONFIG_PATH}.")
    except Exception as e:
        logging.error(f"Failed to save pages configuration: {e}")
        print(f"Error: Failed to save pages configuration: {e}")


def find_pak_fak_pairs(data_dir):
    """
    Find all PAK and FAK PDF pairs in the data directory based on naming convention.
    
    Args:
        data_dir (Path): Path to the data directory containing PDFs.
    
    Returns:
        list of tuples: Each tuple contains (exam_code, pak_pdf_path, fak_pdf_path)
    """
    pak_files = sorted(data_dir.glob('PAK-*.pdf'))
    fak_files = sorted(data_dir.glob('FAK-*.pdf'))

    # 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

    # Find common exam codes
    common_exam_codes = set(pak_exam_codes.keys()) & set(fak_exam_codes.keys())

    if not common_exam_codes:
        logging.warning("No matching PAK and FAK pairs found.")
        print("Warning: No matching PAK and FAK pairs found.")

    pairs = []
    for exam_code in sorted(common_exam_codes):
        pairs.append((exam_code, pak_exam_codes[exam_code], fak_exam_codes[exam_code]))
        logging.info(f"Found PAK and FAK pair for exam_code: {exam_code}")

    return pairs


def get_user_input_pages(exam_code):
    """
    Prompt the user to enter PAK and FAK page numbers for a given exam code.
    
    Args:
        exam_code (str): The exam code.
    
    Returns:
        dict: Dictionary with 'PAK_pages' and 'FAK_pages' as lists of integers.
    """
    print(f"\n--- Specify Page Numbers for Exam Code: {exam_code} ---")
    print("Enter PAK page numbers separated by commas (e.g., 1,2,3):")
    pak_pages_input = input("PAK Pages: ").strip()
    pak_pages = parse_page_numbers(pak_pages_input)

    print("Enter FAK page numbers separated by commas (e.g., 1,2):")
    fak_pages_input = input("FAK Pages: ").strip()
    fak_pages = parse_page_numbers(fak_pages_input)

    return {
        'PAK_pages': pak_pages,
        'FAK_pages': fak_pages
    }


def parse_page_numbers(input_str):
    """
    Parse a comma-separated string of page numbers into a list of integers.
    
    Args:
        input_str (str): Comma-separated page numbers.
    
    Returns:
        list: List of unique, sorted page numbers as integers.
    """
    try:
        pages = sorted({int(p.strip()) for p in input_str.split(',') if p.strip().isdigit()})
        return pages
    except Exception as e:
        logging.error(f"Error parsing page numbers from input '{input_str}': {e}")
        print(f"Error: Invalid page numbers entered. Please enter numbers separated by commas.")
        return []


def extract_text_with_pymupdf(pdf_path, pages=None):
    """
    Extract text from specific pages in PDF using PyMuPDF (fitz).

    Args:
        pdf_path (str): Path to the PDF file.
        pages (list): List of page numbers to extract (1-based indexing).

    Returns:
        str: Extracted text.
    """
    try:
        doc = fitz.open(pdf_path)
        text = ""
        total_pages = doc.page_count

        if pages:
            for p in pages:
                if p < 1 or p > total_pages:
                    logging.warning(f"Page {p} is out of range for {pdf_path}. Skipping.")
                    continue
                page = doc.load_page(p - 1)  # PyMuPDF uses 0-based indexing
                page_text = page.get_text("text")
                text += page_text + "\n"
        else:
            # Extract all pages if no specific pages are provided
            for page_num in range(total_pages):
                page = doc.load_page(page_num)
                page_text = page.get_text("text")
                text += page_text + "\n"

        logging.info(f"Extracted text from PDF {pdf_path} using PyMuPDF.")
        return text
    except Exception as e:
        logging.error(f"Error extracting text from PDF {pdf_path} with PyMuPDF: {e}")
        return ""


def extract_text_with_pdfminer(pdf_path, pages=None):
    """
    Extract text from specific pages in PDF using PDFMiner.six.

    Args:
        pdf_path (str): Path to the PDF file.
        pages (list): List of page numbers to extract (1-based indexing).

    Returns:
        str: Extracted text.
    """
    try:
        laparams = None  # Use default layout analysis parameters
        page_numbers = []

        if pages:
            with open(pdf_path, 'rb') as fp:
                all_pages = list(PDFPage.get_pages(fp))
                for p in pages:
                    if p < 1 or p > len(all_pages):
                        logging.warning(f"Page {p} is out of range for {pdf_path}. Skipping.")
                        continue
                    page_numbers.append(p - 1)  # PDFMiner uses 0-based indexing
        else:
            # Extract all pages if no specific pages are provided
            with open(pdf_path, 'rb') as fp:
                all_pages = list(PDFPage.get_pages(fp))
                page_numbers = list(range(len(all_pages)))

        text = pdfminer_extract_text(pdf_path, page_numbers=page_numbers, laparams=laparams)

        logging.info(f"Extracted text from PDF {pdf_path} using PDFMiner.six.")
        return text
    except Exception as e:
        logging.error(f"Error extracting text from PDF {pdf_path} with PDFMiner.six: {e}")
        return ""


def extract_text_with_ocr(pdf_path, pages=None):
    """
    Extract text from specific pages in PDF using OCR with Tesseract.

    Args:
        pdf_path (str): Path to the PDF file.
        pages (list): List of page numbers to extract (1-based indexing).

    Returns:
        str: Extracted text.
    """
    try:
        text = ""
        if pages:
            for p in pages:
                images = convert_from_path(
                    pdf_path,
                    first_page=p,
                    last_page=p,
                    poppler_path=POPPLER_PATH
                )
                for img in images:
                    # Preprocess the image for better OCR results
                    img = img.convert('L')  # convert to grayscale
                    img = img.point(lambda x: 0 if x < 140 else 255, '1')  # simple threshold
                    # Use both English and Gujarati OCR
                    ocr_text = pytesseract.image_to_string(img, lang='eng+guj')
                    text += ocr_text + "\n"
        else:
            # Extract all pages if no specific pages are provided
            images = convert_from_path(
                pdf_path,
                poppler_path=POPPLER_PATH
            )
            for img in images:
                # Preprocess the image for better OCR results
                img = img.convert('L')  # convert to grayscale
                img = img.point(lambda x: 0 if x < 140 else 255, '1')  # simple threshold
                ocr_text = pytesseract.image_to_string(img, lang='eng+guj')
                text += ocr_text + "\n"

        logging.info(f"Extracted text from PDF {pdf_path} using OCR.")
        return text
    except Exception as e:
        logging.error(f"Error extracting text from PDF {pdf_path} with OCR: {e}")
        return ""


def extract_text_hybrid(pdf_path, pages=None):
    """
    Hybrid extraction: Try PyMuPDF first, fallback to PDFMiner.six, then OCR if no or invalid text is found.

    Args:
        pdf_path (str): Path to the PDF file.
        pages (list): List of page numbers to extract (1-based indexing).

    Returns:
        str: Extracted text.
    """
    text = extract_text_with_pymupdf(pdf_path, pages)
    if not text.strip() or is_garbage(text):
        logging.warning(f"No usable text extracted with PyMuPDF for {pdf_path}. Trying PDFMiner.six.")
        text = extract_text_with_pdfminer(pdf_path, pages)
        if not text.strip() or is_garbage(text):
            logging.warning(f"No usable text extracted with PDFMiner.six for {pdf_path}. Using OCR.")
            text = extract_text_with_ocr(pdf_path, pages)
    else:
        logging.info(f"Text extracted successfully with PyMuPDF for {pdf_path}.")
    return text


def is_garbage(text):
    """
    Define a heuristic to determine if the text is garbage.
    Simple check: if more than 90% of characters are non-ASCII.

    Args:
        text (str): Text to evaluate.

    Returns:
        bool: True if text is considered garbage, False otherwise.
    """
    if not text:
        return True
    non_ascii = sum(1 for c in text if ord(c) > 127)
    ratio = non_ascii / len(text) if len(text) > 0 else 0
    is_garb = ratio > 0.9  # 90% threshold
    logging.debug(f"Text garbage check: non-ASCII ratio = {ratio:.2f}. is_garbage: {is_garb}")
    return is_garb


def normalize_text(text):
    """
    Normalize text by replacing multiple spaces and newlines with single space.

    Args:
        text (str): Text to normalize.

    Returns:
        str: Normalized text.
    """
    text = re.sub(r'\s+', ' ', text, flags=re.UNICODE)
    return text.strip()


def parse_questions_and_options(text):
    """
    Parse questions and options from the extracted PAK text.
    Returns two lists:
    - questions: List of dictionaries with 'number' and 'text'.
    - options: List of dictionaries mapping option labels to option texts.

    Args:
        text (str): Extracted PAK text.

    Returns:
        tuple: (questions, options)
    """
    questions = []
    options = []

    # Refined regex: Match numbers at the start of lines followed by a dot
    question_blocks = re.split(r'(?<!\S)(\d+)[\.]\s+', text, flags=re.UNICODE)
    total_questions = (len(question_blocks) - 1) // 2
    logging.debug(f"Total question blocks found: {total_questions}")

    exclusion_phrases = ["as per", "refer to", "according to", "according"]

    for i in range(1, len(question_blocks), 2):
        q_num_str = question_blocks[i]
        try:
            q_num = int(q_num_str)
        except ValueError:
            logging.warning(f"Invalid question number encountered: {q_num_str}. Skipping block.")
            continue
        block = question_blocks[i + 1]

        # Extract question text ending before option (A)
        q_text_match = re.match(r'\s*(.*?)\s*\(\s*A\s*\)', block, re.IGNORECASE | re.DOTALL | re.UNICODE)
        if q_text_match:
            q_text = q_text_match.group(1).strip()

            # Exclusion: Skip questions starting with exclusion phrases
            if any(q_text.lower().startswith(phrase) for phrase in exclusion_phrases):
                logging.warning(f"Question {q_num} starts with an exclusion phrase. Skipping.")
                continue

            # Extract options using regex, supporting Unicode characters
            opts = re.findall(r'\(\s*([A-Da-d])\s*\)\s*(.*?)(?=\(\s*[A-Da-d]\s*\)|$)', block, re.IGNORECASE | re.DOTALL | re.UNICODE)
            option_dict = {}
            for opt in opts:
                label = opt[0].upper()
                opt_text = ' '.join(opt[1].strip().split())  # Normalize whitespace
                option_dict[label] = opt_text

            # Validation: Ensure all four options are present
            if len(option_dict) != 4:
                logging.warning(f"Question {q_num} has {len(option_dict)} options instead of 4. Skipping this question.")
                continue

            questions.append({'number': q_num, 'text': q_text})
            options.append(option_dict)

            logging.debug(f"Parsed Question {q_num}: {q_text}")
            for label, opt_text in option_dict.items():
                logging.debug(f"  Option {label}: {opt_text}")
        else:
            logging.warning(f"Failed to match question text in block for Question No. {q_num}")

    return questions, options


def parse_answer_key_table(pdf_path):
    """
    Parse answer key table from PDF and return both grid and long format DataFrames.

    Parameters:
    pdf_path (str): Path to the PDF file containing the answer key table

    Returns:
    tuple: (grid_df, long_df) - DataFrames in grid and long format
    """
    try:
        # Read PDF file
        with pdfplumber.open(pdf_path) as pdf:
            if len(pdf.pages) < 2:
                raise ValueError("Insufficient pages in PDF to extract the answer key table.")

            first_page = pdf.pages[1]  # Assuming the table is on the second page (0-based index)
            tables = first_page.extract_tables()
            if not tables:
                raise ValueError("No tables found in the PDF")

            main_table = tables[0]

            # Create column names for the grid format
            columns = []
            for i in range(0, len(main_table[0]), 2):
                if i + 1 < len(main_table[0]):
                    columns.extend([f'Q.No.{(i // 2) + 1}', f'Answer{(i // 2) + 1}'])

            # Convert to DataFrame preserving the grid structure
            grid_df = pd.DataFrame(main_table[1:], columns=columns)

            # Create long format DataFrame for analysis
            long_format_data = []
            for row in main_table[1:]:  # Skip header row
                for i in range(0, len(row), 2):
                    if i + 1 < len(row) and row[i] and row[i + 1]:  # Check if both Q.No. and Answer exist
                        long_format_data.append({
                            'Question_No': row[i],
                            'Answer': row[i + 1]
                        })

            long_df = pd.DataFrame(long_format_data)

        logging.info(f"Parsed answer key table from {pdf_path}")
        return grid_df, long_df

    except Exception as e:
        logging.error(f"Error parsing answer key table from {pdf_path}: {e}")
        return pd.DataFrame(), pd.DataFrame()


def display_formatted_grid(grid_df):
    """
    Display the answer key grid in a formatted table.
    """
    try:
        # Create headers for display
        headers = [col for col in grid_df.columns]

        # Print header row
        print("\nAnswer Key Grid:")
        print("-" * (len(headers) * 12))  # Separator line
        header_row = ""
        for header in headers:
            header_row += f"{header:^10} "
        print(header_row)
        print("-" * (len(headers) * 12))  # Separator line

        # Print data rows
        for _, row in grid_df.iterrows():
            row_str = ""
            for value in row:
                if pd.isna(value) or value == '':
                    value = '-'
                row_str += f"{str(value):^10} "
            print(row_str)
        print("-" * (len(headers) * 12))  # Separator line

    except Exception as e:
        logging.error(f"Error displaying formatted grid: {e}")
        print(f"Error displaying formatted grid: {e}")


def insert_exam_details(exam_details, db_params):
    """
    Insert exam details into the exams table.
    Returns the inserted exam_id.

    Args:
        exam_details (dict): Dictionary containing exam details.
        db_params (dict): Database connection parameters.

    Returns:
        int or None: Inserted exam_id or None if insertion failed.
    """
    # Validate extracted details
    if not all([exam_details['exam_name'], exam_details['advertisement_number'], exam_details['exam_date']]):
        logging.warning("Incomplete exam details. Aborting insertion.")
        print("Incomplete exam details. Aborting insertion.")
        return None

    try:
        # Connect to PostgreSQL using a context manager
        with psycopg2.connect(**db_params) as conn:
            with conn.cursor() as cursor:
                # Extract year from exam_date
                exam_year = exam_details['exam_date'].year

                # Insert into exams table
                insert_query = """
                    INSERT INTO exams (exam_name, advertisement_number, exam_code, year)
                    VALUES (%s, %s, %s, %s)
                    RETURNING exam_id;
                """
                exam_name = exam_details['exam_name']
                advertisement_number = exam_details['advertisement_number']
                exam_code = exam_details.get('exam_code', '')  # Default to empty string if not available

                cursor.execute(insert_query, (exam_name, advertisement_number, exam_code, exam_year))
                exam_id = cursor.fetchone()[0]

                logging.info(f"Inserted exam with exam_id: {exam_id}")
                print(f"Inserted exam with exam_id: {exam_id}")
                return exam_id

    except psycopg2.Error as e:
        logging.error(f"Database error inserting exam details: {e}")
        print(f"Error inserting exam details: {e}")
        return None

    except Exception as e:
        logging.error(f"Unexpected error inserting exam details: {e}")
        print(f"Error inserting exam details: {e}")
        return None


def insert_questions_and_options(questions, options, exam_id, db_params):
    """
    Insert questions and their options into the database.

    Args:
        questions (list): List of question dictionaries.
        options (list): List of option dictionaries.
        exam_id (int): The exam ID from the database.
        db_params (dict): Database connection parameters.
    """
    try:
        if not questions:
            logging.warning("No questions to insert.")
            print("No questions to insert.")
            return

        with psycopg2.connect(**db_params) as conn:
            with conn.cursor() as cursor:
                for q, opts in zip(questions, options):
                    q_num = q['number']
                    q_text = q['text']

                    # Check if question already exists to prevent duplicates
                    cursor.execute(
                        """
                        SELECT question_id FROM questions
                        WHERE exam_id = %s AND question_number = %s
                        """,
                        (exam_id, q_num)
                    )
                    result = cursor.fetchone()
                    if result:
                        question_id = result[0]
                        logging.info(f"Question {q_num} already exists with question_id: {question_id}. Skipping insertion.")
                        continue

                    # Insert question
                    insert_q = """
                        INSERT INTO questions (exam_id, question_number, question_text)
                        VALUES (%s, %s, %s)
                        RETURNING question_id;
                    """
                    cursor.execute(insert_q, (exam_id, q_num, q_text))
                    question_id = cursor.fetchone()[0]
                    logging.info(f"Inserted Question {q_num} with question_id: {question_id}.")

                    # Insert options
                    for label, opt_text in opts.items():
                        # Check if option already exists
                        cursor.execute(
                            """
                            SELECT option_id FROM options
                            WHERE question_id = %s AND option_label = %s
                            """,
                            (question_id, label)
                        )
                        opt_result = cursor.fetchone()
                        if opt_result:
                            logging.info(f"Option {label} for Question {q_num} already exists with option_id: {opt_result[0]}. Skipping insertion.")
                            continue

                        insert_opt = """
                            INSERT INTO options (question_id, option_label, option_text)
                            VALUES (%s, %s, %s);
                        """
                        cursor.execute(insert_opt, (question_id, label, opt_text))
                        logging.debug(f"Inserted Option {label} for Question {q_num}: {opt_text}")

        logging.info(f"Inserted {len(questions)} questions and their options.")
        print(f"Inserted {len(questions)} questions and their options.")

    except Exception as e:
        logging.error(f"Error inserting questions and options: {e}")
        print(f"Error inserting questions and options: {e}")


def insert_answers(long_df, exam_id, db_params):
    """
    Insert answers into the answers table based on the parsed answer key DataFrame.

    Args:
        long_df (pd.DataFrame): DataFrame containing 'Question_No' and 'Answer' columns.
        exam_id (int): The exam ID from the database.
        db_params (dict): Database connection parameters.
    """
    try:
        if long_df.empty:
            logging.warning("No answers to insert.")
            print("No answers to insert.")
            return

        with psycopg2.connect(**db_params) as conn:
            with conn.cursor() as cursor:
                inserted_count = 0
                skipped_count = 0
                for _, row in long_df.iterrows():
                    q_num = row['Question_No']
                    correct_opt = row['Answer'].upper()

                    # Validate data
                    if not isinstance(q_num, int) and not str(q_num).isdigit():
                        logging.warning(f"Invalid Question Number '{q_num}'. Skipping.")
                        skipped_count += 1
                        continue
                    q_num = int(q_num)

                    if correct_opt not in ['A', 'B', 'C', 'D']:
                        logging.warning(f"Invalid Answer Option '{correct_opt}' for Question {q_num}. Skipping.")
                        skipped_count += 1
                        continue

                    # Fetch question_id based on question_number and exam_id
                    cursor.execute(
                        """
                        SELECT question_id FROM questions
                        WHERE exam_id = %s AND question_number = %s;
                        """,
                        (exam_id, q_num)
                    )
                    result = cursor.fetchone()
                    if result:
                        question_id = result[0]
                        # Insert into answers table
                        insert_ans = """
                            INSERT INTO answers (question_id, correct_option)
                            VALUES (%s, %s)
                            ON CONFLICT (question_id) DO UPDATE
                            SET correct_option = EXCLUDED.correct_option;
                        """
                        cursor.execute(insert_ans, (question_id, correct_opt))
                        logging.debug(f"Inserted/Updated Answer for Question {q_num}: {correct_opt}")
                        inserted_count += 1
                    else:
                        logging.warning(f"No question found for Question No. {q_num}. Skipping answer insertion.")
                        print(f"Warning: No question found for Question No. {q_num}. Skipping answer insertion.")
                        skipped_count += 1

        logging.info(f"Inserted {inserted_count} answers and skipped {skipped_count} due to missing questions or invalid data.")
        print(f"Inserted {inserted_count} answers and skipped {skipped_count} due to missing questions or invalid data.")

    except Exception as e:
        logging.error(f"Error inserting answers: {e}")
        print(f"Error inserting answers: {e}")





def extract_and_insert_exam_details(exam_details, db_params):
    """
    Wrapper function to insert exam details.

    Args:
        exam_details (dict): Dictionary containing exam details.
        db_params (dict): Database connection parameters.

    Returns:
        int or None: Inserted exam_id or None if insertion failed.
    """
    return insert_exam_details(exam_details, db_params)


def extract_and_insert_questions(pak_pdf_path, exam_id, db_params, pak_pages):
    """
    Extract questions and options from PAK PDF and insert into database.

    Args:
        pak_pdf_path (Path): Path to the PAK PDF.
        exam_id (int): The exam ID from the database.
        db_params (dict): Database connection parameters.
        pak_pages (list): List of pages to extract from the PAK PDF.
    """
    if not pak_pages:
        logging.warning("No pages specified for PAK PDF extraction. Skipping PAK extraction.")
        print("No pages specified for PAK PDF extraction. Skipping PAK extraction.")
        return

    pak_text = extract_text_hybrid(str(pak_pdf_path), pages=pak_pages)

    # Log a snippet of the extracted PAK text for inspection
    logging.debug(f"PAK Text Extracted (First 1000 characters):\n{pak_text[:1000]}")
    print(f"PAK Text Extracted (First 1000 characters):\n{pak_text[:1000]}")

    # Normalize the extracted text
    normalized_pak_text = normalize_text(pak_text)

    # Parse questions and options
    questions, options = parse_questions_and_options(normalized_pak_text)
    print(f"Extracted {len(questions)} questions.")

    # Save extracted questions to CSV for verification
    if questions:
        questions_df = pd.DataFrame(questions)
        questions_df.to_csv(OUTPUT_DIR / f"extracted_questions_{exam_id}.csv", index=False)
        logging.debug(f"Saved extracted questions to 'extracted_questions_{exam_id}.csv'.")

    # Insert into database
    insert_questions_and_options(questions, options, exam_id, db_params)


def process_exam_pair(exam_code, pak_pdf, fak_pdf, pages_config, db_params):
    """
    Process a single exam's PAK and FAK PDF pair.

    Args:
        exam_code (str): The exam code.
        pak_pdf (Path): Path to the PAK PDF.
        fak_pdf (Path): Path to the FAK PDF.
        pages_config (dict): Configuration mapping exam_code to PAK and FAK pages.
        db_params (dict): Database connection parameters.
    """
    logging.info(f"Starting processing for exam_code: {exam_code}")
    print(f"\n--- Processing Exam Code: {exam_code} ---")

    # Check if exam_code exists in pages_config
    if exam_code in pages_config:
        pak_pages = pages_config[exam_code].get('PAK_pages', [])
        fak_pages = pages_config[exam_code].get('FAK_pages', [])
    else:
        # Prompt user for page numbers
        page_numbers = get_user_input_pages(exam_code)
        pak_pages = page_numbers['PAK_pages']
        fak_pages = page_numbers['FAK_pages']
        # Update pages_config
        pages_config[exam_code] = {
            'PAK_pages': pak_pages,
            'FAK_pages': fak_pages
        }
        save_pages_config(pages_config)

    # Step 1: Extract Exam Details from PAK PDF (Assuming Exam Details are embedded in PAK)
    # Customize this extraction based on how exam details are presented in your PAK PDFs
    print("\n--- Extracting Exam Details from PAK ---")
    pak_text = extract_text_hybrid(str(pak_pdf), pages=pak_pages)
    normalized_pak_text = normalize_text(pak_text)

    # Example extraction logic (Customize as per actual PAK content)
    # For demonstration, we will assume that exam_name, advertisement_number, and exam_date can be extracted via regex
    try:
        exam_name_match = re.search(r'Exam Name[:\-]\s*(.*)', normalized_pak_text, re.IGNORECASE)
        advertisement_number_match = re.search(r'Advertisement Number[:\-]\s*(\d+/\d+-\d+)', normalized_pak_text, re.IGNORECASE)
        exam_date_match = re.search(r'Exam Date[:\-]\s*(\d{2}/\d{2}/\d{4})', normalized_pak_text, re.IGNORECASE)

        exam_name = exam_name_match.group(1).strip() if exam_name_match else "Unknown Exam Name"
        advertisement_number = advertisement_number_match.group(1).strip() if advertisement_number_match else "Unknown Advertisement Number"
        exam_date_str = exam_date_match.group(1).strip() if exam_date_match else "01/01/1970"

        try:
            exam_date = datetime.strptime(exam_date_str, '%d/%m/%Y').date()
        except ValueError:
            logging.warning(f"Invalid exam date format for exam_code {exam_code}: {exam_date_str}. Defaulting to 01/01/1970.")
            exam_date = datetime.strptime('01/01/1970', '%d/%m/%Y').date()

        exam_details = {
            'exam_name': exam_name,
            'advertisement_number': advertisement_number,
            'exam_date': exam_date,
            'exam_code': exam_code
        }

        print(f"Extracted Exam Details:\n  Name: {exam_name}\n  Advertisement Number: {advertisement_number}\n  Exam Date: {exam_date}")
        logging.info(f"Extracted Exam Details for {exam_code}: {exam_details}")

    except Exception as e:
        logging.error(f"Error extracting exam details from PAK for {exam_code}: {e}")
        print(f"Error: Failed to extract exam details for {exam_code}. Skipping this exam.")
        return

    # Step 2: Insert Exam Details into Database
    print("\nInserting Exam Details into Database...")
    exam_id = extract_and_insert_exam_details(exam_details, db_params)
    if not exam_id:
        print(f"Failed to insert exam details for {exam_code}. Skipping to next exam.")
        return

    # Step 3: Extract and Insert Questions from PAK
    print("\n--- Extracting Questions and Options from PAK ---")
    extract_and_insert_questions(pak_pdf, exam_id, db_params, pak_pages)

    # Step 4: Parse and Insert Answers from FAK
    print("\n--- Parsing Answer Key Table from FAK ---")
    grid_df, long_df = parse_answer_key_table(str(fak_pdf))

    if not grid_df.empty and not long_df.empty:
        # Display the grid format
        display_formatted_grid(grid_df)

        # Display statistics
        print("\nAnswer Key Statistics:")
        print(f"Total questions: {len(long_df)}")
        print("\nAnswer distribution:")
        answer_dist = long_df['Answer'].value_counts().sort_index()
        for answer, count in answer_dist.items():
            print(f"Option {answer}: {count} questions")

        # Save to Excel if needed
        output_path = OUTPUT_DIR / f"answer_key_analysis_{exam_code}.xlsx"
        with pd.ExcelWriter(output_path) as writer:
            grid_df.to_excel(writer, sheet_name='Grid Format', index=False)
            long_df.to_excel(writer, sheet_name='Long Format', index=False)
        print(f"\nAnswer key results saved to {output_path}")
        logging.info(f"Answer key results saved to {output_path}")

        # Insert Answers into Database
        print("\n--- Inserting Answers from Answer Key ---")
        insert_answers(long_df, exam_id, db_params)
    else:
        logging.warning(f"Failed to parse the answer key table for exam_code {exam_code}.")
        print("Failed to parse the answer key table. Skipping answers insertion.")


def main():
    """
    Main function to orchestrate the extraction and insertion process for multiple exams.
    """
    # Step 0: Verify Tesseract has Gujarati language data installed
    print("Verifying Tesseract OCR language data...")
    if not verify_tesseract_languages():
        print("Gujarati language data is missing. Please install it and rerun the script.")
        return

    # Step 1: Load Pages Configuration
    pages_config = load_pages_config()

    # Step 2: Find All PAK and FAK Pairs
    exam_pairs = find_pak_fak_pairs(DATA_DIR)
    if not exam_pairs:
        print("No PAK and FAK pairs found in the data directory. Exiting...")
        return

    # Step 3: Process Each Exam Pair
    for exam_code, pak_pdf, fak_pdf in exam_pairs:
        process_exam_pair(exam_code, pak_pdf, fak_pdf, pages_config, DB_PARAMS)

    print("\nAll exams have been processed successfully.")


if __name__ == '__main__':
    main()