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

global $wpdb;

$is_basic_tier = class_exists( 'SftcyWordaiFeatures' ) && SftcyWordaiFeatures::wordai_is_basic_tier();
$limits        = class_exists( 'SftcyWordaiFeatures' ) ? SftcyWordaiFeatures::wordai_get_feature_limits( 'chat_analytics' ) : array();
$history_limit = $is_basic_tier ? max( 1, (int) ( $limits['historyItems'] ?? 10 ) ) : 50;
$history_days  = $is_basic_tier ? max( 1, (int) ( $limits['historyDays'] ?? 7 ) ) : 30;
$gap_limit     = $is_basic_tier ? max( 1, (int) ( $limits['knowledgeGaps'] ?? 3 ) ) : 20;
$upgrade_url   = class_exists( 'SftcyWordaiFeatures' ) ? SftcyWordaiFeatures::wordai_get_upgrade_url() : 'https://softcoy.com/products/wordai-pro/checkout';

$tbl_convos   = $wpdb->prefix . 'wordai_agent_conversations';
$tbl_msgs     = $wpdb->prefix . 'wordai_agent_messages';
$tbl_sources  = $wpdb->prefix . 'wordai_agent_message_sources';
$tbl_feedback = $wpdb->prefix . 'wordai_agent_feedback';
$tbl_leads    = $wpdb->prefix . 'wordai_agent_leads';
$date_floor   = date( 'Y-m-d H:i:s', current_time( 'timestamp' ) - ( DAY_IN_SECONDS * $history_days ) );

$table_exists = static function( $table ) use ( $wpdb ) {
	return $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table;
};

$column_exists = static function( $table, $column ) use ( $wpdb ) {
	return (bool) $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", $column ) );
};

if (
	class_exists( 'SftcyWordaiOpenaiAgentInstall' )
	&& (
		! $table_exists( $tbl_convos )
		|| ! $table_exists( $tbl_msgs )
		|| ! $table_exists( $tbl_leads )
		|| ! $column_exists( $tbl_convos, 'page_url' )
		|| ! $column_exists( $tbl_msgs, 'fallback_used' )
	)
) {
	SftcyWordaiOpenaiAgentInstall::create_or_update_tables();
}

if ( ! $table_exists( $tbl_convos ) || ! $table_exists( $tbl_msgs ) ) :
	?>
	<div class="wordai-card wordai-analytics-empty">
		<h3><span class="dashicons dashicons-chart-area"></span> <?php esc_html_e( 'AI Chat Analytics', 'wordai-pro' ); ?></h3>
		<p><?php esc_html_e( 'Analytics tables are not ready yet. Revisit this page after the plugin update routine finishes or reactivate the plugin once on this development site.', 'wordai-pro' ); ?></p>
	</div>
	<?php
	return;
endif;

$where_date = 'WHERE created_at >= %s';
$lead_table_ready = $table_exists( $tbl_leads );

$total_conversations = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$tbl_convos} {$where_date}", $date_floor ) );
$total_messages      = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$tbl_msgs} {$where_date}", $date_floor ) );
$ai_replies          = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$tbl_msgs} WHERE role = 'assistant' AND created_at >= %s", $date_floor ) );
$fallbacks           = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$tbl_msgs} WHERE role = 'assistant' AND fallback_used = 1 AND created_at >= %s", $date_floor ) );
$helpful             = $table_exists( $tbl_feedback ) ? (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$tbl_feedback} WHERE rating = 1 AND created_at >= %s", $date_floor ) ) : 0;
$not_helpful         = $table_exists( $tbl_feedback ) ? (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$tbl_feedback} WHERE rating = -1 AND created_at >= %s", $date_floor ) ) : 0;
$lead_count          = $lead_table_ready ? (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$tbl_leads} WHERE created_at >= %s", $date_floor ) ) : 0;
$lead_conversion     = $total_conversations > 0 ? round( ( $lead_count / $total_conversations ) * 100, 1 ) : 0;

