This script is not parsing the tables in FAK pdf in right format, use ocr to read the FAK pdf ( not both only FAK) and then extract table for better clarity : # 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

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

            # Removed Gujarati or non-English filtering as per user instruction
            # Previously:
            # if is_gujarati(q_text):
            #     logging.info(f"Question {q_num} is in Gujarati or contains non-English text. Skipping.")
            #     continue  # Skip Gujarati or non-English questions

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

    Args:
        text (str): Extracted FAK text.

    Returns:
        dict: {question_number: correct_option}
    """
    answers = {}
    lines = text.splitlines()
    lines = [line.strip() for line in lines if line.strip()]  # Remove empty lines

    i = 0
    while i < len(lines) - 1:
        q_num_str = lines[i]
        answer_str = lines[i + 1]

        # Check if q_num_str is a number and answer_str is A-D
        if q_num_str.isdigit() and re.match(r'^[A-Da-d]$', answer_str):
            q_num = int(q_num_str)
            correct_opt = answer_str.upper()
            answers[q_num] = correct_opt
            logging.debug(f"Parsed Answer for Question {q_num}: {correct_opt}")
            i += 2
        else:
            logging.warning(f"Unexpected format: '{q_num_str}' followed by '{answer_str}'. Skipping.")
            i += 1

    logging.info(f"Total answers parsed: {len(answers)}")
    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:
        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:
        with psycopg2.connect(**db_params) as conn:
            with conn.cursor() as cursor:
                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}")
                    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.")

        logging.info("Inserted all answers into the answers table.")
        print("Inserted all answers into the answers table.")

    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.")

    # 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

    fak_text = extract_text_hybrid(fak_pdf_path, pages=fak_pages)

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

    # Normalize the extracted text
    normalized_fak_text = normalize_text(fak_text)

    # Parse answers
    answers_dict = parse_fak_answers(normalized_fak_text)
    print(f"Extracted {len(answers_dict)} answers.")

    # 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)...")
    # Custom extraction logic can be implemented here if exam details are on specific pages
    # 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()