<?php
/**
 * Database schema.
 *
 * @package CertaCart_EU_Product_Readiness
 */

namespace CertaCart\EU_Product_Readiness;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

final class Schema {
	public const VERSION = '1.1.0';

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

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

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

		$charset = $wpdb->get_charset_collate();
		$scans   = self::scans_table();
		$results = self::results_table();

		$sql_scans = "CREATE TABLE {$scans} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			status varchar(20) NOT NULL DEFAULT 'queued',
			started_at datetime NULL,
			completed_at datetime NULL,
			total_products bigint(20) unsigned NOT NULL DEFAULT 0,
			processed_products bigint(20) unsigned NOT NULL DEFAULT 0,
			last_product_id bigint(20) unsigned NOT NULL DEFAULT 0,
			overall_score decimal(5,2) NOT NULL DEFAULT 0.00,
			critical_count bigint(20) unsigned NOT NULL DEFAULT 0,
			warning_count bigint(20) unsigned NOT NULL DEFAULT 0,
			info_count bigint(20) unsigned NOT NULL DEFAULT 0,
			in_scope_products bigint(20) unsigned NOT NULL DEFAULT 0,
			scope_json longtext NULL,
			error_message text NULL,
			created_by bigint(20) unsigned NOT NULL DEFAULT 0,
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY status (status),
			KEY created_at (created_at)
		) {$charset};";

		$sql_results = "CREATE TABLE {$results} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			scan_id bigint(20) unsigned NOT NULL,
			product_id bigint(20) unsigned NOT NULL,
			score decimal(5,2) NOT NULL DEFAULT 0.00,
			in_scope tinyint(1) unsigned NOT NULL DEFAULT 1,
			critical_count smallint(5) unsigned NOT NULL DEFAULT 0,
			warning_count smallint(5) unsigned NOT NULL DEFAULT 0,
			info_count smallint(5) unsigned NOT NULL DEFAULT 0,
			findings_json longtext NOT NULL,
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY scan_product (scan_id,product_id),
			KEY scan_score (scan_id,score),
			KEY scan_scope (scan_id,in_scope),
			KEY product_id (product_id)
		) {$charset};";

		dbDelta( $sql_scans );
		dbDelta( $sql_results );
		update_option( 'ccpr_schema_version', self::VERSION, false );
	}
}
