<?php

namespace EasyCriticalCSS;

use WP_Error;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class Database {
	private static $table_name;

	public static function init() {
		self::ensure_db_correct();
		add_action( 'easy_cc_cleanup_old_handshakes', [ __CLASS__, 'cleanup_old_handshakes' ] );
	}

	public static function get_table_name() {
		if ( empty( self::$table_name ) ) {
			global $wpdb;
			self::$table_name = $wpdb->prefix . 'easy_critical_css';
		}

		return self::$table_name;
	}

	public static function create_db_table() {
		global $wpdb;

		$table_name      = self::get_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE $table_name (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			page_url TEXT NOT NULL,
			url_hash CHAR(32) NOT NULL,
			post_id BIGINT UNSIGNED DEFAULT NULL,
			requested_time DATETIME NOT NULL,
			generated_time DATETIME DEFAULT NULL,
			updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			critical_css LONGTEXT DEFAULT NULL,
			remaining_css LONGTEXT DEFAULT NULL,
			secondary_css LONGTEXT DEFAULT NULL,
			size_savings TEXT DEFAULT NULL,
			handshake VARCHAR(64) DEFAULT NULL,
			processing_status ENUM('completed', 'expired', 'failed', 'pending', 'unprocessed') DEFAULT 'unprocessed',
			settings LONGTEXT DEFAULT NULL,
			last_error TEXT,
			consecutive_failures INT UNSIGNED DEFAULT 0,
			last_failure_class VARCHAR(32) DEFAULT NULL,
			last_failure_time DATETIME DEFAULT NULL,
			paused_until DATETIME DEFAULT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY url_hash (url_hash),
			KEY post_id (post_id),
			KEY handshake (handshake)
		) $charset_collate";

		// Make sure we have dbDelta available.
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * Ensures database is correct version.
	 *
	 * This method is excluded from coverage because it wraps wpdb logic and triggers side effects
	 * that are difficult to test in isolation (e.g., dbDelta and option checks).
	 *
	 * @codeCoverageIgnore
	 */
	public static function ensure_db_correct() {
		$stored_db_version = get_option( 'easy_cc_db_version' );
		if ( $stored_db_version !== Plugin::get_db_version() ) {
			self::create_db_table();
		}
	}

	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- All of these methods are dealing with direct DB queries.
	public static function sanitize_data( $data ) {
		global $wpdb;

		$table_name = esc_sql( self::get_table_name() );

		$table_columns  = $wpdb->get_col( "DESC $table_name", 0 ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sanitized_data = [];

		foreach ( $table_columns as $column_name ) {
			// Handle null values first.
			if (
				array_key_exists( $column_name, $data ) &&
				is_null( $data[ $column_name ] )
			) {
				$sanitized_data[ $column_name ] = null;
				continue;
			}

			// Skip if data not set or is array.
			if ( ! isset( $data[ $column_name ] ) || is_array( $data[ $column_name ] ) ) {
				continue;
			}

			$sanitized_data[ $column_name ] = $data[ $column_name ];

			// Numbers don't need additional sanitization.
			if ( ! is_numeric( $data[ $column_name ] ) ) {
				continue;
			}

			// Keep valid JSON strings as is.
			if ( ! Helpers::is_valid_json( $data[ $column_name ] ) ) {
				continue;
			}

			// Everything else needs to be escaped properly.
			$sanitized_data[ $column_name ] = esc_sql( $data[ $column_name ] );
		}

		return $sanitized_data;
	}

	public static function get_row_by_url_hash( $url_hash ) {
		global $wpdb;

		$table_name = esc_sql( self::get_table_name() );

		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM $table_name WHERE url_hash = %s", $url_hash ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);
	}

	public static function get_row_by_url( $page_url ) {
		// Convert url to hash as the hash lookup will be more efficient.
		$url_hash = md5( $page_url );

		return self::get_row_by_url_hash( $url_hash );
	}

	public static function get_row_by_post_id( $post_id ) {
		global $wpdb;

		$table_name = esc_sql( self::get_table_name() );

		// Prefer canonical permalink hash for this post.
		$expected_hash = Helpers::get_url_hash( (int) $post_id );
		if ( ! empty( $expected_hash ) ) {
			$by_hash = self::get_row_by_url_hash( $expected_hash );
			if ( ! empty( $by_hash ) ) {
				Debug::ecc_log(
					[
						'step'     => 'db_pick_by_hash',
						'post_id'  => $post_id,
						'pick_id'  => $by_hash['id'],
						'url_hash' => $expected_hash,
						'reason'   => 'matched_permalink_hash',
					]
				);

				return $by_hash;
			}
		}

		// If no row matches canonical hash, choose the "best" row by preferring completed or pending rows,
		// then most recent generated_time, then newest id. This avoids returning an older/expired duplicate.
		$sql = $wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			"SELECT * FROM {$table_name} WHERE post_id = %d
				ORDER BY FIELD(processing_status, 'completed','pending','unprocessed','failed','expired') DESC,
						 COALESCE(generated_time, '1970-01-01') DESC,
						 id DESC
				LIMIT 1",
			$post_id
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
		$row = $wpdb->get_row( $sql, ARRAY_A );

		if ( ! empty( $row ) ) {
			Debug::ecc_log(
				[
					'step'           => 'db_pick_best_row',
					'post_id'        => $post_id,
					'pick_id'        => $row['id'],
					'url_hash'       => isset( $row['url_hash'] ) ? $row['url_hash'] : null,
					'status'         => isset( $row['processing_status'] ) ? $row['processing_status'] : null,
					'generated_time' => isset( $row['generated_time'] ) ? $row['generated_time'] : null,
				]
			);
		}

		return $row;
	}

	public static function get_row( $identifier ) {
		// If hash, look up by hash.
		if ( preg_match( '/^[a-f0-9]{32}$/', $identifier ) ) {
			return self::get_row_by_url_hash( $identifier );
		}

		// If we have a number, it's a post ID.
		if ( is_numeric( $identifier ) ) {
			// Get expected URL hash first, avoiding older duplicates for this post.
			$expected_hash = Helpers::get_url_hash( $identifier );
			if ( ! empty( $expected_hash ) ) {
				$by_hash = self::get_row_by_url_hash( $expected_hash );
				if ( ! empty( $by_hash ) ) {
					return $by_hash;
				}
			}

			return self::get_row_by_post_id( $identifier );
		}

		return self::get_row_by_url( $identifier );
	}

	public static function upsert_row( $data ) {
		global $wpdb;

		$sanitized_data = self::sanitize_data( $data );

		// Take care of potential existing rows.
		$where        = [];
		$existing_row = null;
		if ( ! empty( $sanitized_data['url_hash'] ) ) {
			$where['url_hash'] = $sanitized_data['url_hash'];
			$existing_row      = self::get_row_by_url_hash( $sanitized_data['url_hash'] );
		} elseif ( ! empty( $sanitized_data['post_id'] ) ) {
			$where['post_id'] = $sanitized_data['post_id'];
			$existing_row     = self::get_row_by_post_id( $sanitized_data['post_id'] );
		} elseif ( ! empty( $sanitized_data['page_url'] ) ) {
			$where['page_url'] = $sanitized_data['page_url'];
			$existing_row      = self::get_row_by_url( $sanitized_data['page_url'] );
		}

		// If existing row was found, update it.
		if ( ! empty( $existing_row ) ) {
			$updated = $wpdb->update( self::get_table_name(), $sanitized_data, $where );

			// If update returned false, it failed.
			if ( $updated === false ) {
				return new WP_Error( 'db_update_failed', __( 'Failed to update existing row.', 'easy-critical-css' ) );
			}

			return [
				'row_id' => $existing_row['id'],
				'status' => 'updated',
			];
		}

		// No existing row found, insert a new one.
		$insert_result = $wpdb->insert( self::get_table_name(), $sanitized_data );
		if ( $insert_result === false ) {
			return new WP_Error( 'db_insert_failed', __( 'Failed to insert row.', 'easy-critical-css' ) );
		}

		return [
			'row_id' => $wpdb->insert_id,
			'status' => 'inserted',
		];
	}

	public static function save_last_error( $identifier, $last_error, $status = null ) {
		// Make sure we have a hash (all entries should have a hash).
		$generated = Critical_CSS::get_generated_css( $identifier );
		if ( empty( $generated['url_hash'] ) ) {
			return;
		}

		$data = [
			'url_hash'   => $generated['url_hash'],
			'last_error' => $last_error,
		];

		if ( ! empty( $status ) ) {
			$data['processing_status'] = $status;
		}

		self::upsert_row( $data );

		// We need to reset get_generated_css cache if we changed the status.
		if ( ! empty( $status ) ) {
			Critical_CSS::clear_cache( $identifier );
			Critical_CSS::clear_cache( $generated['url_hash'] );
			Critical_CSS::get_generated_css( $identifier );
		}
	}

	public static function delete_row( $url_hash = null, $post_id = null, $page_url = null ) {
		global $wpdb;

		// Ensure at least one identifier is provided.
		if ( empty( $url_hash ) && empty( $post_id ) && empty( $page_url ) ) {
			return false;
		}

		$where = [];
		if ( $url_hash ) {
			$where['url_hash'] = $url_hash;
		} elseif ( $post_id ) {
			$where['post_id'] = $post_id;
		} elseif ( $page_url ) {
			$where['page_url'] = $page_url;
		}

		// Delete the row.
		$deleted = $wpdb->delete( self::get_table_name(), $where );

		return (bool) $deleted;
	}

	public static function clear_all_styles() {
		global $wpdb;

		$table_name = esc_sql( self::get_table_name() );

		return $wpdb->query(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			"UPDATE $table_name
				SET
					requested_time    = NULL,
					generated_time    = NULL,
					critical_css      = NULL,
					remaining_css     = NULL,
					secondary_css     = NULL,
					size_savings      = NULL,
					processing_status = 'unprocessed'
			"
		);
	}

	public static function clear_individual_styles( $identifier ) {
		global $wpdb;

		$table_name = esc_sql( self::get_table_name() );
		$hash       = Helpers::get_url_hash( $identifier );

		return $wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"UPDATE {$table_name}
					SET
						generated_time    = NULL,
						critical_css      = NULL,
						remaining_css     = NULL,
						secondary_css     = NULL,
						size_savings      = NULL,
						processing_status = 'unprocessed'
					WHERE url_hash = %s
				",
				$hash
			)
		);
	}

	public static function cleanup_old_handshakes() {
		global $wpdb;

		$table_name = esc_sql( self::get_table_name() );
		$threshold  = gmdate( 'Y-m-d H:i:s', time() - 12 * HOUR_IN_SECONDS );

		$wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"UPDATE {$table_name} SET handshake = NULL WHERE requested_time < %s",
				$threshold
			)
		);
	}

	public static function dedupe_post_rows( $post_id = null, $handshake = null, $dry_run = true ) {
		global $wpdb;

		$table_name = esc_sql( self::get_table_name() );

		if ( empty( $post_id ) ) {
			return [
				'action' => 'none',
				'reason' => 'missing_post_id',
			];
		}

		$rows = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->prepare( "SELECT * FROM {$table_name} WHERE post_id = %d", (int) $post_id ),
			ARRAY_A
		);

		if ( ! is_array( $rows ) || count( $rows ) <= 1 ) {
			return [
				'action' => 'none',
				'reason' => 'no_duplicates',
				'rows'   => $rows,
			];
		}

		// Try to pick canonical row.
		$canonical = null;

		// 1) Prefer handshake match if provided.
		if ( ! empty( $handshake ) ) {
			foreach ( $rows as $r ) {
				if ( ! empty( $r['handshake'] ) && hash_equals( $r['handshake'], $handshake ) ) {
					$canonical = $r;
					break;
				}
			}
		}

		// 2) Prefer canonical permalink hash.
		if ( empty( $canonical ) ) {
			$expected_hash = Helpers::get_url_hash( (int) $post_id );
			if ( ! empty( $expected_hash ) ) {
				$by_hash = self::get_row_by_url_hash( $expected_hash );
				if ( ! empty( $by_hash ) && isset( $by_hash['post_id'] ) && (int) $by_hash['post_id'] === (int) $post_id ) {
					$canonical = $by_hash;
				}
			}
		}

		// 3) Fallback: choose best by processing_status, generated_time, id.
		if ( empty( $canonical ) ) {
			$best     = null;
			$priority = [ 'completed', 'pending', 'unprocessed', 'failed', 'expired' ];
			foreach ( $rows as $r ) {
				if ( empty( $best ) ) {
					$best = $r;
					continue;
				}

				$best_status_idx = array_search( isset( $best['processing_status'] ) ? $best['processing_status'] : 'unprocessed', $priority, true );
				$r_status_idx    = array_search( isset( $r['processing_status'] ) ? $r['processing_status'] : 'unprocessed', $priority, true );

				if ( $r_status_idx === false ) {
					$r_status_idx = count( $priority );
				}
				if ( $best_status_idx === false ) {
					$best_status_idx = count( $priority );
				}

				if ( $r_status_idx < $best_status_idx ) {
					$best = $r;
					continue;
				}

				// If same status priority, prefer later generated_time.
				$best_gen = ! empty( $best['generated_time'] ) ? strtotime( $best['generated_time'] ) : 0;
				$r_gen    = ! empty( $r['generated_time'] ) ? strtotime( $r['generated_time'] ) : 0;
				if ( $r_gen > $best_gen ) {
					$best = $r;
					continue;
				}

				// Finally prefer higher id.
				if ( (int) $r['id'] > (int) $best['id'] ) {
					$best = $r;
				}
			}

			$canonical = $best;
		}

		if ( empty( $canonical ) ) {
			return [
				'action' => 'error',
				'reason' => 'could_not_pick_canonical',
			];
		}

		$canonical_id   = (int) $canonical['id'];
		$canonical_hash = isset( $canonical['url_hash'] ) ? $canonical['url_hash'] : null;

		// Build merged data starting from canonical.
		$merged = $canonical;

		foreach ( $rows as $r ) {
			if ( (int) $r['id'] === $canonical_id ) {
				continue;
			}

			// Merge settings (JSON) without losing keys.
			$canon_settings = ! empty( $merged['settings'] ) ? json_decode( $merged['settings'], true ) : [];
			$other_settings = ! empty( $r['settings'] ) ? json_decode( $r['settings'], true ) : [];
			if ( is_array( $other_settings ) ) {
				foreach ( $other_settings as $k => $v ) {
					if ( ! isset( $canon_settings[ $k ] ) || $canon_settings[ $k ] === '' || $canon_settings[ $k ] === null ) {
						$canon_settings[ $k ] = $v;
					}
				}
				$merged['settings'] = wp_json_encode( $canon_settings );
			}

			// For css fields, prefer non-empty and the most recent generated_time.
			$fields = [ 'critical_css', 'remaining_css', 'secondary_css', 'size_savings', 'last_error' ];
			foreach ( $fields as $f ) {
				$cur = ! empty( $merged[ $f ] ) ? $merged[ $f ] : '';
				$oth = ! empty( $r[ $f ] ) ? $r[ $f ] : '';
				if ( $oth === '' ) {
					continue;
				}
				$cur_gen = ! empty( $merged['generated_time'] ) ? strtotime( $merged['generated_time'] ) : 0;
				$oth_gen = ! empty( $r['generated_time'] ) ? strtotime( $r['generated_time'] ) : 0;
				if ( $cur === '' || $oth_gen >= $cur_gen ) {
					$merged[ $f ] = $oth;
				}
			}

			// requested_time: prefer most recent non-empty requested_time to avoid losing it.
			$cur_req = ! empty( $merged['requested_time'] ) ? strtotime( $merged['requested_time'] ) : 0;
			$oth_req = ! empty( $r['requested_time'] ) ? strtotime( $r['requested_time'] ) : 0;
			if ( $oth_req > $cur_req ) {
				$merged['requested_time'] = $r['requested_time'];
			}

			// generated_time: prefer most recent.
			$cur_gen = ! empty( $merged['generated_time'] ) ? strtotime( $merged['generated_time'] ) : 0;
			$oth_gen = ! empty( $r['generated_time'] ) ? strtotime( $r['generated_time'] ) : 0;
			if ( $oth_gen > $cur_gen ) {
				$merged['generated_time'] = $r['generated_time'];
			}

			// processing_status: prefer higher priority status.
			$priority = [
				'completed'   => 5,
				'pending'     => 4,
				'unprocessed' => 3,
				'failed'      => 2,
				'expired'     => 1,
			];
			$cur_pr   = isset( $merged['processing_status'] ) ? $merged['processing_status'] : 'unprocessed';
			$oth_pr   = isset( $r['processing_status'] ) ? $r['processing_status'] : 'unprocessed';
			if ( isset( $priority[ $oth_pr ] ) && isset( $priority[ $cur_pr ] ) && $priority[ $oth_pr ] > $priority[ $cur_pr ] ) {
				$merged['processing_status'] = $oth_pr;
			}
		}

		// Prepare sanitized data for DB write.
		$write_data   = [];
		$allowed_cols = [ 'page_url', 'url_hash', 'post_id', 'requested_time', 'generated_time', 'critical_css', 'remaining_css', 'secondary_css', 'size_savings', 'handshake', 'processing_status', 'settings', 'last_error' ];
		foreach ( $allowed_cols as $c ) {
			if ( isset( $merged[ $c ] ) ) {
				$write_data[ $c ] = $merged[ $c ];
			}
		}

		// Dry-run: return plan with what we'd do.
		$duplicate_ids = array_map(
			function ( $r ) use ( $canonical_id ) {
				return (int) $r['id'];
			},
			array_filter(
				$rows,
				function ( $r ) use ( $canonical_id ) {
					return (int) $r['id'] !== $canonical_id;
				}
			)
		);

		$plan = [
			'action'         => $dry_run ? 'dry_run' : 'apply',
			'post_id'        => (int) $post_id,
			'canonical_id'   => $canonical_id,
			'canonical_hash' => $canonical_hash,
			'merged'         => $write_data,
			'delete_ids'     => $duplicate_ids,
			'rows'           => $rows,
		];

		Debug::ecc_log( array_merge( [ 'step' => 'dedupe_plan' ], $plan ) );

		if ( $dry_run ) {
			return $plan;
		}

		// Apply the merge in a transaction.
		$wpdb->query( 'START TRANSACTION' );

		$sanitized = self::sanitize_data( $write_data );
		$updated   = $wpdb->update( self::get_table_name(), $sanitized, [ 'id' => $canonical_id ] );
		if ( $updated === false ) {
			$wpdb->query( 'ROLLBACK' );
			return [
				'action' => 'error',
				'reason' => 'update_failed',
			];
		}

		// Delete duplicates
		foreach ( $duplicate_ids as $del_id ) {
			$del_ok = $wpdb->delete( self::get_table_name(), [ 'id' => $del_id ] );
			if ( $del_ok === false ) {
				$wpdb->query( 'ROLLBACK' );
				return [
					'action' => 'error',
					'reason' => 'delete_failed',
					'id'     => $del_id,
				];
			}
		}

		$wpdb->query( 'COMMIT' );

		// Clear static cache for affected identifier(s).
		if ( ! empty( $canonical_hash ) ) {
			Critical_CSS::clear_cache( $canonical_hash );
		}
		Critical_CSS::clear_cache( $post_id );

		return [
			'action' => 'applied',
			'plan'   => $plan,
		];
	}
}
