<?php
/**
 * Migration #001 — initial schema.
 *
 * @package Postlinkly
 * @since   1.0.0
 */

declare(strict_types=1);

namespace Postlinkly\Bootstrap\Migrations;

use Postlinkly\Database\TableNames;

/**
 * Creates the baseline plugin schema on a fresh install.
 *
 * Two tables, created at once with their final shape. Every table
 * has an integer primary key, snake_case columns, BIGINT(20) post
 * identifiers matching WordPress's own convention, and indexes on every
 * column the suggestion engine and admin UI filter by.
 *
 * The schema is deliberately minimal — only data that must be persisted
 * lives here. Cached suggestion maps use transients; rule-application
 * audit trails live as columns on `report_links`, not separate tables;
 * internal-link rot is a derived query, not a stored result.
 *
 * The auto-link rules table (`postlinkly_keywords`) and URL-changer table
 * (`postlinkly_urls`) used to be created here too. They belong to the Pro
 * add-on, which creates them under the same names, so a site that already
 * has them keeps its rows.
 *
 * @since 1.0.0
 * @since 1.18.0 No longer creates the auto-link rules and URL-changer tables.
 */
final class Migration_001_InitialSchema implements MigrationInterface {

	/**
	 * @since 1.0.0
	 */
	public function get_version(): int {
		return 1;
	}

	/**
	 * @since 1.0.0
	 * @since 1.6.0 Returns bool; verifies every table was actually created.
	 * @since 1.18.0 Creates and verifies only the report-links and
	 *        target-keywords tables.
	 */
	public function run( \wpdb $wpdb ): bool {
		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}
		$collate = $wpdb->get_charset_collate();

		\dbDelta( $this->report_links_sql( $collate ) );
		\dbDelta( $this->target_keywords_sql( $collate ) );

		return $this->table_exists( $wpdb, $wpdb->prefix . TableNames::REPORT_LINKS )
			&& $this->table_exists( $wpdb, $wpdb->prefix . TableNames::TARGET_KEYWORDS );
	}

	/**
	 * Checks whether a table exists.
	 *
	 * `dbDelta()` does not report success or failure directly, so this is
	 * the only reliable signal that the `CREATE TABLE` actually took effect.
	 *
	 * @since 1.6.0
	 *
	 * @param \wpdb  $wpdb  Database instance.
	 * @param string $table Fully prefixed table name.
	 *
	 * @return bool
	 */
	private function table_exists( \wpdb $wpdb, string $table ): bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );

		return $table === $found;
	}

	/**
	 * Schema for the report-links table.
	 *
	 * Columns of note:
	 * - `keyword_id` references `postlinkly_keywords.id` (a Pro table).
	 *   Non-zero when the link was inserted by an auto-link rule.
	 * - `url_rule_id` references `postlinkly_urls.id` (a Pro table).
	 *   Non-zero when the link was modified by a URL-changer rule.
	 *   `original_url` preserves the value before the rule fired (for
	 *   "undo" and reporting). Both columns stay in the free schema so the
	 *   report keeps this history whether or not Pro is active.
	 * - `created_by_plugin` is true when Postlinkly itself inserted the
	 *   link (suggestion accept, auto-link rule, manual quick-link).
	 */
	private function report_links_sql( string $collate ): string {
		$table = TableNames::report_links();
		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			post_id bigint(20) unsigned NOT NULL,
			post_type varchar(20) NOT NULL DEFAULT 'post',
			target_id bigint(20) unsigned NOT NULL DEFAULT 0,
			target_type varchar(20) NOT NULL DEFAULT '',
			url text NOT NULL,
			original_url text NULL DEFAULT NULL,
			anchor text NOT NULL,
			anchor_word_count smallint(5) unsigned NOT NULL DEFAULT 0,
			location varchar(32) NOT NULL DEFAULT 'content',
			is_internal tinyint(1) NOT NULL DEFAULT 0,
			created_by_plugin tinyint(1) NOT NULL DEFAULT 0,
			keyword_id bigint(20) unsigned NOT NULL DEFAULT 0,
			url_rule_id bigint(20) unsigned NOT NULL DEFAULT 0,
			tracking_id bigint(20) unsigned NOT NULL DEFAULT 0,
			ai_relation_score decimal(4,3) unsigned NOT NULL DEFAULT 0.000,
			created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY post_lookup (post_id, post_type),
			KEY target_lookup (target_id, target_type),
			KEY keyword_id (keyword_id),
			KEY url_rule_id (url_rule_id),
			KEY tracking_id (tracking_id)
		) {$collate};";
	}

	/**
	 * Schema for the target-keywords table.
	 */
	private function target_keywords_sql( string $collate ): string {
		$table = TableNames::target_keywords();
		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			post_id bigint(20) unsigned NOT NULL,
			post_type varchar(20) NOT NULL DEFAULT 'post',
			source varchar(32) NOT NULL,
			phrase varchar(512) NOT NULL,
			stemmed varchar(512) NOT NULL DEFAULT '',
			normalized varchar(512) NOT NULL DEFAULT '',
			word_count smallint(5) unsigned NOT NULL DEFAULT 0,
			checked tinyint(1) NOT NULL DEFAULT 0,
			impressions int(10) unsigned NOT NULL DEFAULT 0,
			clicks int(10) unsigned NOT NULL DEFAULT 0,
			created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY post_source_phrase (post_id, post_type, source, phrase(191)),
			KEY post_lookup (post_id, post_type),
			KEY source (source)
		) {$collate};";
	}
}