<?php
/**
 * Seed system categories and sync library templates from disk.
 *
 * @package JOCBM
 */

namespace JOCBM\Database;

use JOCBM\Services\TemplateLibrary;
use JOCBM\Services\BlockCategories;

defined( 'ABSPATH' ) || exit;

/**
 * Class Seeder
 */
final class Seeder {

	/**
	 * Seed default data / sync library templates.
	 */
	public static function seed(): void {
		self::seed_categories();
		self::seed_system_blocks();
		self::prune_obsolete_categories();

		/**
		 * Fires after categories and system blocks have been synced from disk.
		 */
		do_action( 'jocbm_after_seed' );
	}

	/**
	 * Default category definitions (hierarchical taxonomy).
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public static function default_categories(): array {
		return CategoryTaxonomy::definitions();
	}

	/**
	 * Upsert categories from a filterable list.
	 */
	private static function seed_categories(): void {
		global $wpdb;

		$table = Schema::table( 'categories' );

		/**
		 * Filter categories upserted into the library.
		 *
		 * Each item: slug, name, description?, parent_slug?, is_pro?, sort_order?
		 * Legacy tuple form [ slug, name, description, sort_order ] is also accepted.
		 *
		 * Pro: append categories for advanced block packs.
		 *
		 * @param array<int, array<string, mixed>|array<int, mixed>> $categories Categories.
		 */
		$categories = apply_filters( 'jocbm_seed_categories', self::default_categories() );

		foreach ( (array) $categories as $cat ) {
			$normalized = self::normalize_category( $cat );
			if ( ! $normalized ) {
				continue;
			}

			$existing_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT id FROM %i WHERE slug = %s',
					$table,
					$normalized['slug']
				)
			);

			$row = array(
				'name'        => $normalized['name'],
				'description' => $normalized['description'],
				'parent_slug' => $normalized['parent_slug'],
				'is_pro'      => $normalized['is_pro'],
				'sort_order'  => $normalized['sort_order'],
			);

			if ( $existing_id ) {
				$wpdb->update(
					$table,
					$row,
					array( 'id' => $existing_id ),
					array( '%s', '%s', '%s', '%d', '%d' ),
					array( '%d' )
				);
				continue;
			}

