i added quantity in the database and the prompt of the ai. here is the new code. it is not saving data in the database. import os
import sqlite3
import fitz
import google.generativeai as genai
from dotenv import load_dotenv
import json
import logging
from pathlib import Path
import uuid
import time

# ------------------------------
# Configure Logging
# ------------------------------
logging.basicConfig(
    filename='app.log',
    filemode='a',
    format='%(asctime)s - %(levelname)s - %(message)s',
    level=logging.INFO
)

# ------------------------------
# Load Environment Variables
# ------------------------------
load_dotenv()
GEMINI_API_KEY = os.environ.get("API_KEY")
if not GEMINI_API_KEY:
    raise ValueError("API key not found. Please set the GEMINI_API_KEY environment variable.")


# ------------------------------
# API Configuration
# ------------------------------
def configure_api():
    """Configure the Gemini API with the provided API key."""
    if not GEMINI_API_KEY:
        raise ValueError("API key not found. Please set the GEMINI_API_KEY environment variable.")
    genai.configure(api_key=GEMINI_API_KEY)
    logging.info("Gemini API configured successfully.")


# ------------------------------
# Create Generative AI Model
# ------------------------------
def create_model():
    """Create and return a generative AI model with specified configuration."""
    generation_config = {
        "temperature": 0.1,
        "top_p": 0.1,
        "top_k": 10,
        "max_output_tokens": 8192,
        "response_mime_type": "text/plain",
    }
    safety_settings = [
        {"category": "HARM_CATEGORY_HARASSMENT", "threshold": "BLOCK_NONE"},
        {"category": "HARM_CATEGORY_HATE_SPEECH", "threshold": "BLOCK_NONE"},
        {"category": "HARM_CATEGORY_SEXUALLY_EXPLICIT", "threshold": "BLOCK_NONE"},
        {"category": "HARM_CATEGORY_DANGEROUS_CONTENT", "threshold": "BLOCK_NONE"},
    ]
    model = genai.GenerativeModel(
        model_name="gemini-1.5-flash-002",
        safety_settings=safety_settings,
        generation_config=generation_config,
        system_instruction="You are a helpful AI.",
    )
    logging.info("Generative AI model created successfully.")
    return model


