GenXdev.AI

1.192.2025

Functions/GenXdev.AI.Queries/Export-ImageDatabase.ps1

################################################################################
<#
.SYNOPSIS
Initializes and populates the SQLite database by discovering images directly.
 
.DESCRIPTION
Creates a SQLite database with optimized schema for fast image searching based on
metadata including keywords, people, objects, scenes, and descriptions. The function
always deletes any existing database file and creates a fresh one, discovers images
using Find-Image from specified directories or configured image directories, and
populates the database directly without requiring a metadata JSON file. Finally,
it creates indexes for optimal performance.
 
.PARAMETER InputObject
Accepts search results from a Find-Image call to regenerate the view.
 
.PARAMETER DatabaseFilePath
Path to the SQLite database file. If not specified, uses the default location
under Storage\allimages.meta.db.
 
.PARAMETER ImageDirectories
Array of directory paths to search for images. If not specified, uses the
configured image directories from Get-AIImageCollection.
 
.PARAMETER EmbedImages
Embed images directly into the database.
 
.EXAMPLE
Export-ImageDatabase -DatabaseFilePath "C:\Custom\Path\images.db" -ImageDirectories @("C:\Photos", "D:\Images") -EmbedImages
 
.EXAMPLE
indexcachedimages
#>
function Export-ImageDatabase {

    [CmdletBinding()]
    [Alias("indexcachedimages", "Inititalize-ImageDatabase", "Recreate-ImageIndex")]

    param(

        [Parameter(
            Position = 0,
            Mandatory = $false,
            ValueFromPipeline = $true,
            HelpMessage = ("Accepts search results from a Find-Image " +
                "call to regenerate the view.")
        )]
        [object[]] $InputObject,
       ###############################################################################
       [Parameter(
        Position = 0,
        Mandatory = $false,
        HelpMessage = "The path to the image database file. If not specified, a default path is used."
        )]
        [string] $DatabaseFilePath,
        ###############################################################################
        [Parameter(
            Mandatory = $false,
            HelpMessage = "Array of directory paths to search for images"
        )]
        [ValidateNotNullOrEmpty()]
        [Alias("imagespath", "directories", "imgdirs", "imagedirectory")]
        [string[]] $ImageDirectories,
        ###############################################################################
        [Parameter(
            Mandatory = $false,
            HelpMessage = (
                "Array of directory path-like search strings to filter images by " +
                "path (SQL LIKE patterns, e.g. '%\\2024\\%')"
            )
        )]
        [string[]] $PathLike = @(),
        ###############################################################################
        [Parameter(
            Mandatory = $false,
            HelpMessage = "Language for descriptions and keywords."
        )]
        [ValidateSet(
            "Afrikaans", "Akan", "Albanian", "Amharic", "Arabic", "Armenian",
            "Azerbaijani", "Basque", "Belarusian", "Bemba", "Bengali", "Bihari",
            "Bork, bork, bork!", "Bosnian", "Breton", "Bulgarian", "Cambodian",
            "Catalan", "Cherokee", "Chichewa", "Chinese (Simplified)",
            "Chinese (Traditional)", "Corsican", "Croatian", "Czech", "Danish",
            "Dutch", "Elmer Fudd", "English", "Esperanto", "Estonian", "Ewe",
            "Faroese", "Filipino", "Finnish", "French", "Frisian", "Ga",
            "Galician", "Georgian", "German", "Greek", "Guarani", "Gujarati",
            "Hacker", "Haitian Creole", "Hausa", "Hawaiian", "Hebrew", "Hindi",
            "Hungarian", "Icelandic", "Igbo", "Indonesian", "Interlingua",
            "Irish", "Italian", "Japanese", "Javanese", "Kannada", "Kazakh",
            "Kinyarwanda", "Kirundi", "Klingon", "Kongo", "Korean",
            "Krio (Sierra Leone)", "Kurdish", "Kurdish (Soranî)", "Kyrgyz",
            "Laothian", "Latin", "Latvian", "Lingala", "Lithuanian", "Lozi",
            "Luganda", "Luo", "Macedonian", "Malagasy", "Malay", "Malayalam",
            "Maltese", "Maori", "Marathi", "Mauritian Creole", "Moldavian",
            "Mongolian", "Montenegrin", "Nepali", "Nigerian Pidgin",
            "Northern Sotho", "Norwegian", "Norwegian (Nynorsk)", "Occitan",
            "Oriya", "Oromo", "Pashto", "Persian", "Pirate", "Polish",
            "Portuguese (Brazil)", "Portuguese (Portugal)", "Punjabi", "Quechua",
            "Romanian", "Romansh", "Runyakitara", "Russian", "Scots Gaelic",
            "Serbian", "Serbo-Croatian", "Sesotho", "Setswana",
            "Seychellois Creole", "Shona", "Sindhi", "Sinhalese", "Slovak",
            "Slovenian", "Somali", "Spanish", "Spanish (Latin American)",
            "Sundanese", "Swahili", "Swedish", "Tajik", "Tamil", "Tatar",
            "Telugu", "Thai", "Tigrinya", "Tonga", "Tshiluba", "Tumbuka",
            "Turkish", "Turkmen", "Twi", "Uighur", "Ukrainian", "Urdu", "Uzbek",
            "Vietnamese", "Welsh", "Wolof", "Xhosa", "Yiddish", "Yoruba", "Zulu"
        )]
        [string] $Language,
        #######################################################################
        [parameter(
            Mandatory = $false,
            HelpMessage = ("The directory containing face images organized by " +
                        "person folders. If not specified, uses the " +
                        "configured faces directory preference.")
        )]
        [string] $FacesDirectory,
        #######################################################################
        [Parameter(
            Mandatory = $false,
            HelpMessage = "Embed images as base64."
        )]
        [switch] $EmbedImages,
        ###############################################################################
        [Parameter(
            Mandatory = $false,
            HelpMessage = "Force rebuild of the image index database."
        )]
        [switch] $ForceIndexRebuild,
        ###############################################################################
        [Parameter(
            Mandatory = $false,
            HelpMessage = "Switch to disable fallback behavior."
        )]
        [switch] $NoFallback,
        ###############################################################################
        [Parameter(
            Mandatory = $false,
            HelpMessage = "Switch to skip database initialization and rebuilding."
        )]
        [switch] $NeverRebuild
    ###############################################################################
    )

    begin {

        # determine database file path if not provided
        $params = GenXdev.Helpers\Copy-IdenticalParamValues `
            -BoundParameters $PSBoundParameters `
            -FunctionName "GenXdev.AI\Get-ImageDatabasePath" `
            -DefaultValues (
                Microsoft.PowerShell.Utility\Get-Variable -Scope Local -ErrorAction SilentlyContinue
        )

        $DatabaseFilePath = GenXdev.AI\Get-ImageDatabasePath @params -NeverRebuild

        # retrieve configured image directories if not provided
        $ImageDirectories = GenXdev.AI\Get-AIImageCollection -ImageDirectories $ImageDirectories

        # output that the image index database is being recreated
        Microsoft.PowerShell.Utility\Write-Host @"
Recreating image index database
Path = $DatabaseFilePath")
Image directories = $(($ImageDirectories -join ", "))
 
"@ -ForegroundColor Cyan

        # output the directories being used for image discovery
        Microsoft.PowerShell.Utility\Write-Verbose (
            "Directories:`r`n$(($ImageDirectories | Microsoft.PowerShell.Utility\ConvertTo-Json))"
        )

        # output whether image embedding is enabled or disabled
        if ($EmbedImages) {
            Microsoft.PowerShell.Utility\Write-Verbose (
                "Image embedding: ENABLED - Images will be stored as binary data in database"
            )
        } else {
            Microsoft.PowerShell.Utility\Write-Verbose (
                "Image embedding: DISABLED - Only file paths will be stored"
            )
        }

        # define schema version constant
        $SCHEMA_VERSION = "1.0.0.3"

        # initialize info object for tracking found results

        $Info = @{
            FoundResults = $false
            TotalImages = 0
        }

        if ([String]::IsNullOrWhiteSpace($DatabaseFilePath)) {

            Microsoft.PowerShell.Utility\Write-Error (
                "Failed to retrieve database file path."
            )
            return
        }

        try {
            [System.Data.SQLite.SQLiteConnection]::Shutdown()
        }
        catch {
            Microsoft.PowerShell.Utility\Write-Verbose (
                "Failed to shutdown SQLite connection: $($_.Exception.Message)"
            )
        }

        Microsoft.PowerShell.Utility\Write-Verbose (
            "Using image database: $DatabaseFilePath"
        )

        $DatabaseFilePath = GenXdev.FileSystem\Expand-Path (
            $DatabaseFilePath
        ) -CreateDirectory -DeleteExistingFile -ErrorAction SilentlyContinue

        # if the database file exists, move it to backup
        if ([IO.File]::Exists($DatabaseFilePath)) {

            # try to move the file to backup, swap if move fails
            if (-not (GenXdev.FileSystem\Move-ItemWithTracking $DatabaseFilePath $DatabaseBackupFilePath -Force)) {

                $tmp = $DatabaseFilePath
                $DatabaseFilePath = $DatabaseBackupFilePath
                $DatabaseBackupFilePath = $tmp
            }
            else {

                # move the *-journal file as wel
                $journalFilePath = "$DatabaseFilePath-journal"

                if ([IO.File]::Exists($journalFilePath)) {
                    if (-not (GenXdev.FileSystem\Move-ItemWithTracking $journalFilePath "$DatabaseBackupFilePath-journal" -Force)) {
                        Microsoft.PowerShell.Utility\Write-Warning (
                            "Failed to move journal file: $journalFilePath"
                        )

                        # move renamed file back
                        if (-not (GenXdev.FileSystem\Move-ItemWithTracking -Path $DatabaseBackupFilePath -Destination $DatabaseFilePath -Force -ErrorAction SilentlyContinue)) {

                            Microsoft.PowerShell.Utility\Write-Warning (
                                "Failed to restore original database file: $DatabaseFilePath"
                            )
                        } else {
                            Microsoft.PowerShell.Utility\Write-Verbose (
                                "Restored original database file: $DatabaseFilePath"
                            )
                        }

                        $tmp = $DatabaseFilePath
                        $DatabaseFilePath = $DatabaseBackupFilePath
                        $DatabaseBackupFilePath = $tmp
                    }
                }
            }
        }

        # output verbose information about database and metadata paths
        Microsoft.PowerShell.Utility\Write-Verbose (
            "Database path: $DatabaseFilePath"
        )
        Microsoft.PowerShell.Utility\Write-Verbose (
            "Metadata path: $(($ImageDirectories | Microsoft.PowerShell.Utility\ConvertTo-Json -Depth 10))"
        )

        # define table creation scripts for database schema
        $createImagesTable = @"
CREATE TABLE IF NOT EXISTS Images (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT UNIQUE NOT NULL,
    image_data BLOB,
    has_explicit_content BOOLEAN DEFAULT 0,
    has_nudity BOOLEAN DEFAULT 0,
    short_description TEXT,
    long_description TEXT,
    picture_type TEXT,
    overall_mood_of_image TEXT,
    style_type TEXT,
    description_keywords TEXT,
    people_count INTEGER DEFAULT 0,
    people_faces TEXT,
    people_json TEXT,
    objects_count INTEGER DEFAULT 0,
    objects_list TEXT,
    objects_json TEXT,
    object_counts TEXT,
    scene_label TEXT,
    scene_confidence REAL,
    scene_confidence_percentage REAL,
    scene_processed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
"@

        # create keywords lookup table for fast searching
        $createKeywordsTable = @"
CREATE TABLE IF NOT EXISTS ImageKeywords (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    image_id INTEGER NOT NULL,
    keyword TEXT NOT NULL,
    FOREIGN KEY (image_id) REFERENCES Images(id) ON DELETE CASCADE
);
"@

        # create people lookup table
        $createPeopleTable = @"
CREATE TABLE IF NOT EXISTS ImagePeople (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    image_id INTEGER NOT NULL,
    person_name TEXT NOT NULL,
    FOREIGN KEY (image_id) REFERENCES Images(id) ON DELETE CASCADE
);
"@

        # create objects lookup table
        $createObjectsTable = @"
CREATE TABLE IF NOT EXISTS ImageObjects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    image_id INTEGER NOT NULL,
    object_name TEXT NOT NULL,
    object_count INTEGER DEFAULT 1,
    FOREIGN KEY (image_id) REFERENCES Images(id) ON DELETE CASCADE
);
"@

        # create scenes lookup table
        $createScenesTable = @"
CREATE TABLE IF NOT EXISTS ImageScenes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    image_id INTEGER NOT NULL,
    scene_name TEXT NOT NULL,
    confidence REAL,
    FOREIGN KEY (image_id) REFERENCES Images(id) ON DELETE CASCADE
);
"@

# create schema version table
        $createSchemaVersionTable = @"
CREATE TABLE IF NOT EXISTS ImageSchemaVersion (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    version TEXT NOT NULL
);
"@

# create indexes for super fast searching with NO TABLE SCANS
        $createIndexes = @"
-- ===================================================================
-- PRIMARY SINGLE-COLUMN INDEXES (Most frequently used filters)
-- ===================================================================
 
-- Path index for unique lookups and sorting (most common operation)
CREATE INDEX IF NOT EXISTS idx_images_path ON Images(path);
 
-- Content safety filters (very frequently used together)
CREATE INDEX IF NOT EXISTS idx_images_nudity ON Images(has_nudity);
CREATE INDEX IF NOT EXISTS idx_images_explicit ON Images(has_explicit_content);
 
-- Core categorical filters
CREATE INDEX IF NOT EXISTS idx_images_picture_type ON Images(picture_type);
CREATE INDEX IF NOT EXISTS idx_images_mood ON Images(overall_mood_of_image);
CREATE INDEX IF NOT EXISTS idx_images_style ON Images(style_type);
CREATE INDEX IF NOT EXISTS idx_images_scene_label ON Images(scene_label);
 
-- Add regular indexes for text search columns (for LIKE/GLOB)
CREATE INDEX IF NOT EXISTS idx_images_short_description ON Images(short_description);
CREATE INDEX IF NOT EXISTS idx_images_long_description ON Images(long_description);
CREATE INDEX IF NOT EXISTS idx_images_description_keywords ON Images(description_keywords);
 
-- Count filters for performance
CREATE INDEX IF NOT EXISTS idx_images_people_count ON Images(people_count);
CREATE INDEX IF NOT EXISTS idx_images_objects_count ON Images(objects_count);
 
-- ===================================================================
-- COMPOSITE INDEXES FOR COMMON FILTER COMBINATIONS
-- ===================================================================
 
-- Safety + Type combinations (very common search pattern)
CREATE INDEX IF NOT EXISTS idx_images_safety_type ON Images(has_nudity, has_explicit_content, picture_type);
CREATE INDEX IF NOT EXISTS idx_images_safety_mood ON Images(has_nudity, has_explicit_content, overall_mood_of_image);
CREATE INDEX IF NOT EXISTS idx_images_safety_style ON Images(has_nudity, has_explicit_content, style_type);
 
-- Type + Mood + Style combinations (aesthetic searches)
CREATE INDEX IF NOT EXISTS idx_images_aesthetic ON Images(picture_type, overall_mood_of_image, style_type);
 
-- Count-based composite indexes (for filtering by presence of people/objects)
CREATE INDEX IF NOT EXISTS idx_images_counts_safety ON Images(people_count, objects_count, has_nudity, has_explicit_content);
 
-- Scene confidence optimization
CREATE INDEX IF NOT EXISTS idx_images_scene_confidence ON Images(scene_label, scene_confidence);
 
-- ===================================================================
-- COVERING INDEXES TO AVOID TABLE LOOKUPS
-- ===================================================================
 
-- Covering index for basic search results (includes most commonly selected columns)
CREATE INDEX IF NOT EXISTS idx_images_covering_basic ON Images(
    path, has_nudity, has_explicit_content, picture_type, overall_mood_of_image,
    style_type, people_count, objects_count, scene_label, scene_confidence,
    short_description, long_description, description_keywords
);
 
-- ===================================================================
-- LOOKUP TABLE INDEXES FOR JOINS (Optimized FOR exact matches first)
-- ===================================================================
 
-- Keywords table optimization - exact matches first, then prefix matches
CREATE INDEX IF NOT EXISTS idx_keywords_exact ON ImageKeywords(keyword, image_id);
CREATE INDEX IF NOT EXISTS idx_keywords_prefix ON ImageKeywords(keyword COLLATE NOCASE, image_id);
CREATE INDEX IF NOT EXISTS idx_keywords_reverse ON ImageKeywords(image_id, keyword);
 
-- People table optimization - exact matches first, then prefix matches
CREATE INDEX IF NOT EXISTS idx_people_exact ON ImagePeople(person_name, image_id);
CREATE INDEX IF NOT EXISTS idx_people_prefix ON ImagePeople(person_name COLLATE NOCASE, image_id);
CREATE INDEX IF NOT EXISTS idx_people_reverse ON ImagePeople(image_id, person_name);
 
-- Objects table optimization - exact matches first, then prefix matches
CREATE INDEX IF NOT EXISTS idx_objects_exact ON ImageObjects(object_name, image_id, object_count);
CREATE INDEX IF NOT EXISTS idx_objects_prefix ON ImageObjects(object_name COLLATE NOCASE, image_id, object_count);
CREATE INDEX IF NOT EXISTS idx_objects_reverse ON ImageObjects(image_id, object_name, object_count);
 
-- Scenes table optimization - exact matches first, then prefix matches
CREATE INDEX IF NOT EXISTS idx_scenes_exact ON ImageScenes(scene_name, image_id, confidence);
CREATE INDEX IF NOT EXISTS idx_scenes_prefix ON ImageScenes(scene_name COLLATE NOCASE, image_id, confidence);
CREATE INDEX IF NOT EXISTS idx_scenes_reverse ON ImageScenes(image_id, scene_name, confidence);
CREATE INDEX IF NOT EXISTS idx_scenes_confidence ON ImageScenes(confidence, scene_name);
 
-- ===================================================================
-- PARTIAL INDEXES FOR SELECTIVE FILTERING (NO TABLE SCANS)
-- ===================================================================
 
-- Only index images with nudity (for faster nudity searches)
CREATE INDEX IF NOT EXISTS idx_images_has_nudity ON Images(path, picture_type, overall_mood_of_image)
    WHERE has_nudity = 1;
 
-- Only index images with explicit content
CREATE INDEX IF NOT EXISTS idx_images_has_explicit ON Images(path, picture_type, overall_mood_of_image)
    WHERE has_explicit_content = 1;
 
-- Only index family-safe images (no nudity, no explicit content)
CREATE INDEX IF NOT EXISTS idx_images_family_safe ON Images(path, picture_type, overall_mood_of_image, style_type)
    WHERE has_nudity = 0 AND has_explicit_content = 0;
 
-- Only index images with people (people_count > 0)
CREATE INDEX IF NOT EXISTS idx_images_with_people ON Images(path, people_count, picture_type)
    WHERE people_count > 0;
 
-- Only index images with objects (objects_count > 0)
CREATE INDEX IF NOT EXISTS idx_images_with_objects ON Images(path, objects_count, picture_type)
    WHERE objects_count > 0;
 
-- ===================================================================
-- PREFIX SEARCH OPTIMIZATION (AVOID LEADING WILDCARD TABLE SCANS)
-- ===================================================================
 
-- For efficient prefix searches without leading wildcards
CREATE INDEX IF NOT EXISTS idx_keywords_prefix_only ON ImageKeywords(
    CASE WHEN keyword GLOB '[A-Za-z]*' THEN substr(keyword, 1, 3) END,
    keyword, image_id
) WHERE keyword GLOB '[A-Za-z]*';
 
CREATE INDEX IF NOT EXISTS idx_people_prefix_only ON ImagePeople(
    CASE WHEN person_name GLOB '[A-Za-z]*' THEN substr(person_name, 1, 3) END,
    person_name, image_id
) WHERE person_name GLOB '[A-Za-z]*';
 
CREATE INDEX IF NOT EXISTS idx_objects_prefix_only ON ImageObjects(
    CASE WHEN object_name GLOB '[A-Za-z]*' THEN substr(object_name, 1, 3) END,
    object_name, image_id
) WHERE object_name GLOB '[A-Za-z]*';
 
CREATE INDEX IF NOT EXISTS idx_scenes_prefix_only ON ImageScenes(
    CASE WHEN scene_name GLOB '[A-Za-z]*' THEN substr(scene_name, 1, 3) END,
    scene_name, image_id
) WHERE scene_name GLOB '[A-Za-z]*';
 
-- ===================================================================
-- CASE-INSENSITIVE SEARCH OPTIMIZATION (COLLATION BASED)
-- ===================================================================
 
-- Main table case-insensitive searches using collation (more efficient than lower())
CREATE INDEX IF NOT EXISTS idx_images_type_nocase ON Images(picture_type COLLATE NOCASE);
CREATE INDEX IF NOT EXISTS idx_images_mood_nocase ON Images(overall_mood_of_image COLLATE NOCASE);
CREATE INDEX IF NOT EXISTS idx_images_style_nocase ON Images(style_type COLLATE NOCASE);
CREATE INDEX IF NOT EXISTS idx_images_scene_nocase ON Images(scene_label COLLATE NOCASE);
 
-- ===================================================================
-- SPECIALIZED INDEXES FOR COMPLEX QUERIES
-- ===================================================================
 
-- Multi-table join optimization with covering columns
CREATE INDEX IF NOT EXISTS idx_images_join_optimize ON Images(
    id, path, has_nudity, has_explicit_content, picture_type,
    overall_mood_of_image, style_type, people_count, objects_count
);
 
-- Range queries on confidence scores
CREATE INDEX IF NOT EXISTS idx_images_scene_confidence_range ON Images(scene_confidence, scene_label, path)
    WHERE scene_confidence > 0;
"@
    }

    process {

        # start stopwatch for timing the process
        $totalTime = [System.Diagnostics.Stopwatch]::StartNew()

        # always delete existing database file to ensure clean rebuild
        if (Microsoft.PowerShell.Management\Test-Path $DatabaseFilePath) {
            Microsoft.PowerShell.Utility\Write-Verbose (
                "Deleting existing database file for clean rebuild..."
            )
            Microsoft.PowerShell.Management\Remove-Item $DatabaseFilePath -Force
        }

        # create new database
        Microsoft.PowerShell.Utility\Write-Verbose "Creating new database..."
        GenXdev.Data\New-SQLiteDatabase -DatabaseFilePath $DatabaseFilePath

        # create tables (without indexes initially for faster inserts)
        Microsoft.PowerShell.Utility\Write-Verbose "Creating database tables..."
        $createTablesQueries = @(
            $createImagesTable,
            $createKeywordsTable,
            $createPeopleTable,
            $createObjectsTable,
            $createScenesTable,
            $createSchemaVersionTable
        )
        GenXdev.Data\Invoke-SQLiteQuery -DatabaseFilePath $DatabaseFilePath -Queries $createTablesQueries

        # get images using Find-Image for direct integration
        Microsoft.PowerShell.Utility\Write-Verbose "Discovering images using Find-Image..."

        # create transaction for batch operations
        $params = GenXdev.Helpers\Copy-IdenticalParamValues `
            -BoundParameters $PSBoundParameters `
            -FunctionName "GenXdev.Data\Get-SQLiteTransaction" `
            -DefaultValues (Microsoft.PowerShell.Utility\Get-Variable -Scope Local -ErrorAction SilentlyContinue)

        $transaction = GenXdev.Data\Get-SQLiteTransaction @params

        # prepare image insertion query
        $insertQuery = if ($EmbedImages) {
            "INSERT INTO Images (path, image_data, has_explicit_content, has_nudity, short_description, long_description, picture_type, overall_mood_of_image, style_type, description_keywords, people_count, people_faces, people_json, objects_count, objects_list, objects_json, object_counts, scene_label, scene_confidence, scene_confidence_percentage, scene_processed_at) VALUES (@path, @image_data, @explicit, @nudity, @short_desc, @long_desc, @pic_type, @mood, @style, @desc_keywords, @people_count, @people_faces, @people_json, @objects_count, @objects_list, @objects_json, @object_counts, @scene_label, @scene_confidence, @scene_conf_pct, @scene_processed)"
        } else {
            "INSERT INTO Images (path, has_explicit_content, has_nudity, short_description, long_description, picture_type, overall_mood_of_image, style_type, description_keywords, people_count, people_faces, people_json, objects_count, objects_list, objects_json, object_counts, scene_label, scene_confidence, scene_confidence_percentage, scene_processed_at) VALUES (@path, @explicit, @nudity, @short_desc, @long_desc, @pic_type, @mood, @style, @desc_keywords, @people_count, @people_faces, @people_json, @objects_count, @objects_list, @objects_json, @object_counts, @scene_label, @scene_confidence, @scene_conf_pct, @scene_processed)"
        }

        function ImportImages {
            param($Info)

            # set found results to true when images are found
            $Info.FoundResults = $true

            # copy identical parameter values for Find-Image
            $findImageParams = GenXdev.Helpers\Copy-IdenticalParamValues `
                -BoundParameters $PSBoundParameters `
                -FunctionName "GenXdev.AI\Find-Image" `
                -DefaultValues (Microsoft.PowerShell.Utility\Get-Variable -Scope Local -ErrorAction SilentlyContinue)

            # set image directories for Find-Image
            $findImageParams.ImageDirectories = GenXdev.AI\Get-AIImageCollection -ImageDirectories $ImageDirectories

            # prepare lookup table inserts
            [System.Collections.Generic.List[String]] $lookupQueries = [System.Collections.Generic.List[String]]::new()
            [System.Collections.Generic.List[System.Collections.Hashtable]] $lookupParams = [System.Collections.Generic.List[System.Collections.Hashtable]]::new()

            function insertImage {
                param($image, $Info)

                process {
                    $lookupQueries.Clear()
                    $lookupParams.Clear()
                    $Info.FoundResults = $true
                    try {
                        # convert image object to json and back for deep copy
                        $image = $image |
                            Microsoft.PowerShell.Utility\ConvertTo-Json -depth 20 |
                            Microsoft.PowerShell.Utility\ConvertFrom-Json
                        $Info.TotalImages++

                        # build parameters for main image record insert
                        $imageParams = @{
                            "path"            = $image.path
                            "explicit"        = if ($image.description -and $image.description.has_explicit_content) { 1 } else { 0 }
                            "nudity"          = if ($image.description -and $image.description.has_nudity) { 1 } else { 0 }
                            "short_desc"      = if ($image.description) { $image.description.short_description } else { "" }
                            "long_desc"       = if ($image.description) { $image.description.long_description } else { "" }
                            "pic_type"        = if ($image.description) { $image.description.picture_type } else { "" }
                            "mood"            = if ($image.description) { $image.description.overall_mood_of_image } else { "" }
                            "style"           = if ($image.description) { $image.description.style_type } else { "" }
                            "desc_keywords"   = if ($image.description -and $image.description.keywords) {
                                if ($image.description.keywords.Count -gt 0) {
                                    $image.description.keywords | Microsoft.PowerShell.Utility\ConvertTo-Json -Compress -Depth 20
                                } else { "[]" }
                            } else { "[]" }
                            "people_count"    = if ($image.people) { $image.people.count } else { 0 }
                            "people_faces"    = if ($image.people -and $image.people.faces) {
                                if ($image.people.faces.Count -gt 0) {
                                    $image.people.faces | Microsoft.PowerShell.Utility\ConvertTo-Json -Compress -Depth 20
                                } else { "[]" }
                            } else { "[]" }
                            "people_json"     = if ($image.people) { $image.people | Microsoft.PowerShell.Utility\ConvertTo-Json -Compress -Depth 20 } else { '{}' }
                            "objects_count"   = if ($image.objects) { $image.objects.count } else { 0 }
                            "objects_list"    = if ($image.objects -and $image.objects.objects) {
                                if ($image.objects.objects.Count -gt 0) {
                                    $image.objects.objects | Microsoft.PowerShell.Utility\ConvertTo-Json -Compress -Depth 20
                                } else { "[]" }
                            } else { "[]" }
                            "objects_json"    = if ($image.objects) { $image.objects | Microsoft.PowerShell.Utility\ConvertTo-Json -Compress -Depth 20 } else { '{}' }
                            "object_counts"   = if ($image.objects -and $image.objects.object_counts) {
                                $image.objects.object_counts | Microsoft.PowerShell.Utility\ConvertTo-Json -Compress -Depth 20
                            } else { "{}" }
                            "scene_label"     = if ($image.scenes) { $image.scenes.label } else { "" }
                            "scene_confidence" = if ($image.scenes) { $image.scenes.confidence } else { 0 }
                            "scene_conf_pct"   = if ($image.scenes) { $image.scenes.confidence_percentage } else { 0 }
                            "scene_processed"  = if ($image.scenes) { $image.scenes.processed_at } else { "" }
                        }

                        # add image data if embedding is enabled
                        if ($EmbedImages) {
                            try {
                                # check if image file exists before reading
                                if (Microsoft.PowerShell.Management\Test-Path $image.path -PathType Leaf) {
                                    Microsoft.PowerShell.Utility\Write-Verbose (
                                        "Reading image data from: $($image.path)"
                                    )
                                    $imageBytes = [System.IO.File]::ReadAllBytes($image.path)
                                    $imageParams["image_data"] = $imageBytes
                                    Microsoft.PowerShell.Utility\Write-Verbose (
                                        "Embedded $($imageBytes.Length) bytes for image: $($image.path)"
                                    )
                                } else {
                                    Microsoft.PowerShell.Utility\Write-Warning (
                                        "Image file not found for embedding: $($image.path)"
                                    )
                                    $imageParams["image_data"] = $null
                                }
                            } catch {
                                Microsoft.PowerShell.Utility\Write-Warning (
                                    "Failed to read image data for embedding: $($image.path) - $($_.Exception.Message)"
                                )
                                $imageParams["image_data"] = $null
                            }
                        }

                        # execute the insert and get the new ID
                        $queries = @($insertQuery, "SELECT last_insert_rowid() AS newId;")
                        $result = GenXdev.Data\Invoke-SQLiteQuery -Transaction $transaction -Queries $queries -SqlParameters @($imageParams)
                        $imageId = $result |
                            Microsoft.PowerShell.Utility\Select-Object -ExpandProperty newId

                        $lookupQueries.Clear()
                        $lookupParams.Clear()

                        # insert keywords if present
                        if ($image.keywords -and $image.keywords.Count -gt 0) {
                            foreach ($keyword in $image.keywords) {
                                $lookupQueries.Add("INSERT INTO ImageKeywords (image_id, keyword) VALUES (@image_id, @keyword)")
                                $lookupParams.Add(@{ "image_id" = $imageId; "keyword" = $keyword })
                            }
                        }

                        # insert people (parse faces string if needed)
                        if ($image.people -and $image.people.faces -and $image.people.faces -ne "" -and $image.people.faces.Count -gt 0) {
                            $people = if ($image.people.faces -is [array]) {
                                $image.people.faces
                            } else {
                                $image.people.faces -split ',' |
                                    Microsoft.PowerShell.Core\ForEach-Object { $_.Trim() } |
                                    Microsoft.PowerShell.Core\Where-Object { $_ -ne "" }
                            }
                            foreach ($person in $people) {
                                $lookupQueries.Add("INSERT INTO ImagePeople (image_id, person_name) VALUES (@image_id, @person_name)")
                                $lookupParams.Add(@{ "image_id" = $imageId; "person_name" = $person })
                            }
                        }

                        # insert objects (parse objects string if needed)
                        if ($image.objects -and $image.objects.objects -and $image.objects.objects -ne "" -and $image.objects.objects.Count -gt 0) {
                            $objects = if ($image.objects.objects -is [array]) {
                                $image.objects.objects
                            } else {
                                $image.objects.objects -split ',' |
                                    Microsoft.PowerShell.Core\ForEach-Object { $_.Trim() } |
                                    Microsoft.PowerShell.Core\Where-Object { $_ -ne "" }
                            }
                            foreach ($obj in $objects) {
                                $lookupQueries.Add("INSERT INTO ImageObjects (image_id, object_name) VALUES (@image_id, @object_name)")
                                $lookupParams.Add(@{ "image_id" = $imageId; "object_name" = $obj })
                            }
                        }

                        # insert scenes if present
                        if ($image.scenes -and (-not [string]::IsNullOrWhiteSpace($image.scenes.label))) {

                            $lookupQueries.Add("INSERT INTO ImageScenes (image_id, scene_name, confidence) VALUES (@image_id, @scene_name, @confidence)")
                            $lookupParams.Add(@{ "image_id" = $imageId; "scene_name" = $image.scenes.label; "confidence" = $image.scenes.confidence })
                        }

                        # execute all lookup inserts in a single batch
                        if ($lookupQueries.Count -gt 0) {
                            GenXdev.Data\Invoke-SQLiteQuery -Transaction $transaction -Queries $lookupQueries -SqlParameters $lookupParams
                            Microsoft.PowerShell.Utility\Write-Verbose (
                                "Inserted lookup data for image ID $imageId"
                            )
                        } else {
                            Microsoft.PowerShell.Utility\Write-Verbose (
                                "No lookup data to insert for image ID $imageId"
                            )
                        }

                        # progress update every 100 images
                        if ($Info.TotalImages % 100 -eq 0) {
                            Microsoft.PowerShell.Utility\Write-Verbose (
                                "Processed $($Info.TotalImages) images..."
                            )
                        }

                        # output the image object if PassThru is enabled
                        if ($PassThru) {
                            $image |

                            Microsoft.PowerShell.Utility\Write-Output
                        }
                    }
                    catch {
                        Microsoft.PowerShell.Utility\Write-Warning (
                            "Failed to insert image data for $($image.path): $($_.Exception.Message)"
                        )
                    }
                }
            }

            # process input object if provided
            if ($InputObject -and $InputObject.Count -gt 0) {

                Microsoft.PowerShell.Utility\Write-Verbose (
                    "Processing input object with $($InputObject.Count) images..."
                )

                $InputObject |
                    Microsoft.PowerShell.Core\ForEach-Object {
                        $Info.FoundResults = $true
                        insertImage $PSItem $Info
                    }
            }
            else {
                GenXdev.AI\Find-Image @findImageParams |
                    Microsoft.PowerShell.Core\ForEach-Object {
                        $Info.FoundResults = $true
                        insertImage $PSItem $Info
                    }
            }
        }

        try {

            try {

                ImportImages $Info

                # if no results found, update all image metadata and retry
                if (-not $Info.FoundResults) {

                    $params = GenXdev.Helpers\Copy-IdenticalParamValues `
                        -BoundParameters $PSBoundParameters `
                        -FunctionName "GenXdev.AI\Update-AllImageMetaData" `
                        -DefaultValues (Microsoft.PowerShell.Utility\Get-Variable -Scope Local -ErrorAction SilentlyContinue)

                    $null = GenXdev.AI\Update-AllImageMetaData @params

                    ImportImages $Info
                }

                # commit the transaction
                $transaction.Commit()
                Microsoft.PowerShell.Utility\Write-Verbose (
                    "Transaction committed successfully with $($Info.TotalImages)) images"
                )

            }
            catch {
                # rollback on error
                $transaction.Rollback()
                Microsoft.PowerShell.Utility\Write-Warning (
                    "Transaction rolled back due to error: $($_.Exception.Message)"
                )
                throw $_
            }

            # create indexes for optimal performance (outside transaction for better performance)
            Microsoft.PowerShell.Utility\Write-Verbose (
                "Creating indexes for optimal performance..."
            )
            GenXdev.Data\Invoke-SQLiteQuery -DatabaseFilePath $DatabaseFilePath -Queries $createIndexes

            # Run ANALYZE to update query optimizer statistics for best performance
            Microsoft.PowerShell.Utility\Write-Verbose (
                "Running ANALYZE to optimize query planning..."
            )
            GenXdev.Data\Invoke-SQLiteQuery -DatabaseFilePath $DatabaseFilePath -Queries "ANALYZE;"

            # insert schema version
            Microsoft.PowerShell.Utility\Write-Verbose (
                "Setting schema version to $SCHEMA_VERSION..."
            )

            GenXdev.Data\Invoke-SQLiteQuery -DatabaseFilePath $DatabaseFilePath -Queries "INSERT OR REPLACE INTO ImageSchemaVersion (id, version) VALUES (1, @version)" -SqlParameters @{ "version" = $SCHEMA_VERSION }


            # output image database stats if PassThru is not enabled
            if (-not $PassThru) {

                $params = GenXdev.Helpers\Copy-IdenticalParamValues `
                    -BoundParameters $PSBoundParameters `
                    -FunctionName "GenXdev.AI\Get-ImageDatabaseStats" `
                    -DefaultValues (Microsoft.PowerShell.Utility\Get-Variable -Scope Local -ErrorAction SilentlyContinue)

                GenXdev.AI\Get-ImageDatabaseStats @params |
                    Microsoft.PowerShell.Utility\Write-Output
            }

            # stop the stopwatch and output completion message
            $totalTime.Stop()
            Microsoft.PowerShell.Utility\Write-Verbose (
                "Image database initialization completed successfully with $($Info.TotalImages) records in $($totalTime.Elapsed.ToString('mm\:ss'))"
            )
        }
        finally {
            # always close the connection
            if ($transaction -and $transaction.Connection) {
                $transaction.Connection.Close()
            }
        }
    }

    end {
    }
}
################################################################################