<?php
/**
 * This file is part of the Wisewand WordPress plugin.
 *
 * @package Wisewand
 * @since 1.0.0
 * @license GPL-2.0-or-later
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
 */
namespace Wisewand\Calendar;

// Exit if accessed directly
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

use WP_Error;
use Wisewand\Utils\Options;
use Wisewand\Logging\Logger;
use Wisewand\Utils\Wisewand_Payload_Builder;

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name stored in class property from $wpdb->prefix is safe to interpolate; direct database calls required for custom plugin table operations

class Calendar_Entry_Manager {

	private $options;
	private $logger;
	private $payload_builder;
	private $repository;

	public function __construct( Options $options, Logger $logger, Wisewand_Payload_Builder $payload_builder, \Wisewand\Repositories\Calendar_Repository $repository = null ) {
		$this->options         = $options;
		$this->logger          = $logger;
		$this->payload_builder = $payload_builder;
		$this->repository      = $repository ?: new \Wisewand\Repositories\Calendar_Repository();
	}

	public function create_entry( $entry_data ) {
		global $wpdb;

		$this->logger->info( 'Creating calendar entry', array( 'keyword' => $entry_data['keyword'] ?? 'unknown' ) );

		$required = array( 'keyword', 'publication_date', 'wisewand_payload' );
		foreach ( $required as $field ) {
			if ( empty( $entry_data[ $field ] ) ) {
				return new WP_Error( 'missing_field', sprintf( 'Required field "%s" is missing', $field ) );
			}
		}

		$entry_data = $this->prepare_entry_data( $entry_data );

		$entry_id = $this->repository->create_entry( $entry_data );

		if ( ! $entry_id ) {
			$this->logger->error(
				'Failed to create calendar entry',
				array(
					'error'   => $this->repository->get_last_error(),
					'keyword' => $entry_data['keyword'],
				)
			);
			return new WP_Error( 'db_error', 'Failed to create calendar entry: ' . $this->repository->get_last_error() );
		}

		$this->logger->info(
			'Calendar entry created',
			array(
				'entry_id'         => $entry_id,
				'keyword'          => $entry_data['keyword'],
				'publication_date' => $entry_data['publication_date'],
			)
		);

		return $entry_id;
	}

	public function update_entry( $entry_id, $updates ) {
		global $wpdb;

		$updates                  = $this->prepare_entry_data( $updates );
		$updates['updated_at']    = current_time( 'mysql' );
		$updates['user_modified'] = 1;

		$result = $this->repository->update_entry(
			$entry_id,
			$updates,
			$this->get_format_for_fields( array_keys( $updates ) )
		);

		if ( ! $result ) {
			$this->logger->error(
				'Failed to update calendar entry',
				array(
					'entry_id' => $entry_id,
					'error'    => $this->repository->get_last_error(),
				)
			);
			return new WP_Error( 'db_error', 'Failed to update entry' );
		}

		$this->logger->info( 'Calendar entry updated', array( 'entry_id' => $entry_id ) );

		return true;
	}

	public function delete_entry( $entry_id ) {
		$result = $this->repository->delete_entry( $entry_id );

		if ( ! $result ) {
			return new WP_Error( 'db_error', 'Failed to delete entry' );
		}

		$this->logger->info( 'Calendar entry deleted', array( 'entry_id' => $entry_id ) );

		return true;
	}

	public function get_entry( $entry_id ) {
		$entry = $this->repository->get_entry( $entry_id );

		if ( ! $entry ) {
			return new WP_Error( 'not_found', 'Entry not found' );
		}

		if ( ! empty( $entry['wisewand_payload'] ) ) {
			$payload = json_decode( $entry['wisewand_payload'], true );

			// Remove deprecated/legacy fields from payload
			$deprecated_fields = array( 'use_inlineimages' );
			foreach ( $deprecated_fields as $field ) {
				if ( isset( $payload[ $field ] ) ) {
					unset( $payload[ $field ] );
				}
			}

			$entry['wisewand_payload_decoded'] = $payload;
		}

		return $entry;
	}

