<?php
/**
 * Dashboard APIs for Chatbot AI.
 *
 * @package Replyan_AI
 */

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

final class Replyan_AI_Dashboard {
	public function register_hooks(): void {
		add_action( 'wp_ajax_replyan_ai_get_dashboard_metrics', array( $this, 'get_dashboard_metrics' ) );
	}

	public function get_dashboard_metrics(): void {
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized.' ), 403 );
		}

		// phpcs:ignore WordPress.Security.NonceVerification.Missing
		$nonce = isset( $_POST['nonce'] ) ? sanitize_text_field( wp_unslash( $_POST['nonce'] ) ) : '';
		if ( ! wp_verify_nonce( $nonce, 'replyan_ai_admin_settings' ) ) {
			wp_send_json_error( array( 'message' => 'Invalid nonce.' ), 403 );
		}

		global $wpdb;
		$sessions_table = $wpdb->prefix . 'replyan_ai_sessions';
		$leads_table    = $wpdb->prefix . 'replyan_ai_leads';
		$forms_sub_tbl  = $wpdb->prefix . 'replyan_form_submissions';
		$agents_table   = $wpdb->prefix . 'replyan_ai_agents';
		$forms_table    = $wpdb->prefix . 'replyan_forms';
		$messages_table = $wpdb->prefix . 'replyan_ai_messages';

		// Top Section Metrics
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total_conversations = (int) $wpdb->get_var( "SELECT COUNT(id) FROM {$sessions_table}" );
		$total_leads         = (int) $wpdb->get_var( "SELECT COUNT(id) FROM {$leads_table}" );
		$unread_messages     = (int) $wpdb->get_var( "SELECT SUM(unread_count_admin) FROM {$sessions_table}" );
		$human_takeover      = (int) $wpdb->get_var( "SELECT COUNT(id) FROM {$sessions_table} WHERE is_manual_mode = 1" );
		$forms_submitted     = (int) $wpdb->get_var( "SELECT COUNT(id) FROM {$forms_sub_tbl}" );
		$total_agents        = (int) $wpdb->get_var( "SELECT COUNT(id) FROM {$agents_table}" );

		// Recent Conversations
		$recent_conversations = $wpdb->get_results( "
			SELECT s.*, a.agent_name 
			FROM {$sessions_table} s 
			LEFT JOIN {$agents_table} a ON s.agent_id = a.id 
			ORDER BY s.last_message_at DESC LIMIT 5
		", ARRAY_A );

		if ( $recent_conversations ) {
			foreach ( $recent_conversations as &$conv ) {
				$last_msg = $wpdb->get_row( $wpdb->prepare( "SELECT content, role FROM {$messages_table} WHERE session_id = %s ORDER BY created_at DESC LIMIT 1", $conv['session_id'] ), ARRAY_A );
				$conv['last_message'] = $last_msg ? $last_msg['content'] : '';
				$conv['last_message_role'] = $last_msg ? $last_msg['role'] : '';
			}
		}

		// Recent Leads
		// Check if replyan_form_submissions and replyan_forms exist
		$recent_leads = array();
		$form_performance = array();
		$forms_exist = $wpdb->get_var("SHOW TABLES LIKE '{$forms_table}'") === $forms_table;

		if ($forms_exist) {
			$recent_leads = $wpdb->get_results( "
				SELECT l.*, f.name as form_name 
				FROM {$leads_table} l 
				LEFT JOIN {$forms_sub_tbl} s ON l.session_id = s.session_id 
				LEFT JOIN {$forms_table} f ON s.form_id = f.id 
				ORDER BY l.created_at DESC LIMIT 5
			", ARRAY_A );

			// Form Performance
			$form_performance = $wpdb->get_results( "
				SELECT f.name, COUNT(s.id) as total_submissions 
				FROM {$forms_table} f 
				LEFT JOIN {$forms_sub_tbl} s ON f.id = s.form_id 
				GROUP BY f.id
			", ARRAY_A );
		} else {
			$recent_leads = $wpdb->get_results( "SELECT * FROM {$leads_table} ORDER BY created_at DESC LIMIT 5", ARRAY_A );
		}

		// Agent Performance
		$agent_performance = $wpdb->get_results( "
			SELECT 
				a.id,
				a.agent_name, 
				COUNT(s.id) as total_conversations, 
				SUM(CASE WHEN s.is_manual_mode = 1 THEN 1 ELSE 0 END) as manual_takeovers 
			FROM {$agents_table} a 
			LEFT JOIN {$sessions_table} s ON a.id = s.agent_id 
			GROUP BY a.id, a.agent_name
		", ARRAY_A );

		if ( $agent_performance ) {
			foreach ( $agent_performance as &$ap ) {
				// Leads per agent
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$ap['leads_captured'] = (int) $wpdb->get_var( $wpdb->prepare( "
					SELECT COUNT(l.id) 
					FROM {$leads_table} l 
					INNER JOIN {$sessions_table} s ON l.session_id = s.session_id 
					WHERE s.agent_id = %d
				", $ap['id'] ) );

				if ($forms_exist) {
					// Forms per agent
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$ap['form_submissions'] = (int) $wpdb->get_var( $wpdb->prepare( "
						SELECT COUNT(fs.id) 
						FROM {$forms_sub_tbl} fs 
						INNER JOIN {$sessions_table} s ON fs.session_id = s.session_id 
						WHERE s.agent_id = %d
					", $ap['id'] ) );
				} else {
					$ap['form_submissions'] = 0;
				}
			}
		}

		// phpcs:enable

		wp_send_json_success( array(
			'top_metrics' => array(
				'total_conversations' => $total_conversations,
				'total_leads'         => $total_leads,
				'unread_messages'     => $unread_messages,
				'human_takeover'      => $human_takeover,
				'forms_submitted'     => $forms_submitted,
				'total_agents'        => $total_agents,
			),
			'recent_conversations' => $recent_conversations,
			'recent_leads'         => $recent_leads,
			'form_performance'     => $form_performance,
			'agent_performance'    => $agent_performance,
		) );
	}
}
