I want to be able to interact with and edit the sql database from the web interface, add a button in the main page which says edit and it should be able to  edit the questions and answers which reflects back to sql, ::::#sceript 1 proto1.py

import os
import re
import logging
import json
from datetime import datetime
from dotenv import load_dotenv
import psycopg2
from psycopg2 import sql
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`
import pdfplumber
import pandas as pd
from pathlib import Path

# Load environment variables from .env file
load_dotenv()

# Configure logging
logging.basicConfig(
    filename='exam_data_import.log',
    filemode='a',
    level=logging.DEBUG,  # Set to DEBUG for detailed logs
    format='%(asctime)s:%(levelname)s:%(message)s'
)

# Database connection parameters from environment variables
DB_PARAMS = {
    'dbname': os.getenv('DB_NAME'),
    'user': os.getenv('DB_USER'),
    'password': os.getenv('DB_PASSWORD'),
    'host': os.getenv('DB_HOST'),
    'port': os.getenv('DB_PORT')
}

# Paths from environment variables
DATA_DIR = Path(os.getenv('DATA_DIR', './data'))
POPPLER_PATH = os.getenv('POPPLER_PATH')
TESSERACT_CMD = os.getenv('TESSERACT_CMD')
OUTPUT_DIR = Path(os.getenv('OUTPUT_DIR', './output'))
FASTTEXT_MODEL_PATH = os.getenv('FASTTEXT_MODEL_PATH')  # e.g., 'models/lid.176.bin'
PAGES_CONFIG_PATH = Path('pages_config.json')  # Path to pages configuration

# Ensure output directory exists
OUTPUT_DIR.mkdir(parents=True, exist_ok=True)

# Configure Tesseract OCR
pytesseract.pytesseract.tesseract_cmd = TESSERACT_CMD

# Optional: Initialize FastText model for language detection if improved accuracy is needed
if FASTTEXT_MODEL_PATH and Path(FASTTEXT_MODEL_PATH).exists():
    try:
        fasttext_model = fasttext.load_model(FASTTEXT_MODEL_PATH)
        logging.info("FastText language detection model loaded successfully.")
    except Exception as e:
        fasttext_model = None
        logging.error(f"Failed to load FastText model: {e}")
else:
    fasttext_model = None
    logging.warning("FastText model not found. Proceeding without FastText language detection.")


def verify_tesseract_languages():
    """
    Verify that Tesseract has the required languages installed.
    If Gujarati ('guj') is not installed, provide instructions.
    """
    try:
        langs = pytesseract.get_languages(config='')
        if 'guj' not in langs:
            logging.error("Gujarati language data ('guj') not found in Tesseract.")
            print("Error: Gujarati language data ('guj') not found in Tesseract.")
            print("Please install it by following the instructions below:")
            print("For Ubuntu/Debian:")
            print("  sudo apt-get install tesseract-ocr-guj")
            print("For Windows:")
            print("  Download 'guj.traineddata' from https://github.com/tesseract-ocr/tessdata and place it in your tessdata directory.")
            return False
        else:
            logging.info("Gujarati language data ('guj') is installed in Tesseract.")
            return True
    except Exception as e:
        logging.error(f"Failed to verify Tesseract languages: {e}")
        print(f"Failed to verify Tesseract languages: {e}")
        return False


def load_pages_config():
    """
    Load the pages configuration from pages_config.json.
    If the file does not exist, create an empty configuration.
    
    Returns:
        dict: Configuration mapping exam_code to PAK and FAK pages.
    """
    if PAGES_CONFIG_PATH.exists():
        try:
            with PAGES_CONFIG_PATH.open('r', encoding='utf-8') as f:
                config = json.load(f)
            logging.info(f"Loaded pages configuration from {PAGES_CONFIG_PATH}.")
            return config
        except Exception as e:
            logging.error(f"Failed to load pages configuration: {e}")
            print(f"Error: Failed to load pages configuration: {e}")
            return {}
    else:
        logging.info(f"No existing pages configuration found. A new one will be created at {PAGES_CONFIG_PATH}.")
        return {}


def save_pages_config(config):
    """
    Save the pages configuration to pages_config.json.
    
    Args:
        config (dict): Configuration mapping exam_code to PAK and FAK pages.
    """
    try:
        with PAGES_CONFIG_PATH.open('w', encoding='utf-8') as f:
            json.dump(config, f, indent=4)
        logging.info(f"Saved pages configuration to {PAGES_CONFIG_PATH}.")
    except Exception as e:
        logging.error(f"Failed to save pages configuration: {e}")
        print(f"Error: Failed to save pages configuration: {e}")


def find_pak_fak_pairs(data_dir):
    """
    Find all PAK and FAK PDF pairs in the data directory based on naming convention.
    
    Args:
        data_dir (Path): Path to the data directory containing PDFs.
    
    Returns:
        list of tuples: Each tuple contains (exam_code, pak_pdf_path, fak_pdf_path)
    """
    pak_files = sorted(data_dir.glob('PAK-*.pdf'))
    fak_files = sorted(data_dir.glob('FAK-*.pdf'))

    # Extract exam codes
    pak_exam_codes = {pak.stem[4:]: pak for pak in pak_files}  # Remove 'PAK-' prefix
    fak_exam_codes = {fak.stem[4:]: fak for fak in fak_files}  # Remove 'FAK-' prefix

    # Find common exam codes
    common_exam_codes = set(pak_exam_codes.keys()) & set(fak_exam_codes.keys())

    if not common_exam_codes:
        logging.warning("No matching PAK and FAK pairs found.")
        print("Warning: No matching PAK and FAK pairs found.")

    pairs = []
    for exam_code in sorted(common_exam_codes):
        pairs.append((exam_code, pak_exam_codes[exam_code], fak_exam_codes[exam_code]))
        logging.info(f"Found PAK and FAK pair for exam_code: {exam_code}")

    return pairs


def get_user_input_pages(exam_code):
    """
    Prompt the user to enter PAK and FAK page numbers for a given exam code.
    
    Args:
        exam_code (str): The exam code.
    
    Returns:
        dict: Dictionary with 'PAK_pages' and 'FAK_pages' as lists of integers.
    """
    print(f"\n--- Specify Page Numbers for Exam Code: {exam_code} ---")
    print("Enter PAK page numbers separated by commas (e.g., 1,2,3):")
    pak_pages_input = input("PAK Pages: ").strip()
    pak_pages = parse_page_numbers(pak_pages_input)

    print("Enter FAK page numbers separated by commas (e.g., 1,2):")
    fak_pages_input = input("FAK Pages: ").strip()
    fak_pages = parse_page_numbers(fak_pages_input)

    return {
        'PAK_pages': pak_pages,
        'FAK_pages': fak_pages
    }


def parse_page_numbers(input_str):
    """
    Parse a comma-separated string of page numbers into a list of integers.
    
    Args:
        input_str (str): Comma-separated page numbers.
    
    Returns:
        list: List of unique, sorted page numbers as integers.
    """
    try:
        pages = sorted({int(p.strip()) for p in input_str.split(',') if p.strip().isdigit()})
        return pages
    except Exception as e:
        logging.error(f"Error parsing page numbers from input '{input_str}': {e}")
        print(f"Error: Invalid page numbers entered. Please enter numbers separated by commas.")
        return []


def extract_text_with_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 = []

    # Refined regex: Match numbers at the start of lines followed by a dot
    question_blocks = re.split(r'(?<!\S)(\d+)[\.]\s+', text, flags=re.UNICODE)
    total_questions = (len(question_blocks) - 1) // 2
    logging.debug(f"Total question blocks found: {total_questions}")

    exclusion_phrases = ["as per", "refer to", "according to", "according"]

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

            # Exclusion: Skip questions starting with exclusion phrases
            if any(q_text.lower().startswith(phrase) for phrase in exclusion_phrases):
                logging.warning(f"Question {q_num} starts with an exclusion phrase. Skipping.")
                continue

            # 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

            # Validation: Ensure all four options are present
            if len(option_dict) != 4:
                logging.warning(f"Question {q_num} has {len(option_dict)} options instead of 4. Skipping this question.")
                continue

            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 str(q_num).isdigit():
                        logging.warning(f"Invalid Question Number '{q_num}'. Skipping.")
                        skipped_count += 1
                        continue
                    q_num = int(q_num)

                    if correct_opt not in ['A', 'B', 'C', 'D']:
                        logging.warning(f"Invalid Answer Option '{correct_opt}' for Question {q_num}. Skipping.")
                        skipped_count += 1
                        continue

                    # Fetch question_id based on question_number and exam_id
                    cursor.execute(
                        """
                        SELECT question_id FROM questions
                        WHERE exam_id = %s AND question_number = %s;
                        """,
                        (exam_id, q_num)
                    )
                    result = cursor.fetchone()
                    if result:
                        question_id = result[0]
                        # Insert into answers table
                        insert_ans = """
                            INSERT INTO answers (question_id, correct_option)
                            VALUES (%s, %s)
                            ON CONFLICT (question_id) DO UPDATE
                            SET correct_option = EXCLUDED.correct_option;
                        """
                        cursor.execute(insert_ans, (question_id, correct_opt))
                        logging.debug(f"Inserted/Updated Answer for Question {q_num}: {correct_opt}")
                        inserted_count += 1
                    else:
                        logging.warning(f"No question found for Question No. {q_num}. Skipping answer insertion.")
                        print(f"Warning: No question found for Question No. {q_num}. Skipping answer insertion.")
                        skipped_count += 1

        logging.info(f"Inserted {inserted_count} answers and skipped {skipped_count} due to missing questions or invalid data.")
        print(f"Inserted {inserted_count} answers and skipped {skipped_count} due to missing questions or invalid data.")

    except Exception as e:
        logging.error(f"Error inserting answers: {e}")
        print(f"Error inserting answers: {e}")





def extract_and_insert_exam_details(exam_details, db_params):
    """
    Wrapper function to insert exam details.

    Args:
        exam_details (dict): Dictionary containing exam details.
        db_params (dict): Database connection parameters.

    Returns:
        int or None: Inserted exam_id or None if insertion failed.
    """
    return insert_exam_details(exam_details, db_params)


def 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 (Path): 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(str(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(OUTPUT_DIR / f"extracted_questions_{exam_id}.csv", index=False)
        logging.debug(f"Saved extracted questions to 'extracted_questions_{exam_id}.csv'.")

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


def process_exam_pair(exam_code, pak_pdf, fak_pdf, pages_config, db_params):
    """
    Process a single exam's PAK and FAK PDF pair.

    Args:
        exam_code (str): The exam code.
        pak_pdf (Path): Path to the PAK PDF.
        fak_pdf (Path): Path to the FAK PDF.
        pages_config (dict): Configuration mapping exam_code to PAK and FAK pages.
        db_params (dict): Database connection parameters.
    """
    logging.info(f"Starting processing for exam_code: {exam_code}")
    print(f"\n--- Processing Exam Code: {exam_code} ---")

    # Check if exam_code exists in pages_config
    if exam_code in pages_config:
        pak_pages = pages_config[exam_code].get('PAK_pages', [])
        fak_pages = pages_config[exam_code].get('FAK_pages', [])
    else:
        # Prompt user for page numbers
        page_numbers = get_user_input_pages(exam_code)
        pak_pages = page_numbers['PAK_pages']
        fak_pages = page_numbers['FAK_pages']
        # Update pages_config
        pages_config[exam_code] = {
            'PAK_pages': pak_pages,
            'FAK_pages': fak_pages
        }
        save_pages_config(pages_config)

    # Step 1: Extract Exam Details from PAK PDF (Assuming Exam Details are embedded in PAK)
    # Customize this extraction based on how exam details are presented in your PAK PDFs
    print("\n--- Extracting Exam Details from PAK ---")
    pak_text = extract_text_hybrid(str(pak_pdf), pages=pak_pages)
    normalized_pak_text = normalize_text(pak_text)

    # Example extraction logic (Customize as per actual PAK content)
    # For demonstration, we will assume that exam_name, advertisement_number, and exam_date can be extracted via regex
    try:
        exam_name_match = re.search(r'Exam Name[:\-]\s*(.*)', normalized_pak_text, re.IGNORECASE)
        advertisement_number_match = re.search(r'Advertisement Number[:\-]\s*(\d+/\d+-\d+)', normalized_pak_text, re.IGNORECASE)
        exam_date_match = re.search(r'Exam Date[:\-]\s*(\d{2}/\d{2}/\d{4})', normalized_pak_text, re.IGNORECASE)

        exam_name = exam_name_match.group(1).strip() if exam_name_match else "Unknown Exam Name"
        advertisement_number = advertisement_number_match.group(1).strip() if advertisement_number_match else "Unknown Advertisement Number"
        exam_date_str = exam_date_match.group(1).strip() if exam_date_match else "01/01/1970"

        try:
            exam_date = datetime.strptime(exam_date_str, '%d/%m/%Y').date()
        except ValueError:
            logging.warning(f"Invalid exam date format for exam_code {exam_code}: {exam_date_str}. Defaulting to 01/01/1970.")
            exam_date = datetime.strptime('01/01/1970', '%d/%m/%Y').date()

        exam_details = {
            'exam_name': exam_name,
            'advertisement_number': advertisement_number,
            'exam_date': exam_date,
            'exam_code': exam_code
        }

        print(f"Extracted Exam Details:\n  Name: {exam_name}\n  Advertisement Number: {advertisement_number}\n  Exam Date: {exam_date}")
        logging.info(f"Extracted Exam Details for {exam_code}: {exam_details}")

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

    # Step 2: Insert Exam Details into Database
    print("\nInserting Exam Details into Database...")
    exam_id = extract_and_insert_exam_details(exam_details, db_params)
    if not exam_id:
        print(f"Failed to insert exam details for {exam_code}. Skipping to next exam.")
        return

    # Step 3: Extract and Insert Questions from PAK
    print("\n--- Extracting Questions and Options from PAK ---")
    extract_and_insert_questions(pak_pdf, exam_id, db_params, pak_pages)

    # Step 4: Parse and Insert Answers from FAK
    print("\n--- Parsing Answer Key Table from FAK ---")
    grid_df, long_df = parse_answer_key_table(str(fak_pdf))

    if not grid_df.empty and not long_df.empty:
        # Display the grid format
        display_formatted_grid(grid_df)

        # Display statistics
        print("\nAnswer Key Statistics:")
        print(f"Total questions: {len(long_df)}")
        print("\nAnswer distribution:")
        answer_dist = long_df['Answer'].value_counts().sort_index()
        for answer, count in answer_dist.items():
            print(f"Option {answer}: {count} questions")

        # Save to Excel if needed
        output_path = OUTPUT_DIR / f"answer_key_analysis_{exam_code}.xlsx"
        with pd.ExcelWriter(output_path) as writer:
            grid_df.to_excel(writer, sheet_name='Grid Format', index=False)
            long_df.to_excel(writer, sheet_name='Long Format', index=False)
        print(f"\nAnswer key results saved to {output_path}")
        logging.info(f"Answer key results saved to {output_path}")

        # Insert Answers into Database
        print("\n--- Inserting Answers from Answer Key ---")
        insert_answers(long_df, exam_id, db_params)
    else:
        logging.warning(f"Failed to parse the answer key table for exam_code {exam_code}.")
        print("Failed to parse the answer key table. Skipping answers insertion.")


def main():
    """
    Main function to orchestrate the extraction and insertion process for multiple exams.
    """
    # 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: Load Pages Configuration
    pages_config = load_pages_config()

    # Step 2: Find All PAK and FAK Pairs
    exam_pairs = find_pak_fak_pairs(DATA_DIR)
    if not exam_pairs:
        print("No PAK and FAK pairs found in the data directory. Exiting...")
        return

    # Step 3: Process Each Exam Pair
    for exam_code, pak_pdf, fak_pdf in exam_pairs:
        process_exam_pair(exam_code, pak_pdf, fak_pdf, pages_config, DB_PARAMS)

    print("\nAll exams have been processed successfully.")


if __name__ == '__main__':
    main()::::::from flask import Flask, render_template, abort, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import relationship
import os
from dotenv import load_dotenv
from datetime import datetime

# Load environment variables
load_dotenv()

app = Flask(__name__)

# Secret Key for session management and security
app.config['SECRET_KEY'] = os.getenv('SECRET_KEY', 'your-default-secret-key')  # Ensure to set this in your .env file

# Database configuration
DB_NAME = os.getenv('DB_NAME')
DB_USER = os.getenv('DB_USER')
DB_PASSWORD = os.getenv('DB_PASSWORD')
DB_HOST = os.getenv('DB_HOST', 'localhost')
DB_PORT = os.getenv('DB_PORT', '5432')

app.config['SQLALCHEMY_DATABASE_URI'] = f'postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# Database Models
class Exam(db.Model):
    __tablename__ = 'exams'
    exam_id = db.Column(db.Integer, primary_key=True)
    exam_name = db.Column(db.String, nullable=False)
    advertisement_number = db.Column(db.String, nullable=False)
    exam_code = db.Column(db.String, unique=True, nullable=False)
    year = db.Column(db.Integer, nullable=False)

    questions = relationship('Question', backref='exam', lazy=True)

class Question(db.Model):
    __tablename__ = 'questions'
    question_id = db.Column(db.Integer, primary_key=True)
    exam_id = db.Column(db.Integer, db.ForeignKey('exams.exam_id'), nullable=False)
    question_number = db.Column(db.Integer, nullable=False)
    question_text = db.Column(db.Text, nullable=False)

    options = relationship('Option', backref='question', lazy=True)
    answer = relationship('Answer', uselist=False, backref='question')

class Option(db.Model):
    __tablename__ = 'options'
    option_id = db.Column(db.Integer, primary_key=True)
    question_id = db.Column(db.Integer, db.ForeignKey('questions.question_id'), nullable=False)
    option_label = db.Column(db.String(1), nullable=False)  # 'A', 'B', 'C', 'D'
    option_text = db.Column(db.Text, nullable=False)

class Answer(db.Model):
    __tablename__ = 'answers'
    answer_id = db.Column(db.Integer, primary_key=True)
    question_id = db.Column(db.Integer, db.ForeignKey('questions.question_id'), unique=True, nullable=False)
    correct_option = db.Column(db.String(1), nullable=False)  # 'A', 'B', 'C', 'D'

# Context Processor to Inject Current Year
@app.context_processor
def inject_current_year():
    return {'current_year': datetime.utcnow().year}

# Routes
@app.route('/')
def home():
    exams = Exam.query.order_by(Exam.year.desc()).all()
    return render_template('exams.html', exams=exams)

@app.route('/exam/<int:exam_id>')
def exam_detail(exam_id):
    exam = Exam.query.get_or_404(exam_id)
    questions = Question.query.filter_by(exam_id=exam_id).order_by(Question.question_number).all()
    return render_template('questions.html', exam=exam, questions=questions)

# API Route to Validate Answer
@app.route('/validate_answer', methods=['POST'])
def validate_answer():
    data = request.get_json()
    exam_id = data.get('exam_id')
    question_id = data.get('question_id')
    selected_option = data.get('selected_option')

    if not all([exam_id, question_id, selected_option]):
        return jsonify({'status': 'error', 'message': 'Missing data provided.'}), 400

    try:
        # Fetch the correct answer
        answer = Answer.query.filter_by(question_id=question_id).first()
        if not answer:
            return jsonify({'status': 'error', 'message': 'Answer not found.'}), 404

        is_correct = (selected_option.upper() == answer.correct_option.upper())

        return jsonify({
            'status': 'success',
            'is_correct': is_correct,
            'correct_option': answer.correct_option.upper()
        }), 200
    except Exception as e:
        # Log the error
        app.logger.error(f"Error validating answer: {e}")
        return jsonify({'status': 'error', 'message': 'Internal server error.'}), 500

# Error Handlers
@app.errorhandler(404)
def not_found(error):
    app.logger.warning(f"Page not found: {request.url}")
    return render_template('404.html'), 404

@app.errorhandler(500)
def internal_error(error):
    app.logger.error(f"Internal server error: {error}")
    return render_template('500.html'), 500

# Main Entry
if __name__ == '__main__':
    app.run(debug=True)