<?php
// Exit if accessed directly.
defined( 'ABSPATH' ) || exit;

/**
 * Data store for wc_appointments_availability_cache.
 *
 * @package Woocommerce/Appointments
 * @since 5.0.0
 */
class WC_Appointments_Availability_Cache_Data_Store extends WC_Data_Store_WP {

	public const TABLE_NAME  = 'wc_appointments_availability_cache';
	public const CACHE_GROUP = 'wc-appointments-availability-cache';

	/**
     * Perform an UPSERT using a natural unique key across key columns.
     * Requires a unique index on (source, source_id, staff_id, product_id, start_ts, end_ts, status).
     *
     * @return int Insert ID if a new row is created, or existing row ID via LAST_INSERT_ID(id) on update.
     */
    protected function upsert_row( array $data ) {
		global $wpdb;

		$table = $wpdb->prefix . self::TABLE_NAME;

		// Build insert columns and values.
		$cols = array_keys( $data );
		$vals = array_values( $data );

		$placeholders = array_fill( 0, count( $vals ), '%s' );
		// Adjust numeric placeholders for performance/typing.
		foreach ( $cols as $i => $col ) {
			if ( in_array( $col, [ 'source_id','product_id','staff_id','priority','ordering','qty','start_ts','end_ts' ], true ) ) {
				$placeholders[ $i ] = '%d';
			}
		}

		// Columns to update on conflict (skip natural key cols).
		$update_cols = [
			'scope','appointable','priority','ordering','qty','range_type','rule_kind','status','date_modified',
		];

		$update_sets = [];
		foreach ( $update_cols as $uc ) {
			if ( array_key_exists( $uc, $data ) ) {
				$update_sets[] = $wpdb->prepare( "{$uc} = %s", $data[ $uc ] );
			}
		}
		// Always refresh date_modified.
		$update_sets[] = $wpdb->prepare( "date_modified = %s", current_time( 'mysql' ) );

		$sql = sprintf(
		    "INSERT INTO %s (%s) VALUES (%s)
			 ON DUPLICATE KEY UPDATE %s, id = LAST_INSERT_ID(id)",
		    $table,
		    implode( ',', $cols ),
		    implode( ',', $placeholders ),
		    implode( ', ', $update_sets ),
		);

		$wpdb->query( $wpdb->prepare( $sql, $vals ) );

		return (int) $wpdb->insert_id;
	}

	public function create( &$object ): void {
		global $wpdb;

		$object->apply_changes();

		$data = [
			'source'       => $object->get_source( 'edit' ),
			'source_id'    => (int) $object->get_source_id( 'edit' ),
			'product_id'   => (int) $object->get_product_id( 'edit' ),
			'staff_id'     => (int) $object->get_staff_id( 'edit' ),
			'scope'        => $object->get_scope( 'edit' ),
			'appointable'  => $object->get_appointable( 'edit' ),
			'priority'     => (int) $object->get_priority( 'edit' ),
			'ordering'     => (int) $object->get_ordering( 'edit' ),
			'qty'          => (int) $object->get_qty( 'edit' ),
			'start_ts'     => (int) $object->get_start_ts( 'edit' ),
			'end_ts'       => (int) $object->get_end_ts( 'edit' ),
			'range_type'   => $object->get_range_type( 'edit' ),
			'rule_kind'    => $object->get_rule_kind( 'edit' ),
			'status'       => $object->get_status( 'edit' ),
			'date_created' => current_time( 'mysql' ),
			'date_modified'=> current_time( 'mysql' ),
		];

		// Use UPSERT to avoid delete-then-insert churn and make writes idempotent.
		$insert_id = $this->upsert_row( $data );
		$object->set_id( (int) $insert_id );

		wp_cache_delete( $object->get_id(), self::CACHE_GROUP );
	}

