<?php
/**
 * Database setup and operations.
 *
 * @package ABCTransferSmartOrders
 */

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

/**
 * Get orders table name.
 *
 * @return string
 */
function abct_get_orders_table_name() {
	global $wpdb;
	return $wpdb->prefix . 'abct_orders';
}

/**
 * Get high-volume queue table name.
 *
 * @return string
 */
function abct_get_orders_queue_table_name() {
	global $wpdb;
	return $wpdb->prefix . 'abct_orders_queue';
}

/**
 * Get shared display lock table name.
 *
 * @return string
 */
function abct_get_shared_display_locks_table_name() {
	global $wpdb;
	return $wpdb->prefix . 'abct_shared_display_locks';
}

/**
 * Get internal queue status flow.
 *
 * @return string[]
 */
function abct_get_orders_queue_statuses() {
	return class_exists( 'ABCT_Status' ) ? ABCT_Status::all() : array(
		'new',
		'accepted',
		'in_production',
		'ready',
		'finished',
		'cancelled',
	);
}

/**
 * Resolve queue status manager singleton.
 *
 * @return ABCT_Status_Manager|null
 */
function abct_get_queue_status_manager() {
	static $manager = null;

	if ( null === $manager && class_exists( 'ABCT_Status_Manager' ) ) {
		$manager = new ABCT_Status_Manager();
	}

	return $manager;
}

/**
 * Plugin activation callback.
 *
 * @return void
 */
function abct_activate_plugin() {
	abct_create_orders_table();
	abct_create_orders_queue_table();
	abct_create_shared_display_locks_table();
	abct_seed_default_opening_hours();
	if ( function_exists( 'abct_premium_module_enabled' ) && abct_premium_module_enabled( 'board' ) ) {
		abct_queue_ensure_cleanup_schedule();
	} else {
		abct_queue_clear_cleanup_schedule();
	}
	// FIX: Track schema version so updates run on existing installs.
	update_option( 'abct_so_db_version', ABCT_SO_DB_VERSION );
}

/**
 * Update table schema when plugin version changes.
 *
 * @return void
 */
function abct_maybe_upgrade_database() {
	$current_version = (string) get_option( 'abct_so_db_version', '' );
	if ( ABCT_SO_DB_VERSION === $current_version ) {
		return;
	}

	abct_create_orders_table();
	abct_create_orders_queue_table();
	abct_create_shared_display_locks_table();
	if ( function_exists( 'abct_premium_module_enabled' ) && abct_premium_module_enabled( 'board' ) ) {
		abct_queue_ensure_cleanup_schedule();
	} else {
		abct_queue_clear_cleanup_schedule();
	}
	update_option( 'abct_so_db_version', ABCT_SO_DB_VERSION );
	if ( function_exists( 'abct_so_log' ) ) {
		abct_so_log( sprintf( 'Database schema upgraded to %s.', ABCT_SO_DB_VERSION ) );
	}
}

/**
 * Create orders table.
 *
 * @return void
 */
function abct_create_orders_table() {
	global $wpdb;

	$table_name      = abct_get_orders_table_name();
	$charset_collate = $wpdb->get_charset_collate();

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';

	$sql = "CREATE TABLE {$table_name} (
		id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		woo_order_id bigint(20) unsigned DEFAULT NULL,
		customer_name varchar(255) NOT NULL,
		phone varchar(50) NOT NULL,
		order_data longtext NOT NULL,
		total decimal(10,2) NOT NULL DEFAULT 0.00,
		status varchar(30) NOT NULL DEFAULT 'new',
		viewed tinyint(1) NOT NULL DEFAULT 0,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY  (id),
		UNIQUE KEY woo_order_id (woo_order_id),
		KEY status (status),
		KEY viewed (viewed),
		KEY created_at (created_at),
		KEY updated_at (updated_at)
	) {$charset_collate};";

	dbDelta( $sql );
}

/**
 * Create queue table used by the Kanban production board.
 *
 * @return void
 */
function abct_create_orders_queue_table() {
	global $wpdb;

	$table_name      = abct_get_orders_queue_table_name();
	$charset_collate = $wpdb->get_charset_collate();

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';

	$sql = "CREATE TABLE {$table_name} (
		id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
		order_id bigint(20) unsigned NOT NULL,
		status varchar(30) NOT NULL DEFAULT 'new',
		priority int(11) NOT NULL DEFAULT 0,
		operator varchar(80) NOT NULL DEFAULT '',
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY  (id),
		UNIQUE KEY order_id (order_id),
		KEY status_priority (status, priority),
		KEY updated_at (updated_at),
		KEY created_at (created_at)
	) {$charset_collate};";

	dbDelta( $sql );
}

