<?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 ) {
		$total_calls = (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->calls_table}
				 WHERE created_at >= %s
				 AND created_at <= %s
				 AND (minute_quota_exceeded = 0 OR minute_quota_exceeded IS NULL)",
				$period_start_dt,
				$period_end_dt
			)
		);

		$billable_calls = (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) 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_billable = 1",
				$period_start_dt,
				$period_end_dt
			)
		);

		$answered_calls = (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->calls_table}
				 WHERE created_at >= %s
				 AND created_at <= %s
				 AND (minute_quota_exceeded = 0 OR minute_quota_exceeded IS NULL)
				 AND call_status = 'completed'",
				$period_start_dt,
				$period_end_dt
			)
		);

		return array(
			'total_calls'    => $total_calls,
			'billable_calls' => $billable_calls,
			'answered_calls' => $answered_calls,
		);
	}

	/**
	 * Get conversation timestamps for dashboard period scans.
	 *
	 * @param string $period_start_dt Period start (UTC datetime).
	 * @param string $period_end_dt   Period end (UTC datetime).
	 * @return string[]
	 */
	public function get_dashboard_period_call_timestamps( $period_start_dt, $period_end_dt ) {
		return $this->wpdb->get_col(
			$this->wpdb->prepare(
				"SELECT 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)",
				$period_start_dt,
				$period_end_dt
			)
		);
	}

	/**
	 * Get average call duration for dashboard stats.
	 *
	 * @param string $period_start_dt Period start (UTC datetime).
	 * @param string $period_end_dt   Period end (UTC datetime).
	 * @return int Rounded average seconds.
	 */
	public function get_dashboard_average_duration_seconds( $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";

		$avg_seconds = (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 OR minute_quota_exceeded IS NULL)
				 AND is_billable = 1
				 AND {$effective_minutes_expr} > 0",
				$period_start_dt,
				$period_end_dt
			)
		);

		return (int) round( $avg_seconds );
	}

	/**
	 * 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 ) {
		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
				 ORDER BY created_at ASC",
				$lookback_start_dt
			)
		);
	}

	/**
	 * 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";

		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 {$effective_minutes_expr} > 0",
				$period_start_dt,
				$period_end_dt
			)
		);
	}

	/**
	 * 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
			)
		);
	}

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