<?php
/**
 * Custom table names and schema installation.
 *
 * @package AiRankSuite
 */

declare(strict_types=1);

namespace AiRankSuite\Support;

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

final class Database {

	// 10: pagination indexes — redirects(status,created_at)+(hits) and
	//     found_links(status)+(internal) — for the server-paged enterprise grids.
	// 11: backlinks(first_seen) for the growth chart, and composite
	//     audit(run_id,severity)+(run_id,check_id) for the filtered report —
	//     both were filtering on unindexed columns at review scale.
	// 12: link_suggestions(status,score)+(status,target_post_id) — the paged
	//     suggestion list and the "pages these links would strengthen" grouping
	//     added in 0.98.30 both filter on status and then sort or group, which
	//     no existing index covered.
	// 17: airanksuite_urls — one row per indexable URL per language (URL-level analysis).
	public const VERSION = '28';

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

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

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

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

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

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

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

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

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

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

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

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

	/** One row per AI call: who asked, what it cost, whether it worked. */
	/**
	 * In-flight AI spend.
	 *
	 * The budget gate reads the ledger, which only holds calls that have
	 * already finished. Two workers starting at the same moment therefore both
	 * saw the same spend and both passed. A row is written here *before* the
	 * provider is called and settled afterwards, so a concurrent check counts
	 * money that is committed but not yet spent.
	 */
	public static function ai_reservations(): string {
		global $wpdb;
		return $wpdb->prefix . 'airanksuite_ai_reservations';
	}

	/** Every indexable URL of the site, per language, with its own scan and scores. */
	public static function urls(): string {
		global $wpdb;
		return $wpdb->prefix . 'airanksuite_urls';
	}

	/**
	 * The sitemap files a discovery run still has to read, and the ones it
	 * has read. A queue in a table rather than in the job option: a sitemap
	 * index with 300 children is 300 rows here, not a 300-element array
	 * rewritten on every tick, and the (run_id, url_hash) key is what makes
	 * a cycle or a duplicate reference harmless — it can only be queued once.
	 */
	public static function url_sitemaps(): string {
		global $wpdb;
		return $wpdb->prefix . 'airanksuite_url_sitemaps';
	}

	/**
	 * Staging for a rescan: the scores a running rescan produces, kept apart
	 * from the ones the screens read until the run completes and publishes
	 * them in one statement. An abandoned rescan leaves nothing but rows here.
	 */
	public static function url_scans(): string {
		global $wpdb;
		return $wpdb->prefix . 'airanksuite_url_scans';
	}

	/**
	 * The journal that makes "Undo this import" true: one row per value an
	 * import overwrote or created, with what was there before — or the fact
	 * that there was nothing, which is the case a naive undo gets wrong by
	 * leaving an empty field where the customer had never had one.
	 */
	public static function import_undo(): string {
		global $wpdb;
		return $wpdb->prefix . 'airanksuite_import_undo';
	}

	/** Proof of consent: one row per decision a visitor makes. */
	public static function consent_log(): string {
		global $wpdb;
		return $wpdb->prefix . 'airanksuite_consent_log';
	}

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

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

	/**
	 * Create/upgrade tables. Idempotent; safe to call on every activation.
	 */
	public static function install(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset = $wpdb->get_charset_collate();

		// AI crawler traffic, one row per bot per UTC day. Counters are bumped
		// with atomic `hits = hits + 1` updates — a bot hit must never
		// read-modify-write a shared blob, because two bots arriving in the
		// same second would each write the other's increment away.
		$crawler_hits = self::crawler_hits();
		dbDelta(
			"CREATE TABLE {$crawler_hits} (
			bot VARCHAR(64) NOT NULL,
			day CHAR(10) NOT NULL,
			hits BIGINT UNSIGNED NOT NULL DEFAULT 0,
			blocked BIGINT UNSIGNED NOT NULL DEFAULT 0,
			last BIGINT UNSIGNED NOT NULL DEFAULT 0,
			last_path VARCHAR(191) NOT NULL DEFAULT '',
			PRIMARY KEY  (bot, day),
			KEY day (day)
			) {$charset}"
		);

		$redirects = self::redirects();
		$not_found = self::not_found();
		$links     = self::links();

		$graph_runs  = self::graph_runs();
		$graph_nodes = self::graph_nodes();
		$graph_edges = self::graph_edges();

