"""
Script to iterate over histogram data and insert it into the database.
This is a stub for future development.
"""
import os
import re
import numpy as np
import sqlalchemy
from sqlalchemy import text, exc

# Database URL from alembic.ini
DATABASE_URL = "postgresql+psycopg2://postgres:pgRocks@localhost:5432/testdb"
BASE_DIR = '/home/kfl/dev/git/avdb-py-v1'

def insert_histogram_data():
    """
    Iterate over histogram data for levels 3-5 and insert it into the database.
    This is a stub and will be developed over several prompts.
    """
    engine = sqlalchemy.create_engine(DATABASE_URL)
    
    # Placeholder for histogram data directory
    img_folder = "img"
    inserted_count = 0
    skipped_count = 0
    duplicate_count = 0
    
    # Iterate over histogram data files (stub)
    for root, dirs, files in os.walk(img_folder):
        for file in files:
            if file.lower().endswith('.mmap'):
                histogram_file_path = os.path.join(BASE_DIR, root, file)
                print(histogram_file_path)
                level = 0
                m = re.match(r'.*(\d{1})\.jpeg.histos.mmap', histogram_file_path)
                if m:
                    level = int(m.group(1))
                if level in (3,4,5):
                    # Extract histogram metadata (stub)
                    try:
                        histogram_values = []  # Placeholder

                        gridsplit = 0
                        if level == 3:
                            gridsplit = 8
                        elif level == 4:
                            gridsplit = 4
                        elif level == 5:
                            gridsplit = 2

                        data = np.memmap(
                            histogram_file_path,
                            dtype="float32",
                            mode='r+',
                            shape=((gridsplit+1)**2,48)
                        )
                        print(data.shape)
                        for row in data:
                            print(row)
                    
                        # # Insert into the database (stub)
                        # with engine.connect() as connection:
                        #     query = text(
                        #         """
                        #         INSERT INTO histogram_table (level, histogram_values)
                        #         VALUES (:level, :histogram_values)
                        #         """
                        #     )
                        #     connection.execute(
                        #         query,
                        #         {"level": level, "histogram_values": histogram_values}
                        #     )
                        #     connection.commit()
                        #     inserted_count += 1
                        #     print(f"Inserted histogram data for level {level}")
                    except exc.IntegrityError as e:
                        # Handle duplicate key violations
                        print(f"Duplicate histogram entry skipped for {histogram_file_path}: {e}")
                        duplicate_count += 1
                    except Exception as e:
                        print(f"Failed to insert histogram data for {histogram_file_path}: {e}")
                        skipped_count += 1
        
    print(f"\nTotal histogram entries inserted: {inserted_count}")
    print(f"Total duplicates skipped: {duplicate_count}")
    print(f"Total entries skipped due to errors: {skipped_count}")

if __name__ == "__main__":
    insert_histogram_data()