			$wpdb->insert(
				$table,
				array_merge(
					array( 'slug' => $normalized['slug'] ),
					$row
				),
				array( '%s', '%s', '%s', '%s', '%d', '%d' )
			);
		}
	}

	/**
	 * Remove legacy leaf categories that are no longer in the taxonomy and unused.
	 */
	private static function prune_obsolete_categories(): void {
		global $wpdb;

		$table  = Schema::table( 'categories' );
		$blocks = Schema::table( 'system_blocks' );
		$junction = Schema::table( 'system_block_categories' );
		$keep   = array();
		$defined = apply_filters( 'jocbm_seed_categories', self::default_categories() );

		foreach ( (array) $defined as $cat ) {
			$normalized = self::normalize_category( $cat );
			if ( $normalized ) {
				$keep[] = $normalized['slug'];
			}
		}

		if ( ! $keep ) {
			return;
		}

		$obsolete = $wpdb->get_col(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- %i table identifiers are not counted by PHPCS.
			$wpdb->prepare(
				'SELECT id FROM %i WHERE slug NOT IN (' . implode( ',', array_fill( 0, count( $keep ), '%s' ) ) . ')',
				$table,
				...$keep
			)
		);

		foreach ( (array) $obsolete as $id ) {
			$id = (int) $id;
			$used = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i WHERE category_id = %d',
					$blocks,
					$id
				)
			);
			if ( $used < 1 && '' !== $junction ) {
				$used = (int) $wpdb->get_var(
					$wpdb->prepare(
						'SELECT COUNT(*) FROM %i WHERE category_id = %d',
						$junction,
						$id
					)
				);
			}
			if ( $used > 0 ) {
				continue;
			}
			$wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );
		}
	}

	/**
	 * @param mixed $cat Category definition.
	 * @return array{slug:string,name:string,description:string,parent_slug:string,is_pro:int,sort_order:int}|null
	 */
	private static function normalize_category( $cat ): ?array {
		if ( ! is_array( $cat ) ) {
			return null;
		}

		// Legacy: [ slug, name, description, sort_order ].
		if ( isset( $cat[0] ) && is_string( $cat[0] ) ) {
			$slug = sanitize_key( (string) $cat[0] );
			$name = sanitize_text_field( (string) ( $cat[1] ?? '' ) );
			if ( ! $slug || ! $name ) {
				return null;
			}
			return array(
				'slug'        => $slug,
				'name'        => $name,
				'description' => sanitize_text_field( (string) ( $cat[2] ?? '' ) ),
				'parent_slug' => '',
				'is_pro'      => 0,
				'sort_order'  => (int) ( $cat[3] ?? 0 ),
			);
		}

		$slug = sanitize_key( (string) ( $cat['slug'] ?? '' ) );
		$name = sanitize_text_field( (string) ( $cat['name'] ?? '' ) );
		if ( ! $slug || ! $name ) {
			return null;
		}

		return array(
			'slug'        => $slug,
			'name'        => $name,
			'description' => sanitize_text_field( (string) ( $cat['description'] ?? '' ) ),
			'parent_slug' => sanitize_key( (string) ( $cat['parent_slug'] ?? '' ) ),
			'is_pro'      => ! empty( $cat['is_pro'] ) ? 1 : 0,
			'sort_order'  => (int) ( $cat['sort_order'] ?? 0 ),
		);
	}

	/**
	 * Insert or update system blocks from registered template directories.
	 * Existing imported user blocks keep their snapshot until they opt in to update.
	 */
	private static function seed_system_blocks(): void {
		global $wpdb;

		$table     = Schema::table( 'system_blocks' );
		$cat_table = Schema::table( 'categories' );
		$cats      = $wpdb->get_results(
			$wpdb->prepare( 'SELECT id, slug FROM %i', $cat_table ),
			OBJECT_K
		);
		$by_slug   = array();
		$seen_keys = array();

		foreach ( $cats as $row ) {
			$by_slug[ $row->slug ] = (int) $row->id;
		}

		foreach ( TemplateLibrary::all() as $block ) {
			// Owner packs are file-first — never seed into system_blocks.
			if ( ! empty( $block['is_owner'] ) || 'creator' === sanitize_key( (string) ( $block['source'] ?? '' ) ) ) {
				continue;
			}

			$category_id = $by_slug[ $block['category'] ] ?? 0;
			if ( ! $category_id ) {
				// Auto-create missing leaf category so Pro packs are never silently skipped.
				$wpdb->insert(
					$cat_table,
					array(
						'slug'        => $block['category'],
						'name'        => ucwords( str_replace( '-', ' ', $block['category'] ) ),
						'description' => '',
						'parent_slug' => '',
						'is_pro'      => 0,
						'sort_order'  => 100,
					),
					array( '%s', '%s', '%s', '%s', '%d', '%d' )
				);
				$category_id = (int) $wpdb->insert_id;
				if ( ! $category_id ) {
					continue;
				}
				$by_slug[ $block['category'] ] = $category_id;
			}

			$category_slugs = is_array( $block['categories'] ?? null )
				? $block['categories']
				: array( $block['category'] );

			$seen_keys[ $block['slug'] . '::' . $block['variant'] ] = true;

			$is_hidden = ! empty( $block['is_hidden'] );
			$publisher = sanitize_key( (string) ( $block['publisher_status'] ?? 'ready' ) ) ?: 'ready';
			if ( 'ready' !== $publisher ) {
				$is_hidden = true;
			}

			$data = array(
				'category_id'   => $category_id,
				'slug'          => $block['slug'],
				'name'          => $block['name'],
				'description'   => $block['description'],
				'variant'       => $block['variant'],
				'markup'        => $block['markup'],
				'css_vars'      => wp_json_encode( $block['css_vars'] ),
				'default_attrs' => wp_json_encode( $block['default_attrs'] ),
				'field_schema'  => wp_json_encode( $block['field_schema'] ),
				'style_schema'  => wp_json_encode( $block['style_schema'] ),
				'template_hash' => $block['template_hash'],
				'is_pro'        => ! empty( $block['is_pro'] ) ? 1 : 0,
				'is_featured'   => ! empty( $block['is_featured'] ) ? 1 : 0,
				'source'        => sanitize_key( (string) ( $block['source'] ?? 'core' ) ) ?: 'core',
				'is_active'     => $is_hidden ? 0 : 1,
				'sort_order'    => $block['sort_order'],
			);

			$existing_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT id FROM %i WHERE slug = %s AND variant = %s',
					$table,
					$block['slug'],
					$block['variant']
				)
			);

			$formats = array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%d', '%d' );

			if ( $existing_id ) {
				// Library row always tracks files. My Blocks keep their snapshot until
				// the user reviews/applies the update (Update available → Review update).
				$wpdb->update(
					$table,
					$data,
					array( 'id' => $existing_id ),
					$formats,
					array( '%d' )
				);
				BlockCategories::sync( $existing_id, $category_slugs, $block['category'] );
				continue;
			}

			$wpdb->insert( $table, $data, $formats );
			$inserted_id = (int) $wpdb->insert_id;
			if ( $inserted_id ) {
				BlockCategories::sync( $inserted_id, $category_slugs, $block['category'] );
			}
		}

		// Refresh Owner pack linkage rows (import-only system_blocks) so extends
		// packs pick up base markup/schema changes and My Blocks can show updates.
		self::refresh_owner_linkages( $table, $by_slug );

		\JOCBM\Services\LibraryBlocks::sync_stylesheets();

		// Deactivate core/pro rows removed from disk; purge unreferenced Owner/creator ghosts.
		$existing   = $wpdb->get_results(
			$wpdb->prepare( 'SELECT id, slug, variant, source FROM %i', $table ),
			ARRAY_A
		);
		$user_table = Schema::table( 'user_blocks' );
		$owner_keys = TemplateLibrary::owner_pack_keys();
		foreach ( $existing ?: array() as $row ) {
			$key    = $row['slug'] . '::' . $row['variant'];
			$source = sanitize_key( (string) ( $row['source'] ?? 'core' ) ) ?: 'core';
			$id     = (int) $row['id'];
			$is_owner_ghost = isset( $owner_keys[ $key ] ) || 'creator' === $source;

			if ( $is_owner_ghost ) {
				$in_use = (int) $wpdb->get_var(
					$wpdb->prepare(
						'SELECT COUNT(1) FROM %i WHERE system_block_id = %d',
						$user_table,
						$id
					)
				);
				if ( $in_use > 0 ) {
					// Keep linkage for imported My Blocks; hide from JBM (FS merge handles ready packs).
					$wpdb->update(
						$table,
						array( 'is_active' => 0 ),
						array( 'id' => $id ),
						array( '%d' ),
						array( '%d' )
					);
					continue;
				}
				$wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );
				BlockCategories::delete_for_block( $id );
				continue;
			}

			if ( empty( $seen_keys[ $key ] ) ) {
				$wpdb->update(
					$table,
					array( 'is_active' => 0 ),
					array( 'id' => $id ),
					array( '%d' ),
					array( '%d' )
				);
			}
		}
	}

	/**
	 * Keep inactive Owner system_blocks rows in sync with resolved disk packs.
	 *
	 * Seeding skips Owner packs (file-first for JBM), but My Blocks imports create
	 * linkage rows whose template_hash must track resolved markup — including when
	 * an `extends` base template changes.
	 *
	 * @param string               $table   system_blocks table.
	 * @param array<string, int>   $by_slug Category slug → id map.
	 */
	private static function refresh_owner_linkages( string $table, array $by_slug ): void {
		global $wpdb;

		foreach ( TemplateLibrary::all() as $block ) {
			if ( empty( $block['is_owner'] ) ) {
				continue;
			}

			$slug    = sanitize_key( (string) ( $block['slug'] ?? '' ) );
			$variant = sanitize_key( (string) ( $block['variant'] ?? 'default' ) ) ?: 'default';
			if ( '' === $slug ) {
				continue;
			}

			$existing_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT id FROM %i WHERE slug = %s AND variant = %s',
					$table,
					$slug,
					$variant
				)
			);
			if ( ! $existing_id ) {
				continue;
			}

			$category    = sanitize_key( (string) ( $block['category'] ?? 'misc' ) ) ?: 'misc';
			$category_id = $by_slug[ $category ] ?? 0;
			if ( ! $category_id ) {
				$category_id = (int) $wpdb->get_var(
					$wpdb->prepare(
						'SELECT category_id FROM %i WHERE id = %d',
						$table,
						$existing_id
					)
				);
			}

			$category_slugs = is_array( $block['categories'] ?? null )
				? $block['categories']
				: array( $category );

			$data = array(
				'category_id'   => $category_id,
				'name'          => sanitize_text_field( (string) ( $block['name'] ?? $slug ) ),
				'description'   => sanitize_text_field( (string) ( $block['description'] ?? '' ) ),
				'markup'        => (string) ( $block['markup'] ?? '' ),
				'css_vars'      => wp_json_encode( $block['css_vars'] ?? array() ),
				'default_attrs' => wp_json_encode( $block['default_attrs'] ?? array() ),
				'field_schema'  => wp_json_encode( $block['field_schema'] ?? array() ),
				'style_schema'  => wp_json_encode( $block['style_schema'] ?? array() ),
				'template_hash' => (string) ( $block['template_hash'] ?? '' ),
				'is_pro'        => ! empty( $block['is_pro'] ) ? 1 : 0,
				'is_featured'   => ! empty( $block['is_featured'] ) ? 1 : 0,
				'source'        => sanitize_key( (string) ( $block['source'] ?? 'core' ) ) ?: 'core',
				'is_active'     => 0,
				'sort_order'    => (int) ( $block['sort_order'] ?? 100 ),
			);

			$wpdb->update(
				$table,
				$data,
				array( 'id' => $existing_id ),
				array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%d', '%d' ),
				array( '%d' )
			);
			BlockCategories::sync( $existing_id, $category_slugs, $category );
		}
	}
}
