<?php
/**
 * Folderra Media Organizer (FREE)
 *
 * @package Folderra_Smart_Folder_Organizer
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

if ( ! defined( 'FOLD82SM_MEDIA_ORGANIZER_DB_VERSION' ) ) {
    define( 'FOLD82SM_MEDIA_ORGANIZER_DB_VERSION', '1.0.0' );
}

if ( ! defined( 'FOLD82SM_MEDIA_QUARANTINE_META' ) ) {
    define( 'FOLD82SM_MEDIA_QUARANTINE_META', 'folderra_quarantine' );
}

if ( ! defined( 'FOLD82SM_MEDIA_UID_META' ) ) {
    define( 'FOLD82SM_MEDIA_UID_META', 'folderra_uid' );
}

class Folderra_Media_Organizer {

    /**
     * Bootstrap.
     *
     * @return void
     */
    public static function init() {
        add_action( 'wp_after_insert_post', array( __CLASS__, 'on_post_save' ), 10, 3 );
        add_action( 'add_attachment', array( __CLASS__, 'on_add_attachment' ), 10 );
        add_action( 'delete_attachment', array( __CLASS__, 'on_delete_attachment' ), 10 );

        // AJAX.
        add_action( 'wp_ajax_fold82sm_media_scan_batch', array( __CLASS__, 'ajax_scan_batch' ) );
        add_action( 'wp_ajax_fold82sm_media_deep_scan_batch', array( __CLASS__, 'ajax_deep_scan_batch' ) );
        add_action( 'wp_ajax_fold82sm_media_hash_batch', array( __CLASS__, 'ajax_hash_batch' ) );
        add_action( 'wp_ajax_fold82sm_media_get_tab', array( __CLASS__, 'ajax_get_tab' ) );
        add_action( 'wp_ajax_fold82sm_media_apply_action', array( __CLASS__, 'ajax_apply_action' ) );
        add_action( 'wp_ajax_fold82sm_media_undo_batch', array( __CLASS__, 'ajax_undo_batch' ) );
        add_action( 'wp_ajax_fold82sm_media_where_used', array( __CLASS__, 'ajax_where_used' ) );

        // Admin assets for the Media Organizer page (wp-pointer tooltip).
        add_action( 'admin_enqueue_scripts', array( __CLASS__, 'enqueue_admin_assets' ) );
    }

    /**
     * Create or update Media Organizer tables.
     *
     * @return void
     */
    public static function install_tables() {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset_collate = $wpdb->get_charset_collate();

        $usage_table   = $wpdb->prefix . 'folderra_media_usage';
        $files_table   = $wpdb->prefix . 'folderra_media_files';
        $actions_table = $wpdb->prefix . 'folderra_media_actions';

        // Usage table: attachment <-> post relations.
        $sql_usage = "CREATE TABLE {$usage_table} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            attachment_id BIGINT(20) UNSIGNED NOT NULL,
            post_id BIGINT(20) UNSIGNED NOT NULL,
            method VARCHAR(32) NOT NULL DEFAULT 'content_url',
            last_seen DATETIME NOT NULL,
            PRIMARY KEY  (id),
            KEY attachment_id (attachment_id),
            KEY post_id (post_id),
            KEY method (method)
        ) {$charset_collate};";

        // Files table: hashes and file metadata.
        $sql_files = "CREATE TABLE {$files_table} (
            attachment_id BIGINT(20) UNSIGNED NOT NULL,
            file_hash CHAR(40) NULL,
            file_size BIGINT(20) UNSIGNED NULL,
            base_filename VARCHAR(255) NULL,
            last_hashed DATETIME NULL,
            PRIMARY KEY  (attachment_id),
            KEY file_hash (file_hash)
        ) {$charset_collate};";

        // Actions table: restore points for bulk operations.
        $sql_actions = "CREATE TABLE {$actions_table} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            batch_id CHAR(36) NOT NULL,
            action VARCHAR(20) NOT NULL,
            attachment_ids LONGTEXT NOT NULL,
            user_id BIGINT(20) UNSIGNED NOT NULL,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY batch_id (batch_id),
            KEY action (action),
            KEY user_id (user_id)
        ) {$charset_collate};";

        dbDelta( $sql_usage );
        dbDelta( $sql_files );
        dbDelta( $sql_actions );

        update_option( 'fold82sm_media_organizer_db_version', FOLD82SM_MEDIA_ORGANIZER_DB_VERSION );
    }

    /**
     * Render Media Organizer page.
     *
     * @return void
     */
    public static function render_page() {
        if ( ! function_exists( 'fold82sm_current_user_can_access' ) || ! fold82sm_current_user_can_access() ) {
            wp_die( esc_html__( 'You do not have permission to access Folderra Smart Folder.', 'folderra-smart-folder-organizer' ) );
        }

        if ( function_exists( 'fold82sm_navigation' ) ) {
            fold82sm_navigation( 'media-organizer' );
        }

        ?>
<div class="wrap">
  <div class="special-folder-container">

    <div class="special-folder-header">
      <div class="header-title-section">
        <h1>
  🗂️ <?php echo esc_html__( 'Media Organizer', 'folderra-smart-folder-organizer' ); ?>

  <button
    type="button"
    id="fold82sm-media-help"
    class="button-link"
    style="margin-left:6px; vertical-align:middle; text-decoration:none;"
    aria-label="<?php echo esc_attr__( 'Help: how to use Media Organizer', 'folderra-smart-folder-organizer' ); ?>"
  >
    <span class="dashicons dashicons-info-outline" aria-hidden="true"></span>
  </button>
</h1>
        <p class="header-subtitle">
          <?php echo esc_html__( 'Find where your media files are used, detect duplicates, and safely clean up your library.', 'folderra-smart-folder-organizer' ); ?>
        </p>
      </div>

      <div class="folder-info-bar">
        <span class="folder-info-item">
          <span class="dashicons dashicons-admin-media"></span>
          <strong id="fold82sm-media-total-count">—</strong>
          <?php echo esc_html__( 'Files', 'folderra-smart-folder-organizer' ); ?>
        </span>

        <button type="button" class="button button-primary" id="fold82sm-media-start-scan">
          <?php echo esc_html__( 'Start Scan', 'folderra-smart-folder-organizer' ); ?>
        </button>

        <button type="button" class="button" id="fold82sm-media-deep-scan">
          <?php echo esc_html__( 'Deep Scan (Public Pages)', 'folderra-smart-folder-organizer' ); ?>
        </button>

        <button type="button" class="button" id="fold82sm-media-compute-hashes">
          <?php echo esc_html__( 'Compute Hashes', 'folderra-smart-folder-organizer' ); ?>
        </button>

        <label class="folder-info-item" style="margin-left:auto;">
          <input type="checkbox" id="fold82sm-media-safe-mode" checked="checked" />
          <span><?php echo esc_html__( 'Safe mode', 'folderra-smart-folder-organizer' ); ?></span>
        </label>
      </div>
    </div>

    <div id="fold82sm-media-progress" style="display:none; margin: 10px 0; padding: 12px; background:#fff; border:1px solid #ccd0d4; border-radius:4px;">
      <div style="margin-bottom:8px; font-weight:600;" id="fold82sm-media-progress-text"></div>
      <div style="height:10px; background:#f0f0f1; border-radius:999px; overflow:hidden;">
        <div id="fold82sm-media-progress-bar" style="height:10px; width:0%; background:#0073aa;"></div>
      </div>
    </div>

    <h2 class="nav-tab-wrapper" style="margin-top: 20px;">
      <a href="#" class="nav-tab nav-tab-active" data-tab="used"><?php echo esc_html__( 'Used', 'folderra-smart-folder-organizer' ); ?></a>
      <a href="#" class="nav-tab" data-tab="unused"><?php echo esc_html__( 'Unused (Detected)', 'folderra-smart-folder-organizer' ); ?></a>
      <a href="#" class="nav-tab" data-tab="duplicates"><?php echo esc_html__( 'Duplicates', 'folderra-smart-folder-organizer' ); ?></a>
      <a href="#" class="nav-tab" data-tab="quarantine"><?php echo esc_html__( 'Quarantine', 'folderra-smart-folder-organizer' ); ?></a>
      <a href="#" class="nav-tab" data-tab="restore"><?php echo esc_html__( 'Restore Points', 'folderra-smart-folder-organizer' ); ?></a>
    </h2>
    
    <div class="special-folder-container" style="margin:15px 0;">
  <div style="
    background:#ffffff;
    border:1px solid #e2e4e7;
    border-radius:8px;
    padding:16px 18px;
    display:flex;
    align-items:flex-start;
    gap:14px;
  ">

    <span class="dashicons dashicons-category" style="
      font-size:22px;
      color:#2271b1;
      margin-top:2px;
    "></span>

    <div style="flex:1;">

      <div style="
        font-weight:600;
        font-size:14px;
        margin-bottom:6px;
      ">
        <?php echo esc_html__( 'Want to organize media into folders?', 'folderra-smart-folder-organizer' ); ?>
      </div>

      <div style="
        color:#50575e;
        font-size:13px;
        margin-bottom:12px;
        max-width:900px;
      ">
        <?php echo esc_html__( 'Folderra PRO includes modern drag-and-drop media folders and subfolders with a streamlined interface built for efficient workflows.', 'folderra-smart-folder-organizer' ); ?>
      </div>

      <a class="button button-primary"
         href="<?php echo esc_url( 'https://wpsmartfolder.com/pro/' ); ?>"
         target="_blank"
         rel="noopener">
        <?php echo esc_html__( 'Explore Folderra PRO', 'folderra-smart-folder-organizer' ); ?>
      </a>

    </div>

  </div>
</div>

    <div id="fold82sm-media-tab-content" style="margin-top: 15px;">
      <div class="no-content-message">
        <?php echo esc_html__( 'Loading...', 'folderra-smart-folder-organizer' ); ?>
      </div>
    </div>

  </div>
</div>
<?php

    }

    /**
     * Ensure each attachment has a Folderra UID.
     *
     * @param int $attachment_id Attachment ID.
     * @return string
     */
    private static function get_or_create_uid( $attachment_id ) {
        $attachment_id = absint( $attachment_id );
        if ( ! $attachment_id ) {
            return '';
        }

        $uid = get_post_meta( $attachment_id, FOLD82SM_MEDIA_UID_META, true );
        $uid = is_string( $uid ) ? sanitize_text_field( $uid ) : '';

        if ( '' === $uid ) {
            $uid = wp_generate_uuid4();
            update_post_meta( $attachment_id, FOLD82SM_MEDIA_UID_META, $uid );
        }

        return $uid;
    }

    /**
     * Normalize uploads URL to relative path compatible with _wp_attached_file.
     *
     * @param string $url URL.
     * @return string
     */
    private static function normalize_uploads_relative_path( $url ) {
        $url = is_string( $url ) ? trim( $url ) : '';
        if ( '' === $url ) {
            return '';
        }

        $url = preg_replace( '/[?#].*$/', '', $url );

        $uploads = wp_upload_dir();
        $baseurl = isset( $uploads['baseurl'] ) ? (string) $uploads['baseurl'] : '';
        $baseurl = rtrim( $baseurl, '/' );

        if ( $baseurl && 0 === strpos( $url, $baseurl ) ) {
            $path = substr( $url, strlen( $baseurl ) );
            return ltrim( (string) $path, '/' );
        }

        $parts = wp_parse_url( $url );
        if ( ! is_array( $parts ) || empty( $parts['path'] ) ) {
            return '';
        }

        $p = (string) $parts['path'];
        $needle = '/wp-content/uploads/';
        $pos = strpos( $p, $needle );
        if ( false === $pos ) {
            return '';
        }

        return ltrim( substr( $p, $pos + strlen( $needle ) ), '/' );
    }

    /**
     * Resolve uploads URL to attachment ID.
     *
     * @param string $url URL.
     * @return int
     */
    private static function url_to_attachment_id( $url ) {
        global $wpdb;

        $rel = self::normalize_uploads_relative_path( $url );
        if ( '' === $rel ) {
            return 0;
        }

        $post_id = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = '_wp_attached_file' AND meta_value = %s LIMIT 1",
                $rel
            )
        );

        if ( $post_id > 0 ) {
            return $post_id;
        }

        $rel2 = preg_replace( '/-\d+x\d+(?=\.[a-zA-Z0-9]+$)/', '', $rel );
        if ( $rel2 && $rel2 !== $rel ) {
            $post_id = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = '_wp_attached_file' AND meta_value = %s LIMIT 1",
                    $rel2
                )
            );
            if ( $post_id > 0 ) {
                return $post_id;
            }
        }

        return 0;
    }

    /**
     * Extract IDs from content.
     *
     * @param string $content Content.
     * @return int[]
     */
    private static function extract_attachment_ids_from_content( $content ) {
        $content = is_string( $content ) ? $content : '';
        if ( '' === $content ) {
            return array();
        }

        $ids = array();

        if ( preg_match_all( '/wp-image-(\d+)/', $content, $m ) ) {
            foreach ( $m[1] as $id ) {
                $ids[] = absint( $id );
            }
        }

        if ( preg_match_all( '/\[gallery[^\]]*ids=\"([0-9,\s]+)\"[^\]]*\]/', $content, $g ) ) {
            foreach ( $g[1] as $list ) {
                $parts = array_filter( array_map( 'trim', explode( ',', $list ) ) );
                foreach ( $parts as $id ) {
                    $ids[] = absint( $id );
                }
            }
        }

        return array_values( array_filter( array_unique( array_map( 'absint', $ids ) ) ) );
    }

    /**
     * Extract uploads URLs from content.
     *
     * @param string $content Content.
     * @return string[]
     */
    private static function extract_uploads_urls_from_content( $content ) {
        $content = is_string( $content ) ? $content : '';
        if ( '' === $content ) {
            return array();
        }

        $urls = array();

        if ( preg_match_all( '#https?://[^"\'>\s]+#i', $content, $m ) ) {
            foreach ( $m[0] as $url ) {
                if ( false !== strpos( $url, '/wp-content/uploads/' ) ) {
                    $urls[] = $url;
                }
            }
        }

        if ( preg_match_all( '#/wp-content/uploads/[^"\'>\s]+#i', $content, $m2 ) ) {
            foreach ( $m2[0] as $u ) {
                $urls[] = $u;
            }
        }

        return array_unique( array_map( 'trim', $urls ) );
    }

    /**
     * Rebuild usage for a post.
     *
     * @param int $post_id Post ID.
     * @return void
     */
    private static function rebuild_usage_for_post( $post_id ) {
        global $wpdb;

        $post_id = absint( $post_id );
        if ( ! $post_id ) {
            return;
        }

        $post = get_post( $post_id );
        if ( ! $post || ! isset( $post->post_content ) ) {
            return;
        }

        $usage_table = $wpdb->prefix . 'folderra_media_usage';

        // Replace relations for this post.
        $wpdb->delete( $usage_table, array( 'post_id' => $post_id ), array( '%d' ) );

        $now = current_time( 'mysql' );
        $found = array();

        $thumb_id = get_post_thumbnail_id( $post_id );
        if ( $thumb_id ) {
            $found[ absint( $thumb_id ) ] = 'featured';
        }

        $ids = self::extract_attachment_ids_from_content( (string) $post->post_content );
        foreach ( $ids as $id ) {
            if ( $id ) {
                $found[ $id ] = 'content_id';
            }
        }

        $urls = self::extract_uploads_urls_from_content( (string) $post->post_content );
        foreach ( $urls as $url ) {
            $aid = self::url_to_attachment_id( $url );
            if ( $aid && ! isset( $found[ $aid ] ) ) {
                $found[ $aid ] = 'content_url';
            }
        }

        foreach ( $found as $attachment_id => $method ) {
            $attachment_id = absint( $attachment_id );
            if ( ! $attachment_id ) {
                continue;
            }

            self::get_or_create_uid( $attachment_id );

            $wpdb->insert(
                $usage_table,
                array(
                    'attachment_id' => $attachment_id,
                    'post_id'       => $post_id,
                    'method'        => sanitize_key( $method ),
                    'last_seen'     => $now,
                ),
                array( '%d', '%d', '%s', '%s' )
            );
        }
    }

    /**
     * Hook: post save.
     *
     * @param int     $post_id Post ID.
     * @param WP_Post $post Post.
     * @param bool    $update Update.
     * @return void
     */
    public static function on_post_save( $post_id, $post, $update ) {
        $post_id = absint( $post_id );
        if ( ! $post_id ) {
            return;
        }

        if ( wp_is_post_autosave( $post_id ) || wp_is_post_revision( $post_id ) ) {
            return;
        }

        if ( ! isset( $post->post_status ) || 'auto-draft' === $post->post_status ) {
            return;
        }

        $allowed_types = array( 'post', 'page' );
        if ( ! isset( $post->post_type ) || ! in_array( $post->post_type, $allowed_types, true ) ) {
            return;
        }

        self::install_tables();
        self::rebuild_usage_for_post( $post_id );
    }

    /**
     * Hook: add attachment.
     *
     * @param int $post_id Attachment ID.
     * @return void
     */
    public static function on_add_attachment( $post_id ) {
        global $wpdb;

        $post_id = absint( $post_id );
        if ( ! $post_id ) {
            return;
        }

        self::install_tables();
        self::get_or_create_uid( $post_id );

        $files_table = $wpdb->prefix . 'folderra_media_files';

        $exists = (int) $wpdb->get_var(
            $wpdb->prepare( "SELECT COUNT(1) FROM {$files_table} WHERE attachment_id = %d", $post_id )
        );

        if ( 0 === $exists ) {
            $wpdb->insert(
                $files_table,
                array(
                    'attachment_id' => $post_id,
                    'file_hash'     => null,
                    'file_size'     => null,
                    'base_filename' => null,
                    'last_hashed'   => null,
                ),
                array( '%d', '%s', '%d', '%s', '%s' )
            );
        }
    }

    /**
     * Hook: delete attachment.
     *
     * @param int $post_id Attachment ID.
     * @return void
     */
    public static function on_delete_attachment( $post_id ) {
        global $wpdb;

        $post_id = absint( $post_id );
        if ( ! $post_id ) {
            return;
        }

        $usage_table = $wpdb->prefix . 'folderra_media_usage';
        $files_table = $wpdb->prefix . 'folderra_media_files';

        $wpdb->delete( $usage_table, array( 'attachment_id' => $post_id ), array( '%d' ) );
        $wpdb->delete( $files_table, array( 'attachment_id' => $post_id ), array( '%d' ) );

        delete_post_meta( $post_id, FOLD82SM_MEDIA_QUARANTINE_META );
        delete_post_meta( $post_id, FOLD82SM_MEDIA_UID_META );
    }

    /**
     * Guard for AJAX.
     *
     * @return void
     */
    private static function ajax_guard() {
        check_ajax_referer( 'fold82sm_media_organizer', 'nonce' );

        if ( ! current_user_can( 'fold82sm_access' ) ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Permission denied.', 'folderra-smart-folder-organizer' ) ), 403 );
        }

        self::install_tables();
    }

    /**
     * AJAX: scan batch.
     *
     * @return void
     */
    public static function ajax_scan_batch() {
        self::ajax_guard();

        $offset   = isset( $_POST['offset'] ) ? absint( $_POST['offset'] ) : 0;
        $per_page = isset( $_POST['per_page'] ) ? absint( $_POST['per_page'] ) : 20;
        $per_page = $per_page > 0 && $per_page <= 100 ? $per_page : 20;

        $q = new WP_Query(
            array(
                'post_type'              => array( 'post', 'page' ),
                'post_status'            => array( 'publish', 'private', 'draft' ),
                'posts_per_page'         => $per_page,
                'offset'                 => $offset,
                'fields'                 => 'ids',
                'no_found_rows'          => false,
                'update_post_meta_cache' => false,
                'update_post_term_cache' => false,
            )
        );

        $total = (int) $q->found_posts;
        $ids   = is_array( $q->posts ) ? array_map( 'absint', $q->posts ) : array();

        foreach ( $ids as $post_id ) {
            self::rebuild_usage_for_post( $post_id );
        }

        $next_offset = $offset + count( $ids );
        $done        = ( $next_offset >= $total ) || 0 === count( $ids );

        wp_send_json_success(
            array(
                'total'       => $total,
                'processed'   => min( $next_offset, $total ),
                'next_offset' => $next_offset,
                'done'        => $done,
            )
        );
    }

    /**
     * AJAX: deep scan batch.
     *
     * @return void
     */
    public static function ajax_deep_scan_batch() {
        self::ajax_guard();

        $offset   = isset( $_POST['offset'] ) ? absint( $_POST['offset'] ) : 0;
        $per_page = isset( $_POST['per_page'] ) ? absint( $_POST['per_page'] ) : 10;
        $per_page = $per_page > 0 && $per_page <= 50 ? $per_page : 10;

        $q = new WP_Query(
            array(
                'post_type'              => array( 'post', 'page' ),
                'post_status'            => array( 'publish' ),
                'posts_per_page'         => $per_page,
                'offset'                 => $offset,
                'fields'                 => 'ids',
                'no_found_rows'          => false,
                'update_post_meta_cache' => false,
                'update_post_term_cache' => false,
            )
        );

        $total = (int) $q->found_posts;
        $ids   = is_array( $q->posts ) ? array_map( 'absint', $q->posts ) : array();

        global $wpdb;
        $usage_table = $wpdb->prefix . 'folderra_media_usage';
        $now         = current_time( 'mysql' );

        foreach ( $ids as $post_id ) {
            $url = get_permalink( $post_id );
            if ( ! $url ) {
                continue;
            }

            $resp = wp_remote_get(
                $url,
                array(
                    'timeout' => 15,
                    'headers' => array( 'User-Agent' => 'Folderra Media Organizer' ),
                )
            );

            if ( is_wp_error( $resp ) ) {
                continue;
            }

            $body = wp_remote_retrieve_body( $resp );
            if ( ! is_string( $body ) || '' === $body ) {
                continue;
            }

            $urls = self::extract_uploads_urls_from_content( $body );
            $found = array();

            foreach ( $urls as $u ) {
                $aid = self::url_to_attachment_id( $u );
                if ( $aid ) {
                    $found[ $aid ] = true;
                }
            }

            foreach ( array_keys( $found ) as $attachment_id ) {
                $attachment_id = absint( $attachment_id );
                if ( ! $attachment_id ) {
                    continue;
                }

                self::get_or_create_uid( $attachment_id );

                $wpdb->insert(
                    $usage_table,
                    array(
                        'attachment_id' => $attachment_id,
                        'post_id'       => $post_id,
                        'method'        => 'crawl_html',
                        'last_seen'     => $now,
                    ),
                    array( '%d', '%d', '%s', '%s' )
                );
            }
        }

        $next_offset = $offset + count( $ids );
        $done        = ( $next_offset >= $total ) || 0 === count( $ids );

        wp_send_json_success(
            array(
                'total'       => $total,
                'processed'   => min( $next_offset, $total ),
                'next_offset' => $next_offset,
                'done'        => $done,
            )
        );
    }

    /**
     * AJAX: hash batch.
     *
     * @return void
     */
    public static function ajax_hash_batch() {
        self::ajax_guard();

        global $wpdb;

        $offset   = isset( $_POST['offset'] ) ? absint( $_POST['offset'] ) : 0;
        $per_page = isset( $_POST['per_page'] ) ? absint( $_POST['per_page'] ) : 20;
        $per_page = $per_page > 0 && $per_page <= 100 ? $per_page : 20;

        $q = new WP_Query(
            array(
                'post_type'              => 'attachment',
                'post_status'            => 'inherit',
                'posts_per_page'         => $per_page,
                'offset'                 => $offset,
                'fields'                 => 'ids',
                'no_found_rows'          => false,
                'update_post_meta_cache' => false,
                'update_post_term_cache' => false,
            )
        );

        $total = (int) $q->found_posts;
        $ids   = is_array( $q->posts ) ? array_map( 'absint', $q->posts ) : array();

        $files_table = $wpdb->prefix . 'folderra_media_files';
        $now         = current_time( 'mysql' );

        foreach ( $ids as $attachment_id ) {
            $file = get_attached_file( $attachment_id );
            if ( ! $file || ! file_exists( $file ) ) {
                continue;
            }

            $hash = md5_file( $file ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_read_md5_file
            $size = filesize( $file ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_read_filesize

            $base = wp_basename( $file );
            $base = is_string( $base ) ? sanitize_text_field( $base ) : '';

            self::get_or_create_uid( $attachment_id );

            $wpdb->replace(
                $files_table,
                array(
                    'attachment_id' => $attachment_id,
                    'file_hash'     => $hash,
                    'file_size'     => $size ? absint( $size ) : null,
                    'base_filename' => $base,
                    'last_hashed'   => $now,
                ),
                array( '%d', '%s', '%d', '%s', '%s' )
            );
        }

        $next_offset = $offset + count( $ids );
        $done        = ( $next_offset >= $total ) || 0 === count( $ids );

        wp_send_json_success(
            array(
                'total'       => $total,
                'processed'   => min( $next_offset, $total ),
                'next_offset' => $next_offset,
                'done'        => $done,
            )
        );
    }

    /**
     * Build attachment row.
     *
     * @param int      $attachment_id Attachment.
     * @param int|null $usage_count Usage count.
     * @return array
     */
    private static function format_attachment_row( $attachment_id, $usage_count = null ) {
        $attachment_id = absint( $attachment_id );
        if ( ! $attachment_id ) {
            return array();
        }

        $title = get_the_title( $attachment_id );
        $title = is_string( $title ) ? $title : '';

        $url   = wp_get_attachment_url( $attachment_id );
        $thumb = wp_get_attachment_image_url( $attachment_id, 'thumbnail' );

        $is_quarantine = ( '1' === get_post_meta( $attachment_id, FOLD82SM_MEDIA_QUARANTINE_META, true ) );

        $row = array(
            'type'          => 'attachment',
            'attachment_id' => $attachment_id,
            'title'         => $title,
            'url'           => $url ? esc_url_raw( $url ) : '',
            'thumb'         => $thumb ? esc_url_raw( $thumb ) : '',
            'usage_count'   => is_null( $usage_count ) ? null : absint( $usage_count ),
            'quarantine'    => $is_quarantine,
        );

        if ( ! is_null( $usage_count ) && $usage_count > 0 ) {
            global $wpdb;
            $usage_table = $wpdb->prefix . 'folderra_media_usage';

            $post_ids = $wpdb->get_col(
                $wpdb->prepare(
                    "SELECT DISTINCT post_id FROM {$usage_table} WHERE attachment_id = %d ORDER BY last_seen DESC LIMIT 10",
                    $attachment_id
                )
            );

            $where = array();
            if ( is_array( $post_ids ) ) {
                foreach ( $post_ids as $pid ) {
                    $pid = absint( $pid );
                    if ( ! $pid ) {
                        continue;
                    }
                    $where[] = array(
                        'post_id' => $pid,
                        'title'   => get_the_title( $pid ),
                        'edit'    => esc_url_raw( get_edit_post_link( $pid, 'raw' ) ),
                        'view'    => esc_url_raw( get_permalink( $pid ) ),
                    );
                }
            }

            $row['where_used'] = $where;
        }

        return $row;
    }

    /**
     * AJAX: get tab.
     *
     * @return void
     */
    public static function ajax_get_tab() {
        self::ajax_guard();

        global $wpdb;

        $tab      = isset( $_POST['tab'] ) ? sanitize_key( wp_unslash( $_POST['tab'] ) ) : 'used';
        $page     = isset( $_POST['page'] ) ? absint( $_POST['page'] ) : 1;
        $per_page = isset( $_POST['per_page'] ) ? absint( $_POST['per_page'] ) : 20;

        $page     = max( 1, $page );
        $per_page = $per_page > 0 && $per_page <= 100 ? $per_page : 20;
        $offset   = ( $page - 1 ) * $per_page;

        $usage_table = $wpdb->prefix . 'folderra_media_usage';
        $files_table = $wpdb->prefix . 'folderra_media_files';
        $actions_table = $wpdb->prefix . 'folderra_media_actions';

        $trash_status = 'trash';

        $items = array();
        $total = 0;

        if ( 'used' === $tab ) {
            $total = (int) $wpdb->get_var(
                "SELECT COUNT(DISTINCT a.ID)
                 FROM {$wpdb->posts} a
                 INNER JOIN {$usage_table} u ON u.attachment_id = a.ID
                 WHERE a.post_type = 'attachment' AND a.post_status <> '{$trash_status}'
                 AND NOT EXISTS (
                    SELECT 1 FROM {$wpdb->postmeta} pm
                    WHERE pm.post_id = a.ID AND pm.meta_key = '" . esc_sql( FOLD82SM_MEDIA_QUARANTINE_META ) . "' AND pm.meta_value = '1'
                 )"
            );

            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT a.ID as attachment_id, COUNT(DISTINCT u.post_id) as usage_count
                     FROM {$wpdb->posts} a
                     INNER JOIN {$usage_table} u ON u.attachment_id = a.ID
                     WHERE a.post_type = 'attachment' AND a.post_status <> %s
                     AND NOT EXISTS (
                        SELECT 1 FROM {$wpdb->postmeta} pm
                        WHERE pm.post_id = a.ID AND pm.meta_key = %s AND pm.meta_value = '1'
                     )
                     GROUP BY a.ID
                     ORDER BY usage_count DESC, a.ID DESC
                     LIMIT %d OFFSET %d",
                    $trash_status,
                    FOLD82SM_MEDIA_QUARANTINE_META,
                    $per_page,
                    $offset
                ),
                ARRAY_A
            );

            foreach ( $rows as $r ) {
                $aid = absint( $r['attachment_id'] );
                $items[] = self::format_attachment_row( $aid, absint( $r['usage_count'] ) );
            }
        } elseif ( 'unused' === $tab ) {
            $total = (int) $wpdb->get_var(
                "SELECT COUNT(1)
                 FROM {$wpdb->posts} a
                 WHERE a.post_type = 'attachment' AND a.post_status <> '{$trash_status}'
                 AND NOT EXISTS (SELECT 1 FROM {$usage_table} u WHERE u.attachment_id = a.ID)
                 AND NOT EXISTS (
                    SELECT 1 FROM {$wpdb->postmeta} pm
                    WHERE pm.post_id = a.ID AND pm.meta_key = '" . esc_sql( FOLD82SM_MEDIA_QUARANTINE_META ) . "' AND pm.meta_value = '1'
                 )"
            );

            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT a.ID as attachment_id
                     FROM {$wpdb->posts} a
                     WHERE a.post_type = 'attachment' AND a.post_status <> %s
                     AND NOT EXISTS (SELECT 1 FROM {$usage_table} u WHERE u.attachment_id = a.ID)
                     AND NOT EXISTS (
                        SELECT 1 FROM {$wpdb->postmeta} pm
                        WHERE pm.post_id = a.ID AND pm.meta_key = %s AND pm.meta_value = '1'
                     )
                     ORDER BY a.ID DESC
                     LIMIT %d OFFSET %d",
                    $trash_status,
                    FOLD82SM_MEDIA_QUARANTINE_META,
                    $per_page,
                    $offset
                ),
                ARRAY_A
            );

            foreach ( $rows as $r ) {
                $aid = absint( $r['attachment_id'] );
                $items[] = self::format_attachment_row( $aid, 0 );
            }
        } elseif ( 'quarantine' === $tab ) {
            $total = (int) $wpdb->get_var(
                "SELECT COUNT(1)
                 FROM {$wpdb->posts} a
                 INNER JOIN {$wpdb->postmeta} pm ON pm.post_id = a.ID
                 WHERE a.post_type='attachment' AND a.post_status <> '{$trash_status}'
                 AND pm.meta_key = '" . esc_sql( FOLD82SM_MEDIA_QUARANTINE_META ) . "' AND pm.meta_value='1'"
            );

            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT a.ID as attachment_id
                     FROM {$wpdb->posts} a
                     INNER JOIN {$wpdb->postmeta} pm ON pm.post_id = a.ID
                     WHERE a.post_type='attachment' AND a.post_status <> %s
                     AND pm.meta_key = %s AND pm.meta_value='1'
                     ORDER BY a.ID DESC
                     LIMIT %d OFFSET %d",
                    $trash_status,
                    FOLD82SM_MEDIA_QUARANTINE_META,
                    $per_page,
                    $offset
                ),
                ARRAY_A
            );

            foreach ( $rows as $r ) {
                $aid = absint( $r['attachment_id'] );
                $items[] = self::format_attachment_row( $aid, null );
            }
        } elseif ( 'duplicates' === $tab ) {

    /* count only hash that have > 1 attachment NOT in trash */
    $total = (int) $wpdb->get_var(
        "SELECT COUNT(1) FROM (
            SELECT f.file_hash
            FROM {$files_table} f
            INNER JOIN {$wpdb->posts} a ON a.ID = f.attachment_id
            WHERE f.file_hash IS NOT NULL AND f.file_hash <> ''
              AND a.post_type = 'attachment'
              AND a.post_status <> '{$trash_status}'
            GROUP BY f.file_hash
            HAVING COUNT(1) > 1
        ) x"
    );

    /* list groups duplicates */
        $groups = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT f.file_hash, COUNT(1) as cnt
             FROM {$files_table} f
             INNER JOIN {$wpdb->posts} a ON a.ID = f.attachment_id
             WHERE f.file_hash IS NOT NULL AND f.file_hash <> ''
               AND a.post_type = 'attachment'
               AND a.post_status <> %s
             GROUP BY f.file_hash
             HAVING COUNT(1) > 1
             ORDER BY cnt DESC
             LIMIT %d OFFSET %d",
            $trash_status,
            $per_page,
            $offset
        ),
        ARRAY_A
    );

    foreach ( $groups as $g ) {
        $hash = isset( $g['file_hash'] ) ? sanitize_text_field( $g['file_hash'] ) : '';
        if ( '' === $hash ) {
            continue;
        }

        /* keep only attachments not in trash */
        $dup_rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT f.attachment_id
                 FROM {$files_table} f
                 INNER JOIN {$wpdb->posts} a ON a.ID = f.attachment_id
                 WHERE f.file_hash = %s
                   AND a.post_type = 'attachment'
                   AND a.post_status <> %s
                 ORDER BY f.attachment_id DESC
                 LIMIT 50",
                $hash,
                $trash_status
            ),
            ARRAY_A
        );        

                $attachments = array();
                foreach ( $dup_rows as $dr ) {
                    $attachments[] = absint( $dr['attachment_id'] );
                }

                $usage_map = array();
                if ( ! empty( $attachments ) ) {
                    $usage_table = $wpdb->prefix . 'folderra_media_usage';

                    $placeholders = implode( ',', array_fill( 0, count( $attachments ), '%d' ) );

                    // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
                    $sql = $wpdb->prepare(
                        "SELECT attachment_id, COUNT(DISTINCT post_id) AS usage_count
                        FROM {$usage_table}
                        WHERE attachment_id IN ($placeholders)
                        GROUP BY attachment_id",
                        $attachments
                    );

                    $usage_rows = $wpdb->get_results( $sql, ARRAY_A );
                    foreach ( $usage_rows as $ur ) {
                        $aid = absint( $ur['attachment_id'] );
                        $usage_map[ $aid ] = absint( $ur['usage_count'] );
                    }
                }

                $group_items = array();
                foreach ( $attachments as $aid ) {
                    $count = isset( $usage_map[ $aid ] ) ? $usage_map[ $aid ] : 0;
                    $group_items[] = self::format_attachment_row( $aid, $count );
                }

                $items[] = array(
                    'type'        => 'duplicate_group',
                    'file_hash'   => $hash,
                    'count'       => absint( $g['cnt'] ),
                    'attachments' => $group_items,
                );
            }
        } elseif ( 'restore' === $tab ) {
                $actions_table = $wpdb->prefix . 'folderra_media_actions';
                $total = (int) $wpdb->get_var( "SELECT COUNT(1) FROM {$actions_table}" );
                
                $rows = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT batch_id, action, attachment_ids, user_id, created_at
                        FROM {$actions_table}
                        ORDER BY id DESC
                        LIMIT %d OFFSET %d",
                        $per_page,
                        $offset
                    ),
                    ARRAY_A
                );            

            foreach ( $rows as $r ) {
                $ids = is_string( $r['attachment_ids'] ) ? ( json_decode( $r['attachment_ids'], true ) ?: array() ) : array();
                $items[] = array(
                    'type'       => 'restore_point',
                    'batch_id'   => isset( $r['batch_id'] ) ? sanitize_text_field( $r['batch_id'] ) : '',
                    'action'     => isset( $r['action'] ) ? sanitize_key( $r['action'] ) : '',
                    'user_id'    => absint( $r['user_id'] ),
                    'created_at' => isset( $r['created_at'] ) ? sanitize_text_field( $r['created_at'] ) : '',
                    'count'      => is_array( $ids ) ? count( $ids ) : 0,
                );
            }
        } else {
            wp_send_json_error( array( 'message' => esc_html__( 'Invalid tab.', 'folderra-smart-folder-organizer' ) ), 400 );
        }

        wp_send_json_success(
            array(
                'tab'        => $tab,
                'items'      => $items,
                'pagination' => array(
                    'page'     => $page,
                    'per_page' => $per_page,
                    'total'    => $total,
                    'pages'    => (int) ceil( $total / $per_page ),
                ),
            )
        );
    }

    /**
     * Record a restore point.
     *
     * @param string $action Action.
     * @param int[]  $attachment_ids IDs.
     * @return string
     */
    private static function record_action_batch( $action, $attachment_ids ) {
        global $wpdb;

        $action = sanitize_key( $action );
        $attachment_ids = is_array( $attachment_ids ) ? array_values( array_filter( array_map( 'absint', $attachment_ids ) ) ) : array();

        $batch_id = wp_generate_uuid4();

        $actions_table = $wpdb->prefix . 'folderra_media_actions';

        $wpdb->insert(
            $actions_table,
            array(
                'batch_id'       => $batch_id,
                'action'         => $action,
                'attachment_ids' => wp_json_encode( $attachment_ids ),
                'user_id'        => get_current_user_id(),
                'created_at'     => current_time( 'mysql' ),
            ),
            array( '%s', '%s', '%s', '%d', '%s' )
        );

        return $batch_id;
    }

    /**
     * AJAX: apply action.
     *
     * @return void
     */
    public static function ajax_apply_action() {
        self::ajax_guard();

        $action = isset( $_POST['do'] ) ? sanitize_key( wp_unslash( $_POST['do'] ) ) : '';
        $ids    = isset( $_POST['ids'] ) ? (array) wp_unslash( $_POST['ids'] ) : array();

        $attachment_ids = array_values( array_filter( array_map( 'absint', $ids ) ) );

        if ( empty( $attachment_ids ) ) {
            wp_send_json_error( array( 'message' => esc_html__( 'No items selected.', 'folderra-smart-folder-organizer' ) ), 400 );
        }

        $safe_mode = isset( $_POST['safe_mode'] ) ? (bool) absint( $_POST['safe_mode'] ) : true;

        $performed = array();

        if ( 'quarantine' === $action ) {
            foreach ( $attachment_ids as $aid ) {
                update_post_meta( $aid, FOLD82SM_MEDIA_QUARANTINE_META, '1' );
                $performed[] = $aid;
            }
        } elseif ( 'unquarantine' === $action ) {
            foreach ( $attachment_ids as $aid ) {
                delete_post_meta( $aid, FOLD82SM_MEDIA_QUARANTINE_META );
                $performed[] = $aid;
            }
        } elseif ( 'trash' === $action ) {
            // Safe mode: never hard delete in FREE.
            if ( ! $safe_mode ) {
                // Still trash only.
            }
            foreach ( $attachment_ids as $aid ) {
                wp_trash_post( $aid );
                $performed[] = $aid;
            }
        } else {
            wp_send_json_error( array( 'message' => esc_html__( 'Invalid action.', 'folderra-smart-folder-organizer' ) ), 400 );
        }

        $batch_id = self::record_action_batch( $action, $performed );

        wp_send_json_success( array( 'batch_id' => $batch_id, 'count' => count( $performed ) ) );
    }

    /**
     * AJAX: undo batch.
     *
     * @return void
     */
    public static function ajax_undo_batch() {
        self::ajax_guard();

        global $wpdb;

        $batch_id = isset( $_POST['batch_id'] ) ? sanitize_text_field( wp_unslash( $_POST['batch_id'] ) ) : '';
        if ( '' === $batch_id ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Invalid batch.', 'folderra-smart-folder-organizer' ) ), 400 );
        }

        $actions_table = $wpdb->prefix . 'folderra_media_actions';

        $row = $wpdb->get_row(
            $wpdb->prepare( "SELECT action, attachment_ids FROM {$actions_table} WHERE batch_id = %s ORDER BY id DESC LIMIT 1", $batch_id ),
            ARRAY_A
        );

        if ( ! $row ) {
            wp_send_json_error( array( 'message' => esc_html__( 'Batch not found.', 'folderra-smart-folder-organizer' ) ), 404 );
        }

        $action = isset( $row['action'] ) ? sanitize_key( $row['action'] ) : '';
        $ids    = is_string( $row['attachment_ids'] ) ? ( json_decode( $row['attachment_ids'], true ) ?: array() ) : array();
        $ids    = array_values( array_filter( array_map( 'absint', is_array( $ids ) ? $ids : array() ) ) );

        $restored = 0;

        if ( 'trash' === $action ) {
            foreach ( $ids as $aid ) {
                wp_untrash_post( $aid );
                $restored++;
            }
        } elseif ( 'quarantine' === $action ) {
            foreach ( $ids as $aid ) {
                delete_post_meta( $aid, FOLD82SM_MEDIA_QUARANTINE_META );
                $restored++;
            }
        } elseif ( 'unquarantine' === $action ) {
            foreach ( $ids as $aid ) {
                update_post_meta( $aid, FOLD82SM_MEDIA_QUARANTINE_META, '1' );
                $restored++;
            }
        }

        wp_send_json_success( array( 'restored' => $restored ) );
    }

    public static function ajax_where_used() {
    self::ajax_guard();

    $attachment_id = isset( $_POST['attachment_id'] ) ? absint( $_POST['attachment_id'] ) : 0;
    if ( ! $attachment_id ) {
        wp_send_json_error(
            array( 'message' => esc_html__( 'Invalid attachment.', 'folderra-smart-folder-organizer' ) ),
            400
        );
    }

    $page     = isset( $_POST['page'] ) ? absint( $_POST['page'] ) : 1;
    $per_page = isset( $_POST['per_page'] ) ? absint( $_POST['per_page'] ) : 20;

    $page     = max( 1, $page );
    $per_page = ( $per_page > 0 && $per_page <= 100 ) ? $per_page : 20;
    $offset   = ( $page - 1 ) * $per_page;

    global $wpdb;
    $usage_table = $wpdb->prefix . 'folderra_media_usage';

    $total = (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(DISTINCT post_id) FROM {$usage_table} WHERE attachment_id = %d",
            $attachment_id
        )
    );

    $post_ids = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT DISTINCT post_id
             FROM {$usage_table}
             WHERE attachment_id = %d
             ORDER BY last_seen DESC
             LIMIT %d OFFSET %d",
            $attachment_id,
            $per_page,
            $offset
        )
    );

    $items = array();

    if ( is_array( $post_ids ) ) {
        foreach ( $post_ids as $pid ) {
            $pid = absint( $pid );
            if ( ! $pid ) {
                continue;
            }

            $items[] = array(
                'post_id' => $pid,
                'title'   => get_the_title( $pid ),
                'edit'    => esc_url_raw( get_edit_post_link( $pid, 'raw' ) ),
                'view'    => esc_url_raw( get_permalink( $pid ) ),
            );
        }
    }

    wp_send_json_success(
        array(
            'attachment_id' => $attachment_id,
            'items'         => $items,
            'pagination'    => array(
                'page'     => $page,
                'per_page' => $per_page,
                'total'    => $total,
                'pages'    => (int) ceil( $total / $per_page ),
            ),
        )
    );
}

