<?php

namespace KaizenCoders\MagicLink\Admin\DB;

class MagicLinks {

	private function table() {
		global $wpdb;
		return $wpdb->prefix . 'kc_ml_magic_links';
	}

	public function get_total_count() {
		global $wpdb;
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table()}" );
	}

	public function get_used_count() {
		global $wpdb;
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table()} WHERE used = 1" );
	}

	public function get_active_count() {
		global $wpdb;
		$now = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp', true ) );
		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$this->table()} WHERE used = 0 AND expires > %s", $now )
		);
	}

	public function get_unique_users_count() {
		global $wpdb;
		return (int) $wpdb->get_var( "SELECT COUNT(DISTINCT user_id) FROM {$this->table()}" );
	}

	public function get_recent_count( $days = 7 ) {
		global $wpdb;
		$start = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );
		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$this->table()} WHERE created_at >= %s", $start )
		);
	}

	public function get_daily_trend( $days = 7 ) {
		global $wpdb;
		$start = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(created_at) AS date,
				        COUNT(*) AS generated,
				        SUM(used) AS used
				 FROM {$this->table()}
				 WHERE created_at >= %s
				 GROUP BY DATE(created_at)
				 ORDER BY date ASC",
				$start
			)
		);
	}

	public function get_heatmap_data() {
		global $wpdb;
		$start = gmdate( 'Y-m-d', strtotime( '-364 days' ) );

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(created_at) AS date, COUNT(*) AS count
				 FROM {$this->table()}
				 WHERE DATE(created_at) >= %s
				 GROUP BY DATE(created_at)
				 ORDER BY date ASC",
				$start
			),
			ARRAY_A
		);
	}

	public function get_top_users( $limit = 5 ) {
		global $wpdb;

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT ml.user_id,
				        u.display_name,
				        u.user_email,
				        COUNT(*) AS generated_count,
				        SUM(CASE WHEN ml.used = 1 THEN 1 ELSE 0 END) AS used_count
				 FROM {$this->table()} AS ml
				 LEFT JOIN {$wpdb->users} AS u ON u.ID = ml.user_id
				 GROUP BY ml.user_id
				 ORDER BY generated_count DESC, used_count DESC
				 LIMIT %d",
				$limit
			)
		);
	}

	public function get_recent_links( $limit = 10 ) {
		global $wpdb;
		$now = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp', true ) );

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT ml.id,
				        ml.user_id,
				        u.display_name,
				        u.user_email,
				        ml.expires,
				        ml.used,
				        ml.ip_address,
				        ml.created_at,
				        CASE WHEN ml.used = 1    THEN 'used'
				             WHEN ml.expires < %s THEN 'expired'
				             ELSE                      'active'
				        END AS status
				 FROM {$this->table()} AS ml
				 LEFT JOIN {$wpdb->users} AS u ON u.ID = ml.user_id
				 ORDER BY ml.created_at DESC
				 LIMIT %d",
				$now,
				$limit
			)
		);
	}
}
