CREATE TABLE IF NOT EXISTS "organizations" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"name" varchar(255) NOT NULL,
	"slug" varchar(255) NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "organizations_slug_unique" UNIQUE ("slug")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "users" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"organization_id" uuid NOT NULL,
	"email" varchar(255) NOT NULL,
	"password_hash" varchar(255) NOT NULL,
	"display_name" varchar(255) NOT NULL,
	"role" varchar(50) DEFAULT 'read_only' NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"last_login_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "users_email_org_unique" UNIQUE ("email","organization_id")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "enrollment_tokens" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"organization_id" uuid NOT NULL,
	"token_hash" varchar(255) NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"max_uses" integer,
	"use_count" integer DEFAULT 0 NOT NULL,
	"is_revoked" boolean DEFAULT false NOT NULL,
	"label" varchar(255),
	"created_by_user_id" uuid NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "enrollment_tokens_token_hash_unique" UNIQUE ("token_hash")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "device_groups" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"organization_id" uuid NOT NULL,
	"name" varchar(255) NOT NULL,
	"description" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "device_groups_name_org_unique" UNIQUE ("name","organization_id")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "devices" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"organization_id" uuid NOT NULL,
	"device_group_id" uuid,
	"name" varchar(255) NOT NULL,
	"device_type" varchar(50) NOT NULL,
	"platform" varchar(50) NOT NULL,
	"os_name" varchar(100) NOT NULL,
	"os_version" varchar(100) NOT NULL,
	"architecture" varchar(50) NOT NULL,
	"kernel_version" varchar(100),
	"cpu_model" varchar(255),
	"cpu_cores" integer,
	"total_memory_bytes" bigint,
	"total_disk_bytes" bigint,
	"ip_address" varchar(45),
	"agent_version" varchar(50) NOT NULL,
	"status" varchar(20) DEFAULT 'offline' NOT NULL,
	"last_heartbeat_at" timestamp with time zone,
	"enrolled_at" timestamp with time zone DEFAULT now() NOT NULL,
	"is_disabled" boolean DEFAULT false NOT NULL,
	"is_archived" boolean DEFAULT false NOT NULL,
	"tags" jsonb DEFAULT '[]' NOT NULL,
	"metadata" jsonb DEFAULT '{}' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "device_credentials" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"device_id" uuid NOT NULL,
	"credential_type" varchar(50) NOT NULL,
	"credential_hash" varchar(255) NOT NULL,
	"expires_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "inventory_snapshots" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"device_id" uuid NOT NULL,
	"reported_at" timestamp with time zone NOT NULL,
	"packages" jsonb DEFAULT '[]' NOT NULL,
	"services" jsonb DEFAULT '[]' NOT NULL,
	"network_interfaces" jsonb DEFAULT '[]' NOT NULL,
	"platform_data" jsonb DEFAULT '{}' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "metric_snapshots" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"device_id" uuid NOT NULL,
	"reported_at" timestamp with time zone NOT NULL,
	"cpu_usage_percent" integer,
	"memory_usage_percent" integer,
	"memory_used_bytes" bigint,
	"memory_total_bytes" bigint,
	"disk_usage_percent" integer,
	"disk_used_bytes" bigint,
	"disk_total_bytes" bigint,
	"load_average_1m" integer,
	"load_average_5m" integer,
	"load_average_15m" integer,
	"uptime_seconds" bigint,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "policies" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"organization_id" uuid NOT NULL,
	"name" varchar(255) NOT NULL,
	"description" text,
	"rules" jsonb DEFAULT '[]' NOT NULL,
	"is_enabled" boolean DEFAULT true NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "compliance_results" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"device_id" uuid NOT NULL,
	"policy_id" uuid NOT NULL,
	"status" varchar(50) NOT NULL,
	"details" jsonb DEFAULT '{}' NOT NULL,
	"evaluated_at" timestamp with time zone NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "jobs" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"organization_id" uuid NOT NULL,
	"created_by_user_id" uuid NOT NULL,
	"job_type" varchar(100) NOT NULL,
	"status" varchar(50) DEFAULT 'queued' NOT NULL,
	"parameters" jsonb DEFAULT '{}' NOT NULL,
	"dispatched_at" timestamp with time zone,
	"started_at" timestamp with time zone,
	"completed_at" timestamp with time zone,
	"expires_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "job_targets" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"job_id" uuid NOT NULL,
	"device_id" uuid NOT NULL,
	"status" varchar(50) DEFAULT 'queued' NOT NULL,
	"result" jsonb,
	"started_at" timestamp with time zone,
	"completed_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "alerts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"organization_id" uuid NOT NULL,
	"device_id" uuid,
	"alert_type" varchar(100) NOT NULL,
	"severity" varchar(20) NOT NULL,
	"title" varchar(255) NOT NULL,
	"message" text NOT NULL,
	"is_resolved" boolean DEFAULT false NOT NULL,
	"resolved_at" timestamp with time zone,
	"resolved_by_user_id" uuid,
	"metadata" jsonb DEFAULT '{}' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "audit_events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"organization_id" uuid NOT NULL,
	"user_id" uuid,
	"action" varchar(255) NOT NULL,
	"target_type" varchar(100) NOT NULL,
	"target_id" uuid,
	"device_ids" jsonb DEFAULT '[]' NOT NULL,
	"result" varchar(50) NOT NULL,
	"ip_address" varchar(45),
	"metadata" jsonb DEFAULT '{}' NOT NULL,
	"correlation_id" varchar(255) NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "api_tokens" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	"organization_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"name" varchar(255) NOT NULL,
	"token_hash" varchar(255) NOT NULL,
	"expires_at" timestamp with time zone,
	"last_used_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "api_tokens_token_hash_unique" UNIQUE ("token_hash")
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "idx_users_email_org" ON "users" ("email","organization_id");
CREATE INDEX IF NOT EXISTS "idx_users_organization_id" ON "users" ("organization_id");
CREATE INDEX IF NOT EXISTS "idx_enrollment_tokens_org" ON "enrollment_tokens" ("organization_id");
CREATE INDEX IF NOT EXISTS "idx_enrollment_tokens_hash" ON "enrollment_tokens" ("token_hash");
CREATE INDEX IF NOT EXISTS "idx_devices_org" ON "devices" ("organization_id");
CREATE INDEX IF NOT EXISTS "idx_devices_status" ON "devices" ("status");
CREATE INDEX IF NOT EXISTS "idx_devices_group" ON "devices" ("device_group_id");
CREATE INDEX IF NOT EXISTS "idx_devices_type" ON "devices" ("device_type");
CREATE INDEX IF NOT EXISTS "idx_devices_last_heartbeat" ON "devices" ("last_heartbeat_at");
CREATE INDEX IF NOT EXISTS "idx_device_credentials_device" ON "device_credentials" ("device_id");
CREATE INDEX IF NOT EXISTS "idx_inventory_device" ON "inventory_snapshots" ("device_id");
CREATE INDEX IF NOT EXISTS "idx_inventory_reported_at" ON "inventory_snapshots" ("reported_at");
CREATE INDEX IF NOT EXISTS "idx_metrics_device" ON "metric_snapshots" ("device_id");
CREATE INDEX IF NOT EXISTS "idx_metrics_reported_at" ON "metric_snapshots" ("reported_at");
CREATE INDEX IF NOT EXISTS "idx_policies_org" ON "policies" ("organization_id");
CREATE INDEX IF NOT EXISTS "idx_compliance_device" ON "compliance_results" ("device_id");
CREATE INDEX IF NOT EXISTS "idx_compliance_policy" ON "compliance_results" ("policy_id");
CREATE INDEX IF NOT EXISTS "idx_jobs_org" ON "jobs" ("organization_id");
CREATE INDEX IF NOT EXISTS "idx_jobs_status" ON "jobs" ("status");
CREATE INDEX IF NOT EXISTS "idx_jobs_type" ON "jobs" ("job_type");
CREATE INDEX IF NOT EXISTS "idx_job_targets_job" ON "job_targets" ("job_id");
CREATE INDEX IF NOT EXISTS "idx_job_targets_device" ON "job_targets" ("device_id");
CREATE INDEX IF NOT EXISTS "idx_job_targets_status" ON "job_targets" ("status");
CREATE INDEX IF NOT EXISTS "idx_alerts_org" ON "alerts" ("organization_id");
CREATE INDEX IF NOT EXISTS "idx_alerts_device" ON "alerts" ("device_id");
CREATE INDEX IF NOT EXISTS "idx_alerts_resolved" ON "alerts" ("is_resolved");
CREATE INDEX IF NOT EXISTS "idx_audit_org" ON "audit_events" ("organization_id");
CREATE INDEX IF NOT EXISTS "idx_audit_user" ON "audit_events" ("user_id");
CREATE INDEX IF NOT EXISTS "idx_audit_action" ON "audit_events" ("action");
CREATE INDEX IF NOT EXISTS "idx_audit_created_at" ON "audit_events" ("created_at");
CREATE INDEX IF NOT EXISTS "idx_audit_correlation" ON "audit_events" ("correlation_id");
CREATE INDEX IF NOT EXISTS "idx_api_tokens_org" ON "api_tokens" ("organization_id");
CREATE INDEX IF NOT EXISTS "idx_api_tokens_hash" ON "api_tokens" ("token_hash");