<?php
    if ( ! defined( 'ABSPATH' ) ) exit;

    class CloudS3StorageDB {

        private static $table_name;

        public function __construct() {
            global $wpdb;

            self::$table_name = $wpdb->prefix . 'cloud_s3_storage_url_linked';

            $charset_collate = $wpdb->get_charset_collate();
            $table_name = self::$table_name;

            $sql = "CREATE TABLE IF NOT EXISTS $table_name (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                image_id bigint(20) unsigned NULL,
                path varchar(255) NOT NULL,
                s3_status varchar(20) NOT NULL,
                last_update datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
                PRIMARY KEY  (id),
                UNIQUE KEY path (path)
            ) $charset_collate;";

            require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
            dbDelta( $sql );

            $column_exists = $wpdb->get_var( "SHOW COLUMNS FROM $table_name LIKE 'last_update'" );
            if ( ! $column_exists ) {
                $wpdb->query( "ALTER TABLE $table_name ADD last_update DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL" );
                error_log("[cloud-s3-storage] Colonna 'last_update' aggiunta automaticamente alla tabella.");
            }

            $image_id_unique = $wpdb->get_var(
                $wpdb->prepare(
                    "SHOW INDEX FROM $table_name WHERE Column_name = %s AND Non_unique = 0",
                    'image_id'
                )
            );

            if (  $image_id_unique  ) {
                // 🔧 Rimuove l'indice UNIQUE su image_id
                $wpdb->query( "ALTER TABLE $table_name DROP INDEX image_id" );
                error_log("[cloud-s3-storage] Indice UNIQUE su 'image_id' rimosso.");
            }

            $this->check_and_insert_all_images();
        }

        public static function getMissingImagesPercentage() {
            global $wpdb;
            
            $images = cloudS3StorageImageFunction::getFileAttachFullVersion();

            if (!empty($images)) {
                $table_name = self::$table_name;

                $existing_images = $wpdb->get_col("SELECT path FROM $table_name");

                $missing_images = array_filter($images, function($image) use ($existing_images) {
                    return !in_array($image['url'], $existing_images);
                });

                $total_images = count($images);
                $missing_count = count($missing_images);
                $missing_percentage = ($total_images > 0) ? ($missing_count / $total_images) * 100 : 0;

                return $missing_percentage;
            }

            return 0;
        }

        public function check_and_insert_all_images($max_missing_images = 10) {
            global $wpdb;

            $images = cloudS3StorageImageFunction::getFileAttachFullVersion();
        
            if (!empty($images)) {
                $table_name = self::$table_name;
        
                $existing_images = $wpdb->get_col("SELECT path FROM $table_name");
        
                $image_urls = array_column($images, 'url');
        
                $missing_images = array_filter($images, function($image) use ($existing_images) {
                    return !in_array($image['url'], $existing_images);
                });
        
                if (empty($missing_images)) {
                    return;
                }

                $total_images = count($images);
                $missing_count = count($missing_images);
                $missing_percentage = ($total_images > 0) ? ($missing_count / $total_images) * 100 : 0;

                if ($max_missing_images > 0 && $missing_count > $max_missing_images) {
                    $missing_images = array_slice($missing_images, 0, $max_missing_images);
                }

                foreach ($missing_images as $image) {
                    if (!isset($image['image_id'])) {
                        error_log("[cloud-s3-storage] image_id mancante per URL: " . $image['url']);
                        continue;
                    }
                    $image_id = isset($image["image_id"]) ? $image["image_id"] : null;
                    $image_url = $image["url"];
        
                    $wpdb->insert(
                        $table_name,
                        array(
                            'image_id'     => $image_id,
                            's3_status'    => "initial",
                            'path'         => $image_url,
                            'last_update'  => current_time('mysql')
                        ),
                        array(
                            '%d',
                            '%s',
                            '%s',
                            '%s'
                        )
                    );
                }
            }
        }
        
        public static function path_check($path) {
            $matches = [];
            
            if (preg_match('/(\d{4})\/(\d{2})\/([^\/]+)$/', $path, $matches)) {
                return $matches[1] . '/' . $matches[2] . '/' . $matches[3];
            }
        
            return $path;
        }

        public static function get_transferred_images_for_s3() {
            global $wpdb;
            $table_name = self::$table_name;

            $images = $wpdb->get_results(
                "SELECT path FROM $table_name WHERE s3_status = 'transferred'"
            );

            $formatted_images = [];
            foreach ($images as $image) {
                $formatted_images[] = [
                    'Key' => $image->path
                ];
            }

            return $formatted_images;
        }
        
        public static function set_image_status($path, $s3_status, $image_id = null) {
            global $wpdb;
            $table_name = self::$table_name;
        
            $existing_image = $wpdb->get_row(
                $wpdb->prepare(
                    "SELECT * FROM $table_name WHERE path = %s",
                    self::path_check($path)
                )
            );
        
            if ($existing_image) {
                $result = $wpdb->update(
                    $table_name,
                    array(
                        's3_status'    => $s3_status,
                        'last_update'  => current_time('mysql')
                    ),
                    array('path' => self::path_check($path)),
                    array('%s', '%s'),
                    array('%s')
                );
            } else {
                $result = $wpdb->insert(
                    $table_name,
                    array(
                        'image_id'     => $image_id,
                        'path'         => self::path_check($path),
                        's3_status'    => $s3_status,
                        'last_update'  => current_time('mysql')
                    ),
                    array('%d', '%s', '%s', '%s')
                );
            }        
        }    

        public static function get_image_status_by_path($path) {
            global $wpdb;
            $table_name = self::$table_name;

            $status = $wpdb->get_var($wpdb->prepare(
                "SELECT s3_status FROM $table_name WHERE path = %s",
                self::path_check($path)
            ));

            return $status ? $status : 'not_found';
        }

        public static function get_image_status_by_id($image_id) {
            global $wpdb;
            $table_name = self::$table_name;

            $status = $wpdb->get_var($wpdb->prepare(
                "SELECT s3_status FROM $table_name WHERE image_id = %d",
                $image_id
            ));

            return $status ? $status : 'not_found';
        }

        public static function get_image_status($image_id = null, $path=null) {
            if ($image_id) return self::get_image_status_by_id($image_id);
            if ($path) return self::get_image_status_by_path($path);

            return 'not_found';
        }

        public static function get_untransferred_images() {
            global $wpdb;
            $table_name = self::$table_name;

            $images = $wpdb->get_results(
                "SELECT image_id, path FROM $table_name WHERE s3_status != 'transferred'"
            );

            return $images;
        }

        public static function remove_image_record($image_id, $path) {
            if ($image_id) self::remove_image_record_by_id($image_id);
            if ($path) self::remove_image_record_by_path($path);
        }

        public static function remove_image_record_by_path($path) {
            global $wpdb;
            $table_name = self::$table_name;

            $wpdb->update(
                $table_name,
                array('s3_status' => 'removed'),
                array('path' => self::path_check($path)),
                array('%s'),
                array('%s')
            );
        }

        public static function remove_image_record_by_id($image_id) {
            global $wpdb;
            $table_name = self::$table_name;

            $wpdb->update(
                $table_name,
                array('s3_status' => 'removed'),
                array('image_id' => $image_id),
                array('%s'),
                array('%d')
            );
        }

        public static function delete_image_record($image_id, $path) {
            if ($image_id) self::delete_image_record_by_id($image_id);
            if ($path) self::delete_image_record_by_path($path);
        }

        public static function delete_image_record_by_path($path) {
            global $wpdb;
            $table_name = self::$table_name;

            $wpdb->delete(
                $table_name,
                array('path' => self::path_check($path)),
                array('%s')
            );
        }

        public static function delete_image_record_by_id($image_id) {
            global $wpdb;
            $table_name = self::$table_name;

            $wpdb->delete(
                $table_name,
                array('image_id' => $image_id),
                array('%d')
            );
        }
        
    }