	/**
	 * Get calendar entries with optional filters
	 *
	 * Performance optimization: Added LIMIT clause to prevent memory exhaustion
	 * with large datasets. Default limit is 1000 entries.
	 *
	 * @param array $filters Optional filters (supports 'limit' parameter).
	 * @return array Calendar entries
	 */
	public function get_entries( $filters = array() ) {
		$where_clauses = array( '1=1' );
		$where_values  = array();

		if ( isset( $filters['publication_date'] ) ) {
			$where_clauses[] = 'publication_date = %s';
			$where_values[]  = $filters['publication_date'];
		}

		if ( isset( $filters['publication_date_start'] ) ) {
			$where_clauses[] = 'publication_date >= %s';
			$where_values[]  = $filters['publication_date_start'];
		}

		if ( isset( $filters['publication_date_end'] ) ) {
			$where_clauses[] = 'publication_date <= %s';
			$where_values[]  = $filters['publication_date_end'];
		}

		if ( isset( $filters['wisewand_status'] ) ) {
			$where_clauses[] = 'wisewand_status = %s';
			$where_values[]  = $filters['wisewand_status'];
		}

		if ( isset( $filters['wordpress_status'] ) ) {
			$where_clauses[] = 'wordpress_status = %s';
			$where_values[]  = $filters['wordpress_status'];
		}

		if ( isset( $filters['keyword_source'] ) ) {
			$where_clauses[] = 'keyword_source = %s';
			$where_values[]  = $filters['keyword_source'];
		}

		// PERFORMANCE FIX: Add LIMIT clause to prevent unbounded queries
		// Default limit: 1000 entries (can be overridden via $filters['limit'])
		$limit = isset( $filters['limit'] ) ? absint( $filters['limit'] ) : 1000;

		// Securely validate and construct ORDER BY clause (whitelisted columns only)
		$order_by = $this->sanitize_order_by( $filters['order_by'] ?? null );

		$entries = $this->repository->get_entries( $where_clauses, $where_values, $order_by, $limit );

		foreach ( $entries as &$entry ) {
			if ( ! empty( $entry['wisewand_payload'] ) ) {
				$entry['wisewand_payload_decoded'] = json_decode( $entry['wisewand_payload'], true );
			}
		}

		return $entries;
	}

	public function check_keyword_exists( $keyword, $exclude_entry_id = null ) {
		return $this->repository->check_keyword_exists( $keyword, $exclude_entry_id );
	}

	public function bulk_create_entries( $entries_data ) {
		global $wpdb;
		$created_ids = array();
		$errors      = array();
		$duplicates  = array();

		foreach ( $entries_data as $entry_data ) {
			// Check if keyword+date already exists
			$keyword = $entry_data['keyword'] ?? '';
			$date    = $entry_data['publication_date'] ?? '';

			if ( $keyword && $date ) {
				$existing = $this->repository->check_duplicate( $keyword, $date );

				if ( $existing ) {
					$duplicates[] = array(
						'keyword'     => $keyword,
						'date'        => $date,
						'existing_id' => $existing,
					);
					continue; // Skip this entry
				}
			}

			$result = $this->create_entry( $entry_data );

			if ( is_wp_error( $result ) ) {
				// Check if it's a duplicate error
				if ( strpos( $result->get_error_message(), 'Duplicate entry' ) !== false ) {
					$duplicates[] = array(
						'keyword' => $entry_data['keyword'] ?? 'unknown',
						'date'    => $entry_data['publication_date'] ?? 'unknown',
					);
				} else {
					$errors[] = array(
						'keyword' => $entry_data['keyword'] ?? 'unknown',
						'error'   => $result->get_error_message(),
					);
				}
			} else {
				$created_ids[] = $result;
			}
		}

		return array(
			'created_ids'     => $created_ids,
			'duplicates'      => $duplicates,
			'errors'          => $errors,
			'success_count'   => count( $created_ids ),
			'duplicate_count' => count( $duplicates ),
			'failed_count'    => count( $errors ),
		);
	}

	public function get_statistics() {
		$stats = array(
			'total_scheduled'     => $this->repository->get_total_count(),
			'by_wisewand_status'  => array(),
			'by_wordpress_status' => array(),
			'upcoming_7_days'     => 0,
			'this_month'          => 0,
		);

		$wisewand_statuses = $this->repository->get_status_counts( 'wisewand_status' );

		foreach ( $wisewand_statuses as $row ) {
			$stats['by_wisewand_status'][ $row['wisewand_status'] ] = $row['count'];
		}

		$wordpress_statuses = $this->repository->get_status_counts( 'wordpress_status' );

		foreach ( $wordpress_statuses as $row ) {
			$stats['by_wordpress_status'][ $row['wordpress_status'] ] = $row['count'];
		}

		// Calculate total imported (draft + published)
		$imported_draft          = $stats['by_wordpress_status']['imported_draft'] ?? 0;
		$imported_published      = $stats['by_wordpress_status']['imported_published'] ?? 0;
		$stats['total_imported'] = $imported_draft + $imported_published;

		// Calculate success rate
		if ( $stats['total_scheduled'] > 0 ) {
			$stats['success_rate'] = round( ( $stats['total_imported'] / $stats['total_scheduled'] ) * 100, 1 );
		} else {
			$stats['success_rate'] = 0;
		}

		$stats['upcoming_7_days'] = $this->repository->get_upcoming_count(
			gmdate( 'Y-m-d' ),
			gmdate( 'Y-m-d', strtotime( '+7 days' ) )
		);

		$stats['this_month'] = $this->repository->get_date_range_count(
			gmdate( 'Y-m-01' ),
			gmdate( 'Y-m-01', strtotime( '+1 month' ) )
		);

		return $stats;
	}