/**
 * Create shared display lock table used to coordinate shared-board ownership.
 *
 * @return void
 */
function abct_create_shared_display_locks_table() {
	global $wpdb;

	$table_name      = abct_get_shared_display_locks_table_name();
	$charset_collate = $wpdb->get_charset_collate();

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';

	$sql = "CREATE TABLE {$table_name} (
		display_id varchar(40) NOT NULL,
		occupancy_key varchar(64) NOT NULL,
		client_id varchar(80) NOT NULL,
		user_id bigint(20) unsigned NOT NULL DEFAULT 0,
		session_token_hash char(64) NOT NULL DEFAULT '',
		user_agent_hash char(64) NOT NULL DEFAULT '',
		ip_hash char(64) NOT NULL DEFAULT '',
		last_seen bigint(20) unsigned NOT NULL DEFAULT 0,
		created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY  (display_id),
		KEY client_id (client_id),
		KEY user_id (user_id),
		KEY last_seen (last_seen)
	) {$charset_collate};";

	dbDelta( $sql );
}

/**
 * Insert WooCommerce order into plugin orders table.
 *
 * @param int $order_id WooCommerce order ID.
 * @return void
 */
function abct_capture_woocommerce_order( $order_id ) {
	$order_id = absint( $order_id );
	if ( $order_id <= 0 || ! function_exists( 'wc_get_order' ) ) {
		return;
	}

	$order = wc_get_order( $order_id );
	if ( ! $order ) {
		if ( function_exists( 'abct_so_log' ) ) {
			abct_so_log( sprintf( 'Order capture failed: wc_get_order returned null for #%d.', $order_id ) );
		}
		return;
	}

	global $wpdb;
	$table_name = abct_get_orders_table_name();

	$existing_id = (int) $wpdb->get_var(
		$wpdb->prepare(
			"SELECT id FROM {$table_name} WHERE woo_order_id = %d LIMIT 1",
			$order_id
		)
	);
	if ( $existing_id > 0 ) {
		if ( function_exists( 'abct_so_log' ) ) {
			abct_so_log( sprintf( 'Order #%d already exists in smart-orders table as row #%d.', $order_id, $existing_id ) );
		}
		return;
	}

	$first_name    = (string) $order->get_billing_first_name();
	$last_name     = (string) $order->get_billing_last_name();
	$customer_name = trim( $first_name . ' ' . $last_name );
	if ( '' === $customer_name ) {
		$customer_name = (string) $order->get_formatted_billing_full_name();
	}
	if ( '' === $customer_name ) {
		$customer_name = __( 'Guest', 'abct-smart-orders' );
	}

	$items_payload = array();
	foreach ( $order->get_items() as $item ) {
		$items_payload[] = array(
			'name'     => $item->get_name(),
			'quantity' => (int) $item->get_quantity(),
			'total'    => wc_format_decimal( $item->get_total(), 2 ),
		);
	}

	$order_payload = array(
		'woo_order_id' => $order_id,
		'items'        => $items_payload,
		'payment'      => array(
			'method' => (string) $order->get_payment_method_title(),
		),
		'note'         => (string) $order->get_customer_note(),
	);

	$date_created = $order->get_date_created();
	$created_at   = $date_created ? $date_created->date( 'Y-m-d H:i:s' ) : current_time( 'mysql' );

	$inserted = $wpdb->insert(
		$table_name,
		array(
			'woo_order_id'  => $order_id,
			'customer_name' => sanitize_text_field( $customer_name ),
			'phone'         => sanitize_text_field( (string) $order->get_billing_phone() ),
			'order_data'    => wp_json_encode( $order_payload ),
			// AUDIT FIX: Persist normalized decimal as string to avoid locale float issues.
			'total'         => (string) wc_format_decimal( $order->get_total(), 2 ),
			'status'        => 'new',
			'viewed'        => 0,
			'created_at'    => $created_at,
		),
		array( '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s' )
	);

	if ( false === $inserted ) {
		// AUDIT FIX: Avoid false hard-fail on race conditions protected by unique key.
		if ( ! empty( $wpdb->last_error ) && false !== strpos( $wpdb->last_error, 'Duplicate entry' ) ) {
			return;
		}

		if ( function_exists( 'abct_so_log' ) ) {
			abct_so_log( sprintf( 'Order #%d insertion failed. DB error: %s', $order_id, (string) $wpdb->last_error ) );
		}
		return;
	}

	if ( function_exists( 'abct_so_log' ) ) {
		abct_so_log( sprintf( 'Order #%d inserted into smart-orders table.', $order_id ) );
	}
}