$recent = $wpdb->get_results(
	$wpdb->prepare(
		"SELECT c.id, c.session_key, c.page_url, c.page_title, c.provider, c.model, c.status, c.last_source_count, c.last_feedback, c.updated_at,
			(SELECT content FROM {$tbl_msgs} m WHERE m.conversation_id = c.id AND m.role = 'user' ORDER BY m.id DESC LIMIT 1) AS last_question,
			(SELECT content FROM {$tbl_msgs} m WHERE m.conversation_id = c.id AND m.role = 'assistant' ORDER BY m.id DESC LIMIT 1) AS last_answer,
			(SELECT id FROM {$tbl_msgs} m WHERE m.conversation_id = c.id AND m.role = 'assistant' ORDER BY m.id DESC LIMIT 1) AS assistant_message_id
		FROM {$tbl_convos} c
		WHERE c.updated_at >= %s
		ORDER BY c.updated_at DESC
		LIMIT %d",
		$date_floor,
		$history_limit
	),
	ARRAY_A
);

$gaps = $wpdb->get_results(
	$wpdb->prepare(
		"SELECT
			(SELECT u.content FROM {$tbl_msgs} u WHERE u.conversation_id = m.conversation_id AND u.role = 'user' AND u.id < m.id ORDER BY u.id DESC LIMIT 1) AS content,
			m.page_url,
			m.provider,
			m.model,
			m.created_at
		FROM {$tbl_msgs} m
		WHERE m.role = 'assistant' AND m.fallback_used = 1 AND m.created_at >= %s
		ORDER BY m.created_at DESC
		LIMIT %d",
		$date_floor,
		$gap_limit
	),
	ARRAY_A
);

$top_pages = $wpdb->get_results(
	$wpdb->prepare(
		"SELECT page_url, page_title, COUNT(*) AS total
		FROM {$tbl_convos}
		WHERE updated_at >= %s AND page_url <> ''
		GROUP BY page_url, page_title
		ORDER BY total DESC
		LIMIT 8",
		$date_floor
	),
	ARRAY_A
);

$source_usage = $table_exists( $tbl_sources ) ? $wpdb->get_results(
	$wpdb->prepare(
		"SELECT source_id, source_title, source_url, COUNT(*) AS total
		FROM {$tbl_sources}
		WHERE created_at >= %s
		GROUP BY source_id, source_title, source_url
		ORDER BY total DESC
		LIMIT 8",
		$date_floor
	),
	ARRAY_A
) : array();

$recent_leads = $lead_table_ready ? $wpdb->get_results(
	$wpdb->prepare(
		"SELECT id, conversation_id, name, email, phone, company, message, consent_given, source_page_url, source_page_title, first_question, last_question, status, created_at, updated_at
		FROM {$tbl_leads}
		WHERE updated_at >= %s
		ORDER BY updated_at DESC, id DESC
		LIMIT %d",
		$date_floor,
		$history_limit
	),
	ARRAY_A
) : array();

$lead_statuses = array(
	'new'       => __( 'New', 'wordai-pro' ),
	'contacted' => __( 'Contacted', 'wordai-pro' ),
	'qualified' => __( 'Qualified', 'wordai-pro' ),
	'won'       => __( 'Won', 'wordai-pro' ),
	'closed'    => __( 'Closed', 'wordai-pro' ),
);
$export_url = wp_nonce_url( admin_url( 'admin-ajax.php?action=sc_wordai_agent_export_leads_csv' ), 'scwordai_wpnonce', 'security' );
?>

