<?php
/**
 * Calendar Repository Class
 *
 * @package Wisewand
 * @subpackage Wisewand/includes/Repositories
 *
 * phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- False positive from Plugin Checker
 * All queries use $wpdb->prepare() with proper placeholders. The checker incorrectly flags
 * the prepare() method calls themselves. Known PHPCS limitation.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Repository pattern requirement
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no object cache
 */

namespace Wisewand\Repositories;

/**
 * Class Calendar_Repository
 *
 * Handles database interactions for the Editorial Calendar.
 */
class Calendar_Repository extends Base_Repository {

	/**
	 * Initialize the repository.
	 */
	public function __construct() {
		parent::__construct( 'wisewand_editorial_calendar' );
	}

	/**
	 * Create a calendar entry.
	 *
	 * @param array $data Entry data.
	 * @return int|false Insert ID on success, false on failure.
	 */
	public function create_entry( $data ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $this->wpdb->insert(
			$this->table_name,
			$data
		);

		if ( $result === false ) {
			return false;
		}
		return $this->wpdb->insert_id;
	}

	/**
	 * Update a calendar entry.
	 *
	 * @param int   $entry_id Entry ID.
	 * @param array $data     Data to update.
	 * @param array $format   Optional format array.
	 * @return bool Success.
	 */
	public function update_entry( $entry_id, $data, $format = null ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $this->wpdb->update(
			$this->table_name,
			$data,
			array( 'id' => $entry_id ),
			$format,
			array( '%d' )
		);

		return $result !== false;
	}

	/**
	 * Delete a calendar entry.
	 *
	 * @param int $entry_id Entry ID.
	 * @return bool Success.
	 */
	public function delete_entry( $entry_id ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $this->wpdb->delete(
			$this->table_name,
			array( 'id' => $entry_id ),
			array( '%d' )
		);

		return $result !== false;
	}

