The pdf text parsing module is parsing the gujarati text as jibberish english therefore it is passing through language filter later on where gujarati has to be removed  : import os
import re
import logging
from datetime import datetime
from dotenv import load_dotenv
from langdetect import detect, DetectorFactory
import psycopg2
from psycopg2 import sql
import fitz  # PyMuPDF
from pdf2image import convert_from_path
import pytesseract
from PIL import Image

# Load environment variables from .env file
load_dotenv()

# Ensure consistent language detection
DetectorFactory.seed = 0

# 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')
os.makedirs(OUTPUT_DIR, exist_ok=True)

# Configure Tesseract OCR
pytesseract.pytesseract.tesseract_cmd = TESSERACT_CMD

def extract_text_with_pymupdf(pdf_path, start_page=None, end_page=None):
    """
    Extract text from PDF using PyMuPDF (fitz).
    """
    try:
        doc = fitz.open(pdf_path)
        text = ""
        total_pages = doc.page_count

        # Determine pages to extract
        if start_page and end_page:
            pages = range(start_page - 1, min(end_page, total_pages))
        elif start_page:
            pages = range(start_page - 1, total_pages)
        else:
            pages = range(total_pages)

        for page_num in pages:
            page = doc.load_page(page_num)
            page_text = page.get_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_ocr(pdf_path, start_page=None, end_page=None):
    """
    Extract text from PDF using OCR with Tesseract.
    """
    try:
        images = convert_from_path(
            pdf_path,
            first_page=start_page,
            last_page=end_page,
            poppler_path=POPPLER_PATH
        )
        text = ""
        for img in images:
            ocr_text = pytesseract.image_to_string(img, lang='eng')
            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, start_page=None, end_page=None):
    """
    Hybrid extraction: Try PyMuPDF first, fallback to OCR if no text is found.
    """
    text = extract_text_with_pymupdf(pdf_path, start_page, end_page)
    if not text.strip():
        logging.warning(f"No text extracted with PyMuPDF for {pdf_path}. Using OCR.")
        text = extract_text_with_ocr(pdf_path, start_page, end_page)
    return text

def is_gujarati(text):
    """
    Detect if the given text is in Gujarati.
    """
    try:
        lang = detect(text)
        return lang == 'gu'
    except Exception as e:
        logging.warning(f"Language detection failed: {e}")
        return False

def normalize_text(text):
    """
    Normalize text by replacing multiple spaces and newlines with single space.
    """
    text = re.sub(r'\s+', ' ', text)
    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.
    """
    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)
    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)
        if q_text_match:
            q_text = q_text_match.group(1).strip()

            # Per-question language detection to skip Gujarati
            if is_gujarati(q_text):
                logging.info(f"Question {q_num} is in Gujarati. Skipping.")
                continue  # Skip Gujarati questions

            # Extract options using regex
            opts = re.findall(r'\(\s*([A-Da-d])\s*\)\s*(.*?)(?=\(\s*[A-Da-d]\s*\)|$)', block, re.DOTALL)
            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.
    """
    answers = {}

    # Pattern to find "Question No. X: Option" or "Question X: Option"
    matches = re.findall(r'Question\s*(?:No\.?)?\s*(\d+)\s*[:\-]\s*([A-Da-d])', text, re.IGNORECASE)

    for match in matches:
        q_num_str, correct_opt_str = match
        try:
            q_num = int(q_num_str)
            correct_opt = correct_opt_str.upper()
            answers[q_num] = correct_opt
            logging.debug(f"Parsed Answer for Question {q_num}: {correct_opt}")
        except ValueError:
            logging.warning(f"Invalid answer format encountered: {match}. Skipping.")

    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.
    """
    # 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.
    """
    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.
    """
    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.
    """
    return insert_exam_details(exam_details, db_params)

def extract_and_insert_questions(pak_pdf_path, exam_id, db_params):
    """
    Extract questions and options from PAK PDF and insert into database.
    """
    pak_text = extract_text_hybrid(pak_pdf_path, start_page=2)
    
    # 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):
    """
    Extract answers from FAK PDF and insert into database.
    """
    fak_text = extract_text_hybrid(fak_pdf_path)
    
    # 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 1: Extract and Insert Exam Details from PAK (First Page)
    print("Extracting Exam Details from PAK (First Page)...")
    # Assume this step is already completed, and you have exam_details
    # In practice, use the OCR extraction you have done previously
    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 2: Extract Questions and Options from PAK (Subsequent Pages)
    print("Extracting Questions and Options from PAK...")
    extract_and_insert_questions(PAK_PDF_PATH, exam_id, DB_PARAMS)

    # Step 3: Extract Answers from FAK
    print("Extracting Answers from FAK...")
    extract_and_insert_answers(FAK_PDF_PATH, exam_id, DB_PARAMS)

    print("Data extraction and insertion completed successfully.")

if __name__ == '__main__':
    main()