	/**
	 * Check if entry has been imported to WordPress
	 *
	 * @param array $entry Calendar entry
	 * @return bool True if imported (draft or published)
	 */
	public function is_imported( $entry ) {
		if ( empty( $entry['wordpress_status'] ) ) {
			return false;
		}

		return in_array( $entry['wordpress_status'], array( 'imported_draft', 'imported_published' ) );
	}

	/**
	 * Get import subtype for an entry
	 *
	 * @param array $entry Calendar entry
	 * @return string|null 'draft' or 'published', or null if not imported
	 */
	public function get_import_subtype( $entry ) {
		if ( ! $this->is_imported( $entry ) ) {
			return null;
		}

		if ( $entry['wordpress_status'] === 'imported_draft' ) {
			return 'draft';
		}

		if ( $entry['wordpress_status'] === 'imported_published' ) {
			return 'published';
		}

		return null;
	}

	/**
	 * Get display status with formatting for UI
	 *
	 * @param array $entry Calendar entry
	 * @return array Status display data with label, color, icon, is_success
	 */
	public function get_display_status( $entry ) {
		$wordpress_status = $entry['wordpress_status'] ?? '';
		$wisewand_status  = $entry['wisewand_status'] ?? '';

		// Imported statuses (success state)
		if ( $wordpress_status === 'imported_draft' ) {
			return array(
				'label'       => 'Imported (Draft)',
				'color'       => '#10b981',
				'icon'        => '✓',
				'is_success'  => true,
				'badge_class' => 'imported-draft',
			);
		}

		if ( $wordpress_status === 'imported_published' ) {
			return array(
				'label'       => 'Imported (Published)',
				'color'       => '#10b981',
				'icon'        => '✓',
				'is_success'  => true,
				'badge_class' => 'imported-published',
			);
		}

		// Other WordPress statuses
		if ( $wordpress_status === 'importing' ) {
			return array(
				'label'       => 'Importing...',
				'color'       => '#eab308',
				'icon'        => '⏳',
				'is_success'  => false,
				'badge_class' => 'importing',
			);
		}

		if ( $wordpress_status === 'failed' ) {
			return array(
				'label'       => 'Failed',
				'color'       => '#ef4444',
				'icon'        => '✗',
				'is_success'  => false,
				'badge_class' => 'failed',
			);
		}

		// Wisewand statuses
		if ( $wisewand_status === 'ready' ) {
			return array(
				'label'       => 'Ready',
				'color'       => '#8b5cf6', // Purple (distinct from generating amber)
				'icon'        => '✓',
				'is_success'  => false,
				'badge_class' => 'ready',
			);
		}

		if ( $wisewand_status === 'generating' ) {
			return array(
				'label'       => 'Generating',
				'color'       => '#f59e0b',
				'icon'        => '⚙',
				'is_success'  => false,
				'badge_class' => 'generating',
			);
		}

		if ( $wisewand_status === 'creating' ) {
			return array(
				'label'       => 'Creating',
				'color'       => '#3b82f6',
				'icon'        => '⚙',
				'is_success'  => false,
				'badge_class' => 'creating',
			);
		}

		if ( $wisewand_status === 'failed' ) {
			return array(
				'label'       => 'Failed',
				'color'       => '#ef4444',
				'icon'        => '✗',
				'is_success'  => false,
				'badge_class' => 'failed',
			);
		}

		// Default/Scheduled
		return array(
			'label'       => 'Scheduled',
			'color'       => '#3b82f6',
			'icon'        => '📅',
			'is_success'  => false,
			'badge_class' => 'scheduled',
		);
	}

