#!/bin/bash

# Function to retrieve creation date of a file
get_creation_date() {
    stat -c %y "$1" | cut -d' ' -f1
}

# Function to extract audio ID from filename
get_audio_id() {
    filename=$(basename -s .png "$1")
    echo "$filename" | cut -d'-' -f1
}

# Directory containing PNG files
directory="./restart/basic/img/"

# SQL drop table definition
sql_drop_table="DROP TABLE IF EXISTS png_files;"

# SQL table definition
sql_table="CREATE TABLE png_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    path VARCHAR(255),
    filename VARCHAR(255),
    creation_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    audio_id VARCHAR(255)
);"

echo "$sql_drop_table"
echo "$sql_table"

# Iterate over PNG files in the directory, sorted numerically
for file in $(ls -v "$directory"/*.png); do
    filename=$(basename "$file")
    creation_date=$(get_creation_date "$file")
    audio_id=$(get_audio_id "$filename")
    echo "INSERT INTO png_files (path, filename, audio_id) VALUES ('$directory', '$filename', '$audio_id');"
done