CREATE TABLE `relay_regions` (
	`id` text PRIMARY KEY NOT NULL,
	`code` text NOT NULL,
	`display_name` text NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`public_origin` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_relay_regions_code` ON `relay_regions` (`code`);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_relay_regions_public_origin` ON `relay_regions` (`public_origin`);
--> statement-breakpoint
CREATE TABLE `relay_nodes` (
	`id` text PRIMARY KEY NOT NULL,
	`region_id` text NOT NULL,
	`status` text DEFAULT 'provisioning' NOT NULL,
	`internal_endpoint_ref` text NOT NULL,
	`capacity_tenants` integer DEFAULT 0 NOT NULL,
	`last_heartbeat_at` text,
	`heartbeat_generation` integer DEFAULT 0 NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`region_id`) REFERENCES `relay_regions`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `idx_relay_nodes_region_status` ON `relay_nodes` (`region_id`,`status`);
--> statement-breakpoint
CREATE TABLE `tenant_placements` (
	`tenant_id` text PRIMARY KEY NOT NULL,
	`home_region_id` text NOT NULL,
	`relay_node_id` text,
	`generation` integer DEFAULT 1 NOT NULL,
	`state` text DEFAULT 'provisioning' NOT NULL,
	`last_error_code` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`tenant_id`) REFERENCES `tenant_instances`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`home_region_id`) REFERENCES `relay_regions`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`relay_node_id`) REFERENCES `relay_nodes`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `idx_tenant_placements_region_state` ON `tenant_placements` (`home_region_id`,`state`);
--> statement-breakpoint
CREATE INDEX `idx_tenant_placements_node_state` ON `tenant_placements` (`relay_node_id`,`state`);
--> statement-breakpoint
CREATE TABLE `tenant_authorization_state` (
	`tenant_id` text PRIMARY KEY NOT NULL,
	`revision` integer DEFAULT 0 NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`tenant_id`) REFERENCES `tenant_instances`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `idx_tenant_authorization_revision` ON `tenant_authorization_state` (`revision`);
--> statement-breakpoint
CREATE TABLE `relay_signing_keys` (
	`kid` text PRIMARY KEY NOT NULL,
	`algorithm` text DEFAULT 'Ed25519' NOT NULL,
	`public_key_jwk` text NOT NULL,
	`private_key_ref` text NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`not_before` text NOT NULL,
	`not_after` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`retired_at` text
);
--> statement-breakpoint
CREATE TABLE `relay_node_credentials` (
	`id` text PRIMARY KEY NOT NULL,
	`node_id` text NOT NULL,
	`mtls_spiffe_id` text NOT NULL,
	`certificate_fingerprint_sha256` text NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`bootstrap_reason` text NOT NULL,
	`issued_by` text NOT NULL,
	`issued_at` text NOT NULL,
	`expires_at` text,
	`last_used_at` text,
	`revoked_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`node_id`) REFERENCES `relay_nodes`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_relay_node_credentials_mtls` ON `relay_node_credentials` (`mtls_spiffe_id`,`certificate_fingerprint_sha256`);
--> statement-breakpoint
CREATE INDEX `idx_relay_node_credentials_node_status` ON `relay_node_credentials` (`node_id`,`status`);
--> statement-breakpoint
CREATE TABLE `phone_handoff_tickets` (
	`id` text PRIMARY KEY NOT NULL,
	`ticket_hash` text NOT NULL,
	`account_id` text NOT NULL,
	`tenant_id` text NOT NULL,
	`expected_origin` text NOT NULL,
	`expires_at` text NOT NULL,
	`consumed_at` text,
	`consumed_by_node_id` text,
	`pending_phone_name` text,
	`pending_ed25519_public` text,
	`pending_x25519_public` text,
	`pending_identity_fingerprint` text,
	`completed_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`tenant_id`) REFERENCES `tenant_instances`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`consumed_by_node_id`) REFERENCES `relay_nodes`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_phone_handoff_ticket_hash` ON `phone_handoff_tickets` (`ticket_hash`);
--> statement-breakpoint
CREATE INDEX `idx_phone_handoff_account_expiry` ON `phone_handoff_tickets` (`account_id`,`expires_at`);
--> statement-breakpoint
CREATE TABLE `phone_route_handles` (
	`id` text PRIMARY KEY NOT NULL,
	`handle_hash` text NOT NULL,
	`tenant_id` text NOT NULL,
	`phone_id` text NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`last_used_at` text,
	`revoked_at` text,
	FOREIGN KEY (`tenant_id`) REFERENCES `tenant_instances`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_phone_route_handle_hash` ON `phone_route_handles` (`handle_hash`);
--> statement-breakpoint
CREATE INDEX `idx_phone_route_tenant_status` ON `phone_route_handles` (`tenant_id`,`status`);
--> statement-breakpoint
CREATE TABLE `relay_phone_principals` (
	`phone_id` text PRIMARY KEY NOT NULL,
	`tenant_id` text NOT NULL,
	`token_hash` text NOT NULL,
	`name` text NOT NULL,
	`ed25519_public` text NOT NULL,
	`x25519_public` text NOT NULL,
	`identity_fingerprint` text NOT NULL,
	`status` text DEFAULT 'active' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`last_used_at` text,
	`revoked_at` text,
	FOREIGN KEY (`tenant_id`) REFERENCES `tenant_instances`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_relay_phone_token_hash` ON `relay_phone_principals` (`token_hash`);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_relay_phone_tenant_identity` ON `relay_phone_principals` (`tenant_id`,`identity_fingerprint`);
--> statement-breakpoint
CREATE INDEX `idx_relay_phone_tenant_status` ON `relay_phone_principals` (`tenant_id`,`status`);
--> statement-breakpoint
CREATE TABLE `device_registration_replays` (
	`pairing_id` text PRIMARY KEY NOT NULL,
	`request_hash` text NOT NULL,
	`response_ciphertext` text NOT NULL,
	`response_nonce` text NOT NULL,
	`expires_at` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`pairing_id`) REFERENCES `pairing_requests`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `idx_device_registration_replay_expiry` ON `device_registration_replays` (`expires_at`);
--> statement-breakpoint
INSERT OR IGNORE INTO `relay_regions`
  (`id`, `code`, `display_name`, `status`, `public_origin`, `created_at`, `updated_at`)
VALUES
  ('region_default', 'default', 'Default region', 'active', 'https://connect.invalid', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
--> statement-breakpoint
INSERT OR IGNORE INTO `tenant_placements`
  (`tenant_id`, `home_region_id`, `generation`, `state`, `created_at`, `updated_at`)
SELECT `id`, 'region_default', 1, 'provisioning', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM `tenant_instances`;
--> statement-breakpoint
DROP TABLE IF EXISTS `provisioner_workers`;
--> statement-breakpoint
DROP TABLE IF EXISTS `provisioning_operations`;
--> statement-breakpoint
INSERT OR IGNORE INTO `tenant_authorization_state`
  (`tenant_id`, `revision`, `status`, `updated_at`)
SELECT `id`, `credential_revision`,
       CASE WHEN `tombstoned_at` IS NULL THEN 'active' ELSE 'suspended' END,
       CURRENT_TIMESTAMP
FROM `tenant_instances`;