<?php
/**
 * Database table handler for layouts with flexible locations.
 *
 * @package OpusPriceBuilder\Database
 */

declare(strict_types=1);

namespace OpusPriceBuilder\Database;

use OpusPriceBuilder\Locations;

/**
 * Handles the layouts database table operations.
 */
class LayoutsTable {

	/**
	 * Table name without prefix.
	 */
	public const TABLE_NAME = 'oppb_layouts';

	/**
	 * Database version for migrations.
	 */
	public const DB_VERSION = '1.0.0';

	/**
	 * Option name for storing database version.
	 */
	public const DB_VERSION_OPTION = 'oppb_layouts_table_version';

	/**
	 * Get the full table name with prefix.
	 *
	 * @return string
	 */
	public static function getTableName(): string {
		global $wpdb;
		return $wpdb->prefix . self::TABLE_NAME;
	}

	/**
	 * Get available locations using centralized Locations class.
	 *
	 * @return array<string, string> Location key => label pairs.
	 */
	public static function getAvailableLocations(): array {
		$locations = Locations::getAll();
		$result    = array();

		foreach ( $locations as $key => $data ) {
			$result[ $key ] = $data['label'];
		}

		return $result;
	}

	/**
	 * Check if a location is valid using centralized Locations class.
	 *
	 * @param string $location Location key to check.
	 * @return bool
	 */
	public static function isValidLocation( string $location ): bool {
		return Locations::isValid( $location );
	}

	/**
	 * Create the layouts table.
	 *
	 * @return void
	 */
	public static function createTable(): void {
		global $wpdb;

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

		$sql = "CREATE TABLE {$table_name} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(255) NOT NULL,
			slug VARCHAR(255) NOT NULL,
			scenario ENUM('simple', 'sale', 'variable_simple', 'variable_sale') NOT NULL DEFAULT 'simple',
			active_locations LONGTEXT,
			canvas_data LONGTEXT,
			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 slug (slug),
			KEY scenario (scenario)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );

