<?php
namespace Vidplex\Tables;

// If this file is called directly, abort.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Class Channel
 *
 * Manages channel-related database operations for the plugin.
 *
 * Handles CRUD operations, caching, and retrieval of channel records,
 * including counts, statistics.
 */
class Channel {
	/**
	 * Singleton instance of the class.
	 *
	 * @var self|null
	 */
	public static $instance;

	/**
	 * Database table name for storing videos.
	 *
	 * @var string
	 */
	private $table_name;

	/**
	 * Cache key prefix for channel-related transient or object cache entries.
	 * Used to prevent cache key collisions across the application.
	 *
	 * @var string
	 */
	private $cache_key = 'vidplex_channels_cache';

	/**
	 * Returns the singleton instance of the class.
	 *
	 * Ensures that only one instance of the class is created and reused
	 * throughout the request lifecycle.
	 *
	 * @return self
	 */
	public static function get_instance() {
		if ( is_null( self::$instance ) ) {
			self::$instance = new self();
		}

		return self::$instance;
	}

	/**
	 * Class constructor.
	 *
	 * Initializes required properties and sets the database table name
	 * using the WordPress database prefix.
	 *
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function __construct() {
		global $wpdb;
		$this->table_name = $wpdb->prefix . 'vidplex_yt_channels';
	}

	/**
	 * Deletes the cached statistics for all channels.
	 *
	 * Clears any stored transient or object cache data related to
	 * channel statistics to ensure fresh data is generated on next access.
	 *
	 * @return void
	 */
	private function delete_channel_stats_transient() {
		$vidplex_stats = get_transient( 'vidplex_stats' );
		if ( is_array( $vidplex_stats ) && isset( $vidplex_stats['total_channels'] ) ) {
			$settings = \Vidplex\Helpers::advanced_settings();
			unset( $vidplex_stats['total_channels'] );
			set_transient( 'vidplex_stats', $vidplex_stats, $settings['video_refresh_interval'] );
		}
	}

	/**
	 * Retrieves the total number of linked channels.
	 *
	 * Counts and returns the number of channels that have been
	 * linked or registered in the database.
	 *
	 * @return int Total number of linked channels.
	 */
	public function get_linked_channel_count() {
		$settings = \Vidplex\Helpers::advanced_settings();

		$vidplex_stats = get_transient( 'vidplex_stats' );

		if ( ! is_array( $vidplex_stats ) ) {
			$vidplex_stats = array();
		}

		if ( isset( $vidplex_stats['total_channels'] ) ) {
			return (int) $vidplex_stats['total_channels'];
		}

		global $wpdb;
		$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table_name}" );

		// Merge new value into existing array.
		$vidplex_stats['total_channels'] = $count;

		set_transient( 'vidplex_stats', $vidplex_stats, $settings['video_refresh_interval'] );