/**
 * Enqueue admin assets for the Media Organizer page only.
 *
 * Uses wp-pointer (core) to show a small tooltip next to the title.
 *
 * @param string $hook_suffix Current admin page hook.
 * @return void
 */
public static function enqueue_admin_assets( $hook_suffix ) {
    // Only run in wp-admin.
    if ( ! is_admin() ) {
        return;
    }

    // Best-effort check: load only on the Media Organizer page.
    // Adjust this if your page slug differs.
    $page = isset( $_GET['page'] ) ? sanitize_key( wp_unslash( $_GET['page'] ) ) : '';
    if ( false === strpos( $page, 'media' ) ) {
        return;
    }

    // Pointer assets are part of WordPress core.
    wp_enqueue_style( 'wp-pointer' );
    wp_enqueue_script( 'wp-pointer' );

    $title = esc_html__( 'How to use Media Organizer', 'folderra-smart-folder-organizer' );
    $body  = esc_html__(
        'Run “Start Scan” to detect where files are used. Use “Compute Hashes” to find duplicates. Move items to Quarantine before deleting, and use Restore Points to undo bulk actions. No front-end changes are made.',
        'folderra-smart-folder-organizer'
    );

    // Pointer content: keep it short and safe.
    $content_html = '<h3>' . $title . '</h3><p>' . $body . '</p>';

    $json_content = wp_json_encode( $content_html );

    // Inline script attached to wp-pointer handle (loaded after it).
    $inline_js = "
    jQuery(function($){
        var \$btn = $('#fold82sm-media-help');
        if (!\$btn.length || typeof \$btn.pointer !== 'function') { return; }

        var isOpen = false;

        // Initialize pointer (do not open automatically).
        \$btn.pointer({
            content: {$json_content},
            position: {
                edge: 'top',
                align: 'left'
            },
            close: function() {
                isOpen = false;
            }
        });

        // Toggle on click.
        \$btn.on('click', function(e){
            e.preventDefault();

            if (isOpen) {
                \$btn.pointer('close');
                isOpen = false;
                return;
            }

            \$btn.pointer('open');
            isOpen = true;
        });
    });
    ";

    wp_add_inline_script( 'wp-pointer', $inline_js );
}
}

// Public wrappers expected by the existing codebase.

/**
 * Install tables wrapper.
 *
 * @return void
 */
function fold82sm_media_organizer_install_tables() {
    Folderra_Media_Organizer::install_tables();
}

/**
 * Page callback wrapper.
 *
 * @return void
 */
function fold82sm_media_organizer_page() {
    Folderra_Media_Organizer::render_page();
}

// Bootstrap.
Folderra_Media_Organizer::init();