/**
 * Determine initial queue priority from WooCommerce order.
 *
 * @param WC_Order $order WooCommerce order.
 * @return int
 */
function abct_queue_resolve_priority( WC_Order $order ) {
	$total = (float) $order->get_total();
	if ( $total >= 300 ) {
		return 30;
	}

	if ( $total >= 120 ) {
		return 20;
	}

	return 10;
}

/**
 * Upsert WooCommerce order in queue table.
 *
 * @param int         $order_id WooCommerce order ID.
 * @param string|null $status Optional forced internal status.
 * @param string|null $operator Optional operator name.
 * @return void
 */
function abct_queue_upsert_order( $order_id, $status = null, $operator = null ) {
	$order_id = absint( $order_id );
	if ( $order_id <= 0 || ! function_exists( 'wc_get_order' ) ) {
		return;
	}

	$order = wc_get_order( $order_id );
	if ( ! $order ) {
		return;
	}

	$allowed_statuses = abct_get_orders_queue_statuses();
	$status           = null === $status ? ABCT_Status::NEW : sanitize_key( (string) $status );
	if ( function_exists( 'abct_queue_operational_to_legacy_status' ) ) {
		$status = abct_queue_operational_to_legacy_status( $status );
	}
	if ( ! in_array( $status, $allowed_statuses, true ) ) {
		$status = ABCT_Status::NEW;
	}

	global $wpdb;
	$table_name = abct_get_orders_queue_table_name();
	$existing   = $wpdb->get_row(
		$wpdb->prepare(
			"SELECT id, operator FROM {$table_name} WHERE order_id = %d LIMIT 1",
			$order_id
		),
		ARRAY_A
	);

	$existing_operator = '';
	if ( is_array( $existing ) && isset( $existing['operator'] ) ) {
		$existing_operator = sanitize_text_field( (string) $existing['operator'] );
	}
	$operator_raw   = null === $operator ? '' : sanitize_text_field( (string) $operator );
	$operator_value = '' !== $operator_raw ? $operator_raw : $existing_operator;

	$data = array(
		'order_id'  => $order_id,
		'status'    => $status,
		'priority'  => abct_queue_resolve_priority( $order ),
		'operator'  => $operator_value,
		'updated_at'=> current_time( 'mysql' ),
	);

	if ( is_array( $existing ) && ! empty( $existing['id'] ) ) {
		$wpdb->update(
			$table_name,
			$data,
			array( 'order_id' => $order_id ),
			array( '%d', '%s', '%d', '%s', '%s' ),
			array( '%d' )
		);
		return;
	}

	$data['created_at'] = current_time( 'mysql' );
	$wpdb->insert(
		$table_name,
		$data,
		array( '%d', '%s', '%d', '%s', '%s', '%s' )
	);
}

/**
 * Map WooCommerce status to plugin queue status.
 *
 * @param string $wc_status WooCommerce status.
 * @param string $current Current queue status.
 * @return string
 */
function abct_map_wc_to_queue_status( $wc_status, $current = 'new' ) {
	$wc_status = sanitize_key( (string) $wc_status );
	$current   = function_exists( 'abct_queue_normalize_operational_status' ) ? abct_queue_normalize_operational_status( $current ) : sanitize_key( (string) $current );

	$map = array(
		'completed' => 'completed',
		'cancelled' => 'cancelled',
		'failed'    => 'cancelled',
		'refunded'  => 'cancelled',
	);

	if ( isset( $map[ $wc_status ] ) ) {
		return $map[ $wc_status ];
	}

	if ( 'processing' === $wc_status && in_array( $current, array( 'completed', 'cancelled' ), true ) ) {
		return 'new';
	}

	return $current;
}

/**
 * Build the lightweight commercial signature used by board delta reenqueue.
 *
 * @param WC_Order|int|string $order_or_status Woo order, order ID or raw status.
 * @param bool|null           $is_paid Optional paid state when using raw status.
 * @return string
 */