		return $count;
	}

	/**
	 * Retrieves all video records from the database.
	 *
	 * Fetches the complete list of videos stored in the table.
	 * Can be used for reporting, caching, or batch processing.
	 *
	 * @return array List of all videos.
	 */
	public function get_all() {
		$cached = get_transient( $this->cache_key );

		if ( false !== $cached ) {
			return $cached;
		}

		$settings = \Vidplex\Helpers::advanced_settings();

		global $wpdb;
		$data = $wpdb->get_results( "SELECT id, channel_id, channel_name, channel_handle, channel_uploads_playlist, next_page_token, last_sync_at FROM {$this->table_name}", ARRAY_A );

		set_transient( $this->cache_key, $data, (int) $settings['video_refresh_interval'] );

		return $data;
	}

	/**
	 * Retrieves specific fields for a single channel.
	 *
	 * Fetches selected columns or data points for the given channel ID
	 * based on the provided arguments.
	 *
	 * @param string|int $channel_id Unique identifier of the channel.
	 * @param array      $args       Optional arguments to specify which fields to retrieve.
	 * @return array|null Associative array of the requested fields, or null if none found.
	 */
	public function get_single_fields( $channel_id, $args = array() ) {
		if ( empty( $channel_id ) || empty( $args ) || ! is_array( $args ) ) {
			return array();
		}

		global $wpdb;

		$columns = $wpdb->get_col( "DESCRIBE {$this->table_name}", 0 ); // gets all column names.

		if ( empty( $columns ) ) {
			return array();
		}

		// Keep only keys that exist in the table.
		$args = array_intersect_key( $args, $columns );

		if ( empty( $args ) ) {
			return array();
		}

		$column_string = implode(
			', ',
			array_map(
				function ( $col ) use ( $wpdb ) {
					return esc_sql( $col );
				},
				$args
			)
		);

		$sql  = "SELECT {$column_string} FROM {$this->table_name} WHERE channel_id = %s";
		$data = $wpdb->get_results(
			$wpdb->prepare( $sql, $channel_id ),
			ARRAY_A
		);

		return $data;
	}

	/**
	 * Retrieves channel details by ID.
	 *
	 * Fetches the database record of a channel based on its unique ID.
	 *
	 * @param string|int $id Unique identifier of the channel.
	 * @return array|null Associative array of channel data if found, or null if not found.
	 */
	public function get_channel_by_id( $id ) {
		$channels = $this->get_all();

		$channel_data = array_values(
			array_filter(
				$channels,
				function ( $channel ) use ( $id ) {
					return $channel['channel_id'] === $id;
				}
			)
		);

		if ( empty( $channel_data ) ) {
			return array();
		}

		return $channel_data;
	}

	/**
	 * Fetches channel details from the YouTube Data API.
	 *
	 * Retrieves up-to-date information for the specified YouTube channel,
	 * including core metadata and statistics, and returns the parsed
	 * response for further processing or storage.
	 *
	 * This method is used during channel refresh operations and may be
	 * called by scheduled tasks or manual sync actions.
	 *
	 * @param string $channel_id The YouTube channel ID to fetch data for.
	 */
	public function fetch_channel_data( $channel_id ) {
		$api_key = get_option( 'vidplex-api-key' );

		$url = "https://www.googleapis.com/youtube/v3/channels?part=snippet,statistics,brandingSettings,contentDetails&key={$api_key}&id={$channel_id}";

		$response = wp_remote_get( $url );

		if ( ! is_wp_error( $response ) ) {
			$body = wp_remote_retrieve_body( $response );
			$data = json_decode( $body, true );

			if ( isset( $data['items'] ) && ! empty( $data['items'] ) ) {
				$channel_data = $data['items'][0];

				$response = wp_remote_get(
					$channel_data['snippet']['thumbnails']['high']['url']
				);

				$image_blob       = '';
				$mime_type        = '';
				$banner_blob      = '';
				$banner_mime_type = '';

				if ( ! is_wp_error( $response ) ) {
					$image_blob = wp_remote_retrieve_body( $response );
					$mime_type  = wp_remote_retrieve_header( $response, 'content-type' );
				}

				if ( isset( $channel_data['brandingSettings']['image']['bannerExternalUrl'] ) ) {
					$response = wp_remote_get( $channel_data['brandingSettings']['image']['bannerExternalUrl'] );

					if ( ! is_wp_error( $response ) ) {
						$banner_blob      = wp_remote_retrieve_body( $response );
						$banner_mime_type = wp_remote_retrieve_header( $response, 'content-type' );
					}
				}

				$refreshed_data = array(
					'channel_name'             => $channel_data['snippet']['title'],
					'channel_handle'           => isset( $channel_data['snippet']['customUrl'] ) ? $channel_data['snippet']['customUrl'] : null,
					'channel_thumbnail'        => $channel_data['snippet']['thumbnails']['high']['url'],
					'channel_uploads_playlist' => $channel_data['contentDetails']['relatedPlaylists']['uploads'],
					'statistics'               => wp_json_encode( $channel_data['statistics'] ),
					'branding_settings'        => wp_json_encode( $channel_data['brandingSettings'] ),
					'published_at'             => $channel_data['snippet']['publishedAt'],
					'thumbnail_blob'           => $image_blob,
					'mime_type'                => $mime_type,
					'banner_blob'              => $banner_blob,
					'banner_mime_type'         => $banner_mime_type,
					'last_sync_at'             => gmdate( 'Y-m-d H:i:s' ),
				);

				$this->update( $channel_id, $refreshed_data );
			}
		}
	}

	/**
	 * Inserts a new channel record into the database.
	 *
	 * Adds a new channel with the provided data. Existing records
	 * are not modified.
	 *
	 * @param array $channel_data Associative array of channel fields and values.
	 * @return int|false Inserted row ID on success, false on failure.
	 */
	public function insert( $channel_data ) {
		global $wpdb;
		$wpdb->hide_errors();

		$response = wp_remote_get(
			$channel_data['snippet']['thumbnails']['high']['url']
		);

		$image_blob       = '';
		$mime_type        = '';
		$banner_blob      = '';
		$banner_mime_type = '';

		if ( ! is_wp_error( $response ) ) {
			$image_blob = wp_remote_retrieve_body( $response );
			$mime_type  = wp_remote_retrieve_header( $response, 'content-type' );
		}

		if ( isset( $channel_data['brandingSettings']['image']['bannerExternalUrl'] ) ) {
			$response = wp_remote_get( $channel_data['brandingSettings']['image']['bannerExternalUrl'] );

			if ( ! is_wp_error( $response ) ) {
				$banner_blob      = wp_remote_retrieve_body( $response );
				$banner_mime_type = wp_remote_retrieve_header( $response, 'content-type' );
			}
		}

		$exists = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table_name} WHERE channel_id = %s",
				$channel_data['id']
			)
		);

		if ( $exists ) {
			return 0;
		}

		$result = $wpdb->insert(
			$this->table_name,
			array(
				'channel_id'               => $channel_data['id'],
				'channel_name'             => $channel_data['snippet']['title'],
				'channel_handle'           => isset( $channel_data['snippet']['customUrl'] ) ? $channel_data['snippet']['customUrl'] : null,
				'channel_thumbnail'        => $channel_data['snippet']['thumbnails']['high']['url'],
				'channel_uploads_playlist' => $channel_data['contentDetails']['relatedPlaylists']['uploads'],
				'statistics'               => wp_json_encode( $channel_data['statistics'] ),
				'branding_settings'        => wp_json_encode( $channel_data['brandingSettings'] ),
				'published_at'             => $channel_data['snippet']['publishedAt'],
				'thumbnail_blob'           => $image_blob,
				'mime_type'                => $mime_type,
				'banner_blob'              => $banner_blob,
				'banner_mime_type'         => $banner_mime_type,
				'last_sync_at'             => gmdate( 'Y-m-d H:i:s' ),
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( $wpdb->last_error ) {
			return $this->get_all();
		}

		$this->delete_channel_stats_transient();

		// Clear the cache after insert.
		delete_transient( $this->cache_key );

		return $result; // returns number of rows inserted or false on failure.
	}

	/**
	 * Updates an existing channel record.
	 *
	 * Modifies the data for a channel identified by the given ID.
	 *
	 * @param string|int $channel_id Unique identifier of the channel to update.
	 * @param array      $data       Associative array of fields and values to update.
	 * @return bool True on successful update, false on failure.
	 */
	public function update( $channel_id, $data ) {
		global $wpdb;

		$result = $wpdb->update( $this->table_name, $data, array( 'channel_id' => $channel_id ) );

		if ( false === $result || $wpdb->last_error ) {
			return false;
		}

		delete_transient( $this->cache_key );

		return true;
	}

	/**
	 * Deletes a channel record from the database.
	 *
	 * Removes the channel identified by the given ID along with any
	 * associated data, if applicable.
	 *
	 * @param string|int $channel_id Unique identifier of the channel to delete.
	 * @return bool True on successful deletion, false on failure.
	 */
	public function delete( $channel_id ) {
		global $wpdb;
		$wpdb->query( 'START TRANSACTION' );

		\Vidplex\Store::get_instance()->videos->delete( $channel_id );

		$result = $wpdb->delete( $this->table_name, array( 'channel_id' => $channel_id ) );

		if ( false === $result || $wpdb->last_error ) {
			$wpdb->query( 'ROLLBACK' );
			return false;
		}

		$wpdb->query( 'COMMIT' );

		delete_transient( $this->cache_key );

		$this->delete_channel_stats_transient();

		return true;
	}
}
