instead of openai api, for text based llm, use local ollama end point with llama3.2 model # 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
from pdf2image import convert_from_path
import pytesseract
from PIL import Image
import pdfplumber
import pandas as pd
from pathlib import Path
import openai
import cv2
import numpy as np
import fasttext  # Optional: Install via `pip install fasttext`

# 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

# OpenAI API configuration
OPENAI_API_KEY = os.getenv('OPENAI_API_KEY')
openai.api_key = OPENAI_API_KEY

# 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 verify_openai_connection():
    """
    Verify that the OpenAI API is accessible.
    
    Returns:
        bool: True if connection is successful, False otherwise.
    """
    try:
        # Make a simple API call (e.g., list models)
        openai.Model.list()
        logging.info("Successfully connected to OpenAI API.")
        return True
    except Exception as e:
        logging.error(f"Failed to connect to OpenAI API: {e}", exc_info=True)
        print("Error: Failed to connect to OpenAI API. Please check your API key and internet connection.")
        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., 4,5):")
    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_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:
                    # Convert PIL image to OpenCV image
                    img_cv = cv2.cvtColor(np.array(img), cv2.COLOR_RGB2BGR)
                    
                    # Convert to grayscale
                    gray = cv2.cvtColor(img_cv, cv2.COLOR_BGR2GRAY)
                    
                    # Apply Gaussian Blur to remove noise
                    gray = cv2.GaussianBlur(gray, (5, 5), 0)
                    
                    # Apply adaptive thresholding
                    thresh = cv2.adaptiveThreshold(
                        gray, 255, cv2.ADAPTIVE_THRESH_GAUSSIAN_C,
                        cv2.THRESH_BINARY, 11, 2
                    )
                    
                    # Morphological operations to enhance text
                    kernel = np.ones((1, 1), np.uint8)
                    thresh = cv2.dilate(thresh, kernel, iterations=1)
                    thresh = cv2.erode(thresh, kernel, iterations=1)
                    
                    # Convert back to PIL image
                    processed_img = Image.fromarray(thresh)
                    
                    # OCR with specified languages
                    ocr_text = pytesseract.image_to_string(processed_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:
                img_cv = cv2.cvtColor(np.array(img), cv2.COLOR_RGB2BGR)
                gray = cv2.cvtColor(img_cv, cv2.COLOR_BGR2GRAY)
                gray = cv2.GaussianBlur(gray, (5, 5), 0)
                thresh = cv2.adaptiveThreshold(
                    gray, 255, cv2.ADAPTIVE_THRESH_GAUSSIAN_C,
                    cv2.THRESH_BINARY, 11, 2
                )
                kernel = np.ones((1, 1), np.uint8)
                thresh = cv2.dilate(thresh, kernel, iterations=1)
                thresh = cv2.erode(thresh, kernel, iterations=1)
                processed_img = Image.fromarray(thresh)
                ocr_text = pytesseract.image_to_string(processed_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 parse_text_with_llm(raw_text):
    """
    Use a Text-based LLM to parse raw OCR text into structured questions and options.
    
    Args:
        raw_text (str): Raw text extracted from OCR.
    
    Returns:
        tuple: (questions, options) where questions is a list of dicts and options is a list of dicts.
    """
    try:
        # Define the prompt with clear instructions
        prompt = (
            "You are an AI assistant that extracts multiple-choice questions from text. "
            "Parse the following text and extract all questions along with their options. "
            "Provide the output in JSON format with two lists: 'questions' and 'options'. "
            "Each question should have a 'number' and 'text', and each options entry should correspond to the question with keys 'A', 'B', 'C', 'D'.\n\n"
            "Example:\n"
            "Input Text:\n"
            "1. What is the capital of France?\n(A) Berlin\n(B) London\n(C) Paris\n(D) Rome\n\n"
            "Output:\n"
            "{\n"
            "  \"questions\": [\n"
            "    {\"number\": 1, \"text\": \"What is the capital of France?\"}\n"
            "  ],\n"
            "  \"options\": [\n"
            "    {\"A\": \"Berlin\", \"B\": \"London\", \"C\": \"Paris\", \"D\": \"Rome\"}\n"
            "  ]\n"
            "}\n\n"
            "Now, parse the following text:\n"
            f"{raw_text}\n\n"
            "Provide the output in the same JSON format."
        )

        # Make the API call to OpenAI
        response = openai.ChatCompletion.create(
            model="gpt-3.5-turbo",  # or "gpt-4" if available
            messages=[
                {"role": "system", "content": "You are a helpful assistant that parses exam questions."},
                {"role": "user", "content": prompt}
            ],
            max_tokens=2000,
            temperature=0.2,
            n=1,
            stop=None
        )

        # Extract the assistant's response
        llm_output = response['choices'][0]['message']['content']
        logging.debug(f"LLM Output: {llm_output}")

        # Parse the JSON response
        parsed_output = json.loads(llm_output)
        questions = parsed_output.get('questions', [])
        options = parsed_output.get('options', [])

        logging.info(f"Parsed {len(questions)} questions using Text-based LLM.")
        return questions, options

    except openai.error.OpenAIError as e:
        logging.error(f"OpenAI API error: {e}")
        return [], []
    except json.JSONDecodeError as e:
        logging.error(f"JSON parsing error from LLM response: {e}")
        return [], []
    except Exception as e:
        logging.error(f"Unexpected error during LLM parsing: {e}", exc_info=True)
        return [], []


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 with 'number' and 'text'.
        options (list): List of option dictionaries with keys 'A', 'B', 'C', 'D'.
        exam_id (int): The exam ID from the database.
        db_params (dict): Database connection parameters.
    """
    try:
        if not questions or not options:
            logging.warning("No questions or options to insert.")
            print("No questions or options 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 in ['A', 'B', 'C', 'D']:
                        opt_text = opts.get(label, '').strip()
                        if not opt_text:
                            logging.warning(f"Option {label} for Question {q_num} is empty. Skipping.")
                            continue

                        # Insert option
                        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 process_exam_pair(exam_code, pak_pdf, fak_pdf, pages_config, db_params):
    """
    Process a single exam's PAK and FAK PDF pair using Tesseract OCR and Text-based LLM for extraction.

    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', [])
        logging.info(f"Loaded pages_config for {exam_code}: PAK_pages={pak_pages}, FAK_pages={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)
        logging.info(f"Updated pages_config for {exam_code}: PAK_pages={pak_pages}, FAK_pages={fak_pages}")

    # Step 1: Extract Exam Details and Questions from PAK PDF using OCR and LLM
    print("\n--- Extracting Exam Details and Questions from PAK ---")
    try:
        # Perform OCR extraction
        raw_text = extract_text_with_ocr(str(pak_pdf), pak_pages)
        logging.debug(f"Raw OCR Text (First 1000 characters): {raw_text[:1000]}")

        # Parse text with LLM to get structured questions and options
        questions, options = parse_text_with_llm(raw_text)
        print(f"Extracted {len(questions)} questions using Text-based LLM.")
        logging.info(f"Extracted {len(questions)} questions using Text-based LLM for {exam_code}.")

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

    # Step 2: Extract Exam Details from Questions (Assuming Exam Details are part of the first few questions)
    # Adjust based on how exam details are embedded in your PAK PDFs
    try:
        if questions:
            # Assuming exam details are in the first question or specific questions
            # Modify this logic based on actual PAK content structure
            first_question = questions[0]
            # Example: Extracting exam details from the first question's text
            # Customize the regex patterns as per your PAK PDF formatting
            exam_details_match = re.search(
                r'Exam Name[:\-]\s*(.*)\s+(Advertisement Number[:\-]\s*(\d+/\d+-\d+))?\s+Exam Date[:\-]\s*(\d{2}/\d{2}/\d{4})',
                first_question['text'],
                re.IGNORECASE
            )
            if exam_details_match:
                exam_name = exam_details_match.group(1).strip()
                advertisement_number = exam_details_match.group(3).strip() if exam_details_match.group(3) else "Unknown Advertisement Number"
                exam_date_str = exam_details_match.group(4).strip()

                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_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}")

                # Remove the first question if it's actually exam details
                questions = questions[1:]
                options = options[1:]
            else:
                logging.warning(f"Failed to extract exam details from questions for {exam_code}.")
                # Fallback: Use predefined or prompt for exam details
                exam_details = {
                    'exam_name': "Unknown Exam Name",
                    'advertisement_number': "Unknown Advertisement Number",
                    'exam_date': datetime.strptime('01/01/1970', '%d/%m/%Y').date(),
                    'exam_code': exam_code
                }
        else:
            logging.warning(f"No questions extracted to derive exam details for {exam_code}.")
            exam_details = {
                'exam_name': "Unknown Exam Name",
                'advertisement_number': "Unknown Advertisement Number",
                'exam_date': datetime.strptime('01/01/1970', '%d/%m/%Y').date(),
                'exam_code': exam_code
            }

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

    # Step 3: 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 4: Insert Questions and Options into Database
    print("\nInserting Questions and Options into Database...")
    if questions and options:
        insert_questions_and_options(questions, options, exam_id, DB_PARAMS)
    else:
        logging.warning(f"No valid questions/options extracted for {exam_code}.")
        print("No valid questions/options extracted. Skipping questions insertion.")

    # Step 5: 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}.")
        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.
    """
    try:
        # 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: Verify OpenAI API connection
        print("Verifying OpenAI API connection...")
        if not verify_openai_connection():
            print("OpenAI API connection failed. Please check your API key and internet connection.")
            return

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

        # Step 3: 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 4: 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.")
        logging.info("All exams have been processed successfully.")

    except Exception as e:
        logging.critical(f"Unexpected error in main execution: {e}", exc_info=True)
        print(f"An unexpected error occurred: {e}. Please check the log for details.")


if __name__ == '__main__':
    main()