<?php
/**
 * Definition of the fitzaro_requests table.
 *
 * @package Fitzaro
 */

namespace Fitzaro\Database;

defined( 'ABSPATH' ) || exit;

/**
 * One row per try-on request. Times are UTC. File paths are relative to the private storage root.
 *
 * Bump VERSION whenever create_table_sql() changes; Installer then runs dbDelta once.
 */
final class Schema {

	public const VERSION = '2';

	public const TABLE = 'fitzaro_requests';

	/**
	 * Full table name with the site's prefix.
	 *
	 * @param string $prefix $wpdb->prefix.
	 */
	public static function table_name( string $prefix ): string {
		return $prefix . self::TABLE;
	}

	/**
	 * CREATE TABLE statement in the exact format dbDelta() expects:
	 * one column per line, two spaces after PRIMARY KEY, no foreign keys.
	 *
	 * Index lengths stay under 767 bytes so they work on MySQL 5.6 / older MariaDB with utf8mb4.
	 *
	 * @param string $table           Full table name.
	 * @param string $charset_collate $wpdb->get_charset_collate().
	 */
	public static function create_table_sql( string $table, string $charset_collate ): string {
		return "CREATE TABLE {$table} (
  id char(36) NOT NULL,
  visitor_hash char(64) NOT NULL,
  ip_hash char(64) NOT NULL DEFAULT '',
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  product_id bigint(20) unsigned NOT NULL,
  variation_id bigint(20) unsigned NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'queued',
  attempts tinyint(3) unsigned NOT NULL DEFAULT 0,
  claim_token char(36) DEFAULT NULL,
  lease_until datetime DEFAULT NULL,
  upload_path varchar(255) DEFAULT NULL,
  result_path varchar(255) DEFAULT NULL,
  provider varchar(32) NOT NULL DEFAULT '',
  model varchar(64) NOT NULL DEFAULT '',
  prompt_version varchar(16) NOT NULL DEFAULT '',
  remote_id varchar(191) DEFAULT NULL,
  cost decimal(12,6) DEFAULT NULL,
  error varchar(500) DEFAULT NULL,
  params longtext,
  created_at datetime NOT NULL,
  started_at datetime DEFAULT NULL,
  finished_at datetime DEFAULT NULL,
  PRIMARY KEY  (id),
  KEY status_lease (status,lease_until),
  KEY visitor_created (visitor_hash,created_at),
  KEY ip_created (ip_hash,created_at),
  KEY user_id (user_id),
  KEY product_id (product_id),
  KEY created_at (created_at)
) {$charset_collate};";
	}
}