this script seems to be working fine, I want to implement it for multiple exam pdfs, each having their PAK and FAK for example , PAK-79-2018-19 and FAK-79-2018-19 and then PAK-49-2019-20 and FAK-49-2019-20 so on and so forth,it'll be cumbersome to keep asking user to enter page number for each so, use a text file or python script where user can specify which pages to parse in which pdf ::# 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 pdfplumber
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+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 = []

    # 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_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 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 (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 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: Parse and handle the Answer Key Table using pdfplumber
    print("\n--- Parsing Answer Key Table ---")
    answer_key_pdf_path = FAK_PDF_PATH  # Ensure this is the correct path to the FAK PDF containing the answer key table
    grid_df, long_df = parse_answer_key_table(answer_key_pdf_path)

    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 = os.path.join(OUTPUT_DIR, "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"\nAnswer key results saved to {output_path}")
        logging.info(f"Answer key results saved to {output_path}")
    else:
        print("Failed to parse the answer key table. Proceeding with existing answer extraction methods.")

    # Step 4: Extract and Insert Exam Details from PAK
    print("\n--- Extracting Exam Details from PAK ---")
    # 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 5: Extract Questions and Options from PAK (User-specified pages)
    print("\n--- Extracting Questions and Options from PAK ---")
    extract_and_insert_questions(PAK_PDF_PATH, exam_id, DB_PARAMS, pak_pages)

    # Step 6: Insert Answers from Parsed Answer Key DataFrame
    print("\n--- Inserting Answers from Answer Key ---")
    insert_answers(long_df, exam_id, DB_PARAMS)

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


if __name__ == '__main__':
    main()