prepare( "p.post_status = 'publish' AND p.post_type IN ({$ph})", $types ); if ( 'drafts' === $tab ) { $where .= " AND sp.ai_state = 'suggested'"; } elseif ( 'needs' === $tab ) { $where .= " AND ( sp.object_id IS NULL OR COALESCE(sp.description, '') = '' OR COALESCE(sp.keyphrases, '') = '' OR sp.keyphrases NOT LIKE '%additional\":[{%' )"; } $sp = $wpdb->prefix . 'aieo_seo_posts'; return "SELECT p.ID, p.post_type, p.post_title, sp.description, sp.keyphrases, sp.ai_state, sp.suggested_description, sp.suggested_title, sp.suggested_at, COALESCE(sp.search_volume_total, 0) AS demand, COALESCE(sp.striking_count, 0) AS striking, IF(COALESCE(sp.description, '') <> '', 1, 0) AS has_description FROM {$wpdb->posts} p LEFT JOIN `{$sp}` sp ON sp.object_type = p.post_type AND sp.object_id = p.ID WHERE {$where}"; } } return array( 'id' => 'seo_meta_batch_catalogue', 'label' => 'Descriptions & Keywords', 'description' => '', 'source' => 'sql', 'sql' => 'aieo_meta_batch_sql', 'registry_id' => 'seo_meta_batch.meta_batch_catalogue', 'scope' => 'local', 'version' => '1', 'per_page' => 25, 'params' => array( 'tab' => array( 'type' => 'text', 'default' => 'needs', 'options' => array( 'needs', 'drafts', 'all' ), 'label' => 'View', ), ), 'row_key' => 'ID', 'default_sort' => array( 'demand', 'desc' ), 'empty_state' => 'Nothing matches this filter. On the "Needs meta" tab that is the good answer: every published object has a description and keywords.', 'capabilities' => array( 'edit' => 'aieo_report_edit', ), 'actions' => array( array( 'id' => 'approve', 'label' => 'Approve AI', 'variant' => 'primary', 'when' => array( 'key' => 'ai_state', 'is' => 'suggested' ), 'data' => array( 'object_type' => 'post_type' ), ), array( 'id' => 'dismiss', 'label' => 'Dismiss', 'when' => array( 'key' => 'ai_state', 'is' => 'suggested' ), 'data' => array( 'object_type' => 'post_type' ), ), array( 'id' => 'ours_description', 'label' => 'Use ours: description', 'when' => array( 'key' => 'ai_state', 'not' => 'suggested' ), 'data' => array( 'object_type' => 'post_type' ), ), array( 'id' => 'ours_keywords', 'label' => 'Use ours: keywords', 'when' => array( 'key' => 'ai_state', 'not' => 'suggested' ), 'data' => array( 'object_type' => 'post_type' ), ), array( 'id' => 'ai_draft', 'label' => 'AI draft', 'when' => array( 'key' => 'ai_available', 'is' => '1' ), 'data' => array( 'object_type' => 'post_type' ), ), ), 'columns' => array( array( 'key' => 'ID', 'label' => 'Content', 'type' => 'object', 'sortable' => true, 'sort_key' => 'post_title', 'truncate' => 60, 'tooltip' => true, ), array( 'key' => 'post_title', 'label' => 'Title', 'type' => 'text', 'searchable' => true, 'hidden' => true, ), array( 'key' => 'post_type', 'label' => 'Type', 'type' => 'badge', 'width' => '110px', 'sortable' => true, 'filter' => 'select', ), array( 'key' => 'demand', 'label' => 'Search volume', 'type' => 'integer', 'width' => '120px', 'sortable' => true, 'filter' => 'number_range', 'aggregate' => 'SUM', ), array( 'key' => 'striking', 'label' => 'Striking distance', 'type' => 'integer', 'width' => '120px', 'sortable' => true, 'filter' => 'number_range', 'empty_zero' => true, 'bands' => array( array( 'min' => 1, 'tone' => 'warning' ) ), 'header_tip' => 'Keywords ranking just off page one, where a description or keyword fix pays off fastest.', ), array( 'key' => 'has_description', 'label' => 'Has description', 'type' => 'boolean', 'width' => '90px', 'align' => 'center', 'sortable' => true, 'filter' => 'select', ), array( 'key' => 'keyword_count', 'select' => '0', 'label' => 'Keywords', 'type' => 'integer', 'width' => '90px', 'sortable' => false, 'empty_zero' => true, 'bands' => array( array( 'min' => 0, 'max' => 0, 'tone' => 'danger' ) ), ), array( 'key' => 'description', 'label' => 'Description now', 'type' => 'text', 'width' => 'md', 'sortable' => false, 'truncate' => 70, 'tooltip' => true, 'empty' => '', ), array( 'key' => 'ours_description', 'select' => "''", 'label' => 'Ours (free)', 'type' => 'text', 'width' => 'md', 'sortable' => false, 'truncate' => 70, 'tooltip' => true, 'empty' => '', ), array( 'key' => 'ours_keywords', 'select' => "''", 'label' => 'Ours: keywords', 'type' => 'list', 'sortable' => false, 'empty' => '', 'format' => array( 'max' => 6 ), ), array( 'key' => 'suggested_description', 'label' => 'AI draft', 'type' => 'text', 'width' => 'md', 'sortable' => false, 'truncate' => 70, 'tooltip' => true, 'empty' => '', ), array( 'key' => 'ai_state', 'label' => 'AI', 'type' => 'badge', 'width' => '110px', 'sortable' => true, 'filter' => 'select', 'select' => "NULLIF(`ai_state`, 'none')", 'empty' => '', 'format' => array( 'labels' => array( 'suggested' => 'draft pending', 'approved' => 'approved', 'dismissed' => 'dismissed', ), 'map' => array( 'suggested' => 'info', 'approved' => 'success', 'dismissed' => 'muted', ), ), ), array( 'key' => 'suggested_at', 'label' => 'Drafted', 'type' => 'datetime', 'width' => '140px', 'nowrap' => true, 'tone' => 'secondary', 'sortable' => true, 'empty' => '', 'format' => array( 'tz' => 'utc' ), 'hidden' => true, ), array( 'key' => 'suggested_title', 'label' => 'AI title', 'type' => 'text', 'hidden' => true, ), array( 'key' => 'keyphrases', 'label' => 'Keyphrases (raw)', 'type' => 'code', 'hidden' => true, ), array( 'key' => 'ai_available', 'select' => "''", 'label' => 'AI available', 'type' => 'text', 'hidden' => true, ), ), );