<?php

namespace ShopManagerPro\Images;

class ImageRepository {
	public static function getUploadedImages() {
		global $wpdb;
		$query = "
			WITH
			meta_fields AS (
				SELECT
					wp_postmeta.post_id , JSON_OBJECTAGG(wp_postmeta.meta_key, wp_postmeta.meta_value) AS meta_fields
				FROM
					{$wpdb->postmeta} AS wp_postmeta
				GROUP BY
					wp_postmeta.post_id
			)
            SELECT
                wp_posts.id AS post_id ,
				wp_posts.post_date_gmt AS post_date_gmt ,
				wp_posts.post_modified_gmt AS post_modified_gmt ,
                JSON_UNQUOTE(JSON_EXTRACT(meta_fields.meta_fields, '$._wp_attached_file')) AS attached_file ,
                JSON_UNQUOTE(JSON_EXTRACT(meta_fields.meta_fields, '$._wp_attachment_metadata')) AS meta_data
            FROM
                {$wpdb->posts} AS wp_posts
            INNER JOIN
                meta_fields ON meta_fields.post_id = wp_posts.id
            WHERE
                wp_posts.post_type = 'attachment' AND
				wp_posts.post_mime_type LIKE 'image/%'
            GROUP BY
                wp_posts.id
             ";
		$images = [];
		foreach ($wpdb->get_results($query) as $row) {
			$id = intval($row->post_id);
			$baseName = dirname($row->attached_file);
			if ('.' === $baseName) {
				$baseName = '';
			} else {
				$baseName .= '/';
			} $metaData = unserialize($row->meta_data);
			if (!is_array($metaData)) {
				\ShopManagerPro\Shared\Utils\Logger::error('Invalid metadata for attachment - not an array', ['attachment_id' => $id, 'metadata' => $row->meta_data]);
				continue;
			} if (!isset($metaData['file'])) {
				\ShopManagerPro\Shared\Utils\Logger::error('Missing essential metadata fields for attachment', ['attachment_id' => $id, 'metadata' => $metaData]);
				continue;
			} $images[$id] = ['original' => new DTO\Image(filename: $metaData['file'], fileSize: $metaData['filesize'] ?? null, width: $metaData['width'] ?? null, height: $metaData['height'] ?? null, createdAt: (new \DateTime($row->post_date_gmt, new \DateTimeZone('GMT')))->format(\DateTime::ATOM), modifiedAt: (new \DateTime($row->post_modified_gmt, new \DateTimeZone('GMT')))->format(\DateTime::ATOM))];
			if (!isset($metaData['sizes']) || !is_array($metaData['sizes'])) {
				\ShopManagerPro\Shared\Utils\Logger::error('No sizes defined in metadata for attachment', ['attachment_id' => $id, 'metadata' => $metaData]);
				continue;
			} foreach ($metaData['sizes'] as $sizeName => $subMetaData) {
				if (!isset($subMetaData['file'])) {
					\ShopManagerPro\Shared\Utils\Logger::error('Missing essential fields for image size', ['size' => $sizeName, 'attachment_id' => $id, 'metadata' => $subMetaData]);
					continue;
				} $images[$id][$sizeName] = new DTO\Image(filename: $baseName.$subMetaData['file'], fileSize: $subMetaData['filesize'] ?? null, width: $subMetaData['width'], height: $subMetaData['height'], createdAt: (new \DateTime($row->post_date_gmt, new \DateTimeZone('GMT')))->format(\DateTime::ATOM), modifiedAt: (new \DateTime($row->post_modified_gmt, new \DateTimeZone('GMT')))->format(\DateTime::ATOM));
			}
		}

return $images;
	}
}
