<?php
/**
 * @license GPL-2.0-or-later
 * @link    https://wordpress.org/plugins/pressio-crm
 */
namespace PressioCRM\API;

defined( 'ABSPATH' ) || exit;

use PressioCRM\Framework\Controller;
use WP_REST_Request;
use WP_REST_Response;
use WP_REST_Server;

/**
 * REST controller for the Reports page.
 *
 * Endpoints:
 *   GET /reports/deals      — won vs lost, win rate, value, per-period chart
 *   GET /reports/contacts   — new contacts per period chart
 *   GET /reports/activities — activity count by type
 *
 * All endpoints accept a `period` param: 30d | 90d | 12m (default: 30d).
 * Results are cached in transients (5 min TTL).
 */
class RestReports extends Controller {

	protected $rest_base = 'reports';

	public function register_routes(): void {
		$period_args = [
			'period' => [
				'type'              => 'string',
				'default'           => '30d',
				'enum'              => [ '30d', '90d', '12m' ],
				'required'          => false,
				'sanitize_callback' => 'sanitize_key',
				'validate_callback' => 'rest_validate_request_arg',
			],
		];

		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/deals',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'deals' ],
				'permission_callback' => [ $this, 'require_contacts_cap' ],
				'args'                => $period_args,
			]
		);

		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/contacts',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'contacts' ],
				'permission_callback' => [ $this, 'require_contacts_cap' ],
				'args'                => $period_args,
			]
		);

		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/activities',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'activities' ],
				'permission_callback' => [ $this, 'require_contacts_cap' ],
				'args'                => $period_args,
			]
		);
	}

	/**
	 * Return period-specific config: SQL interval string, DATE_FORMAT format, and label format key.
	 * Period is validated as enum upstream — all values are safe for SQL interpolation.
	 *
	 * @param string $period One of: '30d', '90d', '12m'.
	 * @return array{ interval: string, date_format: string, label_format: string }
	 */
	private function parse_period( string $period ): array {
		switch ( $period ) {
			case '90d':
				return [
					'interval'     => 'INTERVAL 90 DAY',
					'date_format'  => '%Y-%u',  // year-week
					'label_format' => 'week',
				];
			case '12m':
				return [
					'interval'     => 'INTERVAL 12 MONTH',
					'date_format'  => '%Y-%m',  // year-month
					'label_format' => 'month',
				];
			default: // 30d
				return [
					'interval'     => 'INTERVAL 30 DAY',
					'date_format'  => '%Y-%m-%d',  // year-month-day
					'label_format' => 'day',
				];
		}
	}

	/**
	 * Convert a raw DATE_FORMAT() output key into a human-readable chart label.
	 *
	 * @param string $period_key Raw value from MySQL DATE_FORMAT() e.g. '2026-04-12', '2026-04', '2026-15'.
	 * @param string $label_format 'day' | 'week' | 'month'.
	 */
	private function format_period_label( string $period_key, string $label_format ): string {
		$parts = explode( '-', $period_key );

		if ( 'week' === $label_format ) {
			// '2026-15' → 'W15'
			return 'W' . ltrim( $parts[1] ?? '1', '0' );
		}

		if ( 'month' === $label_format ) {
			// '2026-04' → 'Apr '26'
			$year  = (int) ( $parts[0] ?? gmdate( 'Y' ) );
			$month = (int) ( $parts[1] ?? 1 );
			return gmdate( 'M', mktime( 0, 0, 0, $month, 1, $year ) ) . " '" . substr( (string) $year, 2 );
		}

		// 'day': '2026-04-12' → 'Apr 12'
		$year  = (int) ( $parts[0] ?? gmdate( 'Y' ) );
		$month = (int) ( $parts[1] ?? 1 );
		$day   = (int) ( $parts[2] ?? 1 );
		return gmdate( 'M j', mktime( 0, 0, 0, $month, $day, $year ) );
	}

	/**
	 * GET /reports/deals
	 * Returns won vs lost counts, values, win rate, and a per-period breakdown chart.
	 */
	public function deals( WP_REST_Request $request ): WP_REST_Response {
		$period    = $request->get_param( 'period' );
		$cache_key = 'pressio_crm_rpt_deals_' . $period;
		$cached    = get_transient( $cache_key );

		if ( false !== $cached ) {
			return $this->ok( $cached );
		}

		global $wpdb;

		$deals_table  = $wpdb->prefix . 'pcrm_deals';
		$opts         = $this->parse_period( $period );
		$interval     = $opts['interval'];     // from whitelist — safe to interpolate
		$date_format  = $opts['date_format'];  // passed via prepare() as %s
		$label_format = $opts['label_format'];

		// Totals — interval is from an enum whitelist (parse_period), safe to interpolate.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $interval is a hardcoded literal from a whitelist switch in parse_period(), never user input.
		$totals = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT status, COUNT(id) AS cnt, COALESCE(SUM(value), 0) AS total_value
				 FROM %i
				 WHERE deleted_at IS NULL
				   AND status IN ('won', 'lost')
				   AND closed_at >= DATE_SUB(NOW(), {$interval})
				 GROUP BY status",
				$deals_table
			)
		);
		// phpcs:enable

		$won_cnt = 0; $won_value = 0.0; $lost_cnt = 0; $lost_value = 0.0;
		foreach ( (array) $totals as $row ) {
			if ( 'won' === $row->status ) {
				$won_cnt   = (int) $row->cnt;
				$won_value = (float) $row->total_value;
			} else {
				$lost_cnt   = (int) $row->cnt;
				$lost_value = (float) $row->total_value;
			}
		}

		$total_closed = $won_cnt + $lost_cnt;
		$win_rate     = $total_closed > 0 ? round( ( $won_cnt / $total_closed ) * 100, 1 ) : 0.0;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $interval is a hardcoded literal from a whitelist switch in parse_period(), never user input.
		$chart_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE_FORMAT(closed_at, %s) AS period_key,
				        status,
				        COUNT(id) AS cnt
				 FROM %i
				 WHERE deleted_at IS NULL
				   AND status IN ('won', 'lost')
				   AND closed_at >= DATE_SUB(NOW(), {$interval})
				 GROUP BY period_key, status
				 ORDER BY period_key ASC",
				$date_format,
				$deals_table
			)
		);
		// phpcs:enable

		$chart_map = [];
		foreach ( (array) $chart_rows as $row ) {
			$key = $row->period_key;
			if ( ! isset( $chart_map[ $key ] ) ) {
				$chart_map[ $key ] = [
					'label' => $this->format_period_label( $key, $label_format ),
					'won'   => 0,
					'lost'  => 0,
				];
			}
			$chart_map[ $key ][ $row->status ] += (int) $row->cnt;
		}

		$data = [
			'period'     => $period,
			'total_won'  => $won_cnt,
			'total_lost' => $lost_cnt,
			'value_won'  => round( $won_value, 2 ),
			'value_lost' => round( $lost_value, 2 ),
			'win_rate'   => $win_rate,
			'chart'      => array_values( $chart_map ),
		];

		set_transient( $cache_key, $data, 300 );

		return $this->ok( $data );
	}

	/**
	 * GET /reports/contacts
	 * Returns new contact count and a per-period growth chart.
	 */
	public function contacts( WP_REST_Request $request ): WP_REST_Response {
		$period    = $request->get_param( 'period' );
		$cache_key = 'pressio_crm_rpt_contacts_' . $period;
		$cached    = get_transient( $cache_key );

		if ( false !== $cached ) {
			return $this->ok( $cached );
		}

		global $wpdb;

		$contacts_table = $wpdb->prefix . 'pcrm_contacts';
		$opts           = $this->parse_period( $period );
		$interval       = $opts['interval'];
		$date_format    = $opts['date_format'];
		$label_format   = $opts['label_format'];

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $interval is a hardcoded literal from a whitelist switch in parse_period(), never user input.
		$chart_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE_FORMAT(created_at, %s) AS period_key,
				        COUNT(id) AS cnt
				 FROM %i
				 WHERE deleted_at IS NULL
				   AND created_at >= DATE_SUB(NOW(), {$interval})
				 GROUP BY period_key
				 ORDER BY period_key ASC",
				$date_format,
				$contacts_table
			)
		);
		// phpcs:enable

		$total_new = 0;
		$chart     = [];
		foreach ( (array) $chart_rows as $row ) {
			$cnt        = (int) $row->cnt;
			$total_new += $cnt;
			$chart[]    = [
				'label' => $this->format_period_label( $row->period_key, $label_format ),
				'count' => $cnt,
			];
		}

		$data = [
			'period'    => $period,
			'total_new' => $total_new,
			'chart'     => $chart,
		];

		set_transient( $cache_key, $data, 300 );

		return $this->ok( $data );
	}

	/**
	 * GET /reports/activities
	 * Returns total activity count and a breakdown by activity type.
	 */
	public function activities( WP_REST_Request $request ): WP_REST_Response {
		$period    = $request->get_param( 'period' );
		$cache_key = 'pressio_crm_rpt_activities_' . $period;
		$cached    = get_transient( $cache_key );

		if ( false !== $cached ) {
			return $this->ok( $cached );
		}

		global $wpdb;

		$activities_table = $wpdb->prefix . 'pcrm_activities';
		$opts             = $this->parse_period( $period );
		$interval         = $opts['interval'];

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $interval is a hardcoded literal from a whitelist switch in parse_period(), never user input.
		$type_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT type, COUNT(id) AS cnt
				 FROM %i
				 WHERE created_at >= DATE_SUB(NOW(), {$interval})
				 GROUP BY type
				 ORDER BY cnt DESC",
				$activities_table
			)
		);
		// phpcs:enable

		$total   = 0;
		$by_type = [];
		foreach ( (array) $type_rows as $row ) {
			$cnt     = (int) $row->cnt;
			$total  += $cnt;
			$by_type[] = [
				'type'  => (string) $row->type,
				'count' => $cnt,
			];
		}

		$data = [
			'period'  => $period,
			'total'   => $total,
			'by_type' => $by_type,
		];

		set_transient( $cache_key, $data, 300 );

		return $this->ok( $data );
	}
}