		// The link graph, scaled: one row per run, nodes and edges in their
		// own indexed tables rather than a serialized option. A build writes
		// under its own run_id while the previous complete run stays readable;
		// publishing is flipping one pointer option to the new run_id.
		dbDelta(
			"CREATE TABLE {$graph_runs} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			status VARCHAR(20) NOT NULL DEFAULT 'building',
			phase VARCHAR(20) NOT NULL DEFAULT 'collect',
			cursor_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			total INT UNSIGNED NOT NULL DEFAULT 0,
			processed INT UNSIGNED NOT NULL DEFAULT 0,
			pages INT UNSIGNED NOT NULL DEFAULT 0,
			edges_n INT UNSIGNED NOT NULL DEFAULT 0,
			cancelled TINYINT(1) NOT NULL DEFAULT 0,
			active_slot TINYINT UNSIGNED NULL DEFAULT NULL,
			lock_token VARCHAR(64) NOT NULL DEFAULT '',
			lock_expires BIGINT UNSIGNED NOT NULL DEFAULT 0,
			updated_at INT UNSIGNED NOT NULL DEFAULT 0,
			error TEXT NULL,
			payload LONGTEXT NULL,
			started_at INT UNSIGNED NOT NULL DEFAULT 0,
			finished_at INT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY status (status),
			KEY lock_expires (lock_expires),
			UNIQUE KEY active_slot (active_slot)
			) {$charset}"
		);
		dbDelta(
			"CREATE TABLE {$graph_nodes} (
			run_id BIGINT UNSIGNED NOT NULL,
			post_id BIGINT UNSIGNED NOT NULL,
			title TEXT NULL,
			url TEXT NULL,
			path VARCHAR(191) NOT NULL DEFAULT '',
			post_type VARCHAR(32) NOT NULL DEFAULT '',
			in_n INT UNSIGNED NOT NULL DEFAULT 0,
			out_n INT UNSIGNED NOT NULL DEFAULT 0,
			external_n INT UNSIGNED NOT NULL DEFAULT 0,
			nofollow_n INT UNSIGNED NOT NULL DEFAULT 0,
			words INT UNSIGNED NOT NULL DEFAULT 0,
			depth INT NOT NULL DEFAULT -1,
			authority DOUBLE NOT NULL DEFAULT 0,
			auth_next DOUBLE NOT NULL DEFAULT 0,
			PRIMARY KEY  (run_id, post_id),
			KEY run_path (run_id, path),
			KEY run_depth (run_id, depth),
			KEY run_in (run_id, in_n),
			KEY run_auth (run_id, authority)
			) {$charset}"
		);
		/*
		 * graph_edges.to_id is SIGNED, and that is the whole of the link graph bug.
		 *
		 * It holds a post id when the link resolves to one, 0 while it is still
		 * unresolved, and -1 or -2 for "resolves, but not to a post" and
		 * "broken". The column was BIGINT UNSIGNED, so MySQL turned both
		 * negatives into 0, which is the value that means unresolved. The
		 * resolve phase then selected the same 300 paths on every pass, wrote 0
		 * over 0, and never emptied. The graph could not finish on any site
		 * with a single unresolvable internal link, which is every real site:
		 * twenty seconds of work per slice, forever, with the progress bar
		 * sitting still. Every query that reads this tests `to_id > 0`, so the
		 * negatives stay out of edge counts exactly as intended.
		 */
		dbDelta(
			"CREATE TABLE {$graph_edges} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			run_id BIGINT UNSIGNED NOT NULL,
			from_id BIGINT UNSIGNED NOT NULL,
			to_id BIGINT NOT NULL DEFAULT 0,
			to_path VARCHAR(191) NOT NULL DEFAULT '',
			nofollow TINYINT(1) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY run_from (run_id, from_id),
			KEY run_to (run_id, to_id),
			KEY run_unresolved (run_id, to_id, to_path)
			) {$charset}"
		);

		dbDelta(
			"CREATE TABLE {$redirects} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				source varchar(191) NOT NULL,
				target text NOT NULL,
				type smallint(4) NOT NULL DEFAULT 301,
				status varchar(20) NOT NULL DEFAULT 'active',
				hits bigint(20) unsigned NOT NULL DEFAULT 0,
				last_hit datetime DEFAULT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY source (source),
				KEY status_created (status, created_at),
				KEY hits (hits)
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$not_found} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				path varchar(191) NOT NULL,
				hits bigint(20) unsigned NOT NULL DEFAULT 1,
				referrer varchar(191) DEFAULT NULL,
				last_hit datetime NOT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY path (path)
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$links} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				source_post_id bigint(20) unsigned NOT NULL,
				target_post_id bigint(20) unsigned NOT NULL,
				anchor varchar(191) NOT NULL,
				context text NULL,
				score decimal(5,4) NOT NULL DEFAULT 0,
				status varchar(20) NOT NULL DEFAULT 'suggested',
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY source_post_id (source_post_id),
				KEY target_post_id (target_post_id),
				KEY status_score (status, score),
				KEY status_target (status, target_post_id)
			) {$charset};"
		);

		$keywords = self::keywords();
		$ranks    = self::ranks();

		dbDelta(
			"CREATE TABLE {$keywords} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				keyword varchar(191) NOT NULL,
				source varchar(20) NOT NULL DEFAULT 'manual',
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY keyword (keyword)
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$ranks} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				keyword_id bigint(20) unsigned NOT NULL,
				snapshot_date date NOT NULL,
				position decimal(6,2) NOT NULL DEFAULT 0,
				clicks int(11) NOT NULL DEFAULT 0,
				impressions int(11) NOT NULL DEFAULT 0,
				ctr decimal(6,3) NOT NULL DEFAULT 0,
				url varchar(191) DEFAULT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY keyword_date (keyword_id, snapshot_date),
				KEY snapshot_date (snapshot_date)
			) {$charset};"
		);

		$mentions = self::mentions();

		dbDelta(
			"CREATE TABLE {$mentions} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				prompt varchar(191) NOT NULL,
				run_id varchar(32) NOT NULL,
				mentioned tinyint(1) NOT NULL DEFAULT 0,
				position smallint(4) NOT NULL DEFAULT 0,
				sentiment varchar(12) NOT NULL DEFAULT 'neutral',
				competitors text NULL,
				snippet text NULL,
				cited tinyint(1) NOT NULL DEFAULT 0,
				urls text NULL,
				provider varchar(40) NOT NULL DEFAULT '',
				model varchar(120) NOT NULL DEFAULT '',
				cost_micro bigint(20) unsigned NOT NULL DEFAULT 0,
				checked_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY prompt (prompt),
				KEY run_id (run_id),
				KEY checked_at (checked_at)
			) {$charset};"
		);

		$found = self::found_links();

		dbDelta(
			"CREATE TABLE {$found} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				post_id bigint(20) unsigned NOT NULL,
				url text NOT NULL,
				url_hash char(32) NOT NULL,
				anchor varchar(191) NOT NULL DEFAULT '',
				internal tinyint(1) NOT NULL DEFAULT 0,
				status smallint(6) NOT NULL DEFAULT -1,
				ok tinyint(1) NOT NULL DEFAULT 0,
				redirect_to text NULL,
				checked_at datetime DEFAULT NULL,
				run_id varchar(12) NOT NULL DEFAULT '',
				PRIMARY KEY  (id),
				KEY post_id (post_id),
				KEY url_hash (url_hash),
				KEY ok (ok),
				KEY status (status),
				KEY internal (internal),
				KEY run_id (run_id)
			) {$charset};"
		);

		/*
		 * Proof of consent.
		 *
		 * GDPR Article 7(1) puts the burden on the controller to demonstrate
		 * that consent was given — a cookie in the visitor's browser proves
		 * nothing to a regulator, because the site cannot produce it. This is
		 * the record: what was chosen, when, under which policy version.
		 *
		 * Nothing here identifies a person. The address and user agent are
		 * stored as salted hashes so two decisions from the same visitor can be
		 * recognised as one thread without the site holding either value — the
		 * salt is the site's own AUTH_SALT, so the hashes are useless elsewhere.
		 */
		$urls = self::urls();
		// url_hash is the identity (URLs outgrow every index-safe varchar);
		// post_id links a translated URL back to the WordPress object that
		// renders it — several rows share one post on a URL-translating
		// plugin such as TranslatePress or Weglot.
		dbDelta(
			"CREATE TABLE {$urls} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				url_hash char(32) NOT NULL,
				url text NOT NULL,
				post_id bigint(20) unsigned NOT NULL DEFAULT 0,
				post_type varchar(20) NOT NULL DEFAULT '',
				lang varchar(12) NOT NULL DEFAULT '',
				source varchar(12) NOT NULL DEFAULT 'post',
				status smallint(6) NOT NULL DEFAULT 0,
				indexable tinyint(1) NOT NULL DEFAULT 1,
				canonical text NULL,
				canonical_ok tinyint(1) NOT NULL DEFAULT 1,
				hreflang_ok tinyint(1) NOT NULL DEFAULT 1,
				title varchar(191) NOT NULL DEFAULT '',
				title_len smallint(6) NOT NULL DEFAULT 0,
				desc_len smallint(6) NOT NULL DEFAULT 0,
				h1_count smallint(6) NOT NULL DEFAULT 0,
				words int(10) unsigned NOT NULL DEFAULT 0,
				seo_score tinyint(3) unsigned NULL,
				geo_score tinyint(3) unsigned NULL,
				issues text NULL,
				discovered_at datetime NOT NULL,
				scanned_at datetime NULL,
				run_id varchar(12) NOT NULL DEFAULT '',
				seen_run varchar(12) NOT NULL DEFAULT '',
				seen_at datetime NULL,
				published tinyint(1) NOT NULL DEFAULT 1,
				PRIMARY KEY  (id),
				UNIQUE KEY url_hash (url_hash),
				KEY post_id (post_id),
				KEY lang (lang),
				KEY scanned_at (scanned_at),
				KEY lang_seo (lang, seo_score),
				KEY indexable (indexable),
				KEY published (published),
				KEY seen_run (seen_run)
			) {$charset};"
		);

		$sitemaps = self::url_sitemaps();
		dbDelta(
			"CREATE TABLE {$sitemaps} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_id varchar(12) NOT NULL,
				url_hash char(32) NOT NULL,
				url text NOT NULL,
				state varchar(10) NOT NULL DEFAULT 'queued',
				offset_urls int(10) unsigned NOT NULL DEFAULT 0,
				body_hash char(32) NOT NULL DEFAULT '',
				resets tinyint(3) unsigned NOT NULL DEFAULT 0,
				found int(10) unsigned NOT NULL DEFAULT 0,
				children int(10) unsigned NOT NULL DEFAULT 0,
				error varchar(120) NOT NULL DEFAULT '',
				queued_at datetime NOT NULL,
				done_at datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY run_hash (run_id, url_hash),
				KEY run_state (run_id, state)
			) {$charset};"
		);

		$undo = self::import_undo();
		dbDelta(
			"CREATE TABLE {$undo} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				import_id varchar(16) NOT NULL,
				kind varchar(12) NOT NULL,
				object_id bigint(20) unsigned NOT NULL DEFAULT 0,
				name varchar(191) NOT NULL,
				prev_value longtext NULL,
				existed tinyint(1) NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY one_per_thing (import_id, kind, object_id, name),
				KEY import (import_id, id)
			) {$charset};"
		);

		$scans = self::url_scans();
		dbDelta(
			"CREATE TABLE {$scans} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_id varchar(12) NOT NULL,
				url_id bigint(20) unsigned NOT NULL,
				status smallint(6) NOT NULL DEFAULT 0,
				indexable tinyint(1) NOT NULL DEFAULT 1,
				canonical text NULL,
				canonical_ok tinyint(1) NOT NULL DEFAULT 1,
				hreflang_ok tinyint(1) NOT NULL DEFAULT 1,
				title varchar(191) NOT NULL DEFAULT '',
				title_len smallint(6) NOT NULL DEFAULT 0,
				desc_len smallint(6) NOT NULL DEFAULT 0,
				h1_count smallint(6) NOT NULL DEFAULT 0,
				words int(10) unsigned NOT NULL DEFAULT 0,
				seo_score tinyint(3) unsigned NULL,
				geo_score tinyint(3) unsigned NULL,
				issues text NULL,
				scanned_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY run_url (run_id, url_id)
			) {$charset};"
		);

		$consent = self::consent_log();

		dbDelta(
			"CREATE TABLE {$consent} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				created_at datetime NOT NULL,
				decision varchar(12) NOT NULL DEFAULT 'custom',
				analytics tinyint(1) NOT NULL DEFAULT 0,
				marketing tinyint(1) NOT NULL DEFAULT 0,
				region varchar(4) NOT NULL DEFAULT 'eu',
				policy_version int(10) unsigned NOT NULL DEFAULT 1,
				visitor_hash char(64) NOT NULL DEFAULT '',
				agent_hash char(64) NOT NULL DEFAULT '',
				url varchar(191) NOT NULL DEFAULT '',
				PRIMARY KEY  (id),
				KEY created_at (created_at),
				KEY decision (decision),
				KEY visitor_hash (visitor_hash),
				KEY policy_version (policy_version)
			) {$charset};"
		);

		$audit = self::audit();

		dbDelta(
			"CREATE TABLE {$audit} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_id varchar(20) NOT NULL,
				object_type varchar(12) NOT NULL DEFAULT 'post',
				post_id bigint(20) unsigned NOT NULL DEFAULT 0,
				check_id varchar(40) NOT NULL,
				severity varchar(10) NOT NULL DEFAULT 'warning',
				label varchar(191) NOT NULL,
				detail text NULL,
				fingerprint char(32) NOT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY run_id (run_id),
				KEY post_id (post_id),
				KEY check_id (check_id),
				KEY severity (severity),
				KEY fingerprint (fingerprint),
				KEY run_severity (run_id, severity),
				KEY run_check (run_id, check_id)
			) {$charset};"
		);

		$backlinks = self::backlinks();

		// source_hash is a md5 of the full source URL: source_url is a TEXT
		// because real referrer URLs run past 191 characters routinely, and a
		// TEXT column cannot carry a UNIQUE index. The hash can.
		dbDelta(
			"CREATE TABLE {$backlinks} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				source_hash char(32) NOT NULL,
				source_url text NOT NULL,
				source_domain varchar(191) NOT NULL,
				target_url text NOT NULL,
				target_post_id bigint(20) unsigned NOT NULL DEFAULT 0,
				anchor varchar(191) NOT NULL DEFAULT '',
				rel varchar(60) NOT NULL DEFAULT '',
				discovered_via varchar(20) NOT NULL DEFAULT 'referrer',
				status varchar(12) NOT NULL DEFAULT 'unverified',
				http_status smallint(5) unsigned NOT NULL DEFAULT 0,
				hits int(10) unsigned NOT NULL DEFAULT 0,
				first_seen datetime NOT NULL,
				last_seen datetime NOT NULL,
				checked_at datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY source_target (source_hash, target_post_id),
				KEY source_domain (source_domain),
				KEY status (status),
				KEY target_post_id (target_post_id),
				KEY last_seen (last_seen),
				KEY first_seen (first_seen)
			) {$charset};"
		);

		// v16: consent_log — the Article 7 record of who chose what, when.
		// v14: mentions carry the provider, model and cost of the simulated
		// answer, and the real URLs found in it — kept apart from the model's
		// own "would cite" guess, which is an estimate and is labelled as one.
		// v15: usage rows carry the run they belong to, and a reservations
		//      table makes the budget gate safe under concurrency.
		// v13: AI usage ledger. Every provider call writes one row, so cost is
		// measured rather than guessed, budgets can be enforced against real
		// numbers, and "what did the AI do last month" has an answer. Indexed
		// by time (budget windows) and by job (per-module spend).
		$reservations = self::ai_reservations();
		$usage = self::ai_usage();
		// One call, one statement. Passing two strings would make the second the
		// $execute flag, and dbDelta would silently skip the second table —
		// which is precisely what happened when the reservations table was
		// added and the usage table stopped gaining its new columns.
		dbDelta(
			"CREATE TABLE {$reservations} (
				reservation_id char(36) NOT NULL,
				run_id char(36) NOT NULL DEFAULT '',
				user_id bigint(20) unsigned NOT NULL DEFAULT 0,
				job varchar(40) NOT NULL DEFAULT '',
				estimated_micro bigint(20) unsigned NOT NULL DEFAULT 0,
				state varchar(20) NOT NULL DEFAULT 'reserved',
				created_at datetime NOT NULL,
				expires_at datetime NOT NULL,
				PRIMARY KEY  (reservation_id),
				KEY state_expires (state, expires_at),
				KEY run_id (run_id)
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$usage} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				created_at datetime NOT NULL,
				provider varchar(40) NOT NULL DEFAULT '',
				model varchar(120) NOT NULL DEFAULT '',
				job varchar(40) NOT NULL DEFAULT '',
				prompt_id varchar(60) NOT NULL DEFAULT '',
				post_id bigint(20) unsigned NOT NULL DEFAULT 0,
				user_id bigint(20) unsigned NOT NULL DEFAULT 0,
				prompt_tokens int(10) unsigned NOT NULL DEFAULT 0,
				completion_tokens int(10) unsigned NOT NULL DEFAULT 0,
				cost_micro bigint(20) unsigned NOT NULL DEFAULT 0,
				latency_ms int(10) unsigned NOT NULL DEFAULT 0,
				attempts tinyint(3) unsigned NOT NULL DEFAULT 1,
				ok tinyint(1) NOT NULL DEFAULT 1,
				error_code varchar(40) NOT NULL DEFAULT '',
				run_id char(36) NOT NULL DEFAULT '',
				attempt_type varchar(20) NOT NULL DEFAULT 'initial',
				PRIMARY KEY  (id),
				KEY created_at (created_at),
				KEY job_created (job, created_at),
				KEY post_id (post_id),
				KEY run_id (run_id)
			) {$charset};"
		);

		update_option( 'airanksuite_db_version', self::VERSION, false );
	}
}