	public function read( &$object ): void {
		$data = wp_cache_get( $object->get_id(), self::CACHE_GROUP );

		if ( false === $data ) {
			global $wpdb;

			$data = $wpdb->get_row(
			    $wpdb->prepare(
			        "SELECT
						id as id,
						source,
						source_id,
						product_id,
						staff_id,
						scope,
						appointable,
						priority,
						ordering,
						qty,
						start_ts,
						end_ts,
						range_type,
						rule_kind,
						status,
						date_created,
						date_modified
					FROM {$wpdb->prefix}" . self::TABLE_NAME . " WHERE id = %d LIMIT 1",
			        $object->get_id(),
			    ),
			    ARRAY_A,
			);

			if ( empty( $data ) ) {
				throw new Exception( __( 'Invalid availability cache item.', 'woocommerce-appointments' ) );
			}

			wp_cache_set( $object->get_id(), $data, self::CACHE_GROUP );
		}

		if ( is_array( $data ) ) {
			$object->set_props( $data );
			$object->set_object_read( true );
		}
	}

	public function update( &$object ): void {
		global $wpdb;

		$changes = $object->get_changes();
		$changes['date_modified'] = current_time( 'mysql' );

		// If natural-key columns are unchanged, normal update is fine; otherwise fall back to UPSERT for safety.
		$natural_keys = [ 'source', 'source_id', 'product_id', 'staff_id', 'start_ts', 'end_ts', 'status' ];
		$mutated_natural = array_intersect( array_keys( $changes ), $natural_keys );

		if ( [] === $mutated_natural ) {
			$wpdb->update(
			    $wpdb->prefix . self::TABLE_NAME,
			    $changes,
			    [ 'id' => $object->get_id() ],
			    null,
			    [ '%d' ],
			);
		} else {
			// Rebuild the full row using current values from the object and upsert.
			$data = [
				'source'       => $object->get_source( 'edit' ),
				'source_id'    => (int) $object->get_source_id( 'edit' ),
				'product_id'   => (int) $object->get_product_id( 'edit' ),
				'staff_id'     => (int) $object->get_staff_id( 'edit' ),
				'scope'        => $object->get_scope( 'edit' ),
				'appointable'  => $object->get_appointable( 'edit' ),
				'priority'     => (int) $object->get_priority( 'edit' ),
				'ordering'     => (int) $object->get_ordering( 'edit' ),
				'qty'          => (int) $object->get_qty( 'edit' ),
				'start_ts'     => (int) $object->get_start_ts( 'edit' ),
				'end_ts'       => (int) $object->get_end_ts( 'edit' ),
				'range_type'   => $object->get_range_type( 'edit' ),
				'rule_kind'    => $object->get_rule_kind( 'edit' ),
				'status'       => $object->get_status( 'edit' ),
				'date_created' => $object->get_date_created( 'edit' ) ? $object->get_date_created( 'edit' )->date( 'Y-m-d H:i:s' ) : current_time( 'mysql' ),
				'date_modified'=> current_time( 'mysql' ),
			];
			$this->upsert_row( $data );
		}

		$object->apply_changes();
		wp_cache_delete( $object->get_id(), self::CACHE_GROUP );
	}

	public function delete( &$object, $args = [] ): void {
		global $wpdb;

		$wpdb->delete(
		    $wpdb->prefix . self::TABLE_NAME,
		    [ 'id' => $object->get_id() ],
		    [ '%d' ],
		);

		wp_cache_delete( $object->get_id(), self::CACHE_GROUP );
		$object->set_id( 0 );
	}

