<?php
namespace R2_Offload;

defined( 'ABSPATH' ) || exit;

final class DB {

	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 UNSIGNED NOT NULL,
				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)
			) {$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)
			) {$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};"
		);
	}

	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 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 ] );
	}
}
