<?php
namespace R2_Offload;

defined( 'ABSPATH' ) || exit;

final class DB {

	public const SCHEMA_VERSION = 2;

	public static function items_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'r2_offload_items';
	}

	public static function files_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'r2_offload_files';
	}

	public static function logs_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'r2_offload_logs';
	}

	public static function install(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();
		$items   = self::items_table();
		$files   = self::files_table();
		$logs    = self::logs_table();

		dbDelta(
			"CREATE TABLE {$items} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				attachment_id BIGINT NOT NULL,
				relative_path VARCHAR(500) NOT NULL DEFAULT '',
				provider VARCHAR(20) NOT NULL DEFAULT 'gcs',
				bucket VARCHAR(191) NOT NULL DEFAULT '',
				object_prefix VARCHAR(191) NOT NULL DEFAULT '',
				status VARCHAR(20) NOT NULL DEFAULT 'pending',
				attempts INT UNSIGNED NOT NULL DEFAULT 0,
				last_error TEXT NULL,
				bytes_total BIGINT UNSIGNED NOT NULL DEFAULT 0,
				bytes_uploaded BIGINT UNSIGNED NOT NULL DEFAULT 0,
				checksum VARCHAR(128) NULL,
				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 attachment_id (attachment_id),
				KEY status (status),
				KEY relative_path (relative_path(191))
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$files} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				offload_item_id BIGINT UNSIGNED NOT NULL,
				relative_path VARCHAR(500) NOT NULL,
				mime_type VARCHAR(100) NOT NULL DEFAULT '',
				size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
				checksum VARCHAR(128) NULL,
				status VARCHAR(20) NOT NULL DEFAULT 'pending',
				created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
				updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				KEY offload_item_id (offload_item_id),
				KEY status (status),
				KEY relative_path (relative_path(191))
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$logs} (
				id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				level VARCHAR(10) NOT NULL DEFAULT 'info',
				context VARCHAR(64) NOT NULL DEFAULT '',
				message TEXT NOT NULL,
				meta LONGTEXT NULL,
				created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				KEY level (level),
				KEY created_at (created_at)
			) {$charset};"
		);

		update_option( 'r2_offload_db_version', self::SCHEMA_VERSION, false );
	}

	/** Ensure schema upgrades run on existing installs. */
	public static function maybe_upgrade(): void {
		$current = (int) get_option( 'r2_offload_db_version', 1 );
		if ( $current >= self::SCHEMA_VERSION ) {
			return;
		}

		global $wpdb;
		$items = self::items_table();
		$files = self::files_table();

		// Path-based items use negative IDs — column must be signed.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( "ALTER TABLE {$items} MODIFY attachment_id BIGINT NOT NULL" );

		$cols = $wpdb->get_col( "DESCRIBE {$items}", 0 );
		if ( ! in_array( 'relative_path', (array) $cols, true ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$items} ADD COLUMN relative_path VARCHAR(500) NOT NULL DEFAULT '' AFTER attachment_id" );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$items} ADD KEY relative_path (relative_path(191))" );
		}

		$file_cols = $wpdb->get_col( "DESCRIBE {$files}", 0 );
		if ( is_array( $file_cols ) && ! empty( $file_cols ) ) {
			$indexes = $wpdb->get_results( "SHOW INDEX FROM {$files} WHERE Key_name = 'relative_path'" );
			if ( empty( $indexes ) ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "ALTER TABLE {$files} ADD KEY relative_path (relative_path(191))" );
			}
		}

		self::install();
	}

	public static function upsert_item( int $attachment_id, array $data ): int {
		global $wpdb;
		$table = self::items_table();
		$row   = $wpdb->get_row( $wpdb->prepare( "SELECT id FROM {$table} WHERE attachment_id = %d", $attachment_id ) );
		if ( $row ) {
			$wpdb->update( $table, $data, [ 'id' => $row->id ] );
			return (int) $row->id;
		}
		$data['attachment_id'] = $attachment_id;
		$wpdb->insert( $table, $data );
		return (int) $wpdb->insert_id;
	}

	public static function get_item( int $attachment_id ): ?object {
		global $wpdb;
		$table = self::items_table();
		$row   = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE attachment_id = %d", $attachment_id ) );
		return $row ?: null;
	}

	public static function get_item_by_path( string $relative_path ): ?object {
		global $wpdb;
		$table = self::items_table();
		$relative_path = ltrim( str_replace( '\\', '/', $relative_path ), '/' );
		$row           = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE relative_path = %s LIMIT 1", $relative_path )
		);
		return $row ?: null;
	}

	public static function set_status( int $attachment_id, string $status, ?string $error = null ): void {
		global $wpdb;
		$data = [
			'status'     => $status,
			'updated_at' => current_time( 'mysql' ),
		];
		if ( $error !== null ) {
			$data['last_error'] = $error;
		}
		$wpdb->update( self::items_table(), $data, [ 'attachment_id' => $attachment_id ] );
	}

	/**
	 * Stable negative synthetic ID for a non-attachment uploads-relative path.
	 * Avoids colliding with real WP attachment IDs (always positive).
	 */
	public static function path_to_id( string $relative_path ): int {
		$relative_path = ltrim( str_replace( '\\', '/', $relative_path ), '/' );
		$existing      = self::get_item_by_path( $relative_path );
		if ( $existing ) {
			return (int) $existing->attachment_id;
		}

		$base = (int) sprintf( '%u', crc32( $relative_path ) );
		$base = max( 1, $base % 2000000000 );
		$id   = -1 * $base;

		for ( $i = 0; $i < 500; $i++ ) {
			$try  = ( $i === 0 ) ? $id : -1 * ( ( $base + $i ) % 2000000000 + 1 );
			$clash = self::get_item( $try );
			if ( ! $clash ) {
				return $try;
			}
			if ( (string) ( $clash->relative_path ?? '' ) === $relative_path ) {
				return $try;
			}
		}

		// Extremely unlikely fallback.
		return -1 * ( ( $base ^ time() ) % 2000000000 + 1 );
	}

	public static function replace_files( int $offload_item_id, array $files ): void {
		global $wpdb;
		$table = self::files_table();
		$wpdb->delete( $table, [ 'offload_item_id' => $offload_item_id ] );
		$now = current_time( 'mysql' );
		foreach ( $files as $file ) {
			$wpdb->insert(
				$table,
				[
					'offload_item_id' => $offload_item_id,
					'relative_path'   => ltrim( str_replace( '\\', '/', (string) ( $file['relative_path'] ?? '' ) ), '/' ),
					'mime_type'       => (string) ( $file['mime_type'] ?? '' ),
					'size_bytes'      => (int) ( $file['size_bytes'] ?? 0 ),
					'checksum'        => $file['checksum'] ?? null,
					'status'          => (string) ( $file['status'] ?? 'offloaded' ),
					'created_at'      => $now,
					'updated_at'      => $now,
				]
			);
		}
	}

	/**
	 * Whether a uploads-relative path was recorded as offloaded (attachment sidecar or extra path).
	 */
	public static function is_relative_offloaded( string $relative_path ): bool {
		global $wpdb;
		$relative_path = ltrim( str_replace( '\\', '/', $relative_path ), '/' );
		if ( $relative_path === '' ) {
			return false;
		}

		$items = self::items_table();
		$files = self::files_table();

		$path_item = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$items} WHERE relative_path = %s AND status = 'offloaded' LIMIT 1",
				$relative_path
			)
		);
		if ( $path_item ) {
			return true;
		}

		$file = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT f.id FROM {$files} f
				 INNER JOIN {$items} i ON i.id = f.offload_item_id
				 WHERE f.relative_path = %s AND i.status = 'offloaded'
				 LIMIT 1",
				$relative_path
			)
		);
		return (bool) $file;
	}
}