		// Set database version.
		update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
	}

	/**
	 * Run migrations if needed based on database version.
	 *
	 * @return void
	 */
	public static function maybeRunMigrations(): void {
		$current_version = get_option( self::DB_VERSION_OPTION, '1.0.0' );

		if ( version_compare( $current_version, self::DB_VERSION, '>=' ) ) {
			return;
		}

		// Run migrations in order.
		if ( version_compare( $current_version, '2.0.0', '>=' ) ) {
			self::migrateToV2();
		}

		// Update stored version.
		update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
	}

	/**
	 * Migrate from v1 to v2.
	 *
	 * @return void
	 */
	private static function migrateToV2(): void {
		// Do something for v2 migration if needed.
	}
	/**
	 * Drop the layouts table.
	 *
	 * @return void
	 */
	public static function dropTable(): void {
		global $wpdb;
		$table_name = self::getTableName();
		$wpdb->query( "DROP TABLE IF EXISTS {$table_name}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		delete_option( self::DB_VERSION_OPTION );
	}

	/**
	 * Get default active locations array.
	 *
	 * @return array<string, bool>
	 */
	private static function getDefaultActiveLocations(): array {
		$locations = self::getAvailableLocations();
		$defaults  = array();

		foreach ( array_keys( $locations ) as $location ) {
			$defaults[ $location ] = false;
		}

		return $defaults;
	}

	/**
	 * Parse active locations from database value.
	 *
	 * @param string|null $json_value JSON string from database.
	 * @return array<string, bool>
	 */
	private static function parseActiveLocations( ?string $json_value ): array {
		$defaults = self::getDefaultActiveLocations();

		if ( empty( $json_value ) ) {
			return $defaults;
		}

		$parsed = json_decode( $json_value, true );
		if ( ! is_array( $parsed ) ) {
			return $defaults;
		}

		// Merge with defaults to ensure all locations are present.
		return array_merge( $defaults, array_intersect_key( $parsed, $defaults ) );
	}

	/**
	 * Insert a new layout.
	 *
	 * @param array<string, mixed> $data Layout data.
	 * @return int|false The inserted ID or false on failure.
	 */
	public static function insert( array $data ): int|false {
		global $wpdb;

		$defaults = array(
			'name'             => '',
			'slug'             => '',
			'scenario'         => 'simple',
			'active_locations' => self::getDefaultActiveLocations(),
			'canvas_data'      => '{}',
		);

		$data = wp_parse_args( $data, $defaults );

		// Generate slug if not provided.
		if ( empty( $data['slug'] ) ) {
			$data['slug'] = sanitize_title( $data['name'] );
		}

		// Ensure unique slug.
		$data['slug'] = self::getUniqueSlug( $data['slug'] );

		// Prepare active_locations JSON.
		$active_locations = $data['active_locations'];
		if ( is_array( $active_locations ) ) {
			$active_locations = wp_json_encode( $active_locations );
		}

		// Prepare canvas_data JSON.
		$canvas_data = $data['canvas_data'];
		if ( is_string( $canvas_data ) ) {
			$canvas_data = json_decode( $canvas_data, true );
			if ( null === $canvas_data ) {
				$canvas_data = array();
			}
		}

		$result = $wpdb->insert(
			self::getTableName(),
			array(
				'name'             => sanitize_text_field( $data['name'] ),
				'slug'             => sanitize_title( $data['slug'] ),
				'scenario'         => $data['scenario'],
				'active_locations' => $active_locations,
				'canvas_data'      => wp_json_encode( $canvas_data ),		
			),
			array( '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false === $result ) {
			return false;
		}

		return (int) $wpdb->insert_id;
	}

	/**
	 * Update a layout.
	 *
	 * @param int                  $id   Layout ID.
	 * @param array<string, mixed> $data Layout data.
	 * @return bool
	 */
	public static function update( int $id, array $data ): bool {
		global $wpdb;

		$update_data = array();
		$formats     = array();

		if ( isset( $data['name'] ) ) {
			$update_data['name'] = sanitize_text_field( $data['name'] );
			$formats[]           = '%s';
		}

		if ( isset( $data['slug'] ) ) {
			$update_data['slug'] = sanitize_title( $data['slug'] );
			$formats[]           = '%s';
		}

		if ( isset( $data['scenario'] ) ) {
			$update_data['scenario'] = $data['scenario'];
			$formats[]               = '%s';
		}

		if ( isset( $data['active_locations'] ) ) {
			$active_locations = $data['active_locations'];
			if ( is_array( $active_locations ) ) {
				$active_locations = wp_json_encode( $active_locations );
			}
			$update_data['active_locations'] = $active_locations;
			$formats[]                       = '%s';
		}

		if ( isset( $data['canvas_data'] ) ) {
			$canvas_data = $data['canvas_data'];
			if ( is_string( $canvas_data ) ) {
				$canvas_data = json_decode( $canvas_data, true );
				if ( null === $canvas_data ) {
					$canvas_data = array();
				}
			}
			$update_data['canvas_data'] = wp_json_encode( $canvas_data );
			$formats[]                  = '%s';
		}

		if ( empty( $update_data ) ) {
			return false;
		}

		$result = $wpdb->update(
			self::getTableName(),
			$update_data,
			array( 'id' => $id ),
			$formats,
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Delete a layout.
	 *
	 * @param int $id Layout ID.
	 * @return bool
	 */
	public static function delete( int $id ): bool {
		global $wpdb;

		$result = $wpdb->delete(
			self::getTableName(),
			array( 'id' => $id ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Get a layout by ID.
	 *
	 * @param int $id Layout ID.
	 * @return object|null
	 */
	public static function getById( int $id ): ?object {
		global $wpdb;

		$table_name = self::getTableName();
		$result     = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table_name} WHERE id = %d", $id ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);

		return $result ? $result : null;
	}

	/**
	 * Get a layout by slug.
	 *
	 * @param string $slug Layout slug.
	 * @return object|null
	 */
	public static function getBySlug( string $slug ): ?object {
		global $wpdb;

		$table_name = self::getTableName();
		$result     = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table_name} WHERE slug = %s", $slug ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);

		return $result ? $result : null;
	}

	/**
	 * Get all layouts.
	 *
	 * @param array<string, mixed> $args Query arguments.
	 * @return array<object>
	 */
	public static function getAll( array $args = array() ): array {
		global $wpdb;

		$defaults = array(
			'scenario' => '',
			'search'   => '',
			'orderby'  => 'created_at',
			'order'    => 'DESC',
			'per_page' => 100,
			'page'     => 1,
		);

		$args       = wp_parse_args( $args, $defaults );
		$table_name = self::getTableName();

		$where_clauses = array();
		$where_values  = array();

		if ( ! empty( $args['scenario'] ) ) {
			$where_clauses[] = 'scenario = %s';
			$where_values[]  = $args['scenario'];
		}

		if ( ! empty( $args['search'] ) ) {
			$where_clauses[] = 'name LIKE %s';
			$where_values[]  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

		$where_sql = '';
		if ( ! empty( $where_clauses ) ) {
			$where_sql = 'WHERE ' . implode( ' AND ', $where_clauses );
		}

		$orderby = sanitize_sql_orderby( $args['orderby'] . ' ' . $args['order'] );
		if ( ! $orderby ) {
			$orderby = 'created_at DESC';
		}

		$limit  = (int) $args['per_page'];
		$offset = ( (int) $args['page'] - 1 ) * $limit;

		$sql            = "SELECT * FROM {$table_name} {$where_sql} ORDER BY {$orderby} LIMIT %d OFFSET %d"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$where_values[] = $limit;
		$where_values[] = $offset;

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

		$results = $wpdb->get_results( $prepared_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return $results ? $results : array();
	}

	/**
	 * Duplicate a layout.
	 *
	 * @param int $id Layout ID to duplicate.
	 * @return int|false New layout ID or false on failure.
	 */
	public static function duplicate( int $id ): int|false {
		$layout = self::getById( $id );
		if ( ! $layout ) {
			return false;
		}

		$data = array(
			'name'             => $layout->name . ' (Copy)',
			'slug'             => '',
			'scenario'         => $layout->scenario,
			'active_locations' => self::getDefaultActiveLocations(),
			'canvas_data'      => $layout->canvas_data,
		);

		return self::insert( $data );
	}

	/**
	 * Get active locations for a layout.
	 *
	 * @param int $id Layout ID.
	 * @return array<string, bool>
	 */
	public static function getLayoutActiveLocations( int $id ): array {
		$layout = self::getById( $id );
		if ( ! $layout ) {
			return self::getDefaultActiveLocations();
		}

		return self::parseActiveLocations( $layout->active_locations );
	}

	/**
	 * Get active layout for a specific scenario and location.
	 *
	 * @param string $scenario Price scenario.
	 * @param string $location Display location.
	 * @return object|null
	 */
	public static function getActiveForLocation( string $scenario, string $location ): ?object {
		global $wpdb;

		if ( ! self::isValidLocation( $location ) ) {
			return null;
		}

		$table_name = self::getTableName();

		// Get all layouts for this scenario.
		$layouts = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table_name} WHERE scenario = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$scenario
			)
		);

		if ( ! $layouts ) {
			return null;
		}

		// Find layout with this location active.
		foreach ( $layouts as $layout ) {
			$active_locations = self::parseActiveLocations( $layout->active_locations );
			if ( ! empty( $active_locations[ $location ] ) ) {
				return $layout;
			}
		}

		return null;
	}

	/**
	 * Set a layout as active for a specific location.
	 * Only one layout can be active per scenario+location combination.
	 *
	 * @param int    $id       Layout ID.
	 * @param string $location Display location.
	 * @param bool   $active   Whether to activate or deactivate.
	 * @return bool
	 */
	public static function setActiveForLocation( int $id, string $location, bool $active = true ): bool {
		global $wpdb;

		if ( ! self::isValidLocation( $location ) ) {
			return false;
		}

		$layout = self::getById( $id );
		if ( ! $layout ) {
			return false;
		}

		$table_name = self::getTableName();

		// If activating, first deactivate all other layouts for this scenario+location.
		if ( $active ) {
			$other_layouts = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT id, active_locations FROM {$table_name} WHERE scenario = %s AND id != %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$layout->scenario,
					$id
				)
			);

			foreach ( $other_layouts as $other ) {
				$other_locations = self::parseActiveLocations( $other->active_locations );
				if ( ! empty( $other_locations[ $location ] ) ) {
					$other_locations[ $location ] = false;
					self::update( (int) $other->id, array( 'active_locations' => $other_locations ) );
				}
			}
		}

		// Update this layout's activation status.
		$current_locations              = self::parseActiveLocations( $layout->active_locations );
		$current_locations[ $location ] = $active;

		return self::update( $id, array( 'active_locations' => $current_locations ) );
	}

	/**
	 * Get all active layouts for each location for a specific scenario.
	 *
	 * @param string $scenario Price scenario.
	 * @return array<string, object|null>
	 */
	public static function getActiveLayoutsForScenario( string $scenario ): array {
		$locations = self::getAvailableLocations();
		$active    = array();

		foreach ( array_keys( $locations ) as $location ) {
			$active[ $location ] = self::getActiveForLocation( $scenario, $location );
		}

		return $active;
	}

	/**
	 * Get all active layouts for all scenarios and locations.
	 *
	 * @return array<string, array<string, object|null>>
	 */
	public static function getAllActiveLayouts(): array {
		$scenarios = array( 'simple', 'sale', 'variable_simple', 'variable_sale' );
		$all       = array();

		foreach ( $scenarios as $scenario ) {
			$all[ $scenario ] = self::getActiveLayoutsForScenario( $scenario );
		}

		return $all;
	}

	/**
	 * Count layouts matching criteria.
	 *
	 * @param array<string, mixed> $args Query arguments.
	 * @return int
	 */
	public static function count( array $args = array() ): int {
		global $wpdb;

		$table_name = self::getTableName();

		$where_clauses = array();
		$where_values  = array();

		if ( ! empty( $args['scenario'] ) ) {
			$where_clauses[] = 'scenario = %s';
			$where_values[]  = $args['scenario'];
		}

		if ( ! empty( $args['search'] ) ) {
			$where_clauses[] = 'name LIKE %s';
			$where_values[]  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

		$where_sql = '';
		if ( ! empty( $where_clauses ) ) {
			$where_sql = 'WHERE ' . implode( ' AND ', $where_clauses );
		}

		$sql = "SELECT COUNT(*) FROM {$table_name} {$where_sql}"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( ! empty( $where_values ) ) {
			$sql = $wpdb->prepare( $sql, ...$where_values ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}

		return (int) $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Get a unique slug.
	 *
	 * @param string $slug  Base slug.
	 * @param int    $id    Exclude this ID from check.
	 * @return string
	 */
	private static function getUniqueSlug( string $slug, int $id = 0 ): string {
		global $wpdb;

		$table_name    = self::getTableName();
		$original_slug = $slug;
		$counter       = 1;

		while ( true ) {
			$existing = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM {$table_name} WHERE slug = %s AND id != %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$slug,
					$id
				)
			);

			if ( ! $existing ) {
				break;
			}

			$slug = $original_slug . '-' . $counter;
			++$counter;
		}

		return $slug;
	}
}
