<?php
/**
 * Admin view query service.
 *
 * Centralizes read-only SQL for admin templates so view files
 * can focus on rendering.
 *
 * @package    SiteStaffr
 * @subpackage SiteStaffr/admin
 * @since      0.14.252
 */

if ( ! defined( 'WPINC' ) ) {
	die;
}

/**
 * Read-only data access for admin view templates.
 */
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin table names are internally derived from $wpdb->prefix.
class SiteStaffr_Admin_View_Query_Service {

	/**
	 * WordPress DB handle.
	 *
	 * @var wpdb
	 */
	private $wpdb;

	/**
	 * Calls table name.
	 *
	 * @var string
	 */
	private $calls_table;

	/**
	 * Transcripts table name.
	 *
	 * @var string
	 */
	private $transcripts_table;

	/**
	 * Billing snapshots table name.
	 *
	 * @var string
	 */
	private $snapshot_table;

	/**
	 * Constructor.
	 *
	 * @param wpdb|null $db_handle Optional DB handle for tests.
	 */
	public function __construct( $db_handle = null ) {
		global $wpdb;

		$this->wpdb = $db_handle ? $db_handle : $wpdb;

		$this->calls_table       = $this->wpdb->prefix . 'phoneease_calls';
		$this->transcripts_table = $this->wpdb->prefix . 'phoneease_transcripts';
		$this->snapshot_table    = $this->wpdb->prefix . 'phoneease_billing_cycle_snapshots';
	}

	/*
	 * Dynamic table identifiers are built from trusted $wpdb->prefix values.
	 * Variable data is always passed via placeholders in $wpdb->prepare().
	 */
	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

