<?php

/**
 * Leads repository. Single place for all DB access (DRY).
 *
 * @package Leadite
 */
namespace Leadite\Storage;

defined( 'ABSPATH' ) || exit;
class LeadsRepository {
    /**
     * Submissions table name.
     */
    public static function table() {
        global $wpdb;
        return $wpdb->prefix . 'leadite_submissions';
    }

    /**
     * Meta table name.
     */
    public static function meta_table() {
        global $wpdb;
        return $wpdb->prefix . 'leadite_meta';
    }

    /**
     * Persist a normalized Lead. Returns the new submission ID, or 0 on failure.
     * Performs simple dedup: an identical hash within the last 5 minutes is skipped.
     *
     * @param Lead $lead Normalized lead.
     * @return int
     */
    public static function insert( Lead $lead ) {
        global $wpdb;
        $created = current_time( 'mysql' );
        // Deduplication hash uses the raw IP (via compute_hash → field values).
        // Anonymisation is applied AFTER the hash is computed so dedup accuracy
        // is unaffected; only the persisted value is anonymised.
        $hash = self::compute_hash( $lead );
        if ( self::is_duplicate( $hash ) ) {
            return 0;
        }
        // Anonymise IP immediately before writing to the database.
        // The raw IP remains accessible on $lead->meta('ip') for the rest of the
        // current request (e.g. integrations fired via Leadite/lead_stored).
        $stored_ip = \Leadite\Support\FieldMapper::anonymize_ip( (string) $lead->meta( 'ip' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $ok = $wpdb->insert( self::table(), array(
            'source'      => $lead->source,
            'form_id'     => $lead->form_id,
            'form_name'   => $lead->form_name,
            'status'      => 'new',
            'starred'     => 0,
            'spam_score'  => (int) $lead->meta( 'spam_score', 0 ),
            'hash'        => $hash,
            'ip'          => $stored_ip,
            'user_agent'  => mb_substr( (string) $lead->meta( 'user_agent' ), 0, 255 ),
            'referer'     => mb_substr( (string) $lead->meta( 'referer' ), 0, 255 ),
            'page_url'    => mb_substr( (string) $lead->meta( 'page_url' ), 0, 255 ),
            'lang'        => (string) $lead->meta( 'lang' ),
            'assigned_to' => 0,
            'created_at'  => $created,
        ), array(
            '%s',
            '%s',
            '%s',
            '%s',
            '%d',
            '%d',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s',
            '%s',
            '%d',
            '%s'
        ) );
        if ( !$ok ) {
            return 0;
        }
        $submission_id = (int) $wpdb->insert_id;
        $order = 0;
        foreach ( $lead->fields as $field ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->insert( self::meta_table(), array(
                'submission_id' => $submission_id,
                'field_key'     => mb_substr( $field['key'], 0, 190 ),
                'field_label'   => mb_substr( $field['label'], 0, 190 ),
                'field_value'   => $field['value'],
                'field_type'    => mb_substr( $field['type'], 0, 40 ),
                'canonical'     => mb_substr( ( isset( $field['canonical'] ) ? $field['canonical'] : '' ), 0, 40 ),
                'sort_order'    => $order++,
            ), array(
                '%d',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%d'
            ) );
        }
        // Persist UTM/gclid attribution as system meta rows (field_type=system),
        // so the UI can show them separately and exports can include them.
        $utm = (array) $lead->meta( 'utm', array() );
        foreach ( $utm as $utm_key => $utm_value ) {
            if ( '' === (string) $utm_value ) {
                continue;
            }
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->insert( self::meta_table(), array(
                'submission_id' => $submission_id,
                'field_key'     => mb_substr( '_' . $utm_key, 0, 190 ),
                'field_label'   => mb_substr( strtoupper( str_replace( 'utm_', '', $utm_key ) ), 0, 190 ),
                'field_value'   => (string) $utm_value,
                'field_type'    => 'system',
                'canonical'     => mb_substr( $utm_key, 0, 40 ),
                'sort_order'    => $order++,
            ), array(
                '%d',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%d'
            ) );
        }
        // Process attachments: copy to a private, randomised subdirectory and
        // store only the relative path (never a public URL).
        if ( !empty( $lead->attachments ) && is_array( $lead->attachments ) ) {
            $upload_dir = wp_upload_dir();
            $root_dir = $upload_dir['basedir'] . '/Leadite';
            // Ensure root Leadite/ directory exists and is hardened.
            if ( !file_exists( $root_dir ) ) {
                wp_mkdir_p( $root_dir );
                \Leadite\Activator::harden_upload_dir();
            }
            // Per-submission subdirectory: <submission_id>-<16-byte-random-hex>
            // The token makes the path unguessable even if the filename is known.
            $token = bin2hex( random_bytes( 16 ) );
            // phpcs:ignore WordPress.WP.AlternativeFunctions
            $sub_dir = $root_dir . '/' . $submission_id . '-' . $token;
            $sub_rel = 'Leadite/' . $submission_id . '-' . $token;
            // relative path prefix
            wp_mkdir_p( $sub_dir );
            // Silence file inside the per-submission directory.
            file_put_contents( $sub_dir . '/index.php', '<?php // Silence is golden.' );
            // phpcs:ignore WordPress.WP.AlternativeFunctions
            foreach ( $lead->attachments as $attach ) {
                $file_name = $attach['name'];
                $tmp_path = $attach['tmp_path'];
                $copy = $attach['copy'];
                $field_key = $attach['field_key'];
                $rel_path = '';
                $mime = '';
                $size_bytes = 0;
                $clean_name = sanitize_file_name( $file_name );
                // original display name (stored in file_name column)
                // —— Optional attachment filtering (OFF by default) ——————————
                // Checks are only applied when the admin has explicitly enabled
                // "Leadite attachment filtering" in Settings → Attachments.
                // On rejection: save a 'skipped' row (not silently discard) so
                // the admin can see what was blocked and why.
                if ( \Leadite\Config::get( 'att_filtering_enabled', false ) ) {
                    $skip_reason = '';
                    // 1. Extension + real MIME check via WordPress core.
                    //    First argument must be a real FS path so WP can inspect the file's content
                    //    (magic bytes via finfo). Passing '' here only checks the extension name.
                    $ext_data = wp_check_filetype_and_ext( $tmp_path, $clean_name );
                    $file_ext = strtolower( pathinfo( $clean_name, PATHINFO_EXTENSION ) );
                    $allowed_exts_str = \Leadite\Config::get( 'att_allowed_extensions', 'jpg,jpeg,png,gif,webp,pdf,doc,docx,xls,xlsx,ppt,pptx,txt,csv,zip,odt,ods' );
                    $allowed_exts = array_map( 'trim', explode( ',', strtolower( $allowed_exts_str ) ) );
                    if ( '' === $file_ext || !in_array( $file_ext, $allowed_exts, true ) ) {
                        $skip_reason = sprintf( 
                            /* translators: %s: file extension */
                            __( 'Extension not allowed: .%s', 'leadite' ),
                            ( $file_ext ?: 'none' )
                         );
                    } elseif ( !empty( $ext_data['ext'] ) && $ext_data['ext'] !== $file_ext ) {
                        // Real MIME does not match the declared extension.
                        $skip_reason = sprintf( 
                            /* translators: 1: declared ext, 2: detected ext */
                            __( 'MIME mismatch: declared .%1$s but content detected as .%2$s', 'leadite' ),
                            $file_ext,
                            $ext_data['ext']
                         );
                    }
                    // 2. Size check (only for local files; remote files checked after fetch).
                    if ( '' === $skip_reason && $copy && file_exists( $tmp_path ) ) {
                        $max_bytes = (int) \Leadite\Config::get( 'att_max_size_kb', 10240 ) * 1024;
                        $fsize = (int) filesize( $tmp_path );
                        if ( $fsize > $max_bytes ) {
                            $skip_reason = sprintf( 
                                /* translators: 1: file size, 2: max size */
                                __( 'File too large: %1$s (max %2$s)', 'leadite' ),
                                size_format( $fsize ),
                                size_format( $max_bytes )
                             );
                        }
                    }
                    if ( '' !== $skip_reason ) {
                        // Save a 'skipped' record with the rejection reason visible to admins.
                        $skipped_note = sprintf( 
                            /* translators: %s: rejection reason */
                            __( 'Attachment exists in original form plugin, but was not copied to Leadite. Reason: %s. Change limits: Leadite → Settings → Attachments.', 'leadite' ),
                            $skip_reason
                         );
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                        $wpdb->insert( $wpdb->prefix . 'leadite_files', array(
                            'submission_id' => $submission_id,
                            'field_key'     => mb_substr( $field_key, 0, 190 ),
                            'file_name'     => mb_substr( $clean_name, 0, 255 ),
                            'file_path'     => '',
                            'mime'          => 'skipped',
                            'size_bytes'    => 0,
                            'skip_reason'   => mb_substr( $skipped_note, 0, 500 ),
                        ), array(
                            '%d',
                            '%s',
                            '%s',
                            '%s',
                            '%s',
                            '%d',
                            '%s'
                        ) );
                        self::log( $submission_id, 'att_skipped', mb_substr( $clean_name . ': ' . $skip_reason, 0, 255 ) );
                        continue;
                    }
                }
                // —— End attachment filtering ————————————————————————————————
                // Resolve the source path: for copy=true, $tmp_path is a FS path;
                // for copy=false (Elementor/WPForms/Ninja/Gravity), $tmp_path is a public URL.
                // In both cases we want to end up with a LOCAL filesystem path so the
                // file can be copied into the private Leadite directory.
                $source_local = $tmp_path;
                $defer_remote_fetch = false;
                // set true when source is a remote URL that needs background copy
                if ( !$copy ) {
                    // Attempt to map the public URL to a local FS path.
                    $source_local = str_replace( rtrim( $upload_dir['baseurl'], '/' ), rtrim( $upload_dir['basedir'], '/' ), $tmp_path );
                    // If the URL→path mapping did not yield an existing file the attachment
                    // lives on an external host (CDN, S3, …).  Fetching it synchronously
                    // during a form submission can stall the visitor's request for up to
                    // 15 s and loads the full file into PHP memory.  Instead, we schedule
                    // a one-off background event and skip the file-copy block below.
                    if ( !file_exists( $source_local ) ) {
                        $defer_remote_fetch = true;
                    }
                }
                if ( $defer_remote_fetch ) {
                    // The attachment URL could not be resolved to a local file.
                    // Preferred path: schedule a background WP-Cron event that fetches
                    // the file AFTER the current request has already returned a response
                    // to the visitor (no blocking, TLS enabled, stream-to-disk).
                    //
                    // Fallback path (DISABLE_WP_CRON=true or unreliable low-traffic
                    // sites): do the fetch synchronously here, still with TLS + stream
                    // so we neither disable cert verification nor load the file into RAM.
                    $path_info = pathinfo( $clean_name );
                    $ext = ( isset( $path_info['extension'] ) ? '.' . strtolower( $path_info['extension'] ) : '' );
                    $disk_name = bin2hex( random_bytes( 8 ) ) . $ext;
                    // phpcs:ignore WordPress.WP.AlternativeFunctions
                    $dest = $sub_dir . '/' . $disk_name;
                    $rel_path = $sub_rel . '/' . $disk_name;
                    $use_cron = !(defined( 'DISABLE_WP_CRON' ) && DISABLE_WP_CRON);
                    if ( $use_cron ) {
                        // Deferred path: insert provisional row and schedule the event.
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                        $wpdb->insert( $wpdb->prefix . 'leadite_files', array(
                            'submission_id' => $submission_id,
                            'field_key'     => mb_substr( $field_key, 0, 190 ),
                            'file_name'     => mb_substr( $clean_name, 0, 255 ),
                            'file_path'     => mb_substr( $rel_path, 0, 255 ),
                            'mime'          => 'pending',
                            'size_bytes'    => 0,
                        ), array(
                            '%d',
                            '%s',
                            '%s',
                            '%s',
                            '%s',
                            '%d'
                        ) );
                        $file_row_id = (int) $wpdb->insert_id;
                        wp_schedule_single_event( time(), 'leadite_fetch_remote_attachment', array(array(
                            'url'         => $tmp_path,
                            'dest'        => $dest,
                            'file_row_id' => $file_row_id,
                        )) );
                        // Skip the normal insert below — already done above.
                        continue;
                    }
                    // Synchronous fallback when WP-Cron is disabled.
                    // SSRF guard: validate the URL before fetching — same as the
                    // WP-Cron path in Plugin::handle_fetch_remote_attachment().
                    if ( !\Leadite\Support\UrlValidator::is_safe_url( $tmp_path ) ) {
                        $mime = 'failed';
                        $size_bytes = 0;
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                        $wpdb->insert( $wpdb->prefix . 'leadite_files', array(
                            'submission_id' => $submission_id,
                            'field_key'     => mb_substr( $field_key, 0, 190 ),
                            'file_name'     => mb_substr( $clean_name, 0, 255 ),
                            'file_path'     => '',
                            'mime'          => 'failed',
                            'size_bytes'    => 0,
                        ), array(
                            '%d',
                            '%s',
                            '%s',
                            '%s',
                            '%s',
                            '%d'
                        ) );
                        continue;
                    }
                    // TLS stays enabled; stream=true pipes the response body directly to
                    // disk so the full file never loads into PHP memory.
                    // redirection=>0: do not follow redirects — a redirect could route to
                    // an internal host even if the original URL passed the SSRF check.
                    $sync_response = wp_remote_get( $tmp_path, array(
                        'timeout'     => 20,
                        'redirection' => 0,
                        'stream'      => true,
                        'filename'    => $dest,
                    ) );
                    if ( !is_wp_error( $sync_response ) && 200 === (int) wp_remote_retrieve_response_code( $sync_response ) && file_exists( $dest ) ) {
                        // Use the downloaded file as the first arg so WP inspects real content.
                        $ext_data = wp_check_filetype_and_ext( $dest, $clean_name );
                        $mime = ( !empty( $ext_data['type'] ) ? $ext_data['type'] : 'application/octet-stream' );
                        $size_bytes = (int) filesize( $dest );
                        // Blocker #5: post-download policy — an attacker may serve a
                        // different file than the name/URL suggested. Validate AFTER landing.
                        if ( \Leadite\Config::get( 'att_filtering_enabled', false ) ) {
                            $policy_fail = '';
                            $max_bytes = (int) \Leadite\Config::get( 'att_max_size_kb', 10240 ) * 1024;
                            $allowed_exts_str = \Leadite\Config::get( 'att_allowed_extensions', 'jpg,jpeg,png,gif,webp,pdf,doc,docx,xls,xlsx,ppt,pptx,txt,csv,zip,odt,ods' );
                            $allowed_exts = array_map( 'trim', explode( ',', strtolower( $allowed_exts_str ) ) );
                            $detected_ext = ( !empty( $ext_data['ext'] ) ? strtolower( $ext_data['ext'] ) : '' );
                            if ( $size_bytes > $max_bytes ) {
                                $policy_fail = sprintf( 
                                    /* translators: 1: file size, 2: max size */
                                    __( 'File too large: %1$s (max %2$s)', 'leadite' ),
                                    size_format( $size_bytes ),
                                    size_format( $max_bytes )
                                 );
                            } elseif ( '' === $detected_ext || !in_array( $detected_ext, $allowed_exts, true ) ) {
                                $policy_fail = sprintf( 
                                    /* translators: %s: file extension */
                                    __( 'Extension not allowed: .%s', 'leadite' ),
                                    ( $detected_ext ?: 'none' )
                                 );
                            }
                            if ( '' !== $policy_fail ) {
                                wp_delete_file( $dest );
                                $skip_note = sprintf( 
                                    /* translators: %s: rejection reason */
                                    __( 'Remote attachment rejected after download. Reason: %s', 'leadite' ),
                                    $policy_fail
                                 );
                                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                                $wpdb->insert( $wpdb->prefix . 'leadite_files', array(
                                    'submission_id' => $submission_id,
                                    'field_key'     => mb_substr( $field_key, 0, 190 ),
                                    'file_name'     => mb_substr( $clean_name, 0, 255 ),
                                    'file_path'     => '',
                                    'mime'          => 'skipped',
                                    'size_bytes'    => 0,
                                    'skip_reason'   => mb_substr( $skip_note, 0, 500 ),
                                ), array(
                                    '%d',
                                    '%s',
                                    '%s',
                                    '%s',
                                    '%s',
                                    '%d',
                                    '%s'
                                ) );
                                continue;
                            }
                        }
                    } else {
                        $mime = 'failed';
                        $size_bytes = 0;
                    }
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $wpdb->insert( $wpdb->prefix . 'leadite_files', array(
                        'submission_id' => $submission_id,
                        'field_key'     => mb_substr( $field_key, 0, 190 ),
                        'file_name'     => mb_substr( $clean_name, 0, 255 ),
                        'file_path'     => mb_substr( $rel_path, 0, 255 ),
                        'mime'          => mb_substr( $mime, 0, 100 ),
                        'size_bytes'    => $size_bytes,
                    ), array(
                        '%d',
                        '%s',
                        '%s',
                        '%s',
                        '%s',
                        '%d'
                    ) );
                    continue;
                }
                // Now copy the resolved source (FS path) into the private Leadite directory.
                if ( file_exists( $source_local ) ) {
                    $path_info = pathinfo( $clean_name );
                    $ext = ( isset( $path_info['extension'] ) ? '.' . strtolower( $path_info['extension'] ) : '' );
                    // Randomise the on-disk filename: original name stays in file_name column.
                    $disk_name = bin2hex( random_bytes( 8 ) ) . $ext;
                    // phpcs:ignore WordPress.WP.AlternativeFunctions
                    $dest = $sub_dir . '/' . $disk_name;
                    if ( copy( $source_local, $dest ) ) {
                        // Store RELATIVE path only — never an absolute FS path or public URL.
                        $rel_path = $sub_rel . '/' . $disk_name;
                        $size_bytes = (int) filesize( $dest );
                        $file_type = wp_check_filetype( $dest );
                        $mime = ( $file_type['type'] ? $file_type['type'] : 'application/octet-stream' );
                    }
                }
                if ( $rel_path ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $wpdb->insert( $wpdb->prefix . 'leadite_files', array(
                        'submission_id' => $submission_id,
                        'field_key'     => mb_substr( $field_key, 0, 190 ),
                        'file_name'     => mb_substr( $clean_name, 0, 255 ),
                        'file_path'     => mb_substr( $rel_path, 0, 255 ),
                        'mime'          => mb_substr( $mime, 0, 100 ),
                        'size_bytes'    => $size_bytes,
                    ), array(
                        '%d',
                        '%s',
                        '%s',
                        '%s',
                        '%s',
                        '%d'
                    ) );
                }
            }
        }
        // Invalidate status / KPI caches so menu badges and dashboard refresh.
        self::flush_counts_cache();
        /**
         * Fires after a lead is stored. Integrations (Sheets, webhooks, notifier)
         * hook here in later phases.
         *
         * @param int  $submission_id Stored ID.
         * @param Lead $lead          The lead object.
         */
        do_action( 'Leadite/lead_stored', $submission_id, $lead );
        return $submission_id;
    }

    /**
     * Build dedup hash from canonical-ish identity (form + email + phone + name).
     *
     * @param Lead $lead Lead.
     * @return string
     */
    protected static function compute_hash( Lead $lead ) {
        $parts = array($lead->source, $lead->form_id);
        foreach ( $lead->fields as $f ) {
            if ( in_array( $f['canonical'], array('email', 'phone', 'full_name'), true ) ) {
                $parts[] = $f['canonical'] . ':' . mb_strtolower( trim( $f['value'] ) );
            }
        }
        return md5( implode( '|', $parts ) );
    }

    /**
     * Has an identical hash been stored in the last 5 minutes?
     *
     * @param string $hash Hash.
     * @return bool
     */
    protected static function is_duplicate( $hash ) {
        global $wpdb;
        $table = self::table();
        // Stored created_at uses current_time('mysql') (site-local). The window
        // must be computed in the SAME timezone. wp_date() correctly translates
        // standard Unix UTC timestamps to the site's local timezone.
        $since = wp_date( 'Y-m-d H:i:s', time() - 5 * MINUTE_IN_SECONDS );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $found = $wpdb->get_var( $wpdb->prepare(
            "SELECT id FROM %i WHERE hash = %s AND created_at >= %s LIMIT 1",
            $table,
            $hash,
            $since
        ) );
        return !empty( $found );
    }

    /**
     * Distinct source slugs present in the table (for filters).
     *
     * @return array
     */
    public static function sources() {
        global $wpdb;
        $table = self::table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT source FROM %i ORDER BY source", $table ) );
    }

    /**
     * Distinct form names (for filters).
     *
     * @return array
     */
    public static function form_names() {
        global $wpdb;
        $table = self::table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT form_name FROM %i WHERE form_name <> '' ORDER BY form_name", $table ) );
    }

    /**
     * Query submissions with filters + pagination.
     *
     * @param array $args Filter args.
     * @return array{items: array, total: int}
     */
    public static function query( array $args ) {
        global $wpdb;
        $defaults = array(
            'search'      => '',
            'source'      => '',
            'form'        => '',
            'status'      => '',
            'starred'     => '',
            'assigned_to' => '',
            'tag_id'      => 0,
            'smart_view'  => '',
            'date_from'   => '',
            'date_to'     => '',
            'ids'         => array(),
            'orderby'     => 'created_at',
            'order'       => 'DESC',
            'per_page'    => 25,
            'page'        => 1,
        );
        $args = array_merge( $defaults, $args );
        $table = self::table();
        $mtable = self::meta_table();
        $where = array('1=1');
        $params = array();
        // f-pro-end
        // If an explicit ID list is provided, short-circuit all other filters.
        if ( !empty( $args['ids'] ) && is_array( $args['ids'] ) ) {
            $clean_ids = array_map( 'intval', $args['ids'] );
            $clean_ids = array_filter( $clean_ids, fn( $id ) => $id > 0 );
            if ( !empty( $clean_ids ) ) {
                $placeholders = implode( ',', array_fill( 0, count( $clean_ids ), '%d' ) );
                $where[] = "id IN ({$placeholders})";
                $params = array_merge( $params, $clean_ids );
            }
        }
        if ( '' !== $args['source'] ) {
            $where[] = 'source = %s';
            $params[] = $args['source'];
        }
        if ( '' !== $args['form'] ) {
            $where[] = 'form_name = %s';
            $params[] = $args['form'];
        }
        if ( '' !== $args['status'] ) {
            $where[] = 'status = %s';
            $params[] = $args['status'];
        }
        if ( '1' === (string) $args['starred'] ) {
            $where[] = 'starred = 1';
        }
        if ( '' !== $args['assigned_to'] && '0' !== (string) $args['assigned_to'] ) {
            $where[] = 'assigned_to = %d';
            $params[] = (int) $args['assigned_to'];
        }
        if ( '' !== $args['date_from'] ) {
            $where[] = 'created_at >= %s';
            $params[] = $args['date_from'] . ' 00:00:00';
        }
        if ( '' !== $args['date_to'] ) {
            $where[] = 'created_at <= %s';
            $params[] = $args['date_to'] . ' 23:59:59';
        }
        if ( '' !== $args['search'] ) {
            $like = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where[] = "id IN ( SELECT submission_id FROM %i WHERE field_value LIKE %s )";
            $params[] = $mtable;
            $params[] = $like;
        }
        // f-pro-end
        $where_sql = implode( ' AND ', $where );
        // Whitelist order columns to avoid injection.
        $allowed_orderby = array(
            'id',
            'created_at',
            'status',
            'source',
            'form_name',
            'hot_score_cached',
            'sla_due_at'
        );
        $orderby = ( in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at' );
        $order = ( 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC' );
        $per_page = (int) $args['per_page'];
        // Total.
        $count_params = array_merge( array($table), $params );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$where_sql}", $count_params ) );
        if ( -1 === $per_page ) {
            $sql_params = array_merge( array($table), $params );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $items = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE {$where_sql} ORDER BY {$orderby} {$order}", $sql_params ) );
        } else {
            $per_page = max( 1, $per_page );
            $page = max( 1, (int) $args['page'] );
            $offset = ($page - 1) * $per_page;
            // Page.
            $page_params = array_merge( array($table), $params, array($per_page, $offset) );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $items = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d", $page_params ) );
        }
        return array(
            'items' => ( $items ? $items : array() ),
            'total' => $total,
        );
    }

    /**
     * Single submission row + its meta fields.
     *
     * @param int $id Submission ID.
     * @return array{row: ?object, fields: array}
     */
    public static function find( $id ) {
        $id = (int) $id;
        if ( $id <= 0 ) {
            return array(
                'row'    => null,
                'fields' => array(),
            );
        }
        $cache_key = 'leadite_find_' . $id;
        $cached = wp_cache_get( $cache_key, 'leadite_submissions' );
        if ( false !== $cached ) {
            return $cached;
        }
        global $wpdb;
        $table = self::table();
        $mtable = self::meta_table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE id = %d", $table, $id ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $fields = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE submission_id = %d ORDER BY sort_order ASC", $mtable, $id ) );
        if ( $row && $fields ) {
            foreach ( $fields as &$field ) {
                $field->original_canonical = $field->canonical;
                $field->canonical = \Leadite\Support\FieldMapper::effective_canonical(
                    $row->source,
                    $row->form_id,
                    $field->field_key,
                    $field->canonical,
                    $field->field_label,
                    $field->field_type,
                    $field->field_value
                );
            }
            unset($field);
        }
        $res = array(
            'row'    => $row,
            'fields' => ( $fields ? $fields : array() ),
        );
        wp_cache_set(
            $cache_key,
            $res,
            'leadite_submissions',
            300
        );
        return $res;
    }

    /**
     * Find file attachments for a submission.
     *
     * @param int $id Submission ID.
     * @return array
     */
    public static function find_files( $id ) {
        $id = (int) $id;
        if ( $id <= 0 ) {
            return array();
        }
        $cache_key = 'leadite_files_' . $id;
        $cached = wp_cache_get( $cache_key, 'leadite_submissions' );
        if ( false !== $cached ) {
            return $cached;
        }
        global $wpdb;
        $table = $wpdb->prefix . 'leadite_files';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE submission_id = %d", $table, $id ) );
        $res = ( $results ? $results : array() );
        wp_cache_set(
            $cache_key,
            $res,
            'leadite_submissions',
            300
        );
        return $res;
    }

    /**
     * Find a single file row by its primary key.
     *
     * @param int $id File row ID.
     * @return object|null
     */
    public static function find_file( $id ) {
        $id = (int) $id;
        if ( $id <= 0 ) {
            return null;
        }
        $cache_key = 'leadite_file_' . $id;
        $cached = wp_cache_get( $cache_key, 'leadite_submissions' );
        if ( false !== $cached ) {
            return $cached;
        }
        global $wpdb;
        $table = $wpdb->prefix . 'leadite_files';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE id = %d", $table, $id ) );
        wp_cache_set(
            $cache_key,
            $row,
            'leadite_submissions',
            300
        );
        return $row;
    }

    /**
     * Permanently delete a single lead and ALL its associated data (GDPR erase).
     *
     * Removes:
     *  - Physical attachment files from disk (wp-content/uploads/Leadite/...).
     *  - The per-submission subdirectory (if now empty).
     *  - Rows in: leadite_files, leadite_notes, leadite_log,
     *             leadite_submission_tags, leadite_meta, leadite_submissions.
     *
     * This is the single canonical delete path. Bulk actions, "Clear All" and
     * uninstall all route through here so nothing is ever left behind.
     *
     * @param int $id Submission ID.
     * @return bool True if the submission row was deleted, false if it did not exist.
     */
    public static function delete( $id ) {
        global $wpdb;
        $id = (int) $id;
        if ( $id <= 0 ) {
            return false;
        }
        $upload_dir = wp_upload_dir();
        // 1. Delete physical attachment files and their per-submission subdirectories.
        $files_table = $wpdb->prefix . 'leadite_files';
        $file_rows = $wpdb->get_results( $wpdb->prepare( "SELECT file_path FROM %i WHERE submission_id = %d", $files_table, $id ) );
        if ( $file_rows ) {
            $removed_dirs = array();
            foreach ( $file_rows as $frow ) {
                $rel = ltrim( $frow->file_path, '/' );
                $abs = $upload_dir['basedir'] . '/' . $rel;
                $real = realpath( $abs );
                $root = realpath( $upload_dir['basedir'] . '/Leadite' );
                // Path-traversal guard: only delete files inside uploads/Leadite/.
                if ( $real && $root && 0 === strpos( $real, $root . DIRECTORY_SEPARATOR ) ) {
                    wp_delete_file( $real );
                    $dir = dirname( $real );
                    if ( !isset( $removed_dirs[$dir] ) ) {
                        $removed_dirs[$dir] = true;
                    }
                }
            }
            global $wp_filesystem;
            if ( empty( $wp_filesystem ) ) {
                require_once ABSPATH . 'wp-admin/includes/file.php';
                WP_Filesystem();
            }
            // Remove the per-submission subdirectory if it is now empty.
            foreach ( array_keys( $removed_dirs ) as $dir ) {
                if ( is_dir( $dir ) ) {
                    $remaining = array_diff( scandir( $dir ), array(
                        '.',
                        '..',
                        'index.php',
                        '.htaccess'
                    ) );
                    if ( empty( $remaining ) ) {
                        // Remove the silence file then the directory itself.
                        $idx = $dir . '/index.php';
                        if ( file_exists( $idx ) ) {
                            wp_delete_file( $idx );
                        }
                        $wp_filesystem->delete( $dir );
                    }
                }
            }
        }
        // 2. Delete all dependent DB rows.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->delete( $files_table, array(
            'submission_id' => $id,
        ), array('%d') );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->delete( $wpdb->prefix . 'leadite_notes', array(
            'submission_id' => $id,
        ), array('%d') );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->delete( $wpdb->prefix . 'leadite_log', array(
            'submission_id' => $id,
        ), array('%d') );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->delete( $wpdb->prefix . 'leadite_submission_tags', array(
            'submission_id' => $id,
        ), array('%d') );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->delete( self::meta_table(), array(
            'submission_id' => $id,
        ), array('%d') );
        // 3. Delete the submission row itself.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $deleted = (bool) $wpdb->delete( self::table(), array(
            'id' => $id,
        ), array('%d') );
        if ( $deleted ) {
            self::flush_counts_cache();
        }
        return $deleted;
    }

    /**
     * Update status of one submission.
     *
     * @param int    $id     Submission ID.
     * @param string $status New status.
     * @return bool
     */
    public static function set_status( $id, $status ) {
        global $wpdb;
        $allowed = array(
            'new',
            'read',
            'in_progress',
            'closed',
            'spam'
        );
        if ( !in_array( $status, $allowed, true ) ) {
            return false;
        }
        $data = array(
            'status' => $status,
        );
        if ( 'read' === $status ) {
            $data['read_at'] = current_time( 'mysql' );
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $updated = (bool) $wpdb->update( self::table(), $data, array(
            'id' => (int) $id,
        ) );
        if ( $updated ) {
            self::flush_counts_cache();
        }
        return $updated;
    }

    /**
     * Toggle the starred flag for a submission.
     *
     * @param int  $id      Submission ID.
     * @param bool $starred True = star, false = unstar.
     * @return bool
     */
    public static function set_starred( $id, $starred ) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        return (bool) $wpdb->update(
            self::table(),
            array(
                'starred' => ( $starred ? 1 : 0 ),
            ),
            array(
                'id' => (int) $id,
            ),
            array('%d'),
            array('%d')
        );
    }

    /**
     * Assign a lead to a WordPress user (0 = unassigned).
     *
     * @param int $id      Submission ID.
     * @param int $user_id WordPress user ID, or 0 to clear.
     * @return bool
     */
    public static function set_assigned_to( $id, $user_id ) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        return (bool) $wpdb->update(
            self::table(),
            array(
                'assigned_to' => max( 0, (int) $user_id ),
            ),
            array(
                'id' => (int) $id,
            ),
            array('%d'),
            array('%d')
        );
    }

    public static function log( $submission_id, $action, $detail = '' ) {
        global $wpdb;
        $submission_id = (int) $submission_id;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->insert( $wpdb->prefix . 'leadite_log', array(
            'submission_id' => $submission_id,
            'user_id'       => get_current_user_id(),
            'action'        => mb_substr( (string) $action, 0, 40 ),
            'detail'        => mb_substr( (string) $detail, 0, 255 ),
            'created_at'    => current_time( 'mysql' ),
        ), array(
            '%d',
            '%d',
            '%s',
            '%s',
            '%s'
        ) );
        // Limit the timeline log history to 50 items per lead to prevent DB bloat.
        if ( $submission_id > 0 ) {
            $log_table = $wpdb->prefix . 'leadite_log';
            $limit = 50;
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE submission_id = %d", $log_table, $submission_id ) );
            if ( $count > $limit ) {
                $to_delete = $count - $limit;
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $ids = $wpdb->get_col( $wpdb->prepare(
                    "SELECT id FROM %i WHERE submission_id = %d ORDER BY id ASC LIMIT %d",
                    $log_table,
                    $submission_id,
                    $to_delete
                ) );
                if ( !empty( $ids ) ) {
                    $ids_placeholder = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
                    $query_params = array_merge( array($log_table), array_map( 'intval', $ids ) );
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
                    $wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE id IN ({$ids_placeholder})", $query_params ) );
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                }
            }
        }
    }

    /**
     * Counts by status for the dashboard / badges.
     *
     * @return array
     */
    public static function status_counts() {
        global $wpdb;
        $table = self::table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rows = $wpdb->get_results( $wpdb->prepare( "SELECT status, COUNT(*) AS c FROM %i GROUP BY status", $table ) );
        $out = array();
        foreach ( (array) $rows as $r ) {
            $out[$r->status] = (int) $r->c;
        }
        return $out;
    }

    /**
     * Fetch Key Performance Indicators (KPIs) for the dashboard.
     *
     * @return array{total: int, today: int, week: int, new: int}
     */
    public static function get_kpis() {
        global $wpdb;
        $table = self::table();
        $today_start = wp_date( 'Y-m-d 00:00:00' );
        $week_start = wp_date( 'Y-m-d 00:00:00', strtotime( '-6 days' ) );
        $prev_week_start = wp_date( 'Y-m-d 00:00:00', strtotime( '-13 days' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i", $table ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $today = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE created_at >= %s", $table, $today_start ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $week = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE created_at >= %s", $table, $week_start ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $prev_week = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE created_at >= %s AND created_at < %s",
            $table,
            $prev_week_start,
            $week_start
        ) );
        $counts = self::status_counts();
        $new = ( isset( $counts['new'] ) ? (int) $counts['new'] : 0 );
        return array(
            'total'     => $total,
            'today'     => $today,
            'week'      => $week,
            'new'       => $new,
            'prev_week' => $prev_week,
        );
    }

    /**
     * Batch-fetch fields for an array of already-queried submission row objects.
     *
     * Fires exactly ONE additional query regardless of how many rows are passed.
     * Returns a map keyed by submission ID; the original row objects are embedded
     * so callers get complete lead data without re-fetching rows.
     *
     * Ordering of the returned map mirrors the order of the input $rows array.
     *
     * @param object[] $rows Submission row objects as returned by query()['items'].
     * @return array<int, array{row: object, fields: object[]}>
     */
    public static function find_many( array $rows ) : array {
        if ( empty( $rows ) ) {
            return array();
        }
        global $wpdb;
        $mtable = self::meta_table();
        $ids = array_map( static function ( $r ) {
            return (int) $r->id;
        }, $rows );
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $meta_rows = $wpdb->get_results( $wpdb->prepare( 
            "SELECT * FROM %i WHERE submission_id IN ({$placeholders}) ORDER BY sort_order ASC",
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $mtable,
            ...$ids
         ) );
        // Index meta rows by submission_id.
        $meta_index = array();
        foreach ( (array) $meta_rows as $m ) {
            $meta_index[(int) $m->submission_id][] = $m;
        }
        // Build the result map, preserving the original row order.
        $result = array();
        foreach ( $rows as $row ) {
            $rid = (int) $row->id;
            $fields = ( isset( $meta_index[$rid] ) ? $meta_index[$rid] : array() );
            if ( !empty( $fields ) ) {
                foreach ( $fields as &$field ) {
                    $field->original_canonical = $field->canonical;
                    $field->canonical = \Leadite\Support\FieldMapper::effective_canonical(
                        $row->source,
                        $row->form_id,
                        $field->field_key,
                        $field->canonical,
                        $field->field_label,
                        $field->field_type,
                        $field->field_value
                    );
                }
                unset($field);
            }
            $result[$rid] = array(
                'row'    => $row,
                'fields' => $fields,
            );
        }
        return $result;
    }

    /**
     * Delete both status-count transients so the next read re-computes fresh values.
     *
     * Called automatically by insert(), delete(), and set_status().
     * Safe to call repeatedly — delete_transient() is a no-op when the key does
     * not exist.
     *
     * @return void
     */
    public static function flush_counts_cache() : void {
        delete_transient( 'leadite_v1_status_counts' );
        delete_transient( 'leadite_v1_kpis' );
    }

    /**
     * Status counts with a short transient cache (admin read-path).
     *
     * The 120-second TTL means badge counts are slightly stale between submissions,
     * but insert()/delete()/set_status() flush the transient immediately so the
     * delay only applies to concurrent simultaneous admin sessions.
     *
     * @param int $ttl Cache lifetime in seconds. Default 120.
     * @return array<string, int>
     */
    public static function status_counts_cached( int $ttl = 120 ) : array {
        $cached = get_transient( 'leadite_v1_status_counts' );
        if ( false !== $cached ) {
            return (array) $cached;
        }
        $counts = self::status_counts();
        set_transient( 'leadite_v1_status_counts', $counts, $ttl );
        return $counts;
    }

    /**
     * Dashboard KPIs with a short transient cache (admin read-path).
     *
     * Uses the same TTL and the same flush key as status_counts_cached() so
     * both caches are always invalidated together.
     *
     * @param int $ttl Cache lifetime in seconds. Default 120.
     * @return array{total: int, today: int, week: int, new: int, prev_week: int}
     */
    public static function get_kpis_cached( int $ttl = 120 ) : array {
        $cached = get_transient( 'leadite_v1_kpis' );
        if ( false !== $cached ) {
            return (array) $cached;
        }
        $kpis = self::get_kpis();
        set_transient( 'leadite_v1_kpis', $kpis, $ttl );
        return $kpis;
    }

    /**
     * Get leads count grouped by day for the last $days days.
     *
     * @param int $days Number of days.
     * @return array<string, int> YYYY-MM-DD => count
     */
    public static function get_leads_by_day( $days = 30 ) {
        global $wpdb;
        $table = self::table();
        $since = wp_date( 'Y-m-d 00:00:00', strtotime( '-' . ($days - 1) . ' days' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $results = $wpdb->get_results( $wpdb->prepare( "SELECT DATE(created_at) as date, COUNT(*) as c \r\n\t\t\t\t FROM %i \r\n\t\t\t\t WHERE created_at >= %s \r\n\t\t\t\t GROUP BY DATE(created_at) \r\n\t\t\t\t ORDER BY date ASC", $table, $since ) );
        // Pre-populate last $days days with 0
        $out = array();
        for ($i = $days - 1; $i >= 0; $i--) {
            $date = wp_date( 'Y-m-d', strtotime( '-' . $i . ' days' ) );
            $out[$date] = 0;
        }
        foreach ( (array) $results as $r ) {
            if ( isset( $out[$r->date] ) ) {
                $out[$r->date] = (int) $r->c;
            }
        }
        return $out;
    }

    /**
     * Get leads count grouped by source.
     *
     * @return array<string, int> source => count
     */
    public static function get_leads_by_source() {
        global $wpdb;
        $table = self::table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $results = $wpdb->get_results( $wpdb->prepare( "SELECT source, COUNT(*) as c FROM %i GROUP BY source ORDER BY c DESC", $table ) );
        $out = array();
        foreach ( (array) $results as $r ) {
            $slug = ( $r->source ? $r->source : 'unknown' );
            $out[$slug] = (int) $r->c;
        }
        return $out;
    }

    /**
     * Get top active forms grouped by name/source.
     *
     * @param int $limit Max forms.
     * @return array
     */
    public static function get_leads_by_form( $limit = 5 ) {
        global $wpdb;
        $table = self::table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $results = $wpdb->get_results( $wpdb->prepare( "SELECT source, form_name, COUNT(*) as c \r\n\t\t\t\t FROM %i \r\n\t\t\t\t WHERE form_name <> '' \r\n\t\t\t\t GROUP BY source, form_name \r\n\t\t\t\t ORDER BY c DESC LIMIT %d", $table, $limit ) );
        return ( $results ? $results : array() );
    }

    /**
     * Get combined notes and logs timeline for a submission, sorted reverse-chronologically.
     *
     * @param int $submission_id Submission ID.
     * @return array
     */
    public static function get_timeline( $submission_id ) {
        global $wpdb;
        $submission_id = (int) $submission_id;
        $notes_table = $wpdb->prefix . 'leadite_notes';
        $log_table = $wpdb->prefix . 'leadite_log';
        $users_table = $wpdb->users;
        // Fetch notes.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $notes = $wpdb->get_results( $wpdb->prepare(
            "SELECT n.id, n.submission_id, n.user_id, n.note AS content, '' AS detail, n.created_at, u.display_name AS user_name, 'note' AS type \r\n\t\t\t FROM %i n\r\n\t\t\t LEFT JOIN %i u ON n.user_id = u.ID\r\n\t\t\t WHERE n.submission_id = %d",
            $notes_table,
            $users_table,
            $submission_id
        ) );
        // Fetch logs.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $logs = $wpdb->get_results( $wpdb->prepare(
            "SELECT l.id, l.submission_id, l.user_id, l.action AS content, l.detail, l.created_at, u.display_name AS user_name, 'log' AS type \r\n\t\t\t FROM %i l\r\n\t\t\t LEFT JOIN %i u ON l.user_id = u.ID\r\n\t\t\t WHERE l.submission_id = %d",
            $log_table,
            $users_table,
            $submission_id
        ) );
        // Combine and sort by created_at DESC (newest first).
        $timeline = array_merge( ( $notes ? $notes : array() ), ( $logs ? $logs : array() ) );
        usort( $timeline, static function ( $a, $b ) {
            return strcmp( $b->created_at, $a->created_at );
        } );
        return $timeline;
    }

    // =========================================================================
    // Tags
    // =========================================================================
    /**
     * All global tags.
     *
     * @return object[] [{id, name, slug, color}]
     */
    public static function get_all_tags() {
        global $wpdb;
        $table = $wpdb->prefix . 'leadite_tags';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i ORDER BY name ASC", $table ) );
        return ( $rows ? $rows : array() );
    }

    /**
     * Tags attached to a single lead.
     *
     * @param int $lead_id Submission ID.
     * @return object[] [{id, name, slug, color}]
     */
    public static function get_lead_tags( int $lead_id ) {
        global $wpdb;
        $t = $wpdb->prefix . 'leadite_tags';
        $st = $wpdb->prefix . 'leadite_submission_tags';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT t.* FROM %i t\r\n\t\t\t\t INNER JOIN %i st ON t.id = st.tag_id\r\n\t\t\t\t WHERE st.submission_id = %d\r\n\t\t\t\t ORDER BY t.name ASC",
            $t,
            $st,
            (int) $lead_id
        ) );
        return ( $rows ? $rows : array() );
    }

    /**
     * Fetch tags for multiple leads in a single query to avoid N+1 queries.
     *
     * @param int[] $lead_ids Array of lead IDs.
     * @return array<int, array> Map of lead_id => array of tag objects.
     */
    public static function get_leads_tags( array $lead_ids ) {
        if ( empty( $lead_ids ) ) {
            return array();
        }
        global $wpdb;
        $t = $wpdb->prefix . 'leadite_tags';
        $st = $wpdb->prefix . 'leadite_submission_tags';
        $clean_ids = array_unique( array_filter( array_map( 'intval', $lead_ids ) ) );
        if ( empty( $clean_ids ) ) {
            return array();
        }
        $placeholders = implode( ',', array_fill( 0, count( $clean_ids ), '%d' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $results = $wpdb->get_results( $wpdb->prepare(
            "SELECT t.*, st.submission_id \r\n\t\t\t\t FROM %i t\r\n\t\t\t\t INNER JOIN %i st ON t.id = st.tag_id\r\n\t\t\t\t WHERE st.submission_id IN ({$placeholders})\r\n\t\t\t\t ORDER BY t.name ASC",
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $t,
            $st,
            ...$clean_ids
        ) );
        $map = array();
        foreach ( $clean_ids as $id ) {
            $map[$id] = array();
        }
        if ( $results ) {
            foreach ( $results as $row ) {
                $sub_id = (int) $row->submission_id;
                $tag = (object) array(
                    'id'    => $row->id,
                    'name'  => $row->name,
                    'color' => $row->color,
                );
                $map[$sub_id][] = $tag;
            }
        }
        return $map;
    }

    /**
     * Fetch notes for multiple leads in a single query to avoid N+1 queries.
     *
     * @param int[] $lead_ids Array of lead IDs.
     * @return array<int, array> Map of lead_id => array of note objects [{note, created_at}].
     */
    public static function get_leads_notes( array $lead_ids ) {
        if ( empty( $lead_ids ) ) {
            return array();
        }
        global $wpdb;
        $notes_table = $wpdb->prefix . 'leadite_notes';
        $clean_ids = array_unique( array_filter( array_map( 'intval', $lead_ids ) ) );
        if ( empty( $clean_ids ) ) {
            return array();
        }
        $placeholders = implode( ',', array_fill( 0, count( $clean_ids ), '%d' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $results = $wpdb->get_results( $wpdb->prepare( 
            "SELECT submission_id, note, created_at \r\n\t\t\t\t FROM %i\r\n\t\t\t\t WHERE submission_id IN ({$placeholders})\r\n\t\t\t\t ORDER BY id ASC",
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $notes_table,
            ...$clean_ids
         ) );
        $map = array();
        foreach ( $clean_ids as $id ) {
            $map[$id] = array();
        }
        if ( $results ) {
            foreach ( $results as $row ) {
                $sub_id = (int) $row->submission_id;
                $map[$sub_id][] = (object) array(
                    'note'       => $row->note,
                    'created_at' => $row->created_at,
                );
            }
        }
        return $map;
    }

    /**
     * Replace the complete set of tags on a lead.
     *
     * @param int   $lead_id Submission ID.
     * @param int[] $tag_ids Tag IDs to attach (empty = remove all).
     * @return void
     */
    public static function set_lead_tags( int $lead_id, array $tag_ids ) {
        global $wpdb;
        $st = $wpdb->prefix . 'leadite_submission_tags';
        $lead_id = (int) $lead_id;
        // Remove all existing tag relations for this lead.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->delete( $st, array(
            'submission_id' => $lead_id,
        ), array('%d') );
        // Insert new relations.
        foreach ( array_unique( array_map( 'intval', $tag_ids ) ) as $tag_id ) {
            if ( $tag_id > 0 ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->insert( $st, array(
                    'submission_id' => $lead_id,
                    'tag_id'        => $tag_id,
                ), array('%d', '%d') );
            }
        }
    }

    /**
     * Add a single tag to a lead (idempotent — skips if already attached).
     *
     * @param int $lead_id Submission ID.
     * @param int $tag_id  Tag ID.
     * @return void
     */
    public static function add_lead_tag( int $lead_id, int $tag_id ) {
        global $wpdb;
        $st = $wpdb->prefix . 'leadite_submission_tags';
        // INSERT IGNORE semantics via direct duplicate check.
        $exists = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM %i WHERE submission_id = %d AND tag_id = %d",
            $st,
            $lead_id,
            $tag_id
        ) );
        if ( !$exists ) {
            $wpdb->insert( $st, array(
                'submission_id' => (int) $lead_id,
                'tag_id'        => (int) $tag_id,
            ), array('%d', '%d') );
        }
    }

    /**
     * Remove a single tag from a lead.
     *
     * @param int $lead_id Submission ID.
     * @param int $tag_id  Tag ID.
     * @return void
     */
    public static function remove_lead_tag( int $lead_id, int $tag_id ) {
        global $wpdb;
        $wpdb->delete( $wpdb->prefix . 'leadite_submission_tags', array(
            'submission_id' => (int) $lead_id,
            'tag_id'        => (int) $tag_id,
        ), array('%d', '%d') );
    }

    /**
     * Create a new global tag. Returns the new tag ID, or 0 on error.
     *
     * @param string $name  Display name.
     * @param string $color Hex color, e.g. "#4f46e5".
     * @return int
     */
    public static function create_tag( string $name, string $color = '#6366f1' ) : int {
        global $wpdb;
        $name = mb_substr( sanitize_text_field( $name ), 0, 100 );
        $slug = mb_substr( sanitize_title( $name ), 0, 100 );
        $color = ( sanitize_hex_color( $color ) ?: '#6366f1' );
        if ( '' === $name || '' === $slug ) {
            return 0;
        }
        $table = $wpdb->prefix . 'leadite_tags';
        // Prevent slug collision: append numeric suffix if needed.
        $base_slug = $slug;
        $suffix = 1;
        while ( $wpdb->get_var( $wpdb->prepare( "SELECT id FROM %i WHERE slug = %s", $table, $slug ) ) ) {
            $slug = $base_slug . '-' . $suffix++;
        }
        $ok = $wpdb->insert( $table, array(
            'name'  => $name,
            'slug'  => $slug,
            'color' => $color,
        ), array('%s', '%s', '%s') );
        return ( $ok ? (int) $wpdb->insert_id : 0 );
    }

    /**
     * Update an existing global tag.
     *
     * @param int    $id    Tag ID.
     * @param string $name  New display name.
     * @param string $color New hex color.
     * @return bool
     */
    public static function update_tag( int $id, string $name, string $color ) : bool {
        global $wpdb;
        $name = mb_substr( sanitize_text_field( $name ), 0, 100 );
        $color = ( sanitize_hex_color( $color ) ?: '#6366f1' );
        if ( '' === $name ) {
            return false;
        }
        return (bool) $wpdb->update(
            $wpdb->prefix . 'leadite_tags',
            array(
                'name'  => $name,
                'color' => $color,
            ),
            array(
                'id' => (int) $id,
            ),
            array('%s', '%s'),
            array('%d')
        );
    }

    /**
     * Delete a global tag and all its lead relations.
     * Does NOT delete leads — only the tag and pivot rows.
     *
     * @param int $id Tag ID.
     * @return bool
     */
    public static function delete_tag( int $id ) : bool {
        global $wpdb;
        $id = (int) $id;
        if ( $id <= 0 ) {
            return false;
        }
        // Remove pivot rows first.
        $wpdb->delete( $wpdb->prefix . 'leadite_submission_tags', array(
            'tag_id' => $id,
        ), array('%d') );
        // Remove the tag itself.
        return (bool) $wpdb->delete( $wpdb->prefix . 'leadite_tags', array(
            'id' => $id,
        ), array('%d') );
    }

    // =========================================================================
    // Field Mapper
    // =========================================================================
    /**
     * Transient key used to cache the result of get_distinct_field_keys().
     * Exposed as a constant so callers (e.g. the field-map save handler) can
     * flush the cache after writing new overrides.
     */
    const FIELD_KEYS_CACHE_KEY = 'leadite_v1_field_keys';

    /**
     * Return all distinct (source, form_id, form_name, field_key, field_label)
     * combinations discovered in leadite_meta, joined with any saved manual
     * canonical override from leadite_field_map.
     *
     * Used by the Field Mapper settings tab to show what's available to map.
     * Result is cached in a 5-minute transient to avoid a full table scan on
     * every admin page load.  Call flush_field_keys_cache() after writing new
     * field-map overrides so the UI reflects the change immediately.
     *
     * @return object[] [{source, form_id, form_name, field_key, field_label, guessed_canonical, saved_canonical}]
     */
    public static function get_distinct_field_keys() : array {
        // Short-circuit: return cached result when available.
        $cached = get_transient( self::FIELD_KEYS_CACHE_KEY );
        if ( false !== $cached && is_array( $cached ) ) {
            return $cached;
        }
        global $wpdb;
        $sub = self::table();
        $meta = self::meta_table();
        // Use a GROUP BY query so the DB returns at most one row per
        // (source, form_id, field_key) combination rather than one row per
        // meta record — drastically reducing memory and result-set size on
        // large installations.  MAX() picks an arbitrary non-empty value for
        // the display columns (form_name, field_label, guessed_canonical).
        $rows = $wpdb->get_results( $wpdb->prepare( "SELECT\r\n\t\t\t\t\ts.source,\r\n\t\t\t\t\ts.form_id,\r\n\t\t\t\t\tMAX( s.form_name )      AS form_name,\r\n\t\t\t\t\tm.field_key,\r\n\t\t\t\t\tMAX( m.field_label )    AS field_label,\r\n\t\t\t\t\tMAX( m.canonical )      AS guessed_canonical\r\n\t\t\t\tFROM %i m\r\n\t\t\t\tINNER JOIN %i s ON s.id = m.submission_id\r\n\t\t\t\tWHERE m.field_type NOT IN ('system')\r\n\t\t\t\t\tAND m.field_key <> ''\r\n\t\t\t\tGROUP BY s.source, s.form_id, m.field_key", $meta, $sub ) );
        if ( empty( $rows ) ) {
            return array();
        }
        // Group and normalize in PHP
        $grouped = array();
        foreach ( $rows as $r ) {
            $norm_id = \Leadite\Support\FieldMapper::normalize_form_id( $r->form_id );
            $key = $r->source . '||' . $norm_id . '||' . $r->field_key;
            if ( !isset( $grouped[$key] ) ) {
                $grouped[$key] = array(
                    'source'            => $r->source,
                    'form_id'           => $norm_id,
                    'form_name'         => ( $r->form_name ?: '' ),
                    'field_key'         => $r->field_key,
                    'field_label'       => ( $r->field_label ?: '' ),
                    'guessed_canonical' => ( $r->guessed_canonical ?: '' ),
                );
            } else {
                // Fill missing details if any
                if ( empty( $grouped[$key]['form_name'] ) && !empty( $r->form_name ) ) {
                    $grouped[$key]['form_name'] = $r->form_name;
                }
                if ( empty( $grouped[$key]['field_label'] ) && !empty( $r->field_label ) ) {
                    $grouped[$key]['field_label'] = $r->field_label;
                }
                if ( empty( $grouped[$key]['guessed_canonical'] ) && !empty( $r->guessed_canonical ) ) {
                    $grouped[$key]['guessed_canonical'] = $r->guessed_canonical;
                }
            }
        }
        // Now load all saved manual canonical overrides from leadite_field_map
        $fmap = $wpdb->prefix . 'leadite_field_map';
        $overrides_rows = $wpdb->get_results( $wpdb->prepare( "SELECT source, form_id, field_key, canonical FROM %i", $fmap ) );
        $overrides = array();
        foreach ( (array) $overrides_rows as $or ) {
            $okey = $or->source . '||' . $or->form_id . '||' . $or->field_key;
            $overrides[$okey] = $or->canonical;
        }
        $out = array();
        foreach ( $grouped as $key => $g ) {
            $saved = ( isset( $overrides[$key] ) ? $overrides[$key] : '' );
            $out[] = (object) array(
                'source'            => $g['source'],
                'form_id'           => $g['form_id'],
                'form_name'         => $g['form_name'],
                'field_key'         => $g['field_key'],
                'field_label'       => $g['field_label'],
                'guessed_canonical' => $g['guessed_canonical'],
                'saved_canonical'   => $saved,
            );
        }
        // Sort by source, form_id, field_key
        usort( $out, function ( $a, $b ) {
            $cmp = strcmp( $a->source, $b->source );
            if ( 0 !== $cmp ) {
                return $cmp;
            }
            $cmp = strcmp( $a->form_id, $b->form_id );
            if ( 0 !== $cmp ) {
                return $cmp;
            }
            return strcmp( $a->field_key, $b->field_key );
        } );
        // Cache the assembled result for 5 minutes.  The cache is flushed by
        // flush_field_keys_cache() which is called after saving field-map overrides.
        set_transient( self::FIELD_KEYS_CACHE_KEY, $out, 5 * MINUTE_IN_SECONDS );
        return $out;
    }

    /**
     * Flush the field-keys transient cache.
     *
     * Call this whenever leadite_field_map rows are inserted, updated or
     * deleted so the Field Mapper UI shows fresh data on the next page load.
     *
     * @return void
     */
    public static function flush_field_keys_cache() : void {
        delete_transient( self::FIELD_KEYS_CACHE_KEY );
    }

}