	/**
	 * Query cached rows by filters.
	 *
	 * Filters (all optional):
	 * - product_id (int|int[]) - filter by product(s)
	 * - staff_id (int|int[])   - filter by staff
	 * - scope ('global'|'product'|'staff'|string[])
	 * - appointable ('yes'|'no'|string[])
	 * - time_between ['start_ts' => x, 'end_ts' => y] - intersecting rows with range
	 * - source ('availability'|'appointment'|string[])
	 * - status ('unpaid'|'paid'|'confirmed'|'expired'|...string[])
	 * - limit, offset, order ('ASC'|'DESC'), order_by (start_ts|end_ts|priority)
	 *
	 * Returns array of associative rows; callers can wrap them in data-objects if desired.
	 */
	public function get_items( array $filters = [] ) {
		global $wpdb;

		$where = [ '1=1' ];

		$add_in = function( $field, $values ) use ( &$where ): void {
			$values = is_array( $values ) ? array_map( 'intval', $values ) : [ (int) $values ];
			$values = array_filter( $values, static fn( int $v ): bool => 0 < $v );
			if ( [] !== $values ) {
				$where[] = sprintf( "%s IN (%s)", esc_sql( $field ), implode( ',', array_map( 'intval', $values ) ) );
			}
		};

		if ( isset( $filters['product_id'] ) ) { $add_in( 'product_id', $filters['product_id'] ); }
		if ( isset( $filters['staff_id'] ) )   { $add_in( 'staff_id', $filters['staff_id'] ); }
		if ( isset( $filters['source_id'] ) )  { $add_in( 'source_id', $filters['source_id'] ); }

		if ( isset( $filters['scope'] ) ) {
			$scopes = (array) $filters['scope'];
			$scopes = array_map( 'sanitize_text_field', $scopes );
			if ( [] !== $scopes ) {
				$esc = implode( "','", array_map( 'esc_sql', $scopes ) );
				$where[] = "scope IN ('{$esc}')";
			}
		}

		if ( isset( $filters['appointable'] ) ) {
			$vals = (array) $filters['appointable'];
			$vals = array_map( 'sanitize_text_field', $vals );
			if ( [] !== $vals ) {
				$esc = implode( "','", array_map( 'esc_sql', $vals ) );
				$where[] = "appointable IN ('{$esc}')";
			}
		}

		if ( isset( $filters['source'] ) ) {
			$vals = (array) $filters['source'];
			$vals = array_map( 'sanitize_text_field', $vals );
			if ( [] !== $vals ) {
				$esc = implode( "','", array_map( 'esc_sql', $vals ) );
				$where[] = "source IN ('{$esc}')";
			}
		}

		if ( isset( $filters['status'] ) ) {
			$statuses = (array) $filters['status'];
			$statuses = array_map( 'sanitize_text_field', $statuses );
			if ( [] !== $statuses ) {
				$esc = implode( "','", array_map( 'esc_sql', $statuses ) );
				$where[] = "status IN ('{$esc}')";
			}
		}

		if ( isset( $filters['time_between']['start_ts'], $filters['time_between']['end_ts'] ) ) {
			$start_ts = (int) $filters['time_between']['start_ts'];
			$end_ts   = (int) $filters['time_between']['end_ts'];
			// Intersecting ranges.
			$where[] = $wpdb->prepare( '(end_ts > %d AND start_ts < %d)', $start_ts, $end_ts );
		}

		$order_by = 'start_ts';
		if ( ! empty( $filters['order_by'] ) && in_array( $filters['order_by'], [ 'start_ts', 'end_ts', 'priority' ], true ) ) {
			$order_by = $filters['order_by'];
		}

		$order = 'ASC';
		if ( ! empty( $filters['order'] ) && in_array( strtoupper( $filters['order'] ), [ 'ASC', 'DESC' ], true ) ) {
			$order = strtoupper( $filters['order'] );
		}

		$limit  = isset( $filters['limit'] )  ? max( -1, (int) $filters['limit'] )  : -1;
		$offset = isset( $filters['offset'] ) ? max( 0, (int) $filters['offset'] ) : 0;

		$sql = "SELECT *
				  FROM {$wpdb->prefix}" . self::TABLE_NAME . '
				 WHERE ' . implode( ' AND ', $where ) . "
				 ORDER BY {$order_by} {$order}";

		if ( -1 < $limit ) {
			$sql .= $wpdb->prepare( ' LIMIT %d OFFSET %d', $limit, $offset );
		}

		return $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Delete multiple rows by source and source_id.
	 *
	 * @param string $source The source type ('availability' or 'appointment')
	 * @param int    $source_id The source ID
	 * @return int Number of rows deleted
	 */
	public function delete_by_source( $source, $source_id ) {
		global $wpdb;

		$deleted = $wpdb->delete(
		    $wpdb->prefix . self::TABLE_NAME,
		    [
				'source' => sanitize_text_field( $source ),
				'source_id' => (int) $source_id,
			],
		    [ '%s', '%d' ],
		);

		// Clear any cached objects that might be affected
		wp_cache_flush_group( self::CACHE_GROUP );

		return (int) $deleted;
	}

	/**
	 * Delete rows by multiple criteria (for cart operations).
	 *
	 * @param array $criteria Array of criteria to match
	 * @return int Number of rows deleted
	 */
	public function delete_by_criteria( array $criteria ) {
		global $wpdb;

		// Sanitize criteria
		$sanitized_criteria = [];
		$formats = [];

		foreach ( $criteria as $key => $value ) {
			switch ( $key ) {
				case 'source':
				case 'scope':
				case 'appointable':
				case 'status':
					$sanitized_criteria[ $key ] = sanitize_text_field( $value );
					$formats[] = '%s';
					break;
				case 'source_id':
				case 'product_id':
				case 'staff_id':
				case 'start_ts':
				case 'end_ts':
				case 'priority':
				case 'qty':
					$sanitized_criteria[ $key ] = (int) $value;
					$formats[] = '%d';
					break;
			}
		}

		if ( [] === $sanitized_criteria ) {
			return 0;
		}

		$deleted = $wpdb->delete(
		    $wpdb->prefix . self::TABLE_NAME,
		    $sanitized_criteria,
		    $formats,
		);

		// Clear any cached objects that might be affected
		wp_cache_flush_group( self::CACHE_GROUP );

		return (int) $deleted;
	}

	/**
	 * Bulk insert multiple cache rows efficiently.
	 *
	 * @param array $rows Array of row data arrays
	 * @return int Number of rows inserted
	 */
	public function bulk_insert( array $rows ) {
		global $wpdb;

		if ( [] === $rows ) {
			return 0;
		}

		$table = $wpdb->prefix . self::TABLE_NAME;
		$columns = [
			'source', 'source_id', 'product_id', 'staff_id', 'scope',
			'appointable', 'priority', 'ordering', 'qty', 'start_ts', 'end_ts',
			'range_type', 'rule_kind', 'status', 'date_created', 'date_modified',
		];

		// Optimize: Process in chunks to avoid query size limits and improve performance
		// Larger batches are more efficient, but we need to balance with query size limits
		$chunk_size = 500; // Process 500 rows at a time for optimal performance
		$total_rows = count( $rows );
		$total_inserted = 0;

		// Process rows in chunks
		for ( $offset = 0; $offset < $total_rows; $offset += $chunk_size ) {
			$chunk = array_slice( $rows, $offset, $chunk_size );
			if ( [] === $chunk ) {
				break;
			}

			$values = [];
			foreach ( $chunk as $row ) {
				// Ensure all required fields are present with defaults
				$row = wp_parse_args( $row, [
					'source' => '',
					'source_id' => 0,
					'product_id' => 0,
					'staff_id' => 0,
					'scope' => 'global',
					'appointable' => 'yes',
					'priority' => 10,
					'ordering' => 0,
					'qty' => 0,
					'start_ts' => 0,
					'end_ts' => 0,
					'range_type' => '',
					'rule_kind' => '',
					'status' => '',
					'date_created' => current_time( 'mysql' ),
					'date_modified' => current_time( 'mysql' ),
				] );

				$values[] = $wpdb->prepare(
				    "( %s, %d, %d, %d, %s, %s, %d, %d, %d, %d, %d, %s, %s, %s, %s, %s )",
				    sanitize_text_field( $row['source'] ),
				    (int) $row['source_id'],
				    (int) $row['product_id'],
				    (int) $row['staff_id'],
				    sanitize_text_field( $row['scope'] ),
				    sanitize_text_field( $row['appointable'] ),
				    (int) $row['priority'],
				    (int) $row['ordering'],
				    (int) $row['qty'],
				    (int) $row['start_ts'],
				    (int) $row['end_ts'],
				    sanitize_text_field( $row['range_type'] ),
				    sanitize_text_field( $row['rule_kind'] ),
				    sanitize_text_field( $row['status'] ),
				    $row['date_created'],
				    $row['date_modified'],
				);
			}

			$values_sql = implode( ',', $values );
			$columns_sql = implode( ',', $columns );

			$sql = "INSERT INTO {$table} ({$columns_sql}) VALUES {$values_sql}
					ON DUPLICATE KEY UPDATE
						scope = VALUES(scope),
						appointable = VALUES(appointable),
						priority = VALUES(priority),
						ordering = VALUES(ordering),
						qty = VALUES(qty),
						start_ts = VALUES(start_ts),
						end_ts = VALUES(end_ts),
						range_type = VALUES(range_type),
						rule_kind = VALUES(rule_kind),
						status = VALUES(status),
						date_modified = VALUES(date_modified)";

			$result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$total_inserted += (int) $result;
		}

		// Clear cache group since we've inserted new data (only once at the end)
		wp_cache_flush_group( self::CACHE_GROUP );

		return $total_inserted;
	}

	/**
	 * Count rows by source and source_id.
	 *
	 * @param string $source The source type
	 * @param int    $source_id The source ID
	 * @return int Number of matching rows
	 */
	public function count_by_source( $source, $source_id ) {
		global $wpdb;

		return (int) $wpdb->get_var(
		    $wpdb->prepare(
		        "SELECT COUNT(*) FROM {$wpdb->prefix}" . self::TABLE_NAME . " WHERE source = %s AND source_id = %d",
		        sanitize_text_field( $source ),
		        (int) $source_id,
		    ),
		);
	}

	/**
	 * Delete rows by time range and optional criteria.
	 *
	 * @param array $criteria Deletion criteria
	 * @return int Number of rows deleted
	 */
	public function delete_by_time_criteria( array $criteria ) {
		global $wpdb;

		$where = [];
		$values = [];

		if ( isset( $criteria['source'] ) ) {
			$where[] = 'source = %s';
			$values[] = sanitize_text_field( $criteria['source'] );
		}

		if ( isset( $criteria['status'] ) ) {
			$where[] = 'status = %s';
			$values[] = sanitize_text_field( $criteria['status'] );
		}

		if ( isset( $criteria['end_ts_before'] ) ) {
			$where[] = 'end_ts < %d';
			$values[] = (int) $criteria['end_ts_before'];
		}

		if ( [] === $where ) {
			return 0;
		}

		$where_sql = implode( ' AND ', $where );
		$sql = "DELETE FROM {$wpdb->prefix}" . self::TABLE_NAME . " WHERE {$where_sql}";

		$result = $wpdb->query( $wpdb->prepare( $sql, $values ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		// Clear cache group since we've deleted data
		wp_cache_flush_group( self::CACHE_GROUP );

		return (int) $result;
	}

	/**
	 * Clear all rows from the cache table.
	 *
	 * @return int Number of rows deleted
	 */
	public function clear_all() {
		global $wpdb;

		// Use TRUNCATE instead of DELETE for much faster performance on large tables
		// TRUNCATE is faster because it doesn't log individual row deletions
		$table = $wpdb->prefix . self::TABLE_NAME;
		$result = $wpdb->query( "TRUNCATE TABLE {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		// Clear cache group since we've deleted all data
		wp_cache_flush_group( self::CACHE_GROUP );

		return (int) $result;
	}

	/**
	 * Get availability rules for slots calculation with complex filtering.
	 * Optimized version with better query structure and caching.
	 *
	 * @param int   $product_id Product ID
	 * @param int   $start_ts Start timestamp
	 * @param int   $end_ts End timestamp
	 * @param array $staff_ids Array of staff IDs to include
	 * @param int   $limit Optional limit for pagination (default: -1 for no limit)
	 * @param int   $offset Optional offset for pagination (default: 0)
	 * @return array Array of rule data
	 */
	public function get_availability_rules_for_slots( $product_id, $start_ts, $end_ts, $staff_ids = [], $limit = -1, $offset = 0 ) {
		global $wpdb;

		// Normalize and validate inputs
		$product_id = (int) $product_id;
		$start_ts = (int) $start_ts;
		$end_ts = (int) $end_ts;

		// Extend time range to include overnight rules
		// Query one day before start and one day after end to capture rules that might affect the period
		$extended_start_ts = $start_ts - DAY_IN_SECONDS;
		$extended_end_ts = $end_ts + DAY_IN_SECONDS;

		if ( empty( $staff_ids ) ) {
			$staff_ids = [ 0 ];
		}
		$staff_ids = array_unique( array_map( 'intval', $staff_ids ) );

		// Validate pagination parameters
		$limit = (int) $limit;
		$offset = max( 0, (int) $offset );

		// Create cache key for this query (include pagination params)
		$cache_key = 'avail_rules_' . md5( serialize( [ $product_id, $start_ts, $end_ts, $staff_ids, $limit, $offset ] ) );
		$cached_result = wp_cache_get( $cache_key, self::CACHE_GROUP );

		if ( false !== $cached_result ) {
			#error_log( 'Cache hit for availability rules for slots: ' . $cache_key );
			return $cached_result;
		}

		$table = $wpdb->prefix . self::TABLE_NAME;
		$staff_placeholders = implode( ',', array_fill( 0, count( $staff_ids ), '%d' ) );

		// Optimized query: Use UNION to separate different rule types for better index usage
		$query_parts = [];
		$query_params = [];

		// 1. Global rules (scope = 'global')
		$query_parts[] = "
			SELECT product_id, staff_id, scope, start_ts, end_ts, appointable, qty, priority, ordering
			FROM {$table}
			WHERE source = 'availability'
			  AND scope = 'global'
			  AND start_ts <= %d
			  AND end_ts >= %d
			  AND staff_id IN ({$staff_placeholders})
		";
		$query_params = array_merge( $query_params, [ $extended_end_ts, $extended_start_ts ], $staff_ids );

		// 2. Product-specific rules (scope = 'product' AND product_id matches)
		$query_parts[] = "
			SELECT product_id, staff_id, scope, start_ts, end_ts, appointable, qty, priority, ordering
			FROM {$table}
			WHERE source = 'availability'
			  AND scope = 'product'
			  AND product_id = %d
			  AND start_ts <= %d
			  AND end_ts >= %d
			  AND staff_id IN ({$staff_placeholders})
		";
		$query_params = array_merge( $query_params, [ $product_id, $extended_end_ts, $extended_start_ts ], $staff_ids );

		// 3. Staff-specific rules (scope = 'staff')
		$query_parts[] = "
			SELECT product_id, staff_id, scope, start_ts, end_ts, appointable, qty, priority, ordering
			FROM {$table}
			WHERE source = 'availability'
			  AND scope = 'staff'
			  AND start_ts <= %d
			  AND end_ts >= %d
			  AND staff_id IN ({$staff_placeholders})
		";
		$query_params = array_merge( $query_params, [ $extended_end_ts, $extended_start_ts ], $staff_ids );

		// Combine with UNION and sort by priority DESC, ordering ASC, then by start_ts DESC to ensure
		// rules starting on the target date take precedence over overnight rules
		$final_query = '(' . implode( ') UNION ALL (', $query_parts ) . ') ORDER BY priority DESC, ordering ASC, start_ts DESC';

		// Add pagination if specified
		if ( 0 < $limit ) {
			$final_query .= $wpdb->prepare( ' LIMIT %d OFFSET %d', $limit, $offset );
		}

		$prepared_query = $wpdb->prepare( $final_query, $query_params );
		$results = $wpdb->get_results( $prepared_query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared


		// Cache the results for 5 minutes
		wp_cache_set( $cache_key, $results, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );

		return $results;
	}

	/**
	 * Get cached appointment rows for availability calculation.
	 *
	 * @param int   $product_id Product ID
	 * @param int   $start_ts Start timestamp
	 * @param int   $end_ts End timestamp
	 * @param array $staff_ids Array of staff IDs
	 * @return array Array of appointment data
	 */
	public function get_cached_appointments_for_availability( $product_id, $start_ts, $end_ts, $staff_ids = [] ) {
		$filters = [
			'source' => 'appointment',
			'product_id' => (int) $product_id,
			'time_between' => [
				'start_ts' => (int) $start_ts,
				'end_ts' => (int) $end_ts,
			],
			'order_by' => 'start_ts',
			'order' => 'ASC',
		];

		if ( ! empty( $staff_ids ) ) {
			$filters['staff_id'] = array_map( 'intval', $staff_ids );
		}

		return $this->get_items( $filters );
	}
}
