-- Update Requests: approval workflow for software updates
CREATE TABLE IF NOT EXISTS "update_requests" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "organization_id" UUID NOT NULL REFERENCES "organizations"("id"),
  "device_id" UUID NOT NULL REFERENCES "devices"("id") ON DELETE CASCADE,
  "package_name" VARCHAR(255) NOT NULL,
  "package_manager" VARCHAR(50) NOT NULL DEFAULT 'apt',
  "current_version" VARCHAR(100),
  "target_version" VARCHAR(100) NOT NULL,
  "requested_by_user_id" UUID NOT NULL REFERENCES "users"("id"),
  "status" VARCHAR(50) NOT NULL DEFAULT 'pending',
  "approved_by_user_id" UUID REFERENCES "users"("id"),
  "approved_at" TIMESTAMPTZ,
  "rejection_reason" TEXT,
  "deployment_id" UUID,
  "notes" TEXT,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS "idx_update_requests_org" ON "update_requests"("organization_id");
CREATE INDEX IF NOT EXISTS "idx_update_requests_device" ON "update_requests"("device_id");
CREATE INDEX IF NOT EXISTS "idx_update_requests_status" ON "update_requests"("status");

-- Deployments: track bulk operations across devices
CREATE TABLE IF NOT EXISTS "deployments" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "organization_id" UUID NOT NULL REFERENCES "organizations"("id"),
  "name" VARCHAR(255) NOT NULL,
  "description" TEXT,
  "job_type" VARCHAR(100) NOT NULL,
  "parameters" JSONB NOT NULL DEFAULT '{}',
  "status" VARCHAR(50) NOT NULL DEFAULT 'draft',
  "created_by_user_id" UUID NOT NULL REFERENCES "users"("id"),
  "approved_by_user_id" UUID REFERENCES "users"("id"),
  "approved_at" TIMESTAMPTZ,
  "scheduled_at" TIMESTAMPTZ,
  "started_at" TIMESTAMPTZ,
  "completed_at" TIMESTAMPTZ,
  "total_targets" INTEGER NOT NULL DEFAULT 0,
  "completed_targets" INTEGER NOT NULL DEFAULT 0,
  "failed_targets" INTEGER NOT NULL DEFAULT 0,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS "idx_deployments_org" ON "deployments"("organization_id");
CREATE INDEX IF NOT EXISTS "idx_deployments_status" ON "deployments"("status");

-- Deployment Targets: per-device tracking within a deployment
CREATE TABLE IF NOT EXISTS "deployment_targets" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "deployment_id" UUID NOT NULL REFERENCES "deployments"("id") ON DELETE CASCADE,
  "device_id" UUID NOT NULL REFERENCES "devices"("id") ON DELETE CASCADE,
  "job_id" UUID REFERENCES "jobs"("id"),
  "status" VARCHAR(50) NOT NULL DEFAULT 'pending',
  "result" JSONB,
  "started_at" TIMESTAMPTZ,
  "completed_at" TIMESTAMPTZ,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS "idx_deployment_targets_deployment" ON "deployment_targets"("deployment_id");
CREATE INDEX IF NOT EXISTS "idx_deployment_targets_device" ON "deployment_targets"("device_id");
CREATE INDEX IF NOT EXISTS "idx_deployment_targets_status" ON "deployment_targets"("status");

-- Link update_requests.deployment_id foreign key
ALTER TABLE "update_requests"
  ADD CONSTRAINT "fk_update_requests_deployment"
  FOREIGN KEY ("deployment_id") REFERENCES "deployments"("id")
  ON DELETE SET NULL;