# ------------------------------
# Initialize SQLite Database
# ------------------------------
def initialize_db(db_file="extracted_data.db"):
    """Creates the SQLite database and table if it doesn't exist."""
    try:
        with sqlite3.connect(db_file) as conn:
            cursor = conn.cursor()
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS records (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    file_name TEXT,
                    product_name TEXT,
                    part_number TEXT,
                    voucher_number TEXT,
                    quantity TEXT,
                    UNIQUE (file_name, product_name, part_number, voucher_number, quantity)
                )
            """)
            conn.commit()
        logging.info(f"Database initialized at {db_file}.")
    except Exception as e:
        logging.error(f"Error initializing database {db_file}: {e}")
        raise


# ------------------------------
# Save Records to Database
# ------------------------------
def save_records(records, db_file="extracted_data.db"):
    """Saves multiple records to the SQLite database."""
    try:
        with sqlite3.connect(db_file) as conn:
            cursor = conn.cursor()
            cursor.executemany(
                "INSERT OR IGNORE INTO records (file_name, product_name, part_number, voucher_number, quantity) VALUES (?, ?, ?, ?)",
                records
            )
            conn.commit()
            inserted = cursor.rowcount
            if inserted:
                logging.info(f"Inserted {inserted} new records into the database.")
            else:
                logging.info("No new records were inserted (all were duplicates).")
    except Exception as e:
        logging.error(f"Error saving records to database {db_file}: {e}")


# ------------------------------
# Upload Image to Gemini
# ------------------------------
def upload_image(image_path):
    """Uploads the given image file to Gemini and returns the uploaded file object."""
    try:
        image_path = Path(image_path)
        myfile = genai.upload_file(image_path)
        logging.info(f"Uploaded file: {myfile}")
        return myfile
    except Exception as e:
        logging.error(f"Error uploading image {image_path}: {e}")
        return None


# ------------------------------
# Generate AI Response
# ------------------------------
def generate_ai_response(model, uploaded_file, prompt):
    """Generates AI response using the uploaded file and prompt."""
    try:
        inputs = [uploaded_file, "\n\n", prompt]
        result = model.generate_content(inputs)
        logging.info(f"Generated content: {result.text}")
        return result.text
    except Exception as e:
        logging.error(f"Error generating AI response: {e}")
        return ""


# ------------------------------
# Save AI Response to File
# ------------------------------
def save_response(response_text, response_file_path="response.txt"):
    """Appends the AI response to the response file."""
    try:
        with open(response_file_path, "a", encoding="utf-8") as f:
            f.write("----- New API Response -----\n")
            f.write(response_text + "\n\n")
        logging.info(f"Saved AI response to {response_file_path}.")
    except Exception as e:
        logging.error(f"Error writing to {response_file_path}: {e}")


# ------------------------------
# Parse AI Response
# ------------------------------
def parse_response(response_text):
    """
    Parses the JSON response text to extract relevant fields.

    Args:
        response_text (str): The raw text response from the API.

    Returns:
        list[dict]: A list of dictionaries containing extracted fields.
    """
    try:
        # Extract the JSON part from the response
        json_start = response_text.find("```json")
        json_end = response_text.find("```", json_start + 1)
        if json_start == -1 or json_end == -1:
            logging.error("No JSON content found in the response.")
            return []

        json_str = response_text[json_start + len("```json"):json_end].strip()
        json_data = json.loads(json_str)

        extracted_data = []
        for item in json_data:
            # Extract required fields
            product_name = item.get("Product Name", "").replace('"', '').replace(',', '').strip()
            part_number = item.get("Part Number", "").replace('"', '').replace(',', '').strip()
            voucher_number = item.get("Voucher Code", "").replace('"', '').replace(',', '').strip()
            quantity = item.get("Quantity", "").replace('"', '').replace(',', '').strip()

            extracted_data.append({
                "Product Name": product_name,
                "Part Number": part_number,
                "Voucher Number": voucher_number,
                "Quantity": quantity
            })
        logging.info("Parsed AI response successfully.")
        return extracted_data
    except json.JSONDecodeError as e:
        logging.error(f"Error decoding JSON: {e}")
        return []
    except Exception as e:
        logging.error(f"Unexpected error during parsing: {e}")
        return []


# ------------------------------
# Parse and Save Data
# ------------------------------
def parse_and_save_data(api_response, pdf_file_name):
    """Parses the API response and saves each entry to the database."""
    records = []
    for entry in api_response:
        product_name = entry.get("Product Name", "")
        part_number = entry.get("Part Number", "")
        voucher_number = entry.get("Voucher Number", "")  # Ensure consistency
        quantity = entry.get("Quantity", "")
        records.append((pdf_file_name, product_name, part_number, voucher_number, quantity))

    if records:
        save_records(records)
    else:
        logging.warning(f"No valid data to save for file: {pdf_file_name}")


# ------------------------------
# Process PDFs and Save Data to DB
# ------------------------------
def process_pdf_to_db(pdf_folder, prompt, response_file="response.txt", db_file="extracted_data.db"):
    """Processes PDFs in a folder, extracts data using Gemini, and saves to SQLite DB."""
    # Initialize the response file
    try:
        with open(response_file, "w", encoding="utf-8") as f:
            f.write("API Responses Log\n\n")
        logging.info(f"Initialized response file at {response_file}.")
    except Exception as e:
        logging.error(f"Error initializing {response_file}: {e}")
        return

    model = create_model()

    # Ensure the temp folder exists
    temp_folder = Path("temp")
    temp_folder.mkdir(exist_ok=True)
    logging.info(f"Using temporary folder: {temp_folder.resolve()}")

    # Iterate through PDF files in the folder
    for root, dirs, files in os.walk(pdf_folder):
        for file in files:
            if file.lower().endswith('.pdf'):
                pdf_path = os.path.join(root, file)
                logging.info(f"Processing PDF: {pdf_path}")

                try:
                    pdf_document = fitz.open(pdf_path)
                    logging.info(f"Opened PDF: {pdf_path}")
                except Exception as e:
                    logging.error(f"Error opening PDF {pdf_path}: {e}")
                    continue

                for page_num in range(pdf_document.page_count):
                    # Generate a unique temporary image name
                    image_filename = f"temp_page_{page_num + 1}_{uuid.uuid4().hex}.png"
                    image_path = temp_folder / image_filename
                    try:
                        page = pdf_document.load_page(page_num)
                        pix = page.get_pixmap()
                        pix.save(image_path)
                        logging.info(f"Saved temporary image: {image_path}")
                    except Exception as e:
                        logging.error(f"Error processing page {page_num + 1} of {pdf_path}: {e}")
                        continue

                    try:
                        uploaded_file = upload_image(image_path)
                        if not uploaded_file:
                            logging.error(f"Skipping page {page_num + 1} due to upload failure.")
                            continue

                        ai_response = generate_ai_response(model, uploaded_file, prompt)
                        if not ai_response or ai_response.strip() == "[]":
                            logging.warning(f"No valid response for page {page_num + 1} in {pdf_path}.")
                            save_response("No valid data received.\n", response_file)
                        else:
                            save_response(ai_response, response_file)
                            parsed_data = parse_response(ai_response)
                            if parsed_data:
                                parse_and_save_data(parsed_data, file)
                            else:
                                logging.warning(f"Parsed data is empty for page {page_num + 1} in {pdf_path}.")

                    except Exception as e:
                        logging.error(f"Error during AI processing for page {page_num + 1} of {pdf_path}: {e}")
                    finally:
                        # Ensure the temporary image is deleted
                        if image_path.exists():
                            try:
                                image_path.unlink()
                                logging.info(f"Deleted temporary image: {image_path}")
                            except Exception as e:
                                logging.error(f"Error deleting image {image_path}: {e}")

                pdf_document.close()
                logging.info(f"Finished processing PDF: {pdf_path}\n")


# ------------------------------
# Main Function
# ------------------------------
def main():
    """Main function to configure API and start processing."""
    configure_api()
    initialize_db()

    pdf_folder = "COA_ DOC_2024"  # Path to folder with PDFs
    prompt_file = "prompt.txt"
    response_file = "response.txt"
    db_file = "extracted_data.db"

    if not os.path.isdir(pdf_folder):
        logging.error(f"The folder '{pdf_folder}' does not exist.")
        print(f"Error: The folder '{pdf_folder}' does not exist.")
        return

    if not os.path.isfile(prompt_file):
        logging.error(f"The prompt file '{prompt_file}' does not exist.")
        print(f"Error: The prompt file '{prompt_file}' does not exist.")
        return

    try:
        with open(prompt_file, 'r', encoding='utf-8') as f:
            prompt = f.read().strip()
        if not prompt:
            logging.error("The prompt file is empty.")
            print("Error: The prompt file is empty.")
            return
        logging.info("Prompt file read successfully.")
    except Exception as e:
        logging.error(f"Error reading prompt file: {e}")
        print(f"Error reading prompt file: {e}")
        return

    process_pdf_to_db(pdf_folder, prompt, response_file, db_file)


# ------------------------------
# Entry Point
# ------------------------------
if __name__ == "__main__":
    main() here is the api output: API Responses Log

----- New API Response -----
```json
[
  {
    "Product Name": "Suction Unit HI-VAC Plus 60 Ltrs/Min",
    "Part Number": "SU56",
    "Voucher Code": "PO-N/11286",
    "Quantity": "2 pcs"
  },
  {
    "Product Name": "Motor Pump For Suction Machine",
    "Part Number": "SU50",
    "Voucher Code": "PO-N/11286",
    "Quantity": "2 pcs"
  }
]
```


----- New API Response -----
```json
[]
```