implement the procedure adopted in parsing the table in script 1 into script 2::: (script 1 ) import os
import pandas as pd
import pdfplumber
import numpy as np

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
    """
    # Read PDF file
    with pdfplumber.open(pdf_path) as pdf:
        first_page = pdf.pages[1]
        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)
        
        return grid_df, long_df

def display_formatted_grid(grid_df):
    """
    Display the answer key grid in a formatted table.
    """
    # 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

def main():
    # Get PDF path from environment variable
    pdf_path = os.getenv('fak_pdf_path')
    if not pdf_path:
        raise ValueError("Environment variable 'fak_pdf_path' not set")
    
    try:
        # Parse table and create DataFrames
        grid_df, long_df = parse_answer_key_table(pdf_path)
        
        # 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 = "answer_key_analysis.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"\nResults saved to {output_path}")
        
    except Exception as e:
        print(f"Error processing PDF: {str(e)}")

if __name__ == "__main__":
    main() (script 2 ) # main.py

import os
import re
import logging
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`
from page_selector import get_pages  # Import the page selector function
import camelot  # For table extraction
import tabula  # For table extraction using Tabula
import pandas as pd

# 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
PAK_PDF_PATH = os.getenv('PAK_PDF_PATH')
FAK_PDF_PATH = os.getenv('FAK_PDF_PATH')
POPPLER_PATH = os.getenv('POPPLER_PATH')
TESSERACT_CMD = os.getenv('TESSERACT_CMD')
OUTPUT_DIR = os.getenv('OUTPUT_DIR')
FASTTEXT_MODEL_PATH = os.getenv('FASTTEXT_MODEL_PATH')  # e.g., 'models/lid.176.bin'

# Ensure output directory exists
os.makedirs(OUTPUT_DIR, 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 os.path.exists(FASTTEXT_MODEL_PATH):
    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 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')
                    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 = []

    # Improved split: Use word boundary and flexible spacing, capture question numbers
    # This will split the text and keep the question numbers
    # e.g., ['', '102', 'Question text...', '103', 'Question text...', ...]
    question_blocks = re.split(r'\b(\d+)[\.\:]\s+', text, flags=re.UNICODE)
    total_questions = (len(question_blocks) - 1) // 2
    logging.debug(f"Total question blocks found: {total_questions}")

    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()

            # 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

            if len(option_dict) != 4:
                logging.warning(f"Question {q_num} has {len(option_dict)} options instead of 4.")

            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_fak_answers_regex(text):
    """
    Parse answers from the extracted FAK text using regex.
    Returns a dictionary mapping question numbers to correct options.

    Args:
        text (str): Extracted FAK text.

    Returns:
        dict: {question_number: correct_option}
    """
    answers = {}

    # Use a regex to find all occurrences of question number followed by answer
    # Example pattern: '1 A', '31 A', '61 C', etc.
    # Modified to ensure it captures standalone pairs and avoids corrupted entries
    pattern = re.compile(r'\b(\d{1,3})\s+([A-Da-d])\b')

    matches = pattern.findall(text)
    logging.debug(f"Total answer pairs found: {len(matches)}")

    for q_num_str, answer_str in matches:
        try:
            q_num = int(q_num_str)
            correct_opt = answer_str.upper()
            # Validate answer option
            if correct_opt in ['A', 'B', 'C', 'D']:
                answers[q_num] = correct_opt
                logging.debug(f"Parsed Answer for Question {q_num}: {correct_opt}")
            else:
                logging.warning(f"Invalid answer option '{correct_opt}' for Question {q_num}. Skipping.")
        except ValueError:
            logging.warning(f"Invalid format for question number or answer: '{q_num_str} {answer_str}'. Skipping.")

    logging.info(f"Total answers parsed: {len(answers)}")
    return answers


def parse_fak_answers_camelot(pdf_path, pages=None):
    """
    Parse answers from the extracted FAK PDF using Camelot.

    This function handles multi-column tables by iterating through each table
    and each column pair within the tables to extract all possible answers.

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

    Returns:
        dict: {question_number: correct_option}
    """
    answers = {}

    try:
        # Convert pages list to comma-separated string for Camelot
        if isinstance(pages, list):
            pages_str = ','.join(map(str, pages))
        elif isinstance(pages, str):
            pages_str = pages
        else:
            pages_str = '1'  # Default to first page

        # Extract tables using Camelot with 'stream' flavor and fine-tuned parameters
        tables = camelot.read_pdf(
            pdf_path,
            pages=pages_str,
            flavor='stream',
            edge_tol=500,
            row_tol=30,
            line_tol=40,
            split_text=True
        )
        logging.info(f"Found {tables.n} tables in FAK PDF using Camelot.")

        # Save tables for manual verification (optional)
        for table_num, table in enumerate(tables, start=1):
            table.to_csv(os.path.join(OUTPUT_DIR, f"fak_camelot_table_{table_num}.csv"))
            table.plot('text').savefig(os.path.join(OUTPUT_DIR, f"fak_camelot_table_{table_num}.png"))
            logging.debug(f"Saved Camelot Table {table_num} as CSV and PNG for verification.")

        for table_num, table in enumerate(tables, start=1):
            df = table.df
            logging.debug(f"Processing Camelot Table {table_num} with shape {df.shape}")
            # Drop empty rows
            df = df.dropna(how='all').reset_index(drop=True)
            if df.empty:
                logging.warning(f"Camelot Table {table_num} is empty after dropping NA. Skipping.")
                continue

            # Attempt to identify columns dynamically based on 'Q.No.' and 'Answer' headers
            headers = df.iloc[0].tolist()
            df = df[1:].reset_index(drop=True)
            df.columns = headers

            # Identify all Q.No. and Answer column pairs
            qno_cols = [col for col in df.columns if re.search(r'Q\.?No\.?', col, re.IGNORECASE)]
            ans_cols = [col for col in df.columns if re.search(r'Answer', col, re.IGNORECASE)]

            if len(qno_cols) != len(ans_cols):
                logging.warning(f"Camelot Table {table_num} has {len(qno_cols)} Q.No columns and {len(ans_cols)} Answer columns. Expected equal number.")

            # Iterate through each pair of Q.No and Answer columns
            for q_col, a_col in zip(qno_cols, ans_cols):
                for index, row in df.iterrows():
                    q_num_str = str(row[q_col]).strip()
                    answer_str = str(row[a_col]).strip().upper()

                    # Validate question number and answer
                    if q_num_str.isdigit() and answer_str in ['A', 'B', 'C', 'D']:
                        q_num = int(q_num_str)
                        # Avoid overwriting existing entries
                        if q_num in answers:
                            logging.debug(f"Duplicate Answer for Question {q_num} found in Camelot Table {table_num}, Row {index+2}. Existing Answer: {answers[q_num]}, New Answer: {answer_str}. Skipping.")
                            continue
                        answers[q_num] = answer_str
                        logging.debug(f"Camelot Parsed Answer for Question {q_num}: {answer_str}")
                    else:
                        if not q_num_str.isdigit():
                            logging.debug(f"Invalid Question Number '{q_num_str}' in Camelot Table {table_num}, Row {index+2}. Skipping.")
                        if answer_str not in ['A', 'B', 'C', 'D']:
                            logging.debug(f"Invalid Answer '{answer_str}' for Question {q_num_str} in Camelot Table {table_num}, Row {index+2}. Skipping.")

        logging.info(f"Total answers parsed with Camelot: {len(answers)}")
        return answers

    except Exception as e:
        logging.error(f"Error extracting tables with Camelot: {e}")
        return answers


def parse_fak_answers_tabula(pdf_path, pages=None):
    """
    Parse answers from the extracted FAK PDF using Tabula.

    This function handles multi-column tables by extracting all (Q.No., Answer) pairs.

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

    Returns:
        dict: {question_number: correct_option}
    """
    answers = {}

    try:
        # Convert pages list to comma-separated string for Tabula
        if isinstance(pages, list):
            pages_str = ','.join(map(str, pages))
        elif isinstance(pages, str):
            pages_str = pages
        else:
            pages_str = '1'  # Default to first page

        # Extract tables using Tabula
        tables = tabula.read_pdf(
            pdf_path,
            pages=pages_str,
            multiple_tables=True,
            silent=True,
            lattice=False,  # 'lattice' if tables have lines; 'stream' otherwise
            guess=False,    # Disable Tabula's automatic table guessing
            area=None        # Define if tables are in specific areas
        )
        logging.info(f"Found {len(tables)} tables in FAK PDF using Tabula.")

        # Save tables for manual verification (optional)
        for table_num, table in enumerate(tables, start=1):
            table.to_csv(os.path.join(OUTPUT_DIR, f"fak_tabula_table_{table_num}.csv"), index=False)
            # Tabula does not provide plotting, but CSVs are sufficient for verification
            logging.debug(f"Saved Tabula Table {table_num} as CSV for verification.")

        for table_num, table in enumerate(tables, start=1):
            if table.empty:
                logging.warning(f"Tabula Table {table_num} is empty. Skipping.")
                continue

            # Standardize column names to lower case for consistency
            table.columns = table.columns.str.lower()

            # Identify all Q.No. and Answer column pairs
            qno_cols = [col for col in table.columns if re.search(r'q\.?no\.?', col)]
            ans_cols = [col for col in table.columns if re.search(r'answer', col)]

            if len(qno_cols) != len(ans_cols):
                logging.warning(f"Tabula Table {table_num} has {len(qno_cols)} Q.No columns and {len(ans_cols)} Answer columns. Expected equal number.")

            # Iterate through each pair of Q.No and Answer columns
            for q_col, a_col in zip(qno_cols, ans_cols):
                for index, row in table.iterrows():
                    q_num_str = str(row[q_col]).strip()
                    answer_str = str(row[a_col]).strip().upper()

                    # Validate question number and answer
                    if q_num_str.isdigit() and answer_str in ['A', 'B', 'C', 'D']:
                        q_num = int(q_num_str)
                        # Avoid overwriting existing entries
                        if q_num in answers:
                            logging.debug(f"Duplicate Answer for Question {q_num} found in Tabula Table {table_num}, Row {index+2}. Existing Answer: {answers[q_num]}, New Answer: {answer_str}. Skipping.")
                            continue
                        answers[q_num] = answer_str
                        logging.debug(f"Tabula Parsed Answer for Question {q_num}: {answer_str}")
                    else:
                        if not q_num_str.isdigit():
                            logging.debug(f"Invalid Question Number '{q_num_str}' in Tabula Table {table_num}, Row {index+2}. Skipping.")
                        if answer_str not in ['A', 'B', 'C', 'D']:
                            logging.debug(f"Invalid Answer '{answer_str}' for Question {q_num_str} in Tabula Table {table_num}, Row {index+2}. Skipping.")

        logging.info(f"Total answers parsed with Tabula: {len(answers)}")
        return answers

    except Exception as e:
        logging.error(f"Error extracting tables with Tabula: {e}")
        return answers


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(answers_dict, exam_id, db_params):
    """
    Insert answers into the answers table.

    Args:
        answers_dict (dict): Dictionary mapping question numbers to correct options.
        exam_id (int): The exam ID from the database.
        db_params (dict): Database connection parameters.
    """
    try:
        if not answers_dict:
            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 q_num, correct_opt in answers_dict.items():
                    # 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.")
        print(f"Inserted {inserted_count} answers and skipped {skipped_count} due to missing questions.")

    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 (str): 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(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(os.path.join(OUTPUT_DIR, "extracted_questions.csv"), index=False)
        logging.debug("Saved extracted questions to 'extracted_questions.csv'.")

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


def extract_and_insert_answers(fak_pdf_path, exam_id, db_params, fak_pages):
    """
    Extract answers from FAK PDF and insert into database.

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

    # **Attempt 1: Extract using Camelot**
    answers_dict = parse_fak_answers_camelot(fak_pdf_path, fak_pages)

    # **Attempt 2: Extract using Tabula if Camelot finds insufficient answers**
    # Since we no longer assume 300 questions, we don't set a fixed expectation.
    # Instead, we aim to extract as many answers as possible based on existing questions.
    if len(answers_dict) < 1:  # If Camelot found no answers, try Tabula
        logging.warning(f"Camelot extracted {len(answers_dict)} answers. Trying Tabula for further extraction.")
        print(f"Camelot extracted {len(answers_dict)} answers. Trying Tabula for further extraction.")

        # Extract additional answers using Tabula
        tabula_answers = parse_fak_answers_tabula(fak_pdf_path, fak_pages)

        # Merge Tabula answers, avoiding duplicates
        for q_num, correct_opt in tabula_answers.items():
            if q_num not in answers_dict and correct_opt in ['A', 'B', 'C', 'D']:
                answers_dict[q_num] = correct_opt
                logging.debug(f"Tabula Added Answer for Question {q_num}: {correct_opt}")

    # **Attempt 3: Extract remaining answers using Regex-based OCR if necessary**
    # If after Camelot and Tabula, we're still missing answers, use OCR-based regex extraction
    # Note: Since questions can vary based on user-selected pages, we map only existing questions
    if not answers_dict:
        logging.warning("No answers extracted using Camelot and Tabula. Trying OCR-based extraction.")
        print("No answers extracted using Camelot and Tabula. Trying OCR-based extraction.")

        # Use OCR to extract text
        fak_text = extract_text_with_ocr(fak_pdf_path, pages=fak_pages)

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

        # Save extracted FAK text for verification
        with open(os.path.join(OUTPUT_DIR, "extracted_fak_text.txt"), "w", encoding="utf-8") as f:
            f.write(fak_text)

        # Normalize the extracted text
        normalized_fak_text = normalize_text(fak_text)

        # Parse answers using regex
        answers_dict = parse_fak_answers_regex(normalized_fak_text)

    print(f"Extracted {len(answers_dict)} answers.")

    # Optionally, save extracted answers to CSV for verification
    if answers_dict:
        answers_df = pd.DataFrame(list(answers_dict.items()), columns=['Question Number', 'Correct Option'])
        answers_df.to_csv(os.path.join(OUTPUT_DIR, "extracted_answers.csv"), index=False)
        logging.debug("Saved extracted answers to 'extracted_answers.csv'.")

    # Insert into database
    insert_answers(answers_dict, exam_id, db_params)