	/**
	 * Get dashboard counts for a billing period.
	 *
	 * @param string $period_start_dt Period start (UTC datetime).
	 * @param string $period_end_dt   Period end (UTC datetime).
	 * @return array<string,int>
	 */
	public function get_dashboard_period_counts( $period_start_dt, $period_end_dt ) {
		// One conditional-aggregate pass instead of three COUNT queries —
		// the dashboard strip calls this twice per load (current + prev cycle).
		// Voice/Text CASE lists mirror SiteStaffr_Call_Manager::channel_where_fragment
		// (the source of truth for channel → service_type mapping).
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT COUNT(*) AS total_calls,
				        SUM(CASE WHEN is_billable = 1 THEN 1 ELSE 0 END) AS billable_calls,
				        SUM(CASE WHEN call_status = 'completed' THEN 1 ELSE 0 END) AS answered_calls,
				        SUM(CASE WHEN service_type IN ('webrtc','webrtc_widget','webrtc_button','phone') THEN 1 ELSE 0 END) AS voice_calls,
				        SUM(CASE WHEN service_type IN ('chat_widget','chat_button') THEN 1 ELSE 0 END) AS text_calls
				 FROM {$this->calls_table}
				 WHERE created_at >= %s
				 AND created_at <= %s
				 AND (minute_quota_exceeded = 0 OR minute_quota_exceeded IS NULL)
				 AND (is_test_call = 0 OR is_test_call IS NULL)",
				$period_start_dt,
				$period_end_dt
			),
			ARRAY_A
		);

		return array(
			'total_calls'    => isset( $row['total_calls'] ) ? (int) $row['total_calls'] : 0,
			'billable_calls' => isset( $row['billable_calls'] ) ? (int) $row['billable_calls'] : 0,
			'answered_calls' => isset( $row['answered_calls'] ) ? (int) $row['answered_calls'] : 0,
			'voice_calls'    => isset( $row['voice_calls'] ) ? (int) $row['voice_calls'] : 0,
			'text_calls'     => isset( $row['text_calls'] ) ? (int) $row['text_calls'] : 0,
		);
	}

	/**
	 * Get calls used by dashboard usage projection.
	 *
	 * @param string $lookback_start_dt Inclusive lookback window start.
	 * @return array<int,object>
	 */
	public function get_projection_usage_calls_since( $lookback_start_dt ) {
		// Burn-rate projection is voice-only, matching the voice-only minute
		// balance it is compared against; text chats must not inflate the pace.
		$voice_only = SiteStaffr_Call_Manager::voice_minutes_where_sql();

		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT COALESCE(NULLIF(billable_minutes, 0), GREATEST(0, FLOOR(call_duration / 60))) AS effective_billable_minutes, created_at
				 FROM {$this->calls_table}
				 WHERE created_at >= %s
				 AND call_duration >= 30
				 AND {$voice_only}
				 ORDER BY created_at ASC",
				$lookback_start_dt
			)
		);
	}

	/**
	 * Get the previous/next conversation id relative to a call (detail-page
	 * navigation). "Previous" = the next-older conversation, "next" = the
	 * next-newer one, within the originating tab's scope.
	 *
	 * @param object $call       Current call row (needs id + created_at).
	 * @param string $direction  'prev' (older) or 'next' (newer).
	 * @param bool   $leads_only Restrict to qualified leads (Leads tab context).
	 * @return int 0 when there is no adjacent conversation.
	 */
	public function get_adjacent_call_id( $call, $direction, $leads_only = false ) {
		$older = ( 'prev' === $direction );

		$scope = 'deleted_at IS NULL AND purged_at IS NULL';
		if ( $leads_only ) {
			$scope .= " AND is_qualified_lead = 1 AND persona != 'onboarding'";
		}

		// created_at ties broken by id so navigation is total-ordered.
		$comparison = $older
			? '(created_at < %s OR (created_at = %s AND id < %d))'
			: '(created_at > %s OR (created_at = %s AND id > %d))';
		$ordering = $older ? 'ORDER BY created_at DESC, id DESC' : 'ORDER BY created_at ASC, id ASC';

		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT id FROM {$this->calls_table}
				 WHERE {$scope} AND {$comparison}
				 {$ordering}
				 LIMIT 1",
				$call->created_at,
				$call->created_at,
				$call->id
			)
		);
	}

	/**
	 * Get transcript rows for a call.
	 *
	 * @param int $call_id Call ID.
	 * @return array<int,object>
	 */
	public function get_call_transcripts( $call_id ) {
		$call_id = absint( $call_id );
		if ( $call_id < 1 ) {
			return array();
		}

		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->transcripts_table} WHERE call_id = %d ORDER BY id ASC",
				$call_id
			)
		);
	}

	/**
	 * Get monthly activity anchors for usage cycle navigation.
	 *
	 * @param string $before_dt Upper bound datetime.
	 * @return array<int,object>
	 */
	public function get_cycle_activity_rows( $before_dt ) {
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT MIN(created_at) AS first_call_at
				 FROM {$this->calls_table}
				 WHERE (is_test_call = 0 OR is_test_call IS NULL)
				 AND created_at <= %s
				 GROUP BY YEAR(created_at), MONTH(created_at)",
				$before_dt
			)
		);
	}

	/**
	 * Check whether billing cycle snapshots table exists.
	 *
	 * @return bool
	 */
	public function billing_snapshot_table_exists() {
		$table_match = $this->wpdb->get_var(
			$this->wpdb->prepare( 'SHOW TABLES LIKE %s', $this->wpdb->esc_like( $this->snapshot_table ) )
		);

		return $table_match === $this->snapshot_table;
	}

	/**
	 * Get latest distinct billing cycle period starts from snapshots.
	 *
	 * @param int $limit Maximum number of rows.
	 * @return array<int,object>
	 */
	public function get_billing_snapshot_cycle_period_starts( $limit = 60 ) {
		if ( ! $this->billing_snapshot_table_exists() ) {
			return array();
		}

		$limit = max( 1, absint( $limit ) );

		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT DISTINCT period_start FROM {$this->snapshot_table} ORDER BY period_start DESC LIMIT %d",
				$limit
			)
		);
	}

	/**
	 * Get a historical snapshot for a selected billing cycle.
	 *
	 * @param string $period_start_dt Cycle start datetime.
	 * @param string $period_end_dt   Cycle end datetime.
	 * @return array<string,mixed>
	 */
	public function get_billing_cycle_snapshot( $period_start_dt, $period_end_dt ) {
		if ( ! $this->billing_snapshot_table_exists() ) {
			return array();
		}

		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT plan_name, subscription_status, included_minutes_remaining, addon_minutes_remaining
				 FROM {$this->snapshot_table}
				 WHERE period_start = %s
				 AND period_end = %s
				 ORDER BY captured_at DESC
				 LIMIT 1",
				$period_start_dt,
				$period_end_dt
			),
			ARRAY_A
		);

		return is_array( $row ) ? $row : array();
	}

	/**
	 * Get recent billable calls for usage dashboard table.
	 *
	 * @param string $period_start_dt Period start datetime.
	 * @param string $period_end_dt   Period end datetime.
	 * @param int    $limit           Max rows.
	 * @return array<int,object>
	 */
	public function get_recent_billable_calls_for_period( $period_start_dt, $period_end_dt, $limit = 10 ) {
		$limit = max( 1, absint( $limit ) );

		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->calls_table}
				 WHERE created_at >= %s
				 AND created_at <= %s
				 AND minute_quota_exceeded = 0
				 AND purged_at IS NULL
				 ORDER BY created_at DESC
				 LIMIT %d",
				$period_start_dt,
				$period_end_dt,
				$limit
			)
		);
	}

	/**
	 * Get average call duration for usage dashboard cards.
	 *
	 * @param string $period_start_dt Period start datetime.
	 * @param string $period_end_dt   Period end datetime.
	 * @return float Average call duration (seconds).
	 */
	public function get_average_call_duration_seconds_for_period( $period_start_dt, $period_end_dt ) {
		$effective_minutes_expr = "CASE
			WHEN billable_minutes IS NOT NULL AND billable_minutes > 0 THEN billable_minutes
			WHEN call_duration >= 30 THEN ROUND(call_duration / 60)
			ELSE 0
		END";

		$voice_only = SiteStaffr_Call_Manager::voice_minutes_where_sql();

		return (float) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT AVG(call_duration) FROM {$this->calls_table}
				 WHERE created_at >= %s
				 AND created_at <= %s
				 AND call_duration > 0
				 AND minute_quota_exceeded = 0
				 AND is_billable = 1
				 AND {$voice_only}
				 AND {$effective_minutes_expr} > 0",
				$period_start_dt,
				$period_end_dt
			)
		);
	}

	/**
	 * Get daily billed-minute totals (the billed quantity) for usage charts.
	 *
	 * @param string $period_start_dt Period start datetime.
	 * @param string $period_end_dt   Period end datetime.
	 * @return array<int,object> Rows with day_key + total.
	 */
	public function get_daily_minutes_rows( $period_start_dt, $period_end_dt ) {
		// Billing truth, broken down by day: the same rows and the same rounding
		// SiteStaffr_Usage_Manager uses for "minutes used" (its total_used query), so
		// these rows sum to the headline figure. The old FLOOR-based expression read
		// lower than the headline on every surface that charted or printed it.
		$voice_only = SiteStaffr_Call_Manager::voice_minutes_where_sql();

		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT DATE(created_at) AS day_key,
				        SUM(CASE
				            WHEN billable_minutes IS NOT NULL AND billable_minutes > 0 THEN billable_minutes
				            WHEN call_duration >= 30 THEN ROUND(call_duration / 60)
				            ELSE 0
				        END) AS total
				 FROM {$this->calls_table}
				 WHERE created_at >= %s
				 AND created_at <= %s
				 AND minute_quota_exceeded = 0
				 AND is_billable = 1
				 AND {$voice_only}
				 GROUP BY DATE(created_at)
				 ORDER BY day_key ASC",
				$period_start_dt,
				$period_end_dt
			)
		);
	}

	/**
	 * Count synced knowledge sources whose post changed after their last sync.
	 *
	 * @return int
	 */
	public function get_stale_knowledge_count() {
		$knowledge_table = SiteStaffr_Database::get_knowledge_table();

		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				'SELECT COUNT(DISTINCT k.post_id)
				 FROM %i k
				 INNER JOIN %i p ON p.ID = k.post_id
				 WHERE p.post_status = %s
				 AND p.post_modified > k.synced_at',
				$knowledge_table,
				$this->wpdb->posts,
				'publish'
			)
		);
	}

	/**
	 * Get daily conversation totals for usage chart data.
	 *
	 * @param string    $period_start_dt Period start datetime.
	 * @param string    $period_end_dt   Period end datetime.
	 * @param bool|null $is_billable     Filter by billable flag when provided.
	 * @return array<int,object>
	 */
	public function get_daily_conversation_rows( $period_start_dt, $period_end_dt, $is_billable = null ) {
		$billable_sql = '';

		if ( true === $is_billable ) {
			$billable_sql = ' AND is_billable = 1';
		} elseif ( false === $is_billable ) {
			$billable_sql = ' AND is_billable = 0';
		}

		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT DATE(created_at) AS day_key, COUNT(*) AS total
				 FROM {$this->calls_table}
				 WHERE created_at >= %s
				 AND created_at <= %s
				 AND (is_test_call = 0 OR is_test_call IS NULL)
				 AND minute_quota_exceeded = 0
				 {$billable_sql}
				 GROUP BY DATE(created_at)
				 ORDER BY day_key ASC",
				$period_start_dt,
				$period_end_dt
			)
		);
	}

	/**
	 * After-hours conversation and qualified-lead counts for a period.
	 *
	 * Bucketing happens in PHP, not SQL: started_at is an unambiguous UTC
	 * epoch, and converting it in MySQL would need CONVERT_TZ, which depends
	 * on timezone tables many hosts do not load. PHP also handles DST
	 * transitions inside a billing cycle correctly.
	 *
	 * Two passes rather than one because conversations and qualified leads
	 * use different exclusion rules upstream; each pass mirrors the filter of
	 * the total it must reconcile with, so both results stay strict subsets
	 * of the numbers already on screen.
	 *
	 * @since 1.41.0
	 *
	 * @param string              $period_start_dt Inclusive window start.
	 * @param string              $period_end_dt   Window end.
	 * @param array<string,array> $schedule        Structured weekly schedule.
	 * @return array{total:int,after_hours:int,after_hours_leads:int,after_hours_pct:int}
	 */
	public function get_after_hours_stats( $period_start_dt, $period_end_dt, $schedule ) {
		$result = array(
			'total'             => 0,
			'after_hours'       => 0,
			'after_hours_leads' => 0,
			'after_hours_pct'   => 0,
		);

		// Mirrors get_dashboard_period_counts() — end-inclusive.
		$conversations = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT started_at, created_at
				 FROM {$this->calls_table}
				 WHERE created_at >= %s
				 AND created_at <= %s
				 AND (minute_quota_exceeded = 0 OR minute_quota_exceeded IS NULL)
				 AND (is_test_call = 0 OR is_test_call IS NULL)",
				$period_start_dt,
				$period_end_dt
			)
		);

		$result['total'] = is_array( $conversations ) ? count( $conversations ) : 0;

		if ( ! is_array( $schedule ) || empty( $schedule ) ) {
			return $result;
		}

		$timezone = wp_timezone();

		$result['after_hours'] = self::count_after_hours( $conversations, $schedule, $timezone );

		// Mirrors SiteStaffr_Leads::get_lead_count_between() — end-exclusive.
		$leads = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT started_at, created_at
				 FROM {$this->calls_table}
				 WHERE is_qualified_lead = 1
				 AND is_test_call = 0
				 AND deleted_at IS NULL
				 AND persona != 'onboarding'
				 AND created_at >= %s
				 AND created_at < %s",
				$period_start_dt,
				$period_end_dt
			)
		);

		$result['after_hours_leads'] = self::count_after_hours( $leads, $schedule, $timezone );

		if ( $result['total'] > 0 ) {
			$result['after_hours_pct'] = (int) round( ( $result['after_hours'] / $result['total'] ) * 100 );
		}

		return $result;
	}

	/**
	 * Count how many of these call rows fall outside the schedule.
	 *
	 * @param array<int,object>|null $rows     Call rows with started_at/created_at.
	 * @param array<string,array>    $schedule Structured weekly schedule.
	 * @param DateTimeZone           $timezone Site timezone.
	 * @return int
	 */
	private static function count_after_hours( $rows, $schedule, $timezone ) {
		$count = 0;

		foreach ( (array) $rows as $row ) {
			if ( SiteStaffr_Business_Hours::is_after_hours( self::to_local( $row, $timezone ), $schedule ) ) {
				++$count;
			}
		}

		return $count;
	}

	/**
	 * Conversations and qualified leads grouped by the page they started on.
	 *
	 * Normalizes in SQL — query string, fragment and a trailing slash are
	 * stripped — so /pricing/?utm_source=x and /pricing do not fragment into
	 * separate rows.
	 *
	 * Rows with no recorded page are grouped into a single "unknown" entry
	 * appended last and excluded from the top-N ranking. They are not hidden:
	 * suppressing them would misstate the total against the conversation count
	 * displayed on the same screen. They come from rows saved before the
	 * column existed, and from voice conversations that never reached the save
	 * path that fills it in.
	 *
	 * The lead column carries the same exclusions as
	 * SiteStaffr_Leads::get_lead_count_between() so it reconciles with the
	 * headline qualified-lead total.
	 *
	 * @since 1.41.0
	 *
	 * @param string $period_start_dt Inclusive window start.
	 * @param string $period_end_dt   Inclusive window end.
	 * @param int    $limit           Maximum ranked pages to return.
	 * @return array<int,array{page:string,title:string,conversations:int,leads:int,is_unknown:bool}>
	 */
	public function get_top_source_pages( $period_start_dt, $period_end_dt, $limit = 8 ) {
		$limit = max( 1, (int) $limit );

		// Strip fragment, then query string, then a single trailing slash.
		$normalized = "TRIM(TRAILING '/' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(source_page_url, '#', 1), '?', 1))";

		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT {$normalized} AS page,
				        MAX(source_page_title) AS title,
				        COUNT(*) AS conversations,
				        SUM(CASE WHEN is_qualified_lead = 1
				                  AND deleted_at IS NULL
				                  AND persona != 'onboarding'
				                 THEN 1 ELSE 0 END) AS leads
				 FROM {$this->calls_table}
				 WHERE created_at >= %s
				 AND created_at <= %s
				 AND (minute_quota_exceeded = 0 OR minute_quota_exceeded IS NULL)
				 AND (is_test_call = 0 OR is_test_call IS NULL)
				 GROUP BY page
				 ORDER BY conversations DESC",
				$period_start_dt,
				$period_end_dt
			)
		);

		$ranked  = array();
		$unknown = null;

		foreach ( (array) $rows as $row ) {
			$page = isset( $row->page ) ? trim( (string) $row->page ) : '';

			if ( '' === $page ) {
				if ( null === $unknown ) {
					// Label is left to the view — is_unknown is the signal, and
					// the admin screen and the client-facing report are free to
					// word it differently.
					$unknown = array(
						'page'          => '',
						'title'         => '',
						'conversations' => 0,
						'leads'         => 0,
						'is_unknown'    => true,
					);
				}
				$unknown['conversations'] += (int) $row->conversations;
				$unknown['leads']         += (int) $row->leads;
				continue;
			}

			$ranked[] = array(
				'page'          => $page,
				'title'         => isset( $row->title ) ? (string) $row->title : '',
				'conversations' => (int) $row->conversations,
				'leads'         => (int) $row->leads,
				'is_unknown'    => false,
			);
		}

		$ranked = array_slice( $ranked, 0, $limit );

		if ( null !== $unknown ) {
			$ranked[] = $unknown;
		}

		return $ranked;
	}

	/**
	 * Convert a call row to a local moment.
	 *
	 * started_at is the UTC epoch and is preferred. It defaults to 0 in the
	 * schema, so rows without it fall back to created_at, which is already
	 * stored in site-local time.
	 *
	 * @param object       $row      Row with started_at and created_at.
	 * @param DateTimeZone $timezone Site timezone.
	 * @return DateTimeImmutable
	 */
	private static function to_local( $row, $timezone ) {
		$started_at = isset( $row->started_at ) ? (int) $row->started_at : 0;

		// Convert milliseconds to seconds if needed — matches
		// SiteStaffr_Leads::format_lead_date() and
		// SiteStaffr_Transcript_Viewer::format_call_date().
		if ( $started_at > 9999999999 ) {
			$started_at = (int) floor( $started_at / 1000 );
		}

		if ( $started_at > 0 ) {
			$utc = new DateTimeImmutable( '@' . $started_at );
			return $utc->setTimezone( $timezone );
		}

		$created_at = isset( $row->created_at ) ? (string) $row->created_at : '';
		if ( '' !== $created_at ) {
			try {
				return new DateTimeImmutable( $created_at, $timezone );
			} catch ( Exception $e ) {
				// Fall through to now.
			}
		}

		return new DateTimeImmutable( 'now', $timezone );
	}

	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
}
// phpcs:enable PluginCheck.Security.DirectDB.UnescapedDBParameter