	/**
	 * Get a calendar entry by ID.
	 *
	 * @param int $entry_id Entry ID.
	 * @return array|null Entry data or null.
	 */
	public function get_entry( $entry_id ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM %i WHERE id = %d",
				$this->table_name,
				$entry_id
			),
			ARRAY_A
		);
	}

	/**
	 * Get calendar entries with filters.
	 *
	 * @param array  $where_clauses WHERE clauses.
	 * @param array  $where_values  Values for WHERE clauses.
	 * @param string $order_by      ORDER BY clause.
	 * @param int    $limit         Limit.
	 * @return array List of entries.
	 */
	public function get_entries( $where_clauses, $where_values, $order_by, $limit ) {
		$where_sql = implode( ' AND ', $where_clauses );
		$base_sql  = "SELECT * FROM %i WHERE {$where_sql} ORDER BY {$order_by} LIMIT %d";

		if ( ! empty( $where_values ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$final_sql = $this->wpdb->prepare( $base_sql, array_merge( array( $this->table_name ), $where_values, array( $limit ) ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$final_sql = $this->wpdb->prepare( $base_sql, $this->table_name, $limit );
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_results( $final_sql, ARRAY_A );
	}

	/**
	 * Check if keyword exists.
	 *
	 * @param string   $keyword          Keyword.
	 * @param int|null $exclude_entry_id Exclude ID.
	 * @return array|null Row if exists.
	 */
	public function check_keyword_exists( $keyword, $exclude_entry_id = null ) {
		if ( $exclude_entry_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return $this->wpdb->get_row(
				$this->wpdb->prepare(
					"SELECT id, publication_date FROM %i WHERE keyword = %s AND id != %d LIMIT 1",
					$this->table_name,
					$keyword,
					$exclude_entry_id
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return $this->wpdb->get_row(
				$this->wpdb->prepare(
					"SELECT id, publication_date FROM %i WHERE keyword = %s LIMIT 1",
					$this->table_name,
					$keyword
				),
				ARRAY_A
			);
		}
	}

	/**
	 * Check duplicate by keyword and date.
	 *
	 * @param string $keyword Keyword.
	 * @param string $date    Date.
	 * @return string|null ID if exists.
	 */
	public function check_duplicate( $keyword, $date ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT id FROM %i WHERE keyword = %s AND publication_date = %s",
				$this->table_name,
				$keyword,
				$date
			)
		);
	}

	/**
	 * Get total count.
	 *
	 * @return string|null Count.
	 */
	public function get_total_count() {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_var( $this->wpdb->prepare( "SELECT COUNT(*) FROM %i", $this->table_name ) );
	}

	/**
	 * Get status counts.
	 *
	 * @param string $status_column Column name (wisewand_status or wordpress_status).
	 * @return array List of status counts.
	 */
	public function get_status_counts( $status_column ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		// Note: Column name cannot be prepared with %i or %s directly in SELECT list safely without whitelist, but here we control the input.
		// However, for safety, let's restrict inputs.
		if ( ! in_array( $status_column, array( 'wisewand_status', 'wordpress_status' ) ) ) {
			return array();
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT {$status_column}, COUNT(*) as count FROM %i GROUP BY {$status_column}",
				$this->table_name
			),
			ARRAY_A
		);
	}

	/**
	 * Get upcoming count.
	 *
	 * @param string $start_date Start date.
	 * @param string $end_date   End date.
	 * @return string|null Count.
	 */
	public function get_upcoming_count( $start_date, $end_date ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE publication_date BETWEEN %s AND %s",
				$this->table_name,
				$start_date,
				$end_date
			)
		);
	}

	/**
	 * Get date range count.
	 *
	 * @param string $start_date Start date.
	 * @param string $end_date   End date (exclusive).
	 * @return string|null Count.
	 */
	public function get_date_range_count( $start_date, $end_date ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE publication_date >= %s AND publication_date < %s",
				$this->table_name,
				$start_date,
				$end_date
			)
		);
	}

	/**
	 * Ensure database schema is up to date.
	 */
	public function ensure_schema() {
		$calendar_schema_version = get_option( 'wisewand_calendar_schema_version', '1.0' );

		if ( version_compare( $calendar_schema_version, '1.1', '<' ) ) {
			// Check if calendar table exists before attempting schema updates
			if ( ! $this->table_exists() ) {
				return; // Don't crash if table is missing
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$cal_columns = $this->wpdb->get_col( "SHOW COLUMNS FROM {$this->table_name}" );

			// Add ease_score column
			if ( ! in_array( 'ease_score', $cal_columns ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$this->wpdb->query( "ALTER TABLE {$this->table_name} ADD COLUMN ease_score decimal(5,2) DEFAULT 0 AFTER priority_score" );
			}

			// Add cannibalisation tracking
			if ( ! in_array( 'cannibalisation_risk', $cal_columns ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$this->wpdb->query( "ALTER TABLE {$this->table_name} ADD COLUMN cannibalisation_risk enum('none','low','medium','high') DEFAULT 'none' AFTER ease_score" );
			}

			if ( ! in_array( 'cannibalisation_post_id', $cal_columns ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$this->wpdb->query( "ALTER TABLE {$this->table_name} ADD COLUMN cannibalisation_post_id bigint(20) DEFAULT NULL AFTER cannibalisation_risk" );
			}

			// Add scheduling batch number (for multiple posts per day)
			if ( ! in_array( 'scheduling_batch', $cal_columns ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$this->wpdb->query( "ALTER TABLE {$this->table_name} ADD COLUMN scheduling_batch int(3) DEFAULT 1 AFTER publication_time_range" );
			}

			update_option( 'wisewand_calendar_schema_version', '1.1' );
		}
	}
	/**
	 * Bulk delete entries.
	 *
	 * @param array $ids List of IDs.
	 * @return int|false Number of rows deleted.
	 */
	public function bulk_delete_entries( $ids ) {
		$ids_placeholder = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->query(
			$this->wpdb->prepare(
				"DELETE FROM %i WHERE id IN ($ids_placeholder)",
				array_merge( array( $this->table_name ), $ids )
			)
		);
	}

	/**
	 * Get count of published entries.
	 *
	 * @return string|null Count.
	 */
	public function get_published_count() {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE wordpress_status = 'published'",
				$this->table_name
			)
		);
	}

	/**
	 * Clear all entries except published ones.
	 *
	 * @return int|false Number of rows deleted.
	 */
	public function clear_all_except_published() {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->query(
			"DELETE FROM {$this->table_name} WHERE wordpress_status != 'published'"
		);
	}

	/**
	 * Get calendar status for multiple keywords.
	 *
	 * @param array $keywords_list List of keyword strings.
	 * @return array Array of calendar data indexed by keyword.
	 */
	public function get_keywords_calendar_status( $keywords_list ) {
		if ( empty( $keywords_list ) ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $keywords_list ), '%s' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$calendar_data = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT
					keyword,
					publication_date,
					publication_time,
					wisewand_status,
					wordpress_status,
					publish_as_draft,
					wordpress_post_id,
					id as calendar_id
				FROM %i
				WHERE keyword IN ({$placeholders})
				AND publication_date >= CURDATE()
				ORDER BY publication_date ASC",
				array_merge( array( $this->table_name ), $keywords_list )
			),
			ARRAY_A
		);

		// Index by keyword for easy lookup
		$indexed = array();
		if ( $calendar_data ) {
			foreach ( $calendar_data as $row ) {
				if ( ! isset( $indexed[ $row['keyword'] ] ) ) {
					$indexed[ $row['keyword'] ] = $row;
				}
			}
		}

		return $indexed;
	}

	/**
	 * Get count of upcoming entries from a given date.
	 *
	 * @param string $from_date Starting date (Y-m-d format).
	 * @return int Count of upcoming entries.
	 */
	public function get_upcoming_count_from_date( $from_date ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE publication_date >= %s",
				$this->table_name,
				$from_date
			)
		);
	}
}