function abct_queue_get_payment_runtime_signature( $order_or_status, $is_paid = null ) {
	$order = null;
	if ( $order_or_status instanceof WC_Order ) {
		$order = $order_or_status;
	} elseif ( null === $is_paid && function_exists( 'wc_get_order' ) ) {
		$order_id = absint( $order_or_status );
		if ( $order_id > 0 ) {
			$order = wc_get_order( $order_id );
		}
	}

	if ( $order instanceof WC_Order ) {
		$status  = sanitize_key( (string) $order->get_status() );
		$is_paid = (bool) $order->is_paid();
	} else {
		$status  = sanitize_key( (string) $order_or_status );
		$is_paid = ! empty( $is_paid );
	}

	if ( '' === $status ) {
		return '';
	}

	return $status . '|' . ( $is_paid ? 'paid' : 'unpaid' );
}

/**
 * Reenqueue active board cards when the commercial signature changes.
 *
 * @param int                 $order_id Order ID.
 * @param string              $previous_signature Previously known signature.
 * @param WC_Order|false|null $order Optional order object.
 * @return bool
 */
function abct_queue_maybe_reenqueue_for_payment_change( $order_id, $previous_signature = '', $order = null ) {
	$order_id = absint( $order_id );
	if ( $order_id <= 0 || ! function_exists( 'wc_get_order' ) ) {
		return false;
	}

	if ( ! $order instanceof WC_Order ) {
		$order = wc_get_order( $order_id );
	}
	if ( ! $order instanceof WC_Order ) {
		return false;
	}

	$current_signature = abct_queue_get_payment_runtime_signature( $order );
	if ( '' === $current_signature ) {
		return false;
	}

	$signature_meta_key = '_abct_payment_runtime_signature';
	$stored_signature   = sanitize_text_field( (string) get_post_meta( $order_id, $signature_meta_key, true ) );
	$previous_signature = sanitize_text_field( (string) $previous_signature );

	if ( '' === $previous_signature ) {
		$previous_signature = $stored_signature;
	}

	if ( '' === $previous_signature && 'woocommerce_payment_complete' === current_filter() && $order->is_paid() ) {
		$previous_signature = abct_queue_get_payment_runtime_signature( $order->get_status(), false );
	}

	update_post_meta( $order_id, $signature_meta_key, $current_signature );

	if ( '' === $previous_signature || $previous_signature === $current_signature ) {
		return false;
	}

	$current_operational = function_exists( 'abct_queue_get_operational_status' ) ? abct_queue_get_operational_status( $order_id ) : ABCT_Status::NEW;
	$active_statuses     = function_exists( 'abct_queue_active_operational_statuses' ) ? abct_queue_active_operational_statuses() : array( 'new', 'in_preparation', 'ready' );
	if ( ! in_array( $current_operational, $active_statuses, true ) ) {
		return false;
	}

	if ( function_exists( 'abct_queue_sync_legacy_flow_meta' ) ) {
		abct_queue_sync_legacy_flow_meta(
			$order_id,
			$current_operational,
			array(
				'force_increment' => true,
			)
		);
		return true;
	}

	return false;
}

/**
 * Ensure queue has a row for every new WooCommerce order.
 *
 * @param int $order_id WooCommerce order ID.
 * @return void
 */
function abct_queue_capture_new_order( $order_id ) {
	if ( function_exists( 'abct_queue_initialize_order_operational_meta' ) ) {
		abct_queue_initialize_order_operational_meta( $order_id, 'woocommerce_new_order' );
	}
	abct_queue_upsert_order( $order_id, ABCT_Status::NEW );
}

/**
 * Keep queue synchronized with WooCommerce lifecycle without altering Woo core behavior.
 *
 * @param int            $order_id Order ID.
 * @param string         $old_status Previous status.
 * @param string         $new_status Next status.
 * @param WC_Order|false $order Order object.
 * @return void
 */
