prefix . 'ahmadraza360_lm_' . $suffix; } /** * Initialize database hooks. */ public static function init() { // Run daily data retention purge check on administrative screen loads if ( is_admin() ) { add_action( 'admin_init', array( __CLASS__, 'maybe_purge_old_data' ) ); } } /** * Create custom tables using dbDelta. */ public static function create_tables() { global $wpdb; $charset_collate = $wpdb->get_charset_collate(); require_once ABSPATH . 'wp-admin/includes/upgrade.php'; // 1. Submissions Table $table_submissions = self::table( 'submissions' ); $sql_submissions = "CREATE TABLE $table_submissions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, form_id BIGINT UNSIGNED NOT NULL, form_title VARCHAR(255) NOT NULL, status VARCHAR(50) DEFAULT 'new' NOT NULL, priority VARCHAR(20) DEFAULT 'medium' NOT NULL, assigned_to BIGINT UNSIGNED DEFAULT NULL, is_spam TINYINT(1) DEFAULT 0 NOT NULL, ip_address VARCHAR(45) NOT NULL, user_agent VARCHAR(255) NOT NULL, page_url TEXT NOT NULL, referrer TEXT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY form_id (form_id), KEY status (status), KEY priority (priority), KEY is_spam (is_spam), KEY created_at (created_at) ) $charset_collate;"; dbDelta( $sql_submissions ); // 2. Fields Table (EAV) $table_fields = self::table( 'fields' ); $sql_fields = "CREATE TABLE $table_fields ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, submission_id BIGINT UNSIGNED NOT NULL, field_key VARCHAR(255) NOT NULL, field_value LONGTEXT NOT NULL, PRIMARY KEY (id), KEY submission_id (submission_id), KEY field_key (field_key(191)) ) $charset_collate;"; dbDelta( $sql_fields ); // 3. Notes Table $table_notes = self::table( 'notes' ); $sql_notes = "CREATE TABLE $table_notes ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, submission_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, note_text TEXT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY submission_id (submission_id) ) $charset_collate;"; dbDelta( $sql_notes ); // 4. Activity Log Table $table_activity = self::table( 'activity_log' ); $sql_activity = "CREATE TABLE $table_activity ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, submission_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, activity_type VARCHAR(50) NOT NULL, details TEXT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY submission_id (submission_id) ) $charset_collate;"; dbDelta( $sql_activity ); } /** * Anonymize or drop IP address based on privacy settings. * * @param string $ip Raw IP address. * @return string */ public static function sanitize_ip( $ip ) { $ip_storage = Ahmadraza360_LM_Settings::get( 'ip_storage', 'full' ); if ( 'none' === $ip_storage ) { return ''; } if ( 'anonymize' === $ip_storage ) { if ( empty( $ip ) ) { return ''; } // IPv4 masking if ( filter_var( $ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4 ) ) { return preg_replace( '/\.\d+$/', '.0', $ip ); } // IPv6 masking if ( filter_var( $ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6 ) ) { $parts = explode( ':', $ip ); if ( count( $parts ) > 1 ) { // Keep first 3 segments and clear the rest return implode( ':', array_slice( $parts, 0, 3 ) ) . ':0:0:0:0:0'; } } } return $ip; } /** * Insert form submission record and associated fields. * * @param array $submission_data Metadata of submission. * @param array $form_fields Raw key-value fields. * @return int|false ID of inserted lead on success, false on failure. */ public static function insert_submission( $submission_data, $form_fields ) { global $wpdb; // Mask IP address based on compliance configuration $ip_addr = isset( $submission_data['ip_address'] ) ? $submission_data['ip_address'] : ''; $submission_data['ip_address'] = self::sanitize_ip( $ip_addr ); $table_submissions = self::table( 'submissions' ); $table_fields = self::table( 'fields' ); // Set default priority and status if not overridden if ( ! isset( $submission_data['status'] ) ) { $submission_data['status'] = Ahmadraza360_LM_Settings::get( 'default_status', 'new' ); } if ( ! isset( $submission_data['priority'] ) ) { $submission_data['priority'] = Ahmadraza360_LM_Settings::get( 'default_priority', 'medium' ); } if ( ! isset( $submission_data['created_at'] ) ) { $submission_data['created_at'] = current_time( 'mysql' ); } // Insert main submission metadata $inserted = $wpdb->insert( $table_submissions, $submission_data ); if ( ! $inserted ) { return false; } $submission_id = $wpdb->insert_id; // Insert EAV attributes foreach ( $form_fields as $key => $value ) { // Skip internal contact form fields if ( strpos( $key, '_wpcf7' ) === 0 ) { continue; } // Handle array values (checkboxes, select-multiples) if ( is_array( $value ) ) { $value = implode( ', ', $value ); } $wpdb->insert( $table_fields, array( 'submission_id' => $submission_id, 'field_key' => sanitize_text_field( $key ), 'field_value' => sanitize_textarea_field( $value ), ) ); } // Write initial activation timeline event self::log_activity( $submission_id, 0, 'created', 'Lead captured via form submission.' ); return $submission_id; } /** * Log CRM agent updates inside lead timeline. * * @param int $submission_id Lead ID. * @param int $user_id WP User ID. * @param string $type Event key. * @param string $details JSON or textual descriptions. */ public static function log_activity( $submission_id, $user_id, $type, $details ) { global $wpdb; $wpdb->insert( self::table( 'activity_log' ), array( 'submission_id' => $submission_id, 'user_id' => $user_id, 'activity_type' => sanitize_text_field( $type ), 'details' => sanitize_textarea_field( $details ), 'created_at' => current_time( 'mysql' ), ) ); } /** * Add operator notes to lead files. * * @param int $submission_id Lead ID. * @param int $user_id Operator User ID. * @param string $note Notes text. * @return int Insert ID. */ public static function add_note( $submission_id, $user_id, $note ) { global $wpdb; $wpdb->insert( self::table( 'notes' ), array( 'submission_id' => $submission_id, 'user_id' => $user_id, 'note_text' => sanitize_textarea_field( $note ), 'created_at' => current_time( 'mysql' ), ) ); $note_id = $wpdb->insert_id; self::log_activity( $submission_id, $user_id, 'note_added', 'Added an internal note.' ); return $note_id; } /** * Delete notes by note ID. * * @param int $note_id Note primary ID. * @param int $user_id WP user removing it. * @return bool */ public static function delete_note( $note_id, $user_id ) { global $wpdb; $table = self::table( 'notes' ); // Grab submission ID to log activity $note = $wpdb->get_row( $wpdb->prepare( "SELECT submission_id FROM $table WHERE id = %d", $note_id ) ); if ( $note ) { $wpdb->delete( $table, array( 'id' => $note_id ) ); self::log_activity( $note->submission_id, $user_id, 'note_deleted', 'Removed an internal note.' ); return true; } return false; } /** * Retrieve a list of submissions based on filters. * * @param array $args Filter and paging arguments. * @return array Array of structures. */ public static function get_submissions( $args = array() ) { global $wpdb; $defaults = array( 'search' => '', 'form_id' => '', 'status' => '', 'priority' => '', 'start_date' => '', 'end_date' => '', 'assigned_to' => '', 'orderby' => 'created_at', 'order' => 'DESC', 'limit' => 20, 'offset' => 0, 'is_spam' => 0, ); $params = array_merge( $defaults, $args ); $table_sub = self::table( 'submissions' ); $table_fld = self::table( 'fields' ); $where = array(); $sql_args = array(); // Spam filter $where[] = 'sub.is_spam = %d'; $sql_args[] = $params['is_spam']; // Form ID filter if ( ! empty( $params['form_id'] ) ) { $where[] = 'sub.form_id = %d'; $sql_args[] = $params['form_id']; } // Status filter if ( ! empty( $params['status'] ) ) { $where[] = 'sub.status = %s'; $sql_args[] = $params['status']; } // Priority filter if ( ! empty( $params['priority'] ) ) { $where[] = 'sub.priority = %s'; $sql_args[] = $params['priority']; } // Assignee filter if ( ! empty( $params['assigned_to'] ) ) { $where[] = 'sub.assigned_to = %d'; $sql_args[] = $params['assigned_to']; } // Date Range filters if ( ! empty( $params['start_date'] ) ) { $where[] = 'sub.created_at >= %s'; $sql_args[] = $params['start_date'] . ' 00:00:00'; } if ( ! empty( $params['end_date'] ) ) { $where[] = 'sub.created_at <= %s'; $sql_args[] = $params['end_date'] . ' 23:59:59'; } // Text Search query across fields and core metadata if ( ! empty( $params['search'] ) ) { $search_term = '%' . $wpdb->esc_like( $params['search'] ) . '%'; $where[] = "(sub.form_title LIKE %s OR sub.ip_address LIKE %s OR sub.id IN (SELECT DISTINCT submission_id FROM $table_fld WHERE field_value LIKE %s))"; $sql_args[] = $search_term; $sql_args[] = $search_term; $sql_args[] = $search_term; } // Build WHERE clause $where_clause = ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : ''; // Sorting validation $allowed_orderby = array( 'id', 'form_id', 'form_title', 'status', 'priority', 'created_at' ); $orderby = in_array( $params['orderby'], $allowed_orderby, true ) ? $params['orderby'] : 'created_at'; $order = strtoupper( $params['order'] ) === 'ASC' ? 'ASC' : 'DESC'; // SQL pagination and limits $limit = intval( $params['limit'] ); $offset = intval( $params['offset'] ); // Assemble query $query = "SELECT sub.* FROM $table_sub sub $where_clause ORDER BY sub.$orderby $order"; if ( $limit > 0 ) { $query .= " LIMIT $offset, $limit"; } // Execute if ( ! empty( $sql_args ) ) { $results = $wpdb->get_results( $wpdb->prepare( $query, $sql_args ) ); } else { $results = $wpdb->get_results( $query ); } return $results; } /** * Count total rows matching criteria. * * @param array $args Filtering parameters. * @return int Total counts. */ public static function count_submissions( $args = array() ) { global $wpdb; $defaults = array( 'search' => '', 'form_id' => '', 'status' => '', 'priority' => '', 'start_date' => '', 'end_date' => '', 'assigned_to' => '', 'is_spam' => 0, ); $params = array_merge( $defaults, $args ); $table_sub = self::table( 'submissions' ); $table_fld = self::table( 'fields' ); $where = array(); $sql_args = array(); $where[] = 'sub.is_spam = %d'; $sql_args[] = $params['is_spam']; if ( ! empty( $params['form_id'] ) ) { $where[] = 'sub.form_id = %d'; $sql_args[] = $params['form_id']; } if ( ! empty( $params['status'] ) ) { $where[] = 'sub.status = %s'; $sql_args[] = $params['status']; } if ( ! empty( $params['priority'] ) ) { $where[] = 'sub.priority = %s'; $sql_args[] = $params['priority']; } if ( ! empty( $params['assigned_to'] ) ) { $where[] = 'sub.assigned_to = %d'; $sql_args[] = $params['assigned_to']; } if ( ! empty( $params['start_date'] ) ) { $where[] = 'sub.created_at >= %s'; $sql_args[] = $params['start_date'] . ' 00:00:00'; } if ( ! empty( $params['end_date'] ) ) { $where[] = 'sub.created_at <= %s'; $sql_args[] = $params['end_date'] . ' 23:59:59'; } if ( ! empty( $params['search'] ) ) { $search_term = '%' . $wpdb->esc_like( $params['search'] ) . '%'; $where[] = "(sub.form_title LIKE %s OR sub.ip_address LIKE %s OR sub.id IN (SELECT DISTINCT submission_id FROM $table_fld WHERE field_value LIKE %s))"; $sql_args[] = $search_term; $sql_args[] = $search_term; $sql_args[] = $search_term; } $where_clause = ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : ''; $query = "SELECT COUNT(*) FROM $table_sub sub $where_clause"; if ( ! empty( $sql_args ) ) { return intval( $wpdb->get_var( $wpdb->prepare( $query, $sql_args ) ) ); } else { return intval( $wpdb->get_var( $query ) ); } } /** * Retrieve a single lead record with dynamic fields, notes, and activity timeline. * * @param int $id Lead ID. * @return object|false Formatted lead payload. */ public static function get_lead( $id ) { global $wpdb; $table_sub = self::table( 'submissions' ); $lead = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $table_sub WHERE id = %d", $id ) ); if ( ! $lead ) { return false; } // Enqueue related attributes (EAV) $table_fld = self::table( 'fields' ); $fields = $wpdb->get_results( $wpdb->prepare( "SELECT field_key, field_value FROM $table_fld WHERE submission_id = %d", $id ) ); $lead->fields = array(); if ( ! empty( $fields ) ) { foreach ( $fields as $f ) { $lead->fields[ $f->field_key ] = $f->field_value; } } // Enqueue operator notes $table_notes = self::table( 'notes' ); $lead->notes = $wpdb->get_results( $wpdb->prepare( "SELECT n.*, u.display_name FROM $table_notes n LEFT JOIN {$wpdb->users} u ON n.user_id = u.ID WHERE n.submission_id = %d ORDER BY n.created_at DESC", $id ) ); // Enqueue activity logs $table_activity = self::table( 'activity_log' ); $lead->activities = $wpdb->get_results( $wpdb->prepare( "SELECT a.*, u.display_name FROM $table_activity a LEFT JOIN {$wpdb->users} u ON a.user_id = u.ID WHERE a.submission_id = %d ORDER BY a.created_at DESC", $id ) ); return $lead; } /** * Update status, priority, or assignee for a submission. * * @param int $id Submission ID. * @param array $data Fields to update. * @param int $user_id WP User ID of editor. * @return bool */ public static function update_submission( $id, $data, $user_id ) { global $wpdb; $table_sub = self::table( 'submissions' ); $old_lead = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $table_sub WHERE id = %d", $id ) ); if ( ! $old_lead ) { return false; } $allowed = array( 'status', 'priority', 'assigned_to', 'is_spam' ); $update_payload = array(); foreach ( $allowed as $key ) { if ( isset( $data[ $key ] ) ) { $update_payload[ $key ] = sanitize_text_field( $data[ $key ] ); } } if ( empty( $update_payload ) ) { return false; } $updated = $wpdb->update( $table_sub, $update_payload, array( 'id' => $id ) ); if ( false !== $updated ) { // Log activities for each changed property foreach ( $update_payload as $key => $val ) { if ( $old_lead->$key != $val ) { $old_label = $old_lead->$key; $new_label = $val; // Resolve numeric user display names for assignments if ( 'assigned_to' === $key ) { $old_user = get_userdata( $old_label ); $new_user = get_userdata( $new_label ); $old_label = $old_user ? $old_user->display_name : 'None'; $new_label = $new_user ? $new_user->display_name : 'None'; } if ( 'is_spam' === $key ) { $old_label = $old_label ? 'Spam' : 'Not Spam'; $new_label = $new_label ? 'Spam' : 'Not Spam'; } self::log_activity( $id, $user_id, $key . '_changed', sprintf( 'Changed %s from "%s" to "%s".', ucwords( str_replace( '_', ' ', $key ) ), $old_label, $new_label ) ); } } return true; } return false; } /** * Permanent deletion of a lead submission record. * * @param int $id Lead ID. * @return bool */ public static function delete_submission( $id ) { global $wpdb; $wpdb->delete( self::table( 'activity_log' ), array( 'submission_id' => $id ) ); $wpdb->delete( self::table( 'notes' ), array( 'submission_id' => $id ) ); $wpdb->delete( self::table( 'fields' ), array( 'submission_id' => $id ) ); $deleted = $wpdb->delete( self::table( 'submissions' ), array( 'id' => $id ) ); return (bool) $deleted; } /** * Run data retention purge automatically if configured. */ public static function maybe_purge_old_data() { // Run this query once daily to preserve admin loading times if ( false === get_transient( 'ahmadraza360_lm_daily_purge_check' ) ) { self::run_data_retention_purge(); set_transient( 'ahmadraza360_lm_daily_purge_check', 'checked', DAY_IN_SECONDS ); } } /** * Purges leads older than configured retention time. */ public static function run_data_retention_purge() { global $wpdb; $retention_days = intval( Ahmadraza360_LM_Settings::get( 'data_retention', 0 ) ); // 0 means keep forever if ( $retention_days <= 0 ) { return; } $table_sub = self::table( 'submissions' ); $date_limit = gmdate( 'Y-m-d H:i:s', strtotime( "-{$retention_days} days" ) ); // Fetch all IDs to delete to clean up child relations $ids_to_purge = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM $table_sub WHERE created_at < %s", $date_limit ) ); if ( ! empty( $ids_to_purge ) ) { $ids_str = implode( ',', array_map( 'intval', $ids_to_purge ) ); // Delete child table rows $wpdb->query( "DELETE FROM " . self::table( 'activity_log' ) . " WHERE submission_id IN ($ids_str)" ); $wpdb->query( "DELETE FROM " . self::table( 'notes' ) . " WHERE submission_id IN ($ids_str)" ); $wpdb->query( "DELETE FROM " . self::table( 'fields' ) . " WHERE submission_id IN ($ids_str)" ); $wpdb->query( "DELETE FROM $table_sub WHERE id IN ($ids_str)" ); } } }