def main():
    """
    Main function to orchestrate the extraction and insertion process.
    """
    # 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: Get user-specified pages for PAK PDF
    print("\n--- PAK PDF Page Selection ---")
    pak_pages = get_pages('PAK')
    print(f"Pages to extract from PAK PDF: {pak_pages}")

    # Step 2: Get user-specified pages for FAK PDF
    print("\n--- FAK PDF Page Selection ---")
    fak_pages = get_pages('FAK')
    print(f"Pages to extract from FAK PDF: {fak_pages}")

    # Step 3: Extract and Insert Exam Details from PAK (Assuming it's always on specific pages)
    print("\nExtracting Exam Details from PAK (Assumed to be on specific pages)...")
    # Customize this section if exam details are to be extracted dynamically
    # For demonstration, we're using predefined exam_details
    exam_details = {
        'exam_name': 'Assistant Engineer (Civil), Class-2, Roads and Building Department',
        'exam_date': datetime.strptime('23/06/2019', '%d/%m/%Y').date(),
        'advertisement_number': '79/2018-19',
        'exam_code': None  # If not extracted, keep as None
    }

    print("Inserting Exam Details into Database...")
    exam_id = extract_and_insert_exam_details(exam_details, DB_PARAMS)
    if not exam_id:
        print("Failed to insert exam details. Exiting...")
        return

    # Step 4: Extract Questions and Options from PAK (User-specified pages)
    print("\nExtracting Questions and Options from PAK...")
    extract_and_insert_questions(PAK_PDF_PATH, exam_id, DB_PARAMS, pak_pages)

    # Step 5: Extract Answers from FAK (User-specified pages)
    print("\nExtracting Answers from FAK...")
    extract_and_insert_answers(FAK_PDF_PATH, exam_id, DB_PARAMS, fak_pages)

    print("\nData extraction and insertion completed successfully.")


if __name__ == '__main__':
    main()