function abct_queue_on_wc_status_changed( $order_id, $old_status, $new_status, $order ) {
	$order_id = absint( $order_id );
	if ( $order_id <= 0 ) {
		return;
	}

	$paid_statuses       = function_exists( 'wc_get_is_paid_statuses' ) ? wc_get_is_paid_statuses() : array( 'processing', 'completed' );
	$paid_statuses       = array_map( 'sanitize_key', is_array( $paid_statuses ) ? $paid_statuses : array() );
	$previous_signature  = abct_queue_get_payment_runtime_signature(
		$old_status,
		in_array( sanitize_key( (string) $old_status ), $paid_statuses, true )
	);
	$current = function_exists( 'abct_queue_get_operational_status' ) ? abct_queue_get_operational_status( $order_id ) : ABCT_Status::NEW;
	$mapped = abct_map_wc_to_queue_status( $new_status, $current );

	if ( $mapped !== $current ) {
		$manager      = abct_get_queue_status_manager();
		$allow_reopen = function_exists( 'abct_queue_is_reopen_transition' ) ? abct_queue_is_reopen_transition( $current, $mapped ) : in_array( $current, array( 'completed', 'cancelled' ), true );
		if ( $manager instanceof ABCT_Status_Manager && ! $manager->can_transition( $current, $mapped, array( 'allow_reopen' => $allow_reopen ) ) ) {
			// Preserve operator workflow: Woo sync must never downgrade queue state.
			abct_queue_maybe_reenqueue_for_payment_change( $order_id, $previous_signature, $order );
			return;
		}

		if ( function_exists( 'abct_queue_update_operational_state' ) ) {
			abct_queue_update_operational_state(
				$order_id,
				$mapped,
				array(
					'actor'        => 'woocommerce_sync',
					'silent'       => true,
					'allow_reopen' => $allow_reopen,
				)
			);
		} else {
			abct_queue_upsert_order( $order_id, $mapped );
		}

		do_action(
			'abct_queue_order_changed',
			array(
				'order_id' => $order_id,
				'status'   => function_exists( 'abct_queue_operational_to_legacy_status' ) ? abct_queue_operational_to_legacy_status( $mapped ) : $mapped,
				'status_canonical' => $mapped,
				'source'   => 'woocommerce_status',
			)
		);
	}

	abct_queue_maybe_reenqueue_for_payment_change( $order_id, $previous_signature, $order );
}

/**
 * Schedule queue cleanup cron.
 *
 * @return void
 */
function abct_queue_ensure_cleanup_schedule() {
	if ( wp_next_scheduled( 'abct_queue_cleanup_daily' ) ) {
		return;
	}

	wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', 'abct_queue_cleanup_daily' );
}

/**
 * Unschedule queue cleanup cron.
 *
 * @return void
 */
function abct_queue_clear_cleanup_schedule() {
	wp_clear_scheduled_hook( 'abct_queue_cleanup_daily' );
}

/**
 * Remove old finalized queue rows.
 *
 * @return void
 */
function abct_queue_cleanup_daily() {
	global $wpdb;

	$table_name = abct_get_orders_queue_table_name();
	$cutoff     = gmdate( 'Y-m-d H:i:s', time() - ( 30 * DAY_IN_SECONDS ) );
	$statuses   = array( ABCT_Status::FINISHED, ABCT_Status::CANCELLED );

	$wpdb->query(
		$wpdb->prepare(
			"DELETE FROM {$table_name}
			WHERE status IN (%s, %s)
			  AND updated_at < %s",
			$statuses[0],
			$statuses[1],
			$cutoff
		)
	);
}

/**
 * Register premium operational hooks.
 *
 * @return void
 */
function abct_register_operational_runtime_hooks() {
	static $registered = false;
	if ( $registered ) {
		return;
	}

	$premium_runtime = function_exists( 'abct_premium_module_enabled' ) && ( abct_premium_module_enabled( 'orders_panel' ) || abct_premium_module_enabled( 'board' ) );
	if ( ! $premium_runtime ) {
		abct_queue_clear_cleanup_schedule();
		return;
	}

	$registered = true;
	$board_enabled  = abct_premium_module_enabled( 'board' );
	$orders_enabled = abct_premium_module_enabled( 'orders_panel' );
	$legacy_orders  = (bool) apply_filters( 'abct_enable_legacy_orders_runtime', false );

	// Legacy table runtime is disabled by default and only enabled explicitly.
	if ( $orders_enabled && $legacy_orders ) {
		add_action( 'woocommerce_new_order', 'abct_capture_woocommerce_order', 20, 1 );
	}

	if ( $board_enabled ) {
		add_action( 'woocommerce_new_order', 'abct_queue_capture_new_order', 5, 1 );
		add_action( 'woocommerce_order_status_changed', 'abct_queue_on_wc_status_changed', 10, 4 );
		add_action( 'woocommerce_payment_complete', 'abct_queue_maybe_reenqueue_for_payment_change', 10, 1 );
		add_action( 'init', 'abct_queue_ensure_cleanup_schedule', 30 );
		add_action( 'abct_queue_cleanup_daily', 'abct_queue_cleanup_daily' );
		return;
	}

	abct_queue_clear_cleanup_schedule();
}
add_action( 'plugins_loaded', 'abct_register_operational_runtime_hooks', 60 );