<div id="wordai-chat-analytics-wrapper" class="wordai-chat-analytics-wrapper">
	<div class="wordai-analytics-header wordai-card">
		<div>
			<h3><span class="dashicons dashicons-chart-area"></span> <?php esc_html_e( 'AI Chat Analytics', 'wordai-pro' ); ?></h3>
			<p><?php echo esc_html( sprintf( __( 'Showing reports for the last %d days.', 'wordai-pro' ), $history_days ) ); ?></p>
		</div>
		<div class="wordai-analytics-actions">
			<button type="button" class="button button-secondary" onclick="window.location.reload();"><span class="dashicons dashicons-update"></span><?php esc_html_e( 'Refresh', 'wordai-pro' ); ?></button>
			<?php if ( $is_basic_tier ) : ?>
				<a class="button button-primary" href="<?php echo esc_url( $upgrade_url ); ?>" target="_blank" rel="noopener"><span class="dashicons dashicons-star-filled"></span><?php esc_html_e( 'Upgrade for Full Analytics', 'wordai-pro' ); ?></a>
			<?php else : ?>
				<a class="button button-secondary" href="<?php echo esc_url( $export_url ); ?>"><span class="dashicons dashicons-download"></span><?php esc_html_e( 'Export Leads CSV', 'wordai-pro' ); ?></a>
			<?php endif; ?>
		</div>
	</div>

	<?php if ( $is_basic_tier ) : ?>
		<div class="wordai-analytics-upsell">
			<strong><?php esc_html_e( 'Free analytics preview is active.', 'wordai-pro' ); ?></strong>
			<?php echo esc_html( sprintf( __( 'You can review the last %d chats, %d days of totals, top %d knowledge gaps, and name-only lead capture. Pro unlocks full contact fields, full history, source citations for visitors, exports, handoff reports, and deeper filtering.', 'wordai-pro' ), $history_limit, $history_days, $gap_limit ) ); ?>
			<a href="<?php echo esc_url( $upgrade_url ); ?>" target="_blank" rel="noopener"><?php esc_html_e( 'Upgrade to Pro', 'wordai-pro' ); ?></a>
		</div>
	<?php endif; ?>

	<div class="wordai-analytics-metric-grid">
		<div class="wordai-analytics-metric"><span><?php esc_html_e( 'Chats', 'wordai-pro' ); ?></span><strong><?php echo esc_html( $total_conversations ); ?></strong></div>
		<div class="wordai-analytics-metric"><span><?php esc_html_e( 'Messages', 'wordai-pro' ); ?></span><strong><?php echo esc_html( $total_messages ); ?></strong></div>
		<div class="wordai-analytics-metric"><span><?php esc_html_e( 'AI Replies', 'wordai-pro' ); ?></span><strong><?php echo esc_html( $ai_replies ); ?></strong></div>
		<div class="wordai-analytics-metric"><span><?php esc_html_e( 'Leads', 'wordai-pro' ); ?></span><strong><?php echo esc_html( $lead_count ); ?></strong></div>
		<div class="wordai-analytics-metric"><span><?php esc_html_e( 'Conversion', 'wordai-pro' ); ?></span><strong><?php echo esc_html( $lead_conversion ); ?>%</strong></div>
		<div class="wordai-analytics-metric wordai-analytics-warning"><span><?php esc_html_e( 'Knowledge Gaps', 'wordai-pro' ); ?></span><strong><?php echo esc_html( $fallbacks ); ?></strong></div>
		<div class="wordai-analytics-metric"><span><?php esc_html_e( 'Helpful', 'wordai-pro' ); ?></span><strong><?php echo esc_html( $helpful ); ?></strong></div>
		<div class="wordai-analytics-metric"><span><?php esc_html_e( 'Not Helpful', 'wordai-pro' ); ?></span><strong><?php echo esc_html( $not_helpful ); ?></strong></div>
	</div>

	<div class="wordai-analytics-two-col">
		<div class="wordai-card wordai-analytics-panel">
			<h4><?php esc_html_e( 'Knowledge Gaps', 'wordai-pro' ); ?></h4>
			<?php if ( empty( $gaps ) ) : ?>
				<p class="description"><?php esc_html_e( 'No unanswered or fallback replies recorded in this period.', 'wordai-pro' ); ?></p>
			<?php else : ?>
				<ul class="wordai-analytics-list">
					<?php foreach ( $gaps as $gap ) : ?>
						<li>
							<strong><?php echo esc_html( wp_trim_words( wp_strip_all_tags( $gap['content'] ), 16 ) ); ?></strong>
							<span><?php echo esc_html( strtoupper( $gap['provider'] ) . ' • ' . mysql2date( 'M j, Y g:i A', $gap['created_at'] ) ); ?></span>
						</li>
					<?php endforeach; ?>
				</ul>
			<?php endif; ?>
		</div>
		<div class="wordai-card wordai-analytics-panel">
			<h4><?php esc_html_e( 'Top Pages', 'wordai-pro' ); ?></h4>
			<?php if ( empty( $top_pages ) ) : ?>
				<p class="description"><?php esc_html_e( 'No page activity recorded yet.', 'wordai-pro' ); ?></p>
			<?php else : ?>
				<ul class="wordai-analytics-list">
					<?php foreach ( $top_pages as $page ) : ?>
						<li>
							<a href="<?php echo esc_url( $page['page_url'] ); ?>" target="_blank" rel="noopener"><?php echo esc_html( $page['page_title'] ?: $page['page_url'] ); ?></a>
							<span><?php echo esc_html( sprintf( _n( '%d chat', '%d chats', (int) $page['total'], 'wordai-pro' ), (int) $page['total'] ) ); ?></span>
						</li>
					<?php endforeach; ?>
				</ul>
			<?php endif; ?>
		</div>
	</div>

	<div class="wordai-card wordai-analytics-panel">
		<div class="wordai-analytics-panel-heading">
			<div>
				<h4><?php esc_html_e( 'Captured Leads and Interested Visitors', 'wordai-pro' ); ?></h4>
				<p class="description"><?php esc_html_e( 'Visitors captured from the front-end AI chat assistant. Keep the table bounded for performance; export CSV in Pro for deeper follow-up.', 'wordai-pro' ); ?></p>
			</div>
			<?php if ( $is_basic_tier ) : ?>
				<a class="button button-primary wordai-analytics-small-cta" href="<?php echo esc_url( $upgrade_url ); ?>" target="_blank" rel="noopener"><span class="dashicons dashicons-star-filled"></span><?php esc_html_e( 'Unlock Contact Fields', 'wordai-pro' ); ?></a>
			<?php endif; ?>
		</div>
		<div class="wordai-analytics-table-wrap">
			<table class="widefat striped wordai-analytics-table wordai-leads-table">
				<thead>
					<tr>
						<th><?php esc_html_e( 'Visitor', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Contact', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Source Page', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Latest Question', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Status', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Updated', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Actions', 'wordai-pro' ); ?></th>
					</tr>
				</thead>
				<tbody>
					<?php if ( empty( $recent_leads ) ) : ?>
						<tr><td colspan="7"><?php esc_html_e( 'No captured leads yet. Enable Visitor Lead Capture in AI Chat Settings to start collecting interested visitor details.', 'wordai-pro' ); ?></td></tr>
					<?php else : ?>
						<?php foreach ( $recent_leads as $lead ) : ?>
							<tr data-lead-id="<?php echo esc_attr( (int) $lead['id'] ); ?>">
								<td>
									<strong><?php echo esc_html( $lead['name'] ?: __( 'Anonymous visitor', 'wordai-pro' ) ); ?></strong>
									<br><small><?php echo esc_html( sprintf( __( 'Conversation #%d', 'wordai-pro' ), (int) $lead['conversation_id'] ) ); ?></small>
								</td>
								<td>
									<?php if ( ! empty( $lead['email'] ) ) : ?>
										<a href="mailto:<?php echo esc_attr( $lead['email'] ); ?>"><?php echo esc_html( $lead['email'] ); ?></a><br>
									<?php elseif ( $is_basic_tier ) : ?>
										<span class="wordai-muted"><?php esc_html_e( 'Email capture in Pro', 'wordai-pro' ); ?></span><br>
									<?php endif; ?>
									<?php if ( ! empty( $lead['phone'] ) ) : ?>
										<span><?php echo esc_html( $lead['phone'] ); ?></span><br>
									<?php elseif ( $is_basic_tier ) : ?>
										<span class="wordai-muted"><?php esc_html_e( 'Phone capture in Pro', 'wordai-pro' ); ?></span><br>
									<?php endif; ?>
									<?php if ( ! empty( $lead['company'] ) ) : ?>
										<small><?php echo esc_html( $lead['company'] ); ?></small>
									<?php endif; ?>
								</td>
								<td><a href="<?php echo esc_url( $lead['source_page_url'] ); ?>" target="_blank" rel="noopener"><?php echo esc_html( wp_trim_words( $lead['source_page_title'] ?: $lead['source_page_url'], 9 ) ); ?></a></td>
								<td>
									<?php echo esc_html( wp_trim_words( wp_strip_all_tags( $lead['last_question'] ?: $lead['first_question'] ), 18 ) ); ?>
									<?php if ( ! empty( $lead['message'] ) ) : ?>
										<br><small><?php echo esc_html( wp_trim_words( wp_strip_all_tags( $lead['message'] ), 12 ) ); ?></small>
									<?php endif; ?>
								</td>
								<td>
									<select class="wordai-lead-status-select" <?php disabled( $is_basic_tier ); ?>>
										<?php foreach ( $lead_statuses as $status_key => $status_label ) : ?>
											<option value="<?php echo esc_attr( $status_key ); ?>" <?php selected( $lead['status'], $status_key ); ?>><?php echo esc_html( $status_label ); ?></option>
										<?php endforeach; ?>
									</select>
								</td>
								<td><?php echo esc_html( mysql2date( 'M j, Y g:i A', $lead['updated_at'] ?: $lead['created_at'] ) ); ?></td>
								<td>
									<div class="wordai-lead-actions">
										<?php if ( ! $is_basic_tier ) : ?>
											<button type="button" class="button button-small wordai-lead-status-save"><?php esc_html_e( 'Save', 'wordai-pro' ); ?></button>
											<button type="button" class="button button-small wordai-lead-delete"><?php esc_html_e( 'Delete', 'wordai-pro' ); ?></button>
										<?php else : ?>
											<a class="button button-small" href="<?php echo esc_url( $upgrade_url ); ?>" target="_blank" rel="noopener"><?php esc_html_e( 'Upgrade', 'wordai-pro' ); ?></a>
										<?php endif; ?>
									</div>
								</td>
							</tr>
						<?php endforeach; ?>
					<?php endif; ?>
				</tbody>
			</table>
		</div>
	</div>

	<div class="wordai-card wordai-analytics-panel">
		<h4><?php esc_html_e( 'Recent Conversations', 'wordai-pro' ); ?></h4>
		<div class="wordai-analytics-table-wrap">
			<table class="widefat striped wordai-analytics-table">
				<thead>
					<tr>
						<th><?php esc_html_e( 'Question', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Answer Preview', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Page', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Provider', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Sources', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Feedback', 'wordai-pro' ); ?></th>
						<th><?php esc_html_e( 'Updated', 'wordai-pro' ); ?></th>
					</tr>
				</thead>
				<tbody>
					<?php if ( empty( $recent ) ) : ?>
						<tr><td colspan="7"><?php esc_html_e( 'No chat conversations recorded yet.', 'wordai-pro' ); ?></td></tr>
					<?php else : ?>
						<?php foreach ( $recent as $row ) : ?>
							<tr>
								<td><?php echo esc_html( wp_trim_words( wp_strip_all_tags( $row['last_question'] ?? '' ), 16 ) ); ?></td>
								<td><?php echo esc_html( wp_trim_words( wp_strip_all_tags( $row['last_answer'] ?? '' ), 18 ) ); ?></td>
								<td><a href="<?php echo esc_url( $row['page_url'] ); ?>" target="_blank" rel="noopener"><?php echo esc_html( wp_trim_words( $row['page_title'] ?: $row['page_url'], 8 ) ); ?></a></td>
								<td><?php echo esc_html( strtoupper( $row['provider'] ) ); ?><br><small><?php echo esc_html( $row['model'] ); ?></small></td>
								<td><?php echo esc_html( (int) $row['last_source_count'] ); ?></td>
								<td><?php echo (int) $row['last_feedback'] > 0 ? esc_html__( 'Helpful', 'wordai-pro' ) : ( (int) $row['last_feedback'] < 0 ? esc_html__( 'Not helpful', 'wordai-pro' ) : esc_html__( 'None', 'wordai-pro' ) ); ?></td>
								<td><?php echo esc_html( mysql2date( 'M j, Y g:i A', $row['updated_at'] ) ); ?></td>
							</tr>
						<?php endforeach; ?>
					<?php endif; ?>
				</tbody>
			</table>
		</div>
	</div>

	<div class="wordai-card wordai-analytics-panel">
		<h4><?php esc_html_e( 'Most Used Sources', 'wordai-pro' ); ?></h4>
		<?php if ( empty( $source_usage ) ) : ?>
			<p class="description"><?php esc_html_e( 'No source usage has been recorded yet. Once visitors ask questions answered from embedded content, sources will appear here.', 'wordai-pro' ); ?></p>
		<?php else : ?>
			<ul class="wordai-analytics-list wordai-analytics-source-list">
				<?php foreach ( $source_usage as $source ) : ?>
					<li>
						<a href="<?php echo esc_url( $source['source_url'] ); ?>" target="_blank" rel="noopener"><?php echo esc_html( $source['source_title'] ?: sprintf( __( 'Source #%d', 'wordai-pro' ), (int) $source['source_id'] ) ); ?></a>
						<span><?php echo esc_html( sprintf( _n( 'Used %d time', 'Used %d times', (int) $source['total'], 'wordai-pro' ), (int) $source['total'] ) ); ?></span>
					</li>
				<?php endforeach; ?>
			</ul>
		<?php endif; ?>
	</div>
</div>