	private function prepare_entry_data( $data ) {
		$prepared = array();

		if ( isset( $data['wisewand_payload'] ) ) {
			if ( is_array( $data['wisewand_payload'] ) ) {
				$prepared['wisewand_payload'] = wp_json_encode( $data['wisewand_payload'] );
			} else {
				$prepared['wisewand_payload'] = $data['wisewand_payload'];
			}
		}

		$text_fields = array( 'keyword', 'search_intent', 'wisewand_article_id', 'wisewand_project_id', 'wisewand_persona_id', 'wisewand_lang', 'wisewand_type', 'wisewand_length', 'wisewand_error', 'wordpress_error', 'publication_time_range', 'target_post_type', 'target_category', 'target_tags', 'wordpress_categories', 'notes', 'keyword_source', 'publication_action', 'wisewand_status', 'wordpress_status', 'content_merge_strategy' );

		foreach ( $text_fields as $field ) {
			if ( isset( $data[ $field ] ) ) {
				$prepared[ $field ] = sanitize_text_field( $data[ $field ] );
			}
		}

		$int_fields = array( 'search_volume', 'current_position', 'difficulty', 'existing_post_id', 'wordpress_post_id', 'target_author', 'wisewand_retry_count', 'user_modified', 'created_by', 'publish_as_draft', 'auto_publish' );

		foreach ( $int_fields as $field ) {
			if ( isset( $data[ $field ] ) ) {
				$prepared[ $field ] = absint( $data[ $field ] );
			}
		}

		$decimal_fields = array( 'priority_score' );
		foreach ( $decimal_fields as $field ) {
			if ( isset( $data[ $field ] ) ) {
				$prepared[ $field ] = floatval( $data[ $field ] );
			}
		}

		$date_fields = array( 'publication_date' );
		foreach ( $date_fields as $field ) {
			if ( isset( $data[ $field ] ) ) {
				$prepared[ $field ] = sanitize_text_field( $data[ $field ] );
			}
		}

		$time_fields = array( 'publication_time' );
		foreach ( $time_fields as $field ) {
			if ( isset( $data[ $field ] ) ) {
				$prepared[ $field ] = sanitize_text_field( $data[ $field ] );
			}
		}

		$datetime_fields = array( 'generation_triggered_at', 'generation_completed_at', 'import_started_at', 'published_at', 'scheduled_at' );
		foreach ( $datetime_fields as $field ) {
			if ( isset( $data[ $field ] ) ) {
				$prepared[ $field ] = sanitize_text_field( $data[ $field ] );
			}
		}

		return $prepared;
	}

	private function get_format_for_fields( $fields ) {
		$formats = array();

		foreach ( $fields as $field ) {
			if ( in_array( $field, array( 'id', 'search_volume', 'current_position', 'difficulty', 'existing_post_id', 'wordpress_post_id', 'target_author', 'wisewand_retry_count', 'user_modified', 'created_by', 'publish_as_draft', 'auto_publish' ) ) ) {
				$formats[] = '%d';
			} elseif ( $field === 'priority_score' ) {
				$formats[] = '%f';
			} else {
				$formats[] = '%s';
			}
		}

		return $formats;
	}

	/**
	 * Sanitize ORDER BY clause to prevent SQL injection
	 * Validates column names and directions against whitelist
	 *
	 * @param string|null $order_by User-provided ORDER BY clause
	 * @return string Sanitized ORDER BY clause
	 */
	private function sanitize_order_by( $order_by ) {
		// Default order
		$default_order = 'publication_date ASC, priority_score DESC';

		if ( empty( $order_by ) ) {
			return $default_order;
		}

		// Whitelist of allowed columns
		$allowed_columns = array(
			'id',
			'keyword',
			'keyword_source',
			'search_volume',
			'current_position',
			'search_intent',
			'difficulty',
			'priority_score',
			'publication_action',
			'wisewand_status',
			'wordpress_status',
			'publication_date',
			'publication_time',
			'publication_time_range',
			'publish_as_draft',
			'auto_publish',
			'target_post_type',
			'target_author',
			'scheduled_at',
			'generation_triggered_at',
			'generation_completed_at',
			'import_started_at',
			'published_at',
			'created_at',
			'updated_at',
			'user_modified',
		);

		// Parse ORDER BY clause
		$order_parts     = explode( ',', $order_by );
		$validated_parts = array();

		foreach ( $order_parts as $part ) {
			$part = trim( $part );

			// Match pattern: column_name [ASC|DESC]
			if ( preg_match( '/^(\w+)\s*(ASC|DESC)?$/i', $part, $matches ) ) {
				$column    = $matches[1];
				$direction = isset( $matches[2] ) ? strtoupper( $matches[2] ) : 'ASC';

				// Validate column is in whitelist
				if ( in_array( $column, $allowed_columns, true ) ) {
					// Use esc_sql for extra safety even though we validated
					global $wpdb;
					$validated_parts[] = esc_sql( $column ) . ' ' . $direction;
				}
			}
		}

		// If no valid parts, return default
		if ( empty( $validated_parts ) ) {
			$this->logger->warning(
				'Invalid ORDER BY clause provided, using default',
				array(
					'provided' => $order_by,
					'default'  => $default_order,
				)
			);
			return $default_order;
		}

		return implode( ', ', $validated_parts );
	}
}
